Analytical Star Schemas & DuckDB

Databases are built for one of two jobs.

DuckDB is "SQLite for analytics": it runs inside your Python process with no server, but uses a columnar, vectorised engine that processes values in CPU-cache-sized batches. On a laptop it can scan Parquet files faster than many clusters on data under ~50 GB.

A star schema puts one fact table in the middle and dimension tables around it.

• The fact table stores measurements: numbers you sum, average or count (price_ugx, rainfall_mm, litres), plus foreign keys to the dimensions. It is long and narrow.
• Dimension tables store descriptions used to filter and group: dim_market (market, district, region), dim_commodity (name, category, unit), dim_date (day, month, quarter, year, is_weekend). They are short and wide.

Before writing SQL you declare the grain: what exactly one fact row represents. Here: one commodity in one market on one day. Every measure in the fact table must be true at that grain. That's what lets you compute fact-table size directly: |fact| ≤ |dates| × |markets| × |commodities|.

Dimensions get a surrogate key — a meaningless integer like market_key = 7 — rather than joining facts on market_name = 'Owino'.

Why:

• Integers are smaller and faster to join and index than strings.
• Names change ("Owino" → "St. Balikuddembe Market"); with a surrogate key you update one dimension row, not millions of fact rows.
• You can represent things the source doesn't have, like an Unknown member (key = 0) for facts whose dimension value is missing — so no fact row has a NULL key.
• History can be tracked (SCD Type 2): one market can have several dimension rows with different validity dates.

A fan trap happens when a join matches one fact row to several dimension rows. The fact row is repeated once per match, so SUM(price_ugx) is multiplied — with no error message.

Typical causes:

• the dimension has duplicate keys (the same market_key twice, e.g. loaded twice);
• you join to a table at a finer grain (e.g. daily facts joined to a table with one row per market per vendor);
• you mix grains in the fact table (daily rows next to monthly totals).

Defences: enforce unique keys on dimensions, declare the grain and stick to it, and check row counts before and after joins — a join to a dimension must never increase the number of fact rows.