The export has duplicate emails, and a spreadsheet filter already feels unsafe. You’ve already accidentally deleted 12 valid entries during a manual dedupe last quarter, and you don’t want to repeat that mistake with this 12,000-row lead list. A purpose-built script removes human error, and adding guardrails like a dry run ensures you don’t modify data before you confirm the dedupe logic works as expected. This guide walks you through building that script with non-negotiable safety features for small project workflows.
Build Next Stack editors
Key column you name before touching rows
The top cause of broken dedupe workflows is the script guessing which column to use as a unique identifier, instead of using an explicitly defined key column. For most use cases like lead lists or user exports, your key column will be an email address, but export tools often name this column inconsistently: `contact_email`, `user_primary_email`, or `work_email` are all common alternatives to a plain `email` header. Before you write a single line of dedupe logic, fill out this key column worksheet to codify your rules, and reference it directly in your script configuration:
| Key Column Worksheet Entry | Value you fill in before running the script |
|---|---|
| Exact header name in source CSV | e.g. `primary_contact_email` |
| Case sensitivity rule | e.g. `ignore case ([email protected] = [email protected])` |
| Trim whitespace rule | e.g. `trim leading/trailing spaces before comparison` |
| Empty key column behavior | e.g. `drop rows with empty key column` / `keep all rows with empty key column` |
Normalize the key column values per your worksheet rules before running any duplicate checks. For example, if you set the rule to ignore case, convert all email values to lowercase first, since nearly all email providers treat uppercase and lowercase addresses as identical. If you set the rule to trim whitespace, strip any leading or trailing spaces from key values to catch copy-paste errors that make identical entries look unique. Illustrative example: a 2023 SaaS lead export had 3 different email columns, so explicitly naming the key column prevented the script from deduping against the secondary sales contact email by mistake.
Dry-run that prints keep and drop counts
A dry run runs your full dedupe logic without modifying any files, so you can validate your rules before making permanent changes. Build your script to accept a `–dry-run` flag that triggers this read-only mode, and use this dense checklist to ensure your dry run and write mode have clear guardrails:
✅ Dry-run mode only reads the source CSV, no write permissions enabled for any file
✅ Dry-run mode outputs total rows read, number of unique key values found, number of rows marked to keep, number of rows marked to drop
✅ Dry-run mode prints a random sample of 5 duplicate pairs for manual verification
✅ Write mode is only enabled if the `–dry-run` flag is explicitly omitted
✅ Write mode requires you to input the dry-run’s keep count as a confirmation prompt before writing
If the dry run’s drop count is higher or lower than you expected, you can debug your normalization rules before ever touching production data. For example, if you see 2000 duplicates flagged for a list you expected to only have 200, you may have accidentally set the key column to `lead_city` instead of `primary_contact_email`. You can add an optional flag to export the full list of marked duplicates to a temporary CSV during the dry run, so you can review every entry if you’re working with high-stakes data. Example measurement: a 15,000 row CSV takes less than 2 seconds to run a dry run on a standard laptop, so there is no downside to running this check first.
Write mode that never overwrites the source
Even the most well-tested script can cause data loss if it accidentally overwrites your original source file. Build your write mode to never modify the source CSV, even if you pass an incorrect file path. Hardcode output file names to follow the pattern `[source_filename]_deduped_[timestamp].csv` and `[source_filename]_duplicates_[timestamp].csv`, so every run creates a new, timestamped file with no risk of overwriting existing data. Add a pre-run check that exits immediately if the output file path matches the source file path, to catch accidental misconfigurations.
The deduped output file should include all original columns from the source CSV, not just the key column, so you don’t lose associated data like lead source, sign-up date, or contact name. The duplicates output file should include every row marked for removal, so you can recover entries later if you find a flaw in your dedupe logic. Example measurement: a 10,000 row deduped CSV adds less than 1MB of storage, so storing multiple output versions for different runs has negligible cost.
Header mismatch you refuse instead of guessing
Even if you fill out the key column worksheet correctly, typos or missing columns in the source CSV can break your dedupe workflow. Build a pre-run check that verifies the exact key column name from your worksheet exists in the source CSV’s header row. If the column is missing, the script should exit immediately with a clear error message, instead of trying to guess which column to use. Common header mismatches include typos like `emial` instead of `email`, extra spaces in the header like ` email ` instead of `email`, or missing columns entirely if you exported the wrong report from your tool.
You can extend this check to include any other required columns you need to preserve, such as `lead_source` or `sign_up_date`, so the script exits if those columns are missing rather than generating a useless deduped file. You should also add a check for duplicate column names in the header, as that can cause the script to read the wrong column even if you named the key column correctly.
Fixture CSV you commit next to the script
A fixture CSV is a small, controlled test file with known duplicates that you use to validate your script logic every time you modify it, before running it on real data. Your fixture should cover all the rules in your key column worksheet: case variations, extra spaces in the key column, empty key column values, multiple duplicates of the same key, and valid unique entries. For example, a 10-row fixture might include 3 unique emails, 2 duplicates of the first email (one with uppercase letters, one with a trailing space), 2 duplicates of the second email, 1 empty key column, and 2 other unique entries.
Commit the fixture CSV and its expected deduped output to your git repo next to the script, so anyone using the script can run a quick test to confirm it works as expected. You can build an automatic test that runs the script against the fixture and compares the output to the expected result, catching accidental changes to the dedupe logic before they affect real data. Illustrative example: your fixture test takes less than 1 second to run, so you can run it every time before processing a new export to catch bugs early.
Before you process your next duplicate-heavy CSV export, fill out the key column worksheet, run the fixture test to confirm your script logic works, then run a dry run and cross-check the sample duplicates against your source file before enabling write mode.