Tables

Tables for sigstar. Collect market, on-chain, news and macro data, and explain, forecast and alert on crypto price moves from every angle.

Schema sigstar in the warehouse. All times are UTC; a "day" is a UTC calendar day.

Mart	Grain	What is in it
`fct_prices`	asset, venue, interval (`1d`/`1h`), ts (candle open)	OHLCV of closed candles; `volume` in the coin, `volume_quote` in USD or USDT (venue-reported, or volume x close flagged by `volume_quote_is_estimate`), `log_return`. Venue `onchain`: chainstar's price (close = the bucket's median swap price, high/low = highest/lowest observation, no open), one chain per bucket (highest confidence, then most observations), with `price_chain`, `price_method`, `price_confidence`, `price_observations`
`fct_onchain_prices`	asset, chain, interval, ts (bucket start)	chainstar's high and medium confidence USD prices for the configured tokens: `usd_price`, `observed_price`, `price_low`/`price_high`, `price_method`, `confidence`, `observations`, `rejected_observations`, `volume_usd`, `sources`, `price_id`
`fct_onchain_dex_daily`	asset, day, chain	swaps in the asset's DEX tokens: `swap_count`, `valued_swaps`, `volume_usd`, `coverage_seconds` of the chain's loaded blocks that day
`fct_derivatives`	asset, venue, day	`funding_rate_sum` (the day's settlements, a fraction), `funding_events`, open interest snapshots at this and the next midnight, `oi_change_pct` in contracts
`fct_news`	one headline (URL)	title, link, source, time, provider (`gdelt`/`rss`), feed snippet (max 280 chars), `tags`
`bridge_news_tags`	headline, tag	which asset or topic a headline matched, and the keyword that matched
`news_rules`	keyword	the whole-word keyword rules behind the tags
`agg_news_daily`	tag, day	GDELT article count, share of all monitored articles, article-weighted tone, headlines held; `gdelt_gap` marks GDELT outage days (blanked)
`fct_sentiment`	indicator, day	Fear & Greed value, classification, daily change
`fct_macro`	series, observation day	value, change since the previous observation, its z-score
`fct_onchain_daily`	day, chain, metric, token_address	transactions, fees, fee payers, native and stablecoin transfers (by contract address), coverage
`features_daily`	asset, day (gap-free)	everything below, one row per asset and day

Feature columns in features_daily (each *_z compares the value with the previous 30 days, the day itself excluded, and is empty until 10 earlier days exist):

Column	Meaning
`close`, `ref_venue`	Close on the first venue in `coinbase, kraken, binance, onchain` that has the day (always `onchain` in commercial mode)
`ret_1d`, `ret_1d_z`, `ret_7d`	Log return, its z-score ("size of the move"), 7-day log return
`rv_7d`, `rv_30d`	Realised volatility from daily returns, annualised (x √365)
`range_pct`, `range_z`	(high - low) / close
`rv_intraday`, `rv_intraday_z`	Volatility within the day from the 24 hourly returns, annualised (hourly history starts at 7 days and grows)
`volume_quote`, `volume_z`	Volume across exchange venues in USD/USDT (z on its log)
`spread_bps`, `spread_z`	Gap between the highest and lowest close among USD exchange venues, in basis points
`funding_rate`, `funding_z`	Day's funding, averaged across venues
`oi_usd`, `oi_change_pct`, `oi_change_z`	Open interest at the end of the day; change in contracts during the day
`news_volume`, `news_volume_share`, `news_volume_z`	GDELT articles matching the asset's query (z on log(1 + n))
`news_tone`, `news_tone_z`, `news_headlines`	GDELT tone; headlines held for the asset
`news_<topic>_volume(_z)`, `news_<topic>_tone(_z)`	The same for each topic (market-wide)
`fear_greed`, `fear_greed_change`, `fear_greed_change_z`	Fear & Greed Index and its daily change
`macro_<series>`, `macro_<series>_change(_z)`	Latest FRED value; change on observation days
`onchain_tx_count(_z)`, `onchain_active_wallets(_z)`, `onchain_fees_native`, `onchain_coverage_seconds`	chainstar activity on the asset's chains
`stablecoin_transfer_amount`, `stablecoin_transfer_z`	Stablecoin transfers on all chains (market-wide)
`onchain_price`, `onchain_price_chain`, `onchain_price_method`, `onchain_price_confidence`, `onchain_price_observations`	chainstar's daily on-chain price (median of the day's swaps) and where it came from
`onchain_dex_volume_usd`, `onchain_dex_volume_z`	USD volume of swaps in the asset's DEX tokens (z on its log)
`onchain_swap_count`, `onchain_swap_count_z`, `onchain_dex_coverage_seconds`	Those swaps' count (z on log(1 + n)); seconds of the day the chains' loaded blocks span
`onchain_price_gap_bps`, `onchain_price_gap_z`, `onchain_price_gap_hours`	Median over the day's hours of (on-chain price - exchanges' typical price (H+L+C)/3 on USD venues) / exchanges', in basis points; personal mode only
`<on-chain z column>_note`	Why that on-chain z-score is empty when chain coverage was not comparable with enough earlier days (also `range_z_note` on days priced on-chain)

On-chain z-scores (onchain_*_z, stablecoin_transfer_z, and range_z on days priced on-chain) use only earlier days of comparable chain coverage; see On-chain prices and DEX activity.

Query them directly, for example with the DuckDB CLI:

duckdb warehouse.duckdb "select asset, day, ret_1d, funding_z, news_volume_z
  from sigstar.features_daily where abs(ret_1d_z) > 2 order by day desc"