Nothing is scheduled to fail at 3am
It happens then because that is when the batch window opens, when the upstream export lands, and when the fewest people are awake to catch it. The failure rate at 3am is not unusual. The cost is. A pipeline that fails at 3am and is back by 3:12 is an operational fact. One still being untangled at 10:40 the next morning, with a finance team asking why yesterday's revenue number moved, is a design problem that surfaced at night.
In the systems we get called into, transform logic is almost never the cause. SQL that has produced correct numbers for eight months does not spontaneously become wrong. What breaks is everything around it: the input landed late or not at all, the input changed shape, the previous run died halfway and left a table the next run cannot reason about, two runs overlapped, a token expired, a retry wrote the same rows twice. None of that is hard in daylight. All of it is hard when the person holding the pager has been awake for four minutes and does not own the code.
So the design goal is not fewer failures. It is a pipeline where the correct response to any overnight failure is one of two sentences: re-run it, or leave it, it will catch up on its own. Everything below exists to make one of those sentences true.
You are probably here because
- A job died partway through last night and nobody is sure whether re-running it will double the rows.
- Yesterday's number moved after it was published, and no one can say which run changed it.
- Backfilling a few weeks means somebody hand-writing a one-off script at 11pm.
- The alert channel is full of failures that fixed themselves, so the one that mattered scrolled past.
All four are usually the same root cause — the pipeline cannot be re-run safely — and the next two sections, on the re-run property and the four write patterns, are where that gets fixed.
Root Causes of Overnight Failures — Our Ranking by Frequency
Relative weights from what we find when stabilizing an existing platform. Your mix will differ; the bottom row is always smallest.
The re-run property decides everything else
One question separates a cheap failure from an expensive one. Can you re-run yesterday's job right now, twice, and get exactly the table you would have had if it never failed? If yes, the on-call procedure is one command and the incident is over. If no, every failure becomes an investigation: what got written before it died, what must be deleted first, whether downstream already read the partial state, and whether re-running doubles a row nobody will notice for a quarter.
Most pipelines fail this test for a boring reason. They append. A job running INSERT INTO fact_orders SELECT ... FROM staging with no key and no partition scope cannot distinguish a first run from a second. The engineer who wrote it intended never to run it twice, and that intention is not a property of the system.
Four ways to write, and what each costs
Idempotency is not a philosophy, it is a write pattern. Four are in common use, each with a real cost. Pick per table, not per platform.
| Pattern | How it works | Safe to re-run | Cost / when it fits |
|---|---|---|---|
| Partition overwrite | The unit of work owns a partition and replaces it whole. Delta Lake replaceWhere, Iceberg overwrite by filter, INSERT OVERWRITE, dbt insert_overwrite. | Yes, unconditionally | Cheap, and the default for time-partitioned facts. Requires that a row's partition never changes. |
| Merge on a stable key | MERGE INTO ... ON target.k = source.k with a deterministic business key or a hash of one. | Yes, if the key is stable | Costlier than overwrite on large tables. The right answer for slowly-changing dimensions. |
| Append with run id | Every row carries the run identifier; readers select the latest successful run and a pruning job removes the rest. | Yes, but storage grows | Useful when you need an audit trail per run. Pay in storage and reader complexity. |
| Bare append | INSERT INTO ... SELECT, no key, no scope. | No | Fast to write, and the most common reason a team cannot re-run anything safely. Treat as debt with a date on it. |
The pattern only holds if the query is deterministic, and three things quietly break that. Wall-clock filters: WHERE created_at > now() - interval '1 day' returns a different set every time, so a re-run at 09:00 silently covers a different window than the original at 03:00. Take the logical interval as a parameter. Airflow exposes data_interval_start and data_interval_end; elsewhere, pass --from and --to and refuse to run without them. Generated identifiers: a surrogate key minted from a UUID at load time gives the same source row a different key every run. Hash the business key. Ordering assumptions: ROW_NUMBER() over a non-unique sort column picks a different winner on re-run, so give every window function a deterministic tiebreaker.
On a streaming stack the same design set applies under different names. Idempotency becomes deduplication or an upsert on the message key, the re-run property becomes resetting a consumer offset, and the lateness window becomes watermark configuration. Streaming adds one failure mode batch does not have: small files accumulating until read performance degrades, which makes the compaction job the thing that pages you.
Backfill is a command, not a favor
Every pipeline needs a backfill eventually: a source fixes six weeks of bad records, a definition changes, a currency conversion turns out wrong. Either the team runs the same code path with different parameters, or somebody writes a one-off script at 11pm, and the one-off script is where the real damage happens.
Build backfill as the same entry point as the scheduled run, parameterized by interval. Then add what makes it survivable: bounded concurrency, because sixty days launched in parallel will take down the source database and turn a data fix into an outage; a separate compute pool, so a backfill cannot starve the nightly run; and a progress record, so a run interrupted at day 34 resumes at day 34. Watch the orchestrator default: Airflow schedules every missed interval between a DAG's start date and now unless catchup is off, so a new DAG dated three months back can launch ninety runs at once against production.
The two hours that do not exist
Schedule in UTC and store timestamps in UTC. Local time is a display format, not a scheduling primitive. In the United States, clocks jump from 1:59:59 to 3:00:00 on the spring daylight-saving boundary, so a job scheduled at 2:30 a.m. local never runs that day. In the fall the 1:00 hour occurs twice, so a job at 1:30 a.m. local runs twice: a duplicate load once a year with no code change to blame. The related trap is business-day logic built from a day-of-week function and a hardcoded holiday list. Build a calendar table, load it once a year, join to it.
Late data, and the window you have to choose
Event time and processing time are different, and every pipeline takes a position on the gap whether or not anyone writes it down. A mobile client buffers offline and uploads six hours later. A payment settles two days after authorization. A partner's export skips a region and resends it the next morning. If your job closes each day at midnight and never looks back, all of that is silently lost, and the loss appears as an unexplained divergence from the source system.
Two answers work. The first is a rolling reprocessing window: rebuild not just yesterday but the last N days, where N comes from the observed lateness distribution rather than a guess. Log the difference between event and arrival timestamps for a month, take the 99th percentile, set N a little above it. The cost is easy to state: a three-day window costs roughly three times the compute of the incremental step, and removes an entire class of "the number changed and nobody knows why." On partition-overwrite tables it is nearly free.
The second is explicit restatement: close the day and handle corrections as separate, dated, logged adjustments. That is the right call when consumers need a number that never moves after publication, which is most things finance touches. What matters is that the choice is deliberate, the lateness allowance is written down, and data arriving outside the window lands somewhere a person will see rather than in a WHERE clause.
The upstream will change shape without telling you
This is the top row of the ranking above and where most engineering time goes. A producing team adds a column, renames one, widens a type, changes an enum, or starts sending nulls in a field that was never null. None of that is malicious and most is not even wrong. It is invisible to the producer, because nothing in their deploy tells them a downstream table depends on the old shape.
Defend at the boundary with three cheap mechanisms. Never select star into a typed table — write the column list explicitly, so an added column is a no-op and a removed one is a loud error where it happened. Snapshot the source schema and compare it every run — additive changes pass with a log line, while removals, type narrowing and nullability changes fail before a row is written. dbt's on_schema_change and model contracts do this in the warehouse; a schema registry with compatibility rules does it at the producer for streams. Assert the semantics, not only the types — a column still called status and still a string, now carrying four new values, passes every type check and quietly routes rows into the wrong bucket.
The organizational half matters as much as the code. A list of downstream owners kept in the producer's repository has prevented more incidents than any monitoring tool we have deployed.
Poison rows: fail fast or quarantine
A single malformed record should not stop a nightly load. A systematically malformed batch should. The line is whether the problem says the whole input is wrong.
Fail fast when the signal is structural: the schema changed, row count is outside its historical band by more than a set factor, a required key is null across the whole file, the file is a fraction of its usual size, or the partition is already marked complete. Those mean the input is not what you think it is, and processing it produces confidently wrong numbers, worse than none.
Quarantine when the problem is per-row: an unparseable date, a foreign key with no match, a negative quantity where none is possible. Write the raw payload, the error, the run id and the arrival time to a dead-letter table, let the rest of the batch through, and monitor the quarantine rate. If 0.05 percent is the normal background and this morning it is 4 percent, the rate is the alert. Nobody should read individual dead-letter rows at 3am, and nobody should discover three months later that a table has quietly accumulated a million rejects.
Retries that help, and retries that make it worse
Retry is the most misapplied reliability tool in data engineering, because it is one config line and feels free. It is safe only on an idempotent step and useful only on a transient error.
Classify before you retry. Timeouts, connection resets, HTTP 429 and 5xx responses are worth retrying with exponential backoff and jitter. HTTP 400, 401, 403 and any schema or validation error are terminal: retrying them five times at ten-minute intervals does nothing but delay the page by fifty minutes. On rate limits, honor the Retry-After header rather than inventing a backoff, and cap total attempts. A retry loop hammering a partner API at 3am is how a small failure becomes a suspended account.
Delivery semantics deserve the same care. Most streaming stacks give at-least-once delivery, so duplicates are normal rather than exceptional. Kafka's producer has shipped with idempotence and full acknowledgment on by default since 3.0, which removes duplicates from producer retries, but it does not make your consumer's write idempotent. That part is yours: deduplicate on a message key at the sink, or make the sink write a merge.
Send it over and we will tell you what we would change.
Email the job definition for the pipeline that wakes people up most, the write statement it runs, and one week of your alert log to contact@precisionfederal.com. You get back a short written note naming the three things we would change and why. One business day. No charge, no meeting, no deck.
contact@precisionfederal.comTwo runs at once is a whole class of incident
A job that normally takes twenty minutes takes three hours because the source is slow. The next scheduled run fires, and both are writing the same partition. Depending on the engine you get duplicated rows, a deadlock, a corrupt manifest, or a table briefly empty while a dashboard reads it. Among the easiest failures to prevent, and the one we find most often.
Set a concurrency limit on every scheduled job, at the orchestrator and again in the job. Airflow's max_active_runs=1 covers the orchestrator side; below it, take a lease with a Postgres advisory lock or a control-table row carrying an owner, a heartbeat and a time-to-live, so a crashed run's lock expires instead of blocking forever. And set an execution timeout on everything. A hung task with no timeout does not fail, so it does not alert, so it holds its slot until someone notices the dashboard is a day stale.
Alert on what a person would notice
Task-level alerting is the default in every orchestrator and it is the wrong instrument. "Task load_orders_hourly failed" says nothing about who is affected, whether the next run fixes it, or what to do. Teams that alert this way get a channel full of self-resolving failures, and the on-call correctly learns to ignore it. Then the one that mattered scrolls past at 3am. Alert on outcomes instead. Four signals cover most of it.
Freshness. The maximum event timestamp has not advanced past its service level. The most valuable check you can build, because it catches every upstream cause at once: job failed, job never started, source empty, credential expired, file never landed.
Volume. Row count outside a band derived from the same weekday over recent weeks, not a fixed number. Sunday traffic is not Tuesday traffic, and fixed thresholds generate weekend noise until someone mutes the alert.
Distribution. Null rate on the fields your logic branches on, distinct count of the join key, and the sum of the one numeric column a business owner would recognize. A currency mix-up or a truncated join shows up here and nowhere else.
Cost. Bytes scanned or compute-minutes per run against a ceiling. A retry loop or accidental cross join shows up in the bill within one night.
Alert Quality — Score Your Current Set Out of 100
A muted alert is a decision nobody wrote down. Delete it or fix it.
Deciding what does not page
The highest-value reliability work on most teams is deleting pages, not adding monitoring. An alert earns the right to wake a person only when both are true: something a business owner would notice will be wrong before the next business hour, and an action available now changes it. Bad data reaching a customer-facing surface qualifies, and should also trip a circuit breaker that stops publication.
| Signal | What it usually means | Route | First action |
|---|---|---|---|
| Freshness SLO missed, consumer reads at 8am | Upstream late or job did not start | Ticket, escalate at 06:00 | Check source arrival, then re-run the interval |
| Freshness SLO missed, consumer is a live product surface | Users are seeing stale values now | Page | Serve last-known-good, then re-run |
| Row count outside band by more than 3x | Partial export or a duplicated load | Page | Halt downstream, inspect the partition, re-run |
| Quarantine rate spike | Upstream format or semantic change | Ticket | Sample the dead-letter table, notify the producer |
| Run cost above ceiling | Retry loop, skewed join, or a volume shift | Ticket | Kill the run, read the query plan in the morning |
The runbook is part of the pipeline
Every page links to a runbook short enough to read on a phone. Five things belong in it: what the alert means in one sentence, the query that confirms it is real, the exact command to fix it with parameters spelled out, the blast radius naming which downstream tables and dashboards are stale, and who to wake if it is still broken in thirty minutes.
Write it when you build the alert, not after the first incident. Whoever builds it already knows the answer, and thirty minutes of their time replaces an hour of someone else's confusion at 3am, repeatedly, for years. A runbook that says "investigate the root cause" is worse than none, because it implies coverage that does not exist.
Make it runnable on a laptop
A pipeline that can only be exercised in production will not be tested before a Friday deploy. Nobody is being lazy; the friction is higher than the perceived risk, until the day it is not. Target a full end-to-end run against a small fixture, on a developer machine, in under two minutes. That means transforms written as pure functions of their inputs, fixtures encoding the ugly cases you have actually seen (a null join key, a duplicate business key, a row three days late, a new column, a numeric field delivered as a string), and contract tests against each source, scheduled rather than only in CI. Every incident adds a fixture, which is how a test suite becomes a record of what has bitten you.
- Re-run the last successful interval twice and diff the output. Identical, or not idempotent.
- Kill the job mid-write and re-run. The table should be correct, not merged with a partial.
- Run two instances concurrently on purpose. One should refuse to start.
- Change the schema of an input file and confirm it fails before writing a row.
- Feed it a record dated four days ago and confirm you can find that record afterward.
- Revoke the source credential and confirm the alert names it, not a stack trace.
- Hand the runbook to an engineer who did not build the pipeline and watch them fix it.
Patterns that guarantee a 3am page
- Append-only writes with no key and no partition scope, so no failure can be resolved by re-running.
- Wall-clock filters like
now() - interval '1 day'instead of a passed logical interval. - A backfill path that is a different script from the scheduled path.
- Alerting on task failure rather than data freshness and correctness.
- No execution timeout, so a hung task never fails and never alerts.
- Retrying terminal errors, which converts an immediate page into a delayed one.
- Dropping late-arriving rows in a
WHEREclause with no record that they existed.
The staffing part nobody costs
Reliability is a rotation, not a feature. A pipeline supporting a business that needs numbers by 8am carries a real on-call obligation, and a rotation with fewer than four people produces resignations. Two is not a rotation, it is two people always on call. Before committing to an overnight service level, cost it honestly: who is in it, and what happens during vacation.
The alternative is reducing what can page, which makes every design choice above a staffing decision. Idempotent writes turn the 3am action into a command anyone can run, runbook-linked freshness alerts mean the responder need not have written the code, and rolling reprocessing windows let a missed night self-heal.
A thirty-day hardening pass
When we are brought into a platform that pages too often, this is the sequence, ordered by value per unit of effort. The first week usually removes most of the night pages on its own.
Hardening Sequence
If thirty days is not available, the order below is how we would spend one week, ranked by night pages removed per unit of engineering.
Where the Hours Pay Back Fastest
Ranked by overnight pain removed per week of engineering.
Bottom line
Design for the failure you will have, not the one you hope to avoid. Make every write re-runnable, take the interval as a parameter, build backfill on the same code path, choose a lateness window from measured data, check schemas at the boundary, quarantine bad rows and alert on the rate, cap concurrency, time everything out, and page only when a person can act. Do that and 3am becomes a log line somebody reads over coffee.
Frequently asked questions
Running it twice on the same logical interval produces the same table as running it once. In practice the write replaces a partition whole or merges on a stable business key, and the query contains no wall-clock filters, generated identifiers, or non-deterministic ordering. Verify rather than assume: re-run a completed interval twice and diff.
Only if something a business owner would notice will be wrong before the next business hour, and only if an action available now changes that. A job that retries successfully in twenty minutes is not a page. A dashboard read at 8am is a ticket with a deadline. Bad data reaching a live product surface is a page, and should stop publication automatically.
Measure the gap between event time and arrival time for a month, take the 99th percentile, then either reprocess a rolling window slightly wider than that every night or close each day and publish dated restatements. The failure mode is having no policy, where late rows are dropped by a filter and the loss surfaces as slow divergence from the source system.
Two things, in order. Add concurrency limits and execution timeouts to every scheduled job, usually an afternoon of work, which removes a whole class of overlapping-run and hung-task incidents. Then convert the highest-impact tables to idempotent writes, which turns every remaining failure into a re-run instead of an investigation.
Four people is the practical floor for sustainable overnight coverage once vacation and illness are accounted for. If that is not available, reduce what can page rather than stretching two people across a year. Idempotent writes, runbook-linked alerts and self-healing reprocessing windows are what make a small rotation humane.