data-cleaning-pipeline

Write the cleaning as code that reads raw data and writes a new clean artifact, never editing the original. Order stages as type coercion, structural fixes, normalization, sentinel handling, deduplication, then validation, and end with a reconciliation report counting every mutation.

Define a match key after normalization, then apply an explicit survivor rule such as most recent by updated_at, most complete, or source priority. Using drop_duplicates(keep='first') on unsorted data keeps a random row; log the dedupe count and keep losers queryable.

Fuzzy matching should only propose candidates for a human-reviewed mapping table checked into version control. Auto-merging on string similarity is unsafe because names like 'Johnson Ltd' and 'Johnsen Ltd' clear 90% similarity while being different companies.

It silently converts unparseable values to NaN, which can quietly delete a significant share of a column. Always count the new NaNs after coercion and route failed rows to a rejects file with reasons so the data loss is visible.

Run exploratory data analysis first to profile grain, nulls, sentinels, and outliers; cleaning without profiling is guessing. The EDA findings become the worklist that drives each cleaning stage and its per-column null policy.

Add drift gates using tools like Great Expectations, pandera, or dbt tests that compare today's null rates and schema against historical tolerances. This catches vendor changes, such as a renamed column, at load time instead of in a quarterly report.