dbt Test Authoring: By Hand vs With an AI Agent

An AI agent can take over the repetitive part of dbt test authoring: reading a model, proposing schema and data tests, writing the YAML, and wiring the tests into the pipeline quality suite. A person still decides which tests encode real business rules. dbt itself ships four generic data tests out of the box (`unique`, `not_null`, `accepted_values` and `relationships`), so most of the work is deciding where each one belongs, not inventing new checks.

Key Takeaways

• •dbt ships four generic data tests, and a data test passes when its query returns zero failing rows, so authoring is mostly a coverage decision per column.
• •Unit tests arrived in dbt v1.8 and target complex SQL logic, while data tests check the data that actually lands in the warehouse.
• •An agent is well suited to the mechanical work of reading a model, drafting test YAML and registering it in the quality suite; a person should approve severity and business-rule tests.
• •By default, warn_if and error_if both fire on any failing row (!=0), so a test an agent adds can start failing builds on day one unless severity is reviewed.
• •Evaluate an agent for this job on test relevance, false-positive rate and reviewability of its diffs, not on how many tests it generates.

The job and who does it today

Test authoring is the work of deciding what must be true about a model's output and encoding that as something dbt can run. On most teams the analytics engineer who wrote the model also writes its tests, usually at the end of a pull request, and often under time pressure. The result is uneven coverage: primary keys get unique and not_null, and everything else depends on how careful the author felt that day.

The people downstream feel the gaps first. A dashboard owner sees a duplicated key, or a finance analyst notices a status value nobody expected. The test suite then grows reactively, one incident at a time, because writing a test after a failure is easier to justify than writing twenty before one.

That reactive pattern is the real cost of manual authoring. It is not that any single test is hard to write. It is that nobody has the time to walk every model and every column and ask the same five questions, so coverage follows incidents instead of risk.

The manual workflow, step by step

The manual path has five steps. First, read the model SQL and its upstream refs to understand grain and keys. Second, query the warehouse to learn what the data looks like: distinct values in status columns, null rates, candidate keys. Third, write the YAML. Fourth, run dbt test --select <model> and fix what fails. Fifth, decide what happens in CI and in production runs, including whether a failure warns or errors.

The YAML itself is simple. In the dbt docs, a model entry lists columns with data_tests, and tests that need inputs nest them under an arguments property, which the docs say is available in v1.10.5 and higher. The same page notes that in v2 this nesting is required and that top-level inputs next to the test name raise a validation error. A hand-written suite therefore has to track the dbt version it targets.

Severity is the step people skip. The dbt reference lists both error_if and warn_if with a default of `!=0`, meaning one failing row is enough. That default is fine for a primary key and painful for a freshness-sensitive column that is occasionally null for legitimate reasons.

Where the manual workflow breaks

The first break is coverage drift. A model gains three columns in a refactor and nobody adds tests, because the existing YAML still passes. The second is copy-paste: an accepted_values list is pasted from another model and silently goes stale when a new status ships. The third is the handoff between the person who writes tests and the person who decides whether a failure should page someone.

Unit tests add a fourth concern. The dbt docs say unit tests were introduced in dbt v1.8 and recommend them for complex logic such as regex, date math, window functions and case when statements with many branches. Writing them means hand-building fixture rows for each input, which is exactly the kind of tedious but careful work that gets deferred.

The docs also say dbt Labs strongly recommends running unit tests only in development or CI. So a complete manual process needs a decision about which tests run where, and that decision lives in someone's head or in a wiki page, not in the repo.

What an agent does instead

Our Quality Agent carries a skill, dw-dbt-test-authoring, described as authoring dbt schema and data tests for a model and wiring them into the pipeline quality suite. We limit our claims to that description. The agent reads the model, proposes the schema tests and data tests it considers appropriate, writes them in the project's YAML, and registers them with the quality suite so they run alongside the rest of the pipeline checks.

Because every Data Workers agent is an MCP server, the request can be made from the tool your engineers already use, such as Claude Code or Cursor. A prompt like "add tests for the orders model and wire them into the quality suite" reaches the agent through the Model Context Protocol, which defines how a client discovers and calls tools. No separate dashboard is involved.

The practical difference is consistency. The agent asks the same questions of every model, every time: what is the grain, which columns are keys, which have a closed set of values, which reference other models. It does not get tired on the fortieth model, and it does not skip the new columns added in a refactor.

Manual versus agent, side by side

Effort is the honest axis here. We are not publishing time-saved figures because we do not have a public benchmark to cite for this skill. What we can say qualitatively is that the agent removes the reading, typing and registration work and leaves the judgment work in place.

What still needs a human approval

An agent can infer that order_id looks like a key. It cannot know that your finance team treats a refunded order as the same order, or that a null ship_date is legitimate for digital goods. Business-rule tests, accepted_values lists that encode policy, and anything with relationships across domains should be approved by the model owner.

Severity and thresholds also need a person. Since both warn_if and error_if default to !=0, a generated test can start failing production builds immediately. We recommend that new tests land as warnings, with promotion to errors as an explicit, reviewed change.

In the OSS edition, which is read-only by default, the agent proposes and a person applies. In Pro, writes are available with approval workflows and audit trails, so the same review happens inside the product. Either way, the merge decision stays with a human, and the Autonomous Data Conductor's per-domain autonomy dial lets a team choose how much more than that to delegate.

How to evaluate an agent for this job

• •Relevance: sample ten models and ask whether each proposed test would catch a real failure, or only restates the column name.
• •False-positive rate: run the proposed tests against a known-good snapshot and count failures that were not real defects.
• •Reviewability: the output should be a plain YAML diff in your repo, readable in a normal pull request.
• •Version awareness: confirm it writes arguments syntax correctly for the dbt version you run.
• •Scope discipline: it should propose unit tests only where logic is complex, not for functions the warehouse already tests.
• •Integration: tests it adds must show up in the same suite and alerts your team already watches.

Run the evaluation on models you already know well. If you know the right answer for a model, you can tell quickly whether the agent's proposals are sound. Avoid judging on volume: a hundred generated tests that nobody trusts are worse than ten that the model owner approves.

The community package dbt-utils is a useful yardstick. It contains additional generic tests beyond the four built-ins, and a good agent should know when one of them fits better than a hand-rolled SQL test.

Frequently Asked Questions

Can an agent replace writing dbt tests by hand?

It can replace the mechanical parts: reading the model, drafting YAML and registering tests in the suite. It should not replace the decision about which business rules matter. We treat the agent as the author of a first draft and the model owner as the approver.

Should an agent write unit tests or data tests?

Both have a role, and they answer different questions. Data tests check the data that actually landed. Unit tests, available from dbt v1.8, check transformation logic against fixture rows and are best kept to complex logic. Start with data tests for keys and values, and add unit tests where the SQL is intricate.

Will generated tests break my production runs?

They can, if you do not review severity. Because the dbt defaults for warn_if and error_if are both !=0, a single failing row fails the test. Land new tests as warnings first, watch them for a few runs, then promote the ones that earn it.

Do I need to change how my team works to use this?

No. The skill is invoked from an MCP-compatible client such as Claude Code or Cursor, and the output is YAML in your own repository. Your pull request review process stays the same.