Skip to content

DATABASE_SCHEMA.md

Latest commit

 

History

History
952 lines (724 loc) · 36.9 KB

DATABASE_SCHEMA.md

File metadata and controls

952 lines (724 loc) · 36.9 KB
Raw

Database Schema Documentation - exness-data-preprocess v1.6.0

Database Type: DuckDB (embedded OLAP database) Architecture: Single-file per instrument (unified multi-year storage) Schema Version: v1.6.0 (Phase7 30-column OHLC with 10 global exchange sessions - trading hour detection) Last Updated: 2025-10-17

Overview

Each currency pair is stored in a single DuckDB file containing:

  • All historical tick data from both variants (Raw_Spread + Standard)
  • Pre-computed 1-minute OHLC bars with Phase7 30-column schema (includes normalized spread metrics and 10 global exchange sessions)
  • Metadata tracking coverage and update history

File Naming Convention: {instrument_lowercase}.duckdb

Examples:

  • EURUSD → eurusd.duckdb
  • XAUUSD → xauusd.duckdb
  • GBPUSD → gbpusd.duckdb

Default Location: ~/eon/exness-data/

Table Structure

Each instrument database contains 4 tables:

eurusd.duckdb
├── raw_spread_ticks      (Primary execution data)
├── standard_ticks        (Reference market data)
├── ohlc_1m              (Pre-computed OHLC bars)
└── metadata             (Coverage tracking)

Table 1: raw_spread_ticks

Purpose: Stores execution prices from Exness Raw_Spread variant (98% zero-spreads)

Use Case: Primary data source for OHLC generation and execution analysis

Data Source: https://ticks.ex2archive.com/ticks/EURUSD_Raw_Spread/{YEAR}/{MONTH}/

Schema

Column Type Constraints Description
Timestamp TIMESTAMP WITH TIME ZONE PRIMARY KEY Microsecond-precision tick timestamp (UTC)
Bid DOUBLE NOT NULL Bid price (execution price)
Ask DOUBLE NOT NULL Ask price (execution price)

Indexes

  • Automatic Index: PRIMARY KEY constraint on Timestamp automatically creates an index for efficient queries
  • No explicit index creation needed - DuckDB optimizes PRIMARY KEY columns automatically

Constraints

  • PRIMARY KEY on Timestamp: Ensures no duplicate ticks during incremental updates
  • NOT NULL constraints: Ensures data integrity

Table Comments

Stored in Database: The following comments are automatically added when the database is created (see processor.py lines 138-146).

COMMENT ON TABLE raw_spread_ticks IS
'Exness Raw_Spread variant (execution prices, ~98% zero-spreads).
 Data source: https://ticks.ex2archive.com/ticks/{SYMBOL}_Raw_Spread/{YEAR}/{MONTH}/';

-- Column comments
COMMENT ON COLUMN raw_spread_ticks.Timestamp IS 'Microsecond-precision tick timestamp (UTC)';
COMMENT ON COLUMN raw_spread_ticks.Bid IS 'Bid price (execution price)';
COMMENT ON COLUMN raw_spread_ticks.Ask IS 'Ask price (execution price)';

Retrieve Comments:

-- Query all table comments
SELECT table_name, comment FROM duckdb_tables();

-- Query column comments
SELECT table_name, column_name, data_type, comment
FROM duckdb_columns()
WHERE table_name = 'raw_spread_ticks';

Characteristics

  • Zero-Spreads: ~98% of ticks have Bid = Ask
  • Tick Frequency: Variable (1µs to 130s intervals)
  • Monthly Volume: ~1.2M - 2.9M ticks per month
  • Storage Size: ~150 MB per year

Example Data

Timestamp                       | Bid      | Ask
--------------------------------|----------|----------
2024-09-01 14:05:22.053000+00  | 1.10500  | 1.10500  (zero-spread)
2024-09-01 14:05:22.154000+00  | 1.10505  | 1.10505  (zero-spread)
2024-09-01 14:05:22.256000+00  | 1.10502  | 1.10503  (non-zero spread)

Query Examples

-- Get all ticks for September 2024
SELECT * FROM raw_spread_ticks
WHERE Timestamp >= '2024-09-01' AND Timestamp < '2024-10-01'
ORDER BY Timestamp;

-- Count zero-spread ticks
SELECT COUNT(*) as zero_spreads
FROM raw_spread_ticks
WHERE Bid = Ask;

-- Get tick statistics
SELECT
    DATE_TRUNC('day', Timestamp) as day,
    COUNT(*) as tick_count,
    MIN(Bid) as min_bid,
    MAX(Bid) as max_bid,
    AVG(Ask - Bid) as avg_spread
FROM raw_spread_ticks
WHERE Timestamp >= '2024-09-01'
GROUP BY day
ORDER BY day;

Table 2: standard_ticks

Purpose: Stores traditional market quotes from Exness Standard variant (0% zero-spreads)

Use Case: Reference data for spread comparison and position ratio calculation

Data Source: https://ticks.ex2archive.com/ticks/EURUSD/{YEAR}/{MONTH}/

Schema

Column Type Constraints Description
Timestamp TIMESTAMP WITH TIME ZONE PRIMARY KEY Microsecond-precision tick timestamp (UTC)
Bid DOUBLE NOT NULL Bid price (always < Ask)
Ask DOUBLE NOT NULL Ask price (always > Bid)

Indexes

  • Automatic Index: PRIMARY KEY constraint on Timestamp automatically creates an index for efficient queries
  • No explicit index creation needed - DuckDB optimizes PRIMARY KEY columns automatically

Constraints

  • PRIMARY KEY on Timestamp: Ensures no duplicate ticks during incremental updates
  • NOT NULL constraints: Ensures data integrity
  • Implicit constraint: Bid < Ask (validated at application level)

Table Comments

Stored in Database: The following comments are automatically added when the database is created (see processor.py lines 158-166).

COMMENT ON TABLE standard_ticks IS
'Exness Standard variant (traditional quotes, 0% zero-spreads, always Bid < Ask).
 Data source: https://ticks.ex2archive.com/ticks/{SYMBOL}/{YEAR}/{MONTH}/';

-- Column comments
COMMENT ON COLUMN standard_ticks.Timestamp IS 'Microsecond-precision tick timestamp (UTC)';
COMMENT ON COLUMN standard_ticks.Bid IS 'Bid price (always < Ask)';
COMMENT ON COLUMN standard_ticks.Ask IS 'Ask price (always > Bid)';

Retrieve Comments:

SELECT table_name, column_name, data_type, comment
FROM duckdb_columns()
WHERE table_name = 'standard_ticks';

Characteristics

  • Zero-Spreads: 0% (always Bid < Ask)
  • Tick Frequency: Variable (similar to Raw_Spread)
  • Monthly Volume: ~1.4M - 3.0M ticks per month
  • Storage Size: ~160 MB per year

Example Data

Timestamp                       | Bid      | Ask
--------------------------------|----------|----------
2024-09-01 14:05:22.053000+00  | 1.10498  | 1.10502  (spread = 0.4 pips)
2024-09-01 14:05:22.154000+00  | 1.10503  | 1.10507  (spread = 0.4 pips)
2024-09-01 14:05:22.256000+00  | 1.10500  | 1.10504  (spread = 0.4 pips)

Query Examples

-- Get all ticks for September 2024
SELECT * FROM standard_ticks
WHERE Timestamp >= '2024-09-01' AND Timestamp < '2024-10-01'
ORDER BY Timestamp;

-- Calculate average spread
SELECT AVG(Ask - Bid) * 10000 as avg_spread_pips
FROM standard_ticks
WHERE Timestamp >= '2024-09-01';

-- Compare tick counts by hour
SELECT
    DATE_TRUNC('hour', Timestamp) as hour,
    COUNT(*) as tick_count
FROM standard_ticks
WHERE Timestamp >= '2024-09-01'
GROUP BY hour
ORDER BY hour;

Table 3: ohlc_1m

Purpose: Pre-computed 1-minute OHLC bars with Phase7 30-column schema (v1.6.0)

Use Case: Primary data source for backtesting and technical analysis

Generation Method: Aggregated from raw_spread_ticks and standard_ticks tables

Schema (Phase7 30-Column - v1.6.0)

Column Definitions: See table below for complete column definitions with types and descriptions.

Quick Summary: 30 columns comprising:

  • Core OHLC (5): Timestamp, Open, High, Low, Close
  • Dual Spreads (2): raw_spread_avg, standard_spread_avg
  • Dual Tick Counts (2): tick_count_raw_spread, tick_count_standard
  • Normalized Metrics (4): range_per_spread, range_per_tick, body_per_spread, body_per_tick (v1.2.0+)
  • Timezone/Session Tracking (4): ny_hour, london_hour, ny_session, london_session (v1.3.0+)
  • Holiday Tracking (3): is_us_holiday, is_uk_holiday, is_major_holiday (v1.4.0+)
  • Global Exchange Sessions (10): is_nyse_session, is_lse_session, is_xswx_session, is_xfra_session, is_xtse_session, is_xnze_session, is_xtks_session, is_xasx_session, is_xhkg_session, is_xses_session covering 24-hour forex trading - checks trading day, hours, and lunch breaks (v1.6.0+lunch breaks)

Schema Version: v1.6.0

Architecture: Exchange Registry Pattern (v1.6.0) - Session columns dynamically generated from centralized EXCHANGES dict in exchanges.py

Indexes

  • Automatic Index: PRIMARY KEY constraint on Timestamp automatically creates an index for efficient queries
  • No explicit index creation needed - DuckDB optimizes PRIMARY KEY columns automatically

Constraints

  • PRIMARY KEY on Timestamp: Ensures unique 1-minute bars
  • NOT NULL constraints: Only on OHLC price columns (Open, High, Low, Close) to ensure price data integrity
  • NULLABLE columns:
    • Spread and tick count columns (raw_spread_avg, standard_spread_avg, tick_count_raw_spread, tick_count_standard) can be NULL when LEFT JOIN with Standard ticks yields no matches for that minute
    • Normalized metrics (range_per_spread, range_per_tick, body_per_spread, body_per_tick) are NULL when Standard data is missing or when division by zero would occur

Table Comments

Stored in Database: All 13 column comments are automatically added when the database is created, dynamically generated from schema.py (see OHLCSchema.get_column_comment_sqls()).

COMMENT ON TABLE ohlc_1m IS
'Phase7 v1.6.0 1-minute OHLC bars with 10 global exchange sessions (BID-only from Raw_Spread, dual-variant spreads and tick counts, normalized metrics).
 OHLC Source: Raw_Spread BID prices. Spreads: Dual-variant (Raw_Spread + Standard).';

-- All 30 column comments (see schema.py for complete definitions)
-- Examples:
COMMENT ON COLUMN ohlc_1m.Timestamp IS 'Minute-aligned bar timestamp';
COMMENT ON COLUMN ohlc_1m.Open IS 'Opening price (first Raw_Spread Bid)';
COMMENT ON COLUMN ohlc_1m.range_per_spread IS '(High-Low)/standard_spread_avg - Range normalized by spread (NULL if no Standard ticks)';
COMMENT ON COLUMN ohlc_1m.is_nyse_session IS '1 if during New York Stock Exchange trading hours (09:30-16:00 America/New_York), 0 otherwise - checks both trading day and time via exchange_calendars XNYS';
-- ... (see schema.py OHLCSchema.COLUMNS for all 30 column comments)

Retrieve Comments:

SELECT table_name, column_name, data_type, comment
FROM duckdb_columns()
WHERE table_name = 'ohlc_1m';

Generation Query

INSERT INTO ohlc_1m
SELECT
    DATE_TRUNC('minute', r.Timestamp) as Timestamp,
    FIRST(r.Bid ORDER BY r.Timestamp) as Open,
    MAX(r.Bid) as High,
    MIN(r.Bid) as Low,
    LAST(r.Bid ORDER BY r.Timestamp) as Close,
    AVG(r.Ask - r.Bid) as raw_spread_avg,
    AVG(s.Ask - s.Bid) as standard_spread_avg,
    COUNT(r.Timestamp) as tick_count_raw_spread,
    COUNT(s.Timestamp) as tick_count_standard,
    -- Normalized spread metrics (v1.2.0) - NULL-safe calculations
    CASE
        WHEN AVG(s.Ask - s.Bid) > 0
        THEN (MAX(r.Bid) - MIN(r.Bid)) / AVG(s.Ask - s.Bid)
        ELSE NULL
    END as range_per_spread,
    CASE
        WHEN COUNT(s.Timestamp) > 0
        THEN (MAX(r.Bid) - MIN(r.Bid)) / COUNT(s.Timestamp)
        ELSE NULL
    END as range_per_tick,
    CASE
        WHEN AVG(s.Ask - s.Bid) > 0
        THEN ABS(LAST(r.Bid ORDER BY r.Timestamp) - FIRST(r.Bid ORDER BY r.Timestamp)) / AVG(s.Ask - s.Bid)
        ELSE NULL
    END as body_per_spread,
    CASE
        WHEN COUNT(s.Timestamp) > 0
        THEN ABS(LAST(r.Bid ORDER BY r.Timestamp) - FIRST(r.Bid ORDER BY r.Timestamp)) / COUNT(s.Timestamp)
        ELSE NULL
    END as body_per_tick
FROM raw_spread_ticks r
LEFT JOIN standard_ticks s
    ON DATE_TRUNC('minute', r.Timestamp) = DATE_TRUNC('minute', s.Timestamp)
GROUP BY DATE_TRUNC('minute', r.Timestamp)
ORDER BY Timestamp;

Normalized Spread Metrics (v1.2.0+)

Purpose: Normalize OHLC movements by market activity (spread and tick count) to enable comparison across different market conditions.

Calculations (NULL-safe to handle missing Standard data):

  1. range_per_spread = (High - Low) / standard_spread_avg

    • Interpretation: How many spreads wide is the bar's range?
    • High values: Large price movement relative to typical spread
    • Example: If range = 0.0002 and avg_spread = 0.00004, then range_per_spread = 5.0 spreads

  2. range_per_tick = (High - Low) / tick_count_standard

    • Interpretation: Average price movement per tick
    • High values: High volatility with few ticks (fast moves)
    • Low values: Choppy price action with many ticks

  3. body_per_spread = abs(Close - Open) / standard_spread_avg

    • Interpretation: How many spreads is the directional movement?
    • High values: Strong directional movement relative to spread
    • Low values: Indecision/ranging behavior

  4. body_per_tick = abs(Close - Open) / tick_count_standard

    • Interpretation: Directional efficiency (movement per tick)
    • High values: Strong directional movement per tick
    • Low values: Many ticks but little net movement

NULL Handling: All normalized metrics are NULL when standard_spread_avg or tick_count_standard are 0 or NULL (occurs when LEFT JOIN yields no Standard ticks for that minute).

Use Cases:

  • Identify high-volatility bars independent of absolute price levels
  • Compare market activity across different sessions
  • Filter for significant directional moves
  • Detect ranging vs trending market conditions

Global Exchange Sessions (v1.6.0)

Purpose: Binary flags indicating if timestamp falls during actual trading hours for 10 major global exchanges covering 24-hour forex trading.

Exchanges Covered:

  • North America: XNYS (NYSE - USD, 9:30-16:00 ET), XTSE (TSX - CAD, 9:30-16:00 ET)
  • Europe: XLON (LSE - GBP, 8:00-16:30 GMT), XSWX (SIX Swiss - CHF, 9:00-17:30 CET), XFRA (Frankfurt - EUR, 9:00-17:30 CET)
  • Asia-Pacific: XNZE (NZE - NZD, 10:00-16:45 NZST), XTKS (TSE - JPY, 9:00-11:30 & 12:30-15:30 JST with lunch 11:30-12:30), XASX (ASX - AUD, 10:00-16:00 AEST), XHKG (HKEX - HKD, 9:30-12:00 & 13:00-16:00 HKT with lunch 12:00-13:00), XSES (SGX - SGD, 9:00-12:00 & 13:00-17:00 SGT with lunch 12:00-13:00)

Calculation (via exchange_calendars library):

  • 1: Exchange is open and within trading hours (checks day, time, AND lunch breaks)
  • 0: Exchange is closed (weekend, holiday, outside trading hours, OR during lunch break)

Lunch Breaks (automatically handled by exchange_calendars):

  • Tokyo (XTKS): 11:30-12:30 JST (session flags = 0 during lunch)
  • Hong Kong (XHKG): 12:00-13:00 HKT (session flags = 0 during lunch)
  • Singapore (XSES): 12:00-13:00 SGT (session flags = 0 during lunch)
  • Western exchanges: No lunch breaks (continuous trading)

Architecture: Exchange Registry Pattern

  • Session columns dynamically generated from EXCHANGES dict in exchanges.py
  • Adding new exchange requires only 1-line change to registry
  • Automatic DST handling via IANA timezone database

Use Cases:

  • Filter bars during specific regional trading sessions (e.g., Asian session only)
  • Identify overlapping sessions (e.g., London-New York overlap)
  • Currency-specific filtering (e.g., AUD pairs during XASX sessions)
  • Holiday impact analysis (e.g., major holidays when both XNYS and XLON closed)

Example Queries:

-- Filter for Asian session hours (XTKS, XASX, XHKG, XSES active)
SELECT * FROM ohlc_1m
WHERE Timestamp >= '2024-09-01'
  AND (is_xtks_session = 1 OR is_xasx_session = 1 OR is_xhkg_session = 1 OR is_xses_session = 1);

-- Find EUR/USD bars during London-New York overlap
SELECT * FROM ohlc_1m
WHERE Timestamp >= '2024-09-01'
  AND is_lse_session = 1
  AND is_nyse_session = 1;

-- Identify major holidays (both XNYS and XLON closed)
SELECT DATE(Timestamp) as date, COUNT(*) as bars
FROM ohlc_1m
WHERE Timestamp >= '2024-01-01'
  AND is_nyse_session = 0
  AND is_lse_session = 0
GROUP BY DATE(Timestamp);

Characteristics

  • Timeframe: 1-minute bars (minute-aligned)
  • OHLC Methodology: BID-only from Raw_Spread variant
  • Dual Spreads: Both Raw_Spread and Standard spreads tracked
  • Exchange Sessions: 10 global exchanges covering 24-hour forex trading with hour-based detection (v1.6.0)
  • Holiday Detection: Official holidays via exchange_calendars library (v1.4.0+)
  • Monthly Volume: ~30K - 32K bars per month
  • Storage Size: ~3 MB per year

Example Data

Timestamp            | Open    | High    | Low     | Close   | raw_spread_avg | standard_spread_avg | tick_count_raw_spread | tick_count_standard | range_per_spread | range_per_tick | body_per_spread | body_per_tick | ny_hour | london_hour | ny_session | london_session | is_us_holiday | is_uk_holiday | is_major_holiday | is_nyse_session | is_lse_session | is_xswx_session | is_xfra_session | is_xtse_session | is_xnze_session | is_xtks_session | is_xasx_session | is_xhkg_session | is_xses_session
---------------------|---------|---------|---------|---------|----------------|---------------------|-----------------------|---------------------|------------------|----------------|-----------------|---------------|---------|-------------|------------|---------------|---------------|---------------|------------------|-----------------|---------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------
2024-09-01 14:05:00  | 1.10500 | 1.10520 | 1.10495 | 1.10515 | 0.00001        | 0.00004             | 25                    | 28                  | 6.25             | 0.00089        | 3.75            | 0.00054       | 10      | 15          | NY_Session | London_Session | 0             | 0             | 0                | 1               | 1              | 1               | 1               | 1               | 0               | 0               | 0               | 1               | 1
2024-09-01 14:06:00  | 1.10515 | 1.10525 | 1.10510 | 1.10520 | 0.00001        | 0.00004             | 23                    | 26                  | 3.75             | 0.00058        | 1.25            | 0.00019       | 10      | 15          | NY_Session | London_Session | 0             | 0             | 0                | 1               | 1              | 1               | 1               | 1               | 0               | 0               | 0               | 1               | 1
2024-09-01 14:07:00  | 1.10520 | 1.10530 | 1.10515 | 1.10525 | 0.00001        | 0.00004             | 27                    | 30                  | 3.75             | 0.00050        | 1.25            | 0.00017       | 10      | 15          | NY_Session | London_Session | 0             | 0             | 0                | 1               | 1              | 1               | 1               | 1               | 0               | 0               | 0               | 1               | 1

Notes:

  • All 30 columns shown including normalized metrics (v1.2.0+), timezone/session tracking (v1.3.0+), holiday flags (v1.4.0+), and 10 global exchange sessions with hour-based detection (v1.6.0)
  • range_per_spread = (High-Low) / standard_spread_avg (e.g., 0.00025 / 0.00004 = 6.25 spreads)
  • range_per_tick = (High-Low) / tick_count_standard (e.g., 0.00025 / 28 = 0.00089)
  • body_per_spread = abs(Close-Open) / standard_spread_avg (e.g., 0.00015 / 0.00004 = 3.75 spreads)
  • body_per_tick = abs(Close-Open) / tick_count_standard (e.g., 0.00015 / 28 = 0.00054)
  • Example timestamp (14:05 UTC): NY_Session (10:05 EDT), London_Session (15:05 BST), both XNYS and XLON open and within trading hours (is_nyse_session=1, is_lse_session=1)

Query Examples

-- Get 1-minute bars for September 2024 (all 30 columns)
SELECT * FROM ohlc_1m
WHERE Timestamp >= '2024-09-01' AND Timestamp < '2024-10-01'
ORDER BY Timestamp;

-- Resample to 1-hour bars (all 30 columns)
SELECT
    DATE_TRUNC('hour', Timestamp) as hour,
    FIRST(Open ORDER BY Timestamp) as Open,
    MAX(High) as High,
    MIN(Low) as Low,
    LAST(Close ORDER BY Timestamp) as Close,
    AVG(raw_spread_avg) as raw_spread_avg,
    AVG(standard_spread_avg) as standard_spread_avg,
    SUM(tick_count_raw_spread) as tick_count_raw_spread,
    SUM(tick_count_standard) as tick_count_standard,
    AVG(range_per_spread) as range_per_spread,
    AVG(range_per_tick) as range_per_tick,
    AVG(body_per_spread) as body_per_spread,
    AVG(body_per_tick) as body_per_tick,
    MIN(ny_hour) as ny_hour,
    MIN(london_hour) as london_hour,
    MIN(ny_session) as ny_session,
    MIN(london_session) as london_session,
    MAX(is_us_holiday) as is_us_holiday,
    MAX(is_uk_holiday) as is_uk_holiday,
    MAX(is_major_holiday) as is_major_holiday,
    MAX(is_nyse_session) as is_nyse_session,
    MAX(is_lse_session) as is_lse_session,
    MAX(is_xswx_session) as is_xswx_session,
    MAX(is_xfra_session) as is_xfra_session,
    MAX(is_xtse_session) as is_xtse_session,
    MAX(is_xnze_session) as is_xnze_session,
    MAX(is_xtks_session) as is_xtks_session,
    MAX(is_xasx_session) as is_xasx_session,
    MAX(is_xhkg_session) as is_xhkg_session,
    MAX(is_xses_session) as is_xses_session
FROM ohlc_1m
WHERE Timestamp >= '2024-09-01'
GROUP BY hour
ORDER BY hour;

-- Filter high-volatility bars (range > 5 spreads)
SELECT * FROM ohlc_1m
WHERE Timestamp >= '2024-09-01'
  AND range_per_spread > 5.0
ORDER BY range_per_spread DESC
LIMIT 10;

-- Find strong directional bars (body > 3 spreads, high body efficiency)
SELECT * FROM ohlc_1m
WHERE Timestamp >= '2024-09-01'
  AND body_per_spread > 3.0
  AND body_per_tick > 0.0005
ORDER BY body_per_spread DESC
LIMIT 10;

-- Compare normalized metrics across time periods
SELECT
    DATE_TRUNC('day', Timestamp) as day,
    COUNT(*) as bar_count,
    AVG(range_per_spread) as avg_range_per_spread,
    AVG(body_per_spread) as avg_body_per_spread,
    AVG(raw_spread_avg) * 10000 as avg_raw_spread_pips
FROM ohlc_1m
WHERE Timestamp >= '2024-09-01'
  AND range_per_spread IS NOT NULL  -- Ensure Standard data exists
GROUP BY day
ORDER BY day;

Table 4: metadata

Purpose: Tracks database coverage, update history, and statistics

Use Case: Quick coverage checks without scanning entire database

Schema

Column Type Constraints Description
key VARCHAR PRIMARY KEY Metadata key identifier
value VARCHAR NOT NULL Metadata value (string representation)
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() Last update timestamp

Common Metadata Keys

Key Description Example Value
earliest_date Earliest tick timestamp 2022-01-01
latest_date Latest tick timestamp 2025-10-12
last_update Last database update timestamp 2025-10-12T15:30Z
total_months Number of months of data 46
raw_spread_tick_count Total Raw_Spread ticks 18619662
standard_tick_count Total Standard ticks 19596407
ohlc_bar_count Total 1-minute OHLC bars 413453
database_version Schema version 2.0.0

Example Data

key                      | value              | updated_at
-------------------------|--------------------|--------------------------
earliest_date            | 2024-09-01         | 2025-10-12 15:30:00+00
latest_date              | 2025-10-10         | 2025-10-12 15:30:00+00
last_update              | 2025-10-12T15:30Z  | 2025-10-12 15:30:00+00
total_months             | 13                 | 2025-10-12 15:30:00+00
raw_spread_tick_count    | 18619662           | 2025-10-12 15:30:00+00
standard_tick_count      | 19596407           | 2025-10-12 15:30:00+00
ohlc_bar_count           | 413453             | 2025-10-12 15:30:00+00
database_version         | 2.0.0              | 2025-10-12 15:30:00+00

Query Examples

-- Get all metadata
SELECT * FROM metadata ORDER BY key;

-- Get specific metadata
SELECT value FROM metadata WHERE key = 'earliest_date';

-- Get coverage statistics
SELECT
    (SELECT value FROM metadata WHERE key = 'earliest_date') as earliest,
    (SELECT value FROM metadata WHERE key = 'latest_date') as latest,
    (SELECT value FROM metadata WHERE key = 'total_months') as months,
    (SELECT value FROM metadata WHERE key = 'raw_spread_tick_count') as raw_ticks,
    (SELECT value FROM metadata WHERE key = 'ohlc_bar_count') as ohlc_bars;

DuckDB Introspection & Self-Documentation

Feature: DuckDB provides built-in metadata functions and COMMENT ON statements for self-documenting databases

Querying Table Information

-- Get all tables with comments
SELECT
    schema_name,
    table_name,
    estimated_size,
    column_count,
    comment
FROM duckdb_tables()
WHERE database_name = current_database()
ORDER BY table_name;

Querying Column Information

-- Get all columns with types and comments
SELECT
    table_name,
    column_name,
    data_type,
    is_nullable,
    comment
FROM duckdb_columns()
WHERE database_name = current_database()
ORDER BY table_name, column_index;

Querying Constraints

-- Get all PRIMARY KEY constraints
SELECT
    table_name,
    constraint_type,
    constraint_text
FROM duckdb_constraints()
WHERE database_name = current_database()
ORDER BY table_name;

Comprehensive Schema Introspection

-- Get complete schema information for a table
SELECT
    c.table_name,
    c.column_name,
    c.data_type,
    c.is_nullable,
    c.comment as column_comment,
    t.comment as table_comment,
    con.constraint_type
FROM duckdb_columns() c
LEFT JOIN duckdb_tables() t
    ON c.table_name = t.table_name
LEFT JOIN duckdb_constraints() con
    ON c.table_name = con.table_name
    AND c.column_name = ANY(string_split(con.constraint_text, ','))
WHERE c.table_name = 'raw_spread_ticks'
ORDER BY c.column_index;

Benefits of Self-Documentation

  1. Machine-Readable: BI tools, IDEs, and scripts can query metadata programmatically
  2. Version-Controlled: Comments are stored in database schema, not external files
  3. Single Source of Truth: Documentation lives with the data
  4. Tool Integration: Modern database clients display inline help from comments
  5. No External Dependencies: Schema is fully self-explanatory

Implementation

All tables and columns have embedded documentation via COMMENT ON statements:

  • Table comments: Purpose, data source URLs, characteristics
  • Column comments: Type, constraints, nullability explanations

See processor.py lines 138-215 for implementation details.

Table Relationships

Conceptual Relationship Diagram

┌─────────────────────┐
│  raw_spread_ticks   │
│  (Primary Source)   │
│  - Timestamp (PK)   │
│  - Bid              │
│  - Ask              │
└──────────┬──────────┘
           │
           │ LEFT JOIN on DATE_TRUNC('minute', Timestamp)
           │
           ├──────────────────────────┐
           │                          │
           ▼                          ▼
┌─────────────────────┐    ┌─────────────────────┐
│  standard_ticks     │    │     ohlc_1m         │
│  (Reference)        │    │  (Pre-computed)     │
│  - Timestamp (PK)   │    │  - Timestamp (PK)   │
│  - Bid              │    │  - Open, High...    │
│  - Ask              │    │  - Dual Spreads     │
└─────────────────────┘    │  - Dual Tick Counts │
                           └─────────────────────┘
                                     │
                                     │ Tracked by
                                     ▼
                           ┌─────────────────────┐
                           │     metadata        │
                           │  (Coverage Info)    │
                           │  - key (PK)         │
                           │  - value            │
                           │  - updated_at       │
                           └─────────────────────┘

Relationship Details

  1. raw_spread_ticks → ohlc_1m: Raw_Spread ticks are aggregated to create OHLC bars
  2. standard_ticks → ohlc_1m: Standard ticks are joined to add reference spread statistics
  3. All tables → metadata: Metadata tracks statistics for all tables

Storage Projections

Per Instrument (EURUSD)

Duration Raw_Spread Ticks Standard Ticks OHLC Bars Total Size
1 month ~1.5M ~1.6M ~32K ~160 MB
1 year ~18M ~19M ~384K ~1.9 GB
3 years ~54M ~57M ~1.15M ~5.7 GB

Multi-Instrument

Instruments Duration Total Size
EURUSD 3 years ~5.7 GB
EURUSD + XAUUSD 3 years ~11.4 GB
EURUSD + XAUUSD + GBPUSD 3 years ~17.1 GB

Validation: Based on real EURUSD data (13 months = 2.08 GB)

Query Performance

Performance Benchmarks (13 months of data)

Operation Time Notes
Query 880K ticks (1 month) <15ms Indexed timestamp queries
Query 1m OHLC (1 month) <10ms 32K bars
Resample 1m → 1h (1 month) <15ms 32K → 720 bars
Resample 1m → 1d (1 year) <20ms 384K → 365 bars
Count all ticks <50ms Full table scan
Get coverage metadata <5ms Small metadata table

Index Strategy

All tables use Timestamp indexes (implicitly created by PRIMARY KEY constraints) for optimal date range queries:

-- Automatically optimized by DuckDB
SELECT * FROM raw_spread_ticks
WHERE Timestamp >= '2024-09-01' AND Timestamp < '2024-10-01';

Maintenance Operations

Incremental Updates

When new data is added, the database automatically:

  1. Downloads missing months from Exness
  2. Appends ticks to raw_spread_ticks and standard_ticks (PRIMARY KEY prevents duplicates)
  3. Regenerates OHLC for new date ranges
  4. Updates metadata table

OHLC Regeneration

OHLC bars are regenerated only for affected date ranges:

-- Delete old OHLC for date range
DELETE FROM ohlc_1m
WHERE Timestamp >= '2024-09-01' AND Timestamp < '2024-10-01';

-- Regenerate OHLC for date range
INSERT INTO ohlc_1m
SELECT ... FROM raw_spread_ticks r
LEFT JOIN standard_ticks s ON ...
WHERE r.Timestamp >= '2024-09-01' AND r.Timestamp < '2024-10-01';

Database Integrity

  • PRIMARY KEY constraints: Prevent duplicate ticks during incremental updates
  • NOT NULL constraints: Applied to critical columns (Timestamp, Bid, Ask, OHLC prices) to ensure core data integrity
  • NULLABLE columns: Spread and tick count columns in ohlc_1m allow NULL when LEFT JOIN yields no matches
  • Index maintenance: Automatic by DuckDB

Data Quality

Validation Checks

  1. No Duplicates: PRIMARY KEY constraints on Timestamp
  2. No Missing Minutes: OHLC bars should have no gaps during market hours
  3. Price Sanity: High ≥ Low, Open/Close within [Low, High]
  4. Spread Validity: Raw_Spread avg ≥ 0 when NOT NULL, Standard avg > 0 when NOT NULL
  5. Tick Count: Raw_Spread tick_count should be > 0 for each bar; Standard tick_count can be NULL (no matching Standard ticks for that minute)

Known Characteristics

  • Zero-Spreads: Raw_Spread ~98%, Standard 0%
  • Tick Frequency: Variable (1µs to 130s intervals)
  • Market Hours: Sunday 14:00 UTC to Friday 21:00 UTC
  • Weekend Gaps: No ticks during market closure

Access Patterns

Read-Only Access

import duckdb

# Connect in read-only mode
conn = duckdb.connect('~/eon/exness-data/eurusd.duckdb', read_only=True)

# Query data
df = conn.execute("""
    SELECT * FROM ohlc_1m
    WHERE Timestamp >= '2024-09-01'
    ORDER BY Timestamp
""").df()

conn.close()

Write Access (Incremental Updates)

from exness_data_preprocess import ExnessDataProcessor

processor = ExnessDataProcessor()

# Automatic incremental update
result = processor.update_data(
    pair="EURUSD",
    start_date="2022-01-01",
    delete_zip=True
)

print(f"Months added: {result['months_added']}")

Version History

v1.6.0 (2025-10-17) - Current

  • Fixed: Exchange session columns now check trading HOURS not just trading DAYS (semantic correction)
  • Breaking Change: YES - is_*_session columns now return 1 only during actual trading hours, not all day
  • Impact: Databases must be regenerated with processor.update_data() to get corrected session values
  • Implementation: Added open_hour, open_minute, close_hour, close_minute to ExchangeConfig in exchanges.py
  • Detection Logic: session_detector.py now performs timezone conversion and hour checks for all 10 exchanges
  • OHLC Schema: 30 columns (unchanged column count, corrected semantics)
  • Migration: See audit document at docs/plans/EXCHANGE_SESSION_AUDIT_2025-10-17.md for complete details

v1.5.0 (2025-10-15)

  • Added: 8 exchange session columns (replaced is_nyse_session, is_lse_session with 10 global exchanges)
  • Architecture: Exchange Registry Pattern - session columns dynamically generated from EXCHANGES dict
  • OHLC Schema: 30 columns (Phase7 with 10 global exchange sessions)
  • Exchanges: XNYS (NYSE), XLON (LSE), XSWX (SIX Swiss), XFRA (Frankfurt), XTSE (TSX), XNZE (NZE), XTKS (TSE), XASX (ASX), XHKG (HKEX), XSES (SGX)
  • Coverage: 24-hour forex trading with North America, Europe, and Asia-Pacific exchanges
  • Breaking Change: None - is_nyse_session and is_lse_session retained for backward compatibility (now part of 10-exchange set)

v1.4.0 (2025-10-14)

  • Added: 3 holiday tracking columns (is_us_holiday, is_uk_holiday, is_major_holiday)
  • Added: 2 session flags (is_nyse_session, is_lse_session)
  • OHLC Schema: 22 columns (Phase7 with holiday tracking)
  • Architecture: Dynamic holiday detection via exchange_calendars library
  • Breaking Change: None - additive only

v1.3.0 (2025-10-14)

  • Added: 4 timezone/session tracking columns (ny_hour, london_hour, ny_session, london_session)
  • OHLC Schema: 17 columns (Phase7 with timezone tracking)
  • Architecture: Automatic DST handling via DuckDB AT TIME ZONE
  • Breaking Change: None - additive only

v1.2.0 (2025-10-14)

  • Added: 4 normalized spread metrics to OHLC schema (range_per_spread, range_per_tick, body_per_spread, body_per_tick)
  • Refactored: Centralized schema definition in schema.py module
  • OHLC Schema: 13 columns (Phase7 with normalized metrics)
  • Architecture: Single source of truth for all schema definitions
  • Breaking Change: None - additive only (existing Phase7 columns unchanged)

v1.1.0 (2025-10-12) - Phase7 Initial

  • Added: Phase7 initial OHLC schema with dual spreads and dual tick counts
  • OHLC Schema: BID-only OHLC from Raw_Spread with dual-variant metrics

v2.0.0 (2025-10-12) - Unified Architecture

  • Change: Single-file per instrument (not monthly files)
  • Added: Dual-variant storage (Raw_Spread + Standard)
  • Added: PRIMARY KEY constraints for duplicate prevention
  • Added: Metadata table for coverage tracking
  • Added: Incremental update support

v1.0.0 (Legacy)

  • Structure: Monthly files (eurusd_2024_09.duckdb)
  • OHLC Schema: 7 columns (no dual spreads)
  • Variants: Raw_Spread only
  • Updates: Manual monthly processing

Related Documentation

Last Updated: 2025-10-17 Maintainer: Terry Li terry@eonlabs.com Schema Version: v1.6.0 (Phase7 30-column OHLC with 10 global exchange sessions - trading hour detection)