Twelve Store Exports and the 18% Weekly Drop
You are preparing the quarterly sales dataset for a 12-store retail chain. Eleven stores upload UTF-8 comma-separated CSVs with ISO dates (YYYY-MM-DD). The twelfth store uploads a semicolon-delimited file with dates like 03/04/2025, thousands separators in the amount column (for example, "1,240.50"), and a trailing currency code. Across all files, 3.4% of rows have a blank transaction_id, 1.1% are exact duplicates after trimming whitespace, and 0.7% show negative quantity but positive amount. Files range from 8,000 to 45,000 rows, about 310,000 rows total. The CFO wants store-level revenue by week and has already noticed one store's weekly total is 18% lower than last quarter. You have one afternoon and cannot call every store. Your manager asks whether to drop problematic rows or repair them, and how to avoid silently changing the revenue story.
1. Put the data-quality issues in priority order for producing weekly store revenue and justify the order. For each issue, say whether you would repair, flag, or drop rows.
Read the worked answer
Priority should follow impact on the revenue measure and the risk of irreversible distortion. (1) Date and amount parsing first: a semicolon file with DD/MM/YYYY and comma thousands separators can turn 03/04/2025 into March 4 or April 3, shifting an entire store-week. Parse with explicit format detection, strip currency, remove thousands separators, log every failed parse, and repair where a deterministic rule exists. (2) Duplicates next: exact duplicates after whitespace trim are likely export artifacts, but dropping them changes counts. Deduplicate only exact duplicates, retain an audit count by store/week, and flag near-duplicates for review. (3) Blank transaction_id: an ID is not required to compute revenue, so dropping 3.4% of rows would bias store totals. Keep rows with valid amount and date, assign a synthetic key for lineage, and flag the blank rate. (4) Negative quantity with positive amount: flag as a possible return or data-entry error; do not silently drop because it may represent a credit. The order protects the metric before cosmetic cleanup.
2. How would you handle blank transaction_id and exact duplicates, and what evidence would you show that your handling did not explain the 18% drop?
Read the worked answer
For exact duplicates, I would compare full-row hashes after trimming and normalizing case. If 1.1% are truly identical, I would drop one copy per duplicate group but keep a reconciliation table of rows and revenue removed by store/week. For blank transaction_id, I would not treat a missing ID as a missing sale: compute revenue from amount and quantity, create a surrogate key, and report the blank-ID rate separately. To test the 18% drop, I would produce a before/after cleaning revenue bridge for the affected store: raw total, minus documented duplicates, plus or minus parsed-date reassignments, minus flagged invalid rows. I would also run a sensitivity check: if all blank-ID rows were dropped, how much would that store's weekly revenue change? If the drop persists across both cleaning variants, it is likely real; if it appears only in one variant, the cleaning rule is the suspect. I would show the reconciliation to the CFO rather than only the final number.
3. Design three validation checks that must pass before the weekly revenue table is sent to the CFO, with expected outputs.
Read the worked answer
Check 1: row-count reconciliation by store and source file. Expected output: for each store, parsed row count plus documented exclusions equals source row count, with a variance of 0; any nonzero variance is listed. Check 2: date coverage and uniqueness. Expected output: min and max transaction dates cover the quarter, no store-week has dates outside the quarter, and duplicate rate after deduplication is 0%. Check 3: revenue roll-up. Expected output: sum of weekly revenue equals raw parsed revenue minus documented duplicate and invalid amounts, with a bridge that reconciles to the cent. Also compare the affected store's week-over-week change before and after cleaning and flag if the direction of the 18% drop changes. These checks catch the two failure modes that matter: silent row loss and misassigned dates.