Refactor a Monolithic dbt Model into Layered Staging and Marts

Splitting that model into staging, intermediate, and mart layers is exactly the kind of mechanical-but-fiddly refactor that repo-scale AI agents do well — provided you pin the output to a parity check so "refactored" cannot quietly mean "changed."

When to reach for this

Use this when a model has grown past comfortable review size, mixes source cleanup with business logic, or duplicates transformations that belong in a shared staging layer. It also fits enforcing naming and style conventions across many models at once.

Triggers:

• •
  A model you are afraid to modify because nobody fully understands it.
• •
  The same source-cleaning CTE copy-pasted across three or more models.
• •
  A new team style guide (naming, folder structure) that existing models violate.

The workflow

1. 1.

   Snapshot current behavior first. Before touching anything, materialize the model's output to a comparison table. This is your ground truth for the entire refactor:

   Copy

   CREATE TABLE audit.fct_revenue_before AS
   SELECT * FROM fct_revenue;
   

2. 2.

   Have the agent map the model before it edits. In a repo-aware agent (Claude Code, Cursor), ask for analysis only:

   Copy

   Read models/marts/fct_revenue.sql. Do not edit anything yet.
   Produce: (1) a list of every source/ref it touches, (2) each CTE with a
   one-line purpose, (3) which CTEs are source cleanup vs business logic,
   (4) transformations duplicated elsewhere in models/ (search the repo).
   

   Read this map critically — it is also your review artifact.

3. 3.

   Prompt the refactor with explicit layer rules.

   Copy

   Refactor fct_revenue.sql into our layered structure:
   - stg_<source>__<entity>.sql: renaming, casting, basic cleanup only.
     One staging model per source table. Reuse existing staging models
     where they already exist — do not create duplicates.
   - int_revenue__<step>.sql: multi-source joins and intermediate logic.
   - fct_revenue.sql: final mart, same columns and same grain as today.
   Rules: the final SELECT must produce byte-identical output to the
   current model. No logic changes, no "fixes", no renamed output columns.
   Follow the naming conventions in models/staging/README.md.
   List every file you create or modify before writing any of them.
   

4. 4.

   Review the file plan before accepting edits. Check for staging models that duplicate existing ones and for intermediate models with no second consumer (usually unnecessary indirection).

5. 5.

   Prove parity — the non-negotiable step. Build the refactored DAG, then compare against the snapshot:

   Copy

   SELECT COUNT(*) FROM (
     SELECT * FROM fct_revenue
     EXCEPT
     SELECT * FROM audit.fct_revenue_before
   );
   

   Run the EXCEPT in both directions. Both counts must be zero. Any nonzero result means the agent "improved" something — diff a few offending rows, paste them back, and instruct: "restore exact parity; explain what you changed and why."

6. 6.

   Only then apply intentional improvements. Once parity holds, deliberate changes (fixing a discovered bug, deduplicating logic) go in as separate commits with their own review — never mixed into the refactor commit.

Verify before you trust it

• •
  Bidirectional EXCEPT between old and new output returns zero rows both ways.
• •
  dbt build passes for the whole refactored subgraph, including existing downstream models.
• •
  Row counts and one aggregate (e.g., SUM(revenue) by month) match the snapshot exactly.
• •
  dbt ls --select fct_revenue+ shows downstream consumers still resolve — no ref was renamed out from under them.
• •
  Every new staging model is referenced by at least one other model; no orphans.
• •
  Compilation of downstream models produces identical SQL where they were untouched (dbt compile diff).

Pitfalls

• Silent "bug fixes" during refactor. The most common failure: the agent notices a suspicious filter and corrects it. Sometimes it is a bug — but changing it during a structural refactor destroys your ability to review either change.
• Duplicate staging models. Agents create stg_orders_new.sql beside an existing stg_orders.sql rather than reusing it. The step-2 repo search and step-4 file-plan review exist to catch this.
• Grain drift through join reordering. Moving a join into an intermediate model can change effective join order and fan out rows. The EXCEPT check catches the symptom; a grain test on the mart catches the cause.
• Non-deterministic columns breaking parity.current_timestamp or run-id columns make EXCEPT fail spuriously. Exclude them from the comparison explicitly, and say so in the audit notes.
• Half-migrated conventions. The agent renames columns in staging but misses one consumer, which now silently selects a NULL. The full-subgraph dbt build is what surfaces this.

Tools

• Claude Codestrongest at multi-file repo refactors with instruction-following on "no logic changes"; runs your dbt commands in the loop.
• Cursorsame class of agentic editing with tight IDE diff review, good when you want to approve each file interactively.
• dbt Copilotuseful for regenerating docs/tests on the new layers afterward; less suited to driving the multi-file split itself.

Client-safe variant

For client-owned dbt repos, run the agent inside approved tenancy (Bedrock or Azure OpenAI in the client's cloud, wired into your IDE via their approved gateway) or use a local coding model through Ollama for the mapping and split steps. Strip client identifiers before prompting: schema names, source system names, and any business terms that identify the client become generic placeholders, restored via search-and-replace after generation. The parity proof runs entirely inside the client's warehouse with no AI involvement, so the evidence that the refactor is safe never leaves their boundary.