Orderbook

L2 orderbook snapshots for every perpetual and spot market.

20-level L2 orderbook snapshots for every perpetual and spot market on Hyperliquid and all HIP-3 dexes. Each snapshot captures the top 20 bid and ask price levels at the time it was taken — price, total size at the level, and order count. Updated weekly.

Bucket: s3://hydromancer-reservoir (requester pays)

by_dex/{dex}/orderbook/1m/{asset_class}/date=YYYY-MM-DD/{coin}.parquet

One file per coin per day. {dex} is hyperliquid for native perps/spot, or the HIP-3 dex name (xyz, hyna, felix, …). {asset_class} is perps . Files are only created for dates with data.

Coin naming:

• Perps (native): BTC, ETH, SOL, …

• Perps (HIP-3): same as the coin (e.g. NVDA, AAPL, SP500). Routed via the {dex} path segment.

• Spot (native): @{pair_index} (e.g. @107, @142). The legacy PURR/USDC pair has its slash URL-encoded as PURR%2FUSDC.parquet on disk.

Schema

Each row is one complete orderbook snapshot. Bid and ask levels are stored as list columns (best price first), with up to 20 levels per side.

Column	Type	Description
block_time_ms	int64	Block time in milliseconds since epoch (UTC)
block_number	uint64	Hyperliquid block number
bids	list<struct<px:string, sz:string, n:uint32>>	Bid levels, highest price first. px and sz are decimal strings for lossless precision; n is the order count at that level
asks	list<struct<px:string, sz:string, n:uint32>>	Ask levels, lowest price first. Same field semantics as bids

Why list-of-struct? One row equals one complete snapshot, which matches how you'd think about the book. Parquet physically stores bids.list.item.px, bids.list.item.sz, and bids.list.item.n as separate columns under the hood — so column pruning still works. A query that only reads bids[1].px (the best bid over time) only loads that one physical column.

Quick Start

DuckDB

INSTALL httpfs;
LOAD httpfs;
SET s3_region = 'ap-northeast-1';

-- Best bid / best ask / spread / mid over the day
SELECT
    block_time_ms,
    CAST(bids[1].px AS DOUBLE) AS best_bid,
    CAST(asks[1].px AS DOUBLE) AS best_ask,
    CAST(asks[1].px AS DOUBLE) - CAST(bids[1].px AS DOUBLE) AS spread,
    (CAST(bids[1].px AS DOUBLE) + CAST(asks[1].px AS DOUBLE)) / 2 AS mid
FROM read_parquet('s3://hydromancer-reservoir/by_dex/hyperliquid/orderbook/1m/perps/date=2026-04-06/BTC.parquet')
ORDER BY block_time_ms;

-- Depth within 10 bps of mid (both sides, USD)
WITH snap AS (
    SELECT
        block_time_ms,
        (CAST(bids[1].px AS DOUBLE) + CAST(asks[1].px AS DOUBLE)) / 2 AS mid,
        bids, asks
    FROM read_parquet('s3://hydromancer-reservoir/by_dex/hyperliquid/orderbook/1m/perps/date=2026-04-06/BTC.parquet')
)
SELECT
    block_time_ms, mid,
    list_sum(list_transform(bids, b ->
        CASE WHEN CAST(b.px AS DOUBLE) >= mid * 0.999
             THEN CAST(b.sz AS DOUBLE) * CAST(b.px AS DOUBLE) ELSE 0 END
    )) AS bid_10bps_usd,
    list_sum(list_transform(asks, a ->
        CASE WHEN CAST(a.px AS DOUBLE) <= mid * 1.001
             THEN CAST(a.sz AS DOUBLE) * CAST(a.px AS DOUBLE) ELSE 0 END
    )) AS ask_10bps_usd
FROM snap
ORDER BY block_time_ms;

-- Multi-coin scan: average spread across the top native perps on a day
SELECT
    regexp_extract(filename, '/([^/]+)\.parquet$', 1) AS coin,
    AVG(CAST(asks[1].px AS DOUBLE) - CAST(bids[1].px AS DOUBLE)) AS avg_spread,
    AVG((CAST(bids[1].px AS DOUBLE) + CAST(asks[1].px AS DOUBLE)) / 2) AS avg_mid
FROM read_parquet(
    's3://hydromancer-reservoir/by_dex/hyperliquid/orderbook/1m/perps/date=2026-04-06/*.parquet',
    filename = true
)
GROUP BY coin
ORDER BY avg_mid DESC;

-- Multi-day query for a single coin
SELECT block_time_ms, bids[1].px AS best_bid, asks[1].px AS best_ask
FROM read_parquet('s3://hydromancer-reservoir/by_dex/hyperliquid/orderbook/1m/perps/date=*/BTC.parquet')
WHERE block_time_ms BETWEEN 1775347200000 AND 1775606400000
ORDER BY block_time_ms;

Polars

import polars as pl

# Best bid / best ask / spread
df = pl.scan_parquet(
    "s3://hydromancer-reservoir/by_dex/hyperliquid/orderbook/1m/perps/date=2026-04-06/BTC.parquet"
).select([
    pl.col("block_time_ms"),
    pl.col("bids").list.first().struct.field("px").cast(pl.Float64).alias("best_bid"),
    pl.col("asks").list.first().struct.field("px").cast(pl.Float64).alias("best_ask"),
]).with_columns(
    spread=pl.col("best_ask") - pl.col("best_bid"),
    mid=(pl.col("best_bid") + pl.col("best_ask")) / 2,
).collect()

# Explode to long format (one row per level) if you prefer
long = pl.scan_parquet(
    "s3://hydromancer-reservoir/by_dex/hyperliquid/orderbook/1m/perps/date=2026-04-06/BTC.parquet"
).select([
    pl.col("block_time_ms"),
    pl.col("bids"),
]).explode("bids").unnest("bids").collect()
# columns: block_time_ms, px, sz, n

Notes

• Time ordering. Rows within each file are written in block order. You can iterate without sorting for streaming reconstructions.

• Missing levels. If a side of the book has fewer than 20 populated price levels, the list simply contains fewer elements — no padding with zero/null entries.

• Dex prefixes. The path's {dex} segment is the authoritative dex identifier. HIP-3 coin names do not include the dex: prefix here — e.g. a Trade[XYZ] file for NVDA lives at by_dex/xyz/orderbook/1m/perps/date=…/NVDA.parquet.

• Scale. For context, a busy day for 602 markets produces ~866K rows total per day across all coins in the 1m cadence, roughly ~90 MB compressed.