The short answer
In an enterprise data platform ingesting data from 500 relational database tables into Azure Data Lake Storage, building 500 individual Azure Data Factory pipelines is an anti-pattern. Individual pipelines duplicate code, inflate deployment complexity, and create an unmanageable maintenance burden.
The professional design pattern is an ADF metadata-driven ingestion framework.
The Control Table architecture
The framework centers around a metadata control table stored in Azure SQL Database:
CREATE TABLE config.etl_control (
table_id INT IDENTITY(1,1),
source_schema VARCHAR(50),
source_table VARCHAR(100),
watermark_column VARCHAR(50),
watermark_value DATETIME,
target_container VARCHAR(50),
target_folder VARCHAR(200),
is_active BIT
);The generic orchestration pipeline
A single parameterized ADF pipeline ingests all 500 tables dynamically:
- Lookup Activity: Queries
config.etl_controlto fetch the list of active tables whereis_active = 1. - ForEach Activity: Iterates through the list of returned table metadata records concurrently.
- Copy Activity: Inside the loop, a single generic Copy Activity references parameterized datasets. The source SQL query is constructed dynamically using pipeline expressions:
{
"source": {
"type": "SqlSource",
"sqlReaderQuery": "@concat('SELECT * FROM ', item().source_schema, '.', item().source_table, ' WHERE ', item().watermark_column, ' > ''', item().watermark_value, '''')"
}
}After each successful data extraction completes:
- Update Watermark: A Stored Procedure activity updates
watermark_valuein the control table.
Operational resilience
If an upstream source error or temporary network timeout interrupts a table transfer, only that specific table iteration fails. The ForEach loop continues extracting remaining tables uninterrupted, and operators can re-run failed tables by updating control rows. Onboarding a new table requires adding a single row to the SQL control table, with zero pipeline changes or code redeployments.