dbt Project: Models, Sources and ref

In the previous course, you wrote the cleaned and business layers of a warehouse in SQL. With dozens of queries that depend on one another, you need a tool to order them, test them, document them and version them. That is the role of dbt (data build tool), an open-source command-line tool (dbt Core), also offered in a hosted version by its publisher dbt Labs.

What dbt does

dbt takes care of the T in ELT. It does not load data from applications (that is the role of an ingestion tool) and computes nothing itself: it compiles your SQL files, enriched with Jinja, then runs them in the warehouse (PostgreSQL, BigQuery, Snowflake, Databricks, DuckDB, etc., via an adapter). What you write therefore remains SQL.

Essential commands:

dbt run: builds the models;
dbt test: runs the data tests;
dbt build: builds and tests in graph order;
dbt compile: produces the final SQL in target/compiled/, without running it.

The structure of a project

mon_projet/
├── dbt_project.yml        # project name, folders, default configuration
├── models/
│   ├── staging/shop/      # one folder per source
│   │   ├── _shop__sources.yml
│   │   ├── stg_shop__orders.sql
│   │   └── stg_shop__customers.sql
│   └── marts/
│       └── customers.sql
├── macros/  tests/  seeds/  snapshots/

Connection information is not in the project: it is in profiles.yml, outside the Git repository, with targets such as dev and prod.

Declare the sources

A source is a table that dbt did not build. You declare it in YAML:

version: 2
sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
      - name: customers

In a model, you read it with {{ source('shop', 'orders') }}, which dbt replaces at compile time with the full name, for example raw_shop.orders. Advantages: the physical name is written only once, the source appears in the graph and the documentation, and you can monitor its freshness (dbt source freshness) by specifying a load timestamp column and alert thresholds.

Write a staging model

A model is a .sql file that contains a single SELECT query. No CREATE TABLE: dbt adds it according to the chosen materialization (next lesson). The file name becomes the name of the object created.

-- models/staging/shop/stg_shop__orders.sql
with source as (
    select * from {{ source('shop', 'orders') }}
),
renamed as (
    select
        id                         as order_id,
        user_id                    as customer_id,
        lower(status)              as status,
        amount_cents / 100.0       as amount,
        date(created_at)           as order_date
    from source
)
select * from renamed

A staging model: one source, consistent renames (order_id rather than id), harmonized types and units, no join or business rule. It is the equivalent of the cleaned layer seen in course DE1. The style with named CTEs (source, renamed, final) is a widespread convention that makes review easier.

ref() and the graph

A downstream model reads the staging models with ref():

-- models/marts/customers.sql
select c.customer_id, c.first_name,
       count(o.order_id)          as nb_orders,
       coalesce(sum(o.amount), 0) as total_amount
from {{ ref('stg_shop__customers') }} as c
left join {{ ref('stg_shop__orders') }} as o
       on o.customer_id = c.customer_id and o.status <> 'returned'
group by c.customer_id, c.first_name

ref() does two things:

it resolves the name: in development, dbt_lea.stg_shop__orders; in production, analytics.stg_shop__orders. The same code works in every environment;
it declares a dependency: dbt builds the directed acyclic graph (DAG) of all the models and runs them in the right order, in parallel when possible.

So never hard-code the name of a table built by dbt: without ref(), dbt ignores the dependency, may run the models in the wrong order and would read the production table from your development environment.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

CodeDifficulty 2SQL (SQLite) in the browser2 hints

Compiled stg_shop__orders model

Source tables (declared in dbt as source('shop', 'orders') and source('shop', 'customers')): raw_orders(id, user_id, status, amount_cents, created_at) and raw_customers(id, first_name, last_name, email). In the exercise, write the table names directly: this is the compiled SQL from dbt.

Write the query of the orders staging model, with the columns:

order_id (from id) and customer_id (from user_id);
status in lowercase;
amount: the amount in euros (amount_cents / 100.0);
order_date: the date only, extracted from created_at with date().

Exercise data (SQL)

-- Tables brutes chargées par l'outil d'ingestion (schéma raw_shop)
CREATE TABLE raw_orders (id INTEGER, user_id INTEGER, status TEXT, amount_cents INTEGER, created_at TEXT);
INSERT INTO raw_orders VALUES
 (1, 1, 'COMPLETED', 4990, '2026-03-01 09:15:00'),
 (2, 2, 'completed', 12000, '2026-03-01 14:02:00'),
 (3, 1, 'Returned', 1990, '2026-03-02 10:30:00'),
 (4, 3, 'placed', 3450, '2026-03-03 18:45:00');
CREATE TABLE raw_customers (id INTEGER, first_name TEXT, last_name TEXT, email TEXT);
INSERT INTO raw_customers VALUES
 (1, 'Amina', 'Diallo', ' AMINA@EX.FR'), (2, 'Bastien', 'Morel', 'bastien@ex.fr'),
 (3, 'Camille', 'Roux', 'camille@ex.fr'), (4, 'Dina', 'Petit', 'dina@ex.fr');

with source as (
    select * from raw_orders   -- {{ source('shop', 'orders') }} une fois compilé
)
select ...
from source

CodeDifficulty 2SQL (SQLite) in the browser2 hints

Marts model customers

Available models (compiled as tables): stg_customers(customer_id, first_name, email) and stg_orders(order_id, customer_id, status, amount, order_date).

Write the customers model: for each customer (including those with no orders), display customer_id, first_name, nb_orders (orders whose status is not returned), total_amount (0 if no orders) and first_order_date.

Exercise data (SQL)

CREATE TABLE stg_customers (customer_id INTEGER, first_name TEXT, email TEXT);
INSERT INTO stg_customers VALUES (1, 'Amina', 'amina@ex.fr'), (2, 'Bastien', 'bastien@ex.fr'), (3, 'Camille', 'camille@ex.fr'), (4, 'Dina', 'dina@ex.fr');
CREATE TABLE stg_orders (order_id INTEGER, customer_id INTEGER, status TEXT, amount REAL, order_date TEXT);
INSERT INTO stg_orders VALUES
 (1, 1, 'completed', 49.9, '2026-03-01'), (2, 2, 'completed', 120.0, '2026-03-01'),
 (3, 1, 'returned', 19.9, '2026-03-02'), (4, 3, 'placed', 34.5, '2026-03-03');

select c.customer_id, c.first_name,
       count(o.order_id) as nb_orders,
       ... as total_amount,
       ... as first_order_date
from stg_customers as c   -- {{ ref('stg_shop__customers') }}
left join stg_orders as o on ...
group by c.customer_id, c.first_name

Multiple choiceDifficulty 11 hint
The role of ref()
What happens if a model reads a hard-coded analytics.stg_shop__orders instead of {{ ref('stg_shop__orders') }}?
- Nothing, it is equivalent
- dbt doesn't know the dependency: the execution order may be wrong and development reads the production table
- dbt refuses to compile the project
TaskDifficulty 22 hints
Declare the sources and the first model
In a newly created dbt project (dbt init), the tables raw_shop.orders and raw_shop.customers exist in the warehouse. Create:
1. the file models/staging/shop/_shop__sources.yml that declares the shop source (schema raw_shop) and its two tables;
2. the model models/staging/shop/stg_shop__customers.sql that renames id to customer_id and normalizes the email (lowercase, no spaces);
3. run dbt run --select stg_shop__customers, then dbt compile and open the compiled SQL.
- The YAML file starts with version: 2 and declares sources > shop > tables orders and customers
- The model reads the table with {{ source('shop', 'customers') }} and not with a hard-coded name
- The model contains only a SELECT query, with no CREATE
- dbt run finishes with 1 successful model (PASS=1)
- The compiled file in target/compiled/ contains raw_shop.customers in place of the source() call

End-of-lesson quiz

Which part of ELT does dbt handle?
- Extraction from applications
- Loading
- Transformation in the warehouse
What does a dbt model file contain?
- A CREATE TABLE statement
- A single SELECT query
- A mandatory Python script
How do you read a table loaded by the ingestion tool?
- {{ ref('orders') }}
- {{ source('shop', 'orders') }}
- {{ table('orders') }}
Where are the warehouse connection credentials?
- In dbt_project.yml
- In profiles.yml, outside the repository
- In each model

The rest of the course

Jinja, Macros and Materializations18 min
Testing the Data18 min
Incremental Models and Snapshots20 min
Documentation, Lineage and Best Practices18 min

Build my path See the full syllabus

Transforming Data with dbt · Intermediate · 1 h 32 min

Objectives