"What If a Source Adds a Column?"
Concepts covered: Schema Evolution
One new field, every hop
Adding a column is the mildest schema change a source can make, which is exactly why interviewers open with it. The field itself rarely breaks anything. What breaks is each consumer's assumption about how it locates columns, and a pipeline holds several consumers with different assumptions. A complete answer walks the field from producer to dashboard and states, for every component, how it binds to the schema and what it does with a field it has never seen.
The running example for this lesson is a marketplace whose checkout service emits an orders event with 4 fields: order_id, customer_id, amount_cents, created_at. To launch in Japan, the payments team proposes a fifth field, currency, with a default of USD. The diagram below shows the route that event takes.
- checkout_service (source, Checkout service)
- orders_stream (queue, Event stream, 7-day retention)
- raw_orders (bronze, Raw table, JSON payload)
- ingest_gate (quality gate, Closed field list); on error: route to dead-letter table
- dead_letter (storage, Dead-letter table)
- stg_orders (silver, Typed staging table); retries: 3; backoff: exponential, 1 min base
- fct_revenue (gold, Revenue fact); freshness: hourly
- finance_dashboard (consumer, Finance dashboard)
- partner_export (consumer, Nightly CSV export)
- checkout_service -> orders_stream
- orders_stream -> raw_orders
- raw_orders -> ingest_gate
- ingest_gate -> dead_letter
- ingest_gate -> stg_orders
- stg_orders -> fct_revenue
- fct_revenue -> finance_dashboard
- stg_orders -> partner_export
- Sources: checkout_service, orders_stream
- Raw: raw_orders, ingest_gate, dead_letter
- Curated: stg_orders, fct_revenue
- Serving: finance_dashboard, partner_export
Loud failures and silent ones
| Hop | Binds by | Reaction to currency | Who notices |
|---|---|---|---|
| Raw table with a JSON payload column | Nothing; it stores the payload | Kept inside the payload, unread | Nobody |
| Gate with a closed field list | Name, exact set | Sends every new event to the dead-letter table | On-call, within minutes |
| Staging load using `SELECT *` | Position | Column count mismatch, load fails | On-call, at the next run |
| Staging load with explicit columns | Name | Field dropped without an error | Nobody, until someone asks for it |
| Partner CSV export | Position | Every column after the new one shifts by 1 | The partner, weeks later |
| Revenue fact `sum(amount_cents)` | Meaning | Adds yen to US cents | Finance, at month-end close |
The table sorts into 2 groups. The gate and the SELECT * load fail loudly, which is costly at 3 a.m. but cheap overall, because nothing wrong reaches a consumer. The explicit-column load keeps running, and so do the CSV export and the revenue fact, all producing output that looks valid. Interviewers give most of the credit for spotting those silent failures.
INSERT INTO staging.stg_orders
SELECT
*
FROM raw.orders_parsed
WHERE event_date = DATE '2026-10-01'raw.orders_parsed now returns 5 columns while staging.stg_orders holds 4, so the engine rejects the insert. Adding the column to the target as well only moves the risk: INSERT ... SELECT * matches columns by position, so the load succeeds only while both sides list them in the same order. If the producer places the new field in the middle of its column list, a loud failure becomes a silent swap of values.
That is what happens to the partner export once staging adopts the field. The checkout team put currency third, next to the amount it describes; staging takes the column in that order, the export's SELECT * writes it third, and the partner's parser still reads 4 positions.
The partner's loader may reject JPY as a non-numeric amount, which is the good outcome. Had the new column been numeric, such as a tax amount in cents, every value would parse and every value would sit under the wrong header.
Additive in shape, breaking in meaning
Every schema check in this pipeline approves currency: a new field with a default is the most compatible change a schema can make. Then the first Japanese order arrives as o-1002 with amount_cents of 125000 and currency of JPY. Yen has no minor unit, so that number means 125,000 yen, and the revenue fact, which sums amount_cents as US cents, books it as $1,250.00. The change added a column and, in the same release, redefined an existing one, from US cents to minor units of whatever currency says. No compatibility rule inspects meaning, so this hop is where a strong answer slows down.
A policy per layer
The answer interviewers want ends in a policy, and the policy differs by layer, because each layer promises something different to the people downstream of it.
| Layer | Policy for unknown fields | Why |
|---|---|---|
| Raw | Accept and store everything | Raw is the replay source; a field dropped there is gone for good |
| Staging | Evolve by name from an allowlist; alert on fields not yet on it | New data stays visible without changing typed tables unreviewed |
| Curated facts | Change only through a reviewed migration | Facts carry business definitions that a new field can quietly alter |
| Partner exports | Frozen explicit column list, versioned | An external reader cannot redeploy on your schedule |
About this lesson
This section is part of the Schema Evolution: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.