dbt transformation patterns by hand vs with an AI agent

For dbt transformation patterns by hand vs with an AI agent, the practical difference is who enforces the conventions. The conventions themselves are the same ones in dbt's own guide, How we structure our dbt projects: staging models that clean sources, intermediate models that combine them, and marts that serve the business. By hand, an engineer enforces those rules model by model. With an agent, the layering, refs and materialization choices are applied consistently, and the engineer reviews the result.

Key Takeaways

• •The staging, intermediate and marts layering in dbt is a convention, not something dbt enforces. By hand, it holds only as long as reviewers catch violations.
• •The manual workflow breaks at scale in three places: inconsistent refs, mismatched materializations, and layer drift as the project grows.
• •An agent that knows the layering pattern can scaffold models, wire refs correctly and pick materializations per layer. Its output still goes through code review.
• •Humans keep the decisions that carry business meaning: grain, metric definitions, and approval to write to production.
• •Evaluate an agent on a real slice of your own project, not a demo: check refs, layer boundaries and materializations against your conventions.

dbt transformation patterns by hand vs with an AI agent: the job

The job is turning raw source tables into trustworthy models. Today an analytics engineer does it, often with a data engineer reviewing. The goal is a project where anyone can tell from a model's name and folder what it does and what it depends on.

dbt's guide describes three layers. Staging models sit closest to the source and do light cleanup: renames, casts and basic standardization, one model per source table. Intermediate models combine and reshape staging models for a specific purpose. Marts are the entity-level models that analysts and BI tools query. The guide pairs these layers with materialization advice, and the dbt materializations documentation covers views, tables, incremental models and ephemeral models.

That is our reading of those docs, and the exact materialization defaults you choose are a team decision. What matters here is that the pattern is well understood but not enforced by the tool. Someone has to apply it every time.

The manual workflow, step by step

Here is how the work usually goes when an engineer does it by hand, and where it tends to break.

• •Declare the source. The engineer writes the source YAML, then a staging model per source table. Breaks when column names and types are copied by hand and drift from the warehouse.
• •Build the staging layer. Rename, cast and standardize. Breaks when one engineer joins in staging because it was quicker, and the layer stops being one-to-one with sources.
• •Add intermediate models. Join and reshape. Breaks when the same join logic is rewritten in three places because nobody knew an intermediate model already existed.
• •Build marts. Reference intermediate models and expose a clean entity. Breaks when a mart reads straight from a source or from another mart, creating hidden dependencies.
• •Choose materializations. Views for light staging, tables for heavily queried marts, incremental for large event data. Breaks when the choice is made once and never revisited as data volume changes.
• •Review in a pull request. A reviewer checks refs, layer boundaries and naming by eye. Breaks because reviewers are busy and convention violations are easy to miss in a large diff.

None of these steps is hard. The cost is that they are repetitive and depend on attention. In our experience, the failure is rarely a wrong model on day one. It is a project that slowly stops following its own rules.

What an agent does instead

Our Pipeline Agent carries a skill called dw-dbt-transformation-patterns, which covers idiomatic dbt model layering: staging, intermediate and marts, with correct refs and materializations. We limit the claims here to that description. The agent applies the layering pattern, uses ref() and source() correctly, and picks a materialization appropriate to the layer.

Because every Data Workers agent is an MCP server, you invoke it from the client you already use. Claude Code, Cursor, Windsurf and VS Code all work, as does any MCP-compatible client. The Model Context Protocol is the open standard that makes this possible. In practice a request looks like a prompt in your terminal: scaffold the staging models for these three source tables, then propose an intermediate model for the orders-to-customers join.

The agent does not work in isolation. The Pipeline Agent chains with the catalog, quality and incidents agents. That is how a new model can be checked against existing definitions before it duplicates one, which addresses the manual failure where the same join is written three times. We covered how Data Workers keeps the catalog current in a separate post, and the same context is what lets an agent find the intermediate model that already exists.

Manual vs agent, side by side

We deliberately give no time-saved number in this table. We have not published a measured figure for dbt layering work, and a made-up one would not help you decide anything.

What still needs a human

An agent applying a pattern does not know what your business means. Four decisions stay with people.

• •Grain and entity definitions. What is one row in the orders mart? That is a business decision, not a naming convention.
• •Metric logic. Revenue, active user and churn have definitions your finance and product teams argue about. An agent should implement the agreed one, not invent it.
• •Approval to write. Our open-source edition (dw-claw, Apache 2.0) is read-only by default and runs locally. Pro adds writes, with approval workflows and audit trails. Even then, a person approves the change before it reaches production.
• •Exceptions to the pattern. Sometimes a mart should read a source directly, or a model should break the layering for a good reason. A human decides when the rule gives way.

This matches how we think about autonomy generally. Our Autonomous Data Conductor has a per-domain autonomy dial, so a team can start with the agent proposing changes and move toward more autonomy in areas where it has earned trust.

How to evaluate an agent for dbt layering

Do not judge an agent on a clean demo project. Use a slice of your own, messier one.

• •Pick three source tables and ask the agent to build staging through to one mart. Check that staging is one-to-one with sources and that no mart reads a source directly.
• •Check every ref() and source() against your lineage. A wrong ref is the failure that matters most.
• •Compare the materializations with what your team would have chosen, and read the reasoning for any differences.
• •Ask it to add a model that overlaps with an existing one. A good agent finds the existing model instead of duplicating it.
• •Confirm what it can and cannot write. If it can merge to main without review, that is a finding, not a feature.
• •Check the audit trail. You should be able to see what was proposed, what was approved and by whom.

If you already use Claude Code with dbt, the evaluation is quick because the agent shows up in the same terminal. We are not claiming the agent replaces the analytics engineer. It moves the engineer from writing the repetitive structure to reviewing it.

Frequently Asked Questions

Does dbt enforce staging, intermediate and marts layering? No. The layering is a recommended convention in dbt's project-structure guide. dbt will run a project that ignores it, which is why teams rely on review, naming rules or an agent to keep to it.

Can an AI agent write dbt models without a human reviewing them? Technically it can, but we do not recommend it for production. The agent can scaffold and propose models, while a person approves the grain, the metric logic and any write to production. Our open-source edition is read-only by default for this reason.

Which materialization should each dbt layer use? That is a team decision, and dbt's materializations documentation explains the tradeoffs between views, tables, incremental and ephemeral models. A common starting point is lightweight materializations for staging and persisted ones for heavily queried marts, adjusted as data volume grows.

Can I use Claude Code or Cursor to do this? Yes. Data Workers agents are MCP servers, so Claude Code, Cursor, Windsurf, VS Code and other MCP-compatible clients can invoke the Pipeline Agent from where your engineers already work.