Data pipelines and ETL, 7 of 26
Ten rows in.
Ten rows accounted for.
Nobody asks whether a pipeline works. They ask how you know nothing was lost, and most pipelines answer that with a log line saying how many rows they wrote.
Press the button. The file below is deliberately awful. Everything in it leaves as loaded, rejected or duplicate, the three add up to what went in, and the last number comes back out of the database rather than from the thing that just wrote it.
What is wrong with that file
2026-02-30
a date that parses. new Date rolls it forward to the 2nd of March, so a naive check accepts it and the month-end report gains two days of the wrong month
2026-01-32
the right shape and not a date. The first version of this crashed the entire load on that one cell
(250.00)
a negative written the way accountants write it
1.234,56
a thousands separator and a decimal comma, which is the same number the other way round
$980.00
a currency symbol somebody typed into a numeric column
XBT
a currency that is not on the list, rather than one that fails later at the bank
Four decisions
The count comes back out of the database
A loader reporting how many rows it wrote is the one number that cannot catch a silent drop. After the write, the rows are counted from the far side and both numbers are on the screen. If they disagree the run is marked as not balancing rather than reported as a success with a smaller number.
A duplicate is not an error
Two rows carrying the same reference are the same fact stated twice. Counting them as errors turns a reloaded file into a page of red, and then people stop reading the red. They get their own count and their own colour, and the line where the reference was first seen.
Every problem in a row is reported, not the first
A row with a bad address, a bad currency and an impossible date comes back with three reasons. Stopping at the first is how somebody fixes and resubmits the same file five times.
Running it twice loads it once
The write is one upsert on a natural key, so the second run updates the rows it already wrote instead of adding a second copy. Press the second button and watch the number the database reports stay where it was. A pipeline that cannot be safely re-run is a pipeline nobody dares re-run at two in the morning.
Tested without a database
54 tests on the rules, none of which touch the network: the leap year, the impossible day, the accountant's brackets, the two continents that disagree about which mark is the decimal one, and the arithmetic refusing to balance when a row goes missing.
npx tsx lib/pipeline/rows.test.ts
Where this stops
One file, five hundred rows, one table. A real load runs to millions and arrives in pieces, which changes the shape: the reconciliation moves to a watermark and a checksum per batch, and the retry has to know which batches it already finished.
The arithmetic is the same at both sizes, which is why it is the part worth showing.
The endpoint takes the same file and lists the recent runs. The database it writes into is the one everything else here uses. The whole list is 41 requirements from 114 job posts.