Practical Test 02 (dsc3103_test_02): Milk Star Schema & Analytics

dsc3103_test_02 gives you milk_clean.parquet (20,000 deliveries, March–August 2026), centres.csv (5 centres with a daily capacity_litres) and farmers.csv (294 registered farmers). You build dim_date, dim_centre, dim_farmer and fact_delivery, then answer analytics questions in DuckDB.

The traps are in the data, not the SQL syntax:

1. 300 distinct farmer_ids appear in the deliveries but only 294 are registered — 6 farmers, 408 deliveries, have no dimension row.
2. capacity_litres is a limit per day, but the biggest single delivery is 46.8 L — so any row-by-row comparison finds nothing.
3. A farmer can move to another centre mid-season (July), which changes how "top farmers per centre" should be counted.

If dim_farmer only contains farmers.csv:

• an INNER JOIN silently drops the 408 deliveries (2% of the litres disappear from every total);
• a LEFT JOIN keeps them with a NULL farmer_key, breaking referential integrity and making GROUP BYs show a mysterious NULL group.

Kimball's answer is an explicit Unknown member:

CREATE TABLE dim_farmer AS
SELECT 0 AS farmer_key, -1 AS farmer_id, 'Unknown' AS farmer_name
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY farmer_id) AS farmer_key, farmer_id, farmer_name FROM farmers;

CREATE TABLE fact_delivery AS
SELECT d.date_key, c.centre_key, COALESCE(f.farmer_key, 0) AS farmer_key, m.litres, m.litres * m.price_ugx AS revenue_ugx
FROM milk m
LEFT JOIN dim_farmer f USING (farmer_id)
JOIN dim_centre c USING (centre) JOIN dim_date d ON d.date = m.date;

Result: 20,000 fact rows, zero NULL keys, and the 408 deliveries are visible as "Unknown" — which is itself a data-quality finding to report.

"How many days was each centre over capacity?" Capacity is daily, so first compute the daily total per centre, then compare:

WITH daily AS (
  SELECT centre_key, date_key, SUM(litres) AS daily_litres
  FROM fact_delivery GROUP BY centre_key, date_key)
SELECT c.centre_name, COUNT(*) AS days_over_capacity
FROM daily d JOIN dim_centre c USING (centre_key)
WHERE d.daily_litres > c.capacity_litres
GROUP BY c.centre_name ORDER BY days_over_capacity DESC;

Kiruhura (950 L/day) was over on 41 days, Masaka 36, Mbale 32, Gulu 28, Mbarara 23. WHERE litres > capacity_litres on single deliveries returns zero — a correct query for the wrong question.

Top 3 farmers per centre uses a window function — and must exclude the Unknown bucket, which would otherwise win:

SELECT * FROM (
  SELECT c.centre_name, f.farmer_name, SUM(litres) AS total,
         RANK() OVER (PARTITION BY c.centre_name ORDER BY SUM(litres) DESC) AS rnk
  FROM fact_delivery x JOIN dim_farmer f USING (farmer_key) JOIN dim_centre c USING (centre_key)
  WHERE f.farmer_key != 0 GROUP BY 1, 2) WHERE rnk <= 3;

RANK gives ties the same rank (and skips the next); ROW_NUMBER breaks ties arbitrarily.

Partitioning fact_delivery by month writes one Parquet folder per month. A query with WHERE month = '2026-04' reads only that folder (3,201 rows instead of 20,000) — partition pruning. A filter on another column, like farmer_key = 17, still reads all six. Check with EXPLAIN ANALYZE.

A farmer moving centres in July is a slowly changing dimension: with SCD Type 2 you keep one dimension row per period (valid_from, valid_to), so June deliveries count for the old centre and August for the new one.