S26.md

Now, we master Idempotent Data Transformations with the industry standard: dbt (data build tool).

For decades, Data Engineers built "ETL" pipelines: Extract the data, Transform it row-by-row in a Python or Java server, and Load it into the database. But with the rise of massively powerful cloud warehouses (Snowflake, BigQuery) and Lakehouses (Delta Lake), we shifted to ELT: Extract, Load the raw data into the warehouse, and do the Transformations directly inside the database using SQL.

But writing a 2,000-line SQL script is a maintenance nightmare. Today, we bring software engineering best practices (version control, modularity, CI/CD) to SQL.

---

1. EASY: The "Spaghetti SQL" Problem (Modularity & CTEs)

Scenario: You inherited a legacy pipeline. The core transformation is a single, 1,500-line SQL script with 12 nested subqueries. The Problem: The business asks you to add a discount_code column to the final output. You stare at the query, terrified. If you change a GROUP BY on line 400, it might break a JOIN on line 1,200. Testing it is impossible. Task: Redesign this transformation so a team of engineers can collaborate on it safely.

Solution: Modular DAGs with dbt and CTEs

We ban nested subqueries completely. We break the massive script into smaller, logical files (models) and chain them together using Common Table Expressions (CTEs) and dbt's ref() function.

The Workflow:

1. Staging Models (The Clean Up): We create a file stg_orders.sql. It does one thing: reads raw JSON orders, casts data types (strings to dates), renames columns, and handles NULL values.

WITH raw_orders AS (
    SELECT * FROM {{ source('raw_data', 'orders') }}
)
SELECT id AS order_id, CAST(created_at AS DATE) AS order_date FROM raw_orders

2. Intermediate Models (The Math): We create int_daily_sales.sql. It references the staging model, not the raw table.

WITH stg_orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
)
SELECT order_date, COUNT(*) as total_orders FROM stg_orders GROUP BY 1

3. The DAG Compilation: When you run dbt run, dbt reads these files, figures out the dependencies (the DAG), and executes them in the correct order inside Snowflake.

Why this is best: You can test stg_orders in isolation. When you need to add discount_code, you add it to the staging model, and every downstream model inherits it cleanly. It makes SQL read like modular Python code.

---

2. MEDIUM: The "Duplicate Row" Disaster (Idempotency in SQL)

Scenario: You have a massive table containing 50 billion log events. The Problem: To build a daily aggregate table, you write an INSERT INTO daily_logs SELECT ... script that runs every midnight via Airflow. At 1:00 AM, the database restarts and the job fails halfway through. You manually restart the job at 2:00 AM. The Disaster: The script blindly runs again. You now have double-counted the revenue for half the users. Your SQL was not idempotent. Task: Design a transformation that can be run 100 times in a row without duplicating a single metric.

Solution: dbt Incremental Materializations

We stop writing manual INSERT and DELETE statements. We let dbt handle the boilerplate DDL/DML, and we only write the pure SELECT logic.

The Architecture: We configure the dbt model to be incremental and define a unique_key.

{{ config(
    materialized='incremental',
    unique_key='log_id'
) }}

WITH new_events AS (
    SELECT * FROM {{ ref('stg_logs') }}
    {% if is_incremental() %}
        -- Only grab data that arrived AFTER the last time this ran
        WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
    {% endif %}
)
SELECT * FROM new_events

The Workflow (Under the Hood): When dbt executes this against a modern warehouse, it doesn't just do an INSERT. It automatically wraps your SELECT statement in a highly optimized MERGE statement.

• The Match: It looks at the unique_key (log_id).
• The Action: If log_id = 123 already exists in the target table, it runs an UPDATE (overwriting the half-finished data from the failed run). If it doesn't exist, it runs an INSERT.

Why this is best: You achieve perfect idempotency without writing 50 lines of complex procedural merge logic. The pipeline heals itself on the next run.

---

3. HARD: The "Historical Snapshot" (Slowly Changing Dimensions)

Scenario: A user signs up for your "Basic" software plan in January. In March, they upgrade to the "Pro" plan. The Problem: The operational database (Postgres) simply runs an UPDATE users SET plan = 'Pro' WHERE id = 1. The "Basic" plan is erased from history. The Disaster: When the Finance team runs a report in April to see "How much revenue did the Basic plan generate in February?", your SQL joins the February invoices to the current user table. Because the user is now "Pro", the revenue is falsely attributed to the Pro plan. You just ruined historical financial reporting. Task: Design a system to track the complete history of dimensional changes over time, without complex scripting.

Solution: Slowly Changing Dimensions (SCD Type 2) & dbt Snapshots

We must implement SCD Type 2. Instead of overwriting a row, we create a new version of the row and track its lifespan using valid_from and valid_to timestamps.

The Architecture: Normally, building an SCD Type 2 pipeline requires complex state management and staging tables. With dbt, we use the snapshot feature.

The Configuration:

{% snapshot users_snapshot %}
{{
    config(
      target_schema='snapshots',
      unique_key='id',
      strategy='timestamp',
      updated_at='last_updated_at',
    )
}}
SELECT * FROM {{ source('raw_data', 'users') }}
{% endsnapshot %}

The Workflow:

1. Run 1 (January): dbt queries the raw users table and saves the row. It adds metadata columns: dbt_valid_from = '2026-01-01' and dbt_valid_to = NULL (meaning it's the current active record).
2. Run 2 (March): The user upgrades to Pro. Their last_updated_at timestamp changes in the source DB.
3. The Snapshot Execution: You run dbt snapshot. dbt detects the change.

• It updates the old row: dbt_valid_to = '2026-03-01'.
• It inserts a new row for the same user ID with plan = 'Pro', dbt_valid_from = '2026-03-01', and dbt_valid_to = NULL.

Production Grade Architecture: Now, the Finance team's SQL query must be updated to respect time:

SELECT sum(i.amount), u.plan
FROM invoices i
JOIN users_snapshot u 
  ON i.user_id = u.id 
  AND i.invoice_date >= u.dbt_valid_from 
  AND (i.invoice_date < u.dbt_valid_to OR u.dbt_valid_to IS NULL)
GROUP BY u.plan

This guarantees mathematical perfection for point-in-time historical reporting, making your analytical warehouse an immutable source of truth.

---