The duplicate dilemma
Dashboards for a government client have exactly one job: one metric, one number. So when the client told us they were seeing duplicates across almost every metric, going back eight hours, it was not how I wanted to start a Tuesday. Two hours of bad data I could live with. Eight meant they'd been reading wrong numbers for most of a working day — and they'd found it before we had.
The obvious suspect was a left join in the pipeline. It was visibly producing the duplicate rows, and changing it would have made the symptom disappear in ten minutes.
I didn't.
The join was correct, and had been for months. What had actually changed was the master pipeline upstream — it hadn't synced in time, so the join was doing its job perfectly against stale data. "Fixing" it would have cleared the dashboard and left the real failure sitting there, waiting for a worse day.
So I went to the foundation instead. The master pipeline had been wiping and rebuilding its table on every run, which is why downstream jobs kept catching it mid-load. I moved it to marking records active or inactive against a last-updated timestamp — nothing gets destroyed, and anything reading the table always sees a complete picture.
Eight hours of continuous work, two days to sync through to production, and the join stayed exactly as it was.
Eight hours of duplicates, eight hours of fixing, eight glasses of coffee. And one lesson I've used since: when something breaks downstream, the loudest suspect is usually the innocent one.