Organizing models in directories in dbt - Step-by-Step Execution

Concept Flow - Organizing models in directories

Create root models folder

↓

Create subdirectories for model groups

↓

Place SQL model files in subdirectories

↓

dbt reads directory structure

↓

Models are organized logically

↓

Run dbt to build models

↓

Output models reflect directory organization

Organize your dbt SQL models into folders to keep related models together, making your project easier to manage and understand.

Execution Sample

dbt

models/
  sales/
    sales_summary.sql
    sales_details.sql
  marketing/
    campaign_performance.sql
    leads.sql

This folder structure groups sales and marketing models into separate directories under the main models folder.

Execution Table

Step	Action	Directory Structure	Model Files Found	dbt Behavior
1	Start with empty models folder	models/	None	No models to build
2	Create sales directory	models/sales/	None	No models yet in sales
3	Add sales_summary.sql	models/sales/	sales_summary.sql	dbt detects sales_summary model
4	Add sales_details.sql	models/sales/	sales_summary.sql, sales_details.sql	dbt detects both sales models
5	Create marketing directory	models/marketing/	None	No models yet in marketing
6	Add campaign_performance.sql	models/marketing/	campaign_performance.sql	dbt detects campaign_performance model
7	Add leads.sql	models/marketing/	campaign_performance.sql, leads.sql	dbt detects both marketing models
8	Run dbt build	models/sales/, models/marketing/	All 4 models	dbt builds all models organized by directory

💡 All model files are detected and built by dbt according to their directory organization.

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 6	After Step 7	Final
models folder content	empty	sales/	sales/ with 1 file	sales/ with 1 file, marketing/	sales/ with 1 file, marketing/ with 2 files	sales/ with 2 files, marketing/ with 2 files
dbt detected models	none	sales_summary	sales_summary, sales_details	sales_summary, sales_details	sales_summary, sales_details, campaign_performance, leads	sales_summary, sales_details, campaign_performance, leads

Key Moments - 2 Insights

Why does dbt detect models inside subdirectories automatically?

dbt scans all folders under the main models directory recursively, so any SQL files inside subfolders are included as models, as shown in execution_table steps 3, 4, 6, and 7.

Can I organize models in any folder structure I want?

Yes, as long as the folders are inside the main models directory, dbt will find all SQL files. This helps keep related models grouped logically, as seen in the variable_tracker showing folder contents.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, how many models does dbt detect?

A 2

B 1

C 3

D 0

💡 Hint

Check the 'Model Files Found' column at step 4 in the execution_table.

At which step does the marketing directory first contain model files?

A Step 7

B Step 5

C Step 6

D Step 4

💡 Hint

Look at the 'Directory Structure' and 'Model Files Found' columns for marketing folder in execution_table.

If you add a new folder 'finance' with models inside, what will dbt do?

A Ignore the finance folder

B Detect models inside finance folder automatically

C Only detect models in root models folder

D Require special config to detect finance models

💡 Hint

Refer to key_moments about dbt scanning all subdirectories under models.

Concept Snapshot

Organizing models in directories:
- Place SQL files inside folders under 'models/'
- dbt scans all subfolders recursively
- Keeps project tidy and logical
- No extra config needed
- Models built reflect folder structure

Full Transcript

This visual execution shows how organizing dbt models in directories works. We start with an empty models folder. Then we create subfolders like sales and marketing. We add SQL model files inside these folders. dbt automatically detects all models inside these directories when it runs. The execution table traces each step of adding folders and files, showing how dbt finds models. The variable tracker shows how folder contents and detected models grow over time. Key moments clarify that dbt scans all subdirectories and that any folder under models is included. The quiz tests understanding of when models are detected and how dbt handles new folders. The snapshot summarizes the key points for quick reference.

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