prediction_markets.ohlcv_hourly

Cross-venue hourly OHLCV bars, prices in probability space [0, 1] with volume and trade counts.

The prediction_markets.ohlcv_hourly table provides hourly OHLCV bars across Polymarket and Kalshi, including Polymarket Combos (multi-leg parlays, keyed by the combo condition ID; is_parlay = TRUE in the markets dimension). Grain: one row per (venue, hour, market_id, outcome). Prices are in probability space [0, 1]. Polymarket emits Yes and No bars; Kalshi emits Yes only (No can be derived as 1 - Yes). Hours without trades are a flat bar at the carried close and are flagged via is_forward_filled.

A settled market pins at its settlement-hour bar — the on-chain resolution hour for Polymarket, the actual settlement time (clamped to the scheduled expiration) for Kalshi. Settlement lands as that bar’s final tick: close pins to the settlement value (1/0; 0.5 on Polymarket 50/50 markets; Combos to their exact per-side payout), high/low widen to include it, open keeps the last traded price. Polymarket series end there (forward-fill is bounded at resolution); Kalshi series keep forward-filling flat at the settled value until the scheduled expiration. Bars with real post-settlement trades keep their actual OHLC on both venues.

Table Schema

Column	Type	Description
venue	VARCHAR	polymarket or kalshi
block_month	DATE	First day of the calendar month of hour (partition key)
hour	TIMESTAMP	Bar timestamp, truncated to the hour
market_id	VARCHAR	Polymarket condition_id (combo condition ID for Combos) / Kalshi ticker
outcome	VARCHAR	Outcome side this bar represents
market_name	VARCHAR	Market title (denormalized); for combos the generated combo name, NULL unless every leg is labeled
category	VARCHAR	Unified category enum
category_native	VARCHAR	Original upstream category value
open	DOUBLE	Opening probability of the bar, in [0, 1]
high	DOUBLE	Bar high, in [0, 1]
low	DOUBLE	Bar low, in [0, 1]
close	DOUBLE	Closing probability of the bar, in [0, 1]
vwap	DOUBLE	Volume-weighted average price for the bar. NULL when no trades
volume_contracts	DOUBLE	Total contracts traded in the hour
volume_usd	DOUBLE	Total USD notional traded in the hour
volume_usd_yes	DOUBLE	USD notional traded on the Yes side
volume_usd_no	DOUBLE	USD notional traded on the No side. NULL for Kalshi (Yes-only)
trade_count	BIGINT	Number of fills in the hour
market_end_time	TIMESTAMP	Market expiration time (denormalized)
market_outcome	VARCHAR	Resolved outcome (partial for fractionally-settled combos; NULL/unresolved while open). Point-in-time per bar on both venues: unresolved strictly before the settlement hour, the actual outcome from that bar onward. The venue dimension tables carry a market’s current state
event_market_name	VARCHAR	Event group label (denormalized)
is_forward_filled	BOOLEAN	TRUE when the bar has no trades and was carried forward
_updated_at	TIMESTAMP	When this row was last written by the pipeline

Table sample

Query performance

block_month is the partition key. Always include a block_month or hour filter.

Example query

-- Daily VWAP across both venues for a single category, last 30 days
SELECT
  venue,
  DATE_TRUNC('day', hour) AS day,
  AVG(vwap) AS avg_daily_vwap,
  SUM(volume_usd) AS volume_usd
FROM prediction_markets.ohlcv_hourly
WHERE hour >= NOW() - INTERVAL '30' DAY
  AND category = 'politics'
  AND vwap IS NOT NULL
GROUP BY 1, 2
ORDER BY 1, 2