Read the file
295,481 rows in one flat file, each repeating its restaurant and its visit.
Case study · A test on public data, not a client
One messy file in. Three clean, linked tables out.
Before we move a client’s data, we rehearse on data as messy as theirs. This is one of those rehearsals: New York City’s public restaurant-inspection file, 295,481 rows, moved into a proper database and checked row by row. A test we ran on our own systems in March 2026, not a client project.
- Rows in one flat, messy file
- 295,481
- From the first row read to the last table built
- 4 min 30 s
- The run’s data-quality score, as its report gives it
- 95/100
01The problem
Real data arrives messy
The city publishes every inspection as one flat file. Each row repeats the restaurant and the visit, so one restaurant appears again and again; some rows record no violation at all, and some visits are dated 1 January 1900. Most business data we are asked to move looks like this.
02What we did
Turned it into a database you can trust
We split the file into three linked tables (restaurants, inspections and violations), set aside what could not be true, and checked every table against the source before calling it done.
03What it showed
Clean in minutes, and checked
295,481 rows became 3 linked tables in 4 min 30 s, with row counts and checksums matching and no record left orphaned. The rehearsal also found five faults in our own tooling, all fixed the same day.
One file in, three tables out
Each fact stored once, in the right place
The file wrote every restaurant and every visit out again on each violation. Split into three tables, each restaurant is stored once, each visit once, and each violation is linked to the visit it was found on.
One flat file295,481 rows
- Restaurants30,832
- Inspections93,673
- Violations289,754
How the run went
Read, split, checked
The same three steps run in every migration we do; here is what each one did with this file.
Split and clean
Each restaurant and each visit stored once; impossible dates and empty rows set aside and counted.
Three linked tables
Row counts, checksums and every link between records checked against the source.
What the run set aside
Rows that could not be true, or held nothing at all, were set aside and counted, never loaded as if they were real.
| Item | Count |
|---|---|
| Rows with no violation | 5,727 |
| Visits dated 1 January 1900 | 3,425 |
Where the 4 min 30 s went
Reading the file was the quick part. Most of the time went into building the three tables and linking them.
| Item | Count |
|---|---|
| Reading the file | 90 s |
| Building the three tables | 180 s |
1,094 rows a second, start to finish
After the move
Every check on the result passed
Row counts match
Every source row is accounted for: loaded, folded into its restaurant or visit, or set aside and counted.
Checksums match
Checksums computed on the source and on the target agree.
0 orphaned records
Every inspection has its restaurant, and every violation its inspection.
0 conversion errors
Every value that was loaded converted to its type without an error.
The run tested us too
Five faults found in our own tooling
A rehearsal is for finding problems before a client’s data is involved. This one found five in our own pipeline, and each was fixed the same day and written into the checklist we go through before every migration.
- 01
The review after each migration stopped without saying so
An automatic review runs after every migration. It was starting and then stopping without a word, because of three separate set-up faults. All three were fixed, and the review now finishes.
For a client: a migration with no review behind it, and nobody aware.
- 02
The review was reading an outdated map of our own system
It looked for information under names that had since changed, so its findings would have been incomplete. Every lookup was checked against the system as it really is.
For a client: a report that leaves things out without saying so.
- 03
Checking a finished job twice raised false alarms
Running the review again on a finished migration recorded worse figures and flagged four problems that did not exist. It now recognises a job it has already measured.
For a client: time lost chasing problems that are not there.
- 04
Six of ten pre-flight checks could not run
The safety checks that run before a migration needed a tool that was missing, so six of ten never ran; that is why the report shows 4 of 10. They were rebuilt so they no longer depend on it.
For a client: starting a migration with less protection than promised.
- 05
Our own instructions pointed to the wrong place
Our automation was told to use an address that our documentation listed but the service did not use. The documentation was corrected.
For a client: a small mismatch that can stall a migration on the night.
Under the hood, for your technical teamShow details
PostgreSQL to PostgreSQL on our own Kubernetes cluster (16 vCPU, 32 GB), 28–29 March 2026. The source was loaded from the city’s public CSV, extracted with a connector, then deduplicated, type-cast and linked by foreign key with SQL in the target. Validation compared row counts and checksums with the source and checked both foreign keys for orphans. Our pipeline has been rebuilt since; this page reports the run as it was measured.
Have a migration of your own?
We rehearse it on a copy of your data first, and you sign off before anything moves to production.
Book a call