Star Schema Grain & Fan-Trap Simulator
Size a star schema from its grain, then break it on purpose: give a dimension duplicate keys and watch SUM() silently multiply — the fan trap.
Predict first
Commit to a guess before you touch anything. Being wrong here is how it sticks.
5 markets × 4 commodities × 365 days with full coverage — how many fact rows?
Experiment
Move the controls and watch the outputs. Goals tick off automatically when you reach them.
Grain → fact table size
The fan trap
A fact row joins to every dimension row that shares its key.
Fan trap: a non-unique dimension key multiplied the metric by 3× — with no error message.
Explain it
Put what you saw into words. This is what the exam will ask you to do.
What goes in a fact table vs a dimension table, and why must the grain be declared before writing SQL?
Code it
Write real code against real data. Python and SQL run right here in your browser and hidden tests check your work; the AI reviews it like an examiner.
Build the star schema in DuckDB
A table staging holds cleaned observations (date, market, district, region, commodity, category,
price_ugx, rainfall_mm). Build:
dim_market(market_key, market, district, region)— one row per market;dim_commodity(commodity_key, commodity, category)— one row per commodity;fact_market_prices(date, market_key, commodity_key, price_ugx, rainfall_mm)— grain: one row per (date, market, commodity).
Use integer surrogate keys (e.g. ROW_NUMBER() OVER (ORDER BY market)). Finish with a query that
shows a few fact rows.
Challenge
Fixed variations, a new problem every day, and unlimited AI challenges built from your current settings.
Star Schema Fact Table Cardinality
Our Uganda market Star Schema tracks 8 markets, 6 commodities, across 90 days with 100% daily coverage. At the grain of (date, market, commodity), how many rows are in fact_market_prices?