Detect schema changes with SQL before dashboards break

Someone renames customer_email to email in a migration. The migration is correct, the app works, the tests pass. Three weeks later a chart that groups by customer_email shows an error, or worse, a query that used coalesce(customer_email, 'unknown') quietly returns “unknown” for every row, because the column it falls back to still exists in another table.

Schema changes are the one kind of data incident that the people causing it have no reason to notice. The fix is cheap: ask the database what it looks like today, compare that to what your dashboards expect, and alert on the difference.

What breaks, and how loudly

Four changes cover almost everything:

• A column is dropped or renamed. Queries that name it fail. That’s the loud case, and you find out when someone opens the dashboard.
• A column changes type. numeric to text still runs. Sums stop working, or sort order flips because '10' now sorts before '9'.
• A column is added. Harmless on its own, but it’s often the other half of a rename.
• A table is dropped or replaced. Usually from a pipeline rebuild. The new table may have the same name and a different shape.

The loud case is the lucky one. The type change is the one that costs you a week of wrong numbers. This is the same family of problem as a dashboard that stays green while the number is wrong: nothing errors, so nothing alerts.

Pin the columns you actually use

You don’t need to watch the whole database. Your dashboards read a few dozen columns. List those, with their types, directly in the query:

WITH expected(table_name, column_name, data_type) AS (
  VALUES ('orders', 'id',             'integer'),
         ('orders', 'amount',         'numeric'),
         ('orders', 'created_at',     'timestamp with time zone'),
         ('orders', 'customer_email', 'text')
), actual AS (
  SELECT table_name, column_name, data_type
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name IN ('orders')
)
SELECT coalesce(e.table_name, a.table_name)   AS table_name,
       coalesce(e.column_name, a.column_name) AS column_name,
       CASE WHEN a.column_name IS NULL THEN 'missing'
            WHEN e.column_name IS NULL THEN 'new'
            ELSE 'type changed' END           AS change,
       e.data_type AS expected_type,
       a.data_type AS actual_type
FROM expected e
FULL JOIN actual a USING (table_name, column_name)
WHERE a.column_name IS NULL OR e.column_name IS NULL OR e.data_type <> a.data_type
ORDER BY 1, 2;

I ran this against a test table after renaming customer_email to email and changing amount to text. It returned three rows: amount as type changed, customer_email as missing, email as new. A rename always shows up as a missing and a new pair, which makes it easy to read.

Two details matter. data_type in information_schema is the long form (timestamp with time zone, not timestamptz, and integer, not int4), so write the expected values the way the catalog prints them. And the table_name IN (...) filter keeps the new-column rows limited to the tables you pin. Without it, every table in the schema would show up as “new”.

Turn it into a check that can alert

A monitor wants a single number it can compare to a threshold. Collapse the diff into a count:

WITH expected(table_name, column_name, data_type) AS (
  VALUES ('orders', 'id',             'integer'),
         ('orders', 'amount',         'numeric'),
         ('orders', 'created_at',     'timestamp with time zone'),
         ('orders', 'customer_email', 'text')
)
SELECT count(*) AS drifted_columns
FROM expected e
LEFT JOIN information_schema.columns a
  ON a.table_schema = 'public'
 AND a.table_name = e.table_name
 AND a.column_name = e.column_name
WHERE a.column_name IS NULL OR a.data_type <> e.data_type;

Alert when drifted_columns > 0. The threshold is zero because there’s no normal level of drift to tolerate: if a column you depend on changed, somebody should look. This version ignores new columns on purpose. An added column can’t break a query that already works, and alerting on it trains people to mute the check.

Run it once a day, or after your deploy window. A schema change only happens when someone ships something, so hourly adds cost without catching anything sooner. On a warehouse where queries cost money, information_schema lookups are metadata reads and are normally cheap, but check your platform’s pricing before scheduling it often.

When the check fires, the fix is one of two things: update your dashboard queries to the new name, or update the expected list because the change was intended. Both take a minute. The point is that you do it on the day of the deploy, not three weeks later.

The snapshot approach, when you want to watch everything

The pinned list needs maintenance: add a dashboard, add its columns. If you’d rather watch every table, store a snapshot of the catalog and diff against the last one. This needs a place to write, so it suits a database you own and not a read-only replica.

CREATE TABLE schema_snapshot (
  taken_on    date,
  table_name  text,
  column_name text,
  data_type   text
);

-- run once a day, after the check below
INSERT INTO schema_snapshot
SELECT current_date, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name <> 'schema_snapshot';

The diff compares the catalog to the most recent snapshot:

WITH prev AS (
  SELECT table_name, column_name, data_type
  FROM schema_snapshot
  WHERE taken_on = (SELECT max(taken_on) FROM schema_snapshot)
), now_ AS (
  SELECT table_name, column_name, data_type
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name <> 'schema_snapshot'
)
SELECT coalesce(p.table_name, n.table_name)   AS table_name,
       coalesce(p.column_name, n.column_name) AS column_name,
       CASE WHEN n.column_name IS NULL THEN 'dropped or renamed'
            WHEN p.column_name IS NULL THEN 'added'
            ELSE 'type changed' END           AS change
FROM prev p
FULL JOIN now_ n USING (table_name, column_name)
WHERE n.column_name IS NULL OR p.column_name IS NULL OR p.data_type <> n.data_type;

The snapshot version is noisier. Every new table and every added column lights up, including ones nobody cares about. Filter by schema or table prefix, or keep the snapshot approach for the schemas your pipelines write to and the pinned list for the columns dashboards read.

What this doesn’t catch

A column that keeps its name and type but changes meaning, for example amount switching from dollars to cents, passes this check. For that, reconcile the dashboard number against the source or watch for a sudden jump with a week over week alert. Schema checks tell you the shape changed. Value checks tell you the contents did.

Other engines expose the same catalog. MySQL and Snowflake have information_schema.columns with slightly different type names, and BigQuery has one per dataset. Run the query once, look at how types are spelled, and adjust the expected list.

If you want this running without wiring up a scheduler, arcodash can run any SQL like this on a schedule and alert to Slack or email when the count is above zero. See data quality monitors for how thresholds and schedules work. Start free: 14 days, no card.