Detecting schema drift before it breaks your pipeline

Schema drift gets discussed as one problem. It's four, and they have wildly different blast radii. Ranked by how long they survive undetected — which is the ranking that matters — from least to most dangerous.

1. A field disappears

The safest kind, because almost everything downstream notices immediately. Your loader errors, or the column goes all-NULL and a not_null test fires on the next run. You find out within a day, someone's annoyed, it gets fixed.

The exception is a field nothing has ever asserted on. If order.referrerquietly stops arriving and no test covers it, it becomes NULL forever and you discover it when someone asks why attribution broke last quarter. Cheap insurance: assert presence on every field you actually use, even the boring ones.

2. A new field appears

Usually harmless, occasionally the most important signal you'll get all month. A new field means the upstream system changed, and the field itself is often less interesting than what it implies. order.fulfillment_channel showing up means someone shipped a feature, and the existing rows now mean something subtly different from the ones after it.

Detection is easy — compare the field list against last time. The mistake is treating it as noise and auto-accepting. New fields deserve a human glance precisely because they're evidence of upstream change you weren't told about.

3. A type changes

Now it gets bad. price_cents starts arriving as "2500" instead of 2500, because someone upstream switched serialisation libraries.

Depending on your loader, three things can happen. It errors — best case, you find out immediately. It widens the column to a string, and every numeric aggregation downstream either fails loudly or, worse, silently coerces. Or it drops the values it can't parse, and your totals are quietly wrong by however many rows were affected.

The nastiest version is partial. Not every value changes type — just the ones from one client, or one code path, or one region. Now 4% of your rows are strings, the column still types as numeric because the sample missed them, and your sums are off by an amount too small to notice and too large to ignore once someone does.

Detecting this needs per-value type observation, not just declared column types. A column typed VARCHAR tells you nothing; the distribution of what's in it — 96% parse as numbers, 4% don't — is the actual signal.

4. Presence or distribution shifts

The dangerous one. Nothing errors. Nothing changes type. The field is still there, still the right type, and the meaning has moved.

• discount_code goes from 23% present to 71% present. A promo launched, or a default got set, and every historical comparison is now apples to oranges.
• status gains a fifth value, "partially_refunded". Your CASE statements have no branch for it, so those orders silently fall into ELSE.
• line_items mean length drops from 2.7 to 1.1 because a bundling feature changed how items are grouped. Per-order revenue is unchanged; per-item metrics moved 60%.

These survive for weeks. They produce plausible numbers — slightly different, never impossible — and by the time someone notices, the wrong figures are in three decks and a board update.

What actually catches the last two

Column-type comparison catches kinds 1 and 2, which is why "we check our schema" gives false comfort. Kinds 3 and 4 need a statistical baseline: not what the columns are, but what the values looked like.

The properties worth snapshotting per field:

• Presence rate, against the right denominator (see below)
• Observed type mix — the percentages, not just the winner
• Distinct count, and the value set when cardinality is low
• Numeric min / max / median, which move before means do
• For arrays: mean length and empty rate

Store that as a file next to the pipeline, versioned. On each run, produce a fresh one and diff. That's the whole technique — it's not sophisticated, it just requires having decided to do it before the incident rather than after.

The denominator trap

One detail that makes array-heavy data lie to you. "Present in 100% of records" means two different things for order.id and for line_items[].sku.

For a field inside an array, the natural denominator is elements, not records. A SKU present on every line item is 100% present by element count — while 40% of orders have no line items at all and therefore no SKU. Both statements are true and they support opposite conclusions.

If your drift detection collapses those into one number, array fields will look stable through changes that matter enormously, and unstable through changes that don't. Track the container's population rate separately from the element field's presence.

What to alert on

Alert fatigue kills drift detection faster than not having it, because a channel everyone mutes is worse than no channel. A split that survives contact with reality:

• Page someone: a field you assert on disappeared; a type flipped on a field used in arithmetic; presence on a required field dropped below its check threshold.
• Open a ticket: new field appeared; a low-cardinality field gained a value; presence moved more than ~10 points.
• Log it: distinct counts drifting within a band, numeric ranges widening slightly, mean array length moving a little.

The middle tier is where the value is. It's too slow for a page and too important for a log, and it's exactly where the silent report-corrupting changes live.

Where to put the check

At the ingest boundary, before transformation. Drift is an upstream event, and detecting it after your transformations have run means diagnosing it through several layers of your own logic. Snapshot the raw payload's profile, diff against the last one, and let the transformation layer assume the shape it was built for.