A Metadata-Driven Ingestion Framework in Azure Data Factory

Most ingestion estates grow one pipeline per source table. It works for ten tables. At two hundred, every schema change, credential rotation or logging improvement means editing two hundred pipelines, and nobody is sure they all behave the same way.

A metadata-driven framework flips this around. One generic pipeline does the copying. A control table says what to copy, how, and where. Adding a source becomes inserting a row.

The control table

Keep it in an Azure SQL database (or a Delta table if your orchestration lives in Databricks). A minimal version:

CREATE TABLE etl.ingestion_control (
    id               INT IDENTITY PRIMARY KEY,
    source_system    VARCHAR(50)  NOT NULL,   -- e.g. 'erp', 'crm'
    source_schema    VARCHAR(128) NOT NULL,
    source_table     VARCHAR(128) NOT NULL,
    load_type        VARCHAR(20)  NOT NULL,   -- 'full' | 'incremental' | 'cdc'
    watermark_column VARCHAR(128) NULL,
    last_watermark   VARCHAR(64)  NULL,
    target_path      VARCHAR(400) NOT NULL,   -- bronze/erp/customers/
    is_enabled       BIT          NOT NULL DEFAULT 1,
    priority         INT          NOT NULL DEFAULT 100
);

Everything that varies between tables lives here. Everything that stays the same lives in the pipeline.

The pipeline structure

1. Lookup reads enabled rows for a source system, ordered by priority.
2. ForEach iterates over them with a batch count of, say, 8 to 16, so tables load in parallel without overwhelming the source.
3. Switch on load_type chooses the copy pattern.
4. Copy writes Parquet or Delta into the bronze path, partitioned by load date.
5. Stored procedure updates last_watermark only after a successful copy.
6. Logging writes rows copied, duration and status to a run log table.

Incremental loads with a watermark

For tables with a reliable modified_at column, the source query is built from metadata:

SELECT *
FROM @{item().source_schema}.@{item().source_table}
WHERE @{item().watermark_column} > '@{item().last_watermark}'
  AND @{item().watermark_column} <= '@{pipeline().parameters.run_ts}'

Two rules keep this correct:

• Bound the upper end with a run timestamp captured at the start, so rows modified during the copy are picked up next time rather than lost.
• Update the watermark after the copy succeeds, never before. A failed run then simply repeats the same window.

CDC and deletes

Watermarks miss deletes. For tables where deletes matter, use change data capture:

• SQL Server and Azure SQL: enable CDC on the source and read the change tables, or use the native ADF CDC capability.
• SAP: the SAP CDC connector reads changes through the operational data provisioning framework.
• Everything else: periodic full snapshots compared in the lakehouse.

Land changes in bronze as they arrive, and let the lakehouse apply them. In Databricks, APPLY CHANGES INTO in a DLT pipeline turns the change feed into SCD type 1 or 2 tables in silver.

Connections without secrets in JSON

• Use managed identity for Azure sources and the data lake.
• Keep every other credential in Key Vault, referenced by the linked service.
• Use a self-hosted integration runtime on two nodes for on-premises sources, so one VM restart does not stop ingestion.
• Parameterise linked services by server and database name, so one linked service serves many sources.

Deployment and monitoring

• Connect the factory to git and deploy with ARM or Bicep templates through a release pipeline, with environment-specific parameters.
• Send diagnostics to Log Analytics and alert on failed runs and on tables whose watermark has not moved for longer than expected. A silent stall is more dangerous than a failure.
• Keep the control table in source control too, as seed scripts per environment, so dev and prod agree on what is ingested.

When not to use this pattern

A framework is overhead if you have five tables that never change, or if a managed connector service already covers your sources. It earns its keep when sources are numerous, similar in shape and owned by a small team that needs consistency more than flexibility.

The payoff

Once it is in place, onboarding a new table is a reviewed insert into the control table. Logging, security and retry behaviour are the same for every table, and improving them is a change to one pipeline.