Organizing models in directories in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding model directory structure in dbt

In dbt, how does organizing models into subdirectories affect the resulting database schema and table names?

A Models in subdirectories are flattened into the same schema as the root models folder, ignoring folder names.

B Each subdirectory creates a separate database, isolating models completely.

C Subdirectories in the models folder create nested schemas in the database, reflecting the folder structure.

D Models in subdirectories are ignored unless explicitly referenced in the dbt_project.yml file.

💡 Hint

Think about how dbt treats folder names by default when building models.

By default, dbt compiles models into the target schema without creating nested schemas for subdirectories. The folder structure helps organize code but does not affect the database schema names.

❓ Predict Output

intermediate

Output schema and table name from nested model

Given the following dbt model file path and configuration, what will be the schema and table name of the compiled model?

models/sales/2024/orders.sql

dbt_project.yml contains:

models:
  sales:
    +schema: sales_data

dbt

select * from raw.orders

A Schema: public, Table: orders

B Schema: sales, Table: orders

C Schema: sales_data, Table: orders

D Schema: sales_data, Table: sales_2024_orders

💡 Hint

Check how the +schema config applies to subdirectories.

The +schema config under 'sales' applies to all models in the sales folder and its subfolders. The table name is the model file name without folder prefixes.

❓ data_output

advanced

Resulting model names from multiple directories

Consider a dbt project with these model files:

• models/customers.sql
• models/finance/invoices.sql
• models/finance/2024/payments.sql

With no special schema or alias configurations, what are the compiled table names for these models?

A customers, finance_invoices, finance_2024_payments

B customers, invoices, payments

C customers, invoices, finance_2024_payments

D customers, finance_invoices, payments

💡 Hint

Think about how dbt names tables by default from file names.

By default, dbt uses the model file name as the table name, ignoring folder names. So all tables are named after their file names only.

🔧 Debug

advanced

Why is a model not found after moving to a subdirectory?

A model 'sales_summary.sql' was moved from models/ to models/sales/. After running dbt, a downstream model referencing it fails with 'model not found'. What is the most likely cause?

A The model was not added to the manifest because subdirectories are ignored by dbt.

B dbt requires updating the dbt_project.yml to include new subdirectories explicitly.

C The model file name must be changed to include the subdirectory name.

D The downstream model's ref() call did not update to include the subdirectory path.

💡 Hint

Check how dbt's ref() function resolves model names.

dbt's ref() function uses the model name, which is the file name without folder prefixes. Moving a model to a subdirectory does not change its ref() name, so the downstream model's ref() call should remain the same.

🚀 Application

expert

Configuring dbt to create schemas matching model directories

You want dbt to create separate schemas in the database that match your model directory names (e.g., models/sales/ creates schema 'sales'). Which configuration achieves this behavior?

A Use the <code>+schema</code> config with a Jinja expression referencing <code>this.path</code> and the model path.

B Set <code>generate_schema_name</code> in dbt_project.yml to use the directory name dynamically.

C Set <code>materialized: ephemeral</code> on all models in subdirectories.

D Rename all model files to include the directory name as a prefix.

💡 Hint

Look into how to use Jinja in model configs to customize schema names.

Using the +schema config with a Jinja expression that extracts the directory name from this.path allows dbt to create schemas matching directories dynamically.

Practice

1. Why is it helpful to organize dbt models into directories?

easy

A. It keeps the project clean and easier to manage.

B. It makes dbt run faster.

C. It prevents errors in SQL syntax.

D. It automatically creates dashboards.

Solution

1. Step 1: Understand project organization benefits

   Organizing files into folders helps keep things tidy and easy to find.

2. Step 2: Relate to dbt model management

   In dbt, directories group models logically, making the project easier to manage.

3. Final Answer:

   It keeps the project clean and easier to manage. -> Option A

4. Quick Check:

   Organizing models = easier management [OK]

Hint: Folders group models logically for clarity [OK]

Common Mistakes:

• Thinking folders speed up dbt runs
• Believing folders fix SQL errors
• Assuming folders create dashboards automatically

2. Which of the following is the correct way to reference a model in a subdirectory in dbt SQL?

easy

A. SELECT * FROM subfolder-model_name

B. SELECT * FROM model_name.subfolder

C. SELECT * FROM subfolder.model_name

D. SELECT * FROM model_name

Solution

1. Step 1: Understand dbt model referencing

   dbt uses dot notation to reference models in subfolders: folder.model_name.

2. Step 2: Check each option

   Only SELECT * FROM subfolder.model_name uses correct dot notation with folder before model name.

3. Final Answer:

   SELECT * FROM subfolder.model_name -> Option C

4. Quick Check:

   Use folder.model_name to reference subfolder models [OK]

Hint: Use dot notation: folder.model_name [OK]

Common Mistakes:

• Reversing folder and model name order
• Using hyphens instead of dots
• Ignoring folder name when referencing

3. Given this dbt project structure:

models/
  sales/
    orders.sql
  marketing/
    campaigns.sql

What is the correct way to select all columns from the orders model inside the sales folder?

medium

A. SELECT * FROM orders

B. SELECT * FROM sales.orders

C. SELECT * FROM marketing.campaigns

D. SELECT * FROM models.sales.orders

Solution

1. Step 1: Identify model location

   The orders model is inside the sales folder under models.

2. Step 2: Use correct reference syntax

   dbt references models using folder.model_name, so sales.orders is correct.

3. Final Answer:

   SELECT * FROM sales.orders -> Option B

4. Quick Check:

   Reference subfolder models as folder.model_name [OK]

Hint: Use folder.model_name to select subfolder models [OK]

Common Mistakes:

• Omitting folder name when selecting
• Using full path with 'models.' prefix
• Selecting from wrong folder model

4. You have this dbt model file path: models/finance/revenue.sql. You wrote this SQL:

SELECT * FROM revenue

Why might this cause an error when running dbt?

medium

A. Because the model should be referenced as finance.revenue.

B. Because revenue.sql file is missing.

C. Because SELECT * FROM revenue is invalid SQL syntax.

D. Because dbt does not support subfolders.

Solution

1. Step 1: Understand model referencing in subfolders

   Models inside subfolders must be referenced with folder prefix, e.g., finance.revenue.

2. Step 2: Identify cause of error

   Using just revenue ignores the folder, causing dbt to not find the model.

3. Final Answer:

   Because the model should be referenced as finance.revenue. -> Option A

4. Quick Check:

   Reference subfolder models with folder prefix [OK]

Hint: Always prefix model with folder name in references [OK]

Common Mistakes:

• Assuming model name alone works in subfolders
• Thinking SQL syntax is wrong
• Believing dbt disallows subfolders

5. You want to organize your dbt models by year and quarter. Which directory structure best supports this while keeping references simple?

hard

A. models/year_2024_q1_sales.sql and reference as year_2024_q1_sales

B. models/q1/year_2024/sales.sql and reference as q1.year_2024.sales

C. models/sales/year_2024_q1.sql and reference as sales.year_2024_q1

D. models/year_2024/q1/sales.sql and reference as year_2024.q1.sales

Solution

1. Step 1: Understand multi-level directory organization

   Organizing by year then quarter creates clear hierarchy: year_2024/q1/.

2. Step 2: Check referencing simplicity

   dbt references nested folders with dot notation: year_2024.q1.sales is clear and logical.

3. Final Answer:

   models/year_2024/q1/sales.sql and reference as year_2024.q1.sales -> Option D

4. Quick Check:

   Use nested folders with dot notation references [OK]

Hint: Use nested folders matching dot notation references [OK]

Common Mistakes:

• Reversing folder order causing confusing references
• Using flat filenames losing hierarchy clarity
• Ignoring dot notation for nested folders