What is SQL Model in dbt: Explanation and Example

In dbt, a SQL model is a file containing a SQL query that defines a transformation on your raw data. When you run dbt, it compiles and runs these SQL models to create tables or views in your data warehouse.

How It Works

Think of a SQL model in dbt like a recipe for making a dish. The recipe (SQL code) tells the kitchen (data warehouse) how to prepare the dish (transformed data). When you run dbt, it follows the recipe to create or update the dish exactly as specified.

Each SQL model is a simple SQL file that selects and transforms data from your raw tables or other models. dbt manages the order of these models based on dependencies, so your data is transformed step-by-step in the right sequence.

This approach helps you build clean, tested, and documented data tables that are easy to maintain and understand.

Example

This example shows a simple SQL model that selects customer data and calculates their total spending.

sql

select
  customer_id,
  customer_name,
  sum(order_amount) as total_spent
from raw.orders
group by customer_id, customer_name

Output

customer_id | customer_name | total_spent
------------|---------------|------------
1           | Alice         | 250
2           | Bob           | 400
3           | Carol         | 150

When to Use

Use SQL models in dbt whenever you want to transform raw data into clean, analysis-ready tables. This includes:

• Aggregating data, like totals or averages
• Joining multiple tables to combine information
• Filtering or cleaning data
• Creating intermediate tables for complex workflows

SQL models help organize your data pipeline so analysts and data scientists can work with reliable, well-structured data.

Key Points

• A SQL model is a SQL file defining a data transformation.
• dbt runs models to build tables or views in your warehouse.
• Models depend on each other and run in order automatically.
• They help create clean, tested, and documented data.

Key Takeaways

A SQL model in dbt is a SQL query that transforms raw data into useful tables or views.

dbt runs SQL models in the right order based on dependencies.

Use SQL models to clean, join, aggregate, and prepare data for analysis.

Models make your data pipeline organized, maintainable, and reliable.