skip to content

How would you design batch ingestion for schema drift from source systems you don't control?

level: principalimportance: should knowfreq 40%

answer

  1. you cannot make them warn you
  2. forgiving at the door, strict at the exit
  3. raw stays untouched so you can rebuild
  4. detect, classify, route — as a real step
  5. not every source deserves the same rigour

basics

~20 s

Land permissively and immutably, publish strictly through explicit projections, and make drift detection a first-class pipeline step that classifies each change and routes it — auto-apply the safe ones with a notification, halt on the destructive ones, and tier sources by blast radius.

solid answer

~50 s

Three structural decisions carry most of the weight. First, **two zones**: a landing layer that accepts whatever arrives, stores it immutably with the batch identity, and never fails on a value; and a published layer built by explicit projection, where types and names are your decision rather than the producer's. Second, **drift detection as a pipeline stage**, not a side effect — record the observed source shape every run, diff it, classify additions, drops, retypes and suspected renames, and route each class to a policy that either applies it and notifies or halts and pages. Third, **tiering**: not every source deserves the same rigour, so rank them by what breaks downstream and spend the strict treatment there. Behind all three sits the property that makes mistakes survivable — an immutable raw layer means a wrong decision is a rebuild rather than an archaeology exercise.

code

yaml · 16 lines
yaml
sources:
  vendor_orders:
    tier: 1
    required_columns: [order_id, order_ts, amount, currency]
    landing: { types: wide, immutable: true, stamp: [batch_id, load_ts] }
    on_added_column:   evolve_and_notify
    on_widened_type:   evolve_and_notify
    on_dropped_column: halt_and_page
    on_narrowed_type:  halt_and_page
    on_suspected_rename: halt_and_page
    limits: { max_added_columns_per_run: 3, max_reject_fraction: 0.01 }
  partner_clickstream:
    tier: 3
    landing: { types: wide, immutable: true, stamp: [batch_id, load_ts] }
    on_added_column: evolve_silently
    on_dropped_column: notify

go deeper

for a junior

Understand the basic split: a landing area that accepts whatever the source sends, and a published area whose columns and types your team controls deliberately.

for a middle

Explain why the published layer is built by explicit projection, and describe the detect-classify-route stage that decides whether a given change is applied automatically or stops the run.

for a senior

Show the operational depth: provenance stamping so you can name the affected batch window, both structural and value-level detectors, monotonic widening, and reject handling that keeps the raw value.

for a principal

Own the tradeoffs — tiering sources by blast radius, the attention cost of a pipeline that never fails, circuit breakers against a misbehaving producer, and the protocol for telling consumers a published number was wrong for a defined window.

## The constraint that shapes everything You cannot make the producer tell you before they change. Maybe they are a different company, a vendor SaaS export, a legacy system with no owner, or simply a team with different priorities. Where you *can* negotiate a real agreement with a producing team, that is a different and stronger discipline with its own machinery. This answer is about the case where you cannot, and the whole design follows from it: **assume every batch may differ in shape from the last, and make that assumption cheap to be right about.** ## Zone one: permissive, immutable landing The landing layer's only job is to lose nothing. Concretely: - **Accept what arrives.** Wide types, nullable everything, unknown columns absorbed rather than rejected. A value that does not parse is landed as text, not coerced and not dropped. - **Stamp every row with its provenance** — batch identifier, source file or extract, load timestamp. Without this you cannot answer "which batches were affected", which is the first question in every incident. - **Keep it immutable.** Landed data is never updated in place. This is what makes every downstream decision reversible: if you type a column wrongly, or classify a rename wrongly, you rebuild the published layer from raw rather than trying to reconstruct lost values. The landing zone is deliberately not the thing anyone reports from. Its permissiveness is only safe because nothing downstream trusts it directly. ## Zone two: strict publish The published layer is where your names, your types and your semantics live. It is built by explicit projection from landing: you list the columns you take, you cast them deliberately, and you decide what happens to values that do not convert. This boundary is the entire point of the split. Drift in the source hits the landing zone and stops there. A new source column does not appear in a mart until someone decides it should. A retyped source column does not silently change a published column's type. The producer's changes stop being your consumers' problem automatically and become a decision with an owner. ## Drift detection as a stage, not an accident Drift should be discovered by a step that exists to discover it, running before the load: 1. **Observe** the source's declared shape — a catalogue query, a file header, the key set of an API response. 2. **Diff** it against the shape persisted after the last accepted run. 3. **Classify** each difference: added, dropped, widened, narrowed, suspected rename, arity change. 4. **Route** by class and by zone: apply-and-notify for additive and widening changes into landing; halt-and-page for destructive and ambiguous ones; never touch published tables automatically. 5. **Record** what was applied, when, and which run caused it. Add value-level checks after the load as the backstop for what structural comparison cannot see: null-rate change per column, distinct-count collapse, row count against the trailing window. Structural checks miss semantic change; statistical checks miss a column that was added and never populated. You want both. ## Tiering, because uniform rigour is a waste A principal-level answer resists applying the same policy everywhere. Rank sources by blast radius: **Tier one** feeds finance, regulatory or externally-visible numbers. Declared required-column lists, halt on any structural change, alert routed to a named on-call, backfill procedure written down. **Tier two** feeds internal analytics. Auto-evolve the safe classes, notify a channel, review weekly. **Tier three** is exploratory or low-consequence. Land it, monitor volume, do not page anyone at 3am for it. The cost of drift handling is mostly human attention, and attention spent on tier three is attention not available for tier one. ## Circuit breakers Automation against an uncontrolled source needs limits, because the producer can misbehave without malice: - Cap the number of columns automatic evolution may add per run; halt above it. One buggy producer emitting data-derived field names can otherwise add hundreds overnight. - Cap the fraction of rows that may be rejected before the batch is treated as failed rather than partially loaded. - Require monotonic type widening — never narrow back on a batch that happens to look clean. ## What you owe your consumers Drift will get through anyway, so the design has to include the aftermath. Keep a schema-change history that a human can read, so "when did this column start being NULL" is a query rather than an investigation. Be able to name the affected batch window from provenance columns. And have a stated protocol for telling downstream consumers that a published number was wrong for a defined period — the technical fix is usually the easy half. ## The tradeoff to state out loud Permissive landing buys availability at the cost of attention: the pipeline stops breaking, so nothing forces anyone to look. That is only acceptable when the notification path is real and someone actually reads it. A team that auto-evolves everything and routes alerts to a channel nobody watches has built a system that fails silently by design. Say this in the interview — it is the judgment the question is testing, and it is the reason "just make it never fail" is the wrong instinct.

  • What is the hidden cost of making the landing zone so permissive that the pipeline never fails?
    Attention. Failure is a forcing function; remove it and nothing compels anyone to look at a change. Permissive landing is only safe when the notification path is real and someone owns it. A team that auto-absorbs everything and routes alerts to an unwatched channel has designed a system that fails silently on purpose.
  • How do you decide which sources deserve the strictest drift treatment?
    By what breaks when they are wrong, not by how often they change. Sources feeding finance, regulatory or externally-visible numbers get declared required columns, halt-on-any-change and a named on-call. Exploratory sources get landed and monitored. Drift handling costs human attention, and spending it uniformly starves the cases that matter.
  • Why is an immutable raw layer the precondition for automating anything?
    Because it makes every automated decision reversible. If evolution logic types a column wrongly or misclassifies a rename, you rebuild the published layer from raw. Without it, an automatic ALTER or a coerced value is a one-way door, and the only recovery path is asking a source you do not control to resend history.
  • Which circuit breaker matters most against a producer that misbehaves without warning?
    A cap on how many columns automatic evolution may add in one run. A producer emitting field names derived from data — per tenant, per day — can otherwise add hundreds of columns overnight, and every one of them is permanent. The cap converts an unbounded reshaping into a single halt with an obvious message.

It is a customs hall: everything is let in and recorded at the border, and only the inspected, labelled goods reach the shop floor.

saying these in an interview costs you the question

  • Make landing permissive so the pipeline never fails, and stop there
  • Applies the same drift policy to every source regardless of consequence
  • Auto-evolves published tables that dashboards read
  • Has no immutable raw layer to rebuild from
  • Sends drift alerts to a channel with no owner

context

resolving links…