Dataset grain and join cardinality: protect the unit of analysis

Last updated: 6 Oct 20265 min read

tutorial

The grain of a dataset states what one row represents. Every join and aggregation must preserve or intentionally change that unit.

State the grain before counting

An operations team asks for the share of receipt submissions reviewed within a day. One row in the submissions table represents one submission; one row in the review-events table represents one event. Joining those tables without a cardinality rule can duplicate submissions that received several reviews. The resulting average is weighted by event count rather than by submission. Write the unit beside the metric definition, then identify the unique key at that unit. Metric denominators] depend on this decision.

Make cardinality executable

If the business question needs only the latest review, reduce events to one row per submission under an explicit ordering rule, then join with validate="one_to_one". If a many-to-one lookup is expected, use validate="many_to_one" and inspect unmatched keys through indicator=True. A failed validation is a useful data-quality signal. Dropping duplicates after the join merely hides which source violated the contract. Treat missing keys deliberately; matching null keys can be surprising in data-frame joins.

Compare totals around every join

Record input rows, unique submission IDs, unmatched rows and output rows. The output should equal the left table when the lookup is one-to-one and the join is left-sided. Test a submission with three review events and one without any event. The first must remain one submission; the second must remain in the denominator if the policy counts all submitted receipts. Missingness] decides how its review time is represented, not whether it silently vanishes.

Implementation

latest_reviews = (review_events.sort_values(["submission_id", "reviewed_at", "event_id"])
    .drop_duplicates("submission_id", keep="last"))
receipt_status = submissions.merge(
    latest_reviews[["submission_id", "reviewed_at"]],
    on="submission_id", how="left", validate="one_to_one", indicator=True)
assert len(receipt_status) == len(submissions)
assert receipt_status["submission_id"].is_unique
unmatched = int(receipt_status["_merge"].eq("left_only").sum())

Performance and operating cost

Sorting E review events costs O(E log E); the join is typically O(S + E) expected time with hash-based keys and O(S + E) memory. Pre-aggregate in storage if the event history is too large for memory.

Common Mistakes

Do not treat a one-to-many join as a harmless enrichment.
Do not repair an inflated count with drop_duplicates after computing metrics.
Do not turn unmatched records into missing denominator rows without a written rule.