Merchandising data mart rebuild for a grocery chain

Merchandising and finance reporting for the chain ran on more than four hundred SQL Server stored procedures accreted over a decade, chained by scheduled jobs whose dependencies existed nowhere but in effect. The nightly run finished around 6am on good nights; on bad nights, one person — the same person, always — diagnosed it before the merchandisers arrived at seven. Two acquisitions had added two incompatible POS formats absorbed via per-chain patches, and by the time I joined, adding a column to a sales report was a two-week archaeology project. The mandate was a rebuild, with the explicit warning that the numbers had to keep matching while we did it.

Data engineer, one of three on the rebuild alongside the incumbent pipeline's keeper and a team of report analysts, for fourteen months. My DBA years mattered here — half the legacy logic only made sense if you could read the locking and indexing decisions of whoever wrote it, and I could usually name the year a procedure was written by its style.

I owned ingestion for all three POS formats — the original chain's and both acquisitions' — and the core model layer for sales, margin, and stock movement, which everything downstream reads. The parallel-run diff harness was mine too: the tool that compared every legacy report against its rebuilt equivalent, row by row, and printed the differences finance would ask about. Cutover sequencing per report family was a shared decision, but the harness that made each cutover defensible was my code.

The strategic decision was changing one thing at a time: keep the existing SQL Server extracts exactly as they were and rebuild the transform layer first in dbt on BigQuery, so any discrepancy had one possible cause instead of three. Every report family parallel-ran until its diff hit zero for two consecutive weeks — finance's trust was the actual deliverable, and to the centavo was their stated and non-negotiable definition of matching. I put tests on source assumptions at ingestion, so a POS format drift fails the load loudly at 11pm rather than surfacing as a wrong number in a merchandiser's Monday meeting — the old pipeline's habit of absorbing bad input silently was, more than any performance problem, the thing we were actually replacing. And one legacy procedure survives on purpose: a rebate calculation nobody can yet restate with confidence, documented, quarantined behind a comparison check, and scheduled for its own project rather than pretended away.

Month-end could never be disturbed — cutovers happened in the first week of a month or not at all, which stretched the schedule and was correct. The store count grew mid-project as the second acquisition's branches came online, so ingestion was rebuilt while its inputs multiplied. And the one person who understood the legacy chain was also the person operating it nightly; her availability was the project's true critical path, and protecting her mornings became an explicit planning constraint.

The nightly run now lands at 2:30am instead of 6am, and its failures — rarer, and honestly reported — page with a named failing model and its lineage rather than requiring the one person who knows. Report families cut over with zero-diff parallel runs behind them, and finance signed each one, which had never happened in the old pipeline's decade. New-report lead time went from weeks to days; the quarantined rebate procedure remains, still honest, still scheduled, a monument I refuse to pretend is finished.

The legacy pipeline's keeper is the co-author of everything — fourteen months of her mornings explaining what a procedure really did versus what its name claimed. The second data engineer built the orchestration and alerting in Airflow, the report analysts adjudicated every diff the harness surfaced, and finance's controller — our sternest reviewer — became the rebuild's sponsor the week her month-end close stopped depending on a 6am prayer.