Keeping history instead of overwriting rows

Most scrapers start with an UPSERT. You pull a page, parse a row, and write it to a table keyed on some natural identifier: product ID, listing URL, ticker symbol. Run it again tomorrow and the same key gets overwritten with tomorrow’s values. It works, it’s simple, and it quietly throws away the one thing that made the data worth collecting in the first place: what changed, and when.

I’ve run this mistake into production more than once. A price tracker that only stores “current price” can tell you what something costs right now, which you could have gotten from the page itself. What it can’t tell you is that the price dropped 40% for six hours last Tuesday, or that a listing went out of stock and came back with a different SKU. That’s the actual signal. Overwrite-in-place deletes it on every write.

What breaks when you don’t have history

Three things go wrong in practice, and none of them show up until you need the data you didn’t keep.

First, you can’t debug your own pipeline. If a field starts returning null or a number stops matching the source page, an overwrite table shows you the broken state and nothing else. You can’t tell if the site changed its markup, if a proxy pool started getting served a stripped-down page, or if your parser regression happened three deploys ago. Without a timeline, every anomaly looks like it just started existing.

Second, you lose the ability to detect site-side changes as events instead of noise. Sites change their bot-detection posture over time: a new challenge page rolled out to a subset of IP ranges, a field silently removed from the response to break scrapers without breaking the human-facing site, a rate limit tightened. If you’re only storing current state, this shows up as one confusing row. If you’re storing history, it shows up as a change point you can correlate against your own request logs, proxy pool, and response codes from that exact window.

Third, you can’t answer “as of” questions later. Anyone doing price history, availability tracking, ranking changes, or review count trends needs the actual sequence of values, not just the latest one. There’s no way to reconstruct a time series from a table that only ever held one row per key.

The pattern: append-only with valid ranges

The fix is a pattern data warehousing has used for decades, usually called slowly changing dimensions type 2. You stop updating rows in place and start inserting new rows, each one tagged with when it became true and when it stopped being true.

A minimal version needs three extra columns beyond your scraped fields: valid_from, valid_to, and is_current. When you scrape a row and it matches the last known state for that key, you do nothing, or you bump a last_seen timestamp on the current row. When it differs, you close out the old row by setting its valid_to to now, and insert a new row with valid_from set to now and valid_to left null (or far in the future, if your query engine handles nulls awkwardly in range comparisons).

That gives you a table where a single SELECT * WHERE is_current = true reproduces the old overwrite behavior exactly, and a full table scan on a key gives you the entire history. You haven’t lost the simple case, you’ve added the one you were missing.

Deciding what counts as a change

The part that actually takes engineering judgment is deciding what triggers a new history row. Compare every column and you’ll generate a new row on every single scrape, because scraped data is noisy: whitespace differences, a review count that ticks up by one, a timestamp field on the source page itself. That turns your history table into a write amplification problem and makes it useless for finding real changes, because real changes drown in noise.

The practical approach is to hash a defined subset of columns, the ones that represent the actual state you care about, and compare that hash against the current row’s hash. Price, stock status, title, category. Not “last updated” strings from the page, not fields you know are flaky from parsing, not anything with sub-second precision you don’t need. Decide the subset deliberately, write it down somewhere, and revisit it when you add new fields, because it’s the single biggest lever on whether your history table stays useful or turns into landfill.

I also keep a separate raw_hash of the entire parsed payload, computed but not used for triggering new rows. That’s useful later for debugging parser changes, because you can tell “the fields I care about didn’t change, but something in the raw response did” apart from “nothing changed at all.”

Where scraping infrastructure makes this harder

A history table built for scraped data has a failure mode a warehouse table pulling from a stable internal API doesn’t: the scrape itself can be wrong, and a wrong scrape looks exactly like a real change if you’re not careful.

A proxy that got flagged and started returning a challenge page instead of the real page will produce parsed fields that are null, empty, or garbage, depending on how defensively your parser handles unexpected HTML. If your change-detection logic naively treats “field is now null” as a real state transition, you’ll write a history row claiming a product went out of stock when what actually happened is your request got blocked. Do that across a rotating proxy pool and you’ll get spurious history entries correlated with nothing except which exit node served the request.

The defense is to validate a response before it’s allowed to trigger a history write. Check that the page you got back has the shape you expect: right template markers, right response size range, absence of known challenge-page fingerprints. If a scrape fails that check, log it as a failed fetch, retry through the normal pipeline, but don’t let it touch the history table. The history table should record changes in the source data, not changes in your ability to reach it. Those are two different signals and conflating them is the single most common way I’ve seen these tables get polluted.

Storage and pruning

History tables grow, and they grow faster than people expect once you’re tracking a few hundred thousand keys with a real, non-noisy change frequency. Two practical mitigations. Partition the table by month or quarter on valid_from, so old partitions can be moved to cheaper storage or dropped by retention policy without touching a live index. And keep the “current state” query path separate from the “full history” query path, either as a materialized view of is_current = true rows or a small mirror table, so your everyday reads aren’t scanning a table that has years of closed-out rows in it.

If you genuinely don’t need indefinite history, decide a retention window up front, ninety days or a year, and prune past it on a schedule. Keeping everything forever by default because deleting felt risky is how these tables end up ten times larger than the actual current dataset for no analytical benefit.

When overwrite is still fine

None of this means every table needs history. If you’re scraping a value you only ever query as “what is it right now,” and you have no use case for the sequence, an overwrite table is simpler, cheaper, and correct for the job. Reference data that changes rarely and where you don’t care about the change itself, category taxonomies, static metadata, doesn’t need type 2 treatment either.

The decision point is whether the change itself is information. If the answer is yes, if a price drop, a status flip, or a field going missing tells you something you’d act on, build the history table from day one. Retrofitting it later means you’re starting your time series from whenever you noticed the problem, not from whenever the pipeline actually started running, and that gap is usually the exact period someone ends up asking about.

If you’re building out scraping infrastructure and want more on pipeline design, proxy management, and how detection systems actually work from an operator’s side, you can find the rest of what we’ve written here.

Get new guides and videos first — join the Telegram channel.