Module 9
Duplicates and keys
Remove duplicates deliberately, choose the right row to keep and add index columns.
You'll be able to
- Deduplicate on the right key
- Keep the latest record per key
- Add index columns
Lessons
- 1.Remove duplicates on the right keyDeduplicate by business key and control which row survives.
- 2.Add index columnsNumber rows meaningfully by sorting first and choosing the start value.
- 3.Check key quality before you rely on itProve a key is unique and non-null, and list the offending values when it isn't.
- 4.Choose which duplicate to keep with a business ruleImplement a multi-criteria survivor rule that gives the same answer on every refresh.
Take the duplicates and keys quiz (8 questions)
Exercises
- beginner~6 minPro
Keep one signup per email address (newsletter)
Build a mailing list with one row per email address from signups that contain repeats.
- beginner~6 minPro
Keep one signup per email address (webinar)
Build a mailing list with one row per email address from signups that contain repeats.
- advanced~10 minPro
Keep each person's most recent signup (newsletter)
For every email keep only the row with the latest Signed date, whatever the row order.
- advanced~10 minPro
Keep each person's most recent signup (webinar)
For every email keep only the row with the latest Signed date, whatever the row order.
- beginner~5 minPro
Number claims in date order (consultancy)
Sort claims by date and add a Line number starting at 1.
- beginner~5 minPro
Number claims in date order (school)
Sort claims by date and add a Line number starting at 1.
- beginner~6 minPro
Find invoice numbers that appear more than once (accounts payable)
Before loading, list every invoice number that occurs more than once and how many times.
- beginner~6 minPro
Find invoice numbers that appear more than once (school office)
Before loading, list every invoice number that occurs more than once and how many times.
- intermediate~7 minPro
Count each email once, whatever its case or spacing (petition)
The same address appears as Amara@Mail.example and ' amara@mail.example'. Build a clean list of unique emails.
- intermediate~7 minPro
Count each email once, whatever its case or spacing (raffle)
The same address appears as Amara@Mail.example and ' amara@mail.example'. Build a clean list of unique emails.
- advanced~10 minPro
Keep the highest reading per site and day, with its inspector (air quality)
Sites are inspected several times a day. Keep only the row with the highest reading for each site and day.
- advanced~10 minPro
Keep the highest reading per site and day, with its inspector (pools)
Sites are inspected several times a day. Keep only the row with the highest reading for each site and day.
- intermediate~8 minPro
Flag every order from a repeat customer (kite shop)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- intermediate~8 minPro
Flag every order from a repeat customer (bakery)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- intermediate~8 minPro
Flag every order from a repeat customer (bike rental)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- beginner~5 minPro
List the region and product combinations that sold (kite shop)
Produce one row per Region and Product pair that appears in the orders, sorted.
- beginner~5 minPro
List the region and product combinations that sold (bakery)
Produce one row per Region and Product pair that appears in the orders, sorted.
- beginner~5 minPro
List the region and product combinations that sold (bike rental)
Produce one row per Region and Product pair that appears in the orders, sorted.