11 min read

Diffing Two Crawl Snapshots with DuckDB

Two crawl snapshots are two Parquet files, and almost every question worth asking about a crawl is a question about the difference between them: which pages appeared, which disappeared, which changed status, which got slower. That is a join, and it does not need a warehouse. This page is part of Storing & Versioning Crawl Artifacts in Cloud Storage and covers running the diff with DuckDB, in the same job that wrote the snapshot.

Four things a snapshot diff finds Four categories from diffing a 96 000 URL snapshot against the previous one. Ninety-one thousand two hundred URLs are present in both and unchanged. Three thousand one hundred are present in both and changed in at least one scored field. Twelve hundred appeared since the last snapshot. Five hundred disappeared. Only the middle two categories need investigating, and they are the two a naive row-count comparison never surfaces. In both, unchanged 91,200 URLs — no scored field moved In both, changed 3,100 URLs — at least one scored field moved Appeared 1,200 URLs — new since the previous snapshot Disappeared 500 URLs — gone since the previous snapshot A row-count comparison shows 96,000 against 95,300 and reads as stable. The seventeen hundred URLs that appeared and disappeared cancel out, and the three thousand that changed are invisible entirely.

Environment isolation and dependency declaration 

set -euo pipefail
export DIFF_CURR="/data/crawl/2026-07-31/pages.parquet"
export DIFF_PREV="/data/crawl/2026-07-30/pages.parquet"
export DIFF_OUT="/data/crawl/2026-07-31/diff.parquet"
export DIFF_LCP_TOLERANCE_MS=50      # below this, not a change
export DIFF_SCORE_TOLERANCE=0.5      # below this, not a change
/opt/audit/.venv/bin/pip install "duckdb==1.0.0"

The two tolerance variables are what separate a usable diff from one that reports every row as changed. Floating-point measurements differ slightly between runs for reasons that have nothing to do with the site, and an exact comparison surfaces all of it.

Four reasons this job suits DuckDB Four stacked properties. It reads Parquet directly from local disk or object storage with no load step, so a diff is a query rather than a pipeline. It is columnar, so a diff over four scored fields never touches the other forty. It runs in-process with no server to operate. And it is one file dependency, which means the diff can run inside the same CI job that produced the snapshot. Reads Parquet in place local paths or s3:// URLs, no load step a diff is a query, not a pipeline Columnar execution four scored fields, not forty-three the scan cost matches the question In-process no server, no cluster, no credentials nothing to operate between runs One dependency a single library in the job runs where the snapshot was written The alternative is loading both snapshots into a warehouse to answer a question about two files, which costs more in orchestration than the query ever costs in compute.

Implementation 

-- /opt/audit/diff/snapshot_diff.sql
-- Diff two crawl snapshots. Joined on final_url, not the requested URL, so a
-- moving redirect target is a redirect finding rather than a page change.
INSTALL httpfs; LOAD httpfs;          -- only needed for s3:// paths

WITH curr AS (SELECT * FROM read_parquet($curr_path)),
     prev AS (SELECT * FROM read_parquet($prev_path)),

 joined AS (
   SELECT
     COALESCE(c.final_url, p.final_url)                    AS url,
     CASE WHEN p.final_url IS NULL THEN 'appeared'
          WHEN c.final_url IS NULL THEN 'disappeared'
          ELSE 'both' END                                  AS presence,
     c.status  AS status_now,   p.status  AS status_before,
     c.lcp_ms  AS lcp_now,      p.lcp_ms  AS lcp_before,
     c.composite AS score_now,  p.composite AS score_before,
     c.canonical_url AS canon_now, p.canonical_url AS canon_before,
     c.indexable AS idx_now,    p.indexable AS idx_before
   FROM curr c FULL OUTER JOIN prev p USING (final_url)
 )

SELECT *,
   -- A change is only a change outside the measurement tolerance.
   (status_now  IS DISTINCT FROM status_before)                        AS status_changed,
   (idx_now     IS DISTINCT FROM idx_before)                           AS indexable_changed,
   (canon_now   IS DISTINCT FROM canon_before)                         AS canonical_changed,
   (abs(COALESCE(lcp_now, 0)   - COALESCE(lcp_before, 0))   > $lcp_tol) AS lcp_changed,
   (abs(COALESCE(score_now, 0) - COALESCE(score_before, 0)) > $score_tol) AS score_changed
FROM joined
WHERE presence <> 'both'
   OR status_now IS DISTINCT FROM status_before
   OR idx_now    IS DISTINCT FROM idx_before
   OR canon_now  IS DISTINCT FROM canon_before
   OR abs(COALESCE(lcp_now, 0)   - COALESCE(lcp_before, 0))   > $lcp_tol
   OR abs(COALESCE(score_now, 0) - COALESCE(score_before, 0)) > $score_tol;
#!/usr/bin/env python3
# /opt/audit/diff/run.py — parameterised, so paths never reach the SQL as text.
from __future__ import annotations
import os
import duckdb

SQL = open("/opt/audit/diff/snapshot_diff.sql").read()

con = duckdb.connect()
con.execute("SET enable_progress_bar = false")
res = con.execute(SQL, {
    "curr_path":  os.environ["DIFF_CURR"],
    "prev_path":  os.environ["DIFF_PREV"],
    "lcp_tol":    float(os.environ.get("DIFF_LCP_TOLERANCE_MS", "50")),
    "score_tol":  float(os.environ.get("DIFF_SCORE_TOLERANCE", "0.5")),
}).arrow()

duckdb.from_arrow(res).write_parquet(os.environ["DIFF_OUT"])
print(con.execute(
    "SELECT presence, count(*) FROM res GROUP BY 1 ORDER BY 2 DESC").fetchall())

The choices that matter:

  • FULL OUTER JOIN USING (final_url). A full outer join is what makes appeared and disappeared visible at all; joining on the requested URL instead would report a change every time a redirect destination moves, which is a redirect finding rather than a page change.
  • IS DISTINCT FROM rather than <>. A null on either side compares as unknown with a plain inequality, so a page that gained or lost a canonical would silently not register as changed.
  • Tolerances applied per column, sized from the measurement. Fifty milliseconds on LCP and half a point on a composite are below anything a human would act on, and above the noise two runs produce on an unchanged page.
  • Parameters bound rather than interpolated. Paths come from the environment and are passed as query parameters, so a path containing a quote cannot become SQL.

Verification and smoke test 

set -euo pipefail
python3 /opt/audit/diff/run.py

# 1. The diff is smaller than either input — if it is not, something is wrong.
python3 -c "
import duckdb, os
d = duckdb.sql(f\"SELECT count(*) FROM read_parquet('{os.environ['DIFF_OUT']}')\").fetchone()[0]
c = duckdb.sql(f\"SELECT count(*) FROM read_parquet('{os.environ['DIFF_CURR']}')\").fetchone()[0]
assert d < c * 0.5, f'diff has {d} rows against {c} crawled — check tolerances and scope'
print(f'PASS: {d} changed rows out of {c}')"

# 2. Diffing a snapshot against itself must produce nothing.
DIFF_PREV="$DIFF_CURR" DIFF_OUT=/tmp/self.parquet python3 /opt/audit/diff/run.py
python3 -c "
import duckdb
n = duckdb.sql(\"SELECT count(*) FROM read_parquet('/tmp/self.parquet')\").fetchone()[0]
assert n == 0, f'self-diff produced {n} rows — a comparison is not stable'
print('PASS: self-diff is empty')"

Expected output is two PASS lines. The self-diff assertion is the cheapest possible correctness test and it catches the two most common defects at once: an exact float comparison, and a null-handling mistake that makes a column compare unequal to itself.

Failure modes

Three ways a diff invents changes Three rows. Comparing snapshots crawled under different scope configurations reports every newly included URL as appeared and every excluded one as disappeared. Comparing on the requested URL rather than the final one reports a change whenever a redirect target moves. And diffing float columns with exact equality reports a change on every row because of representation noise. SYMPTOM ROOT CAUSE FIX Thousands appeared and disappeared between two ordinary daily runs The scope changed the two snapshots were crawled under different boundaries Record scope in the snapshot and refuse to diff across a scope-config change Every redirecting URL reports as changed on every single run Joined on the requested URL so a moving redirect target reads as a page change Join on the final URL and treat the hop itself as a separate finding Every row reports a change in a float column that nobody touched Exact float equality representation noise differs between runs Compare with a tolerance per column, sized from the measurement precision All three produce a diff that is technically correct and operationally useless, because the changes it reports are properties of the comparison rather than of the site.

The diff is nearly as large as the snapshot

Either the tolerances are too tight or the two snapshots were crawled under different scope configurations. Run the self-diff first to eliminate the tolerance explanation, then compare the scope config recorded in each snapshot — which is the argument for recording it in the artifact rather than only in the job that produced it.

Appeared and disappeared counts are both large and roughly equal

A canonicalisation change is moving URLs from one spelling to another, so the same page appears under a new address and disappears under the old one. Normalise the join key the same way in both snapshots, and treat the normalisation change itself as the finding.

Memory grows on very large snapshots

A full outer join materialises both sides. Above roughly ten million rows per snapshot, project the columns you need before joining rather than selecting everything, and set SET memory_limit explicitly so the query spills to disk rather than being killed — the same discipline as bounding crawl worker memory.

FAQ

Why join on the final URL rather than the requested one?

Because the requested URL and the page are different things when a redirect is involved. Joining on the requested URL means that every time a redirect destination changes, the page reads as changed even though its content, status and score are identical — and on an estate with active redirect maintenance that is thousands of false changes per run. Joining on the resolved address compares pages with pages, and the hop itself becomes a separate redirect finding.

Why does a diff need per-column tolerances?

Because floating-point measurements differ slightly between runs for reasons unrelated to the site — measurement precision, rounding, and genuine sub-threshold variation. An exact comparison reports every row as changed, which makes the diff useless and hides the changes that matter inside noise. A tolerance sized from the measurement precision, such as fifty milliseconds on a paint timing, filters that out while still catching anything a human would act on.

Why is a self-diff the first thing to assert?

Because it is free and it catches the two defects that break a diff most often. Diffing a snapshot against itself must produce zero rows; if it does not, either a float column is being compared exactly, or a nullable column is comparing unequal to itself because a plain inequality treats null comparisons as unknown. Both produce a diff that looks plausible on real data and is entirely artefact, and both are invisible without this check.