Metadata-driven ingestion framework: design patterns that scale

A metadata-driven ingestion framework is one generic, tested pipeline engine plus a configuration entry per source table that says where the data comes from, how it changes, which key identifies a row and how far the last load got. Adding the three-hundredth table then means merging a reviewed config entry, not writing new pipeline code. The patterns that make it hold at that scale are a typed config schema generated from live source metadata, a load mode chosen per table (snapshot, cursor incremental or CDC), watermarks that advance only after data has landed, reruns that are safe by construction, bounded fan-out per source, and alerts on schema drift.

Most of these decisions are platform-neutral; where Snowflake, Databricks or BigQuery differ, we say so.

What makes ingestion “metadata-driven”?

Hand-built ingestion is usually one script per source, each with its own idea of what “incremental” means, its own retry logic and its own file layout. The hundredth table costs what the first did, and every failure is a new investigation. A metadata-driven design splits the problem into three parts that change at different speeds:

• Configuration says what to load. It changes when sources change, lives in version control and is reviewed like code.
• The engine knows how to load. It changes rarely and is tested like a product.
• State records how far each table got: watermarks and the run log. It changes on every run and is edited only through a reset command.

Separating state from configuration is the decision that matters most early on. If watermarks sit in the same YAML file or the same control-table row as the config, a deploy can reset progress, and every run needs write access to its own configuration.

What belongs in the configuration schema?

The source registry

Each source system gets one registry entry: the type of connector, a reference to the secret (its name, never the secret itself), the source time zone, the network route or proxy, an owner and a maximum number of concurrent extract workers. The concurrency limit belongs here, at source level, because it protects an operational database that is also serving users. Per-table settings cannot enforce it.

Per-table entries

source: erp_core
tables:
  - name: sales_order
    load_mode: incremental          # snapshot | incremental | cdc
    cursor: last_modified_at
    overlap: 2h
    primary_key: [order_id]
    deletes: weekly_snapshot        # cdc_operation | soft_flag | weekly_snapshot | none
    schema_policy: evolve_columns   # evolve_columns | freeze | discard_row
    columns:
      order_id:         {type: bigint, nullable: false}
      status:           {type: string}
      amount:           {type: decimal(18,2)}
      last_modified_at: {type: timestamp, source_tz: Europe/Bucharest}
    schedule_group: erp_core_hourly
    owner: finance-data

Field	Why it is there
load_mode	Decides cost and what changes the engine can see (next section)
cursor, overlap	Define the incremental window; the overlap covers late commits
primary_key	Lets downstream merges absorb duplicates from overlaps and reruns
deletes	Forces someone to state how deletes become visible, per table
columns with types	Stops type inference from guessing, and makes drift detectable
schema_policy	What happens when the source shape changes
schedule_group, owner	Fan-out grouping, and who gets the alert

Generate it, then review it

Nobody types hundreds of these entries correctly. Generate them from the database catalog views or the object metadata a SaaS API exposes; people review the diff and set the fields that need judgment, mostly load mode and delete handling. Rerunning the generator later shows where configuration and source have drifted apart.

Control tables in a database are the common alternative to YAML in git. Operators can change a schedule without a deploy, but changes skip review and environments drift. We prefer configuration in git and state kept separately.

Snapshot, incremental or CDC: how do you choose per table?

Deletes decide most of this. A timestamp cursor sees inserts and updates, but a row that has been hard-deleted leaves nothing for it to find.

Source behaviour	Load mode	How deletes show up	Cost
Small reference table	Full snapshot every run	Key missing from the latest snapshot	Reads everything, trivial at this size
Reliable modified timestamp, soft deletes	Cursor incremental with overlap	Soft-delete flag	Reads only changed rows
Database with a change log or CDC tables	CDC, ordered by commit sequence	Delete arrives as an operation	Needs log retention and monitoring
Hard deletes, no trustworthy change column	Periodic snapshot, optionally incremental in between	Snapshot comparison downstream	Expensive on large tables, so schedule it sparingly
Append-only events	Incremental on event ID or event time	Not applicable	Cheapest
SaaS API	Incremental on the API’s modified field	Depends on the API	Bound by API limits

Check the API before assuming deletes are invisible. Salesforce Bulk API 2.0 query jobs accept a queryAll operation that returns records deleted by a merge or delete. Bulk API 2.0 also rejects compound fields such as addresses, so field lists must name the components.

How should watermarks work?

The watermark is the number that silently loses data when it is wrong. Five rules keep it right:

1. Read with an overlap. Extract rows where cursor > watermark - overlap. A transaction can stamp last_modified_at when it writes and commit minutes later; a reader that has already moved past that timestamp never sees the row. Commit-ordered CDC sequences avoid this, and for timestamp cursors the overlap is the defence. dlt calls the same idea lag, or an attribution window.
2. Commit after landing. The new watermark (the highest cursor value actually landed) is written only once the files are in place. A run that fails in between keeps the old watermark and the next run reads the same range again.
3. Compare and swap. Store each table’s watermark as a small object and write it with a precondition: ifGenerationMatch on Cloud Storage, If-Match on Amazon S3, an If-Match ETag on Azure Blob Storage. Two writers can then never both succeed.
4. Keep it readable and resettable. Plain JSON that an engineer can read during an incident, plus a reset command that keeps previous values, beats a binary checkpoint nobody dares touch.
5. One run per source at a time. A lease (a lock object with an expiry) stops a slow scheduled run and a manual rerun from racing each other.

Backfills are a separate job with an explicit range. dlt, for example, bounds a load with initial_value and end_value, and such ranges can run in parallel. Give backfill jobs more memory and a longer timeout: the first load of a large table looks nothing like the hourly increment.

How do you handle schema drift without breaking pipelines?

Classify changes before deciding what to automate. Additive changes (a new column) and relaxations (a dropped NOT NULL, a widened numeric type) are safe to apply automatically. Renames, narrowed types, dropped columns and primary key changes break consumers, so for any table others depend on they should stop the load and page someone. dlt’s schema contracts express this per table, column and data type with the modes evolve, freeze, discard_row and discard_value.

The warehouses handle evolution differently:

• Snowflake adds new columns during COPY INTO and Snowpipe loads when the table has ENABLE_SCHEMA_EVOLUTION = TRUE, the COPY statement uses MATCH_BY_COLUMN_NAME, and the loading role has EVOLVE SCHEMA or OWNERSHIP on the table. INSERT statements cannot evolve a table.
• Databricks Auto Loader stops the stream when it detects a new column and uses the evolved schema on restart, which is why Databricks recommends running it under Lakeflow Jobs with automatic restarts. rescuedDataColumn keeps unexpected values instead of dropping them, and automatic type widening is in Public Preview on Databricks Runtime 16.4 and above.
• BigQuery load and query jobs that append can add or relax columns with the ALLOW_FIELD_ADDITION and ALLOW_FIELD_RELAXATION schema update options.

Two habits prevent most drift incidents. Declare column types for core tables instead of inferring them from a sample, which turns an integer column into a float the first time a decimal appears. And snapshot every source’s data dictionary on a schedule, independent of the loads, then diff it: the alert names the table and column that changed, even on tables that have not loaded since, and the snapshots can feed your data catalog.

How do you make reruns idempotent?

With an overlap, duplicates are part of the design, so a rerun of any range has to be harmless:

• Write each run’s output as new files under a date and hour partition, with the run ID in the file name. Never rewrite earlier files in place.
• Keep bookkeeping (watermarks, manifests, leases) under a separate prefix, so external tables over the landing zone never read it as data.
• Stamp snapshots with the instant they were taken, so a snapshot landed twice is recognised as the same one.
• Merge downstream on the primary key plus a hash of the tracked columns. A row that arrives twice updates itself, and a history layer with deterministic tie-breaking produces the same result however often a range is replayed.
• Fail per table. A failed table keeps its watermark while the other tables in the source continue, and the rerun picks up only the failures.

How do you orchestrate hundreds of tables?

Generate the fan-out from configuration at runtime instead of writing a DAG file per table. Airflow has dynamic task mapping, AWS Step Functions has the Distributed Map state, and Google Cloud Workflows has parallel loops. Always set a concurrency limit: if Distributed Map’s MaxConcurrency is unset or zero, Step Functions runs up to 10,000 child executions in parallel, which no source database will welcome.

A task per table gives per-table retries and a readable UI, but at several hundred tables the scheduler spends real effort on bookkeeping. A task per source, with a bounded worker pool inside that reports per-table results, is cheaper and keeps the source’s concurrency limit in one place. We lean towards the second.

The workflow stays simple: ingestion jobs in parallel, then transformations in dependency order, with a guard so the scheduled pipeline and an initial backfill never overlap, and a dry-run mode that prints which jobs an execution would start.

What should ingestion observability and quality gates check?

Write one run-log record per table per run: run ID, mode, watermark before and after, rows read, files and bytes written, duration, status and a hash of the schema. Gates at ingestion should stay cheap and structural:

Check	Action
Job or workflow failed	Alert
Null primary key in landed data	Fail the table and keep its watermark
Source schema changed	Alert; stop the table if its policy is freeze
Zero rows where changes are normal	Warn
Watermark not moving for several runs	Warn, then alert
No run at all (paused or broken schedule)	Alert, because silence is a failure too
Snapshot row count far from the source count	Warn

Business rules belong downstream, in the transformation layer’s tests. Ingestion moves rows; deciding whether they make sense is the next layer’s job.

How do you land data on Snowflake, Databricks and BigQuery?

A lake-first landing zone works on all three: typed Parquet in object storage, hive-partitioned as <table>/dt=YYYY-MM-DD/hr=HH/, then exposed to the warehouse. The extraction side never needs to know which warehouse reads it, you can replay without going back to the source, and a later warehouse change leaves extraction untouched. That matters during platform moves such as a Teradata to Snowflake or Databricks migration.

Platform	Query the landing zone in place	Load into native tables	Worth knowing
Snowflake	External tables; for Parquet, Snowflake now recommends Parquet Direct (preview since 16 October 2026), which registers read-only Iceberg tables over existing files	COPY INTO or Snowpipe, with schema evolution as above	External tables are read-only and slower than native tables
Databricks	Not the usual pattern; files are ingested as they arrive	Auto Loader in Lakeflow pipelines, which tracks schema and checkpoints	Auto Loader and the target table each persist a schema, and both have to evolve together
BigQuery	External or BigLake tables with hive partitioning (AUTO, STRINGS or CUSTOM key detection, optional requirePartitionFilter)	Load jobs with schema update options	Google recommends BigLake tables for table-level fine-grained security

For database CDC straight into BigQuery, Google’s Datastream writes to BigQuery in merge or append-only mode and adds a datastream_metadata column, which is an option when you want CDC without running extraction yourself.

Should you build an ingestion framework or buy managed connectors?

The managed options differ more in pricing model than in feature lists:

• Fivetran bills by monthly active rows: distinct primary keys synced in a month, counted separately per connection and table. Cost follows how many rows change, not how many exist. Fivetran completed its merger with dbt Labs on 1 June 2026.
• Airbyte offers volume-based and capacity-based plans, the latter priced on Data Workers, plus Airbyte Core to self-host under the Elastic License 2.0 and a hybrid Enterprise Flex option.
• Databricks Lakeflow Connect provides managed database, SaaS, file and streaming connectors priced on compute. For databases, an ingestion gateway runs continuously on classic compute, so you pay for it even while the pipeline is idle. You choose objects, destination and schedule, but not the ingestion logic itself.
• Snowflake Openflow is a managed integration service built on Apache NiFi, deployable inside Snowflake on AWS, Azure and GCP, or as BYOC in your own AWS account.

Criterion	Managed service (Fivetran, Airbyte Cloud)	Platform-native (Lakeflow Connect, Openflow)	Own framework
Time to first table	Fastest	Fast, if you are already on the platform	Slowest at first, unless you start from an existing engine
Cost driver	Rows changed or capacity bought	Platform compute	Your cloud compute plus engineering time
Long-tail SaaS coverage	Strongest	Growing, varies by release state	Weakest; each API is work
Unusual CDC, delete or type semantics	Vendor’s behaviour	Vendor’s behaviour	Yours to define and test
Data path and residency	Vendor-managed; check hybrid options	Inside your platform account	Entirely in your cloud account
Warehouse lock-in	Low	Tied to one platform	Low with an open landing format
Operating burden	Lowest	Low	Yours

Many enterprises end up running both. Managed connectors cover the dozens of SaaS tools with standard APIs, where maintaining extraction code would eat a team’s time. An own framework covers the few core systems with hundreds of tables, odd delete behaviour and data that must stay in your account. Land both in the same lake with the same conventions and the transformation layer never needs to know which one delivered a row.

Where Duces fits

Our Data Ingestion Framework is built on these patterns. It lands databases (Oracle and MySQL connectors today, other SQLAlchemy engines such as PostgreSQL and SQL Server through the same path), Salesforce through Bulk API 2.0 and any REST API through dlt’s declarative REST source, as typed, hive-partitioned Parquet in your own Google Cloud, AWS or Azure account. Importers generate the YAML configuration from live schemas, so adding a table is one reviewed entry. Watermarks are plain JSON, committed only after data lands and protected by optimistic concurrency, a lease guarantees one run per source, and alerts cover failed jobs and changed source schemas. It deliberately does not model data: the Data Processing & Transformation Framework reads the landing zone and builds SCD2 history, with models generated from the same configuration. Both belong to our Data & AI practice and its Data Integration Factory.

If you are weighing managed connectors against your own framework, or your ingestion scripts have started to fail in new ways each month, talk to the people who build it.