A source dropped a column but your nightly load still succeeds with NULLs — how do you catch that?
answer
- nothing failed, which is the problem
- the source knows its own column list
- write down what you saw last night
- a column that goes all-NULL overnight
- your SELECT decides what you can see
basics
~20 sCompare the source's observed column list against the shape recorded from the last accepted run and fail on a missing column, and monitor per-column null rates so a column that goes from 2 percent null to 100 percent raises an alert on the first batch.
solid answer
~50 sTwo independent detectors, because each catches what the other misses. **Structural**: read the source's own metadata — an information-schema query, a file header, an API response shape — persist it after every accepted run, and diff it at the start of the next one. A column present yesterday and absent today is a halt-and-alert condition, regardless of what the loaded rows look like. **Statistical**: track null rate, distinct count and row count per column per batch. A column that jumps from a small null rate to 100 percent is a dropped or renamed field even when the structural check was skipped or the source metadata lied. The design lever behind this is the extract's projection: an explicit column list turns a drop into a loud source-query error, while `SELECT *` hides drops and surfaces additions. Knowing which trade you have made tells you which detector you cannot go without.
code
sql · 9 lines-- structural check: today's source columns vs the recorded shape
select expected.column_name
from expected_schema expected
left join information_schema.columns actual
on actual.table_name = expected.table_name
and actual.column_name = expected.column_name
where expected.table_name = 'orders'
and actual.column_name is null;
-- any row returned = a column disappeared, halt the rungo deeper
Know that a dropped source column often does not fail the load at all — the field simply arrives as NULL — so success in the job log is not evidence the data is complete.
Explain both detectors and what each misses: a metadata diff against a stored shape catches drops before loading, while per-column null-rate and distinct-count monitoring catches what metadata does not reveal.
Show that you understand the projection trade — explicit lists make drops loud and additions invisible, wildcards do the reverse — and that detection ends with quantifying the affected window and telling downstream consumers.
Own which sources get a declared required-column list and who is paged when it trips, plus the communication protocol for telling business consumers that a published number was wrong for a defined period.
## Why the load succeeded at all A dropped column produces a silent success under a very common combination: a wildcard or loosely-mapped extract into a permissive target, or an explicit target insert where the missing field simply defaults to NULL. Nothing errors because nothing was asked to error — you never told the pipeline that this column was required. The damage compounds because it is invisible. Yesterday's rows have values, today's have NULLs, and every downstream filter on that column starts excluding today's data. Reports do not break; they shrink. The first complaint arrives from a business user days later, and by then several partitions are affected. ## The projection trade you already made The single most consequential choice here is how the extract selects columns, and it is a genuine trade with no free option. **Explicit column list.** `select order_id, customer_id, status from orders` fails immediately when `status` is dropped — the source database raises "column does not exist" and the load never starts. Drops become loud. But new columns are invisible: the query never asks for them, so they land nowhere and nobody is told. **Wildcard.** `select *` carries new columns through where a permissive landing zone can absorb them, so additions are at least discoverable. But drops go quiet: the result set simply has one fewer column and the load fills the target's column with NULL. Neither is wrong. What is wrong is not knowing which one you chose, because it determines which drift class you are blind to and therefore which detector is mandatory rather than optional. ## Detector one: structural comparison Read the source's declared shape and compare it to a stored copy. For a database source that is a catalogue query returning column names and types. For a file drop it is the header line, or the embedded schema in a self-describing format. For an API it is the set of keys observed across a sample of the response. Persist that shape after every accepted run — a small table or a JSON document keyed by source and run timestamp — and diff it before the next extract. Classify the diff into added, removed, retyped and suspected-rename, and route each class to its policy. Crucially this check runs on *metadata*, so it catches a drop even when the resulting rows would have loaded happily. The failure message should name the exact column and the exact difference. "Schema changed" wastes the on-call engineer's first ten minutes. ## Detector two: statistical shape checks Structural checks depend on metadata being available and truthful. Value-level checks depend on nothing but the landed rows, which is why they are the backstop. **Null rate per column per batch.** The strongest single signal. A column whose null rate is normally low and is suddenly 100 percent is either dropped, renamed, or broken upstream — all three want a human. Alert on the *change*, not on an absolute threshold, since some columns are legitimately mostly null. **Distinct-count collapse.** A column that normally carries hundreds of distinct values and now carries one is a strong signal of a default or a truncation, which structural comparison will not see because the column still exists with its type. **Row count against the trailing window.** Not specific to drops, but it catches the case where a filter on the dropped column silently emptied the extract. The useful property of statistical checks is that they also catch the change structural checks are constitutionally unable to see: the column that keeps its name and type and changes its meaning. ## Detector three: declare which columns are required The cheapest hardening of all is a per-source list of columns the pipeline considers mandatory, checked before the load. It is a fraction of a full contract, it lives entirely on your side, it needs no cooperation from the producer, and it converts the drop of any business-critical field into a deterministic pre-load failure with a clear message. Full contract negotiation with the producing team is a different and larger discipline; this is the defensive subset you can implement alone this afternoon. ## Recovering after you find it Detection is half the job. Once you know a column has been NULL for six nights: - Determine the first affected batch from the load timestamps and the schema-change history. - Decide whether the column was dropped, renamed, or is temporarily broken — a rename means the data still exists under a new name and history can be stitched; a drop means the values are gone from the source and cannot be reconstructed from it. - If you kept an immutable raw layer, check whether the value arrived and was lost at the publish step rather than at the source — that is a much happier finding. - Communicate the affected window to the consumers of the derived tables, because their numbers were wrong for that period and some of them acted on those numbers. That last step is what separates a senior answer from a tooling answer. The question in the room is usually not only "how would you detect it" but "how do you tell people their dashboard was wrong for a week".
- How does the choice between an explicit column list and SELECT * change which drift you can detect?An explicit list makes drops loud — the source query itself errors on a missing column — but makes additions invisible, since you never ask for them. A wildcard is the mirror image: additions flow through to a permissive landing zone, drops silently become NULLs. Whichever you choose, the blind side needs a dedicated detector.
- Once you discover the column has been NULL for six nights, what do you do beyond fixing the pipeline?Establish the first affected batch from load timestamps and the schema history, determine whether it was a drop or a rename — a rename means the values still exist and history can be stitched — check whether the raw layer captured them, then tell the consumers of every derived table which window was wrong. People acted on those numbers.
- Which detector catches a change that keeps the column name and type but alters its meaning?Only the value-level one. A structural diff sees an identical column and reports nothing. A distinct-count collapse, a shifted distribution or a null-rate change is the sole automated signal that gross became net or that a unit changed. That is why statistical checks sit beside schema checks rather than being replaced by them.
saying these in an interview costs you the question
- Relies on the load failing to reveal a dropped column
- Assumes SELECT * makes the pipeline immune to drift
- Only alerts on absolute null thresholds, never on change
- Says the source team would have told them
- Detects the drop but never quantifies the affected window