This lesson's interactive features are locked, please to keep using them

Fixing Grain Violations

You found some duplicate rows. Now what? The fix depends on why the duplicates exist.

The most common sign of a grain violation is inflated numbers. Revenue is higher than expected, counts are too large, or averages look off.

Gut checks are worth the moment they take. Before you run an aggregation, ask yourself: what do I expect? If the result matches your expectation, move on. If it doesn't, dig in – something may be wrong with your grain.

Strategy 1: Drop Exact Duplicates

If rows are completely identical, they're likely data loading errors. Just use drop_duplicates():

orders = pd.DataFrame(
    {
        "order_id": ["ORD-7K2M", "ORD-7K2M", "ORD-9P4X", "ORD-3D8V"],
        "customer_id": ["CUS-4821", "CUS-4821", "CUS-7316", "CUS-2059"],
        "revenue": [50.00, 50.00, 75.00, 100.00],
    }
)


clean = orders.drop_duplicates()
print(clean["revenue"].sum())  # 225, correct

You can also keep the first, last, or no occurrence of each duplicate group (default is "first"):

clean = orders.drop_duplicates(keep="first")
clean = orders.drop_duplicates(keep="last")
clean = orders.drop_duplicates(keep=False)

Only use drop_duplicates() when you're confident the duplicates are truly redundant. If two rows share an order_id but have different values, dropping one silently can hide a real problem.

Strategy 2: Re-Aggregate to a Different Grain

Sometimes duplicates exist because the data is at a finer grain than you need. An "order" can have multiple "line items". So if your rows represent line items, multiple order_ids aren't a problem – you just need to roll up to the order grain before order-level aggregations.

line_items = pd.DataFrame(
    {
        "order_id": ["ORD-7K2M", "ORD-7K2M", "ORD-9P4X"],
        "product_id": ["CHL-482", "TST-731", "CHL-482"],
        "quantity": [2, 1, 3],
        "line_revenue": [1598.00, 129.00, 2397.00],
    }
)

# Roll up to order grain
order_summary = (
    line_items.groupby("order_id")
    .agg(
        total_revenue=("line_revenue", "sum"),
        total_items=("quantity", "sum"),
        product_count=("product_id", "nunique"),
    )
    .reset_index()
)

Now each order_id appears exactly once.

Assignment

SnackStack's daily_device_summary table promises one row per device per day, but an ETL bug started double-writing rows.

Each row has a device_id, a date, an avg_temperature, and a reading_count.

Complete the find_grain_violations function. It accepts the summary DataFrame.
1. Find rows whose device_id/date composite key is duplicated.
2. Flag all members of each duplicated group by passing keep=False.
3. Return every duplicated row so an analyst can see exactly what broke the grain.
Complete the fix_device_day_grain function. It accepts the summary DataFrame (same columns) and restores one row per device_id/date.
1. Only keep the first occurrence of each key.
2. Sort by device_id then date so the output is stable, and return the result.