Refactoring a Legacy Query

Concepts covered: Models as queries

The finance dashboard runs a 220-line query pasted into the dashboard tool 3 years ago, and no one has changed it since. It joins orders to payments through a subquery, filters test accounts with a hard-coded list of ids and a pattern on email, and computes monthly revenue per customer. Refactoring it into models is this section's work, under 1 rule: the new models return the same rows as the old query on every day until the old query is deleted.

SELECT
  c.id AS customer_id,
  date_trunc('month', o.created) AS revenue_month,
  SUM(p.amount_cents) / 100.0 AS revenue
FROM raw.customers c
JOIN raw.orders o
  ON o.cust = c.id
JOIN (
  SELECT
    order_id,
    SUM(amount) AS amount_cents
  FROM raw.payments
  WHERE status = 'captured'
  GROUP BY order_id
) p
  ON p.order_id = o.id
WHERE c.email NOT LIKE '%@example.com'
  AND c.id NOT IN (104, 2231, 9087)
GROUP BY c.id, date_trunc('month', o.created)

Step 1: park the query as a model

Copy the query into models/legacy/legacy_customer_revenue.sql unchanged, apart from swapping each raw table name for a source() call, and materialize it as a table. dbt now builds the result the dashboard trusts on the same schedule as everything else, and that table becomes the reference every later step is measured against. The dashboard can read it at once, which retires the pasted copy on day 1.

Step 2: carve out the layers

Start from the tables the query reads, then follow its joins and its grain. 3 raw tables become 3 staging models, each renaming and casting 1 table, and the test-account filter moves into stg_customers, the only place it belongs. The subquery that sums captured payments per order is 1 grain, 1 row per order, so it becomes int_order_payments, joining orders to their captured total. The outer query sums per customer per month, so it becomes fct_customer_monthly_revenue, 1 row per customer per month, the grain the dashboard reads. Each model does the work the legacy query did at that level and nothing from the levels around it.

Where to cut

A subquery with a GROUP BY is a grain of its own and becomes a model. An expression repeated in 2 places, such as date_trunc('month', o.created) in the select and the group by, becomes 1 column, revenue_month, computed once in int_order_payments. A hard-coded list of ids becomes a seed, so the finance team edits a CSV when a new test account appears, and the staging model joins against it.

select
    customer_id,
    revenue_month,
    sum(captured_cents) / 100.0 as revenue
from {{ ref('int_order_payments') }}
group by
    customer_id,
    revenue_month

Step 3: audit until the difference is 0 rows

SELECT
  'legacy_only' AS side,
  customer_id,
  revenue_month,
  revenue
FROM (
  SELECT
    customer_id,
    revenue_month,
    revenue
  FROM analytics.legacy_customer_revenue
  EXCEPT
  SELECT
    customer_id,
    revenue_month,
    revenue
  FROM analytics.fct_customer_monthly_revenue
) AS legacy_only
UNION ALL
SELECT
  'new_only' AS side,
  customer_id,
  revenue_month,
  revenue
FROM (
  SELECT
    customer_id,
    revenue_month,
    revenue
  FROM analytics.fct_customer_monthly_revenue
  EXCEPT
  SELECT
    customer_id,
    revenue_month,
    revenue
  FROM analytics.legacy_customer_revenue
) AS new_only

The query returns every row in 1 result and missing from the other, tagged by side. 0 rows means the 2 results match for every customer in every month, down to the amount. The first run rarely returns 0. The differences repeat across refactors: a left join written as an inner join drops customers whose orders have no captured payment, a join to payments at the wrong grain doubles an order with 2 payments, round applied per order instead of per month moves cents, and 1 test id in the hard-coded list is missing from the seed. Each difference names a model to fix. After each fix, rebuild the chain and rerun the audit until it is empty. Saving the audit as a view called audit_revenue keeps the query in the project, but building a view only stores the query: nothing runs it, and a non-empty result fails nothing. The proof is a select count(*) from audit_revenue, run after each scheduled build and read by the engineer doing the refactor.

Step 4: swap, then delete

Point the dashboard at fct_customer_monthly_revenue, under a contract that fixes the 3 column names and types the panels read. After the next scheduled run, audit_revenue should still return 0 rows; query it again to confirm. Then delete legacy_customer_revenue.sql and audit_revenue.sql, and drop both relations by hand, as the previous section showed. The 220 lines are now 5 models plus 1 seed. Each model sits at 1 grain and is short enough to read in a minute, and you can build and check any of them on its own.

About this lesson

This section is part of the Models: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.