#309 Forward deployed engineer interviews 6.3 min
Profile Messy Customer Data in SQL Before Any Model
My data profile said 275 blank amounts. The file had 363. Here is why.
Run the profile and compare
Copy the code folder first. Install the one package the video uses, duckdb at version 2.0.0, then run python run.py. Compare the before and after lines with the video, which showed 5000 rows, 363 blank amounts, 345 repeat rows and 7 country spellings before, and 4655 rows, 2 countries and 341 missing amounts after.
A hint
The script writes customers.csv in the folder you run it from.
Predict a smaller file
In make_data.py change rows=5000 to rows=1000 in the build function. Predict which numbers in the before and after lists will fall and which will stay the same, such as values in source and countries. Then run python run.py and check your prediction. Afterwards change rows=1000 back to rows=5000.
A hint
Think about which numbers count rows and which count different spellings or values.
Add a new way to write a blank
In make_data.py add "none" to the BLANKS list. Without changing survey.py, predict whether the profile line blank amounts counts those new blanks. Then add 'none' to the BLANKS text in survey.py so that it does, and predict how the number changes. The generated file changes too, so the numbers will not match the video. When you have finished, put both files back as they were. Why is a profile you have not checked a risk?
A hint
The profile only counts the spellings that are in its own list.
Keep the earliest row instead
In clean.py change ORDER BY signup DESC to ORDER BY signup ASC in the people table. Run it once first to note the numbers. Predict whether the number of rows after cleaning changes, and whether the number of missing amounts changes (it can, because the earliest row for a person may have a blank amount where the latest does not). Then run it and write down what changed. Who should agree which row to keep?
A hint
The query still keeps exactly one row for each email.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
Up next in Forward deployed engineer interviews
More in Forward deployed engineer interviews
- #302 Forward Deployed Engineer Interview: What Is Tested
- #303 The FDE Decomposition Round: Vague Ask to a Plan
- #304 Scope an AI Pilot: Metric, Baseline and Stop Rule
- #305 Retry a Flaky API Without Duplicates: Idempotency Keys
- #306 Webhooks Done Right: Signatures, Retries and Replays
- #307 Schema Drift: Map a Customer's Data and Catch Changes
