Refactoring Legacy SQL in a dbt Project—Without Breaking Reports

You just took over a 600-model dbt repository. Nobody can say which tables BI depends on, nightly runs drift, and touching a single sql file can ripple through ten dashboards. Here’s the production workflow we use to clean it up without changing a number: map the DAG and find what nothing consumes, establish parity tests before edits, then ship small, reviewable slices—layering, naming, materializations, and deduplicating near-identical models. Use audit_helper to prove outputs match, roll out behind aliases, and retire safely. If you need outside help, Vertex Data Consulting has done this at scale; if you have a strong team, this is the refactoring course in miniature you can run in-house.

1) Map the DAG and inventory what nothing consumes

Build a complete picture of the dbt project and the warehouse relations it creates. Start with artifacts:

# Create docs artifacts
$ dbt docs generate

# Or list nodes with lineage
$ dbt ls --resource-type model --output json > nodes.json

Parse target/manifest.json to find each dbt model with no children (often safe to retire) and fan-out risk points.

import json
m = json.load(open('target/manifest.json'))
child_map = m['child_map']
models = [k for k,v in m['nodes'].items() if v['resource_type']=='model']
orphans = [n for n in models if len(child_map.get(n, []))==0]
print(len(orphans), 'models have no downstream dependents')

Cross-check with your data warehouse query history to catch external usage (BI tools, ad‑hoc sql queries, lingering stored procedures). Standardize dbt sources for raw data and stage tables early, including freshness tests:

version: 2
sources:
  - name: app
    database: RAW
    schema: APP
    tables:
      - name: orders
        freshness:
          warn_after: {count: 2, period: hour}

Tag layers (stg_, int_, dim_, fct_) and publish the map in GitHub. A data engineer and an analytics engineer should co-review this inventory: you’ll surface monolithic legacy code, duplicated joins, and shadow tables referenced directly by dashboards.

2) Prove parity before you touch anything

Provable parity is what makes dbt refactoring safe. Before changing a line of dbt code or starting a sql transformation, add tests that guarantee the new relation is identical to the old one. Use the dbt-labs/audit_helper package from dbt Labs for row-by-row comparison.

# packages.yml
packages:
  - package: dbt-labs/audit_helper

-- tests/compare_fct_orders.sql
{% set a = ref('fct_orders__old') %}
{% set b = ref('fct_orders__new') %}

{{ audit_helper.compare_relations(
    a_relation=a,
    b_relation=b,
    primary_key='order_id'
) }}

Add cheap smoke checks as data quality gates:

-- tests/fct_orders_parity.sql
with a as (select count(*) c, sum(total_amount) s from {{ ref('fct_orders__old') }}),
     b as (select count(*) c, sum(total_amount) s from {{ ref('fct_orders__new') }})
select 'rowcount' as check, a.c as old, b.c as new where a.c != b.c
union all
select 'amount_sum', a.s, b.s where a.s != b.s

Run these in CI on-demand for the changed nodes only:

$ dbt build --select state:modified+ --defer --state target/prev

Now refactoring legacy sql doesn’t require faith—the build fails if outputs drift. This protects stakeholders while you update transformation logic and lets data teams move faster with confidence.

3) Slice the work: layers, names, materializations, duplicates

Work in small pull requests that reviewers can reason about. Layering creates modularity and clear ownership:

• stg_: one‑to‑one source mappings, type casting, light cleanup; keep business logic out of stage.
• int_: joins and shared transformation steps used by multiple marts.
• dim_/fct_: the data model analysts query.

Prefer modular sql over monolithic legacy sql code: break giant subqueries into readable CTEs. If a sql file exceeds a few hundred lines, it’s a code smell. Centralize repeatable sql snippets as macros. Keep names stable with alias until cutover:

-- models/marts/fct_orders_v2.sql
{{ config(materialized='table', alias='fct_orders') }}
select * from {{ ref('int_orders_enriched') }}

Choose materializations deliberately. Views for stg_/small int_, tables for dims/facts, and incremental for big facts—plan the merge pattern and vacuum/cluster. See our guide on incremental strategies that hold up in prod. When migrating stored procedures into dbt, extract them into modular steps using ctes and small transforms first; prove parity, then simplify. This keeps business logic testable and the workflow reversible.

4) Compare outputs, roll out behind aliases, retire safely

For each slice, stand up the new relation side‑by‑side, compare with audit_helper, then flip the alias. Keep a rollback view for a stabilization window. A pragmatic rollout workflow:

1. Create fct_orders_v2 while fct_orders__old remains public.
2. Run parity in CI and a scheduled shadow job; add an on-demand job for testers.
3. Flip alias so fct_orders points to _v2; keep _old_bridge for rollback.
4. Watch downstream dashboards and warehouse errors for 3–7 days; measure runtime and scanned bytes.
5. Drop _old in a change window and search logs one last time for external references.

Keep pull requests tight and scoped (naming-only, materialization-only, dedupe-only). If performance regresses, inspect explain plans and see our notes on why dbt runs slow. For heavier repos, we run targeted audits—see dbt repo performance services. This is also the moment to migrate what’s safe while holding the line on outputs.

5) Keep numbers stable and stakeholders calm (FAQs)

• What’s “refactor” vs. rebuild? Refactor changes structure, not results. Rebuild changes results on purpose (new metrics or grain). Guard both with tests.
• Is dbt good for ETL? It’s the T in ELT inside your data warehouse. Use ingestion for E/L; do transformations here.
• Do CTEs hurt performance? Engines often inline them. Measure old vs. new on identical warehouses and inputs. Using ctes is mainly for readability and modularity.
• Why does dbt recommend CTEs? They localize logic, replace deeply nested subqueries, and make reviews safer.
• How should I model for BI? Star schemas, consistent keys, narrow columns, and pre-aggregations where needed to optimize queries in downstream tools.
• Is ChatGPT good at refactoring code? Helpful for drafts and renames, but never trust AI without parity tests—especially on legacy code.
• Python in dbt? Supported on several adapters; check dbt Labs docs for your engine.
• New to dbt or moving to an analytics engineering mindset? Pair seniors with juniors, codify reviews, and run a short bootcamp; we offer training & enablement.
• CLI/Core setup fast? Install adapter, configure profiles.yml, run dbt debug, then dbt build. Keep GitHub actions simple early.
• Refactor while you migrate? Yes—ship in slices with parity. Keep business logic stable as you consolidate the repository.

One more guardrail: bake parity tests into your CI workflow and treat them as blocking. Data leaders sleep better, and your team avoids regressions.

---

This playbook lets you modernize legacy sql code safely—model by model. Start with one high-impact table and a parity audit. If you want a second set of hands, contact Vertex.