Clearhouse

A shop till, a webshop and a wholesale feed each have a reasonable way of storing orders. Put their exports together and those choices start causing problems. The till uses Slovenian headers and decimal commas. The webshop nests its order lines and mixes timestamp formats. The wholesale feed writes prices in cents.

Some orders appear in more than one system. A customer buys online and picks the order up in the shop, so counting the webshop order and the till receipt as separate sales doubles the revenue. A file that opens neatly in Excel can still be wrong in a way that matters.

Clearhouse reads each export into the same raw record shape and keeps a reference to its original file and line or element. Parsing and validation happen after that, so each repair can be logged beside the original value.

The pipeline checks products against a catalogue, matches customers to a CRM list, converts totals to euros and reconciles repeated orders. It writes clean order, line and customer tables, a separate file of duplicate copies, and a quarantine file for orders it cannot trust.

An HTML report brings those outputs together. It shows the record counts, issues by source and the reason each quarantined order was set aside. Someone reviewing the run can trace a problem back to the export it came from.

A product code such as brk_42 has one clear match in the catalogue, so the parser can normalise it and record the change. A date it cannot parse or an unknown product code needs a person to look at it, so the order goes to quarantine.

Matching a shop pickup to an online order takes more care. A valid order reference is the first choice. Without one, the customer, items and total must agree, and pickup must fall within seven days. The match is accepted only when exactly one online order fits. If two fit, the receipt stays separate and gets a flag.

The pipeline also avoids repairing totals from values it already knows are invalid. An early version recalculated the total of an order with a negative quantity. That made the numbers agree without making the order trustworthy.

The data generator starts with clean orders, writes the messy exports and records every defect it introduces. A separate scorer reads the pipeline's output files and compares them with that ground truth. The pipeline never reads the answer, and the scorer never imports the pipeline's code.

On the default generated dataset, 1,283 input records become 1,143 clean orders, 77 quarantined orders and 63 duplicate copies. All 1,220 underlying orders receive the expected outcome, all 63 copies are paired with their originals, and all 8,001 checked fields on clean orders match the truth.

The scorer also has tests that deliberately break a correct output, including losing an order and counting one twice. It has to name those problems. A perfect score would mean little if the scorer accepted everything.

One damaged Slovenian name exposed an ordering bug. A byte in the broken encoding looked like a non-breaking space, so tidying whitespace first destroyed the information needed to recover the original letter. Repairing the encoding had to happen before cleaning up spaces.

Reading the final CSV would have been an easy way to miss that. Comparing each field with a known answer made the mismatch visible.

• The exports are synthetic. These are sample results, not results from a client deployment.
• I wrote the generator and pipeline together. Passing shows that the pipeline handles the defects I designed for; real exports can fail in other ways.
• Each generated order gets at most one defect. Orders with several interacting problems need more coverage.
• The catalogue, CRM and exchange rates are trusted inputs, and the pipeline keeps the dataset in memory.