01. Anatomy of DBT Project
The Anatomy of a DBT Project
A DBT project is fundamentally a collection of SQL files, YAML configurations, and Jinja macros. However, unlike a messy folder of random SQL scripts, a DBT project enforces a structured, software-engineering approach to data transformations.
1. The Standard Directory Structure
When you initialize a new project using dbt init, DBT automatically scaffolds a precise directory tree. Every folder inside has a strict, specific purpose designed to scale with your team.
my_dbt_project/
├── dbt_project.yml # ️ The control center: configures the whole project
├── models/ # ️ The core: where your SQL transformations live
│ ├── staging/ # (Layer 1) Cleaned raw data
│ ├── marts/ # (Layer 2) Business-ready tables
├── seeds/ # Static CSV files (e.g., country codes, mappings)
├── macros/ # Reusable SQL functions written in Jinja
├── snapshots/ # Slowly Changing Dimension (SCD) tracking
├── tests/ # Custom SQL queries for rigorous data validation
└── README.md # Project documentation
2. How These Components Work Together
DBT acts as a compiler. It reads all these different folders, compiles the Jinja and SQL into optimized dialect-specific SQL, and pushes it to your warehouse. Here is a high-level view of how they interact during a standard dbt run:
A Beginner's Walkthrough of the Workflow
If you are brand new to data engineering, the flow above might look complex. Let's break down exactly what happens from start to finish when you use DBT:
️ 1. Writing the Logic (The Inputs)
You—the developer—write your data transformations in.sqlfiles inside themodels/folder. However, this isn't normal SQL; you are allowed to use special placeholders and logic called Jinja (e.g.,{{ ref('my_table') }}). You can also write reusable snippets of code called Macros (like a function) to avoid copy-pasting the same logic everywhere.
2. The Compiler (The Magic)
When you type the commanddbt runin your terminal, the DBT Compiler wakes up. It gathers your Models, grabs any Macros you referenced, and reads the overarching rules in yourdbt_project.ymlfile. It then translates all that special Jinja logic into plain, pure SQL that your database can understand.
️ 3. Execution (The Heavy Lifting)
Once compiled, DBT securely connects to your Data Warehouse (like Snowflake) and pushes those pure SQL commands. Your data never leaves Snowflake. DBT simply acts like an orchestra conductor telling Snowflake exactly how to build the tables.
️ 4. Validation (The Safety Net)
After the tables are built in Snowflake, you can rundbt test. DBT will execute specific SQL queries defined in yourtests/folder to check if your new tables have errors—like accidental duplicates or missing values.
You write modular templates locally ️ DBT compiles them into pure SQL ️ Snowflake executes the code and stores the data.
3. Core Components Deep Dive
Here is a breakdown of exactly what goes where, and the best practices associated with each directory.
| Component | File Types | Purpose & Best Practices |
|---|---|---|
models/ | .sql, .yml | This is where 90% of your work happens. Every .sql file represents a single transformation model (Table/View). Rule of Thumb: One .sql file = One physical object in your DB. |
seeds/ | .csv | For small, static datasets (e.g., regional mappings, zip codes) that don't exist in your upstream sources. Run dbt seed to load them directly into the warehouse as tables. |
macros/ | .sql | If you find yourself writing the exact same complex CASE WHEN logic multiple times, write it once here as a Jinja Macro (like a Python function for SQL). Keeps code incredibly DRY. |
tests/ | .sql | For complex, singular business logic tests. If a SQL query placed here returns any rows, DBT immediately flags the test as a failure. |
snapshots/ | .sql | Automates "Slowly Changing Dimensions Type 2". If a source system overwrites an address, a snapshot will capture and store both the old and new values with dbt_valid_from timestamps. |
4. The Lifecycle of a DBT Model (Execution Flow)
To truly understand how these folders and commands interconnect, let's look at the chronological lifecycle of a single model. How does a simple text file become a materialized table?
- Write (Development):
- You write a
SELECTstatement in a new.sqlfile inside themodels/directory. - You define its Materialization component (e.g., whether it should be built as a
view,table, orincremental) in a YAML configuration file or directly at the top of the SQL script.
- You write a
- Compile (
dbt compile):- DBT reads your file and evaluates all the Jinja syntax (like
{{ ref() }}and macros). - It translates this into raw, warehouse-specific SQL statements (saved locally in your
target/compiled/folder) without executing anything in the database yet.
- DBT reads your file and evaluates all the Jinja syntax (like
- Run (
dbt run):- DBT securely connects to your Data Warehouse.
- It automatically wraps your compiled
SELECTquery inside DDL/DML statements (likeCREATE OR REPLACE TABLE as ...) based on your chosen materialization. - The warehouse executes the statement. The transformed data finally lands in the Data Warehouse.
- Test & Validate (
dbt test):- Once the table has landed safely in the warehouse, DBT fires off validation queries defined in your
tests/folder. - It tests the newly materialized data (e.g., verifying a primary key column is truly unique) to catch errors before downstream dashboards consume it.
- Once the table has landed safely in the warehouse, DBT fires off validation queries defined in your
- Build (
dbt build):- Rather than running and testing separately, you will eventually use the
dbt buildcommand. This master command binds the entire ecosystem together: it seeds data, runs models, tests them immediately, and takes snapshots—all executed in perfect dependency order.
- Rather than running and testing separately, you will eventually use the
DBT forces data teams to treat their data pipelines exactly like software engineers treat application code. By adhering to this strict modular architecture, large teams can scale effortlessly without the codebase collapsing into an unmanageable mess.