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

{% 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:

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

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

{% snapshot snap_customers %}

{{
    config(
        target_schema = 'snapshots',
        unique_key = 'customer_id',
        strategy = 'check',
        check_cols = ['country', 'customer_tier', 'is_active']
    )
}}

SELECT
    id::bigint AS customer_id,
    email,
    country,
    customer_tier,
    is_active,
    updated_at
FROM {{ source('shopify', 'customers') }}

{% endsnapshot %}

2. snapshots/schema.yml

version: 2

snapshots:
  - name: snap_customers
    description: "SCD Type 2 snapshot tracking historical customer tier and location changes."
    columns:
      - name: dbt_scd_id
        description: "Surrogate primary key representing a unique customer state version."
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
      - name: dbt_valid_from
        tests:
          - not_null

3. models/marts/core/dim_customers.sql

{{ config(materialized='view') }}

SELECT
    customer_id,
    email,
    country,
    customer_tier,
    is_active,
    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,
    dbt_scd_id AS customer_version_key
FROM {{ ref('snap_customers') }}

4. models/marts/core/fct_orders.sql

{{ config(materialized='table') }}

WITH orders AS (
    SELECT * FROM {{ ref('stg_shopify__orders') }}
),

customers AS (
    SELECT * FROM {{ ref('dim_customers') }}
),

joined AS (
    SELECT
        o.order_id,
        o.order_placed_at,
        o.order_total_usd,
        c.customer_id,
        c.customer_version_key,
        c.country AS country_at_order_time,
        c.customer_tier AS tier_at_order_time
    FROM orders o
    LEFT JOIN 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
)

SELECT * FROM joined

---

📊 100-Point Evaluation Rubric

Evaluation Dimension	Weight	Target Standard (Pass ≥ 80%)
1. Snapshot Block Syntax	25 pts	Correct {% snapshot %} block, valid target_schema, and primary key declaration.
2. Strategy Selection & Optimization	25 pts	Appropriate choice of strategy; high-cardinality volatile columns (last_login_at) intentionally omitted to prevent runaway table bloat.
3. View Dimension Architecture	25 pts	Clean dim_customers view model exposing human-readable effective_from, effective_to, is_current, and surrogate key.
4. Historical Range Join	25 pts	Downstream mart query correctly joins using o.order_placed_at >= c.effective_from AND o.order_placed_at < c.effective_to with zero duplicate rows.

---

📤 Submission Instructions & Guidelines

1. Deliverable Formats: Upload your completed .sql and .yml files in a .zip archive, or paste your GitHub repository link in the Submission Notes.
2. Dialect Compatibility: Ensure ANSI SQL timestamp casting and join logic are compatible with Snowflake, BigQuery, Databricks, or PostgreSQL.
3. Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
4. Upload: Use the drag-and-drop file upload zone below.
5. Confirmation: Click Submit Assignment to complete the milestone.