Metadata-Driven ETL: delivering new data assets faster

We believe it's time to reveal how we work and why our metadata-driven approach allows us to deliver data integration projects more efficiently than traditional solutions. While traditional ETL (Extract, Transform, Load) has evolved significantly during the years, it remains a critical topic for enterprise data integration projects. This blog reveals the data integration philosophy that drives our minds and our solutions. The principles explained later in this blog are already implemented in the SAS-based software solution - MDIS Data Management (hereinafter, MDIS). Customers in public, insurance, and banking sectors successfully use MDIS for enterprise data integration purposes for many years. The blog is intended for our existing customers, our future clients, and for everyone interested in modern data integration methodologies.

The Challenges Behind Enterprise ETL

‍

Organizations that rely on traditional data integration tools often face a common challenge: ETL systems become increasingly difficult to maintain as data volumes, business rules, and regulatory requirements continue to grow. What initially starts as a manageable collection of ETL jobs gradually evolves into a huge number of lines of code, thousands of workflows, and a growing dependency on a small number of experienced developers.

Why Traditional ETL Becomes Difficult to Sustain

‍

Traditional ETL projects are usually based on manually developed programs, graphical ETL workflows, manually maintained execution order, custom scheduling logic, and extensive testing after every change. As projects grow, implementation time and maintenance costs increase, while even small requirement changes require big modifications across multiple jobs.

‍

The Problem Lies in the Traditional Way ETL Solutions Are Designed

‍

A metadata-driven approach is a game changer - instead of building static ETL workflows, in MDIS developers define metadata describing what should happen, while the platform determines how it should be executed. This significantly reduces development work, simplifies maintenance, and minimizes implementation risks.

‍

Metadata Instead of Static ETL Flows

‍

With MDIS, developers configure metadata describing target tables, validation rules, transformations, historization logic, process dependencies, and execution parameters. The execution engine automatically generates the required processing logic.

Automatic Detection of Processing Order

‍

One of the most difficult tasks in large ETL environments is maintaining the correct table processing order. Rather than hardcoding execution sequences, MDIS analyzes validation rules, table dependencies, and process relationships to determine the execution order automatically.

Record-Level Processing Improves Performance

‍

MDIS generates SAS code where the essential job is performed by a single SAS DATA step. It reads the source table row by row and performs needed transformations, joins with other tables, and performs data quality checks on a row level. So, the source table is scanned only once and the target table is created in a single step. To perform the same operations, traditional DBMS require multiple SQL steps and/or database cursors, which can be slow. As a result, they cannot achieve the same level of performance as MDIS.

Parallel Processing Without Manual Optimization

‍

MDIS leverages historical execution statistics to optimize process execution. Based on historical performance, it determines whether parallel execution is beneficial, selects the optimal number of parallel sessions, and schedules processes while respecting their dependencies.

Simplifying System Maintenance

‍

Business requirements continuously evolve. In regulated sectors, this may include new validation rules, audit expectations, reporting changes, regulatory updates, and new source systems. MDIS metadata-driven architecture isolates business logic into configurable metadata, making changes localized, reducing regression testing effort, and minimizing deployment risks.

Built-in Operational Intelligence

‍

MDIS provides comprehensive operational visibility through execution registers, validation registers, change registers, historization registers, execution simulation, and automatic failure recovery. Together, these capabilities give administrators a centralized view of the entire ETL environment, allowing them to track executed processes, applied validations, data changes, and recovery activities after failures. 

Supporting the Entire Data Integration Lifecycle

‍

MDIS metadata-driven architecture simplifies development, testing, initial data loading, production operations, and future enhancements through reusable metadata rather than project-specific implementations.

Conclusion

‍

As enterprise environments continue to grow in size and complexity, developing traditional ETL implementations becomes increasingly expensive. Moreover, implementation of changes often becomes impossible.

MDIS metadata-driven architecture replaces static workflows with configurable metadata and automated execution logic, reducing implementation effort, simplifying maintenance, and enabling organizations to focus on business requirements instead of ETL mechanics and deliver new data assets faster.

‍

… we will provide more details and unveil more in future blogs.

‍

‍