fact_trades

hypercore.core.fact_trades

Schema: hypercore.core Table: fact_trades Type: Table

What

Core fact table recording every matched trade on Hyperliquid across all market types (perpetual futures, spot, and prelaunch). Each row represents a single trade with both buyer and seller details, fees, PnL, and liquidation information. This is the primary source of truth for all trading activity on HyperCore.

Key Use Cases

• Analyzing trading volume and patterns across perp, spot, and prelaunch markets
• Tracking buyer/seller activity, fees, and realized PnL
• Monitoring liquidation events and methods
• Measuring market-maker (builder) participation and fees
• Understanding TWAP and client order routing

Important Relationships

• Enriched by core__ez_perp_trades (joins dim_perp_markets for leverage, size decimals)
• Enriched by core__ez_spot_trades (joins dim_spot_markets for token name, canonical status)
• Market type derived from coin format: plain = perp, @N = spot, xyz:name = prelaunch
• Symbol resolved from dim_spot_markets and silver__token_details for spot tokens

Commonly-used Fields

• trade_id: Unique trade identifier from the L1
• coin / symbol: Raw asset ID and human-readable symbol
• market_type: perp, spot, or prelaunch
• price / trade_size / trade_notional: Execution price, size, and USD notional
• buyer_address / seller_address: Trade counterparties
• buyer_fee / seller_fee: Fees charged (negative = rebate)
• is_liquidation: Whether this trade is a liquidation

Sample queries

-- Platform overview: trade counts and volume by market type
SELECT
    market_type,
    COUNT(*) AS trades,
    COUNT(DISTINCT coin) AS markets,
    ROUND(SUM(trade_notional) / 1e9, 1) AS volume_billions_usd
FROM hypercore.core.fact_trades
GROUP BY 1
ORDER BY 4 DESC;

-- Daily trading volume for top perp markets (last 30 days)
SELECT
    block_timestamp::DATE AS dt,
    coin,
    COUNT(*) AS trades,
    ROUND(SUM(trade_notional) / 1e6, 1) AS volume_millions
FROM hypercore.core.fact_trades
WHERE market_type = 'perp'
    AND block_timestamp >= DATEADD('day', -30, CURRENT_DATE)
    AND coin IN ('BTC', 'ETH', 'SOL', 'HYPE')
GROUP BY 1, 2
ORDER BY 1, 4 DESC;

-- Cross-asset perpetuals: crypto, equities, and commodities on one chain
SELECT
    CASE
        WHEN coin LIKE 'cash:%' THEN 'Equities'
        WHEN coin LIKE 'flx:%' THEN 'Commodities'
        WHEN coin LIKE 'km:%' THEN 'Equities (KM)'
        ELSE 'Crypto'
    END AS asset_class,
    COUNT(DISTINCT coin) AS markets,
    COUNT(*) AS trades_7d,
    ROUND(SUM(trade_notional) / 1e6, 1) AS volume_millions_7d
FROM hypercore.core.fact_trades
WHERE market_type = 'perp'
    AND block_timestamp >= DATEADD('day', -7, CURRENT_DATE)
GROUP BY 1
ORDER BY 4 DESC;

-- Spot vs perp market structure comparison (last 7 days)
SELECT
    market_type,
    block_timestamp::DATE AS dt,
    COUNT(*) AS trades,
    COUNT(DISTINCT buyer_address) AS unique_buyers,
    COUNT(DISTINCT seller_address) AS unique_sellers,
    ROUND(SUM(trade_notional) / 1e6, 1) AS volume_millions,
    ROUND(AVG(trade_notional), 2) AS avg_trade_size
FROM hypercore.core.fact_trades
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_DATE)
GROUP BY 1, 2
ORDER BY 2, 1;

Columns

Column Name	Data Type	Description
TRADE_ID	NUMBER	Unique trade identifier assigned by the Hyperliquid L1 matching engine.
BLOCK_NUMBER	NUMBER	L1 block number containing this event.
BLOCK_TIMESTAMP	TIMESTAMP_NTZ	Block production timestamp on the Hyperliquid L1. This is the canonical time for ordering blockchain events.
COIN	TEXT	Raw asset identifier. Perps use plain ticker (e.g. BTC), spot uses @token_index format (e.g. @2), prelaunch uses xyz:name prefix.
SYMBOL	TEXT	Human-readable trading symbol. Resolved from token metadata for spot tokens; same as coin for perps.
MARKET_TYPE	TEXT	Type of market: ‘perp’ for perpetual futures, ‘spot’ for HIP-1 token trading, ‘prelaunch’ for pre-listing futures.
TRADE_TIMESTAMP	TIMESTAMP_NTZ	Exact fill timestamp from the matching engine, may differ slightly from block_timestamp.
TX_HASH	TEXT	L1 transaction hash uniquely identifying the on-chain transaction.
PRICE	FLOAT	Execution price in USD for this trade.
TRADE_SIZE	FLOAT	Size of the trade in base asset units.
TRADE_NOTIONAL	FLOAT	USD notional value of the trade (price x size), rounded to 2 decimal places.
BUYER_ADDRESS	TEXT	HyperCore address of the buyer in this trade.
SELLER_ADDRESS	TEXT	HyperCore address of the seller in this trade.
BUYER_ORDER_ID	NUMBER	Order ID for the buyer’s order that was filled in this trade.
SELLER_ORDER_ID	NUMBER	Order ID for the seller’s order that was filled in this trade.
BUYER_DIR	TEXT	Buyer’s trade direction (e.g. Open Long, Close Short).
SELLER_DIR	TEXT	Seller’s trade direction (e.g. Open Short, Close Long).
BUYER_CROSSED	BOOLEAN	Whether the buyer’s order crossed the spread (was a taker).
SELLER_CROSSED	BOOLEAN	Whether the seller’s order crossed the spread (was a taker).
BUYER_FEE	FLOAT	Fee charged to the buyer. Negative values indicate a maker rebate.
SELLER_FEE	FLOAT	Fee charged to the seller. Negative values indicate a maker rebate.
FEE_TOKEN	TEXT	Token in which trading fees are denominated.
BUYER_CLOSED_PNL	FLOAT	Realized profit/loss for the buyer if this trade closes or reduces an existing position.
SELLER_CLOSED_PNL	FLOAT	Realized profit/loss for the seller if this trade closes or reduces an existing position.
BUYER_START_POSITION	FLOAT	Buyer’s position size before this trade was executed.
SELLER_START_POSITION	FLOAT	Seller’s position size before this trade was executed.
BUILDER_ADDRESS	TEXT	Address of the block builder (MEV/builder infrastructure). NULL for most trades.
TOTAL_BUILDER_FEE	FLOAT	Combined builder fee from both buyer and seller sides.
BUYER_TWAP_ID	NUMBER	TWAP order ID for the buyer. NULL if not a TWAP order.
SELLER_TWAP_ID	NUMBER	TWAP order ID for the seller. NULL if not a TWAP order.
BUYER_CLIENT_ORDER_ID	TEXT	Client-assigned order ID for the buyer’s order.
SELLER_CLIENT_ORDER_ID	TEXT	Client-assigned order ID for the seller’s order.
LIQUIDATED_USER	TEXT	Address of the user being liquidated. NULL if this is not a liquidation trade.
LIQUIDATION_MARK_PRICE	FLOAT	Mark price at the time of liquidation. NULL if not a liquidation.
LIQUIDATION_METHOD	TEXT	Method used for the liquidation (e.g. backstop, market). NULL if not a liquidation.
IS_LIQUIDATION	BOOLEAN	Boolean indicating whether this trade is a liquidation event.
INSERTED_TIMESTAMP	TIMESTAMP_NTZ	Timestamp when the record was inserted into this table (Snowflake SYSDATE).
MODIFIED_TIMESTAMP	TIMESTAMP_NTZ	Timestamp when the record was last modified in this table (Snowflake SYSDATE).
FACT_TRADES_ID	TEXT	Surrogate key for fact_trades, generated as MD5 hash of trade_id.
_INVOCATION_ID	TEXT	dbt invocation identifier for tracking which run produced this record.