Do not put a derived hour in a dedupe key
Beeswax bid_hour is a naive America/New_York wall clock. Appending Z stamped rows four hours early, duplicated an hour per campaign, and doubled spend from $24.46 to $48.92.
Published 2026-09-09 · Search job: reporting timezone bugs
Reporting timezone bugs become money bugs when the dedupe key is built from a timestamp you derived. Beeswax bid_hour is a naive wall clock in the zone the report was requested in. This seat requests America/New_York via BUZZ_REPORTING_TIMEZONE. The old parser appended Z and called it UTC. Every delivery row was stamped four hours early. The dedupe key was built from that derived bucket_start_at. The next sync inserted a second row for auctions already recorded rather than updating them.
Ten campaigns each gained a duplicate hour. sum(spend_cents) filtered to non-snapshot sources doubled from the true $24.46 to $48.92. The daily-cap guard and the impression debit read that sum. The platform believed the fleet had spent about 2x reality and paused all ten campaigns on a cap they had not reached.
What the identity has to be
A delivery rollup's identity must never depend on how we interpret its timestamp. The fix is provider_report_hour_label on the parsed row. The dedupe key now uses Beeswax's own reported hour string. The conversion can be corrected or re-zoned any number of times and rows update in place.
The apply path also deletes two computable legacy keys: the correct-derived one, and the one the original skewed parser would have produced by appending Z. That is what heals stranded rows with no cleanup script and no fuzzy matching on amounts.
Never put a derived value in a dedupe key. Never widen that delete to match on impressions or spend. A campaign can legitimately serve identical numbers in two different hours.
What did not break
The prepaid ledger was not over-debited. calculateCrossSourceCoveredSpendDebitCents reconciles the reporting sum against the lifetime snapshot. That cross-source check is load-bearing. Do not simplify it. The two sources themselves must not be summed. Two spend sources, one auction.
What we will not claim
We will not treat $48.92 as real spend. We will not treat the pause as proof the daily cap is wrong. We will not "fix" a future timezone by changing the key again. Change the conversion. Keep the label.
A dashboard that then shows odd hours or missing clicks after a timezone correction is usually reading the leftover duplicate or the snapshot zero, not a new provider gap. Why a dashboard can show zero clicks.