A timezone bug that cost us three days

Daily order counts were slightly wrong for two weeks. Not obviously wrong — a few percent, shifted between adjacent days, invisible at weekly granularity.

The cause was one line:

loaded_at = datetime.utcnow()          # naive: correct value, no tzinfo

utcnow() returns the correct UTC time with no timezone attached. Anything downstream that localises it treats it as local time. Our warehouse column was timestamptz, so the driver helpfully interpreted the naive value as server-local and shifted it by the server's offset.

The correct version:

loaded_at = datetime.now(timezone.utc)  # aware

Python has since deprecated utcnow() for exactly this reason, which is validating and did not help us at the time.

What made it take three days: weekly totals were perfect, because the error moved rows between adjacent days rather than losing them. Every aggregate check we had was weekly. The only signal was daily, and daily numbers are noisy enough that a few percent looks like a Tuesday.

Two things changed:

Aware datetimes are enforced. A lint rule bans utcnow() outright.

We added a daily reconciliation against the source system's own count, not just a weekly one. Checking at the grain you publish at, rather than the grain that is convenient, is the general form of the lesson.