Notes to myself on a metadata-driven ELT platform at a large UK general insurer. What the pattern bought us, the seven ways it went wrong, and the thing all seven had in common. Written down because in a year I will remember that it worked and I will have forgotten why it hurt.
Why this post exists. I am writing this for the version of me who picks up a similar platform in twelve months and thinks "metadata-driven, great, I have done this before". I have. It was a good pattern and I would build it again. But almost every incident I worked on there came back to the same structural property, and that is worth having written down rather than half remembered. Everything below is generalised. No employer, no product names, no ticket numbers, and the object names are neutral stand-ins for the real ones.
A Snowflake warehouse fed by Matillion, sourcing mostly from a Databricks lake with some direct extracts from an old iSeries platform, some file drops on S3, and a few Snowflake to Snowflake copies. Data moved through four layers on the way in.
The part worth writing down is that almost none of that was hard-coded. The behaviour of the platform lived in tables, not in pipelines.
This was the big one. There was a control table in an admin schema holding one row per source object, and a small number of generic pipelines that read it. Onboarding a new table was an insert. Not a new job, not a code change, not a release.
The columns that mattered were roughly these.
SOURCE_SYSTEM, SOURCE_SCHEMA, SOURCE_TABLE EXTRACTION_METHOD -- which branch of the generic job to take EXTRACTION_ORDER -- dependency sequencing LANDING_SCHEMA / LANDING_TABLE, and the staging equivalents CDC_COLUMN_IDENTIFIER -- which column to watch for change CDC_LAST_VALUE -- the watermark, written back after every run LAST_EXTRACT_DATETIME / NEXT_EXTRACT_DATETIME INTERVAL_INCREMENT / INTERVAL_UNIT WHERE_STATEMENT -- optional source-side filter ERROR_IF_NO_DATA -- is an empty extract a failure or not TRUNCATE_STAGING_FLAG CLONE_TO_LOWER_ENVS -- a JSON dict, per environment, Y or N IS_ACTIVE
The parent pipeline read the whole table into a grid variable, looped, mapped each column onto a job variable, and called one generic child pipeline per row. The child had no idea which table it was loading. It only knew the values it had been handed.
The EXTRACTION_METHOD column is the one I would point at if someone asked what made this design good. It branched between the lake, the iSeries platform, file loads and warehouse to warehouse copies, all inside the same job. Four quite different extraction mechanics, one place to maintain them.
The governance schema held a rules table with a similar shape. One row per rule, and the rules were policy expressed as data rather than as code.
RULE_ID DOMAIN -- so a domain could be run or excluded independently SCHEMA_NAME / OBJECT_NAME -- what the rule applies to SOURCE_KEY_COLUMN_1..8 -- up to eight key columns for the join TRIGGER_TABLE + join columns ACTION_QUERY -- the statement that does the deletion or redaction RETENTION_PERIOD / RETENTION_UNIT APPROVER, START_DATE, END_DATE, IS_ACTIVE
A single job selected the active rules into a grid variable and iterated, exactly as ingestion did. One product line ran on its own schedule and was excluded from the standard domain run by a filter on DOMAIN.
What I liked about this: the approver column meant the business signed off a row in a table, and that row was the thing that ran. There was no translation step between the policy and the implementation where someone could quietly get it wrong. What I did not like about it is further down.
Three more tables did the unglamorous work.
And on top of all that, Snowflake's own INFORMATION_SCHEMA, which I ended up leaning on for the coverage analysis described below.
None of these were exotic. That is rather the point. Every one of them is a normal consequence of moving behaviour out of code and into rows.
The one that cost the most compute. A column mapping was missing in a merge component, so the value that should have been written back into CDC_LAST_VALUE never arrived. The watermark stayed null. A null watermark fell back to the epoch. Every run therefore extracted the entire dataset from the beginning of time, wrote it all through the layers, and then failed to update the watermark again.
The job reported success every single day. There was no failed run, no alert, no error in the log. The only visible symptom was a duration that had crept up, on a platform where plenty of jobs are slow for legitimate reasons. It sat there until somebody looked at the run times properly and asked why a delta load was moving that much data.
The lesson I want to keep. In a metadata-driven framework the dangerous failures are not the ones that throw. They are the ones where the framework does exactly what the metadata told it to do, and the metadata was wrong. A green run is not evidence of anything.
The incremental filter looked at the source row effective date. That catches changes made in the source system. It does not catch a change to a Type 2 dimension that the fact joins to.
So a fact row whose source timestamp had not moved never re-entered the merge, and never picked up the new surrogate key when the dimension version behind it expired. The fact carried on pointing at a dimension row that had been closed off weeks earlier. Every report built on that join was quietly wrong, and again, nothing failed.
This is a design gap rather than a bug. The watermark was doing precisely what it was configured to do. Nobody had asked the question "what else, other than the source, can invalidate this row".
Surrogate keys were generated by hashing a set of business columns. That is a good pattern, right up until the two places that build the hash stop agreeing with each other.
The concrete instance I spent time on: two status code columns had been added to one side of the hash for a household claims dimension and not the other, and a source reference column had not been renamed consistently in the calculator component. The joins stopped matching, and every unmatched row took the default unknown-member key of minus one. No error. A fact table full of minus ones, which is exactly the kind of thing that looks like a data quality issue at the source until you trace it.
The same class of failure comes from an inconsistent TRIM or UPPER on one side of the calculation, or from a genuinely subtle one: including the CDC operation column in the hash. Do that and a delete followed by a re-insert produces a different key for the same business entity, so the load stops being idempotent and reruns start creating duplicates.
This is the failure mode that is hardest to see, because there is nothing to look at. If a table was never inserted into the retention rules, no retention ran against it. No rule, no job, no alert, no evidence. Silence is indistinguishable from compliance.
I ended up writing a notebook against production that did two joins in opposite directions.
-- modelled tables with no retention rule at all
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN governance.retention_rules_master r
ON r.schema_name = t.table_schema
AND r.object_name = t.table_name
AND r.is_active = TRUE
WHERE t.table_schema IN ( ...modelled schemas... )
AND r.rule_id IS NULL;
-- rules pointing at objects that no longer exist
SELECT r.rule_id, r.schema_name, r.object_name
FROM governance.retention_rules_master r
LEFT JOIN information_schema.tables t
ON t.table_schema = r.schema_name
AND t.table_name = r.object_name
WHERE r.is_active = TRUE
AND t.table_name IS NULL;
Both sides returned rows. That query should not have been a one-off investigation. It should have been a scheduled test that fails the build, and if I set this pattern up again from scratch it will be.
The pre-production environment cloned the modelled layer down from production every day, but rebuilt the transformation layer from source. So the data composition and the metadata state in pre-production were never quite the same as production.
The practical consequence is that data-sensitive bugs do not reproduce reliably below production. You can have a fix that demonstrably works in pre-production and still does nothing in production, because the row that triggers the problem only exists in one of them. That eats an enormous amount of time and it is very hard to explain to anyone outside the team why "it works in pre" is not the reassurance it sounds like.
Junk dimensions used auto-increment surrogate keys while the facts joined to them on hashes. That combination means a junk dimension and its fact are welded together: you cannot truncate and reload one without invalidating every foreign key reference in the other. They have to move as a pair, every time.
This is the single strongest argument I have for deterministic, hash-based keys everywhere. If the key is a function of the business columns, reloading one table in isolation is safe, because the key it regenerates is the key it had before. The cascade disappears. It is also most of the argument for moving that platform onto dbt, where that convention is the default rather than something you have to defend in review.
This one is structural and it is the root of several of the others. Schema changes went through migrations, source control and a pull request. Metadata changes were UPDATE statements against a table.
| A DDL change | A metadata change | |
|---|---|---|
| Review | Pull request | Whoever had access |
| History | Full, in the repository | The current row value |
| Diff | Visible before merge | None |
| Rollback | A revert | Remember the old value |
| Blast radius | One object | Every table the row governs |
So the thing that determined the behaviour of the platform was the one thing with no version history, no diff and no review gate. A single-character change to a where clause in a control row could alter what a production job loaded that night, and there would be no record that anything had happened.
Six of these seven are the same failure wearing different clothes. Moving behaviour out of code and into data does not remove the complexity, it moves it somewhere that has none of the tooling we normally rely on. Code has tests, reviews, diffs, type checks and a compiler that shouts. Rows in a table have none of that by default. You have to build every one of those safeguards yourself, and the pattern is seductive precisely because it works so well before you have built any of them.
The pattern is still right. Onboarding a table with an insert instead of a release is genuinely worth having, and at the volume of source objects we were dealing with there was no realistic alternative. But the honest version of the trade is this: you exchange code you have to write for data you have to govern, and governing data is not free.
One generic job, a control table, and onboarding becomes an insert. At scale there is no sensible alternative.
The expensive failures all reported success. Look at durations, row counts and key distributions, not at run status.
Source timestamps do not know about dimension changes. Write down what invalidates a row before trusting the filter.
If the behaviour lives in a table, then the table needs reviews, diffs and history. Otherwise the most important thing you own is the least controlled.