Data Cleaning Exercises

Module 9

Duplicates

Row grain, composite keys, deterministic survivors, provenance and legitimate repeats.

  1. Find the row grain before removing duplicates

    State what one row means, choose the key that matches it, and keep one row per key with a deterministic rule. · 10 min

  2. Deterministic survivor rules

    Keep exactly one row per key with an ordering that gives the same survivor on every run. · 10 min

  3. Legitimate repeats

    Tell real duplicates from repeated but legitimate events before removing anything. · 6 min

  4. Duplicate reports

    Report duplicates with the survivor's key so reviewers can check the rule. · 6 min

Module 9 quiz

Practice questions: choose an answer and check it to see the explanation. Nothing here is timed or graded.

  1. 1. Two order lines share order_id and line_no but differ in amount. Are they duplicates?
  2. 2. A dedup query uses ROW_NUMBER() OVER (PARTITION BY account ORDER BY updated DESC). What is missing?
  3. 3. Two identical rows appear in a sales file. The business says customers can buy the same item twice in one order. What should you do?
  4. 4. What is row grain?
  5. 5. Lines re-sent with versions 1, 2 and 10 for the same key, version stored as text. Sorting descending as text keeps which?
  6. 6. What should happen to the rows that lose the survivor rule?