Course contents

The Analytics Engineer’s Field Guidedbt 1: build the modelsStep 2: From scripts to models

From scripts to models

Part 2 · dbt · step 2

From scripts to models: source(), ref() and the graph

A model is just a SELECT. Two functions wire everything together.

· · ·

How Alice worked before dbt

Until now, Alice's job lived in a Snowflake worksheet (the web UI's SQL editor): a few hundred lines of CREATE OR REPLACE TABLE ... AS SELECT blocks. She'd highlight a block, hit run, wait, highlight the next one. The order mattered, and it lived only in her head: run the customers block before the orders block, or the join returns nothing.

It worked the way a house of cards works. When marketing wanted the same numbers filtered differently, she copied the script, and now there were two. Nobody could tell which version was real, the tables were called orders_final and orders_final_v2, and the day she was ill, nobody could refresh anything.

The worksheet
One long scriptBlocks run by hand, in an order she remembers.
Copy to changeA variant means a second script, then a third.
No historyWho changed the revenue logic in March? Nobody knows.
Only Alice can run itThe knowledge is in her head, not in the repo.
The dbt project
One file per tableA model is a SELECT, the filename is the table.
Change in placeEdit the model, rebuild, one version exists.
Git tracks everythingEvery change reviewed, dated and attributable.
Anyone can run itdbt run builds the whole thing in the right order.

The rest of this lesson is the mechanics of that move: a model instead of a script, source() instead of a hardcoded raw table name, and ref() instead of remembering what to run first.

A model is a SELECT

Every model is a .sql file with one SELECT in it. dbt wraps it in the DDL, runs it, and creates a Snowflake object named after the file. By default that object is a view; step 4 shows how to make it a table. That is the whole concept.

Alice's raw orders table is messy: amounts in cents, statuses with stray spaces, a timestamp where she wants a date. She writes a model to clean it:

raw.orders, as loaded
amount_cents1000
status'PAID  '
created_at'2024-05-25T08:13Z'
→
stg_orders.sql
stg_orders, what dbt builds
amount_eur10.00
status'paid'
order_date2024-05-25
-- models/staging/stg_orders.sql
SELECT
    order_id,
    customer_id,
    amount_cents / 100.0     AS amount_eur,
    lower(trim(status))      AS status,
    created_at::date           AS order_date   -- :: is Snowflake's short CAST
FROM {{ source('raw', 'orders') }}

The {{ ... }} part is Jinja, a template language dbt runs before Snowflake sees anything. {{ ... }} prints a value into the SQL (here, the raw table's full name). {% ... %} runs logic, like an if, and prints nothing. {# ... #} is a comment that vanishes. So the file only becomes valid SQL once dbt compiles it. Step 5 (macros) shows the rest.

No semicolon at the end: dbt wraps your SELECT in its own statement, and a stray ; can break it.

source(): point at raw tables

First, where dbt sits. dbt is an ELT tool, and it only does the T. An ingestion tool like Fivetran or Airbyte does Extract and Load: it copies raw data into Snowflake as-is. Then dbt Transforms it, in place, with SQL.

So dbt doesn't build the raw tables. You declare them once in a .yml file and read them with source(). (Models you build use ref(), below.) If the raw table moves, you fix one line. dbt can also draw it on the graph (below) and check it's still fresh.

# models/staging/_sources.yml
sources:
  - name: raw               # a label you pick, not a schema
    database: RAW_DB        # where the tables really are
    schema: PUBLIC
    tables:
      - name: orders
      - name: customers

source('raw', 'orders') compiles to RAW_DB.PUBLIC.ORDERS, the real table. The pictures' raw.orders is shorthand for it. dbt reads every .yml under models/, whatever its name; the leading _ just sorts it first.

Following along on a trial?
You have no RAW_DB yet. Run this once in a worksheet for two tiny raw tables:
USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS RAW_DB;
CREATE OR REPLACE TABLE RAW_DB.PUBLIC.ORDERS
  (order_id INT, customer_id INT, amount_cents INT, status STRING, created_at TIMESTAMP);
INSERT INTO RAW_DB.PUBLIC.ORDERS VALUES
  (1, 10, 1000, 'PAID  ', '2024-05-25 08:13:00'),
  (2, 11, 2700, 'pending', '2024-05-25 19:42:00');
CREATE OR REPLACE TABLE RAW_DB.PUBLIC.CUSTOMERS (customer_id INT, name STRING, country STRING);
INSERT INTO RAW_DB.PUBLIC.CUSTOMERS VALUES (10, 'Marco', 'IT'), (11, 'Lea', 'FR');
GRANT USAGE ON DATABASE RAW_DB TO ROLE USERLAND_ROLE;
GRANT USAGE ON SCHEMA RAW_DB.PUBLIC TO ROLE USERLAND_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA RAW_DB.PUBLIC TO ROLE USERLAND_ROLE;

What goes in the .yml files

Two kinds of YAML sit next to your SQL, and each answers a different question:

  • _sources.yml, "where does raw data live?" The source name, the real database and schema, the list of tables. Optionally a freshness block, which dbt source freshness checks (step 7).
  • _models.yml, "what do my models promise?" For each model: a description, its columns, the data_tests on those columns, and an owner under meta.
# models/staging/_models.yml
models:
  - name: stg_orders
    description: "One row per order, amounts in EUR, statuses normalised."
    config:
      meta:                     # under config: since dbt 1.10
        owner: team-finance
    columns:
      - name: order_id
        description: "Primary key."
        data_tests: [unique, not_null]   # called tests: before dbt 1.8
      - name: status
        description: "Lowercase, trimmed order status."

An entry is optional: dbt builds a model without one, but then it has no description and no tests. Tests run with dbt test, not dbt run, and a failing one prints how many bad rows it found. YAML tests are the reusable kind; one-off SQL checks go in tests/ (both in step 7).

So _sources.yml describes the raw tables you read (someone else made them), and _models.yml the models you build (you promise what they contain). A third file joins them later:

File
Describes
Typical content
_sources.yml
Raw tables you read, built by loaders
loaded_at_field, freshness: warn_after 24h
_models.yml
Models you build, one entry per .sql file
data_tests: [unique, not_null], descriptions
_exposures.yml
Things outside dbt that consume your models, like a dashboard (step 9)
depends_on: ref('fct_orders'), an owner

Write the YAML with the model, not in a cleanup sprint. The descriptions become the docs site (dbt docs generate, step 9), the tests the safety net, and meta.owner what the dbt-score linter checks (step 12).

Your single best debugging tool
To see the SQL dbt actually generates, run dbt compile --select stg_orders and open target/compiled/my_project/models/staging/stg_orders.sql. Every ref(), source() and macro is resolved into plain SQL you can paste into Snowflake. target/ is dbt's scratch folder, rebuilt on every command and kept out of Git. When a model misbehaves, look there first.

ref(): wire models together

When one model builds on another, reference it with ref(), never a hardcoded name. Here's what that one function actually does:

  • At compile time, it becomes the real table name. {{ ref('stg_orders') }} turns into USERLAND.DBT_ALICE.STG_ORDERS when Alice runs it and PROD_DB.STAGING.STG_ORDERS when Airflow runs production. (Why STAGING? Step 4 gives each layer its own schema, and step 14 sets the final names.) Same SQL, right table everywhere.
  • It declares a dependency. Before running anything, dbt reads every ref() and source() in the project and draws the dependency graph, the DAG.
  • The graph decides the build order. dbt runs parents before children, in parallel where possible. You never write "run this first, then that": the refs already say it.

fct_orders needs the customers too, so Alice adds their staging model first:

-- models/staging/stg_customers.sql
SELECT customer_id, name AS customer_name, country
FROM {{ source('raw', 'customers') }}
-- models/marts/fct_orders.sql
SELECT o.order_id, o.amount_eur, c.country
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c USING (customer_id)
raw.orders
→
stg_orders
→
fct_orders

As a project grows, a join like this often moves into an intermediate model (step 3), and the graph dbt draws looks like this:

raw.orders
raw.customers
source()
stg_orders
stg_customers
ref()
int_orders_enriched
ref()
fct_orders
you write source() and ref() · dbt reads them, draws this graph, and builds it in order, sources first, every time

You never maintain that graph: dbt builds it from your ref() and source() calls. Rename a model and forget one ref, and the run fails loudly instead of building against a ghost.

Build it: dbt run and --select

# build every model
dbt run

# stg_orders and everything downstream of it (the + is on the right)
dbt run --select stg_orders+

# stg_orders and everything upstream it needs (the + is on the left)
dbt run --select +stg_orders

# run the data tests (dbt build runs everything, tests included)
dbt test

dbt run prints a line per model (OK created sql view model dbt_alice.stg_orders) and a summary that should show ERROR=0. In Snowsight, your views are under USERLAND → DBT_ALICE.

The naming convention that maps to the layers

Each layer has a prefix, and the prefix tells you what a model is before you even open it. This is the convention the rest of the guide leans on, and the one dbt-score will later enforce automatically:

sourcesraw
Landed by your EL tool. Never edited. You read them with source().
stagingstg_
One model per source table. Rename, recast, clean. No joins.
intermediateint_
The messy joins and logic that don't belong in a final table. Optional.
martsfct_ / dim_
Business-ready tables the BI tools read. Facts (events, like orders) and dimensions (things, like customers).

Next step: what each layer is really for, and how to shape the marts so the business questions have answers.

Lesson quiz

  1. What exactly is a dbt model?

    1. A YAML block that declares a table's columns, data types and tests
    2. A .sql file of CREATE TABLE and INSERT statements dbt runs in order
    3. A stored procedure that dbt deploys into Snowflake and then calls
    4. A single SELECT in a .sql file, which dbt turns into a view or table
  2. Your ingestion tool loads RAW_DB.PUBLIC.orders. How should a staging model read it?

    1. {{ source('raw', 'orders') }}, declared once in a .yml
    2. {{ ref('orders') }}, since every table is a ref
    3. FROM RAW_DB.PUBLIC.orders, written directly in the model
    4. {{ source('orders') }}, with just the table name
  3. Alice moves stg_orders.sql from models/staging/ to models/marts/ and renames it zz_orders.sql. fct_orders now reads {{ ref('zz_orders') }}. In which order does dbt run build them?

    1. fct_orders first, since files build in alphabetical order
    2. zz_orders first, because fct_orders refs it
    3. Both at once, since they now share a folder
    4. It fails, since staging must build before marts