In a batch ingestion pipeline, what is schema drift and why does it break loads?

## What schema drift is Schema drift is the accumulated difference between the shape a source system produces **today** and the shape your ingestion job was written against. It is not a bug in your code and not a bug in theirs — it is the normal consequence of two systems owned by two teams changing on two schedules. An application team adds a column because a feature needed it; nobody involved in that change was thinking about the nightly extract that reads the table. The word "drift" is apt because the divergence is usually gradual and often invisible until something downstream is obviously wrong. ## The five shapes of change **Added column.** The most common and, superficially, the most harmless. If your extract names its columns explicitly, the new one simply never arrives — no error, no data. If your extract is `SELECT *` into a fixed target, the load fails because the target has no place to put it. **Dropped column.** The source stops producing a field. An explicit column list turns this into a loud failure at the source query ("column does not exist"). A wildcard extract into a permissive target usually just stops filling that column, and it quietly becomes all NULLs. **Renamed column.** The hardest to classify, because to any automated comparison a rename is indistinguishable from one drop plus one add. The data is still there; the lineage is broken. **Retyped column.** An integer becomes a string because someone started storing `"N/A"`; a date column becomes a timestamp; a numeric widens to accommodate a bigger range. Widening (integer to bigint, numeric to string) is survivable; narrowing loses data or fails on the first value that doesn't fit. **Reordered columns.** Irrelevant if you load by name, catastrophic if you load positionally — a header-less CSV or a fixed-width file that gained a column in the middle will write every subsequent value into the wrong target column. ## Loud failures and silent ones The instinct is to treat a failed load as the bad outcome. In practice a failed load is the *good* outcome: it is loud, it is timestamped, and someone investigates it that morning. The expensive failure is the one that succeeds. A new revenue column that never lands means a finance dashboard is under-reporting and no alert fires. A dropped `status` column loading as NULL means every downstream filter on `status = 'active'` returns nothing, and the first symptom is a business user asking why a number went to zero — weeks later, with the raw evidence already overwritten. This asymmetry is why most mature ingestion designs deliberately convert silent drift into loud drift: explicit projections, recorded expected schemas compared each run, per-column null-rate monitors, and row-shape assertions. ## Why batch ingestion is especially exposed A batch job runs on a schedule against whatever the source looks like at that moment. It has no notification channel from the source, no handshake, and typically no record of what the source looked like last time unless you built one. It also runs unattended at 3am, which is when the gap between "it broke" and "someone noticed" is longest. File-based sources make it worse: a CSV or JSON drop has no enforced schema at all, so the file that arrives tomorrow can differ from today's in any way the producer likes, including a header row that quietly changed. ## Where drift gets detected There are three practical detection points, and good pipelines use more than one. **At extract time**, by reading the source's own metadata (an information-schema query, a file header, an API's declared response shape) and diffing it against a stored copy from the previous successful run. This is the only point that catches added columns you are not selecting. **At load time**, when a cast fails or the target rejects an unknown field. Cheap, but it only catches the drift that happens to be incompatible with the target. **After load**, through data-quality checks on the landed rows: null rates per column, distinct-value counts, row counts against the previous run. This is the safety net for drift the first two missed. ## What "handling" drift actually means Handling is not the same as tolerating. A pipeline that accepts anything and lands it has moved the problem downstream, not solved it. The working definition of a well-handled pipeline is: **every schema change is either applied deliberately or surfaced to a human quickly, and none of them lose data quietly.** Which of those two branches a given change takes — auto-evolve the target, or halt and alert — is the real design decision, and it depends on whether the change is additive or destructive and on how much blast radius sits downstream of the table.