Messy Real World Data
Handle time zones, dirty join keys, shifting definitions and late data like a seasoned analyst
A taste of a lesson
Joining CRM accounts to billing gives only a 71 percent match rate. Both use account IDs. What's going on?
Start by looking at unmatched rows from both sides. Common culprits: one system stores IDs as text with leading zeros, the other as numbers that dropped them; trailing spaces; different case; or test and internal accounts in one system only. Try normalising: trim, uppercase, pad to a fixed length, cast both to text. Then recheck the match rate. If it is still low, ask the system owners whether some accounts were migrated with new IDs. What do five unmatched IDs from each side look like?
Written by the teacher as an example. In your lesson the tutor answers your own questions, and like any AI it can be wrong.
What you will be able to do
- Handle time zones, daylight saving and day boundaries correctly
- Diagnose and fix low match rates when joining on dirty keys
- Match records across sources with a measured error trade off
- Manage changed definitions, schema drift and late arriving data
- Keep an assumptions log that makes analysis defensible
Lesson plan
- 1 Time is harder than it looks Work correctly with time zones and day boundaries. Start
- 2 Text, encodings and free text Clean garbled text and messy categories. Start
- 3 Joining on dirty keys Raise match rates and understand unmatched rows. Start
- 4 Matching entities across sources Resolve records that refer to the same person or company. Start
- 5 Definitions and schemas that change Keep trends meaningful through changes. Start
- 6 Late data and the assumptions log Handle incomplete recent data and document assumptions. Start
Try asking
About this tutor
An intermediate tutor for analysts and data scientists who have learned the basics of cleaning and now face the harder problems of real organisations. You will deal with time zones and daylight saving changes, text encodings, free text categories, joining on dirty keys, matching records that refer to the same person or company, definitions that changed over time, schema changes and late arriving data. Each lesson is a realistic case built from common workplace situations, and you finish with an assumptions log habit that makes your analysis defensible.
Reviews
Students can review a tutor after a paid lesson. Nobody has yet.
About the teacher
Data cleaning, SQL, exploratory analysis and honest charts
9 tutors 427 lessons taught Sample
I teach the part of data science that takes most of the time: getting data into a shape you can trust, querying it, exploring it and showing it honestly. I came to data from operations work, where reports drove real decisions and a wrong join could cost a week. I teach by handing you small, deliberately messy tables and asking...
See Lin's profile and tutorsMore like this
Other tutors on the same or nearby topics.