Temporal Data, Star Schema & Slowly Changing Dimensions

CREATE TABLE market_ticks (
    id BIGINT PRIMARY KEY,
    symbol TEXT NOT NULL,
    price DECIMAL(12, 4) NOT NULL,
    tick_at TIMESTAMP NOT NULL
);
CREATE INDEX idx_symbol_time ON market_ticks (symbol, tick_at);
-- (symbol, tick_at) as a composite index directly serves the dominant query:
SELECT * FROM market_ticks WHERE symbol = 'AAPL' AND tick_at BETWEEN :start AND :end;

CREATE TABLE dim_client (               -- dimension: descriptive context
    id INTEGER PRIMARY KEY,
    client_name TEXT NOT NULL,
    risk_tier TEXT NOT NULL
);
CREATE TABLE dim_date (                 -- dimension: pre-computed date attributes
    id INTEGER PRIMARY KEY,
    full_date DATE NOT NULL,
    year INTEGER NOT NULL,
    quarter INTEGER NOT NULL
);
CREATE TABLE fact_trades (              -- fact: the measurable events
    id INTEGER PRIMARY KEY,
    client_id INTEGER REFERENCES dim_client(id),
    date_id INTEGER REFERENCES dim_date(id),
    trade_amount DECIMAL(15, 2) NOT NULL,
    quantity INTEGER NOT NULL
);
-- Reporting query: total trade volume per risk tier, per quarter - a simple join+aggregate
SELECT c.risk_tier, d.quarter, SUM(f.trade_amount)
FROM fact_trades f
JOIN dim_client c ON f.client_id = c.id
JOIN dim_date d ON f.date_id = d.id
GROUP BY c.risk_tier, d.quarter;

CREATE TABLE dim_client (
    id INTEGER PRIMARY KEY,             -- surrogate key: a new row per version
    client_natural_id INTEGER NOT NULL, -- the real, stable client identity
    risk_tier TEXT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE,                      -- NULL means "current version"
    is_current BOOLEAN NOT NULL DEFAULT TRUE
);
-- when risk_tier changes: close the old row, insert a new one
-- UPDATE dim_client SET valid_to = CURRENT_DATE, is_current = FALSE WHERE client_natural_id = 42 AND is_current;
-- INSERT INTO dim_client (client_natural_id, risk_tier, valid_from, is_current) VALUES (42, 'high', CURRENT_DATE, TRUE);
-- fact_trades.client_id keeps pointing at whichever dim_client.id version was current
-- at the time the trade happened, so historical reports stay accurate