Metadata-driven ETL: how far should configuration go?

Two earlier posts on this site argue for jobs-as-configuration: the scheduler-inside-the-scheduler post says specs are right and runtime dispatch is wrong, and the DAG factory post shows config generating DAGs across environments. Both leave a question hanging: how much of the pipeline itself should live in that spec?

Because once the factory exists, the temptation curve is steep. First the spec holds a schedule and an owner. Then a source table and a target table. Then a column list. Then a filter expression. Then a transform_sql field. Then, one sprint later, someone adds if semantics to the spec schema and you have invented a programming language — one with no debugger, no tests, no IDE support, and a YAML parser for a compiler.

Metadata-driven ETL is the right default for a large estate. I have run a platform where a couple of hundred ingestion pipelines were JSON documents and perhaps two dozen genuinely bespoke DAGs were Python. The ratio is the point: the win comes from being clear about which side of the line each job belongs on, and from making the line itself hard to blur.

The spectrum

Every pipeline platform sits somewhere on this spectrum:

The two ends fail in opposite ways. All the way left, fifty ingestion jobs are fifty files of copy-pasted Python that drift apart — the disease the factory cured. All the way right, the spec has absorbed so much behaviour that it is code in everything but syntax and tooling. The defensible stop is the third box: metadata selects and parameterizes behaviour; it never defines it.

What belongs in metadata

The test I use: a field belongs in the spec if it states a fact about the job, not a procedure for doing it. Facts are things a reviewer can verify against a source system, a catalog, or an agreement with the data owner:

• Source and target: system, schema, table, file pattern, landing bucket.
• The load pattern's name — full_reload, incremental_by_watermark, scd2_merge — plus the parameters that pattern requires: key columns, the watermark column, the soft-delete flag.
• Scheduling facts: cadence or timetable name, dependencies expressed as upstream assets.
• Operational facts: owner, SLA, alert channel, environments (feeding straight into the factory from the previous posts).

Everything in that list is declarative, diffable, and boring — which is exactly what you want reviewed in a pull request that says "onboard table number 214." A well-run estate approves that PR in minutes, because there is nothing in it that can loop, branch, or surprise.

What belongs in code

The load patterns themselves. A pattern is a real Python function — task group, taskflow function, whatever shape your platform prefers — written once, unit-tested, and versioned like any other code:

PATTERNS = {
    "full_reload": full_reload,
    "incremental_by_watermark": incremental_by_watermark,
    "scd2_merge": scd2_merge,
}
 
def build_tasks(spec: JobSpec):
    try:
        pattern = PATTERNS[spec.load_pattern]
    except KeyError:
        raise UnknownPatternError(spec.load_pattern, known=PATTERNS)
    return pattern(spec)

The dictionary is the whole design. It is a closed set: the spec can choose any pattern that exists, and cannot express one that does not. When a new job does not fit — and a few genuinely will not — there are exactly two sanctioned moves:

1. Promote a new pattern. If the need is generic (a third source system wants CDC-style merges), write cdc_merge as tested code, add it to the dictionary, and every future job gets it by name.
2. Write a real DAG. If the need is specific — a business transformation, a one-off sequence, anything with actual logic in it — it is not an ingestion spec's job. It gets Python, code review, and unit tests like the application code it actually is.

Both moves keep behaviour in code. What must never happen is the third, unsanctioned move: bending the spec schema so the square job fits the round pattern.

The inner-platform smell

The failure mode has a name — the inner-platform effect: rebuilding the features of your programming language, badly, inside your configuration. It never arrives as a design decision. It arrives as five reasonable pull requests:

• A custom_sql field, "just for this one table."
• A skip_if expression evaluated at run time.
• Jinja templating inside spec values, so specs can reference other specs.
• A steps array, because one job needed two actions in sequence.
• A condition block with == and in operators, because the steps needed branching.

Each is a small mercy for one job. Together they convert every spec on the platform from a reviewable fact sheet into a program that must be mentally executed to be understood — with none of the tooling that makes programs tractable. You cannot put a breakpoint in a JSON file. You cannot write a unit test for one spec's condition block without running the framework that interprets it. The fifty-line generic interpreter that made the platform elegant becomes the only person on the team who knows what will happen at 2am, and it is not on call.

My working rule, borrowed from the binding-time argument of the anti-pattern post: if a reviewer must trace the interpreter to predict what a spec change does, the spec has crossed the line. Facts left of the line, procedures right of it, and the escape hatch — a plain DAG — kept cheap and unshameful so nobody is tempted to smuggle procedures across.

Where the metadata lives

The classic warehouse designs of the 2000s put mapping metadata in database tables — a data_flow table, a column_mapping table, loaded by the ETL tool at run time. I understand why: specs-in-tables were editable without a deployment, and deployments were the expensive part back then.

Today that trade is inverted, and specs belong in git, next to the patterns that interpret them:

• Review is the control. A spec change in git gets a diff, an approver, and CI validation against the schema. A row updated in a metadata table gets none of that — it is the mutable-Airflow-Variable problem from the factory post, at estate scale.
• Parse time must stay cheap. The factory reads specs on every scheduler parse cycle; local files cost nothing, database reads at import time are the exact thing the earlier posts warn against.
• Environments come free. Specs in git flow through the same promotion gates as everything else. Metadata tables need their own migration and sync story per environment — a second deployment system maintained solely to avoid the first one.

Run-time state — watermarks, run outcomes, row counts — still belongs in a database, written by tasks as they run. The distinction is the same line again: the spec is facts decided before the run; state is facts produced by the run. Keeping them in different stores keeps anyone from blurring declaration and execution.

The honest trade-offs

Generic code fails generically. When incremental_by_watermark breaks for table 214, the stack trace speaks framework, not job. Good patterns log the resolved spec — every parameter, post-substitution — as the first act of every run, so the on-call engineer debugs values, not indirection.

Patterns accrete parameters. Every "almost fits" job lobbies for one more optional field, and a pattern with fifteen optional parameters is a branchy program wearing a pattern's name. When a parameter is only used by two jobs, that is usually two bespoke DAGs asking to exist.

The platform team becomes a bottleneck by design. Promoting a pattern requires their review; that is the mechanism working, but it needs an SLA. If a new pattern takes a quarter to land, teams will not wait — they will tunnel logic through whatever spec fields already exist, and the smell list above writes itself.

The takeaway

Metadata-driven ETL fails at the extremes and earns its keep in the middle: specs that state facts, a closed dictionary of tested patterns that carry all the behaviour, and a cheap, respectable exit to plain Python for the jobs that were never ingestion in the first place. The previous posts argued that specs should bind at parse time; this one adds the other half — decide what a spec is allowed to say, and enforce it in the schema, before the fifth reasonable pull request decides for you.