What is dbt? How it turns raw warehouse data into clean tables

dbt is a tool that turns the raw data in your company’s data warehouse (its central database for analysis) into clean, trustworthy tables. It does this with files written in SQL, the language for asking a database questions, and it runs them in the right order and tests the results automatically. You will usually meet it as a project folder that another team owns, full of subfolders with odd names.

Say a teammate tells you to “just put it in dbt.” You nod, then open the project and find folders named staging, intermediate, and marts, which turn out to be the stages data passes through on its way from raw to ready for reports. Half the files have names you cannot pronounce. The idea behind all of it is simple once you strip away the fog.

dbt in one sentence

dbt (data build tool) turns warehouse SQL into a managed project. Each model is a SELECT that builds a table or view, and dependencies are declared inside the SQL. Tests and documentation live next to the models, and a command line (the text window where you type commands) tool (or a cloud runner) builds everything in the right order.

It is not a database, and it is not a scheduler for every job in the company. It is also not a BI (business intelligence) tool. It does not replace Python for odd one-offs, and it does not magically clean bad source data. What it is, is a transform framework tuned for analytics SQL that many people will share, review, and schedule.

Suppose you already write SQL against Snowflake, BigQuery, Redshift, Databricks SQL, Postgres, or a similar engine. Then dbt is mostly a project structure and a compiler wrapped around the SQL you already know. The warehouse still stores the data, and dbt tells the warehouse which models to create and in what order.

Rule of thumb: If your problem is “we ran the same transformation twice and got different answers,” dbt is about versioned, testable transforms. If your problem is “we never loaded the file,” fix the loading and the scheduling first, as the earlier posts in this series explain.

The three pieces analysts actually meet

1. Models: SQL that builds tables or views

A model is usually one .sql file whose body is a SELECT. dbt turns that query into a table or view in the warehouse. You point at other models with a special function, often written like ref('orders_clean'), so dbt knows the dependency graph and builds A before B.

That graph is the big upgrade over a folder of random scripts. When someone asks “what feeds the revenue mart?”, you follow the ref() links instead of guessing which notebook (a file that mixes code with its results, like a Jupyter notebook) ran last Tuesday.

2. Tests: checks on the built data

Tests are checks that run against the warehouse after a build, or as part of it. Four built-in ideas are common.

• Unique on a primary key (the column that uniquely identifies each row) column, so no ID appears twice.
• Not null on required fields, so nothing important is blank.
• Accepted values for status codes, so a stray new status gets noticed.
• Relationships, so foreign keys point at real parent rows.

Teams also write custom SQL tests, such as “yesterday’s order count should not drop more than 40% versus the prior same weekday.” In spirit this matches the validation habits in the data quality series: cheap, clear, and tied to the people who use the data. dbt’s contribution is packaging the tests next to the model. That way, shipping a metric and shipping the checks for that metric stay in one pull request, which is a proposed code change that a teammate reviews before it merges.

3. Docs: definitions that sit with the code

YAML files (a simple text format for settings) next to the models can describe tables, columns, and owners. Generated docs sites turn that into browsable lineage and descriptions. Perfect docs are rare, but useful docs are possible. Aim for the grain of the model, what “active customer” means here, and who to ping when a test fails.

If your company also has a catalog tool, treat the dbt docs as the engineering-facing source of truth for transforms and the catalog as the place people go to discover data. They should not invent two different definitions of revenue.

Staging, intermediate, marts: why the folders exist

Most dbt projects layer their models so that each stage has one job. The names vary from team to team, but the idea does not.

Staging: source-shaped and lightly cleaned

Staging models usually sit one step above the raw sources. They rename columns to a house style, cast types, and filter out obvious junk. They keep the grain close to the source system, where grain means what one row stands for. The goal is a stable, readable base, so that nobody joins the raw dump forever.

If five analysts each rename cust_id differently, you get five silent join bugs. Staging is where you agree once.

Intermediate: building blocks, not the final story

Intermediate models join staged pieces, apply business rules, or reshape data for reuse. They are the workshop tables. They are useful for debugging, but they are not always what executives open. A good intermediate model has a clear grain and a name that says what it is, such as int_orders_with_customers, and not a joke that only three people understand.

Marts: consumer-ready facts and dimensions

Marts are what BI tools and metric dashboards should prefer. Each one has a single grain, names that match how the business talks, and fewer leftover columns from earlier steps. This is where “orders by day by channel” or an “active accounts snapshot” should live.

Marts are the tables you are most careful about promoting between environments, as the next post in this series explains. They are also the first boards you watch for freshness and volume, as the post after that explains.

Why analysts should care, even if engineers own the repo

You might never click “deploy” in dbt Cloud. You still inherit the results, and five of them matter most.

• Definitions stop living only in Slack. Column descriptions and tests force someone to write “what is a paid order?” next to the code that computes it. That is metric hygiene with a build step (see Metrics that matter).
• Lineage becomes navigable. When finance’s number disagrees with marketing’s, the first question is not “who is right?” It is “which models and filters differ?”
• Your one-off SQL can graduate. A notebook that saves the company money every month should not stay a private hero script. Moving from staging to marts gives important logic a path into shared use.
• Tests fail before the meeting. Failures on unique keys and not-null columns are early news, the same idea as the automatic checks in the quality series.
• Review culture improves the numbers. Pull requests on SQL beat “I fixed it in prod late at night” when it comes to institutional memory.

Python still matters for profiling, charts, and pipelines that are not pure SQL transforms (see Python for analytics). dbt does not cancel notebooks. It gives shared warehouse logic a home that is not someone’s desktop.

What dbt is not, so you set expectations

• Not a full extract and load stack. Landing files, change data capture (CDC, which streams row-level changes out of a source database), and API (the way one program connects to another) extractors live elsewhere. dbt assumes the data is already in the warehouse, or reachable as a source.
• Not the only way to schedule work. Something must call dbt build or an equivalent, such as Airflow, Dagster, dbt Cloud jobs, or cron (the built-in timer that runs jobs on a schedule). The earlier post on orchestration (scheduling and connecting automated steps) covers that.
• Not automatic governance. Access control, personal data policies, and legal holds are separate. dbt can document and test, but it cannot replace stewardship.
• Not a substitute for grain discipline. A beautiful project with the wrong grain still ships wrong dashboards. The foundations and quality habits still apply.
• Not a free pass to skip code review. Bad SQL at scale is still bad SQL. The framework multiplies good and bad habits alike.

Worked example: a tiny orders path

Imagine raw tables have already landed: raw.orders and raw.customers. The business wants a daily revenue mart by channel, with tests on order IDs and non-null amounts. Here is a conceptual shape, with simplified names and no full project install.

This is a staging model sketch for orders, showing the SQL body only.

SELECT
  order_id,
  customer_id,
  CAST(order_ts AS TIMESTAMP) AS order_ts,
  LOWER(TRIM(channel)) AS channel,
  CAST(amount AS NUMERIC) AS amount,
  UPPER(TRIM(status)) AS status
FROM {{ source('app', 'orders') }}
WHERE order_id IS NOT NULL;

An intermediate model might join customers and filter to paid statuses. A mart then adds things up by day and channel.

SELECT
  DATE(order_ts) AS order_date,
  channel,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM {{ ref('int_orders_enriched') }}
WHERE status = 'PAID'
GROUP BY 1, 2;

Next is conceptual YAML for tests on the staging model. It shows schema (the expected fields and their types) tests and not a full file.

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: amount
        tests:
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['PAID', 'PENDING', 'CANCELLED', 'REFUNDED']

The table below shows what that project shape buys you on a Monday morning.

Notice that you did not need a vendor bake-off to understand the design. The value is structure, tests, and shared definitions. Install details, adapters, and the choice between cloud and core belong in a later how-to, once your team is ready.

How to read a dbt change as an analyst

You may not own the repo, but you will review the outcomes. When someone opens a pull request that touches “your” metric, run through this short checklist.

• Which models changed? Renames in staging alone are lower risk than changes to a mart’s filters.
• Did the grain change? One row used to mean one order, and now it means one order line. That deserves a meeting and not a silent merge.
• Did the tests change? Removing a unique test without a story is a smell. Adding tests is usually good news.
• Is the metric spec still true? If paid orders now include a new status, the published definition and the dashboard footnote should move with the SQL.
• What is the before and after on a known day? Ask for a side-by-side total for last Tuesday in stage. Numbers beat vibes.

This is how analysts take part without needing admin rights. You own the business meaning, you insist on comparable checks, and you refuse surprise grain shifts. That partnership is why dbt culture works when it works. When it fails, the usual reason is that transforms shipped without a consumer in the loop.

If your team has no formal review process yet, still write the five bullets above into a ticket before a “quick prod fix.” The point is shared memory and not ceremony.

How dbt fits what came earlier in the series

• The overall path: dbt lives mainly in the transform step, after data lands and before or beside the step that serves it.
• Latency (how long you wait for a result): dbt models often run on a batch schedule, such as hourly or nightly. Streaming needs different patterns, so do not force every real-time dream into a nightly mart.
• Storage: warehouses are the usual home. Lakes and lakehouses can host similar ideas with different tooling.
• Orchestration: dbt is one step in a larger DAG (directed acyclic graph, a chain of jobs where each runs only after the ones it depends on), and it is not always the whole chain. Upstream extract failures still break your beautiful models.

If your “dbt job” is green but the raw tables are empty, you have an orchestration and source problem and not a mart design problem. Keep the layers honest.

Common mistakes

• Treating dbt as magic BI. It builds tables, and people and tools still build charts and decisions.
• One giant model for everything. That gives you SQL that cannot be debugged and that only one person dares to edit, so split it into layers.
• Skipping tests “until later.” Later never comes, so ship three tests on the most blamed table first.
• Marts that still look like raw dumps. Consumers then reinvent the cleaning in every dashboard.
• Copy-pasting the same business logic into five models. The intermediate layer exists so you fix it once.
• Documenting nothing. You will then argue about grain every week, so write one sentence of grain per mart.
• Letting analysts query only raw tables forever. That bypasses the project and brings the chaos back.
• Assuming dbt replaces a quality culture. Tests without owners and response habits become noise, as the later post on observability and the quality series explain.

Quick recap

• dbt is a transform project framework made of SQL models, tests, and docs, built against your warehouse.
• Models form a dependency graph, so builds and debugging follow the real lineage.
• Staging cleans sources, intermediate holds reusable logic, and marts serve consumers.
• Analysts care because definitions, review, and the promotion of logic become visible work.
• dbt is not extraction, not full orchestration, and not a substitute for grain or quality habits.
• The next post covers environments, why laptop truth is dangerous, and who can write to production.

How to practice this week

• Pick one number you report weekly, and write its grain and filters in plain English, in metric spec style.
• Find where that number is built today, whether in a dbt model, a scheduled query, a notebook, or a dashboard calculation.
• If you have a dbt repo, open the lineage for that mart and list its staging and intermediate parents. If you cannot open the docs site, sketch the same graph on paper.
• Propose one test that would have caught last quarter’s scare, such as a unique key, a non-null amount, or an accepted status list.
• Stop a new one-off from becoming permanent. If you re-run the same SQL three times, file a ticket to promote it into a shared model.
• Read your warehouse’s model names through the three-layer lens, and note anything labeled “final” that is still shaped like a source.

Series notes

This is Part 5 of How data actually moves.

Sources

Research and further reading used for this article:

• dbt Labs: Introduction to dbt (what dbt is and how models fit analytics engineering)
• dbt Labs: About dbt models (SQL models, materializations, and ref thinking)
• dbt Labs: Data tests (generic and singular tests next to models)
• dbt Labs: How we structure our dbt projects (staging, intermediate, and marts guidance)
• dbt Labs: Documentation (descriptions and docs generation with the project)
• Analytics Made Simple: SQL series (query skills that power warehouse models)
• Analytics Made Simple: Data quality (checks and dimensions that pair with dbt tests)
• Analytics Made Simple: Learn (paths across foundations, pipelines, and more)

More definitions in Key Terms →

Merch on Etsy

Open store →

Naive Bayes Coffee Mug View on Etsy → Git Version Control Shirt View on Etsy → Apache Iceberg T-Shirt View on Etsy → Git Version Control Shirt View on Etsy →