One model per source table rule in dbt - Mini Project: Build & Apply

One Model Per Source Table Rule in dbt

📖 Scenario: You work as a data analyst in a company that collects sales data from multiple stores. Each store's raw data is stored in separate source tables in your data warehouse. To keep your data models clean and easy to maintain, you want to follow the one model per source table rule in dbt.This means you will create one dbt model for each source table, transforming the raw data step-by-step.

🎯 Goal: Build dbt models that follow the one model per source table rule by creating a model for each source table and then combining them in a final model.You will start by defining source tables, then create models for each source, and finally create a combined model that joins the data.

📋 What You'll Learn

Define source tables in dbt source configuration

Create one dbt model per source table

Create a final dbt model that joins the individual models

Use simple SQL SELECT statements in models

💡 Why This Matters

🌍 Real World

In real companies, raw data often comes from many tables. Creating one model per source table helps organize transformations clearly and makes debugging easier.

💼 Career

Data analysts and engineers use dbt to build reliable data pipelines. Understanding this rule is key to writing clean, maintainable dbt projects.

1

Define source tables in dbt

In your sources.yml file, define two source tables named store_a_sales and store_b_sales under the source called raw_sales. Each source table should have columns sale_id, product, and amount.

dbt

# Define source tables in sources.yml
# Your code here

Need a hint?

Remember to define the source name raw_sales and list both tables store_a_sales and store_b_sales with their columns.

2

Create one model per source table

Create two dbt models named store_a_sales.sql and store_b_sales.sql. Each model should select all columns from its respective source table using the source() function, for example, select * from {{ source('raw_sales', 'store_a_sales') }}.

dbt

-- In store_a_sales.sql
-- Your code here

-- In store_b_sales.sql
-- Your code here

Need a hint?

Use the source() function to select all columns from each source table in separate model files.

3

Create a combined model joining the two sources

Create a dbt model named combined_sales.sql that joins the two models store_a_sales and store_b_sales on sale_id. Select sale_id, product from store_a_sales, and amount from both tables with aliases amount_a and amount_b.

dbt

-- combined_sales.sql
-- Your code here

Need a hint?

Use {{ ref('model_name') }} to refer to other models and join them on sale_id.

4

Print the combined sales model SQL

Print the SQL query from the combined_sales.sql model to see the final combined data.

dbt

# Print the combined_sales.sql query below
# Your code here

Need a hint?

Print the final SQL query string exactly as it appears in the combined_sales model.