learning-it by example

Back to the video

Profile Messy Customer Data in SQL Before Any Model

My data profile said 275 blank amounts. The file had 363. Here is why.

Video #309 ยท Forward deployed engineer interviews

Steps in the video

  1. 0:00 My profile said 275 blank amounts. The file had 363.
  2. 0:32 The idea
  3. 1:13 Setup
  4. 1:40 A messy file
  5. 2:12 The profile
  6. 2:51 The clean up
  7. 3:29 Before and after
  8. 4:21 What this does not show
  9. 4:55 Mistakes to avoid
  10. 5:18 My take and next steps
  11. 5:44 Recap

Files

  • clean.py (18 lines)
  • make_data.py (29 lines)
  • run.py (26 lines)
  • survey.py (35 lines)

Download the code (zip)

Tested with

Python 3.12.15 | duckdb==2.0.0

What this does not show

A generated 5,000 row file from one fixed seed, with each problem put in on purpose. The clean-up keeps negative amounts, leaves 341 amounts missing, and keeps the latest row per email, a rule a customer must agree. The first, wrong count of 275 was a side check.

Sources

Learning IT by Example.