S045 · Data Modelling — Dimensional, Data Vault, OBT

-- star_schema.sql — build the minimum viable Kimball warehouse.
DROP SCHEMA IF EXISTS mart CASCADE;
CREATE SCHEMA mart;
 
-- ============================================================
-- Dimension: dim_date (pre-populated calendar table)
-- ============================================================
CREATE TABLE mart.dim_date (
  date_key      INT PRIMARY KEY,
  full_date     DATE NOT NULL UNIQUE,
  year          INT NOT NULL,
  quarter       INT NOT NULL,
  month         INT NOT NULL,
  day           INT NOT NULL,
  day_of_week   TEXT NOT NULL,
  is_weekend    BOOLEAN NOT NULL
);
INSERT INTO mart.dim_date
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT AS date_key,
  d::DATE                     AS full_date,
  EXTRACT(YEAR    FROM d)::INT AS year,
  EXTRACT(QUARTER FROM d)::INT AS quarter,
  EXTRACT(MONTH   FROM d)::INT AS month,
  EXTRACT(DAY     FROM d)::INT AS day,
  TO_CHAR(d, 'Day')            AS day_of_week,
  EXTRACT(DOW FROM d) IN (0,6) AS is_weekend
FROM generate_series('2024-01-01'::DATE, '2026-12-31'::DATE, '1 day') d;
 
-- ============================================================
-- Dimension: dim_customer (SCD Type 2)
-- ============================================================
CREATE TABLE mart.dim_customer (
  customer_key       BIGSERIAL PRIMARY KEY,           -- surrogate
  customer_id        TEXT NOT NULL,                    -- business key
  name               TEXT NOT NULL,
  email              TEXT,
  city               TEXT,
  segment            TEXT,
  effective_from     TIMESTAMPTZ NOT NULL,
  effective_to       TIMESTAMPTZ NOT NULL DEFAULT '9999-12-31',
  is_current         BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE (customer_id, effective_from)
);
 
-- Insert initial row
INSERT INTO mart.dim_customer (customer_id, name, email, city, segment, effective_from)
VALUES ('C001', 'Alice', 'alice@ex.com', 'Hyderabad', 'gold', '2024-01-01');
 
-- Simulate Alice moving to Bangalore on 2024-06-01 — SCD Type 2 update
BEGIN;
  UPDATE mart.dim_customer
     SET effective_to = '2024-06-01', is_current = FALSE
   WHERE customer_id = 'C001' AND is_current;
  INSERT INTO mart.dim_customer (customer_id, name, email, city, segment, effective_from)
  VALUES ('C001', 'Alice', 'alice@ex.com', 'Bangalore', 'gold', '2024-06-01');
COMMIT;
 
-- ============================================================
-- Dimension: dim_product (SCD Type 1)
-- ============================================================
CREATE TABLE mart.dim_product (
  product_key BIGSERIAL PRIMARY KEY,
  sku         TEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  category    TEXT NOT NULL,
  color       TEXT
);
INSERT INTO mart.dim_product (sku, name, category, color) VALUES
  ('SKU-1','LED Bulb','electronics','warm'),
  ('SKU-2','USB Cable','electronics','black');
 
-- ============================================================
-- Fact: fact_sales — grain = one row per order line
-- ============================================================
CREATE TABLE mart.fact_sales (
  sale_id        BIGSERIAL PRIMARY KEY,
  sale_date_key  INT  NOT NULL REFERENCES mart.dim_date(date_key),
  customer_key   BIGINT NOT NULL REFERENCES mart.dim_customer(customer_key),
  product_key    BIGINT NOT NULL REFERENCES mart.dim_product(product_key),
  qty            INT NOT NULL,
  amount         NUMERIC(10,2) NOT NULL,
  discount       NUMERIC(10,2) NOT NULL DEFAULT 0
);
 
-- Insert a sale on 2024-03-15 (Alice was in Hyderabad) and 2024-08-10 (Alice was in Bangalore)
INSERT INTO mart.fact_sales (sale_date_key, customer_key, product_key, qty, amount)
SELECT 20240315,
  (SELECT customer_key FROM mart.dim_customer WHERE customer_id='C001' AND '2024-03-15'::DATE BETWEEN effective_from AND effective_to),
  (SELECT product_key FROM mart.dim_product WHERE sku='SKU-1'),
  3, 15.00;
 
INSERT INTO mart.fact_sales (sale_date_key, customer_key, product_key, qty, amount)
SELECT 20240810,
  (SELECT customer_key FROM mart.dim_customer WHERE customer_id='C001' AND '2024-08-10'::DATE BETWEEN effective_from AND effective_to),
  (SELECT product_key FROM mart.dim_product WHERE sku='SKU-2'),
  2, 6.00;
 
-- ============================================================
-- The query that proves SCD Type 2 works
-- ============================================================
SELECT d.full_date, c.city AS city_at_time_of_sale, p.name, f.qty, f.amount
FROM mart.fact_sales f
JOIN mart.dim_date     d ON d.date_key    = f.sale_date_key
JOIN mart.dim_customer c ON c.customer_key = f.customer_key
JOIN mart.dim_product  p ON p.product_key = f.product_key
ORDER BY d.full_date;
-- Alice's March sale shows Hyderabad; August sale shows Bangalore.