Testing Metric Logic With dbt Data Tests in CI

Metric drift starts quietly. A dbt transformation defines "active user" one way. A dashboard built on top of it defines the term a little differently, because someone adjusted a filter in the BI tool six months ago and never told anyone. Neither side is lying. Neither side even knows there's a disagreement, not until an executive asks why two reports show different numbers for the same quarter.

The root problem is structural. When a metric definition lives in BI-layer configuration instead of a version-controlled dbt project, the deployment pipeline has no point where it can check that logic before it reaches a dashboard. Nothing runs it through a test. Nothing flags that it changed. It just ships, silently, every time someone opens the report-builder and edits a formula.

Drift like this is almost never deliberate. A product change gets reflected in dbt, but the update never makes it downstream into the reporting layer. Or a non-technical stakeholder tweaks a calculation to answer a one-off question, and that tweak quietly becomes the new permanent definition, with no record of why it changed or who approved it. By the time anyone notices, the fix means tracing the same metric across two or three different systems, trying to figure out which version is correct and which one has been wrong for weeks.

dbt Core's answer to this is structural. Models and tests live in the same version-controlled project, and metrics can be defined directly in YAML through MetricFlow (full Semantic Layer querying needs dbt Cloud, but the definitions themselves are still version-controlled either way). The practical result: the metric definition and the tests that validate it move through the same pull request, the same review, and the same CI run. The logic lives in the same system, so there's no separate system where it can drift unnoticed.

How dbt's three test mechanisms divide the validation work

dbt gives you three distinct ways to catch problems, and each one is built for a different kind of failure.

Data tests check the warehouse itself, every time the pipeline runs. They compile down to a SQL SELECT statement: if that query returns zero rows, the test passes; if it returns anything at all, the test fails. Inside that category there are two flavors. Generic tests, like unique, not_null, accepted_values, and relationships, get declared once in YAML and reused across as many models as needed. They form the broad net: cheap to write, and you can apply them everywhere. Singular tests are one-off SQL files that live in the tests/ directory; you build them for assertions generic tests can't express, like a join across two models that should never produce duplicates, or an aggregate that should never go negative.

Unit tests, added in dbt 1.8, work differently. Instead of checking live warehouse data, they run a model's SQL logic against small, hand-built mock inputs, and you compare the output to an expected result. That matters because it separates two questions that often get tangled together: is the transformation logic correct, and does the current data happen to look fine? A unit test answers the first question without needing a single row of real data.

Parse-time validation is the third mechanism, and it's the cheapest one by far. Running dbt parse checks every metric definition against the schema of the model it depends on, catching a broken reference before any query ever runs against the warehouse.

These three aren't substitutes for each other. If a project only runs data tests, it will catch bad warehouse data but miss broken logic before it's ever materialized. A project that only runs unit tests will catch broken logic but miss the case where the warehouse itself goes stale or inconsistent. The three mechanisms cover three separate failure points, and the next question is where in the DAG each one actually belongs.

Matching each test type to the right DAG layer

Writing the right kind of test doesn't help much if it sits in the wrong place. A test fired at the wrong layer either runs too late to stop the damage, or fires constantly on problems it was never designed to explain.

Picture an orders fact table: it flows from raw source data, through staging, through an intermediate model, and finally into a mart that feeds a revenue dashboard.

At the sources layer, the job is to catch a bad supplier before anything downstream touches it. Freshness checks, not_null and unique tests on identifiers, and accepted-values tests on known categorical fields all belong here. This is the cheapest place in the whole DAG to stop a problem, because nothing has been transformed yet.

At staging, the concern shifts to renaming, casting, and deduplication. Generic tests in YAML cover a lot of this ground, but unit tests earn their keep here too: feed the model a handful of mock raw rows, check that the cleaned output renames and casts fields the way it's supposed to, all without touching the warehouse.

Intermediate models are where reusable business logic lives, so other models depend on them as contract surfaces. Relationship tests and custom generic tests belong here: if an intermediate model that feeds three separate marts has a broken join, that failure appears once, at the intermediate layer, instead of three times as disconnected mart failures that look unrelated until someone traces them back.

Marts are the layer closest to what a dashboard actually displays, and they carry the most exposure to metric drift. You need grain tests, referential integrity checks, and rules about measures that should never go negative or should always balance. Singular tests that pin known aggregate values are especially useful at this layer: they catch the case where something upstream quietly shifted a metric's historical total. This is the last checkpoint before a bad number reaches someone's dashboard.

Tools like dbt-checkpoint can enforce, at the pull request stage, that models at each of these layers carry the minimum test coverage they're supposed to. That turns "did someone remember to test this" into a structural requirement instead of something that depends on a reviewer's memory.

Using unit tests to pin metric calculation logic before data enters the picture

Of the three mechanisms, unit tests are the sharpest instrument for metric logic specifically, because they isolate one question: does the transformation compute the right answer, regardless of what the warehouse happens to contain right now.

A data test can only catch a problem if the warehouse currently holds an example of it, while a unit test can catch it before that coincidence ever happens. A revenue calculation that mishandles refunds might pass every data test for months, simply because no refund happened to land in the data during that window. A unit test doesn't wait for that coincidence. Feed it a mock row with a refund and declare what the correct output should be; the test either confirms the logic handles it or fails immediately, long before a real refund lands in production.

The same logic applies to definition changes. Say a team redefines "active user" from a 7-day window to a 30-day window. It's a business decision with real consequences for every downstream report, but you can validate the new calculation against expected output with a unit test before the model is even materialized. You catch the drift the moment someone writes the change, not months later when someone notices the active-user count looks strange on a dashboard.

There's a second benefit that's easy to undersell: because metric definitions in MetricFlow YAML are human-readable, a stakeholder who isn't a dbt engineer can still look at a pull request and recognize whether the logic matches what they meant. That closes a gap that causes a lot of drift in the first place, where a business definition gets translated into code once and then nobody outside the data team checks whether the translation still matches the original intent.

Structuring CI to run the right tests on the right changes without burning compute

Running the entire test suite on every single pull request can sound thorough. In practice it's a compute bill that grows with the project and a latency problem that makes engineers start ignoring CI results altogether, which defeats the point of having the tests.

Slim CI solves this by comparing the pull request branch against the current production state and building only what actually changed, plus whatever depends on it downstream, using the state:modified+ selector. Instead of rebuilding and retesting an entire DAG with every commit, the pipeline touches only the parts a given change could plausibly affect.

As of September 10, 2026, Slim CI, defer to production, failed-execution recovery, and concurrent execution are all generally available for dbt Projects on Snowflake. So teams can reuse dbt artifacts from recent production runs to speed up development, and they don't need to stand up and maintain a separate artifact store on the side.

Before dbt 1.9, dynamic configurations could trick dbt into thinking a model had changed when it hadn't, triggering false positives in Slim CI selection, a real wrinkle in earlier versions.

The practical split is that pull request CI runs unit tests on whatever logic changed, plus state-aware data tests on the models that change actually touches. The full critical test suite, the heavier and more expensive one, runs on a separate production job where cost and runtime pressure matter less.

Severity settings do the rest of the work of keeping this sane. Structural failures, like a broken grain or a null primary key, get severity: error, which blocks deployment. Business anomalies, like a KPI that's unexpectedly high or low, get severity: warn, which surfaces the signal and notifies whoever owns that metric without halting the whole pipeline. An out-of-range number might be a real business event, not a data error, and a pipeline shouldn't grind to a halt over something that might turn out to be good news.

Adding statistical and regression tests to catch drift that structural tests miss

Every structural test can pass and a metric can still be wrong in the way that matters most: it no longer matches its own history. Structural tests check that the schema is correct. They say nothing about whether this quarter's number is consistent with what the same calculation produced last quarter, which is exactly the kind of drift a business stakeholder notices first.

Statistical tests fill that gap. Packages like dbt-expectations offer tests such as expect_column_values_to_be_within_n_moving_stdevs, which checks whether a value has drifted from its own recent moving average, so you don't need to hand-tune a fixed threshold. That's useful for a metric like conversion rate, where "normal" shifts gradually over time and a fixed cutoff would either miss slow drift or constantly cry wolf over ordinary seasonal movement. A sudden skew in a payment-amount distribution, for instance, usually points to an ingestion problem upstream, something no schema test would ever catch because the schema itself is perfectly fine.

Regression tests, written as singular SQL, catch a related but different problem: they pin a known aggregate value for a metric that matters, like total revenue for a closed quarter, so that if an upstream change later shifts that historical number, the test fails immediately. This matters most when a model feeding an old report gets touched. The exact conditions that produced the original output might not be reproducible anymore, which makes catching the shift at test time far better than trying to reconstruct it after the fact.

Severity discipline carries over here too. Statistical anomaly tests almost always run at warn severity. They're meant to flag something for a human to look at, not to block a deployment over a number that might be a legitimate business event rather than a broken pipeline.

Governance tooling that enforces test coverage as a project-level requirement, not a per-PR habit

None of the previous sections hold up if test coverage depends on individual engineers remembering to write tests every time they touch a model. Under delivery pressure, that discipline erodes. Someone ships a new mart without a grain test because the deadline was tight, and nobody notices until the mart is already feeding three dashboards.

The fix is to move enforcement from a habit to a mechanical requirement. dbt-checkpoint runs at commit stage and can block a commit, or be wired into CI to block a merge, if a model lacks the tests or documentation it's supposed to have. That check doesn't rely on a reviewer remembering to look for it.

dbt-project-evaluator works at the DAG level. It runs as a dbt package that scans the whole project for structural problems: models with no tests at all, sources with no freshness checks, marts that reach across domain boundaries to query staging models directly instead of going through the intermediate layer the way they're supposed to.

At enterprise scale, the failure mode is predictable: models multiply faster than anyone can track ownership of them, test coverage degrades as deadlines tighten, and the DAG slowly turns into a dependency graph nobody fully understands anymore. Governance tooling applied at the project level is the structural response to that pattern. Code review alone, no matter how careful, doesn't scale against it.

Where dbt CI enforcement reaches its limits

dbt tests are built to catch problems at transformation time. They're not built to catch everything that can go wrong with a metric, and the honest version of this argument has to say where the edges are.

A few things dbt CI tests simply don't do. They don't serve self-serve analytics, where a business user wants to slice a metric across an arbitrary dimension at query time. They don't apply per-user row-level security, a concern that lives downstream of the transformation layer. And they don't catch production data going stale between pipeline runs: a freshness SLA checks a last-updated timestamp at the moment it runs, but it isn't watching continuously in between.

That leaves a real gap between a pipeline being green and a metric actually being correct in the moment someone looks at it. A table can match its schema perfectly and still be sitting on stale data. A metric can pass every unit test and still get computed on the wrong grain once it lands in a dashboard. CI tests confirm the pipeline did what it was told. They confirm what the pipeline was told to do, not whether that still matches reality at query time, which is the argument for pairing dbt tests with data observability tooling.

There's also a case where metric logic belongs outside a materialized dbt model. Metrics that need to flex across arbitrary dimensions, handle period-over-period comparisons, or apply per-user filters don't belong baked into a transformation, because every new variation would mean a new pull request and a new pipeline run just to answer one question. A semantic layer computes that kind of thing on the fly instead. Most teams end up running both: dbt handles the persistent, expensive, widely shared transformations, and a semantic layer handles the flexibility that query time demands. Neither one replaces the other, and the strongest setups treat them as two halves of the same job.