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

---

3. Production Anti-Patterns

Anti-Pattern	Operational Failure	Production Standard
Hardcoding table names (FROM raw.db.orders)	Breaks DAG lineage & multi-environment compilation	Always use {{ source('raw', 'orders') }} or {{ ref('stg_orders') }}
Putting heavy aggregations in stg_ models	Fragile pipeline, duplicated business logic	Keep staging 1:1, move business transformations to int_
Omitting primary key tests (unique, not_null)	Silent data duplication in downstream BI reports	Enforce strict schema tests on all primary keys in schema.yml
Unbounded is_incremental() without lookback	Missed updates from late-arriving CDC records	Add a deterministic lookback period (e.g. MAX(updated_at) - INTERVAL '3 DAYS')
Non-DRY SQL snippets duplicated across models	High maintenance burden and logic drift	Encapsulate reusable SQL logic inside dbt Macros

---

4. Production Code Blueprints

4.1 Schema Definition & Automated Quality Gates (schema.yml)

version: 2

sources:
  - name: ecom_raw
    database: raw_platform
    schema: mongodb
    tables:
      - name: orders
        loaded_at_field: _updated_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

models:
  - name: fct_orders
    description: "Incremental fact table capturing processed ecommerce customer orders."
    columns:
      - name: order_id
        description: "Surrogate primary key for orders."
        tests:
          - unique
          - not_null
      - name: customer_id
        description: "Foreign key referencing dim_customers."
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: total_amount_usd
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0

4.2 Generic Custom Macro for Deduplication (macros/deduplicate_by_window.sql)

{% macro deduplicate_by_window(relation, primary_key, order_by_clause) %}
    SELECT
        *
    EXCEPT (_row_num)
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY {{ primary_key }}
                ORDER BY {{ order_by_clause }}
            ) AS _row_num
        FROM {{ relation }}
    )
    WHERE _row_num = 1
{% endmacro %}

4.3 Production Incremental Fact Model (models/marts/fct_orders.sql)

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    partition_by={
      "field": "created_at",
      "data_type": "timestamp",
      "granularity": "day"
    } if target.type == 'bigquery' else none
) }}

WITH stg_orders AS (
    SELECT * FROM {{ ref('stg_ecommerce__orders') }}
    {% if is_incremental() %}
        WHERE updated_at >= (SELECT COALESCE(MAX(updated_at), '1900-01-01') FROM {{ this }})
    {% endif %}
),

stg_payments AS (
    SELECT * FROM {{ ref('stg_ecommerce__payments') }}
),

deduped_orders AS (
    {{ deduplicate_by_window('stg_orders', 'order_id', 'updated_at DESC') }}
),

final AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_status,
        COALESCE(p.payment_amount_usd, 0) AS total_amount_usd,
        o.created_at,
        o.updated_at
    FROM deduped_orders o
    LEFT JOIN stg_payments p ON o.order_id = p.order_id
)

SELECT * FROM final