> For the complete documentation index, see [llms.txt](https://docs.hydromancer.xyz/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.hydromancer.xyz/reservoir/schema-reference/snapshots.md).

# Snapshots

Daily snapshots of all trader positions, spot token balances, and account values. Taken from the on-chain ABCI state at end of day. Available from August 2025 onward.

{% hint style="info" %}
Some dates may be missing if the ABCI state file was not available. This is normal — check the S3 listing for available dates. The gap from late October to mid-December 2025 is a known period without ABCI state captures.
{% endhint %}

## S3 Paths

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

```
global/snapshots/perp/all/date=YYYY-MM-DD/{block}_{timestamp}.parquet
global/snapshots/spot/date=YYYY-MM-DD/{block}_{timestamp}.parquet
global/snapshots/account_values/date=YYYY-MM-DD/{block}_{timestamp}.parquet

by_dex/{dex}/snapshots/perp/date=YYYY-MM-DD/{block}_{timestamp}.parquet
```

File names include the block number and timestamp (milliseconds) from the ABCI state, e.g., `926030000_1773706027926.parquet`.

## Perp Snapshot Schema

One row per user per market. Contains every open perpetual position.

| Column              | Type     | Description                                                                                                                                                                                                                                                                    |
| ------------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `user`              | string   | Wallet address                                                                                                                                                                                                                                                                 |
| `market`            | string   | Market name (e.g., `BTC`, `xyz:MSFT`)                                                                                                                                                                                                                                          |
| `size`              | float64  | Position size (positive = long, negative = short)                                                                                                                                                                                                                              |
| `notional`          | float64  | Position notional value (USD)                                                                                                                                                                                                                                                  |
| `entry_price`       | float64  | Average entry price                                                                                                                                                                                                                                                            |
| `liquidation_price` | float64? | Estimated liquidation price (null if not calculable)                                                                                                                                                                                                                           |
| `leverage_type`     | string   | `cross` or `isolated`                                                                                                                                                                                                                                                          |
| `leverage`          | uint32   | Leverage multiplier                                                                                                                                                                                                                                                            |
| `funding_pnl`       | float64  | Cumulative funding PnL (USD)                                                                                                                                                                                                                                                   |
| `account_value`     | float64  | Margin available on the collateral token of the traded market. For Hyperliquid native markets this is the USDC margin. For HIP-3 dexes this is the margin in the dex's collateral token (e.g., USDE for HyENA, USDH for Felix). Includes spot collateral for unified accounts. |
| `account_mode`      | string?  | `standard`, `dex_abstraction`, `unified`, `portfolio_margin`                                                                                                                                                                                                                   |

## Spot Snapshot Schema

One row per user per token. Contains all spot token holdings with positive balance.

| Column        | Type    | Description                               |
| ------------- | ------- | ----------------------------------------- |
| `user`        | string  | Wallet address                            |
| `token`       | string  | Token name (e.g., `USDC`, `HYPE`, `PURR`) |
| `balance`     | float64 | Token balance                             |
| `entry_value` | float64 | Entry value in USD                        |

## Account Values Schema

One row per user per dex. Aggregated account-level metrics.

| Column                 | Type    | Description                                                                                                                                                                                                     |
| ---------------------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `user`                 | string  | Wallet address                                                                                                                                                                                                  |
| `dex`                  | string  | DEX name                                                                                                                                                                                                        |
| `collateral_token`     | string  | Collateral token (e.g., `USDC`, `USDE`)                                                                                                                                                                         |
| `account_value`        | float64 | Total margin available on the dex's collateral token. For Hyperliquid this is the USDC equity. For HIP-3 dexes this is the equity in the dex's collateral token. Includes spot collateral for unified accounts. |
| `total_long_notional`  | float64 | Sum of long position notionals                                                                                                                                                                                  |
| `total_short_notional` | float64 | Sum of short position notionals                                                                                                                                                                                 |
| `account_mode`         | string? | Account abstraction mode                                                                                                                                                                                        |

## Quick Start

### DuckDB

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

-- Top BTC positions by size on a specific day
SELECT user, size, notional, entry_price, liquidation_price, leverage_type, leverage
FROM read_parquet('s3://hydromancer-reservoir/by_dex/hyperliquid/snapshots/perp/date=2026-03-22/*.parquet')
WHERE market = 'BTC'
ORDER BY abs(size) DESC
LIMIT 20;

-- Open interest by market
SELECT market,
       sum(CASE WHEN size > 0 THEN notional ELSE 0 END) as long_notional,
       sum(CASE WHEN size < 0 THEN notional ELSE 0 END) as short_notional,
       count(*) as positions
FROM read_parquet('s3://hydromancer-reservoir/global/snapshots/perp/all/date=2026-03-22/*.parquet')
GROUP BY market
ORDER BY long_notional + abs(short_notional) DESC;

-- Largest accounts by value
SELECT user, dex, account_value, total_long_notional, total_short_notional
FROM read_parquet('s3://hydromancer-reservoir/global/snapshots/account_values/date=2026-03-22/*.parquet')
ORDER BY account_value DESC
LIMIT 20;

-- USDC whale balances
SELECT user, balance
FROM read_parquet('s3://hydromancer-reservoir/global/snapshots/spot/date=2026-03-22/*.parquet')
WHERE token = 'USDC'
ORDER BY balance DESC
LIMIT 20;
```