Schema drift: the upstream rename that loads NULLs for a week

Tuesday, 09:40, someone upstream renames amount to amount_cents. No mail, no PR on your side. The loader runs at 10:00, finishes green, and amount is NULL from that day on. Six days later the revenue dashboard shows a flat line and the question comes to you. This page covers the check at ingest that fails the run instead, where dbt and Great Expectations catch the same thing, and the contract that makes the producer tell you first. The print-ready A4 PDF is at the bottom.

What happened

• amount became amount_cents upstream. No mail.
• The loader mapped nothing to amount. The job stayed green and the column went NULL for six days.

A rename is the common case. A retype (an integer that becomes a string, a timestamp that loses its time zone) and a dropped column do the same thing with different symptoms.

Catch it at ingest

• An expected schema: the columns and their types, in a file, in git.
• Diff it on every run. A missing or retyped column fails the run, before the load.
• dbt not_null in schema.yml: fails the first night the column is NULL.
• Great Expectations: expect_column_to_exist, expect_column_values_to_be_of_type.

The check, before the load

Twelve lines of Python in front of the loader. The run fails at 09:41 with the column name in the message.

expected = {
    'order_id': 'int64',
    'amount': 'float64',
    'placed_at': 'datetime64[ns]',
}
actual = df.dtypes.astype(str).to_dict()

missing = expected.keys() - actual.keys()
retyped = {c for c in expected
           if c in actual and actual[c] != expected[c]}
if missing or retyped:
    raise ValueError(f'schema drift: {missing} {retyped}')

A rename shows up as missing. A string where a float was shows up as retyped. Extra columns are allowed here on purpose: additions rarely break a load, they just need a ticket. Log them.

Where each tool catches it

The dbt version, for the column in question:

models:
  - name: stg_orders
    columns:
      - name: amount
        tests:
          - not_null

It runs after the load, so the NULL rows are already in staging. But it fires the first night, not six days later, and the failing test names the column.

Remember

• A data contract is the ingest check moved to the producer. They run it before they deploy.
• A NULL rate alert is the last net: amount going from 0% NULL to 100% NULL in one day is not drift, it is a break, and it should page someone.

The trap: the check that passes anyway

The order matters: ingest check, then load, then dbt tests, then the NULL rate alert. Each one is cheaper to fix than the next.