Skip to main content
Schema: ethereum.price Table: fact_prices_ohlc_hourly Type: Base Table

​
What

This table provides hourly OHLC (Open, High, Low, Close) price data for all assets tracked in the price schema. It is designed for time series analysis, volatility studies, and historical price lookups.

​
Key Use Cases

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

​
Important Relationships

  • Join with dim_asset_metadata: For asset metadata
  • Join with ez_token_transfers: For USD value calculations

​
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

OHLC Data Retrieval 
SELECT hour, asset_id, open, high, low, close
FROM <blockchain_name>.price.fact_prices_ohlc_hourly
WHERE asset_id = '<asset_id>'
  AND hour >= CURRENT_DATE - 30
ORDER BY hour DESC;

​
Columns

Column NameData TypeDescription
ASSET_IDTEXTUnique identifier for the asset from the price provider. Provider-specific ID format.
Example: ‘ethereum’ | | HOUR | TIMESTAMP_NTZ | UTC timestamp truncated to the hour for price recording. Used for joining with hourly transaction data. Example: ‘2024-01-15 14:00:00.000’ | | OPEN | FLOAT | Opening price at the start of the hour in USD. First recorded price in the hour for OHLC analysis. Example: 2995.25 | | HIGH | FLOAT | Highest price reached during the hour in USD. Maximum price in the hour for volatility analysis. Example: 3005.75 | | LOW | FLOAT | Lowest price reached during the hour in USD. Minimum price in the hour for support level analysis. Example: 2985.50 | | CLOSE | FLOAT | Closing price at the end of the hour in USD. Last recorded price in the hour, commonly used for valuations. Example: 3000.50 | | PROVIDER | TEXT | | | FACT_PRICES_OHLC_HOURLY_ID | TEXT | Primary key - unique identifier for each row ensuring data integrity. Format: Usually VARCHAR containing composite key generated using MD5 hash of the relevant columns. Example: MD5(block_number, tx_hash, trace_index) Usage:
  • Deduplication in incremental loads
  • Join operations for data quality checks
  • Troubleshooting specific records
Important: Implementation varies by table - check table-specific documentation. | | 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
  • SLA tracking
Query Example: 
-- Check data latency
SELECT
    DATE_TRUNC('hour', block_timestamp) as block_hour,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    AVG(TIMESTAMPDIFF('minute', block_timestamp, inserted_timestamp)) as avg_latency_minutes
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2;
``` |
| MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp of the most recent update to this record.

**Format**: TIMESTAMP_NTZ

**Triggers for Updates**:
- Data corrections
- Enrichment additions
- Reprocessing for accuracy
- Schema migrations

**Monitoring Usage**:
```sql
-- Recently modified records
SELECT *
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
AND modified_timestamp >= CURRENT_DATE - 1;

-- Data quality tracking
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_number) as blocks_affected
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
GROUP BY 1
ORDER BY 1 DESC;
``` |