01. End to End DBT Project
End to End DBT Project
This hands-on module guides you through building a complete dbt project, from sourcing raw data to creating production-ready models, tests, and documentation.
You will learn best practices for project structure, incremental models, snapshots, and orchestration.
Key Components of the Project
Sources – Raw tables ingested from upstream systems
Staging Models – Standardize and clean raw data
Intermediate Models – Transformations for analytical use
Mart / Production Models – Ready for dashboards or reporting
Snapshots – Track historical changes
Tests – Ensure data quality
Documentation – YAML descriptions and lineage
Orchestration – Automate execution with Airflow or dbt Cloud
1. Project Structure
my_dbt_project/
│
├── models/
│ ├── staging/
│ │ └── stg_customers.sql
│ ├── marts/
│ │ └── fct_orders.sql
│ └── intermediate/
│ └── int_customer_orders.sql
│
├── snapshots/
│ └── customers_snapshot.sql
│
├── tests/
│ └── custom_tests.sql
│
├── macros/
│ └── utility_macros.sql
│
├── dbt_project.yml
└── profiles.yml
- staging → Clean and standardize raw data
- intermediate → Transformations and business logic
- marts → Production-ready models for BI
- snapshots → Historical tracking
2. Sample Incremental Model
{{ config(
materialized='incremental',
unique_key='order_id'
) }}
select *
from {{ source('app', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
- Processes only new or updated rows
- Ensures efficient incremental loads
3. Implementing Snapshots
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['email', 'first_name', 'last_name']
)
}}
select * from {{ source('app', 'customers') }}
{% endsnapshot %}
- Tracks historical changes without recomputing the entire table
4. Testing and Documentation
- YAML descriptions for models, columns, and sources
- dbt tests for uniqueness, not null, and relationships
version: 2
models:
- name: fct_orders
description: "Fact table for customer orders"
columns:
- name: order_id
description: "Primary key for orders"
tests:
- unique
- not_null
5. Orchestration
- Schedule dbt runs using Airflow, Prefect, or dbt Cloud
- Include incremental models, snapshots, and tests in the DAG
Best Practices
Maintain clear folder structure
Use incremental models for large datasets
Implement snapshots for historical tracking
Add tests and documentation for reliability
Orchestrate pipelines for automated, repeatable runs