Home / Data Platform / Lakehouse

Data PlatformRunbookIntermediate

Sev1: upstream schema change broke the production loads — schema drift triage and recovery

Recover when a source system renames, retypes or drops columns and pipelines fail or, worse, load nulls silently. Detect the change, quarantine bad batches, adapt the load safely and put schema checks and contracts in place.

Deepak Bhardwaj3 min read

Applies toDelta Lake / Databricks, Spark, Azure Data Factory, dbt, Snowflake, any batch or CDC ingestion

Two kinds of failure

Loud Silent
AnalysisException: cannot resolve column, type cast errors, A schema mismatch detected when writing to the Delta table Renamed column arrives as a new column; old column becomes NULL for every new row; KPIs drop to zero

Silent ones are worse — check null rates, not just job status.

1. Find what changed

Compare the incoming schema with the target:

incoming = spark.read.format("parquet").load("abfss://raw@stdata.dfs.core.windows.net/crm/customers/2026/10/25/")
target = spark.table("silver.crm_customers")
inc = {f.name: f.dataType.simpleString() for f in incoming.schema}
tgt = {f.name: f.dataType.simpleString() for f in target.schema}
print("new:", set(inc) - set(tgt))
print("missing:", set(tgt) - set(inc))
print("type changed:", {c: (tgt[c], inc[c]) for c in set(inc) & set(tgt) if inc[c] != tgt[c]})

Check null rates on today's load vs. yesterday's:

SELECT load_date, COUNT(*) rows, AVG(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS email_null_rate
FROM silver.crm_customers WHERE load_date >= current_date() - 3 GROUP BY load_date ORDER BY load_date;

2. Stop and quarantine

  • Pause downstream jobs that would publish bad numbers (gold tables, extracts, dashboards refresh).
  • If a bad batch already landed, roll it back. Delta:
DESCRIBE HISTORY silver.crm_customers LIMIT 5;
RESTORE TABLE silver.crm_customers TO VERSION AS OF 412;

Snowflake: clone BEFORE (STATEMENT => ...) and swap. Keep the raw files — they're your replay source.

3. Adapt the load

Change Safe handling
New column Add to target (ALTER TABLE ... ADD COLUMNS) or allow mergeSchema for that write
Renamed column Map explicitly in the transform: coalesce(new_name, old_name) AS old_name until consumers move
Type widened (int→bigint) Delta supports some widening (type widening feature) — otherwise cast in the transform
Type narrowed / changed meaning Treat as a new column; involve the source owner
Column dropped Keep the target column, fill NULL, flag consumers; don't drop downstream automatically 
(incoming
  .withColumnRenamed("email_address", "email")
  .write.format("delta").mode("append")
  .option("mergeSchema", "true")       # only when you've decided new columns are OK
  .saveAsTable("silver.crm_customers"))

Caution

Don't turn on mergeSchema or autoMerge globally to make the error go away. It hides renames as new columns — the silent failure above.

4. Replay

Re-run the affected loads from raw for the impacted dates, in order, with idempotent writes (MERGE or partition overwrite). Reconcile row counts and key metrics with the source.

Prevent it next time

  • Schema checks at ingestion: expected columns and types; fail fast or route to a quarantine table.
  • Data quality checks on null rates and row counts (dbt tests, Great Expectations, DLT expectations).
  • A data contract with the source team: schema, change notice period, and a channel to announce changes.
  • Auto Loader with cloudFiles.schemaEvolutionMode = rescue keeps unexpected data in _rescued_data instead of losing it.

Related fixes