dbt Copilot

01

The Problem

On a data team that owns dbt, a large share of the week goes to answering questions about the project itself. Where does this column come from? If I change this staging model, which dashboards move? Is this mart safe to use for the board deck? The answers already exist in manifest.json and run_results.json, but getting them means clicking through dbt docs, grepping SQL, and remembering which tests failed last night.

Generic chat assistants don't help much here. They don't know the project, and when they guess they invent model names and columns. The goal was an assistant that is grounded: it can only state what the project's artifacts say, and it shows its work.

"Answer from the artifacts, never from memory, and show every lookup behind the answer."

02

How It Works

01

Real dbt artifacts

The demo runs on a dbt project I built for it: 7 sources, 20 models across staging, intermediate and mart layers, 37 tests, an exposure for an executive dashboard, and a few problems planted on purpose. It runs on DuckDB, so dbt build and dbt docs generate produce genuine manifest, run results and catalog files.

02

A graph and health engine

A TypeScript engine loads the artifacts into a DAG, resolves loose names ("the delinquency mart") to real nodes, walks lineage in both directions, computes the blast radius of a change, and runs health checks an analytics engineer would run in review.

03

Claude with six read-only tools

A serverless function gives Claude list_models, search, get_model, get_lineage, get_impact and get_health. The system prompt tells it to answer only from tool results. The loop is capped at six rounds, and on the last one tool use is switched off so it must answer.

04

Answers you can check

Each answer lists the tool calls it made, highlights the nodes it used in the lineage graph, and links every model name to its columns, tests, flags and SQL.

05

Works without the LLM too

If the API isn't reachable, a rule-based router answers the common question shapes with the same tools. Visitors can also load their own manifest.json; it is parsed in the browser and never uploaded.

03

The Tool Loop

The core is short on purpose. Tools run against the in-memory project, errors go back to the model so it can recover, and every call is recorded for the audit trail.

server/chat.ts · grounded tool loop

for (let round = 0; round < MAX_ROUNDS; round++) {
  const res = await client.messages.create({
    model, max_tokens: 1200, system: systemPrompt(project), tools: TOOL_DEFS,
    // last round: no more tools, write the answer
    ...(round === MAX_ROUNDS - 1 ? { tool_choice: { type: "none" } } : {}),
    messages,
  });
  const toolUses = res.content.filter((b) => b.type === "tool_use");
  if (res.stop_reason !== "tool_use") return { text: textOf(res), focus, trace };

  messages.push({ role: "assistant", content: res.content });
  messages.push({ role: "user", content: toolUses.map((tu) => {
    try {
      const { result } = executeTool(project, tu.name, tu.input);
      trace.push({ tool: tu.name, input: tu.input, ok: true });
      return { type: "tool_result", tool_use_id: tu.id, content: JSON.stringify(result) };
    } catch (e) {
      return { type: "tool_result", tool_use_id: tu.id, content: e.message, is_error: true };
    }
  }) });
}

04

Health Checks

The checks encode the review I'd do before trusting a model. Each flag carries a severity and a concrete fix.

Check	Why it matters
Failing or warning tests, failed or skipped runs	The model's data is wrong right now.
Exposure at risk	A dashboard sits downstream of a failure or a warning.
No tests, or grain not tested	Duplicates from a fan-out join would pass silently.
Mart reads a raw source	Staging logic is skipped or copied.
Source without freshness	A stalled load would go unnoticed.
Missing model or column docs	Nobody can use the model without asking its author.
Unused staging model, select * in a mart, incremental without unique_key	Wasted build time, unreviewed schema changes, duplicates.

The planted problems in Harbor Lease include a failing accepted_values test (a new lease status nobody added to the list), a warning on null payment methods, a mart that reads a raw source, and an undocumented, untested model that nothing uses. The engine finds all of them, and a lineage-aware question like "Can I trust the executive dashboard?" surfaces a test warning four models upstream.

05

Design Decisions

01

Tools over retrieval. Lineage is a graph question, so embedding chunks of SQL and hoping the right ones come back is the wrong shape. Tools return exact facts, and when a name doesn't exist the tool says so instead of the model inventing one.

02

Read-only by construction. The assistant has no warehouse connection and no tool that writes anything. The worst a bad answer can do is be wrong, and the audit trail makes that easy to spot.

03

Same facts in both modes. The offline router and Claude call the same functions, so switching modes changes the wording, not the facts. It also made the engine easy to unit test.

04

Cheap and bounded. It runs on Claude Haiku by default, with per-visitor rate limits, an origin allowlist and a capped loop. A typical question costs about a tenth of a cent at list prices.

06

What I'd Build Next

Column-level lineage by parsing compiled SQL with sqlglot, so "where does collection_rate come from" traces through expressions, not just models. A pull-request bot that comments the blast radius and new health flags on every dbt PR. Read-only query execution against a warehouse, with SQL validation, to answer "why did this metric change" and not just "how is it built".