Schema change: the break you can see coming

Freshness needs a promise. Volume needs a history. Uniqueness needs a key you agree on. Schema needs none of those: the warehouse already publishes the column list of every table it holds, and the check is a comparison of two lists. There is nothing to tune, no band to widen, no percentile to argue about. Snapshot the list nightly and the monitor is finished.

What makes it interesting is the other end. A freshness page says "late", which everybody understands. A schema page says "the shape changed", and what happens next depends entirely on which change it was. One class of change is harmless, one breaks loudly, and one passes every check you own while quietly corrupting numbers downstream.

A message that says "schema changed on orders_daily" sends the reader to go and look, which costs them 20 minutes before they know whether it matters. Three facts turn it into a decision:

• What changed, by name. Not a count. "dropped ship_region; added promo_code" is the whole difference between a page that gets triaged and a page that gets starred for later.
• Which class it belongs to. Addition only, removal, or a type change. The class is what decides the route, and the diff query can label it without any extra work.
• Who reads the column. This is the lineage question and session 7 answers it properly. Until then, a hand-maintained list of the five columns that appear in dashboard filters gets you most of the value for an afternoon of work.

A new column appears. Every existing query names its columns explicitly, so every existing query is unaffected. This is the class that a schema monitor is accused of being noisy about, and the accusation is fair right up to the moment somebody deploys a model that reads the table with select * and gains a column nobody validated.

An addition is still worth knowing about for a reason that has nothing to do with breakage: it is evidence that an upstream team shipped something. A new column is the only free notification you will ever get that a producer changed their mind about what this table contains. Route it to a channel, not to a person, and read it once a week.

A column is dropped, or renamed, which the warehouse reports as one drop plus one addition. Or a type narrows: bigint to int, numeric(18,4) to numeric(10,2), a wide varchar to a short one. Anything that can no longer hold the values that used to fit.

These fail fast and they fail visibly. Builds error, dashboards show a column-not-found message, and somebody complains within the hour. The monitor buys you the head start between the load finishing and the first human noticing, which on a nightly table is often the difference between fixing it before 09:00 and explaining it at 09:15.

Renames deserve one specific warning. A set difference reports a rename as dropped ship_region; added shipping_region, and a tired reader at 07:00 sees "dropped" and starts an incident. Worth teaching the message to say "possible rename" when a drop and an addition arrive on the same night, which costs one line of logic and saves a false escalation.

The class that costs real money. Nothing errors, no query fails, and the column list is unchanged, so a column-set diff is blind to all of it.

• A type widens. An integer becomes a numeric. Every downstream sum still runs, and results that used to be whole numbers now carry fractions that a rounding step in a report may or may not handle the same way as before.
• A timestamp loses or gains a timezone. timestamp to timestamptz is a widening in the type system and a change of meaning in the data. Every date-bucketed count shifts by the offset, which for a grocery delivery company means orders moving between days.
• A code column starts carrying a new value. status gains held. Every case when status in ('placed','shipped','cancelled') in the estate now sends those rows to the else branch or drops them entirely, and the totals get quietly smaller.

The first two are catchable with a second diff on data_type, which is the same query shape and the same free metadata. The third is not a schema change at all in the strict sense: the column list and the types are identical. It is a distribution change, and it belongs to the segment-share statistic in session 5.

Three more shapes worth storing in the snapshot, all of them free and all of them invisible to a column-set diff.

Nullability is the one to store first, because it is a single extra column in the snapshot and it is the leading indicator for a null-rate incident of exactly the kind session 5 measures. A column that became nullable on night 20 and jumped to 11 percent null on night 25 is not a mystery. It is a change you already recorded.

Night 23 drops ship_region and adds promo_code. Night 24 puts it back. The diff on night 24 reads dropped promo_code; added ship_region, which is a true statement about a real difference between two consecutive nights, and it arrives as a page on a night when nothing was wrong.

There is no clever query that fixes this, because the monitor is not mistaken. It is answering the question it was asked. What has to change is the question, and there are three defensible answers:

• Compare against last night. A revert pages. You accept one extra page per incident in exchange for a monitor with no state to maintain and no concept of "correct". This is what the bench runs, and it is why the top rung carries a false page it cannot remove.
• Compare against a declared column set. A revert is silence, because the shape now matches the declaration again. The cost is that somebody has to maintain the declaration, and a declaration that drifts from reality is worse than no declaration.
• Compare against last night, then suppress a diff that exactly inverts the previous one. Cheap, catches the common case, and quietly hides a genuine flap where a column appears and disappears nightly. If you take this route, count the suppressions and look at the count weekly.

Pick one before the first page arrives. The reason to decide in advance is that the decision made at 07:20 on the morning of a revert is always the same one, which is to mute the monitor.

information_schema.columns is in every warehouse that speaks SQL, and it carries more than the names: data_type, is_nullable, character_maximum_length, numeric_precision and ordinal_position. Reading it is free and instant, because it is catalogue metadata rather than data.

The important part is that a live read is not enough. information_schema tells you what the table looks like now, and a diff needs what it looked like last night, which nothing stores for you. So the monitor has two halves: a snapshot job that appends the current column set to the metrics table every night, and a diff query that reads two nights out of it. The snapshot is the half that has to run reliably, because a missing snapshot makes tomorrow's diff compare against the wrong night and report a change that never happened.

Once the snapshot carries data_type and is_nullable, the second diff is a full outer join on column name rather than two set differences, and it catches the first two members of the silent class.

Two practical notes. First, warehouses report type names inconsistently across versions, so numeric can become decimal with no underlying change at all. Normalise the type string before comparing, or your first week is all false pages. Second, distinguish widening from narrowing in the message: int to bigint is almost always safe and worth a channel note, while bigint to int can truncate and belongs on the rota.

Precision is the one people skip and regret. numeric(18,4) to numeric(18,2) is neither an addition, a removal, nor a type change by name. It is a silent rounding of every amount in the table, and it shows up as a two-cent difference in a reconciliation three weeks later.

On a table you build, a schema change is something you did. It is in a pull request, it went through review, and the diff mostly tells you what you already know. The monitor is still worth having, because the deploy that changed the shape was not always the deploy you were watching, but its value is modest.

On a table another team produces, a schema change is an event you had no part in. There was no ticket on your board, no review you were invited to, and the first signal available to you is the shape of the data on the morning after. That is exactly the gap a metadata monitor exists to fill, and it is why the schema check earns its slot on ingested and third-party tables far more than on your own marts.

Build-time schema enforcement on tables you produce is a different mechanism with different tooling, and it belongs to the transform project's own contracts. learn-analytics-engineering-with-phoebe covers that side.