learning-it by example

#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.

1. Why did the first DuckDB profile of the messy customer file report 275 blank amounts when the file really had 363?
2. What is the point of profiling a customer file before giving it to a model?
3. In the DuckDB profiling of the generated 5,000-row customer file, what did the cleaned data hold compared with the raw file?
4. Why does the profiling code read the CSV with all_varchar=true?
5. The clean up keeps one row for each email, the latest. What does the video say about that choice?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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

All Forward deployed engineer interviews videos

Related videos