Wall-Clock Schedule Hazard

On the last Sunday in October a revenue load ran twice and doubled a day's figures. The following March the same job did not run at all. The orchestrator reported success both times, because from its point of view it did exactly what its schedule said.

Civil time is not monotonic. Daylight-saving transitions make one local hour occur twice and another not exist. Jurisdictions change their rules, sometimes with a few months' notice. Offsets are not all whole hours — India runs at UTC+05:30, Nepal at +05:45 — so "local midnight" and "a date boundary" are not the same instant everywhere, and a partitioning scheme that assumes they are produces rows in the wrong day's partition for part of the estate.

A schedule is a business statement; a trigger is a machine statement. The hazard is what happens when the second is expressed in the units of the first.

Why it matters

The faults are silent by construction. A duplicate execution is not an error — the task succeeded, twice. A skipped execution is not an error either; nothing ran, so nothing failed. The damage appears downstream as numbers that are wrong rather than missing, which is the harder kind to notice and the slower kind to correct.

The exposure scales with the estate rather than with the risk. A platform with 300 local-time schedules faces 2 transitions a year against each of them — 600 exposures — and has no list of which ones matter. The idempotency change that neutralises them costs roughly 8 hours per load.

Implementation patterns

• Trigger in UTC and convert inside the job. The business-day boundary becomes a parameter — "the trading day is 00:00 to 24:00 in Europe/London" — resolved in code where it is visible, testable and can differ per market.
• Make every task a pure function of its logical date, so a repeat is a no-op. Loads become a merge on (business_key, logical_date) rather than an append keyed on a generated surrogate.
• Add the assertion that makes a mistake loud: a uniqueness constraint or post-load check on (business_key, logical_date). This is the cheapest control in the area and the one most often absent.
• Keep the timezone database current in the images that run schedules, because rule changes ship as data, not as code.
• Never schedule anything in the hour around a transition even after doing all of the above, as a belt-and-braces measure that costs nothing.

Industry example

This is why general-purpose schedulers grew explicit timezone handling from about 2005 onward, and why mature orchestration tools separate the logical date of a run from the wall-clock instant it was triggered. The separation exists because the industry learned that a run needs an identity independent of when it happened to start — which is the same property that makes backfills, retries and reruns safe, and is why the fix here generalises well beyond daylight saving.

Failure scenarios

• Autumn duplicate: the 01:30 job runs twice and an append-style load doubles the day. Row-count checks compare against the previous day, and on a Sunday the variance threshold is already wide enough to absorb it.
• Spring skip: the same 01:30 job never fires, the downstream sees no new partition, and the freshness alert — if it exists — is set to a 24-hour threshold that the next day's run satisfies.
• A rule change in one jurisdiction moves a transition date, and a schedule adjusted last year to avoid the window is inside it again.
• Half-hour offsets put events either side of local midnight into the wrong daily partition for one market, producing a small persistent discrepancy that is blamed on late-arriving data.

Trade-offs

Converting an estate to UTC triggers is not free: the change itself is configuration, but re-testing every partition boundary and every report written against local dates is weeks of work for a mature platform. In a jurisdiction that does not observe daylight saving, that work returns nothing.

The idempotency change is the opposite trade. It costs about a day per load, and it removes the damage from the entire class of duplicate executions — transitions, scheduler restarts, manual re-runs, retried tasks whose worker was only presumed dead. The timezone fix removes one cause; idempotency removes the consequence of all of them.

When not to use it

Do not start with the timezone migration. Start with idempotency and the uniqueness assertion, because they are cheaper, cover more causes, and make the timezone work non-urgent rather than critical.

Skip the timezone migration entirely for a platform operating in a single zone with no daylight saving — much of Asia and Africa — unless and until a second market appears. It flips the moment a second jurisdiction is added, because at that point no single local clock is correct for the whole schedule, and the conversion that was optional becomes the only coherent option.

Interview question

Q: A nightly load ran twice on one Sunday in October and not at all on one Sunday in March, and the orchestrator reported success every time. Explain what happened, then tell me which fix you would ship first and why — the timezone change or something else.

What a strong answer covers: the repeated and missing hour, and that neither produces an error · why an append-style load turns a duplicate execution into corruption while a merge on the logical date does not · shipping idempotency first because it also covers restarts, manual re-runs and zombie-task retries · the uniqueness assertion that converts silence into a failed run · UTC triggers with business-day conversion inside the job as the structural fix · why moving the job an hour is not a fix · and the adjacent traps of rule changes and non-whole-hour offsets.

Quick check

Quiz: Why does a daylight-saving transition corrupt data rather than raising an error? Because the scheduler did exactly what its schedule said — one local hour occurs twice — so the duplicate run is a success, and only a non-idempotent load turns that into wrong numbers.

Flashcard: Which fix comes first, UTC triggers or idempotent loads? Idempotent loads, because they cost about a day and remove the damage from every duplicate-execution cause, while the timezone change removes only one of them.