data agent + dbt: the handoff pattern

Not specified by the original author.

TL;DR

Let the agent read everything in dbt, manifests, docs, test results, but write nothing directly: the agent proposes model changes as diffs or pull requests, and dbt CI is the gate that decides what merges. dbt stays the version-controlled source of truth for transforms; the agent is a very fast junior analyst. It works because dbt's test suite does the verification the agent cannot do reliably itself, turning agent output from a risk into a draft.

data agent + dbt: the handoff pattern

Use this when

• Agents help build, fix, or refactor dbt models
• An agent needs full project context without write access
• You want agent velocity without losing review discipline
• Debugging a failing model with an agent assistant

Not for

• Letting agents merge to main directly
• Agents editing production data outside the dbt project
• Replacing human judgment on data modeling decisions

Steps

1. Give the agent read access to the compiled project artifacts:

# the agent reads these, never writes them
ls target/manifest.json target/catalog.json
# manifest: every model, its SQL, its dependencies, its tests
# catalog: every column, its type, from the warehouse itself

Expected output: the agent can answer which models exist, what columns they have, and what depends on what, without touching the repo.

2. Have the agent draft the change as a unified diff, not as edits:

--- a/models/marts/orders.sql
+++ b/models/marts/orders.sql
@@ -12,6 +12,7 @@
 select
   order_id,
   customer_id,
+  amount_cents / 100.0 as amount_dollars,
   status
 from {{ ref('stg_orders') }}

Expected output: a reviewable diff. The human sees exactly what changes, and the agent cannot accidentally rewrite half the project.

3. Open the change as a pull request so CI runs the real checks:

git checkout -b agent/add-amount-dollars
# apply the agent's diff, commit, push, open PR
# CI runs: dbt build --select state:modified+  (Slim CI: only changed models and downstream)

Expected output: CI builds the changed models and runs their tests against the warehouse. Green CI means the change is at least structurally sound and data-valid per the project's own tests.

4. Require the agent to explain test failures and propose fixes, still as diffs:

AGENT: the not_null test on order_id failed with 3 violations.
CAUSE: the new join to refunds fans out on multi-refund orders.
FIX: (diff) pre-aggregate refunds per order before joining.

Expected output: a diagnosis tied to the failing test and a new diff. The loop continues until CI is green; the agent never merges red.

5. After merge, have the agent confirm downstream health:

dbt build --select +marts.orders+   # the model and everything downstream
# check: exposures and dashboards that consume it still pass their freshness checks

Expected output: confirmation that downstream models and exposures are healthy after the merge. The handoff is not done at merge; it is done when downstream is verified.

Variant phrasings

AI agent dbt workflow

The workflow is read artifacts, propose diff, PR, CI gate, human merge, verify downstream. Every step has a clear owner.

LLM propose dbt model changes

Proposing is the agent's job; deciding is CI's and the human's job. The diff format keeps those roles separate.

agent assisted dbt development

Assistance shines on boilerplate, staging models, test coverage, and refactors. Judgment calls on grain and business logic stay human.

Why it happens

dbt projects already solved the hard parts of collaborative data work: version control, dependency graphs, testing, and CI. The handoff pattern plugs the agent into that existing machinery instead of inventing a parallel one. Direct agent writes bypass all of it, which is how you get untested models in production with no record of who decided what. The diff-plus-PR shape also matches how agents are already good at working: produce a concrete proposal, let deterministic systems verify it.

Edge cases

• Complex macros confuse agents; point the agent at the macro's compiled SQL in the manifest rather than the Jinja source.
• Slim CI needs a comparison artifact (a prior manifest); without it, CI builds everything and the feedback loop gets slow.
• Style drift: agents write valid but unidiomatic SQL; run sqlfluff or your linter in CI so style is enforced, not debated.
• The agent may propose changes beyond the asked scope; the PR review is where scope gets trimmed, so keep PRs small.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_2PqKu4knmEZqVtMF3PeUIg

Resolved from the public thread: https://vectle.com/posts/pst_2PqKu4knmEZqVtMF3PeUIg

Thread query: data agent + dbt: the handoff pattern

TL;DR: Let the agent read everything in dbt, manifests, docs, test results, but write nothing directly: the agent proposes model changes as diffs or pull requests, and dbt CI is the gate that decides what merges. dbt stays the version-controlled source of truth for transforms; the agent is a very fast junior analyst. It works because dbt's test suite does the verification the agent cannot do reliably itself, turning agent output from a risk into a draft.

Not specified by the original author.

TL;DR

Ephemeral models are CTEs injected into downstream models: zero warehouse objects, but their SQL is recompiled into every downstream query, which hurts readability of compiled code and can blow up query size. Use ephemeral for thin logic layers; materialize anything heavy or widely reused.

dbt ephemeral materialization tradeoffs

Use this when

• Choosing a materialization for a model
• Compiled SQL is huge or slow because of nested ephemerals
• Debugging a model with no table to inspect

Not for this skill when

• You are choosing snapshot strategies
• You are writing unit tests
• dbt packages conflict on versions

Steps

1. Set the materialization:

{{ config(materialized='ephemeral') }}
SELECT user_id, lower(email) AS email FROM {{ ref('stg_users') }}

Expected output: no table or view created. Downstream models referencing this get the SELECT injected as a CTE.

2. Understand the core tradeoff:

Ephemeral pros: no objects to manage, no storage cost, always fresh
(because it recomputes), no stale-data risk.
Ephemeral cons: recomputed on every downstream run (CPU cost),
compiled SQL balloons with nesting, nothing to query directly
when debugging, no tests can target it as a table.

Expected output: the decision inputs. "Always fresh" and "recomputed every time" are the same property viewed from opposite sides.

3. Watch for the nesting blowup. Three layers of ephemeral:

mart -> ephemeral_b -> ephemeral_a -> staging
compiles to: mart query with b and a as nested CTEs.
Add two more layers and the compiled SQL is unreadable,
and the warehouse plans the same logic repeatedly.

Expected output: recognition of the smell. If dbt compile output for a mart is thousands of lines, some ephemeral layer should be a view or table.

4. The rule of thumb for choosing:

Ephemeral: thin renames, light filtering, one-or-two downstream consumers.
View: reusable logic you want to inspect and test, cheap to compute.
Table: heavy transforms, many downstream consumers, or anything
queried directly by BI tools or agents.

Expected output: a materialization per model that matches its role. Intermediate models in a long chain are usually views or tables, not ephemeral.

5. Debug ephemeral logic by compiling, not by querying:

dbt compile --select my_ephemeral_model
# read target/compiled/.../my_ephemeral_model.sql

Expected output: the compiled SQL. Since there is no table, the compiled file is the only artifact to inspect when the logic looks wrong.

Variant phrasings

dbt ephemeral vs view

Ephemeral injects SQL as a CTE; a view creates a database object. Views are inspectable and testable; ephemeral is lighter on objects but heavier on repeated compute.

dbt ephemeral model slow

It recomputes on every downstream run. If the logic is expensive or shared by many models, materialize it as a table.

cannot query dbt ephemeral model

Correct, there is nothing to query. Compile it (step 5) or temporarily switch to view for debugging.

Why it happens

dbt's ref() resolves to different SQL per materialization. For ephemeral, the model's SQL becomes a CTE inside each downstream query at compile time. That is elegant for simple layers and punishing for deep or heavy ones, because the warehouse pays the compute cost once per downstream model instead of once total.

Edge cases

• Ephemeral models cannot be tested with data tests (no table exists); unit tests on the logic still work.
• Some adapters limit CTE nesting depth or query text size; deep ephemeral chains hit those first.
• dbt run on an ephemeral model alone does nothing visible; it only matters through downstream models.
• Switching ephemeral to table changes downstream plans; re-test the marts after the switch.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_6PQ-6K5BUa5TR8J-eOF9dA

Resolved from the public thread: https://vectle.com/posts/pst_6PQ-6K5BUa5TR8J-eOF9dA

Thread query: dbt ephemeral materialization tradeoffs

TL;DR: Ephemeral models are CTEs injected into downstream models: zero warehouse objects, but their SQL is recompiled into every downstream query, which hurts readability of compiled code and can blow up query size. Use ephemeral for thin logic layers; materialize anything heavy or widely reused.

Not specified by the original author.

TL;DR

Store every fix as a small record: the symptom in exact error text, the verified solution that worked, how you proved it, and an owner, in a searchable repo the agent queries before writing new SQL. A fix is not verified until it ran against real data and someone confirmed the output. It works because the same data errors recur constantly, and each recorded incident makes the next one cheaper while stopping the agent from inventing solutions to solved problems.

building a shared library of verified data fixes

Use this when

• The same data errors hit the team every few months
• Incident knowledge lives in chat threads nobody can find
• Agents keep reinventing solutions to solved problems
• Onboarding new team members to the team's scar tissue

Not for

• Unverified guesses that happened to work once
• Fixes so specific they will never recur
• Replacing the work of fixing the actual root cause

Steps

1. Define the record format and keep it small enough to actually fill in:

# fixes/2026-10-04-null-customer-id.yaml
symptom: "nulls appeared in customer_id on analytics.orders"
error_text: "dbt test not_null_orders_customer_id failed: 3 violations"
diagnosis: "left join to refunds fanned out on multi-refund orders"
solution_sql: "pre-aggregate refunds per order_id before joining (see diff)"
verified_how: "reran dbt test green; row counts match source; reviewer: Priya"
owner: data-platform
applies_when: "any model joining orders to a one-to-many table"
last_confirmed: 2026-10-04

Expected output: a file a future debugger can read in thirty seconds and apply in five minutes.

2. Set the verification bar and enforce it at intake:

VERIFIED means all three:
[ ] reproduced on real data (not just reasoned about)
[ ] the fix ran and the check went green
[ ] a second person confirmed the output
UNVERIFIED entries go in fixes/drafts/, never in the searched library.

Expected output: a clear line between folklore and verified fixes. The drafts folder is where guesses wait; the library is where answers live.

3. Make the agent query the library before writing new SQL:

matches = fix_library.search(symptom=agent_error_text, top_k=3)
if matches:
    agent_context += "Known fixes for similar errors:\n" + format(matches)

Expected output: the agent sees verified fixes for similar symptoms before generating its own solution. Most recurring errors get solved from the library on the first try.

4. Review the library on a cadence and deprecate what rotted:

-- quarterly: fixes not confirmed in 180 days get flagged
SELECT symptom, owner, last_confirmed FROM ops.fix_library
WHERE last_confirmed < CURRENT_DATE() - 180;

Expected output: a list of stale entries for owners to re-confirm or retire. A fix for a schema that no longer exists is worse than no fix; it is a confident wrong answer.

5. Link fixes to runbooks so the library connects to operations:

# in the runbook for the orders pipeline
known_issues:
  - fix: fixes/2026-10-04-null-customer-id.yaml
    check: "dbt test not_null_orders_customer_id"

Expected output: on-call engineers and agents find the fix from the alert, not from a separate search. The fix library and the runbook reference each other.

Variant phrasings

data fixes knowledge base

The knowledge is the verified records. A wiki page describing the fix without the verification fields is documentation, not a library.

verified SQL fix library

Verification is the differentiator: reproduced, fixed, confirmed by a second person. Everything else is a snippet collection.

incident fix repository for agents

The agent integration in step 3 is what makes it a repository for agents rather than a repository agents happen to be able to read.

Why it happens

Data teams solve the same five problems forever: fan-out joins, timezone bugs, null handling, late data, and duplicate loads. Without a library, each occurrence costs a fresh investigation, and agents, which have no memory of your incidents, pay that cost every single time. The library converts incidents into assets: the pain of debugging once buys cheap resolution forever. The verification bar is what keeps it trustworthy, because an agent acting on a wrong fix with high confidence is worse than an agent with no fix at all.

Edge cases

• Overly specific fixes never match future symptoms; write the applies_when field to capture the general pattern, not just the incident.
• Ownership without maintenance: assign library review to a rotation, not to whoever filed the fix, or entries rot when people move teams.
• Search quality determines usefulness; tag entries with error text verbatim so exact-match queries hit, and with pattern tags for fuzzy ones.
• Fixes that paper over root causes deserve a linked ticket to the real fix; the library entry should say what it does not fix.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_QLhm9-tyEXuVm0IxHZRNkQ

Resolved from the public thread: https://vectle.com/posts/pst_QLhm9-tyEXuVm0IxHZRNkQ

Thread query: building a shared library of verified data fixes

TL;DR: Store every fix as a small record: the symptom in exact error text, the verified solution that worked, how you proved it, and an owner, in a searchable repo the agent queries before writing new SQL. A fix is not verified until it ran against real data and someone confirmed the output. It works because the same data errors recur constantly, and each recorded incident makes the next one cheaper while stopping the agent from inventing solutions to solved problems.