Creating your first model in dbt - Try It Yourself

Creating your first model

📖 Scenario: You are working as a data analyst at a retail company. You want to create a simple dbt model that selects customer data from a source table and prepares it for analysis.

🎯 Goal: Build a basic dbt model that selects customer names and their total purchase amounts from the source table raw.customers.

📋 What You'll Learn

Create a dbt model SQL file named customer_purchases.sql

Select the columns customer_id, customer_name, and total_purchases from raw.customers

Filter to include only customers with total_purchases greater than 100

Order the results by total_purchases in descending order

💡 Why This Matters

🌍 Real World

Creating dbt models helps organize and transform raw data into clean, usable datasets for business analysis.

💼 Career

Data analysts and engineers use dbt models to build reliable data pipelines and prepare data for reporting and machine learning.

1

Create the initial dbt model file

Create a dbt model SQL file named customer_purchases.sql that selects the columns customer_id, customer_name, and total_purchases from the source table raw.customers.

dbt

-- Your SQL code here
SELECT customer_id, customer_name, total_purchases
FROM raw.customers

Need a hint?

Use a SELECT statement to choose the columns and FROM to specify the source table.

2

Add a filter for total purchases

Add a WHERE clause to the existing model to include only customers with total_purchases greater than 100.

dbt

SELECT customer_id, customer_name, total_purchases
FROM raw.customers
-- Add a WHERE clause to filter total_purchases > 100

Need a hint?

Use WHERE total_purchases > 100 to filter the rows.

3

Order the results by total purchases

Add an ORDER BY clause to the model to sort the results by total_purchases in descending order.

dbt

SELECT customer_id, customer_name, total_purchases
FROM raw.customers
WHERE total_purchases > 100
-- Add ORDER BY total_purchases DESC

Need a hint?

Use ORDER BY total_purchases DESC to sort from highest to lowest.

4

Display the final model output

Print the final SQL query for the dbt model customer_purchases.sql.

dbt

-- Print the final SQL query
print("SELECT customer_id, customer_name, total_purchases FROM raw.customers WHERE total_purchases > 100 ORDER BY total_purchases DESC")

Need a hint?

Use a print statement to show the final SQL query as a string.