dbt project structure - Mini Project: Build & Apply

Understanding dbt Project Structure

📖 Scenario: You are working as a data analyst who wants to organize your data transformation work using dbt (data build tool). You want to create a simple dbt project structure to manage your SQL models and configurations.

🎯 Goal: Build a basic dbt project structure by creating the main folders and files needed to start transforming data with dbt.

📋 What You'll Learn

Create a models folder with a SQL file inside

Create a dbt_project.yml configuration file

Create a profiles.yml file placeholder for connection settings

Understand the purpose of each file and folder in the dbt project

💡 Why This Matters

🌍 Real World

dbt is widely used in data teams to organize and automate data transformations in a clear, maintainable way.

💼 Career

Understanding dbt project structure is essential for data analysts and engineers working with modern data pipelines and analytics workflows.

1

Create the models folder and a SQL model file

Create a folder named models and inside it create a file named my_first_model.sql with the exact content: select 1 as id.

dbt

# Create the models folder and add my_first_model.sql with 'select 1 as id' inside
# Your code here

Hint

The models folder holds your SQL files that define data transformations. Start with a simple SQL file that selects a constant.

2

Create the dbt_project.yml configuration file

Create a file named dbt_project.yml in the root folder with the exact content:
name: 'my_dbt_project' version: '1.0' config-version: 2 profile: 'default' model-paths: ['models']

dbt

# models/my_first_model.sql
select 1 as id

# Create dbt_project.yml with the specified content
# Your code here

Hint

The dbt_project.yml file tells dbt about your project name, version, and where to find your models.

3

Create a placeholder profiles.yml file for connection settings

Create a file named profiles.yml with the exact content:
default: target: dev outputs: dev: type: 'postgres' host: 'localhost' user: 'user' password: 'password' dbname: 'my_database' schema: 'public'

dbt

# models/my_first_model.sql
select 1 as id

# dbt_project.yml
name: 'my_dbt_project'
version: '1.0'
config-version: 2
profile: 'default'
model-paths: ['models']

# Create profiles.yml with the specified content
# Your code here

Hint

The profiles.yml file stores your database connection details. This example uses a local Postgres database.

4

Display the dbt project structure summary

Print the exact text:
Project structure includes models folder with SQL files, dbt_project.yml config file, and profiles.yml connection file.

dbt

# models/my_first_model.sql
select 1 as id

# dbt_project.yml
name: 'my_dbt_project'
version: '1.0'
config-version: 2
profile: 'default'
model-paths: ['models']

# profiles.yml
default:
  target: dev
  outputs:
    dev:
      type: 'postgres'
      host: 'localhost'
      user: 'user'
      password: 'password'
      dbname: 'my_database'
      schema: 'public'

# Print the project structure summary below
# Your code here

Hint

Use a print statement to show the summary text exactly as given.

Practice

1. Which folder in a dbt project typically contains SQL files that define your data transformations?

easy

A. snapshots/

B. models/

C. macros/

D. tests/

Solution

1. Step 1: Understand folder purposes

   The models/ folder is where SQL files for data transformations live.

2. Step 2: Identify correct folder for SQL models

   Other folders like macros/ hold reusable code, snapshots/ hold snapshot definitions, and tests/ hold test files.

3. Final Answer:

   models/ -> Option B

4. Quick Check:

   Data transformations = models/ folder [OK]

Hint: Models folder holds SQL transformations [OK]

Common Mistakes:

• Confusing macros/ with models/
• Thinking snapshots/ holds models
• Assuming tests/ contains SQL models

2. Which of the following is the correct name for the main configuration file in a dbt project?

easy

A. dbt_project.yml

B. dbt_config.yml

C. project.yaml

D. dbt_settings.yml

Solution

1. Step 1: Recall main config file name

   The main configuration file for dbt projects is named dbt_project.yml.

2. Step 2: Verify other options

   Other options like dbt_config.yml or dbt_settings.yml are incorrect names.

3. Final Answer:

   dbt_project.yml -> Option A

4. Quick Check:

   Main config file = dbt_project.yml [OK]

Hint: Main config file always named dbt_project.yml [OK]

Common Mistakes:

• Using dbt_config.yml instead
• Confusing with generic project.yaml
• Assuming settings file controls project

3. Given this dbt project structure snippet:

my_dbt_project/
├── models/
│   ├── customers.sql
│   └── orders.sql
├── macros/
│   └── date_utils.sql
└── dbt_project.yml

Which file would you edit to add a reusable SQL function?

medium

A. models/customers.sql

B. dbt_project.yml

C. macros/date_utils.sql

D. models/orders.sql

Solution

1. Step 1: Identify purpose of macros/ folder

   The macros/ folder holds reusable SQL functions and macros.

2. Step 2: Locate reusable function file

   The file macros/date_utils.sql is the right place to add reusable SQL functions.

3. Final Answer:

   macros/date_utils.sql -> Option C

4. Quick Check:

   Reusable SQL functions = macros/ folder [OK]

Hint: Reusable SQL code goes in macros/ folder [OK]

Common Mistakes:

• Adding functions inside models/ files
• Editing dbt_project.yml for SQL code
• Confusing macros/ with models/

4. You see this error when running dbt: Compilation Error: Could not find model 'sales_summary'. Which is the most likely cause related to project structure?

medium

A. The 'sales_summary.sql' file is missing from the models/ folder.

B. The 'sales_summary.sql' file is inside the macros/ folder.

C. The dbt_project.yml file is missing.

D. The snapshots/ folder contains 'sales_summary.sql'.

Solution

1. Step 1: Understand error meaning

   The error means dbt cannot find the model named 'sales_summary'.

2. Step 2: Check model file location

   Models must be in the models/ folder. If the file is missing there, dbt can't compile it.

3. Final Answer:

   The 'sales_summary.sql' file is missing from the models/ folder. -> Option A

4. Quick Check:

   Missing model file in models/ causes compilation error [OK]

Hint: Models must be in models/ folder to compile [OK]

Common Mistakes:

• Placing model files in macros/ folder
• Assuming missing dbt_project.yml causes this error
• Confusing snapshots/ with models/

5. You want to organize your dbt project so that models related to customers and orders are in separate folders inside models/. How should you update your dbt_project.yml to reflect this structure?

hard

A. Add models:
my_dbt_project:
+materialized: view only

B. No changes needed; dbt auto-detects subfolders without config

C. Add macros:
my_dbt_project:
customers:
+materialized: table

D. Add models:
my_dbt_project:
customers:
+materialized: table
orders:
+materialized: table

Solution

1. Step 1: Understand folder-specific config in dbt_project.yml

   You can specify configs per subfolder inside models/ by nesting them under your project name.

2. Step 2: Define separate configs for customers and orders folders

   Adding customers: and orders: keys with materialization settings applies configs to those subfolders.

3. Final Answer:

   Add models: my_dbt_project: customers: +materialized: table orders: +materialized: table -> Option D

4. Quick Check:

   Use nested keys in dbt_project.yml for subfolder configs [OK]

Hint: Use nested keys in dbt_project.yml for subfolder configs [OK]

Common Mistakes:

• Not nesting configs under project name
• Configuring macros instead of models
• Assuming no config needed for subfolders