Switching from v3 to v3.1

v3.1 covers 27 October 2026 onward. The rebuilt history from 31 August (13:00 UTC) to 26 October is being published. v3 downloads switch off around 5 November 2026, except the hours listed on the notice.

Words used on this page.

• unit: an hour's rows of one category, or of one busy sub_tag split out of it. Each unit is a lean file and a provenance file, or several such pairs (parts).
• lean file: the event columns and each row's receive time. provenance file: each row's sequence and which of our recorders heard it, joined to the lean file on row_ordinal.
• sub_tag: a market's label below its category, from the hour's category map.
• twin: the other token's mirror-image row, which v3.1 does not store and you can rebuild.
• recorder: one of our machines that capture the feed. source_witness is the recorder whose copy of the row we kept.
• arrival_skew: how receive times differ between recorders (it mixes the network path with clock error).
• spread_differs: true where the venue's spread differs from the rule below; the venue's value is then in spread_raw.
• canonical_token_map.object: the manifest's path to the token map, which gives each market's canonical and complement token.
• ab_accounting: a block of the manifest with counts per event type, among them boundary_dropped (below).

You need DuckDB 1.5.5 or later. The first query installs the httpfs extension.

Start every hour from its manifest. https://dl.pendulumflow.com/v3.1/<YYYY-MM-DD>/<HH>/<hour>.manifest.json lists every file of the hour with its sha256 (<hour> is YYYY-MM-DDTHH, e.g. 2026-10-29T18). Download the manifest first, then exactly the files it lists. Never glob or guess file names. Each category is one lean file and one .provenance.parquet. A busy sub_tag can have its own pair, and a large unit is split into parts named .p1 to .p<N>: then there is no file without a part number, so read every part. Busy hours are 1.5–2 GB in v3 and 0.8–1.1 GB in v3.1: download them rather than range-reading. On HTTP 429 or 503, wait 30–60 seconds and try again.

INSTALL httpfs; LOAD httpfs;
SET http_retries = 8; SET http_retry_wait_ms = 2000; SET http_retry_backoff = 2;

To turn the manifest into the lists of lean and provenance files (each entry of its files list has file, role, category, sub_tag, part and sha256):

SET VARIABLE day = '2026-10-29';
SET VARIABLE hh  = '18';
SET VARIABLE h    = getvariable('day') || 'T' || getvariable('hh');
SET VARIABLE v31  = 'https://dl.pendulumflow.com/v3.1/' || getvariable('day') || '/' || getvariable('hh') || '/';
CREATE OR REPLACE TABLE manifest AS
  SELECT * FROM read_json(getvariable('v31') || getvariable('h') || '.manifest.json', maximum_object_size = 268435456);
CREATE OR REPLACE TABLE files AS SELECT unnest(files, recursive := true) FROM manifest;
SET VARIABLE lean = (SELECT list(getvariable('v31') || file ORDER BY file) FROM files WHERE role = 'lean');
SET VARIABLE prov = (SELECT list(getvariable('v31') || file ORDER BY file) FROM files WHERE role = 'provenance');

Which file holds my market? The manifest names the hour's category map by category_map_sha256; the file is https://dl.pendulumflow.com/v3.1/maps/category-map-<first 8 characters of that sha256>.parquet, with the columns market, category and sub_tag.

Market and token ids are binary. The token id is the column asset_id. Compare them with unhex(): WHERE market = unhex('B90BCEE8…'). A plain string such as market = 'B90B…' matches nothing, and DuckDB gives no error.

A typical query, before and after

v3:

SELECT timestamp, timestamp_received, best_bid, best_ask, spread
FROM read_parquet('2026-10-29T18.parquet')
WHERE market = unhex('<market id in hex>') AND event_type IN ('best_bid_ask', 'price_change')
ORDER BY timestamp, sequence;

v3.1. Here the market's category is crypto and the unit is one file. Replace crypto with your market's category from the map, or the query returns no rows. With parts, list every .p<k> file and its .provenance file, or use the lists from the manifest above: read_parquet(getvariable('lean')) and read_parquet(getvariable('prov')) in place of the two file lists (they hold every category of the hour, so keep the market filter):

SELECT l.timestamp,
       l.timestamp + to_milliseconds(l.recv_lag_ms) AS timestamp_received,
       l.best_bid, l.best_ask,
       CASE WHEN p.spread_differs THEN p.spread_raw
            WHEN l.event_type = 'best_bid_ask' THEN l.best_ask - l.best_bid END AS spread
FROM read_parquet(['2026-10-29T18.crypto.parquet']) AS l
JOIN read_parquet(['2026-10-29T18.crypto.provenance.parquet']) AS p USING (row_ordinal)
WHERE l.market = unhex('<market id in hex>') AND l.event_type IN ('best_bid_ask', 'price_change')
ORDER BY l.asset_id, l.timestamp, p.sequence, l.row_ordinal;

Skip the provenance join unless you need the exact per-token order (sequence), the recorder columns, spread or the twin columns. Each message is stored once, whichever recorders heard it, and the join never adds rows. row_ordinal is unique across the whole hour, so the join is safe across parts and categories.

The other token of each market

In a two-outcome market, v3.1 stores one token's price_change, best_bid_ask and book rows. It drops the other token's row when it is an exact mirror of the first. The stored row then has mirror_dropped = true. Rebuild a row for each stored row with mirror_dropped = true; there is no twin for any other row. Trades (last_trade_price) and all other event types are never mirrored. To rebuild the other token's row from a stored one, using twin_seq_delta, twin_recv_delta_ns and twin_arrival_skew from the provenance file:

• token: complement_token from the token map named by the manifest's canonical_token_map.object (under v3.1/maps/, one file per day, about 500 MB: download the day's map once and reuse it);
• same timestamp; sequence = sequence + twin_seq_delta (signed);
• receive time = the stored row's timestamp + recv_lag_ms plus twin_recv_delta_ns (nanoseconds, always a whole number of milliseconds); arrival_skew = twin_arrival_skew;
• price_change: price = 1 − price, same size, the opposite side; best_bid = 1 − best_ask, best_ask = 1 − best_bid;
• best_bid_ask: best_bid = 1 − best_ask, best_ask = 1 − best_bid;
• book: bids = the stored asks at 1 − price, with the same sizes, in the same order (do not reverse); asks = the stored bids the same way.
• every other column is the stored row's, including market, source_witness, witness_set, transaction_hash, fee_rate_bps and row_ordinal. A rebuilt row shares its stored row's row_ordinal, so row_ordinal together with whether the row is rebuilt is unique.

Check your rebuild against the hour's twin_check.parquet (counts and fingerprints; its columns and the twin-check-v1 fingerprint are on the v3.1 format page and in quickstart.py), and the count of rebuilt rows against the sum of the manifest's top-level mirror_rows_dropped (one count per event type). Repeated identical books are dropped on purpose and cannot be rebuilt. A dropped repeat book was identical to the book before it, so the previous book still applies. Counts of book rows and update rates change; the manifest's top-level book_rows_dropped (one number) says by how much. Rows the previous hour already had are counted in ab_accounting.<event type>.boundary_dropped; they are missing from both archives, so they are not part of the difference between v3 and v3.1.

Columns you will miss

• timestamp_received = timestamp + to_milliseconds(recv_lag_ms): our receive time to the millisecond, rounded down. recv_lag_ms is an int64 (BIGINT in DuckDB), and can be negative when the venue's clock is ahead of ours. v3 had microseconds; v3.1 keeps whole milliseconds by design.
• spread = the CASE above. Not best_ask − best_bid alone, and not COALESCE. spread_differs is never null; on rows other than best_bid_ask the spread is null, as in v3.
• sequence, source_witness, witness_set and arrival_skew are in the provenance file. witness_set lists the recorders that heard the message, each between bars (for example |o|p|i|). arrival_skew is in microseconds but floored to a whole millisecond.

The three likeliest mistakes

1. Guessing file names. Reading *.parquet, or one category file, silently misses split sub_tags and parts. Take the list from the manifest.
2. Treating the venue's timestamp, or opening_state.parquet, as "what was known at hh:00". Both go by the venue's time, and rows received up to about an hour later can be in them. For a start with no lookahead, rebuild from rows with timestamp + recv_lag_ms ≤ hh:00, in that order. The columns of opening_state.parquet are on the v3.1 format page.
3. Expecting both tokens, and the old columns, to be there.
   • The second token's mirrored rows are absent until you rebuild them.
   • Filtering on is_primary_copy = true shows one recorder's view, and can leave a token with no book in the hour. In one quiet hour we measured (during a venue disruption), that held for over half of all tokens.
   • sequence is in the provenance file, so a per-token order without the join is incomplete.