The short version
To convert a SQL query to a dbt model, paste the query into a .sql file under models/ with no trailing semicolon and run it unchanged first. Then replace every hard-coded schema.table with {{ source() }}, lift each nested subquery into a named CTE (or a staging model you call with {{ ref() }}), and replace now() with a var() so the date is visible and can be overridden. Pick view or table, add a few tests, and finish with EXCEPT both ways against the original query: zero rows means the dbt model returns what the old SQL did.
I did that below with one legacy report on PostgreSQL 16.14, dbt-core 1.11.7 and dbt-postgres 1.10.0. The first comparison was not clean, and the reason is the useful part.
The legacy query
A customer tier report, the kind that lives in a .sql file on a shared drive: hard-coded erp. schema, a subquery inside a subquery, a CASE ladder, now() twice.
SELECT c.customer_id,
c.company_name,
r.region_name,
t.order_count,
t.revenue,
CASE
WHEN t.revenue >= 10000 THEN 'platinum'
WHEN t.revenue >= 5000 THEN 'gold'
WHEN t.revenue >= 1000 THEN 'silver'
WHEN t.revenue > 0 THEN 'bronze'
ELSE 'none'
END AS tier,
CASE WHEN t.last_order < now() - interval '30 days' THEN 'Y' ELSE 'N' END AS lapsing
FROM erp.customers c
JOIN erp.regions r ON r.region_id = c.region_id
JOIN (
SELECT o.customer_id,
count(*) AS order_count,
sum(l.line_total) AS revenue,
max(o.order_date) AS last_order
FROM erp.orders o
JOIN (
SELECT order_id,
sum(quantity * unit_price * (1 - discount)) AS line_total
FROM erp.order_lines
GROUP BY order_id
) l ON l.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'test')
AND o.order_date >= now() - interval '90 days'
GROUP BY o.customer_id
) t ON t.customer_id = c.customer_id
WHERE c.is_active = 'Y'
ORDER BY t.revenue DESC;
I loaded eight customers, 27 orders and 54 order lines into a scratch database, order_date as a date column. Run on 2026-10-25 at 20:46 UTC:
customer_id | company_name | region_name | order_count | revenue | tier | lapsing
1 | Acme Tools | North | 5 | 17519.77200 | platinum | N
2 | Baltic Freight | South | 2 | 2958.39050 | silver | Y
4 | Delta Print | West | 2 | 2480.00000 | silver | Y
3 | Cedar Dental | North | 3 | 1539.88750 | silver | N
6 | Fjord Marine | West | 3 | 811.54900 | bronze | N
(5 rows)
Step 1: run it in dbt unchanged
dbt's refactoring legacy SQL guide starts here: get dbt to run the existing SQL "with as few modifications as possible", because while refactoring you will move logic around but should not change it. I saved the query as models/legacy/customer_tiers_legacy.sql and ran dbt run. It failed:
Database Error in model customer_tiers_legacy (models/legacy/customer_tiers_legacy.sql)
syntax error at or near ";"
LINE 40: ;
^
dbt wraps the model in its own create view ... as (...), so the closing semicolon ends up inside the parentheses. Delete it and the copy runs:
20:47:07 1 of 1 OK created sql view model analytics.customer_tiers_legacy ............... [CREATE VIEW in 0.08s]
Keep this model as the "before". The guide recommends this alongside approach, refactoring a copy, partly because it makes auditing easier.
Step 2: hard-coded schema names become sources
erp.customers only works where the schema is called erp. Declare it as a source in a .yml file under sources: (schema defaults to the name; spelled out here for clarity):
# models/staging/_erp__sources.yml
sources:
- name: erp
schema: erp
tables:
- name: customers
- name: regions
- name: orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: order_lines
{{ source('erp', 'orders') }} compiles to the full table name and creates a dependency on the source table, so the raw tables show up in lineage. Sources take tests too.
Step 3: subqueries become CTEs, one becomes a staging model
The guide's CTE layout: import CTEs (a plain select * per source, filters allowed), logical CTEs named after the old subquery aliases, a final CTE, then select * from final. Nothing nested.
The innermost subquery, l, reads one table and needs no join: one row per order. That fits the guide's description of a staging model, so it became a file of its own:
-- models/staging/stg_erp__order_totals.sql
-- was the innermost subquery "l": one row per order
select
order_id,
sum(quantity * unit_price * (1 - discount)) as line_total
from {{ source('erp', 'order_lines') }}
group by order_id
The report model calls it with {{ ref('stg_erp__order_totals') }}. ref() fills in the schema from configuration and records the dependency, so dbt run builds the staging view before the model that reads it. Subquery t aggregates across orders and its result is used once, so it stays a CTE; the guide calls intermediate models optional, worth it when large flows come together.
Step 4: replace now() with a var
now() makes the report depend on when it ran, with no trace of that in the result. In dbt, var() takes an optional second argument as the default. I defaulted to the run's own date, run_started_at, a Python datetime in UTC:
{% set as_of = var('as_of_date', run_started_at.strftime('%Y-%m-%d')) %}
To rebuild the report for another day, pass the variable on the command line as a YAML dictionary; --vars overrides anything in dbt_project.yml and the default. My first translation was the obvious one: >= now() - interval '90 days' became >= date '{{ as_of }}' - 90, and < now() - interval '30 days' became < date '{{ as_of }}' - 30.
Did the dbt model return the same rows as the SQL?
After dbt run the new table had 6 rows to the legacy view's 5. EXCEPT returns the rows of the first query that are not in the second, so I ran it both ways:
SELECT 'only in legacy' AS side, * FROM (
SELECT * FROM analytics.customer_tiers_legacy
EXCEPT
SELECT * FROM analytics.customer_tiers
) a
UNION ALL
SELECT 'only in dbt', * FROM (
SELECT * FROM analytics.customer_tiers
EXCEPT
SELECT * FROM analytics.customer_tiers_legacy
) b
ORDER BY customer_id, side;
side | customer_id | company_name | region_name | order_count | revenue | tier | lapsing
only in dbt | 4 | Delta Print | West | 2 | 2480.00000 | silver | N
only in legacy | 4 | Delta Print | West | 2 | 2480.00000 | silver | Y
only in dbt | 8 | Harbor Cafe | South | 1 | 191.00000 | bronze | Y
(3 rows)
Harbor Cafe's only order is dated 2026-07-27, exactly 90 days back, and Delta Print's last order is 2026-09-25, exactly 30 days back. Both sit on a boundary. now() returns a timestamp with time zone, the start of the current transaction, so now() - interval '90 days' carries the time of day. Compared with a date, this is what Postgres did:
cutoff | included | as_timestamp
2026-07-27 20:47:44.035366+00:00 | False | 2026-07-27 00:00:00+00:00
The date counts as midnight, which is earlier than 20:47, so an order on the boundary day was out of the window and a last order on the 30-day boundary was already "lapsing". Whoever wrote >= meant to include that day; the query never did, except when run at exactly midnight. date - integer gives a plain date with no time, so the faithful translation flips both comparisons:
where o.order_date > date '{{ as_of }}' - 90
...
case when t.last_order <= date '{{ as_of }}' - 30 then 'Y' else 'N' end as lapsing
That reproduces what the old query actually returned, bug included; fixing the business rule is a separate change, made after parity, with the people who read the report. One caveat: run_started_at is UTC, and my server's TimeZone was Etc/UTC too. If yours is not, now() and the var can disagree about the date for a few hours a day. Rerun, compare again:
side | customer_id | company_name | region_name | order_count | revenue | tier | lapsing
(0 rows)
dbt_rows | legacy_rows
5 | 5
EXCEPT drops duplicates, so I compared counts too; without a key, use EXCEPT ALL. My data had boundary rows because I put them there. Put some in yours.
One more thing the var exposed
Testing a backfill with --vars '{as_of_date: "2026-09-30"}' counted October orders in a September report: the old query had no upper bound because now() was always the latest moment. I added and o.order_date <= date '{{ as_of }}'. With today's date it changes nothing (the parity check above was run after it); for 2026-09-30 it returned 6 rows, Acme Tools down from platinum to gold with 3 orders instead of 5.
The finished model and its compiled SQL
dbt compile --select customer_tiers prints the SQL dbt will send, and writes it under target/ without building anything. The model file has {{ config(materialized='table') }} on top; the compiled output, trimmed:
with customers as (
select * from "sql_to_dbt_one_query_becomes_one_model"."erp"."customers"
where is_active = 'Y'
),
...
order_totals as (
select * from "sql_to_dbt_one_query_becomes_one_model"."analytics"."stg_erp__order_totals"
),
...
where o.order_date > date '2026-10-25' - 90
and o.order_date <= date '2026-10-25'
...
case when t.last_order <= date '2026-10-25' - 30 then 'Y' else 'N' end as lapsing
...
select * from final
The date is now in the SQL, readable in target/ after every run. The ORDER BY is gone: without one, Postgres returns rows in an unspecified order, and the BI tool or the final query sorts.
View or table?
dbt models are views by default. The docs suggest views for light work like renaming or recasting, and tables for models queried by BI tools. So the staging model stays a view and customer_tiers is a table, which target/run/ shows as a create table ... as. A table does not pick up new source rows until the next dbt run, which suits a dated report.
A couple of tests
unique and not_null on customer_id, and accepted_values on tier and lapsing, in models/marts/_marts.yml. From dbt 1.10.5 the values go under arguments:. A test is a select for failing rows, so a pass means zero came back:
20:47:54 2 of 6 PASS accepted_values_customer_tiers_tier__platinum__gold__silver__bronze__none [PASS in 0.03s]
...
20:47:54 Done. PASS=6 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=6
None of these would have caught the boundary bug. Tests check rules; only the comparison with the old query checks the answer.
When it is more than one query
A read-only query is the easy case. For a procedure full of INSERT, UPDATE and MERGE, see converting a stored procedure to dbt models. For sources and staging across a whole migrated schema, see dbt sources and staging models after a migration.