dbt_project.yml configuration - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the purpose of dbt_project.yml

What is the main purpose of the dbt_project.yml file in a dbt project?

A It contains SQL queries to transform data in the warehouse.

B It stores the raw data extracted from the source databases.

C It defines the project configuration including model paths, version, and materializations.

D It manages user permissions and access control for the dbt project.

💡 Hint

Think about where dbt stores project-wide settings.

The dbt_project.yml file is the main configuration file for a dbt project. It sets paths, versions, and default behaviors like materializations.

❓ Predict Output

intermediate

Output of model materialization setting

Given this snippet from dbt_project.yml:

models:
  my_project:
    +materialized: view
    staging:
      +materialized: table

What materialization will the model staging.orders use?

A incremental

B view

C ephemeral

D table

💡 Hint

Check if there is a more specific setting for staging models.

The staging folder has a specific materialization set to table, which overrides the project-level view setting.

❓ data_output

advanced

Effect of source-paths configuration

Consider this dbt_project.yml snippet:

source-paths:
  - models
  - analysis

If you add a new SQL model file under analysis/, will dbt compile it as a model?

A No, because <code>analysis</code> files are not compiled as models by default.

B Yes, because <code>analysis</code> is included in <code>source-paths</code>.

C Yes, but only if the file has a specific naming convention.

D No, because <code>source-paths</code> only applies to data sources, not models.

💡 Hint

Think about the difference between models and analysis directories in dbt.

The analysis directory is for analysis files, not models. Even if included in source-paths, dbt does not compile analysis files as models.

🔧 Debug

advanced

Identify the error in dbt_project.yml snippet

What error will occur with this dbt_project.yml snippet?

models:
  my_project:
    +materialized: table
    +schema: analytics
    staging:
      +materialized: incremental
      +schema: staging_data

A YAML parsing error due to incorrect indentation.

B No error; configuration is valid.

C Runtime error because <code>+schema</code> cannot be set at folder level.

D Error because <code>+materialized</code> cannot be incremental.

💡 Hint

Check YAML indentation and valid dbt config keys.

The snippet is correctly indented and uses valid dbt config keys. Incremental materialization and schema overrides are allowed.

🚀 Application

expert

Predict the schema for nested model configurations

Given this dbt_project.yml snippet:

models:
  my_project:
    +schema: analytics
    marts:
      +schema: marts_schema
      finance:
        +schema: finance_schema
        +materialized: table

What schema will the model marts.finance.revenue use?

A finance_schema

B analytics

C default schema of the warehouse

D marts_schema

💡 Hint

Remember that nested configurations override parent settings.

The most specific schema setting applies. Here, finance_schema overrides marts_schema and analytics.

Practice

1. What is the primary purpose of the dbt_project.yml file in a dbt project?

easy

A. To configure project settings like name, version, and model paths

B. To write SQL queries for data transformation

C. To store raw data files for analysis

D. To define user access permissions for the project

Solution

1. Step 1: Understand the role of dbt_project.yml

   This file tells dbt how to run the project by setting configurations.

2. Step 2: Identify what configurations it includes

   It sets project name, version, file paths, and model build settings, not SQL or data files.

3. Final Answer:

   To configure project settings like name, version, and model paths -> Option A

4. Quick Check:

   Project configuration = To configure project settings like name, version, and model paths [OK]

Hint: Remember: dbt_project.yml sets project-wide settings [OK]

Common Mistakes:

• Confusing dbt_project.yml with SQL model files
• Thinking it stores data instead of configurations
• Assuming it manages user permissions

2. Which of the following is the correct YAML syntax to set the project name to "my_dbt_project" in dbt_project.yml?

easy

A. project_name: my_dbt_project

B. project: "my_dbt_project"

C. project-name: my_dbt_project

D. name: my_dbt_project

Solution

1. Step 1: Recall the correct key for project name in dbt_project.yml

   The key to set the project name is name, not project_name or others.

2. Step 2: Check YAML syntax

   YAML uses key: value pairs, so name: my_dbt_project is correct syntax.

3. Final Answer:

   name: my_dbt_project -> Option D

4. Quick Check:

   Project name key = name [OK]

Hint: Use name: to set project name in YAML [OK]

Common Mistakes:

• Using underscores or hyphens incorrectly in key names
• Putting project name under wrong key
• Using quotes unnecessarily or missing them when needed

3. Given this snippet in dbt_project.yml:

models:
  my_project:
    materialized: table
    schema: analytics

What will be the materialization type and schema for models in the my_project namespace?

medium

A. Materialized as views, schema is default

B. Materialized as tables, schema is analytics

C. Materialized as incremental, schema is analytics

D. Materialized as tables, schema is default

Solution

1. Step 1: Read the materialized setting under models.my_project

   The setting materialized: table means models will be built as tables.

2. Step 2: Check the schema setting

   The schema is explicitly set to analytics, so models will be created in that schema.

3. Final Answer:

   Materialized as tables, schema is analytics -> Option B

4. Quick Check:

   Materialized = table, schema = analytics [OK]

Hint: Look for materialized and schema keys under models [OK]

Common Mistakes:

• Assuming default schema when schema is set
• Confusing materialized types like view vs table
• Ignoring indentation that defines scope

4. Identify the error in this dbt_project.yml snippet:

name: my_project
version: 1.0
models:
  my_project:
    materialized: table
    schema analytics

medium

A. Version should be a string, not a number

B. Incorrect indentation for 'models' key

C. Missing colon after 'schema' key

D. Project name should be under 'project' key

Solution

1. Step 1: Check YAML syntax for key-value pairs

   Each key must be followed by a colon and a space before the value. Here, schema analytics misses the colon.

2. Step 2: Verify other parts

   Indentation and version format are correct; project name uses correct key name.

3. Final Answer:

   Missing colon after 'schema' key -> Option C

4. Quick Check:

   YAML key-value needs colon [OK]

Hint: Check every key has a colon and space before value [OK]

Common Mistakes:

• Forgetting colon after keys
• Wrong indentation levels
• Using wrong keys for project name

5. You want to configure your dbt project so that all models in the sales folder are materialized as incremental and use the schema sales_data. Which dbt_project.yml snippet correctly achieves this?

hard

A. models:
sales:
+materialized: incremental
+schema: sales_data

B. models:
sales:
materialized: incremental
schema: sales_data

C. models:
sales:
materialized: incremental
+schema: sales_data

D. models:
sales:
+materialized: incremental
schema: sales_data

Solution

1. Step 1: Understand dbt config syntax for folder-level overrides

   To set configs for all models in a folder, use the '+' prefix before keys.

2. Step 2: Check which options use '+' for both keys

   Both materialized and schema need '+' prefix to apply to all models in sales folder.

3. Final Answer:

   models: sales: +materialized: incremental +schema: sales_data -> Option A

4. Quick Check:

   Folder configs use '+' prefix [OK]

Hint: Use '+' before keys to apply configs to all models in a folder [OK]

Common Mistakes:

• Missing '+' prefix for folder-level configs
• Using '+' on only one key instead of both
• Confusing folder name with project name