> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.
# Orderbook Snapshots
> Point-in-time snapshots of the Hyperliquid order book.
The `hyperliquid.raw.orderbook_snapshots` table contains point-in-time snapshots of every resting order on the Hyperliquid order book. New snapshots arrive approximately every 15 minutes; each snapshot is one row per resting order at that moment. The same order will appear in successive snapshots until it is filled or canceled, so you can observe how an individual order's size, trigger conditions, or status evolve over time.
## Table Columns
| Column Name | Description |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| snapshot\_id | UUID identifying a single snapshot. All rows from the same snapshot share this value. Use it to scope a query to one complete book at one point in time. |
| snapshot\_timestamp | UTC timestamp of the snapshot. Constant across all rows of the same `snapshot_id`. |
| max\_order\_timestamp | The latest `order_timestamp` represented in the snapshot. Useful as a "what's new" cutoff when combining the snapshot with later orders. Identical for every row in a snapshot. |
| user | The address of the user who placed the order. |
| coin | A unique identifier for the asset being traded:
• The coin for perpetuals is the standard token symbol, e.g. HYPE
• The coin for spot tokens is an ID representing a pair of tokens based on Hyperliquid's metadata, e.g. `@4` represents token 5/token 0, which corresponds to JEFF/USDC. The metadata is available from the info endpoint of Hyperliquid's API. |
| side | B - Buy ・ A - Ask (Sell) |
| limit\_price | The maximum price the buyer or seller is willing to pay. |
| size | The current size of the order. If the order has been partially filled since it was placed, this is the size of the unfilled remainder. |
| original\_size | The size of the order when it was first placed. If the order has not been partially filled, `size` equals `original_size`. |
| order\_id | Order ID generated by Hyperliquid (also referred to as `oid`). Note: `order_id` is **not** unique on its own — Hyperliquid reuses the oid space across markets. Use `(snapshot_id, coin, order_timestamp, order_id)` to identify a unique row. |
| order\_timestamp | The time the order was originally placed on Hyperliquid. Unlike `snapshot_timestamp`, this can be hours or days before the snapshot. |
| order\_type | The type of order. Refer to the [Hyperliquid Docs](https://hyperliquid.gitbook.io/hyperliquid-docs/trading/order-types#order-types). |
| time\_in\_force | Behaviour for the order. Refer to the [Hyperliquid Docs](https://hyperliquid.gitbook.io/hyperliquid-docs/for-developers/api/exchange-endpoint#place-an-order). |
| trigger\_condition | What will make this order become active and go into the order book. For example `Price below 1560.1`. The value can also be `Triggered`, meaning the trigger condition has already been met. |
| is\_trigger | Boolean. Whether this order has a trigger condition (see `trigger_condition`). |
| trigger\_price | The price at which the trigger fires, if `is_trigger` is true. |
| is\_position\_tpsl | Boolean. Whether this order is a take profit or stop loss order. |
| is\_reduce\_only | Boolean. Whether this order reduces the current position, instead of opening a new order in the opposite direction. Refer to the [Hyperliquid Docs](https://hyperliquid.gitbook.io/hyperliquid-docs/trading/order-types#order-options). |
| client\_order\_id | Custom order ID provided by the user (also referred to as `cloid`). Nullable. |
| children | JSON variant. Will contain one or two orders that will be added once the parent order is triggered (typically a take profit and/or stop loss). |
## Notes
### Identifying a unique row
Because the same order persists across snapshots and Hyperliquid reuses the oid space across markets, the minimum unique grain is:
```
(snapshot_id, coin, order_timestamp, order_id)
```
`order_id` alone is **not** unique — the same numeric oid may appear for different orders on different coins.
### Snapshot grouping
All rows that share a `snapshot_id` belong to the same book at one moment. To get the most recent complete book for a coin:
```sql theme={null}
WITH latest AS (
SELECT snapshot_id
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
QUALIFY ROW_NUMBER() OVER (ORDER BY snapshot_timestamp DESC) = 1
)
SELECT side, limit_price, size, user, order_timestamp
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
AND snapshot_id = (SELECT snapshot_id FROM latest)
ORDER BY side, limit_price::float DESC;
```
### Tracking a single order over time
Because the same order appears in multiple snapshots until it is filled or canceled, you can observe its evolution:
```sql theme={null}
SELECT
snapshot_timestamp,
size,
limit_price,
trigger_condition
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
AND user = '0x...'
AND order_id = '414287276291'
ORDER BY snapshot_timestamp;
```
### Freshness via `max_order_timestamp`
`max_order_timestamp` is the latest order placement time represented in a snapshot. The gap between `snapshot_timestamp` and `max_order_timestamp` is useful for detecting stale or thinly-populated books:
```sql theme={null}
SELECT
snapshot_id,
snapshot_timestamp,
max_order_timestamp,
DATEDIFF('second', max_order_timestamp, snapshot_timestamp) AS lag_seconds
FROM hyperliquid.raw.orderbook_snapshots
WHERE snapshot_timestamp > DATEADD(hour, -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY 2 DESC;
```
### Reconstructing a fresher book from `hyperliquid.raw.orders` and `hyperliquid.raw.fills`
New snapshots arrive approximately every 15 minutes, so the latest one can be up to \~15 minutes behind real time. To bring it forward, combine three sources:
| What changed since the snapshot | Source table | How to apply |
| ------------------------------------------------------------------------------------- | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Orders **removed** from the book (filled, canceled, liquidated, rejected, etc.) | `hyperliquid.raw.orders` | Drop the order from the reconstructed book if its latest `status_change_timestamp > snapshot_timestamp` and the resulting status is **terminal**. |
| Orders **added** to the book after the snapshot | `hyperliquid.raw.orders` | Include any order whose latest status is **non-terminal** and whose `order_timestamp > max_order_timestamp`. |
| Orders **partially filled** since the snapshot (still resting, but with reduced size) | `hyperliquid.raw.fills` | For each surviving order, subtract the sum of fill sizes with `timestamp > snapshot_timestamp` from the snapshot's `size`. A partial fill on Hyperliquid does **not** change order status — the order keeps resting with a smaller size, so it would never appear in the `orders` table change stream. |
`max_order_timestamp` is the useful lower bound for the "what's new" filter: any order placed at or before it is already represented in the snapshot, so the `orders` lookup only needs to consider placements after that point.
#### Terminal vs non-terminal statuses
`hyperliquid.raw.orders` records every status change for every order. To decide whether an order is still resting, take its **latest** `status` (by `status_change_timestamp`) and check whether that status is terminal. Refer to the [Hyperliquid order-status reference](https://hyperliquid.gitbook.io/hyperliquid-docs/for-developers/api/info-endpoint#query-order-status-by-oid-or-cloid) for the full taxonomy; broadly:
* **Terminal** (order leaves the book): `Filled`, `Canceled`, `MarginCanceled`, `Liquidated`, `SelfTradeCanceled`, `ReduceOnlyCanceled`, `VaultWithdrawalCanceled`, `OpenInterestCapCanceled`, `DelistedCanceled`, `SiblingFilledCanceled`, `ScheduledCancel`, and the various `*Rejected` statuses.
* **Non-terminal** (order still resting): `Open`, `Triggered`, and trigger-condition modifications.
#### SQL sketch
```sql theme={null}
-- Reconstruct BTC's book as of "now" from the latest snapshot,
-- removing orders that have since terminated, adjusting size for
-- partial fills, and adding orders placed after the snapshot.
WITH latest AS (
SELECT snapshot_id, snapshot_timestamp, max_order_timestamp
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
QUALIFY ROW_NUMBER() OVER (ORDER BY snapshot_timestamp DESC) = 1
),
-- User-maintained list — keep current with Hyperliquid's status taxonomy.
terminal_statuses(status) AS (
SELECT column1 FROM VALUES
('Filled'), ('Canceled'), ('MarginCanceled'), ('Liquidated'),
('SelfTradeCanceled'), ('ReduceOnlyCanceled'),
('VaultWithdrawalCanceled'), ('OpenInterestCapCanceled'),
('DelistedCanceled'), ('SiblingFilledCanceled'), ('ScheduledCancel'),
('Rejected'), ('TickRejected'), ('MinTradeNtlRejected'),
('PerpMarginRejected'), ('ReduceOnlyRejected'),
('BadAloPxRejected'), ('IocCancelRejected'),
('BadTriggerPxRejected'), ('MarketOrderNoLiquidityRejected')
),
-- Latest status per order since the snapshot.
order_status_since AS (
SELECT o.user, o.order_id, o.order_timestamp,
o.side, o.limit_price, o.size, o.status
FROM hyperliquid.raw.orders o, latest
WHERE o.coin = 'BTC'
AND o.status_change_timestamp > latest.snapshot_timestamp
QUALIFY ROW_NUMBER() OVER (
PARTITION BY o.user, o.order_id, o.order_timestamp
ORDER BY o.status_change_timestamp DESC
) = 1
),
-- Snapshot rows whose orders are still resting (didn't go terminal).
snapshot_survivors AS (
SELECT obs.user, obs.order_id, obs.order_timestamp,
obs.side, obs.limit_price, obs.size
FROM hyperliquid.raw.orderbook_snapshots obs, latest
WHERE obs.coin = 'BTC' AND obs.snapshot_id = latest.snapshot_id
AND NOT EXISTS (
SELECT 1 FROM order_status_since s
WHERE s.user = obs.user
AND s.order_id = obs.order_id
AND s.order_timestamp = obs.order_timestamp
AND s.status IN (SELECT status FROM terminal_statuses)
)
),
-- Net fill size against each surviving order since the snapshot.
fills_since AS (
SELECT user, order_id, SUM(size::number(38,10)) AS filled_size
FROM hyperliquid.raw.fills, latest
WHERE coin = 'BTC' AND timestamp > latest.snapshot_timestamp
GROUP BY user, order_id
),
-- Snapshot-side resting orders, with size adjusted for partial fills.
adjusted_snapshot AS (
SELECT s.user, s.order_id, s.order_timestamp, s.side, s.limit_price,
GREATEST(s.size::number(38,10) - COALESCE(f.filled_size, 0), 0) AS size
FROM snapshot_survivors s
LEFT JOIN fills_since f USING (user, order_id)
),
-- Orders that appeared in the book after the snapshot.
new_orders AS (
SELECT s.user, s.order_id, s.order_timestamp,
s.side, s.limit_price, s.size
FROM order_status_since s, latest
WHERE s.order_timestamp > latest.max_order_timestamp
AND s.status NOT IN (SELECT status FROM terminal_statuses)
)
SELECT * FROM adjusted_snapshot WHERE size > 0
UNION ALL
SELECT * FROM new_orders;
```
The `terminal_statuses` list above is illustrative — keep it in lockstep with the [Hyperliquid order-status reference](https://hyperliquid.gitbook.io/hyperliquid-docs/for-developers/api/info-endpoint#query-order-status-by-oid-or-cloid). Missing a status will keep terminated orders in the reconstructed book; over-listing one will drop orders that should still rest.
### Query tips
* For efficient date-range queries, filter on `snapshot_timestamp::date`.