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

Milk Star Schema Warehouse & Two-Stage Analytics Simulator

Rebuild the Test 02 warehouse decisions: what an inner join does to unregistered farmers, why capacity needs a two-stage aggregation, and how month partitioning prunes a query.

  1. 1 · Predict0/1
  2. 2 · Experiment0/3
  3. 3 · Explain0/1
  4. 4 · Code it0/3
  5. 5 · Challenge
  6. 0/5 tasks done

Predict first

Commit to a guess before you touch anything. Being wrong here is how it sticks.

6 unregistered farmers made 408 deliveries. How many fact rows survive an INNER JOIN to dim_farmer?

Experiment

Move the controls and watch the outputs. Goals tick off automatically when you reach them.

Unregistered farmers

milk_clean.parquet has 20,000 deliveries; 294 farmers are registered.

fact_delivery rows
19,592
Rows lost
408
NULL farmer_key
0

An inner join silently deletes every delivery from an unregistered farmer — and their litres from every total.

days_over_capacity

capacity_litres is a DAILY limit per centre. Biggest single delivery in the data: 46.8 L.

Capacity
950 L/day
Days over capacity
0
Peak day
1203.5 L

Zero — because no single delivery is anywhere near a whole day's capacity. The query answers the wrong question.

Partition pruning

Partitions scanned
6 of 6
Rows scanned
20,000

Explain it

Put what you saw into words. This is what the exam will ask you to do.

How do you keep unregistered farmers in the warehouse, and why does days_over_capacity need a CTE?

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.

SQL · DuckDBexam

dim_farmer with an Unknown member

Some deliveries in milk come from farmers missing from farmers. Build:

  • dim_farmer(farmer_key, farmer_id, farmer_name) with an Unknown row (farmer_key = 0, farmer_id = 'UNKNOWN', farmer_name = 'Unknown') plus one row per registered farmer (keys 1, 2, … by farmer_id);
  • fact_delivery(delivery_id, date, centre, farmer_key, litres, price_ugx) that keeps every delivery, with no NULL farmer_key.

End with: SELECT farmer_key = 0 AS unknown, COUNT(*) FROM fact_delivery GROUP BY 1 ORDER BY 1;

Data in the working directory: milk_clean.csv farmers.csv centres.csv
Ctrl/⌘ + Enter to run

Challenge

Fixed variations, a new problem every day, and unlimited AI challenges built from your current settings.

Unregistered Farmer Deliveries in `milk_clean.parquet`

In dsc3103_test_02, milk_clean.parquet has 20,000 rows across 300 distinct farmer_ids, while farmers.csv only contains 294 registered farmers. The 6 unregistered farmers (113, 126, 172, 226, 255, 334) have 61, 68, 69, 67, 75, and 68 deliveries respectively. How many rows in fact_delivery map to the Unknown Farmer (farmer_key = 0)?