Fact Prices Ohlc Hourly

price.fact_prices_ohlc_hourly

Schema: tron.price Table: fact_prices_ohlc_hourly Type: Base Table

What

This table provides hourly OHLC (Open, High, Low, Close) price data for assets tracked in the price schema. Designed for time series analysis, volatility studies, and candlestick chart creation.

Key Use Cases

• Technical analysis and candlestick chart creation
• Volatility studies and risk assessment
• Historical price lookups and trend analysis

Important Relationships

• Join with dim_asset_metadata: Use asset_id for asset metadata
• Join with ez_asset_metadata: Use asset_id for token details

Commonly-used Fields

• hour: UTC timestamp for the price period
• asset_id: Unique identifier for the asset
• open: Opening price at start of hour
• high: Highest price during hour
• low: Lowest price during hour
• close: Closing price at end of hour

Sample queries

-- TRX OHLC data for the last 30 days
SELECT
    hour,
    open,
    high,
    low,
    close,
    provider
FROM tron.price.fact_prices_ohlc_hourly
WHERE asset_id = 'tron'
    AND hour >= CURRENT_DATE - 30
ORDER BY hour DESC;

-- Daily volatility from hourly OHLC
SELECT
    DATE(hour) AS day,
    MIN(low) AS daily_low,
    MAX(high) AS daily_high,
    (MAX(high) - MIN(low)) / MIN(low) * 100 AS daily_range_pct
FROM tron.price.fact_prices_ohlc_hourly
WHERE asset_id = 'tron'
    AND hour >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 1 DESC;

Columns

Column Name	Data Type	Description
ASSET_ID	TEXT	Unique identifier for the asset from the upstream price provider. Example: ‘tron’
HOUR	TIMESTAMP_NTZ	UTC timestamp truncated to the hour for price recording. Example: 2024-01-15 14:00:00.000
OPEN	FLOAT	Opening price at the start of the hour in USD. Example: 0.1195
HIGH	FLOAT	Highest price reached during the hour in USD. Example: 0.1205
LOW	FLOAT	Lowest price reached during the hour in USD. Example: 0.1185
CLOSE	FLOAT	Closing price at the end of the hour in USD. Example: 0.1200
PROVIDER	TEXT	Data source that provided the price information. Values include ‘coingecko’, ‘coinmarketcap’, ‘dex_aggregated’. Example: ‘coingecko’
FACT_PRICES_OHLC_HOURLY_ID	TEXT	Primary key — unique identifier for each row ensuring data integrity. Format: VARCHAR containing composite key generated using MD5 hash of the relevant columns. Usage: Deduplication in incremental loads Join operations for data quality checks Troubleshooting specific records
INSERTED_TIMESTAMP	TIMESTAMP_NTZ	UTC timestamp when the record was first added to the Flipside database. Format: TIMESTAMP_NTZ Use Cases: Data freshness monitoring Incremental processing markers Debugging data pipeline issues
MODIFIED_TIMESTAMP	TIMESTAMP_NTZ	UTC timestamp of the most recent update to this record. Format: TIMESTAMP_NTZ Use Cases: Tracking data corrections and reprocessing Monitoring incremental model updates Data quality audits