Data Warehouse Design

Data Warehouse Design

# Star Schema (most common for analytics)
# Fact table: events/transactions (large, many FKs)
# Dimension tables: lookup data (smaller, descriptive)

-- Fact table (events -- append only)
CREATE TABLE fct_orders (
    order_sk      BIGINT PRIMARY KEY,  -- surrogate key
    order_id      VARCHAR NOT NULL,    -- natural key
    customer_sk   BIGINT NOT NULL,     -- FK to dim_customer
    product_sk    BIGINT NOT NULL,     -- FK to dim_product
    date_sk       INT NOT NULL,        -- FK to dim_date (YYYYMMDD)
    quantity      INT,
    unit_price    DECIMAL(10,2),
    revenue       DECIMAL(10,2),
    created_at    TIMESTAMP
);

-- Dimension table with SCD Type 2 (track history)
CREATE TABLE dim_customer (
    customer_sk   BIGINT PRIMARY KEY,  -- surrogate key
    customer_id   VARCHAR,             -- natural key
    name          VARCHAR,
    email         VARCHAR,
    country       VARCHAR,
    segment       VARCHAR,
    valid_from    TIMESTAMP,
    valid_to      TIMESTAMP DEFAULT '9999-12-31',  -- NULL = current
    is_current    BOOLEAN DEFAULT TRUE
);

-- Date dimension (pre-populated)
CREATE TABLE dim_date (
    date_sk      INT PRIMARY KEY,  -- YYYYMMDD
    date_actual  DATE,
    day_of_week  VARCHAR,
    month_name   VARCHAR,
    quarter      INT,
    year         INT,
    is_weekend   BOOLEAN,
    is_holiday   BOOLEAN
);