One model per source table rule in dbt - Step-by-Step Execution

Concept Flow - One model per source table rule

Identify source tables

↓

Create one model per source table

↓

Write SQL SELECT for each model

↓

Run dbt to build models

↓

Models reflect source tables individually

↓

Use models for downstream analysis

This flow shows how for each source table, you create one dbt model that selects from it, keeping models simple and focused.

Execution Sample

dbt

select * from raw.customers;

select * from raw.orders;

Two simple dbt models each selecting all data from one source table.

Execution Table

Step	Action	Model Created	SQL Query	Result
1	Identify source table 'customers'	None	N/A	Source table found
2	Create model 'customers'	customers.sql	select * from raw.customers;	Model ready
3	Identify source table 'orders'	None	N/A	Source table found
4	Create model 'orders'	orders.sql	select * from raw.orders;	Model ready
5	Run dbt build	customers.sql, orders.sql	Run both queries	Models built separately
6	Use models in analysis	customers, orders	select * from {{ ref('customers') }};	Data from customers model
7	Exit	N/A	N/A	All models created and ready

💡 All source tables have one corresponding model; dbt run completes successfully.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	Final
models_created	[]	['customers']	['customers', 'orders']	['customers', 'orders']	['customers', 'orders']
dbt_run_status	Not run	Not run	Not run	Success	Success

Key Moments - 2 Insights

Why do we create one model per source table instead of combining multiple tables in one model?

Creating one model per source table keeps models simple and easier to maintain, as shown in execution_table steps 2 and 4 where each model selects from only one source.

What happens if you forget to create a model for a source table?

That source table won't have a corresponding model, so downstream analysis can't use it. Execution_table step 7 shows all models created; missing one would stop here.

Visual Quiz - 3 Questions

Look at the execution table, after which step is the 'orders' model created?

A Step 4

B Step 5

C Step 2

D Step 3

💡 Hint

Check the 'Model Created' column in execution_table rows.

According to variable_tracker, what is the status of dbt_run_status after step 5?

A Not run

B Success

C Failed

D Running

💡 Hint

Look at the 'dbt_run_status' row and the 'After Step 5' column.

If you combined both source tables into one model, how would the execution_table change?

A The dbt run would fail

B There would be more steps for each source table

C There would be only one model creation step instead of two

D No change in the table

💡 Hint

Refer to the number of model creation steps in execution_table.

Concept Snapshot

One model per source table rule:
- Create one dbt model for each source table
- Each model contains a simple SELECT from its source
- Keeps models clear and maintainable
- Run dbt to build all models separately
- Use models for downstream analysis

Full Transcript

In dbt, the 'One model per source table' rule means you create a separate model for each source table you want to use. For example, if you have source tables named 'customers' and 'orders', you write one model selecting from 'customers' and another selecting from 'orders'. This keeps your models simple and easy to manage. The execution flow starts by identifying each source table, then creating a model for it, running dbt to build these models, and finally using these models in your analysis. The execution table shows each step, including model creation and running dbt. The variable tracker shows how the list of models grows and the dbt run status changes from not run to success. Key moments include understanding why one model per source table is better and what happens if a model is missing. The visual quiz tests your understanding of when models are created and the dbt run status. Remember, this rule helps keep your data transformations organized and clear.

Practice

1. What is the main reason for following the one model per source table rule in dbt?

easy

A. To avoid using source tables in models

B. To combine multiple source tables into one model

C. To write fewer SQL queries

D. To keep models simple and easy to understand

Solution

1. Step 1: Understand the rule and its benefits

   The rule encourages creating one dbt model for each source table to keep things simple and clear. This approach helps with clarity, debugging, and teamwork by avoiding complex combined models.

2. Final Answer:

   To keep models simple and easy to understand -> Option D

3. Quick Check:

   One model per source table = simplicity [OK]

Hint: One model per source table means simpler, clearer models [OK]

Common Mistakes:

• Thinking it means combining tables into one model
• Believing it reduces the number of models
• Assuming it avoids using source tables

2. Which of the following is the correct way to define a dbt model for a source table named customers?

easy

A. Create a model file named all_data.sql combining all source tables

B. Create a model file named customers.sql with a SELECT from the source table

C. Create a model file named customers.sql but do not select from the source table

D. Create a model file named source_customers.sql without referencing the source

Solution

1. Step 1: Match model name to source table and reference it

   Following the rule, the model file should be named after the source table, here customers.sql. The model should select data from the source table to represent it clearly.

2. Final Answer:

   Create a model file named customers.sql with a SELECT from the source table -> Option B

3. Quick Check:

   Model name = source table name + SELECT from source [OK]

Hint: Model file name matches source table name and selects from it [OK]

Common Mistakes:

• Naming model file unrelated to source table
• Not selecting from the source table
• Combining multiple tables in one model

3. Given this dbt model SQL for source table orders:

select * from {{ source('sales_db', 'orders') }}

What will be the output if the source table has 100 rows?

medium

A. The model will return 100 rows identical to the source table

B. The model will return zero rows

C. The model will return 1 row with aggregated data

D. The model will cause a syntax error

Solution

1. Step 1: Understand the SQL query and predict output

   The query selects all columns and rows from the source table orders. Since it selects all rows without filtering, the output will have the same 100 rows as the source.

2. Final Answer:

   The model will return 100 rows identical to the source table -> Option A

3. Quick Check:

   Select * from source = same rows [OK]

Hint: Select * from source returns all rows unchanged [OK]

Common Mistakes:

• Thinking it returns zero rows
• Assuming aggregation happens automatically
• Expecting syntax errors from correct source() usage

4. You wrote this dbt model for source table products:

select * from source('inventory', 'products')

But dbt throws an error. What is the problem?

medium

A. Model file name does not match source table

B. Wrong source table name

C. Missing double curly braces around the source function

D. Using select * is not allowed

Solution

1. Step 1: Check dbt source function syntax and identify issue

   The source function must be wrapped in double curly braces: {{ source(...) }}. The model uses source(...) without {{ }}, causing a syntax error.

2. Final Answer:

   Missing double curly braces around the source function -> Option C

3. Quick Check:

   dbt functions need {{ }} [OK]

Hint: Always wrap dbt functions in {{ }} [OK]

Common Mistakes:

• Forgetting {{ }} around source()
• Assuming select * is disallowed
• Ignoring model file naming conventions

5. You have two source tables: customers and orders. According to the one model per source table rule, how should you organize your dbt models if you want to create a report showing total orders per customer?

hard

A. Create separate base models for customers and orders, then create a third model joining them for the report

B. Create one model combining both source tables directly without base models

C. Create a single model selecting from both source tables with UNION

D. Create only one model for orders and ignore customers

Solution

1. Step 1: Follow one model per source table for base models and build report model

   Create one model for customers and one for orders separately to keep base models simple. Create a third model that joins these base models to calculate total orders per customer, keeping transformations clear and modular.

2. Final Answer:

   Create separate base models for customers and orders, then create a third model joining them for the report -> Option A

3. Quick Check:

   Base models per source + separate report model [OK]

Hint: Base models per source, then join in separate model [OK]

Common Mistakes:

• Combining sources in one base model
• Skipping base models and joining sources directly
• Ignoring one source table