# How to detect schema drift, stale tables, and row-count drops in Postgres > Copy-paste SQL and dbt tests for the three silent failures behind a green pipeline and a wrong dashboard. Source: https://rowmonitor.com/guides/postgres-data-quality-checks ยท Updated 2026-10-30 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: ```sql 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: ```sql 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: ```sql 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. ```sql 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: ```sql 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'; ``` ```sql 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: ```sql 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. ```sql 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: ```sql 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`: ```yaml 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: ```sql -- 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: ```yaml 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: ```sql 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. ## Or let Row Monitor run them Row Monitor runs these three checks for you on the Postgres tables you name. The first clean check stores the baseline: columns and types, row count, and the newest timestamp. After that, a dropped column, a stale table, or a row-count drop sends a Slack or email alert with the diff, a plain-English explanation, and a dbt `schema.yml` snippet you can paste into your project. It connects with a read-only role like the one above and never writes to your database. There are no rules to write first. It is free for 3 tables; Pro covers 50 tables for $39 a month. [Watch 3 tables free](https://rowmonitor.com/signup) ## FAQ ### Does this work on Snowflake or BigQuery? The SQL here is written for Postgres. Snowflake and BigQuery both expose an information_schema and support the same ideas, but the function names, system views, and costs differ, so the queries need porting. Row Monitor supports Postgres today; Snowflake and BigQuery are on the waitlist. ### Will these queries slow down my database? The schema check reads catalog views and is cheap. The freshness and row-count checks are fast when updated_at and created_at are indexed, because Postgres reads only the newest index entries or the date range it needs. Without those indexes they scan the table, so run them off-peak or against a read replica, or use the reltuples estimate for very large tables. ### What permissions do the checks need? USAGE on the schema and SELECT on the tables. information_schema.columns only lists columns the current role has privileges on, so the schema check must run as a role with SELECT on the table, or it will report columns as removed. pg_stat_user_tables and pg_class are readable without extra grants. ### Why compare with the same weekday last week instead of yesterday? Many business tables load less at weekends and more on weekdays. Comparing Monday with Sunday flags that rhythm as an anomaly every week. Comparing Monday with last Monday removes it, so an alert means something changed. ### Do I still need dbt tests if I run these checks? Yes. dbt tests check the logic of your models, such as uniqueness and accepted values. These checks watch whether data arrived, in the usual volume, in the usual shape, and they keep running when dbt does not.