Models and Transformations

Models are the core building blocks of your dbt project. They define the transformations that turn raw data into analytics-ready datasets using SQL. This guide covers how to create models, manage dependencies, and leverage dbt's templating capabilities.

---

What Are Models?

In dbt, a model is a SQL file that defines a transformation. When you run dbt, it compiles these SQL files into executable queries and runs them against your data warehouse, creating views or tables.

Models serve three key purposes:

1. Transform data into useful analytics structures

2. Document transformations with clear SQL

3. Create dependencies between different data assets

Each model typically results in a single table or view in your data warehouse.

---

Creating Your First Model

A model is simply a .sql file in your models/ directory. Let's start with a basic example:

-- models/staging/stg_customers.sql
SELECT
  id as customer_id,
  first_name,
  last_name,
  email,
  date_joined
FROM {{ source('jaffle_shop', 'customers') }}

When you run dbt run, this SQL gets compiled and executed in your data warehouse, creating a view called stg_customers with transformed customer data.

---

Using Common Table Expressions (CTEs)

CTEs make your models more readable and maintainable by breaking complex queries into logical building blocks. They're a powerful way to structure your transformations:

-- models/marts/customer_orders.sql
WITH customers AS (
    SELECT * FROM {{ ref('stg_customers') }}
),

orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),

customer_orders AS (
    SELECT
        customer_id,
        COUNT(order_id) as order_count,
        SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id
)

SELECT
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    customers.email,
    COALESCE(customer_orders.order_count, 0) as order_count,
    COALESCE(customer_orders.total_spent, 0) as total_spent
FROM customers
LEFT JOIN customer_orders USING (customer_id)

CTEs offer several benefits:

• Improve readability by breaking complex logic into named sections

• Allow you to reuse intermediate calculations

• Make troubleshooting easier by separating transformation steps

---

Model Dependencies with ref()

The ref() function is one of dbt's most powerful features. It allows you to reference other models, automatically creating dependencies:

-- models/marts/customer_lifetime_value.sql
WITH customer_orders AS (
    SELECT * FROM {{ ref('customer_orders') }}
)

SELECT
    customer_id,
    total_spent,
    total_spent * 0.15 as estimated_future_value,
    total_spent * 1.15 as lifetime_value
FROM customer_orders

When you use ref():

1. dbt automatically determines the correct schema and table name

2. dbt builds a dependency graph, ensuring models run in the correct order

3. dbt creates lineage documentation for your project

A key benefit is that if you rename models or change schemas, dbt handles all the references for you.

---

Model Configuration with config()

The config() function lets you control how a model is materialized and other settings:

-- models/marts/large_summary_table.sql
{{ 
  config(
    materialized='table',
    sort='date_day',
    dist='customer_id'
  ) 
}}

SELECT
  date_trunc('day', created_at) as date_day,
  customer_id,
  sum(amount) as total_amount
FROM {{ ref('stg_orders') }}
GROUP BY 1, 2

Common configuration options include:

• materialized: How the model should be created ('view', 'table', 'incremental', 'ephemeral')

• schema: Which schema the model should be created in

• tags: Labels to organize and select models

• Database-specific options (like sort, dist, cluster_by, etc.)

---

Using Jinja for Dynamic SQL

Jinja is a templating language that allows you to generate dynamic SQL. dbt uses Jinja to make your transformations more flexible and reusable.

Conditional Logic

Use if/else statements to adapt your SQL based on conditions:

SELECT
  order_id,
  order_date,
  {% if target.name == 'prod' %}
    amount
  {% else %}
    amount * 100 as amount_in_cents
  {% endif %}
FROM {{ ref('stg_orders') }}

Looping

Generate repetitive SQL using for loops:

SELECT
  order_id,
  {% for i in range(1, 5) %}
    item_{{ i }}_id,
    item_{{ i }}_quantity,
    {% if not loop.last %},{% endif %}
  {% endfor %}
FROM {{ ref('stg_order_items') }}

Variables

Use variables to make your models configurable:

-- Using a variable defined in dbt_project.yml or passed via --vars
SELECT *
FROM {{ ref('stg_orders') }}
WHERE order_date >= '{{ var("start_date", "2020-01-01") }}'

---

Model Organization Best Practices

Organize your models to reflect their purpose in your analytics pipeline:

Staging Models

Staging models clean and standardize source data:

• Naming and datatype standardization

• Simple filtering

• One-to-one relationship with source tables

• Typically materialized as views

-- models/staging/stg_customers.sql
SELECT
  id as customer_id,
  first_name,
  last_name,
  email,
  -- Convert to ISO date format
  PARSE_DATE('%Y-%m-%d', date_joined) as date_joined
FROM {{ source('jaffle_shop', 'customers') }}
WHERE id IS NOT NULL

Intermediate Models

Intermediate models combine and transform staging models:

• Join related data sources

• Apply business logic

• Create reusable building blocks

• Typically materialized as views

-- models/intermediate/int_customer_orders.sql
SELECT
  o.order_id,
  o.customer_id,
  c.email,
  o.order_date,
  o.status,
  o.amount
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c ON o.customer_id = c.customer_id

Mart Models

Mart models prepare data for business consumption:

• Oriented around business entities (customers, products, etc.)

• Optimized for specific use cases

• Include calculated metrics

• Often materialized as tables for performance

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

SELECT
  date_trunc('month', o.order_date) as order_month,
  p.payment_method,
  count(distinct o.order_id) as order_count,
  sum(o.amount) as total_amount
FROM {{ ref('int_customer_orders') }} o
JOIN {{ ref('stg_payments') }} p ON o.order_id = p.order_id
GROUP BY 1, 2

---

Troubleshooting Models

When your models have issues, use these strategies to troubleshoot:

Compiling Without Running

Use dbt compile to see the generated SQL without running it:

dbt compile --models customer_lifetime_value

Then check the compiled SQL in target/compiled/[project_name]/models/...

Execute Specific Models

Run only the model you're working on:

dbt run --models staging.stg_customers

Or run a model and everything that depends on it:

dbt run --models +customer_lifetime_value

Check Logs

Detailed logs are available in the logs/ directory and often contain helpful error information.

---

Advanced Model Techniques

Custom Schemas

Generate custom schemas to separate models by team or environment:

{{ 
  config(
    schema='marketing_' ~ target.name
  ) 
}}

SELECT * FROM {{ ref('stg_marketing_campaigns') }}

Post-Hooks

Execute SQL after a model is created, such as granting permissions:

{{ 
  config(
    post_hook='GRANT SELECT ON {{ this }} TO ROLE analytics_readers'
  ) 
}}

SELECT * FROM {{ ref('stg_customers') }}

Documentation in Models

Add descriptions to your models using YAML files:

version: 2

models:
  - name: customer_orders
    description: "One record per customer with order summary data"
    columns:
      - name: customer_id
        description: "The primary key of the customers table"
      - name: order_count
        description: "Count of orders placed by this customer"
        tests:
          - not_null
      - name: total_spent
        description: "Total amount spent on all orders"

These descriptions will appear in your automatically generated documentation.

By mastering models and transformations in dbt, you can build a reliable, maintainable analytics pipeline that transforms raw data into valuable business insights.