dbt project structure - Step-by-Step Execution

Concept Flow - dbt project structure

Start: Create dbt project

↓

Folders and files created

↓

models/ folder

↓

macros/ folder

↓

tests/ folder

↓

dbt_project.yml file

↓

profiles.yml file (outside project)

↓

Run dbt commands

↓

Build models, run tests, generate docs

↓

End

Shows the flow from creating a dbt project to its folder structure and running commands.

Execution Sample

dbt

dbt init my_project
cd my_project
ls -R

Creates a new dbt project and lists its folder structure.

Execution Table

Step	Action	Result	Output Example
1	Run 'dbt init my_project'	Creates project folder with default structure	my_project/ ├── models/ ├── macros/ ├── tests/ ├── dbt_project.yml
2	Change directory to project	Inside my_project folder	Current folder: my_project
3	List files recursively	Shows folders and files inside project	models/ example_model.sql macros/ tests/ dbt_project.yml
4	Edit dbt_project.yml	Configure project settings	name: my_project version: 1.0 ...
5	Create profiles.yml (outside project)	Set database connection info	~/.dbt/profiles.yml with target configs
6	Run 'dbt run'	Builds models in database	Models compiled and run successfully
7	Run 'dbt test'	Runs tests on models	All tests passed
8	Run 'dbt docs generate'	Generates documentation site	Docs generated in target/ directory
9	Run 'dbt docs serve'	Serves docs locally	Open browser at localhost:8080
10	End	Project structure used for dbt workflow	Project ready for development

💡 All steps complete, dbt project structure established and used

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 6	Final
Project Folder	None	my_project created	my_project with folders	my_project with compiled models	my_project ready for use
dbt_project.yml	None	Created default file	Configured settings	Used for model build	Configured and used
profiles.yml	None	Not created	Not created	Created outside project	Set with DB connection
Models	None	Example model present	Example model present	Models built in DB	Models built and tested

Key Moments - 3 Insights

Why is profiles.yml outside the dbt project folder?

profiles.yml holds sensitive database connection info and is shared across projects, so it is stored in the user's home directory, not inside the project folder (see execution_table step 5).

What is the purpose of the models/ folder?

The models/ folder contains SQL files that define the data transformations dbt will run. These files are compiled and executed during 'dbt run' (see execution_table step 6).

Why do we run 'dbt docs generate' and 'dbt docs serve'?

'dbt docs generate' creates documentation files from the project, and 'dbt docs serve' starts a local web server to view them. This helps understand the project structure and lineage (see execution_table steps 8 and 9).

Visual Quiz - 3 Questions

Look at the execution table, what folder is created immediately after running 'dbt init my_project'?

A models/

B tests/

C All of the above

D macros/

💡 Hint

Check step 1 output in the execution_table showing the folder structure created.

At which step is the database connection configured?

A Step 4

B Step 5

C Step 6

D Step 3

💡 Hint

Look at the action describing profiles.yml creation in the execution_table.

If you skip running 'dbt test', what would you miss according to the execution flow?

A Checking data quality with tests

B Building models in the database

C Generating documentation

D Creating the project folder

💡 Hint

Refer to step 7 in the execution_table about running tests.

Concept Snapshot

dbt project structure:
- 'dbt init <project>' creates folders: models/, macros/, tests/, and dbt_project.yml
- profiles.yml stores DB connection info outside project
- models/ holds SQL files for transformations
- Run 'dbt run' to build models
- Run 'dbt test' to check data quality
- Run 'dbt docs generate' and 'dbt docs serve' for docs

Full Transcript

This visual execution shows how a dbt project is created and structured. First, running 'dbt init my_project' creates the project folder with key subfolders like models/, macros/, and tests/, plus the dbt_project.yml file. The profiles.yml file, which contains database connection details, is stored outside the project folder in the user's home directory. The models/ folder contains SQL files that define data transformations. Running 'dbt run' compiles and runs these models in the database. Running 'dbt test' executes tests to ensure data quality. Documentation is generated and served locally using 'dbt docs generate' and 'dbt docs serve'. This flow helps beginners understand how dbt organizes projects and runs workflows step-by-step.

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