Skip to main content

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 .sql files inside the models/ 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 command dbt run in your terminal, the DBT Compiler wakes up. It gathers your Models, grabs any Macros you referenced, and reads the overarching rules in your dbt_project.yml file. 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 run dbt test. DBT will execute specific SQL queries defined in your tests/ folder to check if your new tables have errors—like accidental duplicates or missing values.

The Ultimate Summary

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.

ComponentFile TypesPurpose & Best Practices
models/.sql, .ymlThis 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/.csvFor 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/.sqlIf 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/.sqlFor complex, singular business logic tests. If a SQL query placed here returns any rows, DBT immediately flags the test as a failure.
snapshots/.sqlAutomates "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?

  1. Write (Development):
    • You write a SELECT statement in a new .sql file inside the models/ directory.
    • You define its Materialization component (e.g., whether it should be built as a view, table, or incremental) in a YAML configuration file or directly at the top of the SQL script.
  2. 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.
  3. Run (dbt run):
    • DBT securely connects to your Data Warehouse.
    • It automatically wraps your compiled SELECT query inside DDL/DML statements (like CREATE OR REPLACE TABLE as ...) based on your chosen materialization.
    • The warehouse executes the statement. The transformed data finally lands in the Data Warehouse.
  4. 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.
  5. Build (dbt build):
    • Rather than running and testing separately, you will eventually use the dbt build command. 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.

Thinking Like a Software Engineer

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.