Skip to main content
Schema: flow.price Table: fact_prices_ohlc_hourly Type: View

​
price__fact_prices_ohlc_hourly

​
Description

A comprehensive fact table containing raw OHLC (Open, High, Low, Close) price data at hourly intervals from multiple price providers. This table preserves the original provider structure and contains uncurated price data that may include duplicates, inconsistencies, and data quality issues inherent to the source APIs. It serves as the foundation for price analysis while maintaining data lineage and enabling provider-specific analysis. The table supports detailed price movement analysis and technical indicator calculations.

​
Key Use Cases

  • Technical Analysis: Calculating technical indicators, support/resistance levels, and price patterns
  • Volatility Analysis: Measuring price volatility and risk metrics across different time periods
  • Provider Comparison: Analyzing price differences and data quality across different price providers
  • Data Quality Assessment: Identifying gaps, anomalies, and inconsistencies in price data
  • Raw Data Access: Providing access to unprocessed price data for custom analysis and backtesting
  • Historical Price Analysis: Conducting detailed historical price movement analysis and research

​
Important Relationships

  • price__dim_asset_metadata: Links to asset metadata through asset_id and provider
  • price__ez_asset_metadata: Provides curated asset information for analysis
  • price__ez_prices_hourly: Curated, deduplicated version of this table with one price per asset per hour
  • core__fact_blocks: May be used for blockchain-specific price analysis and correlation studies
  • core__fact_transactions: For transaction value analysis and price impact studies

​
Commonly-used Fields

  • HOUR: Essential for time-series analysis and temporal data aggregation
  • ASSET_ID: Primary identifier for joining with asset metadata and other price-related data
  • OPEN: Starting price for the hour, used in OHLC analysis and trend calculations
  • HIGH: Maximum price during the hour, important for resistance level analysis
  • LOW: Minimum price during the hour, important for support level analysis
  • CLOSE: Ending price for the hour, most commonly used for price trend analysis

​
Columns

Column NameData TypeDescription
ASSET_IDTEXTThe unique identifier representing the asset within the price provider’s system. Data type: STRING. This field serves as the primary key for identifying specific assets across different price providers and data sources. Used for joining price data with asset metadata, tracking price movements over time, and maintaining referential integrity between fact and dimension tables. Example: ‘bitcoin’ for Bitcoin on CoinGecko, ‘ethereum’ for Ethereum on CoinMarketCap. Critical for data lineage, asset identification, and cross-provider data reconciliation in financial analytics and reporting workflows.
HOURTIMESTAMP_NTZThe timestamp representing the hour when the price data was recorded or aggregated. Data type: TIMESTAMP. This field provides the temporal reference for hourly price data and is used for time-series analysis, price trend calculations, and temporal data aggregation. Used for hourly price tracking, time-based filtering, and chronological analysis of price movements. Example: ‘2024-01-15 14:00:00’ for the hour starting at 2 PM on January 15, 2024. Critical for time-series analytics, price volatility calculations, and maintaining temporal consistency in financial data analysis.
OPENFLOATThe opening price of the asset at the beginning of the specified hour, denominated in USD. Data type: FLOAT. This field represents the first trading price of the asset during the hour and is used for OHLC (Open, High, Low, Close) price analysis and volatility calculations. Used for price range analysis, volatility measurement, and technical analysis indicators. Example: 44000.00 for Bitcoin opening at $44,000.00 USD per BTC at the start of the hour. Critical for candlestick chart analysis, price momentum calculations, and understanding intra-hour price movements.
HIGHFLOATThe highest trading price of the asset during the specified hour, denominated in USD. Data type: FLOAT. This field represents the peak price reached by the asset within the hour and is used for price range analysis and volatility calculations. Used for resistance level analysis, volatility measurement, and understanding price extremes during trading periods. Example: 46000.00 for Bitcoin reaching a high of $46,000.00 USD per BTC during the hour. Critical for technical analysis, price range calculations, and understanding market sentiment and price momentum.
LOWFLOATThe lowest trading price of the asset during the specified hour, denominated in USD. Data type: FLOAT. This field represents the minimum price reached by the asset within the hour and is used for price range analysis and volatility calculations. Used for support level analysis, volatility measurement, and understanding price extremes during trading periods. Example: 43000.00 for Bitcoin reaching a low of $43,000.00 USD per BTC during the hour. Critical for technical analysis, price range calculations, and understanding market sentiment and price momentum.
CLOSEFLOATThe closing price of the asset at the end of the specified hour, denominated in USD. Data type: FLOAT. This field represents the final trading price of the asset during the hour and is used for OHLC (Open, High, Low, Close) price analysis and trend calculations. Used for price trend analysis, momentum calculations, and technical indicator computations. Example: 45000.50 for Bitcoin closing at $45,000.50 USD per BTC at the end of the hour. Critical for candlestick chart analysis, price trend identification, and understanding market direction and momentum.
PROVIDERTEXT
INSERTED_TIMESTAMPTIMESTAMP_NTZThe UTC timestamp when the record was first created and inserted into this table. Data type: TIMESTAMP_NTZ. Used for ETL auditing, tracking data freshness, and identifying when data was loaded or updated in the analytics pipeline. Example: ‘2023-01-01 12:00:00’. This field is critical for monitoring data latency, troubleshooting ETL issues, and supporting recency tests in dbt.
MODIFIED_TIMESTAMPTIMESTAMP_NTZThe UTC timestamp when this record was last updated or modified by an internal ETL or dbt process. Data type: TIMESTAMP_NTZ. Used for change tracking, ETL auditing, and identifying the most recent update to a record. Example: ‘2023-01-02 15:30:00’. This field is important for troubleshooting data issues, monitoring pipeline health, and supporting recency or freshness tests in dbt.
FACT_PRICES_OHLC_HOURLY_IDTEXTpk_id is a surrogate primary key, uniquely generated for each row in the table. Data type: STRING or INTEGER (implementation-specific). This field ensures every record is uniquely identifiable, even if the source data lacks a natural primary key. Used for efficient joins, deduplication, and as a reference in downstream models. Example: an auto-incremented integer or a UUID string. Essential for maintaining data integrity and supporting dbt tests for uniqueness.