The dbt Project Structure — A Tour

This lesson on The dbt Project Structure — A Tour is hands-on and example-driven. You will be able to navigate, structure, and configure every core file and directory within a dbt project. You will understand where transformation models, static seed datasets, Jinja macros, data quality tests, and snapshots belong. You will also configure the central dbt_project.yaml file to connect your project to warehouse profiles and set execution defaults.

What You'll Be Able To Do

• Configure project definitions, connection profiles, and default materializations in dbt_project.yaml.
• Organize transformation logic, singular test assertions, and administrative queries into their correct directories.
• Ingest static lookup data into warehouse tables from CSV files using dbt seed.
• Abstract repeated SQL patterns and business calculations into reusable Jinja macros.
• Configure snapshot files to implement Slowly Changing Dimensions (Type 2) for mutating source data.

Detailed Concept Walkthrough

1. Central Project Configuration (dbt_project.yaml)

The dbt_project.yaml file is the required control center that identifies a directory as a dbt project and orchestrates global execution rules.

• Mechanism: Defines the project name, links the project to a specific database profile in profiles.yaml, and declares path locations for all subdirectories.
• Execution Flow: When any dbt CLI command runs, dbt parses this file first to resolve project-level configurations, materialization defaults (e.g., view vs. table), and global variables.
• Syntax Rule: The profile: key inside dbt_project.yaml must precisely match the profile identifier defined in your environment's profiles.yaml or the connection will fail immediately.

# dbt_project.yaml
name: 'analytics_core'
version: '1.0.0'
profile: 'snowflake_prod' # Must match profiles.yaml profile name

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]

models:
  analytics_core:
    staging:
      +materialized: view
    marts:
      +materialized: table

Key Takeaway: Without a valid dbt_project.yaml pointing to an existing profile, no dbt commands can execute.

2. Core Transformations and Analyses (models/ and analyses/)

The models/ directory contains production DAG transformation logic, while analyses/ houses ad-hoc and administrative queries that dbt compiles without persisting into the database.

• Mechanism: SQL files in models/ represent database relations (tables, views, incremental datasets) that build DAG dependencies via the {{ ref() }} function.
• Under the Hood: SQL files in analyses/ are parsed and compiled into raw SQL inside the target/compiled/ directory during dbt compile, but dbt creates no relations in the warehouse.
• Best Practice: Place one-off audits, exploratory queries, and administrative data quality scripts in analyses/ to keep production model schemas clean.

-- analyses/audit_missing_customer_ids.sql
-- Compiled to target/compiled/ but NOT built as a view or table
select
    order_id,
    customer_id,
    ordered_at
from {{ ref('stg_orders') }}
where customer_id is null

Key Takeaway: Use models/ for persistent transformation DAGs and analyses/ for compiled, non-persisted SQL scripts.

3. Reusable Logic and Ingestion (macros/ and seeds/)

Macros provide DRY programming logic via Jinja templating, while seeds load small, version-controlled static datasets directly from CSVs.

• Mechanism: Macros act as custom functions or parameterized SQL snippets that can be called across models to enforce consistent logic like date math or currency conversions.
• Ingestion Mechanics: The seeds/ directory holds flat CSV files that dbt seed converts into database tables, mapping CSV headers directly to table columns.
• Best Practice: Treat seeds only as lookup tables (e.g., country codes, tax brackets) rather than a replacement for high-volume ETL ingestion pipelines.

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, scale=2) %}
    round( cast( ( {{ column_name }} / 100.0 ) as numeric ), {{ scale }} )
{% endmacro %}

-- models/staging/stg_payments.sql
select
    payment_id,
    {{ cents_to_dollars('amount_cents') }} as amount_usd
from {{ source('raw_payments', 'charges') }}

Key Takeaway: Macros centralize repeatable calculation logic, while seeds provide a lightweight method to load static reference data.

4. State Tracking and Assertions (snapshots/ and tests/)

Snapshots preserve point-in-time historical records of mutable source data, while singular tests validate data integrity through custom SQL assertions.

• Mechanism: Snapshots track Slowly Changing Dimensions Type 2 (SCD2) by monitoring a source table and recording timestamp intervals (valid_from / valid_to) for mutating rows.
• Assertion Behavior: Singular tests in tests/ are written as SQL queries where returning zero rows denotes a pass, and any returned rows denote an assertion failure.
• Under the Hood: During dbt snapshot, dbt merges updated source records into existing snapshot tables, appending new versions of modified records rather than overwriting.

-- tests/assert_total_payment_is_positive.sql
-- Fails if any records are returned
select
    order_id,
    sum(amount_usd) as total_amount
from {{ ref('fct_orders') }}
group by order_id
having total_amount < 0

Key Takeaway: Snapshots capture historical changes over mutable data, and singular tests flag data quality errors whenever queries return rows.

Topics Covered in The dbt Project Structure — A Tour

1. Analysis Directory (0:38 - 1:49) — Covers running administrative and ad-hoc SQL scripts that compile without generating warehouse relations.
2. Project Configuration File (1:49 - 3:35) — Explains how dbt_project.yaml defines project settings, links database profiles, and sets default materializations.
3. Macros and Jinja Logic (3:35 - 5:40) — Demonstrates writing reusable Jinja functions to eliminate duplicated transformation code across models.
4. Project README (5:51 - 6:44) — Outlines standard documentation practices for onboarding contributors and detailing project context.
5. Static Seed Data (6:44 - 8:33) — Details uploading version-controlled CSV lookup tables to the warehouse using the dbt seed command.
6. Snapshots for SCD2 (8:33 - 10:27) — Describes preserving historical records of mutating source tables using automated snapshot configurations.
7. Singular Tests (10:30 - 12:30) — Reviews custom SQL assertions where any returned rows indicate a test failure.
8. Models Directory (12:30 - 12:43) — Introduces the primary directory where SQL and Python transformation models reside.

dbt Cheat Sheet

• dbt_project.yml — Defines project metadata, paths, and default model materializations

  name: 'my_project'
  profile: 'default'
  model-paths: ["models"]
  

• dbt run — Executes SQL transformations defined in the models directory

  dbt run --select stg_customers
  

• dbt seed — Loads CSV files from seeds directory into warehouse tables

  dbt seed --select country_codes
  

• dbt snapshot — Executes snapshot jobs to record SCD2 table changes

  dbt snapshot --select orders_snapshot
  

• dbt test — Runs singular and generic assertions against project models

  dbt test --select assert_positive_revenue
  

• {% macro %} — Defines reusable Jinja-SQL transformation logic in macros directory

  {% macro get_tax(col) %} ({{ col }} * 0.08) {% endmacro %}
  

Comparison Table

Common Pitfalls

• Mistake: Loading large transactional datasets through the seeds folder. Avoid: Reserve seeds strictly for small static lookup tables and use ETL tools for transactional data.
• Mistake: Profile name in dbt_project.yaml mismatching the profiles.yaml file entry. Avoid: Verify the profile string in dbt_project.yaml matches the target connection block in profiles.yaml.
• Mistake: Writing singular tests that return passing records instead of failing records. Avoid: Structure singular test queries to return only the rows that violate data quality rules.
• Mistake: Repeating custom transformation logic across models instead of using macros. Avoid: Encapsulate shared SQL and Jinja code inside macro files within the macros directory.

FAQs

• Why does dbt compile analysis SQL files without building tables or views? Analyses are designed for ad-hoc queries, administrative tasks, and auditing. Compiling them interpolates Jinja references without cluttering your database schema.
• What makes singular tests in the tests/ directory different from generic tests? Singular tests are standalone .sql files containing bespoke queries that fail on any returned rows, whereas generic tests are reusable parameter-driven YAML definitions.
• Why are seeds considered an anti-pattern when overused? Seeds are committed to git repositories, making them inefficient for fast-changing, high-volume data that should instead enter via production ingestion pipelines.