One model per source table rule in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why follow the one model per source table rule in dbt?

In dbt, why is it recommended to create exactly one model for each source table?

A It automatically creates indexes on all source tables.

B It reduces the number of SQL queries dbt runs, improving performance.

C It allows multiple models to write to the same table simultaneously.

D It simplifies debugging and testing by isolating transformations per source table.

💡 Hint

Think about how isolating transformations helps when you want to find errors or test data.

Having one model per source table keeps transformations clear and isolated. This makes debugging and testing easier because each model corresponds directly to one source table.

❓ Predict Output

intermediate

Output of dbt model with one source table

Given this dbt model SQL code that selects all columns from a source table, what will be the output schema?

dbt

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

A An empty table because no filters are applied.

B An error because source() function is not valid in dbt models.

C A table with all columns from the 'orders' source table.

D A table with only the primary key column from 'orders'.

💡 Hint

The source() function references the source table directly.

The model selects all columns from the 'orders' source table, so the output schema matches the source table's columns.

❓ data_output

advanced

Resulting row count after one model per source table transformation

Assume the source table 'customers' has 1000 rows. A dbt model applies a filter to select only customers from 'USA'. If 300 rows match, how many rows will the model output have?

A 300 rows, because the filter selects only USA customers.

B 1000 rows, because the model does not change row count.

C 0 rows, because filters are ignored in dbt models.

D 1300 rows, combining all customers and filtered ones.

💡 Hint

Filters in SQL reduce the number of rows returned.

The model filters the source table to only include customers from USA, so the output has 300 rows.

🔧 Debug

advanced

Identify the error in violating one model per source table rule

Which problem arises if you create multiple dbt models that transform the same source table independently?

A Data duplication and inconsistent transformations across models.

B dbt automatically merges models, so no problem occurs.

C The source table gets deleted after the first model runs.

D dbt throws a syntax error during compilation.

💡 Hint

Think about what happens if you have different versions of the same data.

Multiple models on the same source table can cause duplicated data and inconsistent results, making maintenance harder.

🚀 Application

expert

Best practice application of one model per source table rule

You have three source tables: 'orders', 'customers', and 'products'. You want to build a sales report model that combines data from all three. According to the one model per source table rule, how should you organize your dbt models?

A Create one big model that directly queries all three source tables and produces the report.

B Create three separate models, one for each source table, then create a fourth model that joins these three models for the report.

C Create one model per source table and duplicate the join logic in each model.

D Create a single model that copies all source tables into one table before reporting.

💡 Hint

Think about modularity and reusability in dbt models.

Following the rule, each source table gets its own model. Then a separate model combines them for the report. This keeps transformations clear and modular.

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