What is dbt? A beginner's guide to dbt models, tests and dbt build

Suppose your team has 40 SQL queries that build reports. They live in different files, someone runs them by hand in the right order, nobody is sure which one depends on which, and when a number looks wrong, nobody can tell where it broke. dbt (data build tool) fixes exactly that. It lets you write each transformation as a plain SQL SELECT in its own file, then it runs them in the right order inside your warehouse, tests the results, and writes the documentation.

dbt does not extract or load data. It only does the "T": transform. That is why it is tied to ELT, where raw data is loaded first and transformed in the warehouse. The code in this guide is illustrative. It was not run here, because dbt needs a project and a warehouse connection, so check details against your dbt version.

What is a model?

A model is one SELECT statement saved in a .sql file. dbt wraps it in the CREATE TABLE AS or CREATE VIEW AS for you, and the file name becomes the table name. That is all a model is.

A first model might clean the raw orders table:

-- models/stg_orders.sql
select
    order_id,
    customer_id,
    cast(amount as integer) as amount,
    order_date
from {{ source('shop', 'orders') }}
where amount is not null

Two things to notice. There is no CREATE TABLE, only the SELECT. And instead of writing the raw table's name directly, it says {{ source('shop', 'orders') }}, a dbt function that points to the raw table you declared. The double curly braces are Jinja, a small templating language dbt uses to add programming features to SQL.

ref(): how models connect

A second model can build on the first, with ref():

-- models/daily_revenue.sql
select
    order_date,
    count(*)    as orders,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
group by order_date

{{ ref('stg_orders') }} means "the model called stg_orders". It does 2 jobs. It writes the correct table name for you, including the right schema for the environment you are in, and it tells dbt that daily_revenue depends on stg_orders. From all those links dbt draws the DAG, a map of what depends on what, and always runs models in the right order. You never write the order yourself.

A common structure is to name early cleaning models with a stg_ prefix (staging), and the final tables for reports fct_ or dim_, which echoes the star schema idea.

Tests: checks on your data

dbt can check your data after it builds it. The simplest tests are written in a YAML file (a plain text format for settings) next to the models:

# models/schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

This says the order_id column must have no repeats and no NULLs. dbt turns each into a query that looks for violations, and the test fails if it finds any. Two more built-in tests are accepted_values (a column may only hold listed values) and relationships (every value must exist in another table, like a foreign key). In older dbt versions the key is called tests instead of data_tests.

Tests are what turn "I think the numbers are right" into "the pipeline checks them every time".

dbt run, dbt test and dbt build

Three commands, and the difference is a classic interview question.

dbt run      # build the models
dbt test     # run the tests on what already exists
dbt build    # models, tests, seeds and snapshots together, in order

• `dbt run` builds the models only. It does not run any tests.
• `dbt test` runs the tests only. It checks tables that already exist and does not build anything.
• `dbt build` does both, in DAG order: it builds a model, tests it, and only then moves on to the models that depend on it. It also loads seeds (small CSV files kept in the project) and runs snapshots (which record how rows change over time, like SCD Type 2).

The important behaviour is what happens on failure. In dbt build, if a test fails on a model, the models that depend on it are skipped, so bad data does not flow into your reports. With dbt run followed by dbt test, the bad data has already been built before the test tells you about it. This is why dbt build is usual in production runs, and dbt run is handy while you are developing and want a quick rebuild.

You can also build only part of the project, for example one model and everything that depends on it: dbt build --select stg_orders+.

Where dbt fits

dbt sits after the data has been loaded. Something else, such as a loading tool, copies the raw data into the warehouse. dbt transforms it. Then a scheduler such as Airflow runs dbt build every night. In a layered design like medallion architecture, dbt models are often the code that moves data from one layer to the next.

What to learn first

You already know the main skill, which is SQL. Learn in this order: solid SELECT, JOIN and GROUP BY (start here), CTEs (subquery vs CTE), because most dbt models are written as a stack of CTEs, and then the dbt basics themselves in the dbt & Analytics Engineering track. The dbt interview questions cover the build and run difference and more.