05 · Data Modeling: Slowly Changing Dimensions, Conformed Dimensions

Dimension: dim_product
Product 789: "Laptop Pro" → Category: "Electronics"

6 months later:
Product 789: "Laptop Pro" → Category: "Computer" (marketing decides to reorg)

Question: When analyzing sales by product category in Q1:
 - Include "Laptop Pro" as "Electronics"?
 - Include "Laptop Pro" as "Computer"?
 - Show both versions?

UPDATE dim_product 
SET category = 'Computer' 
WHERE product_id = 789;

-- Old value "Electronics" is lost forever

-- Initial insert
INSERT INTO dim_product VALUES (789, 'Laptop Pro', 'Electronics');

-- 6 months later: Category changes
UPDATE dim_product 
SET category = 'Computer' 
WHERE product_id = 789;

-- Query on old fact table:
SELECT SUM(sales), category
FROM fact_sales f
JOIN dim_product d ON f.product_key = d.product_id
WHERE f.sale_date IN ('2024-01-15', '2024-07-15')
GROUP BY category;

-- Both sales show category = 'Computer' (inaccurate for Jan sales!)

-- Initial
INSERT INTO dim_product VALUES 
  (1, 789, 'Laptop Pro', 'Electronics', 
   '2024-01-01', NULL, TRUE);
   -- (surrogate_key, business_key, name, category, eff_date, end_date, is_current)

-- 6 months later: Category changes
UPDATE dim_product 
SET end_date = '2024-07-01', is_current = FALSE
WHERE product_id = 789 AND is_current = TRUE;

INSERT INTO dim_product VALUES 
  (2, 789, 'Laptop Pro', 'Computer', 
   '2024-07-01', NULL, TRUE);

-- Now query:
SELECT SUM(f.sales), d.category
FROM fact_sales f
JOIN dim_product d ON f.product_key = d.surrogate_key
WHERE f.sale_date IN ('2024-01-15', '2024-07-15')
GROUP BY d.category, f.sale_date;

-- 2024-01-15: Electronics
-- 2024-07-15: Computer
-- ✅ Historically accurate!

MERGE INTO dim_product t
USING (
  SELECT 
    product_id,          -- Business key
    product_name,
    category,
    current_timestamp() as load_time
  FROM silver.products
) s
ON t.product_id = s.product_id AND t.is_current = TRUE
WHEN MATCHED AND (
  t.category != s.category OR 
  t.product_name != s.product_name
) THEN
  -- Close old record
  UPDATE SET 
    is_current = FALSE,
    end_date = s.load_time
WHEN NOT MATCHED THEN
  -- Insert new record
  INSERT (surrogate_key, product_id, product_name, category, 
          effective_date, end_date, is_current)
  VALUES (
    NEXT_VALUE(seq_product_key),
    s.product_id,
    s.product_name,
    s.category,
    s.load_time,
    NULL,
    TRUE
  );

INSERT INTO dim_product VALUES 
  (789, 'Laptop Pro', 'Electronics', NULL);

-- 6 months later
UPDATE dim_product 
SET 
  previous_category = category,
  category = 'Computer'
WHERE product_id = 789;

-- Result
SELECT * FROM dim_product WHERE product_id = 789;
-- (789, 'Laptop Pro', 'Computer', 'Electronics')

-- Main dimension (current only, Type 1)
CREATE TABLE dim_product (
  product_id INT PRIMARY KEY,
  product_name VARCHAR,
  category VARCHAR
);

-- History table (full audit trail)
CREATE TABLE dim_product_history (
  product_id INT,
  product_name VARCHAR,
  category VARCHAR,
  effective_date DATE,
  end_date DATE,
  change_reason VARCHAR
);

-- Fast queries use main table (Type 1)
SELECT SUM(sales), category
FROM fact_sales f
JOIN dim_product d ON f.product_id = d.product_id
GROUP BY category;

-- Compliance/audit queries use history
SELECT * FROM dim_product_history 
WHERE product_id = 789
ORDER BY effective_date;

CREATE TABLE dim_product (
  surrogate_key INT,
  product_id INT,

  -- Type 2: History tracking
  category VARCHAR,
  effective_date DATE,
  end_date DATE,
  is_current BOOLEAN,

  -- Type 1: Current denormalization
  current_category VARCHAR (updated daily)
);

Sales fact table: 
  dimension: Customer (id, name, segment, country)

Support fact table:
  dimension: Customer (id, name, title, support_tier)

Question: What's the customer's segment?
  Sales dimension says: "Premium"
  Support dimension says: ???

Problem: Inconsistent customer definition across warehouse!

graph TB
    FS["fact_sales"]
    FSup["fact_support_tickets"]
    FC["gold.dim_customer<br/>(SINGLE<br/>CONFORMED<br/>DIMENSION)"]

    FS -->|customer_key| FC
    FSup -->|customer_key| FC

-- Single source of truth for customers
CREATE TABLE gold.dim_customer (
  customer_key INT PRIMARY KEY,
  customer_id INT UNIQUE,

  -- Attributes from Sales process
  segment VARCHAR,         -- From sales CRM
  country VARCHAR,         -- From sales CRM

  -- Attributes from Support process
  support_tier VARCHAR,    -- From support system

  -- Attributes from Marketing
  email_segment VARCHAR,   -- From email marketing

  -- Tracking
  effective_date DATE,
  end_date DATE,
  is_current BOOLEAN
);

-- All fact tables use this dimension
CREATE TABLE gold.fact_sales (
  customer_key INT REFERENCES gold.dim_customer,
  product_key INT,
  date_key INT,
  amount DECIMAL
);

CREATE TABLE gold.fact_support_tickets (
  customer_key INT REFERENCES gold.dim_customer,
  support_agent_key INT,
  date_key INT,
  resolution_time_minutes INT
);

-- Start from Silver (cleaned single-source data)
CREATE TABLE gold.dim_customer AS

-- Main source: Sales
SELECT 
  ROW_NUMBER() OVER (ORDER BY customer_id) as customer_key,
  sc.customer_id,
  sc.name,
  sc.segment,
  sc.country,

  -- Join with Support data
  sup.support_tier,

  -- Join with Marketing data
  mk.email_segment,

  current_date() as effective_date,
  NULL as end_date,
  TRUE as is_current

FROM silver.customers sc
LEFT JOIN silver.support_tiers sup 
  ON sc.customer_id = sup.customer_id
LEFT JOIN silver.marketing_segments mk 
  ON sc.customer_id = mk.customer_id;

-- Conformed, historicized customer dimension
MERGE INTO gold.dim_customer t
USING (
  SELECT 
    sc.customer_id,
    sc.name,
    sc.segment,      -- Updates tracked via SCD Type 2
    sc.country,
    sup.support_tier,
    mk.email_segment,
    current_timestamp() as load_time
  FROM silver.customers sc
  LEFT JOIN silver.support_tiers sup ON sc.customer_id = sup.customer_id
  LEFT JOIN silver.marketing_segments mk ON sc.customer_id = mk.customer_id
) s
ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHEN MATCHED AND (
  t.segment != s.segment OR 
  t.support_tier != s.support_tier OR
  t.email_segment != s.email_segment
) THEN UPDATE SET 
  is_current = FALSE,
  end_date = s.load_time
WHEN NOT MATCHED THEN INSERT *;