Creating dbt Models: By Hand vs With an AI Agent
What changes when an agent authors the config block, refs and materialization, and what still needs a person
An AI agent can author a new dbt model, including the config block, the ref() calls and the materialization, faster than a person can type it, but it should not merge that model without review. The reason is in the dbt documentation on SQL models: dbt ships with five built-in materializations (view, table, incremental, ephemeral and materialized_view), and picking the wrong one is a cost and correctness decision, not a syntax detail. This post compares the manual workflow with what an agent does and marks where a human still signs off.
Key Takeaways
- •dbt ships with five built-in materializations, and by default dbt builds models as views, so every new model carries an implicit materialization decision (per the dbt docs).
- •Writing a model by hand is mostly lookup work: which upstream models exist, what to
ref(), which materialization fits, and what the config block needs. That is the part an agent takes over. - •The Pipeline Agent's skill for this job is narrow: author a new dbt model with a correct config block, refs and materialization for the generated pipeline.
- •A human should still approve the business logic, the materialization on large tables, and the merge. Agents propose; reviewers decide.
- •Evaluate an agent for this job on whether it picks real upstream models, compiles cleanly, and leaves a diff you can review, not on how fluent its SQL looks.
The job and who does it today
Creating a dbt model is the most common unit of work in analytics engineering. Someone needs a new table: a staging model over a freshly loaded source, an intermediate join, or a mart for a dashboard. An analytics engineer or a data engineer who already knows the project writes a .sql file, adds a config block, wires it to upstream models, and opens a pull request.
The task looks small, and the SQL often is. What takes time is everything around the SQL. The author has to know which models already exist, what naming convention the project follows, how similar models are materialized, and which tests the team expects. That knowledge lives in the heads of two or three people and in a project directory nobody has read end to end.
This is why we treat the job as a good candidate for an agent. The inputs are structured: a project, a target pipeline, and a set of conventions. The output is a file that either compiles or does not. It is also why the job is easy to get subtly wrong, because a model can compile and still be built as the wrong object type.
The manual workflow, step by step
The manual path has six steps, and each one has a handoff. First, the author reads the request in a ticket or chat thread and works out which grain the new model should have. Second, they search the project for upstream models. Third, they write the SQL. Fourth, they write the config block and choose a materialization. Fifth, they run dbt build against a development schema. Sixth, they open a pull request and wait for review.
Step two is where most time disappears. In the dbt docs on SQL models, ref() serves two purposes: it determines run order by building the dependency graph, and it resolves to the right database object per environment. A wrong ref() therefore does not just break one query. It changes the DAG, and in a large project that can mean a model silently depends on a staging layer it was never meant to touch.
Step four is the quiet one. The config block is a few lines, so people copy it from the nearest model. If the nearest model is an incremental fact table and the new one is a small dimension, the copy brings along an incremental strategy and unique key that the new model does not need. Review catches some of this, but a reviewer reading a diff sees only the lines that changed.
Where the manual workflow breaks
The manual workflow breaks at the handoffs, not at the keyboard. The requester describes a table in business terms. The author translates it into models. The reviewer checks the SQL but usually not the choice of materialization, because the choice is invisible unless you know what the model costs to rebuild. Each handoff drops context.
The second failure is convention drift. Teams write down a style guide, and then each new model is written from whichever existing model the author opened first. Over a year, the project accumulates three ways of naming staging models and two ways of declaring a unique key. Nobody decided that. It happened one copy-paste at a time.
The third failure is the default. Because dbt builds models as views unless told otherwise, a model with no config block still runs. It can sit as a view over a heavy join for months before a dashboard becomes slow. The model was never wrong enough to fail; it was just never decided on. That is the class of problem we want an agent to remove, because it comes from omission, not from difficulty.
What an agent does instead
The Pipeline Agent has a skill for this job, dw-creating-dbt-models. Its description is deliberately narrow: author a new dbt model with the correct config block, refs and materialization for the generated pipeline. We do not claim more than that. It does not decide what the business wants, and it does not redesign your project.
In practice, that means the agent starts from the pipeline it is building, not from a blank file. It reads which upstream models the pipeline produces, writes the model against them using ref() rather than hard-coded table names, and emits a config block that states the materialization explicitly instead of inheriting the default. A person reviewing the pull request then sees the decision written down in the diff.
Because every Data Workers agent is an MCP server, you invoke the skill from the client you already use, such as Claude Code or Cursor. MCP is an open protocol that exposes tools to an AI model, and it states that hosts must obtain explicit user consent before invoking any tool. We read that as a design constraint, and it is why the agent produces a proposal and a diff rather than a silent write.
Manual vs agent, side by side
| Step | By hand | With the Pipeline Agent |
|---|---|---|
| Find upstream models | Search the project, ask a teammate | Starts from the models the pipeline already generates |
| Reference upstream | Type ref() calls, risk a typo or wrong layer | Writes ref() calls against known models |
| Config block | Copy from the nearest model | Writes an explicit config block for this model |
| Materialization | Often left at the default view or copied | Chosen and stated explicitly, flagged for review |
| Validation | Run dbt build in a dev schema | Same compile and build check, run before the PR opens |
| Review | Reviewer reads SQL, may miss the config | Reviewer sees a diff with the decision written down |
| Effort | Hours of attention spread across handoffs | Concentrated in review; we have no attributed benchmark to quote |
We deliberately do not put a time-saved number in that table. Public, attributed figures for this specific task are scarce, and we would rather describe the effort qualitatively than invent one. The difference is where a person's attention goes: from assembling the file to judging it.
What still needs a human approval
Three things stay with a person. The first is the business definition. If the request is "active customers," someone has to say what active means, and an agent that guesses will produce clean SQL for the wrong question. The second is the materialization on anything large. Choosing between table, incremental and materialized_view trades build cost against freshness, and that is a budget decision.
The third is the merge. Data Workers' open-source edition is read-only by default, and the Pro edition adds writes behind approval workflows and audit trails. We think that split is right for model authoring: the agent can draft and validate freely, and a human approves before anything reaches the main branch. Each domain can sit at a different point on the autonomy dial, so a team can let the agent auto-merge staging models while keeping marts under review.
A practical rule we suggest: let the agent run alone on models that are cheap to rebuild and easy to revert, and require approval where a mistake costs warehouse spend or changes a number an executive reads. You can tighten or loosen that per domain as trust builds.
How to evaluate an agent for this job
Evaluate on outcomes you can check. First, does the generated model compile and build in a development schema? That is binary. Second, does every ref() point to a model that really exists, and does it avoid hard-coded database names? Third, is the materialization stated in the config block, and can the agent explain why it chose it?
Then test against your own project, not a demo. Give the agent three requests you have already built by hand: a staging model, a join, and a mart. Compare its output with the merged versions. Differences are informative. If it picked a different materialization, ask whether the agent or your team was right. If it ignored a naming convention, that tells you what context it was missing.
Finally, check what it leaves behind. A good agent leaves a reviewable diff, a record of what it ran, and no changes outside the files it was asked to touch. The dbt-core repository is the reference for how the compile and build steps behave, so your evaluation should use the same commands your CI uses.
Frequently Asked Questions
Can an AI agent write dbt models correctly?
It can write models that compile and follow the structure the project already uses, particularly when it starts from a known pipeline and known upstream models. Correctness in the business sense still depends on a clear definition from a person. We recommend treating agent output as a pull request from a fast junior teammate: useful, checked, and never merged unseen.
Which materialization will the agent choose?
It states one explicitly in the config block instead of relying on the default, which the dbt docs describe as a view. The five options are view, table, incremental, ephemeral and materialized view. For large or costly models we recommend a human confirm the choice, since it affects build time and warehouse spend.
Do I need to change my dbt project to use the agent?
No. The agent works against your existing project and conventions and runs inside Claude Code, Cursor or any MCP-compatible client. The open-source edition is read-only by default, so you can try it on a real project without granting write access, and move to Pro for writes with approvals when you are ready.
Does the agent replace analytics engineers?
No. It takes over the lookup and boilerplate work of creating a model: finding upstream models, writing refs and setting the config block. Analytics engineers keep the work that needs judgment, such as defining metrics, reviewing logic and deciding what is worth building at all.