The Bug That Only Happened on Tuesdays

The ticket said: "Weekly partner export fails. Happens most weeks. No pattern identified."

It had been open five months. Four people had been assigned to it. Each had done what I was about to do, which was look at the most recent failure, find nothing conclusive, mark it transient, and close it. It had been reopened three times.

What follows is roughly nine hours of work, compressed. I am keeping the wrong turns in because the wrong turns are the interesting part.

The symptom

A batch job runs nightly at 02:00 UTC. It pulls the last seven days of transactions, aggregates them per partner, writes a CSV to object storage, and notifies a downstream system. Normal runtime is about four minutes.

When it failed, it failed like this:

partner_export.exceptions.EmptyExportError: refusing to write export with 0 rows

At least it was a deliberate failure. Somebody had thought about it and decided that writing an empty file was worse than crashing. Good instinct, and that guard is the only reason any of this was ever noticed.

First hypothesis: upstream data is late

The obvious explanation. The job asks for the last seven days of transactions. If the ingestion pipeline that fills the transactions table has not finished by 02:00, the query returns nothing.

This has the shape of a real answer. Intermittent, timing dependent, and it explains why nobody could reproduce it during the day.

I checked ingestion completion timestamps for the last sixty days. Median completion 00:41. Worst case in two months, 01:12. Never within forty minutes of the export job.

I also checked whether the transactions table was actually empty at the time of a failure. It was not. A point in time query against a recent failure window returned 1.4 million rows for the period.

So the data was there and the job could not see it. Hypothesis dead. But it produced the most valuable artifact of the whole investigation, because to correlate against ingestion I had pulled a list of exact failure timestamps.

Getting the actual pattern

Nobody had done this. The ticket said "no pattern identified" and everyone, including me, had taken that at face value and gone off to examine the most recent occurrence.

I dumped every failure from the job's history, twenty two months of it, into a file and ran the least sophisticated analysis available:

$ awk '{print $1}' failures.txt | xargs -I{} date -d {} +%A | sort | uniq -c
     31 Tuesday

Thirty one failures. All Tuesday.

Sit with that for a second, because the lesson has nothing to do with dates. Four engineers had looked at this. The ticket had been open five months. And the entire mystery collapsed the moment somebody spent ninety seconds aggregating the failures instead of examining one of them.

Debugging an intermittent problem by studying a single instance is like deciding a coin is biased by flipping it once. The instance tells you what a failure looks like. Only the population tells you what causes it.

There was a second pattern too. Thirty one failures across roughly ninety five Tuesdays in the window, so about a third of Tuesdays.

Second hypothesis: something else runs on Tuesdays

A weekly pattern strongly suggests a weekly cause. Something that only happens on Tuesdays: a maintenance window, a competing job, a lock, a partner side process, a cache eviction.

I went through the scheduler. Four Tuesday jobs. Two ran at 06:00 and were irrelevant. One was a vacuum on an unrelated schema. The fourth was a partner reconciliation task touching the same tables, which looked promising, except it ran on Tuesday afternoons, twelve hours after the failure, and disabling it in staging changed nothing.

Dead end. But it forced me to accept that nothing external was different on Tuesdays, which meant the cause was inside the job.

Third hypothesis: an off by one in the date window

If the job computes its own seven day window and does it wrongly, a weekday pattern is plausible. Suppose it computes "last week" as a calendar week and the boundary logic is broken. You would expect failures clustered on whatever day the week rolls over.

This felt right. I went and read the window computation:

end = datetime.now(timezone.utc).date()
start = end - timedelta(days=7)

Which is fine. Boring, correct, no calendar week logic at all. I stared at it for a while trying to make it be the bug, which is a thing I do and should do less.

Reproducing it

I had spent most of a day on hypotheses. Time to stop theorising and make the failure happen in front of me.

The job took an --as-of argument for backfills, so I could run it against any date. I ran it against every date in a recent failing month, in staging, against a production snapshot.

Tuesdays failed. Everything else passed. Reproducible, deterministic, on demand.

This is the single biggest step change in any investigation. The bug stops being something that happens to you and becomes something you can do.

Then I ran it with SQL logging on and diffed a Tuesday run against a Wednesday run.

The queries were byte identical. Same date range, same parameters.

Identical queries, different results. So the failure was not in building the query. It was in what happened to the rows afterwards.

Finding it

The job's shape is: query rows, filter, group, write. I put a counter at each stage and re ran the Tuesday case.

fetched:   1,412,908
filtered:          0
grouped:           0

1.4 million rows in, zero out of the filter. The whole thing was one filter.

def is_in_period(row, start, end):
    txn_date = datetime.strptime(row["txn_date"], "%Y-%m-%d")
    return start <= txn_date.date() <= end

Still looks fine. The dates were right, I had checked them.

So I printed a row.

txn_date = '2026-03-10'
parsed   = 2026-03-10 00:00:00
start    = 2026-03-03
end      = 2026-03-10

That row should pass. And on Wednesday it did.

The difference was one directory up, in the loader that produced row. There were two loaders, a fast path and a slow path, and the slow path used a different format string:

# loaders/bulk.py
row["txn_date"] = d.strftime("%Y-%m-%d")

# loaders/incremental.py
row["txn_date"] = d.strftime("%Y-%W-%d")     # four years old

%W is the week number of the year, zero padded, Monday based. Not the month.

For most of the month that produces a string strptime rejects with %Y-%m-%d, raising a ValueError that a broad except upstream was swallowing into a skipped row. But for the first twelve weeks of the year it is worse, because the week number is a valid month number and the parse succeeds against a completely different date.

So 2026-03-10, which is week 10, becomes 2026-10-10. October. Outside the seven day window. Filtered out.

Why Tuesday, and why only a third of them

Two things had to coincide.

The incremental loader, the one with the bad format string, only ran when the bulk snapshot was stale, which happened after the Monday night compaction job. So the buggy code path was reached on Tuesdays.

%W is Monday based, so within a given week the week number is stable. What varied was whether the resulting fake date landed inside or outside the export's seven day window. During weeks 1 to 12 of the year, %W produces a plausible month and the row is silently relocated in time. After week 12 it produces 2026-13-10 and higher, strptime raises, and the row is dropped by the swallowing handler. Same outcome, different mechanism. In some weeks the corrupted dates landed close enough that a partial result still cleared the non empty guard, so the job "succeeded" with wrong numbers.

That last part is the worrying bit. EmptyExportError only fired when every single row fell outside the window. There were an unknown number of Tuesdays where some rows survived, the export was written, nobody was alerted, and the partner received an incomplete file.

The bug we were chasing was the loud version of a quiet one that had been running for four years.

What I changed

The format string. Thirty seconds.

Deleted the swallowing except. The loader had:

try:
    rows.append(transform(raw))
except Exception:
    continue

This is why the bug survived four years. A ValueError from strptime on forty percent of rows should be an incident, not a continue. It now raises, with the offending value and the row index in the message.

Stopped passing dates as strings between layers. Both loaders produced str and the filter parsed str. There was no reason for either. The row type now carries a date object, formatting happens once at CSV write time, and the class of bug is gone rather than fixed. This was the largest part of the diff and the only part that prevents a recurrence.

Added a data integrity assertion. After filtering, if fewer than half the fetched rows survive, the job fails loudly with a sample of what was dropped. The original guard only caught total wipeout. This catches partial corruption, which was the more dangerous failure.

Backfilled. We regenerated twenty two months of Tuesday exports. Eleven were materially wrong. That conversation was not fun.

What I would tell myself at hour one

Aggregate before you investigate. The pattern was one awk and a uniq -c away and it was available on day one of a five month old ticket. For anything intermittent, the population is the evidence, not the instance.

"No pattern identified" is a claim, not a fact. It was in the ticket, everybody inherited it, and nobody re tested it.

Get a reliable repro before forming theories. I burned most of a day on hypotheses I could have falsified in ten minutes each once I had one.

When identical inputs give different outputs, stop reading the code that produces the inputs. Twice I went back to re read the window computation because it felt like the kind of place this bug lives. The SQL log had already told me it was correct.

A bug that raises is a gift. The EmptyExportError guard somebody wrote years ago is the only reason this was ever found. The silent partial corruption had no such guard. Whenever you find a loud bug, ask whether it is the visible edge of a quiet one. Here, it was.