Walk into most data teams and ask where the “revenue” number comes from. You will get three different answers, two of which are wrong, and nobody will be able to tell you which one the CFO’s dashboard uses. The query that produces it lives in a Looker dashboard, or a notebook, or a Slack thread from eight months ago, and it has a WHERE status != 'test' filter that someone added once and never documented.
This is not a people problem. It is a tooling problem. Analytics SQL has been stuck in the stone age while application code got version control, tests, code review, and CI. dbt is the tool that finally drags SQL into the same century. It does not replace your warehouse and it does not invent a new query language. It just makes the transformation layer behave like software.
The Naive Setup and Where It Breaks
Here is how almost every analytics stack starts. You have raw data landing in a warehouse (BigQuery, Snowflake, Redshift, whatever). Someone needs a metric. They write a query. It works. Six months later you have 300 of these queries scattered across tools.
-- Lives in a dashboard. Nobody else knows it exists.
SELECT
date_trunc('month', o.created_at) AS month,
sum(oi.quantity * oi.unit_price) AS revenue
FROM raw.orders o
JOIN raw.order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
AND o.is_test = false
GROUP BY 1;
Harmless enough on its own. The problem is what happens around it:
- The next analyst needs revenue too. They cannot find this query, so they write their own. Their version forgets
is_test = false. Now two dashboards disagree by 3%. - The
orderstable gets a newrefund_amountcolumn. Nobody updates the revenue logic because nobody knows how many copies exist. - A raw table changes its schema upstream. Something breaks silently. You find out when sales asks why the board deck shows zero.
- A new hire asks “what feeds this chart?” and the honest answer is “I have no idea, let me spend a day finding out.”
The naive setup has no single source of truth, no tests, no lineage, and no way to change anything safely. Every query is an island. This is spaghetti SQL, and it does not scale past a handful of people.
The instinct to fix this is usually stored procedures or a pile of scheduled CREATE TABLE AS scripts. That is better than nothing, but it trades one mess for another: stored procedures live in the database, not in git, so you still have no review, no tests, and no diff. You have moved the spaghetti, not untangled it.
What dbt Actually Changes
dbt (data build tool) does one thing: it takes SELECT statements and turns them into tables and views in your warehouse, in the right order, with tests and documentation attached. That is it. You write SQL, dbt handles the CREATE TABLE, the dependency ordering, and the orchestration.
The leverage comes from four shifts that each sound small and together change everything.
| Old world | dbt world |
|---|---|
| Queries scattered across tools | All transformations in one git repo |
| Copy-paste reuse | ref() and modular models |
| “Looks right to me” | Automated tests on every model |
| Tribal knowledge | Generated lineage graph and docs |
A dbt “model” is just a .sql file containing a single SELECT. The filename becomes the table or view name. You never write CREATE TABLE. dbt wraps your SELECT in the correct DDL based on a config.
-- models/marts/finance/revenue.sql
{{ config(materialized='table') }}
SELECT
date_trunc('month', order_date) AS month,
sum(net_amount) AS revenue
FROM {{ ref('fct_orders') }}
WHERE is_completed
GROUP BY 1
The {{ ref('fct_orders') }} is the whole trick. More on that below.
The ref Pattern Is the Point
The single most important idea in dbt is ref(). Instead of hardcoding table names, you reference other models by name:
-- Bad (what you do without dbt):
FROM analytics.prod.fct_orders
-- Good (what you do with dbt):
FROM {{ ref('fct_orders') }}
This looks cosmetic. It is not. Three things fall out of it for free.
Dependency graph. Because every model declares which other models it reads from via ref(), dbt knows the full dependency tree. It builds models in topological order. You never think about “what runs before what” again. If revenue refs fct_orders, dbt guarantees fct_orders is built first.
Environment isolation. ref() compiles to a fully-qualified name based on your target. In production it resolves to analytics.prod.fct_orders. In your dev environment it resolves to analytics.dev_chirag.fct_orders. Same code, different schema, zero changes. You can build the entire pipeline into your own sandbox and nobody notices.
Lineage. The ref() edges are exactly the lineage graph. dbt renders it automatically. When someone asks “what breaks if I change this raw table,” you have an answer in two seconds instead of a day.
Here is what the dependency graph looks like conceptually:
raw.orders ──┐
├──> stg_orders ──┐
raw.order_items┘ ├──> fct_orders ──> revenue ──> [dashboard]
│
raw.customers ──> stg_customers┘
dbt reads that graph from your ref() calls and runs it in order. Change stg_orders and dbt knows fct_orders and revenue are downstream.
Project Structure That Scales
A dbt project is just a folder of SQL and YAML. The structure that holds up at scale follows three layers: staging, intermediate, and marts.
my_project/
dbt_project.yml
models/
staging/
stripe/
_stripe__sources.yml # declares raw tables
_stripe__models.yml # tests + docs for staging
stg_stripe__payments.sql
stg_stripe__customers.sql
intermediate/
finance/
int_payments_pivoted.sql
marts/
finance/
fct_orders.sql
dim_customers.sql
revenue.sql
tests/
macros/
seeds/
The layers each have one job:
- Staging - one model per source table. Rename columns, cast types, basic cleaning. Nothing else. This is the only layer that touches raw tables directly.
stg_stripe__paymentsis a thin, renamed, typed view overraw.stripe.payments. - Intermediate - the messy join and reshape logic that is not yet a final business object. These are the building blocks you do not expose directly.
- Marts - the business-facing tables.
fct_orders,dim_customers,revenue. This is what analysts and BI tools query. Named for the business, not the source.
The rule that keeps it clean: a model only refs models in its own layer or the layer below. Marts ref intermediate and staging. Staging refs sources. Nothing refs sideways into another team’s marts without a conversation. This is the same layering discipline that keeps application code from turning into a hairball.
Sources are declared in YAML so dbt knows where raw data lives and can test its freshness:
# models/staging/stripe/_stripe__sources.yml
version: 2
sources:
- name: stripe
database: raw
schema: stripe
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}
loaded_at_field: _loaded_at
tables:
- name: payments
- name: customers
Now {{ source('stripe', 'payments') }} in a staging model both references the table and lets dbt check whether the data is stale before you build on top of it.
Testing Is Not Optional Anymore
This is the part that separates dbt from “SQL in git.” dbt tests are assertions about your data that run every time the pipeline builds. The four built-in generic tests cover most real bugs:
# models/marts/finance/_finance__models.yml
version: 2
models:
- name: fct_orders
columns:
- name: order_id
tests:
- unique
- not_null
- name: customer_id
tests:
- not_null
- relationships:
to: ref('dim_customers')
field: customer_id
- name: status
tests:
- accepted_values:
values: ['completed', 'pending', 'cancelled', 'refunded']
unique and not_null catch the duplicate-rows bug that silently doubles your revenue. relationships catches orphaned foreign keys (orders pointing at customers that do not exist). accepted_values catches the day someone adds a new status upstream and your CASE statement silently drops those rows.
For logic that the generic tests cannot express, a singular test is just a query that should return zero rows:
-- tests/assert_revenue_is_positive.sql
-- Fails if any month has negative revenue
SELECT month, revenue
FROM {{ ref('revenue') }}
WHERE revenue < 0
If that query returns rows, the test fails and the build stops. You find out at CI time, not when the CFO opens the dashboard. This is the single biggest cultural change dbt brings: broken data becomes a failed check in a pull request instead of a fire drill three weeks later.
dbt vs Stored Procedures
The honest comparison, because this is the real decision most teams face:
| Concern | Stored procedures | dbt |
|---|---|---|
| Version control | Lives in the DB, hard to diff | Plain files in git |
| Testing | Manual, ad hoc | Built-in, runs on every build |
| Lineage | None, you trace by hand | Generated automatically |
| Code review | Rare, painful | Normal PR workflow |
| Dependency ordering | You manage it | dbt computes it |
| Reuse | Copy-paste or nested procs | ref() and macros |
| Onboarding | Read the whole DB | Read the docs site |
Stored procedures can do imperative logic and row-by-row work that dbt cannot, so they are not strictly worse. But for the analytics transformation layer, which is almost entirely set-based SELECT logic, dbt wins on every axis that matters for a team larger than one person.
Migrating a Messy Warehouse in Six Weeks
You do not boil the ocean. You do not try to move 300 queries at once. You strangle the mess one metric at a time, starting with the ones that matter most. Here is a realistic plan that I have watched work.
Week 1 - Set up and prove it. Install dbt, connect it to a dev schema in your warehouse, and get CI running (dbt Cloud or a GitHub Action calling dbt build). Pick one high-value, high-pain metric. Revenue is the usual choice because everyone cares and everyone disagrees about it. Build it end to end: sources, one or two staging models, one mart. Add unique and not_null tests. The goal this week is a working pipeline, not coverage.
Week 2 - Lock the definition. Take your new revenue model and reconcile it against the three existing conflicting versions. Find out which filters are correct. Document the decision in the model. This is often the hardest week because it is organizational, not technical: you are forcing the team to agree on one definition. Point the most-watched dashboard at the dbt-built table.
Weeks 3 to 4 - Build the staging layer. Create one staging model per raw source table you actually use. This is mechanical and fast: rename, cast, clean. Declare sources with freshness checks. By the end you have a clean, typed, tested foundation that every future model builds on. Resist the urge to build marts here; the staging layer is the investment that pays off for everything after.
Week 5 - Migrate the next five metrics. With staging in place, each new mart is cheap. Pick the next five most-used queries from your BI tool’s usage logs (every BI tool tracks this; use the data). Rebuild each as a dbt mart refing the staging models. Add tests. Repoint dashboards. You will start deleting old ad-hoc queries, which is the whole point.
Week 6 - Lineage, docs, and a freeze. Run dbt docs generate and publish the docs site with the lineage graph. Now anyone can trace any number back to its raw source. Institute the rule that makes it stick: no new analytics table gets created outside dbt. New metrics go through a PR with tests. The warehouse stops accumulating spaghetti because the only sanctioned path to a new table is the reviewed, tested one.
After six weeks you will not have migrated everything. You will have migrated the 20% of queries that produce 80% of the decisions, and you will have a wall that stops new mess from forming. The long tail migrates opportunistically over the following months, every time someone touches an old query.
What Works, What Does Not, and What to Do
What works: dbt genuinely solves the single-source-of-truth problem. Lineage and tests are not marketing; they change how the team operates. The ref() pattern and layered structure scale from one analyst to fifty. Onboarding goes from weeks to days because the docs site is generated from the actual code, so it cannot drift.
What does not: dbt is a transformation tool, not an ingestion or orchestration tool. You still need something to land raw data (Fivetran, Airbyte, custom loaders) and something to schedule runs (dbt Cloud, Airflow, Dagster). Jinja templating gets ugly fast if you over-engineer macros; most teams write too much clever Jinja and regret it. And dbt does not make bad data good. If your raw data is wrong, dbt will faithfully transform wrong data into wrong metrics, just with better lineage.
What to actually do: if your team has more than a couple of analysts and any disagreement about what the numbers mean, start this week. Do not plan a six-month migration. Pick your single most-argued-about metric, build it in dbt with two tests, and point one dashboard at it. The first model is the hard part because it forces the setup and one definitional fight. Everything after that is downhill, and the day someone’s bad data gets caught by a failing test in a pull request instead of a board meeting, the whole team gets it.
Comments