We checked every public dbt project we could find for docs-vs-warehouse drift

Here's my favorite bug of the year, found this week in a stranger's public dbt project.

A model called fact_customer_survey is a UNION of two branches. Branch one, line 30: NULL as dissatisfacation_category. Read it again, slowly. Branch two, line 51, spells it correctly: dissatisfaction_category. SQL takes a UNION's output names from the first branch, so the warehouse column is DISSATISFACATION_CATEGORY, typo and all. The correct spelling in branch two gets thrown away silently.

Now the good part. The project's YAML docs declare dissatisfaction_category. Spelled right.

The documentation was correct. The code was wrong. The query ran fine, every day, no errors. And anything that trusted the docs was asking the warehouse for a column that does not exist.

You cannot catch this by reading the SQL. You catch it by holding the docs and the warehouse next to each other, and as far as we can tell, nothing was doing that for column names.

Two files, both confident, never compared

Every dbt project that runs dbt docs generate writes two descriptions of the same data. manifest.json holds what you claimed: models, columns, descriptions. catalog.json holds what the warehouse actually reports back. They drift apart quietly. Someone renames a column in a migration. A chunk of logic gets commented out. A model stops being materialized. Nothing complains.

There are good linters in this space. dbt-checkpoint and dbt-project-evaluator will tell you a column has no description. dbt-checkpoint does reach for the catalog too: check-database-casing-consistency compares it against the manifest to check database and schema names agree on casing. What all of them mostly check is whether documentation exists. Whether it is still true is a different question.

So we measured it

How bad is this in the wild? We went and counted.

We collected every public dbt project we could find that serves both artifacts. 426 candidates, 324 verified pairs. Then one command across all of them:

npx clarilayer dbt-check --json

Full disclosure: we build that tool. It reads the two local files, compares declared columns against cataloged ones, and prints the disagreements. Free, no account, nothing leaves your machine. Every number below can be reproduced by pointing it at the same public artifacts.

354 projects ran in 52 seconds with zero failures. Then we threw most of the corpus away, on purpose. 77 projects use artifact schema versions we don't support yet. 23 publish a docs site whose catalog was never generated at all (more on those in a minute). 14 have artifact pairs generated so far apart that comparing them means nothing.

One thing we refuse to do is quote a single blended number. 43% of the reachable corpus is vendor packages, and a full third is Fivetran alone, whose published catalogs come from Postgres CI runs rather than production warehouses. A blended average would mostly measure one vendor's CI setup. The stratum that matters is organizational production projects, and the reachable public population there is small: 17 organizations, 12 of which clear every gate.

What twelve production projects look like inside

Nine of the twelve carry documentation for columns that do not exist in their warehouse.

• Cal-ITP (California transit), BigQuery, 619 models, 106 phantom columns
• Allvue Systems, Snowflake, 572 models, 112 phantom columns
• Cook County Assessor's Office, Athena, 147 models, 11 phantom columns
• SageRx (open drug data), Postgres, 106 models, 9 phantom columns
• Open edX Aspects, ClickHouse, 36 models, 6 phantom columns

Three projects came back with no phantom columns at all: OHDSI's dbt-synthea (87 models), Data for Good France (62), and effidic (10). Worth saying out loud, because a survey where everyone fails usually means the instrument is broken.

The Allvue row hides a second finding: 323 type mismatches. Most projects never hand-write data_type in YAML, but where they do, it disagrees with the warehouse constantly. The dominant pattern across the stratum is a declared string sitting on top of a warehouse NUMBER.

These are not dead repos, either. Cal-ITP and Cook County generated the artifacts we measured within days of our run.

And those 23 never-generated catalogs deserve their own paragraph. These are published docs sites where the YAML is complete and polished, and the catalog is an empty stub. No warehouse was ever introspected. One of them documents 1,280 models this way. The site looks great. Nothing behind it has ever been compared to anything.

We tried hard to prove our own tool wrong

A drift scanner vouching for its own findings is worth nothing, so we audited ours like a hostile reviewer would.

We hand-adjudicated 79 findings, roughly a quarter of everything flagged in the organizational stratum, by pulling each project's actual model SQL and checking what the SELECT really produces. Result: 72 confirmed drift, 7 undetermined, zero false positives. The exact binomial bound puts the false-positive rate under 3.7%. Weight it by how the sample was drawn across projects and the conservative bound is under 15%. One caveat inside that: a single project (42 findings) publishes artifacts but no source code, so its findings can't be adjudicated in either direction. Its drift counts stand, but we exclude it from the false-positive math, which tightens the bound to 12.3%.

We also went after three specific ways the tool could be fooling us. Two of those checks covered all 311 findings rather than the sample.

Case sensitivity. Snowflake uppercases unquoted identifiers, so maybe we just handle case badly. The control says otherwise: in the Snowflake project, 5,684 of 5,700 successful column matches depend on case normalization working. If it were broken, every one of them would be a phantom. 112 are.

Adapter-specific columns. A column that only exists on some adapters could look phantom against a catalog generated on one. Two projects in the stratum are provably single-adapter, so this theory cannot apply to them, and we checked every finding they have. A census, no sampling. 20 of 20 are real drift.

Stale relations. Maybe the model does produce the column and the catalog is just behind. We swept all 311 findings for column names appearing in live, uncommented SQL. Eight candidates. All eight turned out to be real drift anyway. Seven are CTE-internal aliases renamed before the output. The eighth is the UNION typo this post opened with.

So where does real drift come from? Renames that never made it back into the YAML. Columns deleted while their docs live on. And my favorite category, seven cases in the sample: model logic that has been commented out while the documentation still describes the intent. One project has a 20 KB cleaning projection sitting inside a /* */ block. The live model is a raw passthrough, and the warehouse holds 173 raw Airbyte column names like WEEK STARTING 01/19/2025 - RESOURCES - BASE TARGET HOURS. The docs describe the model someone meant to build.

What this check cannot see

Now the part where we tell you what our own tool misses.

dbt docs generate does not rebuild your models. If a project generates docs without a dbt run first, the catalog describes whatever is deployed, however old, while both files carry fresh timestamps. Our staleness warning reads file timestamps. It catches stale artifacts and completely misses stale warehouses. At least one project in the corpus appears to do exactly this. A drift survey that hid its own blind spot would be committing the sin it measures, so there it is, and it is the first thing we want to fix.

Twelve of Cal-ITP's 106 findings are YAML entries with a literal trailing comma in the name, like - name: feed_type,. The warehouse genuinely has no column by that name, so they are true findings, but they are syntax accidents rather than semantic drift, and we count them separately. Cal-ITP's number holds without them; we re-sampled the remainder and it is ordinary drift.

The corpus has limits too. 17 reachable organizations is not the dbt ecosystem. GitLab's public dbt docs return 403 to every programmatic client, so their project is missing from this corpus entirely. Some dbt-docs builds inline the artifacts into index.html with no separate JSON to fetch, and we hit two of those, so this kind of measurement is getting harder over time rather than easier.

The part about agents, kept short

Documentation used to have one consumer, and humans are forgiving readers. dbt's own MCP server now feeds your models and docs to AI agents as ground truth. A wrong column name used to cost a teammate ten minutes. Now it quietly becomes something an agent believes. The UNION case is the reminder that the error can sit on either side of the comparison.

Run it on yours

Thirty seconds, no signup:

dbt docs generate
npx clarilayer dbt-check

Everything stays on your machine. --json if you want to script it. If the findings are worth keeping, --save stages the ten most serious columns and models into a ClariLayer Context Inbox as proposals, or up to twenty-four with --save-top; you approve or reject each one, and approved items become entries your AI agent recalls before it touches that model again. That last part is the product we sell. The check is not.

The corpus list, per-project results, and the full false-positive audit are published at github.com/clarilayer/clarilayer/tree/main/survey. Run the check on your own project. If it flags something wrong, or flags something wrongly, open an issue. The second kind is the one we most want to hear about.