dbt-data-build-tool-transformations

dbt (Data Build Tool) Transformations

Architectural reference guide for building, testing, and maintaining analytics engineering pipelines using dbt with modern data warehouses (Snowflake, BigQuery, Databricks, PostgreSQL).

---

1. Data Model Layers & DAG Architecture

[ Raw Sources ] 
      │
      ▼
┌─────────────────────────────────────────┐
│ Staging (stg_source_entity)             │ ── Type casting, renaming, 1:1 with source
└─────────────────────────────────────────┘
      │
      ▼
┌─────────────────────────────────────────┐
│ Intermediate (int_domain_joined)        │ ── Business logic, aggregations, joins
└─────────────────────────────────────────┘
      │
      ▼
┌─────────────────────────────────────────┐
│ Marts (fct_orders, dim_customers)       │ ── Dimensional facts/dims or OBT for BI
└─────────────────────────────────────────┘

1.1 Layer Responsibilities

• Staging (stg_): Clean, standardize data types, rename fields to snake_case, light data filtering. Strictly 1-to-1 mapping with raw source tables. Materialized as view or ephemeral.
• Intermediate (int_): Structural transformation layer where entities are joined, complex business logic is applied, and domain prep is performed. Materialized as ephemeral or view (or table if complex).
• Marts (fct_, dim_, obt_): Business-facing consumption models. Star schema (Fact/Dimension tables) or One Big Table (OBT) optimized for analytical engines. Materialized as table or incremental.

---

2. Idempotency & Incremental Materializations

Idempotency in dbt ensures that re-executing dbt run for a historical partition window updates existing records without duplicating primary keys or losing state.

2.1 Incremental Merging Strategy

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='sync_all_columns',
    cluster_by=['order_date']
) }}

WITH source_data AS (
    SELECT * 
    FROM {{ ref('stg_ecommerce__orders') }}
    {% if is_incremental() %}
        -- Lookback window (e.g. 3 days) to handle late-arriving events safely
        WHERE updated_at >= (SELECT DATEADD('day', -3, MAX(updated_at)) FROM {{ this }})
    {% endif %}
)

SELECT
    order_id,
    customer_id,
    order_status,
    total_amount_usd,
    created_at,
    updated_at
FROM source_data