Models and Transformations

What Are Models?

In dbt™, a model is simply a SQL file that defines a transformation. Models are the core building blocks of a dbt™ project and allow you to structure your data processing efficiently within your data warehouse.

Instead of running isolated SQL queries, dbt™ organizes SQL logic into modular, reusable models that are version-controlled, testable, and automatically documented.

How Models Work

1. You write a SQL query – A model is a SELECT statement that defines how data should be transformed.

2. dbt™ compiles it into executable SQL – It resolves dependencies, applies Jinja logic, and prepares the SQL for execution.

3. The query runs inside your data warehouse – The final transformed data is stored as a table or a view.

Example: A Simple dbt™ Model

A model file (models/staging/stg_orders.sql) might look like this:

select  
  order_id,  
  customer_id,  
  order_date,  
  total_amount  
from {{ source('jaffle_shop', 'orders') }}  
where order_status = 'complete'

When you run dbt run, dbt™ compiles this SQL and runs it in your data warehouse, creating either a table or a view, depending on the materialization settings.

---

Model Structure and Syntax

1. Using the ref() Function

The ref() function allows you to reference other models within your dbt™ project, ensuring dependencies are properly managed.

Example (models/marts/orders_summary.sql):

select  
  customer_id,  
  count(order_id) as total_orders,  
  sum(total_amount) as total_revenue  
from {{ ref('stg_orders') }}  
group by customer_id

• {{ ref('stg_orders') }} dynamically compiles to the full table name in the warehouse.

• It ensures dependency tracking, so dbt™ runs models in the correct order.

2. Model Configuration (config())

You can configure models using the config() function to control behavior such as materialization and indexing.

Example: Configuring a model to materialize as a table (models/marts/orders_summary.sql):

{{ config(materialized='table', sort='order_date', unique_key='order_id') }}  

select * from {{ ref('stg_orders') }}

This ensures:

• The model is stored as a table instead of a view.

• The table is sorted by order_date.

• order_id is defined as a unique key.

💡 Want to learn more about Model Materialization types? See Model Materialization documentation.

3. Using Jinja for Dynamic SQL

dbt™ extends SQL with Jinja, a templating engine that allows for reusable logic.

Example: Defining an active customers macro (macros/active_customers.sql):

{% macro active_customers() %}  
   select * from customers where status = 'active'  
{% endmacro %}

You can then use this macro inside a model:

select * from {{ active_customers() }}

Benefits of Using Jinja for Dynamic SQL

✅ Eliminates redundant SQL ✅ Ensures consistency across transformations ✅ Simplifies query maintenance

---

Model Dependencies

1. How dbt™ Handles Dependencies

When models reference each other using ref(), dbt™ automatically determines the correct execution order.

Example dependency chain:

1. stg_orders pulls raw order data.

2. orders_summary aggregates the staged order data.

3. customer_revenue joins order summaries with customer details.

To visualize dependencies, run:

docs generate
dbt docs serve 

This generates an interactive model lineage graph. It only works for dbt core users.

2. Managing Dependencies with ref()

By using ref(), dbt™ ensures:

• Correct execution order – Models are built in the right sequence.

• Better maintainability – Table names are dynamically resolved.

• Automated testing and documentation – Dependencies are tracked.

---

Best Practices for dbt™ Models

• Follow a consistent project structure – Use staging/, marts/, and intermediate/ models.

• Use ref() instead of hardcoding table names – This ensures maintainability and dependency tracking.

• Keep models modular – Avoid writing complex SQL in a single model; break logic into separate models.

• Apply testing to critical models – Use schema tests to validate integrity.

• Leverage Jinja macros – Reduce duplication and enforce consistency across transformations.