---
title: "Fail-safe caches and backfills: never delete history before the new data arrives"
description: "A fail-safe cache is one where a failed refresh leaves the previous data untouched: write with a pure upsert, never delete-then-reinsert, and retry the timeout exception your client library really raises. Backfills fill gaps with IS NULL instead of rewriting decisions, snapshots check themselves on read, and a value from today never answers a question about the past."
date: 2026-10-11
lang: en
canonical: https://goingstellar.ro/methods/fail-safe-caches-and-backfills/
author: Going Stellar
publisher: GOING STELLAR S.R.L.
topic: Engineering / Data pipelines
license: CC BY 4.0
alternate_ro: https://goingstellar.ro/ro/metode/cache-uri-si-backfill-uri-sigure-la-esec/
---

# Fail-safe caches and backfills: never delete history before the new data arrives

*A fail-safe cache is one where a failed refresh leaves the previous data untouched: write with a pure upsert, never delete-then-reinsert, and retry the timeout exception your client library really raises. Backfills fill gaps with IS NULL instead of rewriting decisions, snapshots check themselves on read, and a value from today never answers a question about the past.*

> Engineering · Data pipelines · 2026-10-11 · https://goingstellar.ro/methods/fail-safe-caches-and-backfills/

## Key points

- A refresh that deletes history before the new pull succeeds turns a failed API call into a month of zeros; a pure upsert leaves stale but correct data instead.
- A retry wrapper protects nothing unless it catches the exception the client library actually raises; some Google API clients raise a raw TimeoutError, not RequestException.
- Backfills should filter on IS NULL, because filtering on 'not equal to the target' silently overwrites every decision a person made since the first run.
- A cached snapshot should compare the input it was built from on every read, because invalidation that depends on every caller remembering it will eventually be forgotten.
- A time threshold such as 'newer than the last success' skips every unit that failed once; units that can fail independently need a row each with their own state.

## What a fail-safe cache or backfill is

A fail-safe cache or backfill is one where a failed run leaves the previous data exactly as it was. Never delete history and then re-insert it on the condition that the new pull succeeds; write with a pure upsert (insert, or update when the key already exists), so a pull that fails or times out changes nothing. A backfill should fill gaps (`WHERE column IS NULL`), not rewrite values that a person has already decided.

We keep several derived tables that are rebuilt from external APIs on a schedule: daily advertising cost, cost per product, monthly profit per business line. The useful question is never whether a pull will fail, but what the table looks like after it does.

## Rule 1: pure upsert, and retry the timeout you actually get

The tempting way to refresh a date range is `DELETE FROM cache WHERE day >= :start` followed by an insert of whatever the pull returned. It looks idempotent and it is fail-unsafe. If the pull returns nothing because a credential expired or the API timed out, the delete has already run and the history is gone.

The fail-safe version is an upsert: `INSERT ... ON CONFLICT (key) DO UPDATE`. Rows the pull returns are refreshed; rows it does not return keep their last known value. A failed pull leaves yesterday's numbers, which are stale but correct, and a freshness check can flag them as stale. Deleted numbers cannot be flagged. They just look like zero.

**Retry the exception the library really raises.** Every external call should sit inside a retry with backoff, and the retry must catch the right class. A Google API client library we use raises a raw `TimeoutError` on a slow response, not the `RequestException` from `requests` that a retry wrapper is usually written for. A retry that catches the wrong class reads as handled in review and never fires. Force a timeout in a test, read which class comes out, and catch that class by name.

## Rule 2: a backfill fills gaps, it does not rewrite decisions

A backfill is a script that populates a new column for rows that already exist. The safe filter is `WHERE column IS NULL`: only rows that have no answer yet. The unsafe filter is `WHERE column != target`: every row that is not already at the value the script would choose.

We wrote the unsafe version. The script assigned a category to existing records. On its first run the column was empty everywhere, so both filters selected the same rows and the result was right. But the form in the interface writes a category on every save, with a default. From the second run on, the backfill would have converted every choice a person had made in the meantime into the script's value. We proved it on a copy of production: we set two records by hand to two different categories, ran the backfill again, and both came out with the script's category. The script's own header said it was idempotent and could be run any number of times, which made a second run recommended rather than merely possible.

**Idempotent is not the same as harmless.** An operation can converge to the same state on every run and still destroy information: the decision a person made between two runs. The test that catches a destructive backfill is not "run it twice in a row", which passes, but "a person chose something else, then the script ran again".

## Rule 3: a snapshot must check itself on read

A snapshot is any value derived once and kept for reuse: a cached setting, a precomputed context, a materialised table. A snapshot that waits to be invalidated by the code that changes its source goes out of date on the first code path that forgets to invalidate it.

We hit this in an Android app. An object written to keep every screen on the same colour theme held a themed context built once, when the process started. The theme switch in the menu changed the system setting but never refreshed that object. From the moment a person chose dark mode until the process died, backgrounds were dark and the text was dark too: a contrast of **1.02**:1 on about **25** screens.

The fix makes the snapshot verify itself on every read: compare the mode it was built for with the mode requested now, and rebuild only when they differ. For a data cache the same rule means storing the input next to the cached value and comparing on read, so a forgotten invalidation produces a rebuild, not a wrong answer.

## Rule 4: today's state does not tell you what was true then

A current value cannot answer a question about the past. We built a rule that held orders whose product had no stock, and reported **166** orders waiting for goods that did not exist. The objection came at once: those products were sold out now, but they had stock when the orders were placed. For almost every flagged product, later orders had shipped, in one case **122** hours after the first held order. Stock arrives in batches.

Stock is a number about now; orders are events in the past. Without a stock history, the question that separates the cases is also about events: *did anything with this product ship after this order was placed?* If yes, the goods existed after the order and the hold has another cause. If no, the order is last in the queue. Applied in code, the list went from **166** to **26** orders, each one genuinely the last for its product. When a decision depends on past state, keep a dated snapshot of that state instead of reconstructing it from today.

## The traps that return less instead of failing

**A cap justified by an old measurement.** Two pagination limits in one system each had a comment justifying them. One API refuses page **50** and above, a ceiling of **10,000** rows, and the pages run from newest to oldest, so for the largest store only the most recent days were ever read. Invoice coverage was **0**% between days **12** and **23**, while smaller stores that fit under the ceiling stayed flat at about **90**%: the control group that proved it. The other cap read **30** pages of **200**, with the comment "**6,000** orders, more than any **10**-day window". True when it was written; later one store had **9,966** orders in **10** days and the job lost **3,966** from the tail.

A cap does not raise an error. It returns less, and less looks identical to "that is all there is". When pagination is ordered, what is lost is systematically the tail. We now write any "N pages, N rows, N days is enough" constant with the measured volume beside it and a runtime check that logs when the cap is reached. After the fix, the same job read **28,039** orders where it had read **10,000**.

**A time threshold skips failures forever.** An importer took the file links posted in a chat group that were newer than the last file it had imported successfully. A link that failed once was never retried, because by the next run newer links had succeeded and the threshold had moved past it. Measured after **16** days of scheduled runs, **8** links out of **59** had never arrived. **Four** had never been tried; **four** had failed once, and **three** of those worked on a new attempt. A timestamp records how far you got, not what you did, so it is safe only when processing is all-or-nothing.

The fix is a row per unit with its own state (to fetch, done, failed permanently), and retries read the state, not a date. A validity window stops retrying what is truly dead, here **10** days against the **7**-day life of the transfer service's links, and those links are still reported rather than forgotten.

## Where we were wrong

Every case above started with code that carried a correct-looking justification: a comment explaining the cap, an idempotency claim in a header, a retry wrapper, a component built to prevent exactly the bug it caused. The justification is what stopped the next reader from checking. We now treat a comment that justifies a limit as a prompt to measure again, not as evidence. And when we checked our stock hold for false positives, we counted recent deliveries per product, but those parcels had left while there was stock: the same mistake twice in one day.

## Limits of this method

Fail-safe means stale-but-flagged, not correct. A pure upsert keeps the last good value, so it needs a freshness check that turns red when the value stops moving; without one, an upsert cache hides an outage longer than a delete would.

## Checklist

**1. Upsert.** No `DELETE` before a pull; deletions are a separate step after the pull is checked.

**2. Retry.** Force a timeout in a test and catch the exception class that really comes out.

**3. Backfill.** Filter on `IS NULL`, and test "a person chose something else, then the script ran again".

**4. Snapshot.** Store the input next to the derived value and compare on every read.

**5. Past questions.** Answer them with events or dated snapshots, never with today's value.

**6. Caps.** Write the measured volume beside every limit and log when it is reached.

**7. Units.** Give every item that can fail on its own a row with its own state.

## Questions and answers

### Why is delete-then-insert dangerous for a cache table?

Delete-then-insert removes the old rows before the new data has arrived. If the external API times out or a credential has expired, the pull returns nothing and the history is already gone, so reports show zeros instead of an error. A pure upsert (INSERT ... ON CONFLICT DO UPDATE) refreshes only the rows the pull returns and leaves every other row at its last known value, which a freshness check can flag as stale.

### What is the safe filter for a backfill script?

A backfill should select rows with no value yet, using WHERE column IS NULL. Filtering on rows whose value differs from the target looks equivalent on the first run, when the column is empty, but on every later run it overwrites the choices people made in the interface. Test a backfill with the scenario 'a person set a different value, then the script ran again', not just by running it twice.

### Is an idempotent script always safe to run again?

No. Idempotent means the script converges to the same state on every run; it does not mean the script preserves information. A backfill that resets every row to its own computed value is idempotent and still destroys the decisions people made between runs. Safety requires that the script only touches rows that have no answer yet, and that it reports, rather than deletes, rows it now considers wrong.

### How do I check whether a pagination limit is silently losing data?

Write the measured volume next to the limit and log at runtime whenever the limit is reached, because a cap returns fewer rows without raising an error. Compare what the tool reads with what your own database holds for the same window, or keep a control group small enough to fit under the cap. With newest-first ordering, the rows lost are always the oldest ones in the window.

### Why should each item in an import have its own status row?

An import that remembers only the time of its last success skips any item that failed once, because the threshold moves past it as soon as newer items succeed. Giving each item a row with a state such as to fetch, done or failed permanently lets retries read the state instead of a date. A validity window then stops retries on items that are truly dead, and those are still reported.

## How to cite

**Cite this as:** Going Stellar (2026). *Fail-safe caches and backfills: never delete history before the new data arrives*. GOING STELLAR S.R.L.. https://goingstellar.ro/methods/fail-safe-caches-and-backfills/

```bibtex
@misc{goingstellar2026fail,
  title  = {Fail-safe caches and backfills: never delete history before the new data arrives},
  author = {{Going Stellar}},
  year   = {2026},
  url    = {https://goingstellar.ro/methods/fail-safe-caches-and-backfills/},
  note   = {2026-10-11}
}
```

Licence. Free to quote with attribution.

## Also available

- This page as HTML: https://goingstellar.ro/methods/fail-safe-caches-and-backfills/
- Same article in Romanian: https://goingstellar.ro/ro/metode/cache-uri-si-backfill-uri-sigure-la-esec/