Transformations with dbt

What you will build: a small dbt project on your laptop that turns raw Brew & Bean order data into clean staging models and a daily_revenue table, with tests and documentation. It runs on DuckDB, a free database that lives in a single file, so you need no cloud account.

Prerequisites: SQL (joins, GROUP BY, CTEs from *Advanced SQL*), Python 3.9 or newer, and the ELT idea from *ETL vs ELT and pipeline design*.

In ELT the raw data is loaded into the warehouse first and transformed there with SQL. That leaves a question: where does all that SQL live? Often in scattered scripts and dashboard queries that nobody tests, and that break when a column changes.

dbt (data build tool) is the T in ELT. You write each transformation as a SELECT statement in its own .sql file called a model. dbt works out the order the models must run in, wraps each SELECT in the right CREATE VIEW or CREATE TABLE statement, runs them in your warehouse, and lets you add tests and documentation next to the code. Because it is plain text files, everything goes in Git, gets reviewed in pull requests and runs in CI.

dbt does not extract or load data and does not process data itself; the warehouse does the work. dbt Core is open source and free. dbt also has a hosted product (dbt Cloud); check the official site for its current plans.

name: brew_bean
version: "1.0.0"
profile: brew_bean

model-paths: ["models"]
seed-paths: ["seeds"]

models:
  brew_bean:
    staging:
      +materialized: view
    marts:
      +materialized: table

Project settings. Staging models become views; marts become tables. The profile name must match profiles.yml.

brew_bean:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: brew.duckdb
      threads: 1

Connection details. dbt looks for profiles.yml in the current folder first, then in ~/.dbt. For a real warehouse this file holds credentials, so never commit it to Git.

Now the raw data. Seeds are CSV files in the seeds folder. They suit small reference data and tutorials; real order data would be loaded by an ingestion tool and declared as a source.

order_id,customer_name,item_id,qty,created_at
1,Asha,cappuccino,2,2026-10-01 09:15:00
2,Ravi,espresso,1,2026-10-01 09:40:00
3,Meera,croissant,2,2026-10-01 18:05:00
4,Asha,masala-chai,1,2026-10-02 08:30:00
5,Kiran,cold-brew,1,2026-10-02 17:45:00
6,Ravi,veg-sandwich,1,2026-10-02 19:10:00
7,Meera,filter-coffee,3,2026-10-03 08:55:00
8,Asha,banana-bread,2,2026-10-03 16:20:00

Raw orders, one line per order for simplicity.

item_id,name,category,price
espresso,Espresso,coffee,120
cappuccino,Cappuccino,coffee,180
cold-brew,Cold Brew,coffee,220
filter-coffee,Filter Coffee,coffee,80
masala-chai,Masala Chai,tea,90
croissant,Butter Croissant,bakery,150
banana-bread,Banana Bread,bakery,130
veg-sandwich,Veg Grilled Sandwich,food,160

The Brew & Bean menu.

A common layout has two layers. Staging models sit directly on raw data and only clean it: rename columns, cast types, one staging model per raw table. Marts models join and aggregate staged data into tables that analysts and dashboards use. Each layer refers to the previous one with ref().

select
    order_id,
    customer_name,
    item_id,
    qty,
    cast(created_at as timestamp) as created_at
from {{ ref('raw_orders') }}

Cleans raw_orders. {{ ref('raw_orders') }} points at the seed and tells dbt to build the seed first.

select
    item_id,
    name as item_name,
    category,
    price
from {{ ref('raw_menu') }}

Renames name to item_name so it is clearer in joins.

with lines as (
    select
        cast(o.created_at as date) as order_date,
        o.order_id,
        o.qty * m.price as revenue
    from {{ ref('stg_orders') }} as o
    join {{ ref('stg_menu') }} as m
        on o.item_id = m.item_id
)

select
    order_date,
    count(distinct order_id) as orders,
    sum(revenue) as revenue
from lines
group by order_date
order by order_date

The daily revenue table. Notice there is no CREATE TABLE: dbt adds that based on the materialization setting.

Tests are declared in YAML next to the models. Built-in generic tests are unique, not_null, accepted_values and relationships (a foreign-key check). dbt runs each as a query that looks for bad rows; a test passes when it finds none.

version: 2

models:
  - name: stg_orders
    description: "One row per order line, cleaned from raw_orders."
    columns:
      - name: order_id
        description: "Unique id of the order."
        data_tests:
          - unique
          - not_null
      - name: item_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_menu')
              field: item_id
      - name: qty
        data_tests:
          - not_null

  - name: stg_menu
    columns:
      - name: item_id
        data_tests:
          - unique
          - not_null
      - name: category
        data_tests:
          - accepted_values:
              values: ["coffee", "tea", "bakery", "food"]

  - name: daily_revenue
    description: "Orders and revenue in rupees for each day."
    columns:
      - name: order_date
        data_tests:
          - unique
          - not_null

Descriptions feed the generated docs; data_tests run with dbt test. In dbt versions before 1.8 the key is called tests instead of data_tests. Newer dbt versions may print a deprecation warning suggesting that arguments like values: and to: go under an arguments: key; the file still works, and the warning tells you how to update it.

dbt debug      # checks your project and connection
dbt seed       # loads the CSVs into DuckDB
dbt run        # builds the models in dependency order
dbt test       # runs all tests
dbt build      # seed + run + test together, in order, stopping dependents on failure

Run these from the brew_bean folder. Use dbt build in day-to-day work.

To look at the result, open the DuckDB file from Python (install the library with pip install duckdb if needed):

import duckdb

con = duckdb.connect("brew.duckdb", read_only=True)
print(con.sql("select * from daily_revenue").fetchall())

Reads the table dbt built. Because the data matches the Spark lesson, you should see the same totals.

Now generate documentation and a lineage graph that shows how models depend on each other:

dbt docs generate
dbt docs serve

Opens a local website with every model, its columns, descriptions, tests and a lineage diagram.

Open seeds/raw_orders.csv and change espresso in row 2 to espresso2. Run dbt seed then dbt test. The relationships test on stg_orders.item_id fails because that item is not in the menu. dbt shows how many rows failed and the file with the compiled test query. This is the real value of dbt: bad data is caught at the pipeline, not by an angry stakeholder looking at a wrong dashboard. Undo your edit and run dbt build again.

An incremental model adds a filter that applies only on later runs. The sketch below shows the idea; it needs a real source with a growing table, so treat it as a pattern to adapt.

{{ config(materialized='incremental', unique_key='order_id') }}

select order_id, item_id, qty, created_at
from {{ ref('stg_orders') }}

{% if is_incremental() %}
where created_at > (select max(created_at) from {{ this }})
{% endif %}

On the first run dbt builds the whole table. After that, only rows newer than the latest existing row are processed, and unique_key lets dbt replace rows that changed. {{ this }} means the table being built.

In production dbt is usually run by an orchestrator (see *Orchestration with Apache Airflow*), against a warehouse such as BigQuery, Snowflake or Databricks (see *Cloud data platforms*). You switch warehouses by installing a different adapter and changing profiles.yml; most of your SQL carries over. The data quality ideas here are extended in *Data quality, testing and observability*.

How this comes up in interviews: what dbt does and does not do; ref() and the DAG; the difference between view, table and incremental; how you test data; and why ELT with dbt replaced many hand-written SQL scripts.

You are an analytics engineer. Write a dbt model in SQL called [model_name] that [what it should produce, e.g. shows revenue per category per month]. Use ref() to refer to these staging models: [list of models and their columns]. Then write the schema.yml entries with descriptions and sensible tests (unique, not_null, accepted_values, relationships). Explain each test in one sentence.

What to expect: Check that the model uses ref() instead of hard-coded table names, groups by every non-aggregated column, and that the tests make sense for the grain (what one row means).