Ez Prices Hourly

price.ez_prices_hourly

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

What

This table provides hourly USD price data for tokens and native TRX on the Tron blockchain. Prices are aggregated from multiple data sources with quality checks, and missing hours are forward-filled (imputed) to ensure continuous coverage.

Key Use Cases

• USD valuation of token holdings and transfers
• Historical price analysis and trend tracking
• Portfolio value calculations
• DEX price comparison and arbitrage detection

Important Relationships

• Join with ez_token_transfers: Use contract_address and DATE_TRUNC('hour', block_timestamp) for transfer USD values
• Join with ez_native_transfers: Use DATE_TRUNC('hour', block_timestamp) where is_native = TRUE for TRX price
• Join with ez_asset_metadata: Use token_address for additional asset metadata

Commonly-used Fields

• hour: UTC timestamp truncated to the hour
• token_address: Contract address (NULL for native TRX)
• symbol: Token ticker symbol
• price: USD price per whole token unit
• is_imputed: Whether the price was forward-filled

Sample queries

-- TRX price history
SELECT
    hour,
    price
FROM tron.price.ez_prices_hourly
WHERE token_address IS NULL
    AND hour >= CURRENT_DATE - 30
ORDER BY hour DESC;

-- Top tokens by price data coverage
SELECT
    token_address,
    symbol,
    COUNT(*) AS hours_with_price,
    SUM(CASE WHEN is_imputed THEN 1 ELSE 0 END) AS imputed_hours,
    MIN(hour) AS first_price,
    MAX(hour) AS last_price
FROM tron.price.ez_prices_hourly
WHERE hour >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 50;

Columns

Column Name	Data Type	Description
HOUR	TIMESTAMP_NTZ	UTC timestamp truncated to the hour for price recording. Example: 2024-01-15 14:00:00.000
TOKEN_ADDRESS	TEXT	Contract address of the token on the blockchain, in 0x-prefixed hex format. NULL for native TRX. Example: ‘0xa614f803b6fd780986a42c78ec9c7f77e6ded13c’
SYMBOL	TEXT	Token ticker symbol as commonly recognized. Example: ‘USDT’
NAME	TEXT	NAME column
DECIMALS	NUMBER	Number of decimal places for the token. TRX uses 6 decimals; most TRC-20 tokens use 18. Example: 6
PRICE	FLOAT	USD price of one whole token unit at the recorded hour. Example: 0.12
BLOCKCHAIN	TEXT	The blockchain network where the asset exists. Example: ‘tron’
IS_NATIVE	BOOLEAN	Boolean indicating if the asset is the blockchain’s native currency (TRX).
IS_IMPUTED	BOOLEAN	Boolean flag indicating if the price was forward-filled from a previous hour due to missing data from the source. Imputed prices carry forward the last known good price.
IS_DEPRECATED	BOOLEAN	Flag indicating if the asset is no longer actively supported or tracked.
IS_VERIFIED	BOOLEAN	Boolean indicating Flipside team verification of the asset.
EZ_PRICES_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