When should a batch load auto-evolve the target table's DDL instead of failing loudly?

## The decision, stated plainly Every schema change that arrives at your loader takes one of two branches: the pipeline applies it and keeps running, or the pipeline stops and a human decides. Getting this split right is what separates an ingestion platform that people trust from one that either wakes someone up nightly or corrupts things quietly. The two questions that decide the branch are **reversibility** and **blast radius**. ## Axis one: is the change reversible? **Reversible (candidate for automation).** Adding a nullable column: nothing existing changes, and if the column turns out to be junk you drop it later with no data loss. Widening a type — integer to bigint, numeric to a wider precision, a typed column to string in a raw zone: every existing value survives the widening, so the operation is safe to apply and the information content only grows. **Not reversible (must halt).** Dropping a column destroys data unless you have the source to reload from. Narrowing a type — string back to integer, timestamp to date — fails on the first non-conforming value or truncates silently. A rename is worse than either, because your tooling cannot tell it apart from a drop plus an add and will therefore guess, and a wrong guess either orphans history or grafts one column's history onto a different column's meaning. A useful rule: **never auto-drop and never auto-narrow.** If your evolution logic has a code path that removes a column or reduces a type's range, that path is a future incident. ## Axis two: what is downstream? A raw landing table that only the transformation layer reads has almost no blast radius. Adding a column there affects nothing until someone chooses to project it. A published mart that fifty dashboards and a reverse-ETL sync read is a different object. Auto-evolving it means a column appeared in a shared asset with no review of its name, its semantics, its nullability or its ownership. Even when the mechanical change is safe, the governance is not, and the column will be permanent within a week. This is why the mature architecture is two-zone: **permissive at landing, strict at publish**. Automation lives in the landing zone; the publish boundary is an explicit projection that a human changes deliberately. ## Evolve *and* alert The framing "auto-evolve or fail loudly" is a false binary. Applying a safe change and telling nobody is how a schema accumulates twelve columns nobody can explain. The working pattern is: 1. Detect the difference between the observed source shape and the recorded expected shape. 2. Classify it: additive / widening / destructive / ambiguous. 3. Apply the safe classes, record the change with a timestamp and the run that caused it, and emit a notification to a channel the data team reads. 4. Halt the load for the unsafe classes, with a message naming the exact column and the exact difference, and route the batch somewhere it can be inspected rather than half-loaded. Step 3's notification is what makes automation acceptable — the change is applied but not invisible. ## What makes automation go wrong **Type ping-pong.** A source column that alternates between integer-looking and string-looking values across batches will, under naive widening logic, cause repeated ALTERs. Widen once, in one direction, and never widen back. **Column explosion.** A buggy producer emitting keys derived from data — a per-tenant or per-day field name — will grow your table without bound. Cap the number of columns an automatic evolution may add in a single run and halt above the threshold; that ceiling is a cheap circuit breaker. **Silent semantic change.** The riskiest change is invisible to any structural check: the column keeps its name and its type, and its *meaning* changes — `amount` moves from gross to net, `status` gains a new enum value that downstream filters do not handle. No DDL evolution logic will catch this, which is why value-level quality checks sit alongside schema checks rather than being replaced by them. **Evolving a table under a running consumer.** Adding a column to a table an external tool introspects can force a refresh, break a `SELECT *`-based view, or change a downstream model's output shape. Know which of your targets have that property. ## Making automatic changes recoverable Automation is only defensible if you can undo it. Two things make that true: an **immutable raw layer** you can rebuild the target from, and a **schema-change log** that records what was applied when. With both, an evolution that turns out to be wrong is a rebuild, not an archaeology exercise. Without them, "the pipeline changed it" is where the investigation ends. ## How to answer this in an interview Do not answer with a policy; answer with a decision procedure. Name the classification, name the two axes, state the never-rules (never auto-drop, never auto-narrow, never auto-evolve a published contract), and describe the notification that makes the automated branch safe. Then say what you keep so you can roll it back.