Drift Detection for Your Warehouse
Drift occurs when the warehouse no longer matches the schema you have in version control. On a warehouse
running dbt, the complication is that half the objects are supposed to change without a migration when
dbt run drops and recreates its models. A drift check that cannot tell those apart from an unplanned
change is noise.
This page sets up a check that reports the schema layer and stays quiet about dbt's output, using the project built in the setup guide.
What migrate status Does Not Tell You
Someone with warehouse access manually adds a column in a hurry:
ALTER TABLE raw.customers ADD COLUMN legacy_flag UInt8 DEFAULT 0
Atlas is unaware of this change, so the migration history is untouched. Running atlas migrate status
reports the revisions table with no issue in sight:
atlas migrate status --env local
Migration Status: OK
-- Current Version: 20260812082643
-- Next Version: Already at latest version
-- Executed Files: 4
-- Pending Files: 0
Versioned deployments trust the revisions table so applies remain fast and deterministic. Detecting drift (the added column) means comparing against the warehouse itself.
Checking for Drift
atlas schema diff inspects the live warehouse and reports the statements that would bring it
to the desired state:
atlas schema diff --env local --from "$CLICKHOUSE_URL" --to "file://schema.ch.hcl"
ALTER TABLE `raw`.`customers` DROP COLUMN `legacy_flag`;
That output is the drift report. A column exists in the warehouse that the code does not describe.
On a clean warehouse the same command prints:
Schemas are synced, no changes to be made.
The statements are a description of the difference, not a remediation script. Dropping the column may be right, or the column may be something the code should adopt. Decide, then either revert the change or add it to the schema file and generate a migration.
Excluding dbt's Database
The exclude list that makes it possible to use
this check also blinds it. A table was created inside dbt's database next to the models:
CREATE TABLE analytics.hand_made (id UInt64) ENGINE = MergeTree ORDER BY id
With exclude = ["analytics.*", ...], the check reports only the source-layer drift, and says nothing
about the stray table:
ALTER TABLE `raw`.`customers` DROP COLUMN `legacy_flag`;
Remove the exclude line and the check sees everything inside analytics, with no way to tell dbt's
two legitimate models from the table nobody planned:
ALTER TABLE `raw`.`customers` DROP COLUMN `legacy_flag`;
-- Drop "stg_events" view
DROP VIEW `analytics`.`stg_events`;
-- Drop "daily_active_users" table
DROP TABLE `analytics`.`daily_active_users`;
-- Drop "hand_made" table
DROP TABLE `analytics`.`hand_made`;
The check against your schema file therefore covers the schema layer only. It cannot cover dbt's output, because a schema file is the wrong expected state for objects a materialization rewrites on every run. The next section uses the correct one.
Detecting Drift Inside dbt's Database
The expected state for dbt's models is whatever dbt builds right now from the committed project. Give dbt an empty database to build into, and that becomes something Atlas can diff production against.
Add the scratch database analytics_ci to the schema layer, so Atlas provisions it and its grants:
schema "analytics_ci" {
}
permission {
for = schema.analytics_ci
to = role.dbt_runner
privileges = [SELECT, INSERT, CREATE_TABLE, CREATE_VIEW, DROP_TABLE, DROP_VIEW, ALTER, OPTIMIZE, TRUNCATE]
}
-- Add new schema named "analytics_ci"
CREATE DATABASE `analytics_ci` ENGINE Atomic;
-- Grant on schema "analytics_ci" to "dbt_runner"
GRANT ALTER, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, INSERT, OPTIMIZE, SELECT, TRUNCATE ON `analytics_ci`.* TO `dbt_runner`;
Add a target that points there, and keep analytics_ci.* in env.exclude alongside analytics.*:
ci:
type: clickhouse
host: localhost
port: 8123
user: dbt
password: ""
schema: analytics_ci
secure: False
Rebuild the models into it, then diff the two databases:
dbt run --target ci
atlas schema diff \
--from "clickhouse://default:pass@localhost:9000/analytics" \
--to "clickhouse://default:pass@localhost:9000/analytics_ci"
Schemas are synced, no changes to be made.
Production matches what the committed project builds. Now the two changes from the previous section,
a column patched onto a model table and a table created by hand, are both inside analytics:
ALTER TABLE analytics.daily_active_users ADD COLUMN hand_added UInt8 DEFAULT 0;
CREATE TABLE analytics.hand_made (id UInt64) ENGINE = MergeTree ORDER BY id;
Running schema diff again reports exactly those two, and stays silent about the models that are
supposed to be there:
ALTER TABLE `daily_active_users` DROP COLUMN `hand_added`;
-- Drop "hand_made" table
DROP TABLE `hand_made`;
Views are part of the comparison. Hand-editing production's view definition:
CREATE OR REPLACE VIEW analytics.stg_events AS
SELECT event_id, user_id, event_type, occurred_at, toDate(occurred_at) AS event_date,
'patched' AS hand_added
FROM raw.events;
produces the statement that restores the committed definition:
atlas schema diff \
--from "clickhouse://default:pass@localhost:9000/analytics" \
--to "clickhouse://default:pass@localhost:9000/analytics_ci"
-- Modify "stg_events" view
CREATE OR REPLACE VIEW `stg_events` (
`event_id` UUID,
`user_id` UInt64,
`event_type` String,
`occurred_at` DateTime,
`event_date` Date
) AS SELECT event_id, user_id, event_type, occurred_at, toDate(occurred_at) AS event_date FROM raw.events;
Rebuilding without processing data
A full rebuild costs a full run, which is too much for a scheduled check on a real warehouse.
dbt run --empty builds the same objects with limit 0 instead.
Do not run --empty across the whole project, though, as it wraps model queries with its limit 0
predicate. For a view, that wrapper lands in the stored definition, so the view genuinely differs
from production and the diff reports it:
-- Modify "stg_events" view
CREATE OR REPLACE VIEW `stg_events` (...) AS SELECT ... FROM (SELECT * FROM raw.events WHERE false LIMIT 0);
The fix is not to exclude views from the diff, which would give up view drift detection. Views hold
no data, so build them normally and use --empty only for the materializations that do:
dbt run --select config.materialized:view --target ci
dbt run --empty --exclude config.materialized:view --target ci
atlas schema diff \
--from "clickhouse://default:pass@localhost:9000/analytics" \
--to "clickhouse://default:pass@localhost:9000/analytics_ci"
Schemas are synced, no changes to be made.
Running the --empty pass first fails. A table model whose ref() points at a view that has not been
built yet in the scratch database dies with Database Error in model daily_active_users, and dbt skips
its children. Views first, then the rest.
Keep in mind:
- The comparison is structural, so differing row counts between the databases are irrelevant.
- The rebuild has to come from the same commit that produced production. Otherwise, you are looking at your own unreleased model changes rather than at drift.
Run It on a Schedule
If you use Atlas Cloud Schema Monitoring, there is a purpose-built action for this:
ariga/atlas-action/monitor/schema
syncs the schema on a cron and drift detection runs in Cloud. It takes the same exclude patterns,
so dbt's databases stay out of it:
on:
schedule:
- cron: '0 */4 * * *'
jobs:
monitor:
runs-on: ubuntu-latest
steps:
- uses: ariga/setup-atlas@v0
- uses: ariga/atlas-action/monitor/schema@v1
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
url: ${{ secrets.CLICKHOUSE_URL }}
slug: warehouse
exclude: |-
analytics.*
analytics_ci.*
Without Cloud, the same check runs from the CLI on any scheduler. atlas schema diff exits 0
whether or not it finds differences, so the job decides on the output:
name: Warehouse drift
on:
schedule:
- cron: '0 * * * *'
workflow_dispatch:
jobs:
drift:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: ariga/setup-atlas@v0
with:
cloud-token: '${{ secrets.ATLAS_CLOUD_TOKEN }}'
- name: Check for drift
env:
CLICKHOUSE_URL: '${{ secrets.CLICKHOUSE_URL }}'
CLICKHOUSE_DEV_URL: 'docker://clickhouse/23.11'
run: |
out=$(atlas schema diff --env local \
--from "$CLICKHOUSE_URL" --to "file://schema.ch.hcl")
if echo "$out" | grep -q "Schemas are synced"; then
echo "no drift"
exit 0
fi
echo "::error::warehouse drifted"
echo "$out"
exit 1
Running that logic locally against the demo warehouse, a clean warehouse prints no drift and exits
0, while a warehouse with the out-of-band column present prints the ALTER statement and exits 1.
The same job can cover dbt's side with the two-pass rebuild and second diff from the section above. That needs dbt installed in the runner and the scratch database reachable, so it belongs in whichever pipeline already runs dbt.
Block a Deployment on Drift
A scheduled check reports drift after the fact. The pre-apply drift check refuses to apply migrations onto a warehouse that has drifted since the last applied revision:
env "local" {
// ...
check "migrate_apply" {
drift {
on_error = CONTINUE // switch to FAIL once the diff is clean
}
}
}
This check compares against the expected state for the latest revision, which it reads from the
Atlas Registry. Set migration.repo.name in atlas.hcl and push the
directory with atlas migrate push before enabling it.
Without a registry-backed migration directory, the migration apply fails:
Executing pre-execution check (1 check in total):
-- check at atlas.hcl:32 (drift):
-------------------------------------------
Error: drift check requires migration.repo.name or an atlas:// directory URL to be set
The check has its own exclude list, which is where dbt's database belongs so model rebuilds never
block a deployment:
check "migrate_apply" {
drift {
on_error = FAIL
exclude = ["analytics.*"]
}
}
When drift.exclude is set it replaces env.exclude for this check rather than extending it, so
repeat the dbt patterns here. The revisions table and its schema are excluded automatically.
Atlas Cloud Schema Monitoring runs drift detection continuously instead of on your schedule, through an agent in the database's network, and supports warehouse drivers including ClickHouse and Snowflake.
Next Steps
Schema Management for dbt Projects
The project and exclude patterns this check runs against
Pre-apply Drift Detection
The full reference for the drift block, including exclude semantics
Schema Monitoring
Continuous drift detection with ERDs, diffs and Slack alerts
Atlas Registry
Where the expected state for the pre-apply check comes from
Safe DDL on Large Tables
Which ALTERs rewrite data, and how to block a destructive one
Atlas vs dbt
Why these are not competing tools, and where the line falls
Have questions? Feedback? Find our team on our Discord server.