Synthetic Data Quality Pipeline

Moving messy records without guessing

A small Python pipeline that cleans what it safely can and hands everything else to a person, with the reason attached.

My role
Requirements, edge cases and approvals; AI tools did the implementation
Built with
Python, pytest
Data
60 fictional equipment records
Source on GitHub

Messy records in. Traceable data out.

Before and after. Before: rows from the fictional sample input, where one has a blank ID, one has the date 2024-02-30, one says N/A for quantity, one has the category “Pressure Unit” and one repeats another row’s ID. After: clean records with standard categories, and an exceptions file holding rows for a person with their reasons.
Real rows from this project’s fictional sample data.

The situation

Before inventory moves into a new system, someone has to decide what every messy record means. Guess wrong and a pump gets quoted as a sensor, or a quantity nobody entered quietly becomes zero.

This project rebuilds that problem with 60 fictional equipment records and one rule: when the data can’t decide, a person does.

Where this comes from

In my professional role, I led an accuracy-first migration of approximately 2,500 inventory records into an in-house quoting module. I coordinated five review rounds with sales and product teams to resolve ambiguous equipment rules and legacy free-text entries before launch.

Three decisions that shape it

  1. Flag, don’t fix.

    A category like “pressure unit” could mean a pump or a sensor. Instead of picking one, the row goes to a person with the reason attached. It’s slower, but an ambiguous category is never resolved silently.

    Exact alias matching; no fuzzy matching.

    Illustration: the label “pressure unit” could point to a pump or a sensor, so the row is sent to human review.

  2. Accept one date format.

    03/04/2024 is March 4 to one reader and April 3 to another, and an impossible date like February 30th looks normal at a glance. Only real calendar dates written as YYYY-MM-DD get through.

    Strict YYYY-MM-DD with ASCII digits; impossible dates rejected.

    Illustration: 03/04/2024 reads as March 4 in the US and April 3 in the UK, so the date is rejected rather than guessed.

  3. Reject every copy of a duplicated ID.

    When two rows claim the same ID, nothing in the data says which one is real, so neither is kept. Rejecting only the second copy would quietly trust whichever happened to come first.

    IDs compared after trimming; case-sensitive.

The result

All 60 rows are accounted for: 37 clean records and 23 held for review, each with its reason. Run it twice and you get the same files.

All 60 rows reconciled; duplicate IDs are the most common review reason

Synthetic sample chart: 60 rows, 37 accepted and 23 held for review. Review reasons, one per row: duplicate ID 4; ambiguous category, invalid date, invalid quantity and unknown category 3 each; missing ID and missing quantity 2 each; missing category, missing date and missing item name 1 each.
SYNTHETIC RESULT The 60-row generated sample reconciles completely. Counts come from the committed outputs/summary.json.

What I’d do next

A simple review screen: each held row with its reason and the closest likely match shown, with the final call left to the reviewer.

Technical notes

  • Input and mapping files are checked for structural problems before anything is written; a malformed file stops the run with exit code 2, while bad rows become reviewable exceptions.
  • Every input row gets exactly one outcome, and summary.json reconciles input = accepted + rejected.
  • A blank quantity is missing, while an explicit 0 is a valid count; neither is defaulted.
  • Exceptions keep the source row number, the raw values and every error code, so a reviewer sees exactly what arrived.
  • Outputs carry no timestamps, so a rerun is byte-for-byte identical.
  • Standard-library Python at runtime; 21 pytest tests, including a small fixture whose expected output was worked out by hand.

Full method, tests and run instructions on GitHub

My role

I co-developed the original agent prompt kit for this project with AI assistance, and I provided the business context, project purpose and initial architecture outline. I set requirements, evaluation criteria, edge-case priorities and approval decisions.

I used a planner-and-builder AI workflow to produce the implementation and supporting documentation. AI tools performed the implementation work, while I remained responsible for the project's direction and final approvals.

During the first review round, I personally followed the project's run-and-check instructions in PowerShell and confirmed that the outputs matched the agents' reports. Later corrections and their regression results were verified by agents.

What I learned

I learned to distinguish missing information from an explicit zero and to preserve that distinction throughout validation. I also became more deliberate about using deterministic transformations only when the available facts support a clear decision. Ambiguous mappings belong in a traceable review process rather than being resolved through an undocumented guess.

How these were built

Academic connections

These are conceptual connections to the reasoning described in Applied Thinking. They do not claim formal implementation of every theory.