Temporal Data, Star Schema & Slowly Changing Dimensions
You'll learn to
- -Index a time-series table for the range queries it is asked, with a composite (symbol, time) index
- -Split reporting data into a fact table and dimension tables, and run one aggregate query over them
- -Use Slowly Changing Dimension Type 2 so old facts keep the description that was true then
This closing chapter of the module shifts from transactional design, tuned for recording individual events, to analytical design, tuned for adding up and reporting over large volumes of history. They have different trade-offs, and real financial systems need both.
Time-Series Data: A Market Data Feed
A market data feed produces a constant stream of price ticks. A tick is one recorded price for one symbol at one instant; a ticker symbol such as AAPL is the short code for a company's shares. The dominant question is a range: "every tick for AAPL between 9:30 and 10:00". The table is simple. What matters is designing its index around that question. Think of a phone book sorted by surname and then first name: a composite index on (symbol, time) works the same way, so the database jumps to the symbol and then reads a continuous run of times.
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;At production volume one huge table becomes unwieldy. Time-based partitioning, splitting the table behind the scenes by day or month, is the natural next step. It is previewed here and covered in depth in the sharding chapter of the planet-scale module.
Star Schema: Fact and Dimension Tables
A star schema has one central fact table, the measurable events such as trades with an amount and a quantity, surrounded by dimension tables, the descriptive context such as which client, which security and which date. The shape is drawn like a star, with the fact table in the middle. It is built for adding up: "total trade volume by client tier, by quarter", the usual pattern in regulatory reporting. Think of a receipt: the numbers are the facts, and the labels, store, customer and date, are the dimensions.
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;Slowly Changing Dimensions, Type 2: Preserving History
A client's risk tier changes over time. If dim_client is updated in place, every historical trade now appears to have been made under the client's current tier, which is false for a report on a past quarter. SCD Type 2 never updates the dimension row. It inserts a new row with a new surrogate key, closes the old row, and lets each historical fact keep pointing at the version that was true at the time.
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 accurateThe demo uses client 42, who was low risk until the end of March and high risk after. One trade of 1,000 happened in the first quarter, and one of 2,000 in the second. See how each approach reports volume by tier.
Name the distinction: a transactional schema is optimized for correctly recording individual events, and a star schema is optimized for aggregating across many of them. Real systems keep both, with an ETL process (extract, transform, load) that periodically fills the star schema from the transactional one.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design the Market Data Feed, Regulatory Reporting, and the Stratton Oakmont capstone schema in the LLD Lab's Wolf of Wall Street act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!