Upstream changed what a column means and nothing broke, we just reported wrong numbers for a week

Adding the layer above tests, because tests only catch what you predicted: monitor the outputs for shape, not just the inputs for validity.

The things worth alerting on, in rough order of how often they catch something:

Row counts per day, per important table, against a rolling baseline. A twenty percent move should tell somebody.

Distribution of key categorical columns. If a status field is normally sixty percent one value and it becomes thirty, that is exactly your incident and it is visible in a chart nobody had.

Null rates per column. A field that silently stops being populated is extremely common and produces no error anywhere.

The business metric itself, day over day. Revenue, orders, signups. This is the last line of defence and it is what your finance colleague was doing manually.

The reason this catches things tests do not: you are asking has anything changed rather than is this specific thing wrong. Most silent data incidents are a distribution shift, not a validation failure.

It does not need to be sophisticated. A daily query writing counts and distributions into a table, and a simple threshold alert, catches a remarkable amount. The people with elaborate anomaly detection mostly caught the same things the simple version would have.

You cannot assert on everything, and you do not need to. A small number of test shapes catch the overwhelming majority of this, and yours would have been caught by the first one.

Accepted values. For every low-cardinality column you branch on, assert the set of values. A new value fails the test loudly instead of falling into a default bucket. This is the single highest-value data test there is and it is two lines.

The general principle behind it: never let a default silently absorb the unknown. Your case statement should either fail or route unknown values to something visibly labelled unknown that somebody monitors. A default that means both no and we have never seen this is where the whole incident lived.

Not null and uniqueness on keys.

Relationships: every foreign key resolves. Catches upstream deletions.

Row count within a range of recent history. Catches partial loads.

Freshness. The newest row is not older than expected. Catches a silently stopped feed, which is the other version of your problem.

Five test types, applied to your handful of important models, and you have covered most of what actually goes wrong. Most transformation tools have all of these built in and people configure none of them.

The organisational half, which is the actual root cause and the only permanent fix: the team that owns the source did not know you depended on that column's meaning.

From their side, adding a new status value is a completely ordinary product change. It broke you because your dependency was invisible to them.

What helps:

Tell them the dependency exists. Simply knowing that finance reporting reads that field changes how people behave. Most upstream teams are happy to give notice and cannot give notice about something they do not know about.

Ask to be on the release notes or the channel where schema changes get discussed. Low effort, high return.

Write down the assumptions in the transformation itself. A comment saying which values are expected and what happens to others is the thing the next person needs, and it is also what makes a review catch this.

Formalise it if the relationship supports it: an agreed contract on the fields you depend on, with a process for changing them. Worth it for a small number of critical feeds and overkill for everything.

And the incident habit: write this one up. Eight days of wrong numbers is exactly the kind of thing that gets forgotten in a fortnight, and the write-up is what turns it into the accepted-values test rather than into a story.