dbt Models

A model is the most important concept in dbt. Almost everything you do in dbt centers around models. This topic explains what a model is, how to write one, how to configure it, and how models connect to each other.

What Is a Model?

A model is a .sql file inside your models/ folder that contains a single SELECT statement. dbt takes that SELECT statement and creates a table or view in your warehouse. The file name becomes the table or view name.

models/stg_orders.sql   →   creates a view/table named  stg_orders
models/fct_revenue.sql  →   creates a view/table named  fct_revenue

You never write CREATE TABLE or INSERT statements in a dbt model. You only write SELECT. dbt wraps your SELECT with the correct CREATE statement based on your materialization setting.

A Minimal Model

-- models/stg_orders.sql

select
    order_id,
    customer_id,
    order_date,
    amount,
    status
from raw.orders

Running dbt run with this file creates a view called stg_orders in your warehouse schema. Every column in the SELECT appears in the view.

Adding Transformations

Models do more than just select columns. They clean data, rename columns, cast types, and calculate new fields.

-- models/stg_orders.sql

select
    order_id,
    customer_id,
    cast(order_date as date)                     as order_date,
    amount / 100.0                               as amount_dollars,
    lower(status)                                as status,
    case
        when status = 'completed' then true
        else false
    end                                          as is_completed
from raw.orders
where order_date is not null

This model renames nothing but adds type casting, unit conversion, and a derived boolean column. Every downstream model that references this one gets clean, consistent data automatically.

Model Configuration Options

You configure a model using a Jinja config block at the top of the file. The config block sets materialization, tags, aliases, and more.

Setting Materialization

{{ config(materialized='table') }}

select ...

Setting Schema

{{ config(
    materialized='table',
    schema='reporting'
) }}

select ...

This model writes to the reporting schema instead of your default schema. Useful for organizing output tables into logical groups.

Setting an Alias

{{ config(alias='final_orders') }}

select ...

The file is named stg_orders.sql but the warehouse object is named final_orders. Use aliases when you need the warehouse name to differ from the file name.

Adding Tags

{{ config(tags=['finance', 'daily']) }}

select ...

Tags let you run subsets of models: dbt run --select tag:daily runs only models tagged with daily.

The ref() Function

When one model needs data from another model, you use the ref() function instead of hardcoding the table name.

-- models/fct_orders.sql

select
    o.order_id,
    c.name        as customer_name,
    o.amount_dollars
from {{ ref('stg_orders') }}   o
join {{ ref('stg_customers') }} c
  on o.customer_id = c.customer_id

ref('stg_orders') resolves to the actual schema and table name at compile time. dbt also records this dependency and ensures it runs stg_orders before fct_orders. You get correct run order for free.

Model Layers in Practice

A well-organized project uses three layers of models:

RAW DATA
   |
   v
STAGING (stg_*)
  Purpose: one model per source table
  Action: rename, cast, light cleanup
  Materialization: view (fast, no storage cost)
   |
   v
INTERMEDIATE (int_*)
  Purpose: join staging models, apply logic
  Action: joins, aggregations, business rules
  Materialization: view or ephemeral
   |
   v
MARTS (fct_* and dim_*)
  Purpose: analytics-ready tables for dashboards
  Action: final shape for end users
  Materialization: table (fast to query)

Staging Example

-- models/staging/stg_customers.sql
select
    id            as customer_id,
    name          as full_name,
    email,
    created_at    as signup_date
from {{ source('crm', 'customers') }}

Mart Example

-- models/marts/dim_customers.sql
{{ config(materialized='table') }}

select
    customer_id,
    full_name,
    email,
    signup_date,
    date_diff(current_date(), signup_date, day) as days_since_signup
from {{ ref('stg_customers') }}

Running Specific Models

You do not always need to run every model. dbt gives you powerful selection syntax:

# Run one specific model
dbt run --select stg_orders

# Run a model and all models that depend on it (downstream)
dbt run --select stg_orders+

# Run a model and all models it depends on (upstream)
dbt run --select +fct_orders

# Run all models in a folder
dbt run --select staging

# Run all models tagged finance
dbt run --select tag:finance

# Run models changed since last commit
dbt run --select state:modified

How dbt Names the Output Object

dbt names the warehouse object using a combination of your target schema and the model file name:

Target schema:   analytics_dev
Model file name: stg_customers.sql
Warehouse name:  analytics_dev.stg_customers

Target schema:   analytics_dev
Model file name: fct_orders.sql
Config alias:    final_orders
Warehouse name:  analytics_dev.final_orders

Checking the Compiled SQL

After running dbt compile or dbt run, inspect the compiled SQL in target/compiled/. This shows you the exact SQL that dbt sent to your warehouse, with all Jinja replaced by real SQL.

target/compiled/my_project/models/fct_orders.sql:

select
    o.order_id,
    c.name as customer_name,
    o.amount_dollars
from analytics_dev.stg_orders o
join analytics_dev.stg_customers c
  on o.customer_id = c.customer_id

This file is your best debugging tool. When a model fails, read the compiled SQL and run it manually in your warehouse to see the exact error.

Previous lesson

Back to course

Next lesson