> 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/fills.md).

# Fills

Every trade execution (fill) across all perpetual and spot markets on Hyperliquid and all HIP-3 dexes. Updated daily. Partitioned by date, split by dex and sub-dataset.

## S3 Paths

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

```
global/fills/raw/date=YYYY-MM-DD/fills.parquet              All fills
global/fills/spot/all/date=YYYY-MM-DD/fills.parquet          Spot fills only
global/fills/spot/builder_fills/date=YYYY-MM-DD/fills.parquet

by_dex/{dex}/fills/perp/all/date=YYYY-MM-DD/fills.parquet
by_dex/{dex}/fills/perp/liquidations/date=YYYY-MM-DD/fills.parquet
by_dex/{dex}/fills/perp/adl/date=YYYY-MM-DD/fills.parquet
by_dex/{dex}/fills/perp/builder_fills/date=YYYY-MM-DD/fills.parquet
by_dex/{dex}/fills/perp/twap_fills/date=YYYY-MM-DD/fills.parquet
```

Sub-datasets (`liquidations`, `adl`, `builder_fills`, `twap_fills`) are subsets of `all`. A liquidation fill appears in both `liquidations/` and `all/`. Files are only created when non-empty — if a day has no ADL events for a dex, there is no `adl/` file for that date.

**Available dexes:** `hyperliquid`, `xyz`, `cash`, `hyna`, `flx`, `km`, `vntl`

## Schema

All fill files use the same 27-column Parquet schema:

| Column                | Type               | Description                                                                                                          |
| --------------------- | ------------------ | -------------------------------------------------------------------------------------------------------------------- |
| `coin`                | string             | Raw market identifier (e.g., `BTC`, `xyz:MSFT`, `@107`)                                                              |
| `dex`                 | string             | DEX name                                                                                                             |
| `asset_class`         | string             | `perp` or `spot`                                                                                                     |
| `base_symbol`         | string             | Base asset (e.g., `BTC`, `MSFT`, `PURR`)                                                                             |
| `quote_symbol`        | string             | Quote/collateral asset (e.g., `USDC`, `USDT0`, `USDE`, `USDH`)                                                       |
| `price`               | decimal(20,10)     | Execution price                                                                                                      |
| `size`                | decimal(20,10)     | Fill size                                                                                                            |
| `side`                | string             | `buy` or `sell`                                                                                                      |
| `timestamp`           | timestamp(ms, UTC) | Execution time                                                                                                       |
| `direction`           | string             | Position direction (see below)                                                                                       |
| `realized_pnl`        | decimal(20,10)     | Realized profit/loss from this fill                                                                                  |
| `tx_hash`             | string             | Transaction hash                                                                                                     |
| `order_id`            | uint64             | Order ID                                                                                                             |
| `trade_id`            | uint64             | Trade ID                                                                                                             |
| `fee`                 | decimal(20,10)     | Trading fee                                                                                                          |
| `fee_token`           | string             | Token used for fee payment                                                                                           |
| `address`             | string             | User wallet address                                                                                                  |
| `crossed`             | boolean            | Whether the order crossed the spread                                                                                 |
| `start_position`      | decimal(20,10)     | Position size before this fill                                                                                       |
| `client_order_id`     | string?            | Client-provided order ID (nullable)                                                                                  |
| `builder`             | string?            | Builder address (nullable)                                                                                           |
| `builder_fee`         | decimal(20,10)?    | Builder fee (nullable)                                                                                               |
| `deployer_fee`        | decimal(20,10)?    | Deployer fee (nullable, HIP-3 fills only). Column present from 2026-03-21 onwards; earlier files may not include it. |
| `priority_gas`        | decimal(20,10)?    | Priority gas fee in HYPE (nullable). Column present from 2026-04-13 onwards; earlier files may not include it.       |
| `twap_id`             | uint64?            | TWAP order ID (nullable)                                                                                             |
| `is_liquidation`      | boolean?           | `true` if user was liquidated, `null` for spot fills                                                                 |
| `liquidation_mark_px` | decimal(20,10)?    | Mark price at liquidation (nullable)                                                                                 |
| `liquidation_method`  | string?            | Liquidation method (nullable)                                                                                        |

### Direction values

**Perp positions:**

| Value          | Meaning                     |
| -------------- | --------------------------- |
| `Open Long`    | Opening a long position     |
| `Open Short`   | Opening a short position    |
| `Close Long`   | Closing a long position     |
| `Close Short`  | Closing a short position    |
| `Long > Short` | Flipping from long to short |
| `Short > Long` | Flipping from short to long |

**Liquidations:**

| Value                         | Meaning                           |
| ----------------------------- | --------------------------------- |
| `Liquidated Cross Long`       | Cross-margin long liquidation     |
| `Liquidated Cross Short`      | Cross-margin short liquidation    |
| `Liquidated Isolated Long`    | Isolated-margin long liquidation  |
| `Liquidated Isolated Short`   | Isolated-margin short liquidation |
| `Auto-Deleveraging`           | Auto-deleveraging event           |
| `Partial Borrow Liquidation`  | Partial borrow liquidation        |
| `Backstop Borrow Liquidation` | Backstop borrow liquidation       |

**Special:**

| Value              | Meaning                         |
| ------------------ | ------------------------------- |
| `Settlement`       | Settlement                      |
| `Net Child Vaults` | Net child vault position change |

**Spot:**

| Value                  | Meaning                |
| ---------------------- | ---------------------- |
| `Buy`                  | Spot buy               |
| `Sell`                 | Spot sell              |
| `Spot Dust Conversion` | Automatic dust cleanup |

## Quick Start

### DuckDB

```sql
-- Install and load httpfs extension for S3 access
INSTALL httpfs;
LOAD httpfs;
SET s3_region = 'ap-northeast-1';

-- All BTC fills on a specific day
SELECT * FROM read_parquet('s3://hydromancer-reservoir/by_dex/hyperliquid/fills/perp/all/date=2026-03-22/fills.parquet')
WHERE coin = 'BTC'
ORDER BY timestamp;

-- Daily volume by market
SELECT base_symbol, dex,
       sum(price * size) as volume_usd,
       count(*) as trades
FROM read_parquet('s3://hydromancer-reservoir/global/fills/raw/date=2026-03-22/fills.parquet')
WHERE asset_class = 'perp'
GROUP BY base_symbol, dex
ORDER BY volume_usd DESC;

-- All liquidations across all dexes for a week
SELECT *
FROM read_parquet('s3://hydromancer-reservoir/by_dex/*/fills/perp/liquidations/date=*/fills.parquet')
WHERE timestamp >= '2026-03-15' AND timestamp < '2026-03-22';

-- Spot trading activity
SELECT base_symbol, quote_symbol, count(*) as trades, sum(size) as total_size
FROM read_parquet('s3://hydromancer-reservoir/global/fills/spot/all/date=2026-03-22/fills.parquet')
GROUP BY base_symbol, quote_symbol
ORDER BY trades DESC;
```

### Python

```python
import duckdb

con = duckdb.connect()
con.execute("INSTALL httpfs; LOAD httpfs; SET s3_region = 'ap-northeast-1';")

df = con.execute("""
    SELECT * FROM read_parquet(
        's3://hydromancer-reservoir/by_dex/hyperliquid/fills/perp/all/date=2026-03-22/fills.parquet'
    )
    WHERE coin = 'BTC'
""").fetchdf()

print(df.head())
```