Designing a metadata-driven ETL framework with Azure ADF: An architectural perspective

In today’s data-driven landscape, integrating diverse data sources into a cohesive system is a complex challenge. As an architect, I set out to design a solution that could seamlessly connect on-premises databases, cloud applications and file systems to a centralized data warehouse. Traditional ETL (extract, transform, load) processes often felt rigid and inefficient, struggling to keep pace with the rapid evolution of data ecosystems. My vision was to create an architecture that not only scaled effortlessly but also adapted dynamically to new requirements without constant manual rework. 

The result of this vision is a metadata-driven ETL framework built on Azure Data Factory (ADF). By leveraging metadata to define and drive ETL processes, the system offers unparalleled flexibility and efficiency. In this article, I’ll share the thought process behind this design, the key architectural decisions I made and how I addressed the challenges that arose during its development. 

Recognizing the need for a new approach 

The proliferation of data sources — ranging from relational databases like SQL Server and Oracle to SaaS platforms like Salesforce and file-based systems like SFTP — exposed the limitations of conventional ETL strategies. Each new source typically requires a custom-built pipeline, which quickly became a maintenance burden. Adjusting these pipelines to accommodate shifting requirements was time-consuming and resource-intensive. I realized that a more agile and sustainable approach is essential. 

A metadata-driven design emerged as the ideal solution. By centralizing ETL configurations in a metadata repository, I could abstract the logic of the processes away from the pipelines themselves. This abstraction allowed for the addition of a new data source or modification of an existing flow to be achieved by updating metadata rather than rewriting code. The promise of reduced maintenance and faster adaptability drove me to pursue this direction. 

Crafting the architectural framework 

Laying the groundwork with a metadata schema 

The cornerstone of this architecture is a carefully crafted metadata schema, hosted in an Azure SQL Database. This schema captures all the necessary details to orchestrate the ETL process: connection information, copy operation definitions and configuration settings. I chose Azure SQL Database for its robust relational capabilities, which enable efficient querying and management of intricate metadata relationships. Its tight integration with Azure services, such as ADF, ensures a smooth data flow, while its scalability and high availability meet the demands of a reliable system. 

The schema includes several key tables: 

Connections: Stores source and destination details, with sensitive data linked to secure references rather than stored directly. 

CopyOperations: Specifies the types of data transfers, such as database-to-database or file-to-storage. 

ETLConfig: Defines the specifics of each ETL process, including objects to transfer, execution order and any pre- or post-processing steps. 

This metadata serves as the blueprint, dynamically guiding the entire ETL workflow. 

Orchestrating with a parent pipeline 

Central to the framework is a parent pipeline in ADF, which I designed as the primary orchestrator. This pipeline is parameterized to accept an identifier for a specific ETL flow. When triggered, it calls a stored procedure in the Azure SQL Database, which returns a JSON response detailing the processes to execute. For example, the JSON might outline a sequence of transfers from a database to a data lake, each with its own parameters. 

The parent pipeline then delegates these tasks to specialized child pipelines, passing along the necessary metadata. This design eliminates the need for hardcoding specific flows into the pipeline itself, making it highly adaptable. The decision to use a stored procedure for generating the JSON configuration was intentional — it allows complex logic to reside in the database, keeping the pipeline lean and focused on execution. 

Vikram Garg

Building modularity with child pipelines 

To manage the variety of data sources and destinations, I opted for a modular structure. Each type of data transfer — whether from a database to another database or a file system to cloud storage — is handled by a dedicated child pipeline. These child pipelines are templates and parameterized, meaning they can be reused across different scenarios by simply adjusting the runtime inputs. 

For instance, a child pipeline designed for database-to-database transfers can handle any such operation by receiving the appropriate source and destination details from the metadata. This modularity simplifies expansion: supporting a new transfer type involves creating a new child pipeline template and updating the metadata, rather than redesigning the entire system. 

Prioritizing security 

Security is a non-negotiable aspect of data integration. To safeguard sensitive information like connection credentials, I integrated Azure Key Vault into the framework. The metadata schema references only the keys to these secrets, which are retrieved by the pipelines at runtime. This ensures that sensitive data remains encrypted and inaccessible outside of authorized processes. 

This design not only bolsters security but also streamlines secret management across different environments, such as development and production. It was a deliberate choice to embed security into the architecture from the ground up, ensuring compliance and trust in the system. 

Enabling flexible execution 

The framework is needed to support both scheduled and on-demand ETL processes. To achieve this, I incorporated multiple triggering mechanisms. For scheduled runs, I used Azure Logic Apps, which read timing configurations from the metadata and initiate the parent pipeline accordingly. For on-demand execution, an API endpoint via Azure API Management allows external systems or users to trigger the process as needed. 

I also added event-based triggers, such as monitoring a file drop location for new arrivals and launching the ETL process automatically. This versatility ensures the framework can meet a wide range of operational demands. 

Ensuring reliability with monitoring and logging 

A robust ETL system requires visibility and reliability. I built comprehensive logging into the framework, capturing execution details—start times, end times, statuses and errors — in the Azure SQL Database. For real-time oversight, I integrated Azure Application Insights to monitor pipeline performance. 

To handle failures, I designed a utility pipeline that logs exceptions, sends alerts for critical issues and flags problems for investigation. This proactive monitoring minimizes disruptions and keeps the system running smoothly. 

Empowering users with a simple interface 

To broaden the framework’s usability, I developed a web-based interface hosted on Azure App Service. This UI allows non-technical users to manage metadata — adding new sources, adjusting configurations or setting schedules — without needing to navigate ADF directly. This self-service capability reduces IT dependency and accelerates the integration process. 

Tackling design challenges 

The design process presented several hurdles. One major challenge was ensuring the framework could handle diverse data sources without becoming overly complex. The modular child pipelines addressed this by isolating each operation type, maintaining simplicity while supporting variety. 

Performance was another concern. With large data volumes, I needed to minimize latency and maximize throughput. I employed parallel processing where feasible and optimized data partitioning to enhance efficiency. ADF’s native data movement features further streamlined transfers. 

Scalability posed its own issues. As the number of copy operations grew, I encountered limitations in ADF’s switch case activity, which is capped at 25 cases. To overcome this, I added a categorization layer in the parent pipeline, grouping operations by type and effectively expanding capacity. This solution required careful validation but proved effective. 

Another constraint was ADF’s static integration runtime selection for on-premises sources. Since runtime couldn’t be dynamically chosen, I configured multiple linked services tied to different runtimes, selecting the appropriate one via metadata. It’s a workaround that ensures compatibility with hybrid environments. 

Reflecting on the design journey 

This project underscored several critical insights. Scalability must be baked into the architecture from the start — using metadata as the driver ensures the system can evolve without constant refactoring. Modularity simplifies maintenance and extension, proving its worth as new needs arise. Security, integrated early, builds a foundation of trust. And empowering users with intuitive tools fosters collaboration and efficiency. 

This metadata-driven ETL framework represents a shift in how I approach data integration. Its flexibility, scalability and user-centric design make it a powerful tool for managing complex data ecosystems. For fellow architects, I’d recommend exploring metadata-driven solutions — they require upfront effort but deliver lasting value. My experience with Azure ADF has been a journey of innovation, and I look forward to seeing how this approach evolves. 

This article is published as part of the Foundry Expert Contributor Network. Want to join?