Skip to main content

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