Monthly deltas

The History snapshot holds every table whole. Each month a delta takes last month's snapshot to the new one, so a local copy stays current without downloading the whole snapshot again. The delta for December 2026 lives in deltas/2026-12/ and takes the published history/2026-11/ to the published history/2026-12/.

What a delta holds

Path	What it is
`manifest.json`	The parent and next snapshots it joins, each named by its manifest's sha256; the next snapshot's `venues` and `omitted_venues`; and one entry per changed file with its `op`, its `source` and the source's `sha256`
`CHECKSUMS.sha256`	The sha256 of every file of the delta
`snapshot/<path>`	A file of the next snapshot, whole: a new or replaced month of fills or candles, a text file, and the next snapshot's `manifest.json` and `CHECKSUMS.sha256`
`changes/<table>.parquet`	The rows of `markets`, `resolutions`, `matched_events` or `fees` that changed, each with an `op`

Each entry of files in the manifest has one op:

add or replace: copy snapshot/<path> to <path>. Closed months never change in place, so a month file is new or replaced whole.
delete: remove <path>.
change: apply the rows of changes/<path> to the table <path>. Each row has an op column and a row column holding the table's columns. With upsert, the row replaces the row with the same key or is added. With delete, the row holds its key columns only, and the row with that key is removed.

The keys are:

Table	Key
`markets`	`venue`, `market_id`
`resolutions`	`venue`, `market_id`, `seq`
`matched_events`	`event_id`
`fees`	`venue`, `category`, `effective_from`

Applying a delta twice gives the same result. The manifest's schema_break is true when the schema changed between the two snapshots. The reference tables then ship whole, with replace.

A venue the next snapshot omits (omitted_venues) leaves through the delta: its month files are deleted and its rows are deletes by key.

The manifest lists the next snapshot's CHECKSUMS.sha256 and manifest.json last. Copy them last too, so a folder whose manifest is still the parent's is a delta not yet fully applied, and running the apply again finishes it.

Apply a delta with DuckDB

Run this with Python and the duckdb package in the folder that holds both history/2026-11 and deltas/2026-12. It refuses a folder that holds neither of the delta's two snapshots, a delta whose CHECKSUMS.sha256 does not list its manifest.json, any file of the delta whose sha256 is not the one its manifest names, and any path that leaves the folder.

import hashlib
import json
import pathlib
import shutil

import duckdb

KEYS = {
    "markets.parquet": ["venue", "market_id"],
    "resolutions.parquet": ["venue", "market_id", "seq"],
    "matched_events.parquet": ["event_id"],
    "fees.parquet": ["venue", "category", "effective_from"],
}


def sha256(path):
    digest = hashlib.sha256()
    with open(path, "rb") as f:
        for block in iter(lambda: f.read(1 << 20), b""):
            digest.update(block)
    return digest.hexdigest()


def inside(root, rel):
    """root / rel, refusing a path that would leave root."""
    parts = pathlib.PurePosixPath(rel).parts
    if not parts or rel.startswith("/") or ".." in parts:
        raise ValueError(f"refusing the path {rel!r}")
    return root.joinpath(*parts)


def apply_delta(snapshot, delta):
    snapshot, delta = pathlib.Path(snapshot), pathlib.Path(delta)
    raw = (delta / "manifest.json").read_bytes()
    listed = (delta / "CHECKSUMS.sha256").read_text().splitlines()
    if f"{hashlib.sha256(raw).hexdigest()}  manifest.json" not in listed:
        raise ValueError("the delta's CHECKSUMS.sha256 does not list its manifest.json")
    manifest = json.loads(raw)
    parent, nxt = manifest["parent"], manifest["next"]
    if sha256(snapshot / "manifest.json") not in (parent["manifest_sha256"], nxt["manifest_sha256"]):
        raise ValueError(f"{snapshot} holds neither {parent['bundle_id']} nor {nxt['bundle_id']}")
    con = duckdb.connect()
    for f in manifest["files"]:
        target = inside(snapshot, f["path"])
        if f["op"] == "delete":
            target.unlink(missing_ok=True)
            continue
        source = inside(delta, f["source"])
        if sha256(source) != f["sha256"]:
            raise ValueError(f"{source} is not the file the delta's manifest names")
        if f["op"] in ("add", "replace"):
            target.parent.mkdir(parents=True, exist_ok=True)
            shutil.copyfile(source, target)
        elif f["op"] == "change":
            keys = ", ".join(KEYS[f["path"]])
            new = target.with_name(target.name + ".new")
            con.sql(
                f"""
                SELECT * FROM (
                  SELECT base.* FROM read_parquet($target) AS base
                  ANTI JOIN (SELECT row.* FROM read_parquet($changes)) AS ch USING ({keys})
                  UNION ALL BY NAME
                  SELECT row.* FROM read_parquet($changes) WHERE op = 'upsert'
                ) ORDER BY {keys}
                """,
                params={"target": str(target), "changes": str(source)},
            ).write_parquet(str(new))
            new.replace(target)
        else:
            raise ValueError(f"unknown op {f['op']!r} for {f['path']}")


apply_delta("history/2026-11", "deltas/2026-12")

The folder history/2026-11 then holds December's snapshot. Its month and text files are byte for byte the published ones. The four reference tables hold the same rows, but DuckDB writes its own Parquet bytes, so those four files do not match CHECKSUMS.sha256. To compare them, compare rows. Its manifest.json is the published one, so next month's delta applies to it in turn.

Monthly deltasCopy Anchor Link

What a delta holdsCopy Anchor Link

Apply a delta with DuckDBCopy Anchor Link

On this page

Monthly deltas

What a delta holds

Apply a delta with DuckDB