Organizing models in directories in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to specify the directory for models in the dbt project configuration.

dbt

models:
  [1]:  # directory name
    +materialized: view

Drag options to blanks, or click blank then click option'

Aanalysis

Bstaging

Cmodels

Dsources

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'models' as a directory name inside the models config causes redundancy.

Confusing 'sources' with model directories; sources are defined separately.

The staging directory is commonly used to organize raw source data models in dbt.

2fill in blank

medium

Complete the code to define a subdirectory for customer models inside the models directory.

dbt

models:
  [1]:
    +materialized: table

Drag options to blanks, or click blank then click option'

Amarketing

Bsales

Ccustomers

Dfinance

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing unrelated directory names like 'finance' when the models are about customers.

Using uppercase letters in directory names which is not a common practice.

The customers subdirectory organizes models related to customer data.

3fill in blank

hard

Fix the error in the dbt project configuration to correctly organize models in the 'analytics' directory.

dbt

models:
  analytics:
    +materialized: [1]

Drag options to blanks, or click blank then click option'

Atable

Bview

Cincremental

Dtemporary

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'temporary' which is not a valid materialization in dbt.

Choosing 'view' which creates non-persistent models.

The table materialization creates persistent tables, suitable for analytics models.

4fill in blank

hard

Fill both blanks to configure the 'finance' directory with incremental models and the 'marketing' directory with views.

dbt

models:
  finance:
    +materialized: [1]
  marketing:
    +materialized: [2]

Drag options to blanks, or click blank then click option'

Aincremental

Bview

Ctable

Dephemeral

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up 'table' and 'incremental' materializations.

Using 'ephemeral' which does not create physical tables or views.

Finance models often use incremental materialization for efficiency. Marketing models commonly use view materialization for flexibility.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps model names to their materializations, filtering only models with materialization 'table'.

dbt

model_materializations = {model: config[1] for model, config in models_config.items() if config[1][2] 'table' and model[3]('sales')}

Drag options to blanks, or click blank then click option'

A['materialized']

B==

Cstartswith

Din

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'in' instead of 'startswith' for filtering model names.

Using dot notation instead of square brackets to access dictionary keys.

This comprehension extracts the 'materialized' value from each config, filters for models materialized as 'table', and only includes models whose names start with 'sales'.

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