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.