How to detect schema drift, stale tables, and row-count drops in Postgres

The job finished. Airflow is green, dbt says every test passed, and the revenue chart still shows half a day. Nothing crashed, so nothing paged anyone. Most of these incidents come down to three failures that a successful run does not rule out:

• Schema drift. A column was renamed, dropped, or retyped upstream. Queries still run; they return nulls or zeros.
• Freshness lag. The table stopped updating. Every query is correct, about yesterday.
• Volume drops. The load ran but brought a fraction of the usual rows: a partial file, a filter bug, an API that paged early.

Each one can be caught with a small SQL query against Postgres. This guide gives you those queries, what to treat as a failure versus a warning, and the dbt versions. Every SQL block was run on Postgres before publishing. Swap public.orders for your own table.

1. Schema drift: diff information_schema between runs

Postgres already describes every column in information_schema.columns. Detecting drift means saving that list after a good run and comparing it on the next one. Start with a table to hold the baseline:

create table if not exists dq_schema_snapshot (
  table_schema text not null,
  table_name   text not null,
  column_name  text not null,
  data_type    text not null,
  is_nullable  text not null,
  captured_at  timestamptz not null default now(),
  primary key (table_schema, table_name, column_name)
);

After a run you trust, replace the stored snapshot for the table. Doing it in one transaction means a failed refresh never leaves you with half a baseline:

begin;
delete from dq_schema_snapshot
where table_schema = 'public' and table_name = 'orders';

insert into dq_schema_snapshot (table_schema, table_name, column_name, data_type, is_nullable)
select table_schema, table_name, column_name, data_type, is_nullable
from information_schema.columns
where table_schema = 'public' and table_name = 'orders';
commit;

On every later run, diff the live columns against the baseline. No rows means no drift:

with current_cols as (
  select column_name, data_type, is_nullable
  from information_schema.columns
  where table_schema = 'public' and table_name = 'orders'
),
baseline as (
  select column_name, data_type, is_nullable
  from dq_schema_snapshot
  where table_schema = 'public' and table_name = 'orders'
)
select
  coalesce(c.column_name, b.column_name) as column_name,
  case
    when c.column_name is null then 'removed'
    when b.column_name is null then 'added'
    when c.data_type <> b.data_type then 'type_changed'
    else 'nullability_changed'
  end as change,
  case when c.column_name is null or c.data_type <> b.data_type then 'fail' else 'warn' end as severity,
  b.data_type as baseline_type,
  c.data_type as current_type
from current_cols c
full outer join baseline b on b.column_name = c.column_name
where c.column_name is null
   or b.column_name is null
   or c.data_type <> b.data_type
   or c.is_nullable <> b.is_nullable
order by severity, column_name;

What should fail and what should warn

• Fail on a removed column or a changed type. Downstream SQL that selects the column breaks or silently changes meaning.
• Warn on an added column or a nullability change. Usually harmless, but someone should know a contract moved.
• A rename shows up as one removed and one added column. Treat it as a failure: anything reading the old name now gets nothing.

data_type reports character varying whether the column is varchar(50) or varchar(500). If length or precision changes matter to you, add character_maximum_length and numeric_precision to both the snapshot and the comparison.

2. Freshness: how old is the newest row?

If the table has an updated_at (or a load timestamp), freshness is the age of its newest value compared with the window you expect. Pick the window from how often the table loads, not from a default: an hourly feed might allow 2 hours, a nightly batch 26.

select
  max(updated_at) as newest_row,
  now() - max(updated_at) as lag,
  coalesce(now() - max(updated_at) > interval '6 hours', true) as is_stale
from public.orders;

The coalesce(..., true) matters: on an empty table max() returns null, and an empty table should count as stale rather than slip through as unknown. An index on updated_at lets Postgres answer max(updated_at) from the end of the index instead of scanning the table.

Tables with no timestamp column

Postgres keeps cumulative counters of inserted, updated, and deleted rows per table in pg_stat_user_tables. Save the total on a schedule, and a table whose counter has not moved since the previous capture has not been written to. Like the schema baseline, keep the history in a small table:

create table if not exists dq_write_counter (
  relid       oid not null,
  writes      bigint not null,
  captured_at timestamptz not null default now()
);

insert into dq_write_counter (relid, writes)
select relid, n_tup_ins + n_tup_upd + n_tup_del
from pg_stat_user_tables
where schemaname = 'public' and relname = 'customers';

select
  captured_at,
  writes - lag(writes) over (order by captured_at) as writes_since_previous
from dq_write_counter
where relid = 'public.customers'::regclass
order by captured_at desc
limit 2;

A writes_since_previous of 0 during hours when the table should load is your stale signal. Two caveats: the counters are kept per server, so read them on the primary, and they are reset by pg_stat_reset() and after a crash, which shows up as a negative difference you should ignore.

3. Row counts: compare with the same weekday last week

A raw row count tells you nothing without a baseline. Comparing yesterday with the day before is noisy for any table with a weekly rhythm, such as orders that dip at weekends. Comparing with the same weekday last week cancels that out. This version builds the days with generate_series, so a day with zero rows shows up as 0 instead of disappearing from the result:

with days as (
  select generate_series(current_date - 8, current_date - 1, interval '1 day')::date as day
),
daily as (
  select d.day, count(o.created_at) as row_count
  from days d
  left join public.orders o
    on o.created_at >= d.day
   and o.created_at <  d.day + 1
  group by d.day
)
select
  y.day,
  y.row_count,
  w.row_count as same_weekday_last_week,
  round(100.0 * (y.row_count - w.row_count) / nullif(w.row_count, 0), 1) as pct_change
from daily y
join daily w on w.day = y.day - 7
where y.day = current_date - 1;

To turn that into a verdict, decide on thresholds and a minimum volume. The numbers below are starting points to tune, not rules: fail when the count halves, warn when it doubles, and skip the judgment when last week had too few rows for a percentage to mean anything.

with counts as (
  select
    count(*) filter (where created_at >= current_date - 1 and created_at < current_date)     as yesterday,
    count(*) filter (where created_at >= current_date - 8 and created_at < current_date - 7) as last_week
  from public.orders
  where created_at >= current_date - 8 and created_at < current_date
)
select
  yesterday,
  last_week,
  case
    when last_week < 100 then 'too_small_to_judge'
    when yesterday < last_week * 0.5 then 'fail'
    when yesterday > last_week * 2 then 'warn'
    else 'ok'
  end as verdict
from counts;

• Small tables are noisy. Going from 6 rows to 2 is a 67% drop and probably nothing. The last_week < 100 floor keeps them out of your alerts.
• Day boundaries follow the session time zone. current_date and the comparison with a timestamptz column both use the session TimeZone. Run the check with the time zone your business days are in, for example set time zone 'America/New_York'.
• Spikes matter too. A doubled load is often a duplicate backfill. Warn on it rather than ignoring it.

For very large tables where even an indexed count is slow, the planner's estimate is close to free. It is only as current as the last ANALYZE, so use it for rough size tracking, not for a daily threshold:

select c.reltuples::bigint as estimated_rows, s.last_analyze, s.last_autoanalyze
from pg_class c
join pg_stat_user_tables s on s.relid = c.oid
where c.oid = 'public.orders'::regclass;

4. The same checks as dbt tests

If the tables are dbt sources or models, you can express all three checks in a dbt project. Freshness is built in. On dbt 1.10 and later, freshness and loaded_at_field sit under config. Older versions put them directly on the table. Run it with dbt source freshness:

version: 2

sources:
  - name: app
    schema: public
    tables:
      - name: orders
        config:
          loaded_at_field: updated_at
          freshness:
            warn_after: {count: 6, period: hour}
            error_after: {count: 24, period: hour}

Row counts fit a singular test: a SQL file in tests/ that fails when it returns rows. This is the threshold query from section 3, keeping only the failing case:

-- tests/orders_row_count_drop.sql
-- Fails when yesterday has under half the rows of the same weekday last week.
select yesterday, last_week
from (
  select
    count(*) filter (where created_at >= current_date - 1 and created_at < current_date)     as yesterday,
    count(*) filter (where created_at >= current_date - 8 and created_at < current_date - 7) as last_week
  from {{ source('app', 'orders') }}
  where created_at >= current_date - 8 and created_at < current_date
) counts
where last_week >= 100
  and yesterday < last_week * 0.5;

Column contracts (dbt 1.5 and later) cover schema drift for models dbt builds. With contract: {enforced: true}, dbt checks the model's columns and types against the YAML before it builds, and fails the build on a mismatch. The contract has to list every column the model returns:

version: 2

models:
  - name: fct_orders
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: integer
        constraints:
          - type: not_null
      - name: customer_id
        data_type: integer
      - name: amount
        data_type: numeric
      - name: created_at
        data_type: timestamp with time zone

5. Where dbt tests stop

dbt tests are worth having. They also leave gaps that line up with the three failures above:

• They run when dbt runs. If the scheduler never starts the job, or the job is paused, no test fails. A stale table is exactly the case where nothing ran.
• Contracts cover models, not sources. A contract stops dbt from building a model whose output changed shape. It does not watch the raw tables your sources point at, where most upstream drift starts.
• Most tests check shape, not arrival. not_null, unique, and accepted_values pass on a table that loaded a tenth of its usual rows.
• Thresholds are fixed and written by hand. The tests that fire tend to be the ones written after an incident. A table nobody thought about has none.

The SQL in this guide closes those gaps when it runs on its own schedule against the tables themselves, outside the dbt run.

6. Automating it

Whichever way you run the checks, the setup is the same three decisions:

• A schedule independent of the pipeline. Cron, an Airflow DAG of its own, or a scheduled job in your platform. If the checks only run after the load, they cannot notice that the load never happened.
• An alert that says what changed. A Slack message reading "orders: 412 rows vs 1,980 same weekday last week" gets acted on; "check failed" gets muted.
• A read-only role. The checks only read. Give them a role that cannot write:

create role row_monitor_reader with login;
grant connect on database postgres to row_monitor_reader;
grant usage on schema public to row_monitor_reader;
grant select on all tables in schema public to row_monitor_reader;
alter default privileges in schema public grant select on tables to row_monitor_reader;

Set a password with \password row_monitor_reader in psql, and use your database name in place of postgres. alter default privileges only covers tables later created by the role that runs it, so run it as the role that owns your tables.