SUBMISSION: Project 4 — Build a Slowly Changing Dimension (SCD Type 2) Customer Snapshot

# SUBMISSION: Project 4 — Build a Slowly Changing Dimension (SCD Type 2) Customer Snapshot > 🎯 **Hands-on Project Deliverable:** Implement Type 2 Slowly Changing Dimensions in dbt. Build automated snapshot tables that preserve history across customer location and subscription tier changes. --- ## 🏢 Business Scenario & Executive Prompt Your marketing team is celebrating: *"US customers had a record-breaking Q4 with 80% revenue share!"* However, upon auditing, you realize that thousands of European customers immigrated or updated their billing address to the US in Q1 of this year. Because the raw e-commerce database table only stores the current address, all historical Q4 orders were retroactively attributed to the US! You are tasked with building a **dbt Snapshot (SCD Type 2)** pipeline that captures daily historical state and writing the dimensional mart query that joins orders to customer dimensions as they existed on the date of purchase. --- ## 📋 Upstream Raw Schema (`raw_shopify.customers`) The raw customer database table is overwritten continuously by Fivetran CDC: - `id` (bigint): Unique customer ID. - `email` (varchar): Customer email. - `country` (varchar): 2-letter country code ('US', 'GB', 'DE', 'IN'). - `customer_tier` (varchar): 'free', 'starter', 'growth', 'enterprise'. - `is_active` (boolean): Account status. - `last_login_at` (timestamp): Updated on every web session. - `updated_at` (timestamp): Last record update timestamp in PostgreSQL. *Key Analytical Requirement:* We need historical tracking on `country`, `customer_tier`, and `is_active`. We do **NOT** want to snapshot on `last_login_at` because tracking daily logins would cause exponential row explosion without analytical value. --- ## 🎯 Required Deliverables Submit a `.zip` archive or GitHub repo link containing: 1. **`snapshots/snap_customers.sql`**: Configured dbt snapshot capturing changes on `country`, `customer_tier`, and `is_active`. 2. **`snapshots/schema.yml`**: Schema tests validating uniqueness of `dbt_scd_id` and non-null constraints on `dbt_valid_from`. 3. **`models/marts/core/dim_customers.sql`**: User-friendly view model exposing `effective_from`, `effective_to`, `is_current`, and surrogate key. 4. **`models/marts/core/fct_orders.sql`**: Mart model demonstrating point-in-time time-range joins (`BETWEEN effective_from AND effective_to`). --- ## 🛠️ Step-by-Step Implementation Guide ### Step 1 — Choose the Snapshot Strategy In dbt, you have two snapshot strategies: - **`timestamp` Strategy:** Compares the source's `updated_at` column. Best when `updated_at` reliably changes on every mutation. - **`check` Strategy:** Compares specific columns using `check_cols=['country', 'customer_tier', 'is_active']`. Best when `updated_at` changes on irrelevant updates (like `last_login_at`). Configure `snap_customers.sql` using either strategy with careful justification. ### Step 2 — Configure the Snapshot Block ```sql {% snapshot snap_customers %} {{ config( target_schema='snapshots', unique_key='customer_id', strategy='check', check_cols=['country', 'customer_tier', 'is_active'] ) }} SELECT id AS customer_id, email, country, customer_tier, is_active, updated_at FROM {{ source('shopify', 'customers') }} {% endsnapshot %} ``` ### Step 3 — Build the `dim_customers` View Layer Do not have analysts query `snap_customers` directly with internal dbt column names (`dbt_valid_from`, `dbt_valid_to`). Instead, create `models/marts/core/dim_customers.sql` to expose clean, readable column aliases: - `dbt_scd_id AS customer_version_key` - `dbt_valid_from AS effective_from` - `COALESCE(dbt_valid_to, '9999-12-31'::timestamp) AS effective_to` - `(dbt_valid_to IS NULL) AS is_current` ### Step 4 — Wire Fact Models with Point-in-Time Joins In `models/marts/core/fct_orders.sql`, join orders to `dim_customers` on customer ID and order timestamp: ```sql SELECT o.order_id, o.order_placed_at, o.order_amount_usd, c.customer_id, c.customer_version_key, c.country AS country_at_order_time, c.customer_tier AS tier_at_order_time FROM {{ ref('stg_shopify__orders') }} o LEFT JOIN {{ ref('dim_customers') }} c ON c.customer_id = o.customer_id AND o.order_placed_at >= c.effective_from AND o.order_placed_at < c.effective_to ``` *Result:* If Customer 42 lived in the UK from Jan–Nov and moved to the US in Dec, orders placed in July are attributed to the UK, while orders in Dec are attributed to the US! ### Step 5 — Validate the Pipeline ```bash dbt snapshot # builds snapshots/snap_customers dbt run --select dim_customers fct_orders dbt test --select snapshots.snap_customers ``` --- ## 💻 Starter Scaffold ### 1. `snapshots/snap_customers.sql` ```sql {% snapshot snap_customers %} {{ config( target_schema = 'snapshots', unique_key = 'customer_id', strategy = 'check', check_cols = ['coun