Practice Exam (sample_qtn): Milk Validation & Collisions

Farmers deliver milk to collection centres. You get data/raw/milk_raw.csv (deliveries) and data/raw/centres.csv (valid centres with capacities), and must write clean_milk(df) -> (clean_df, rejected_df):

• clean_df — deliveries you trust, ready for the star schema;
• rejected_df — every row you didn't keep, with a reason (invalid_date, invalid_litres, unknown_centre, record_id_collision, …).

Two rules frame everything: never modify data/raw/, and every input row must be accounted for. Examiners check both the logic and that you can prove nothing was lost.

Part (a) asks whether validation rules run before or after tidying text. Run them after.

rule_unknown_centre checks each delivery's centre against centres.csv. If a clerk typed " mbarara " or "MBARARA", the raw string doesn't match "Mbarara", so checking first rejects a perfectly valid delivery — and a farmer doesn't get paid.

So the order is: normalise formatting (df["centre"] = df["centre"].str.strip().str.title(), parse dates, coerce numbers) → then apply membership and domain rules. Tidying fixes representation; validation judges content. Mixing them up turns formatting noise into false rejections.

These look similar and are handled oppositely:

Keeping "the first" collision row is a silent guess that can pay the wrong farmer or the wrong amount. Rejecting both sends the conflict to a human. Order matters: remove exact duplicates first, otherwise two identical rows would look like a collision.

exact = df.duplicated(keep="first")
df = df[~exact]
collision = df.duplicated(subset=["record_id"], keep=False)   # marks BOTH rows

The invariant you assert at the end:

rows_in = len(clean_df) + len(rejected_df) + exact_duplicates_removed

It catches the most common bugs: a filter that silently drops rows without recording them, a rule applied twice, or a collision handled by keeping one row. Examiners love asking for it because it turns "I think my cleaning is right" into a check that either passes or fails.

Two more habits that score marks: give every rejected row exactly one reason (the first rule it failed, applied in a fixed order), and log the count per reason so the operations team can see why data was rejected, not just how much.