lighter_robinhood.dex.order_books table contains flattened order book updates with bid and ask levels.
Table Columns
| Column Name | Data Type | Description |
|---|---|---|
timestamp | TIMESTAMP_NTZ(9) | UTC timestamp of the order book update. |
market_id | NUMBER(38,0) | Lighter market ID. |
symbol | VARCHAR | Market symbol (e.g., BTC, ETH/USDG). |
market_type | VARCHAR | ’perpetuals’ or ‘spot’. |
base_symbol | VARCHAR | Base asset symbol. |
quote_symbol | VARCHAR | Quote asset symbol. |
collateral_symbol | VARCHAR | Collateral asset symbol for perpetuals. NULL for spots. |
base_l1_address | VARCHAR | L1 contract address of the base asset, when applicable. |
side | VARCHAR | ’ask’ or ‘bid’. |
level | NUMBER(38,0) | 0-based index within the ask or bid array. |
price | FLOAT | Price at this level in quote asset units. |
size | FLOAT | Size at this level in base asset units. |
usd_amount | FLOAT | Dollar value at this level. |
nonce | NUMBER(38,0) | Global sequence number at update time. |
begin_nonce | NUMBER(38,0) | Sequence number at the start of the batch. |
book_offset | NUMBER(38,0) | Per-market incrementing version counter. |
_created_at | TIMESTAMP_NTZ(9) | Timestamp when the row was first recorded. |
_updated_at | TIMESTAMP_NTZ(9) | Timestamp when the row was last updated. |
Sample Query
SELECT
timestamp,
symbol,
side,
level,
price,
size
FROM lighter_robinhood.dex.order_books
WHERE timestamp >= CURRENT_DATE
AND symbol = 'BTC'
ORDER BY timestamp DESC, side, level
LIMIT 100;