Accepted - 2026-01-23
Reference data for cryptocurrency instruments changes over time, and these changes have specific characteristics that standard temporal modeling approaches fail to capture:
-
Announced vs Effective Timing: Exchanges often announce specification changes (e.g., tick size adjustments) days before they become effective. For example:
- Binance announces on Jan 10: "Tick size for BTCUSDT will change to 0.05 effective Jan 15 00:00 UTC"
- The change is not effective until Jan 15, but we learn about it on Jan 10
-
Late Corrections: Exchanges sometimes issue corrections after the initial announcement:
- Initial announcement: "Effective Jan 15"
- Correction on Jan 16: "Actually was effective Jan 14 23:00 UTC, not Jan 15"
-
Regulatory Requirements: Financial regulations often require accurate historical reconstruction:
- "What was the minimum tick size for BTCUSDT at 2024-01-14 22:30 UTC?"
- Must account for later corrections to provide accurate answers
-
Audit Trail: Need complete lineage showing:
- When we first learned about a change (system time)
- When the change was actually effective in the market (business time)
- Who made the change and why
Standard Approaches and Their Limitations:
| Approach | Business Time | System Time | Late Corrections | Audit Trail |
|---|---|---|---|---|
| Snapshot (current k2) | ❌ No | ✅ Yes | ❌ Overwrites | |
| SCD Type 2 (system time) | ❌ No | ✅ Yes | ❌ Corrupts history | ✅ Yes |
| SCD Type 2 (business time) | ✅ Yes | ❌ No | ✅ Yes | ❌ No system time |
| Bitemporal (selected) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Complete |
Real-World Example:
Timeline:
Jan 10, 09:00 - Binance announces tick_size change effective Jan 15
Jan 11, 15:00 - We ingest announcement (record_created_at = Jan 11 15:00, valid_from = Jan 15 00:00)
Jan 15, 00:00 - Change goes live (valid_from timestamp)
Jan 16, 08:00 - Exchange corrects: "Actually effective Jan 14 23:00, not Jan 15"
- New record inserted (record_created_at = Jan 16 08:00, valid_from = Jan 14 23:00)
Query: "What was tick_size on Jan 14 22:00?"
Answer: Old value (because corrected valid_from is Jan 14 23:00)
Query: "What did we THINK tick_size was on Jan 14 based on data as of Jan 12?"
Answer: Old value (because record_created_at for new spec was Jan 11, but valid_from was Jan 15)
Implement bitemporal modeling with four timestamp columns tracking both business time and system time:
CREATE TABLE refdata.silver_instruments (
instrument_sk STRING PRIMARY KEY, -- Surrogate key
-- Natural key
exchange STRING NOT NULL,
symbol STRING NOT NULL,
-- ⭐ BITEMPORAL DIMENSIONS
-- Business time: When the specification was effective in reality
valid_from TIMESTAMP NOT NULL, -- Effective start time
valid_to TIMESTAMP, -- Effective end time (NULL = current)
-- System time: When we learned about it
record_created_at TIMESTAMP NOT NULL, -- When first recorded in our system
record_updated_at TIMESTAMP, -- When last modified (NULL = never updated)
-- Instrument specifications
tick_size DECIMAL(38, 18),
lot_size DECIMAL(38, 18),
-- ... other fields
-- Audit fields
change_reason STRING, -- 'initial_load', 'spec_update', 'manual_correction'
changed_by STRING -- 'ingestion_job', 'admin_user_id'
);1. Point-in-Time Query (most common):
-- "What was the tick_size effective on Jan 14 22:00?"
SELECT tick_size
FROM refdata.silver_instruments
WHERE exchange = 'binance'
AND symbol = 'BTCUSDT'
AND valid_from <= '2024-01-14 22:00:00'
AND (valid_to IS NULL OR valid_to > '2024-01-14 22:00:00')
ORDER BY record_created_at DESC -- Latest correction wins
LIMIT 1;2. Historical Reconstruction:
-- "What did we THINK was effective on Jan 14, based on knowledge as of Jan 12?"
SELECT tick_size
FROM refdata.silver_instruments
WHERE exchange = 'binance'
AND symbol = 'BTCUSDT'
AND valid_from <= '2024-01-14 22:00:00'
AND (valid_to IS NULL OR valid_to > '2024-01-14 22:00:00')
AND record_created_at <= '2024-01-12 23:59:59' -- Knowledge cutoff
ORDER BY record_created_at DESC
LIMIT 1;3. Audit Trail:
-- "Show all changes to BTCUSDT specifications"
SELECT
valid_from,
valid_to,
tick_size,
record_created_at,
change_reason,
changed_by
FROM refdata.silver_instruments
WHERE exchange = 'binance'
AND symbol = 'BTCUSDT'
ORDER BY record_created_at DESC;Traditional SCD Type 2 uses effective_date and end_date (business time only). We enhance it:
-- Standard SCD Type 2 record lifecycle
INSERT: valid_from = new_effective_date, valid_to = NULL
UPDATE: Old record gets valid_to set, new record inserted with valid_to = NULL
-- Bitemporal addition
record_created_at = CURRENT_TIMESTAMP() -- Tracks when we learned about it
record_updated_at = NULL (or timestamp of last correction)When a correction arrives, we do NOT update the old record. Instead:
- Insert a NEW record with corrected
valid_fromand currentrecord_created_at - Old record remains unchanged (preserves what we thought at that time)
- Queries using
ORDER BY record_created_at DESCautomatically get the latest correction
Example:
-- Original record (announced Jan 10, ingested Jan 11)
INSERT INTO silver_instruments VALUES (
'sk_001', 'binance', 'BTCUSDT',
'2024-01-15 00:00:00', NULL, -- Business time
'2024-01-11 15:00:00', NULL, -- System time
0.05, ...
);
-- Correction arrives Jan 16: "Actually effective Jan 14 23:00"
-- DO NOT UPDATE! Instead, insert new record:
INSERT INTO silver_instruments VALUES (
'sk_002', 'binance', 'BTCUSDT',
'2024-01-14 23:00:00', NULL, -- Corrected business time
'2024-01-16 08:00:00', NULL, -- Current system time
0.05, ...
);
-- Queries will use sk_002 (latest record_created_at)
-- But sk_001 is preserved for audit trail✅ Accurate Historical Reconstruction: Can answer "what was effective at time T?" accounting for late corrections
✅ Complete Audit Trail: Preserves full lineage of changes, meeting regulatory requirements
✅ Immutable History: Late corrections don't overwrite; they add new records
✅ Flexible Querying:
- Point-in-time: Use business time only
- As-of queries: Use business time + system time filter
- Audit: Show all records ordered by system time
✅ Regulatory Compliance: Supports MiFID II, Dodd-Frank requirements for historical accuracy
❌ Increased Complexity: Queries require ORDER BY record_created_at DESC LIMIT 1 pattern
❌ Storage Overhead: Corrections create new records instead of updates (~10-20% more storage)
❌ Learning Curve: Developers must understand bitemporal semantics
❌ DBT Customization: Standard dbt-utils.snapshots insufficient; requires custom macro
1. Encapsulate Complexity:
-- Create DBT macro for bitemporal queries
{% macro bitemporal_query(table, filters, as_of_business, as_of_system=none) %}
SELECT * FROM {{ table }}
WHERE {{ filters }}
AND valid_from <= {{ as_of_business }}
AND (valid_to IS NULL OR valid_to > {{ as_of_business }})
{% if as_of_system %}
AND record_created_at <= {{ as_of_system }}
{% endif %}
ORDER BY record_created_at DESC
LIMIT 1
{% endmacro %}2. Storage Optimization:
- Partition by
months(valid_from)(not daily; reference data changes infrequently) - Use Iceberg's metadata filtering (no full table scans)
- Compress with Zstandard (30-50% reduction)
3. Developer Documentation:
- Provide query templates in docs/runbooks/
- Include examples in API documentation
- Create helper functions in Python query layer
4. Testing:
- Dedicated test suite for bitemporal logic (
tests/unit/test_bitemporal_queries.py) - Test late corrections explicitly
- Verify audit trail completeness
Approach: Use Iceberg's native snapshot isolation for temporal queries
Pros:
- Simple: No additional timestamp columns
- Built-in: Iceberg handles versioning
Cons:
- ❌ No business time tracking (can't represent "effective Jan 15")
- ❌ Snapshots track file state, not logical effective dates
- ❌ Late corrections would overwrite historical snapshots
- ❌ Can't answer "what was effective on date X?" without additional metadata
Why Rejected: Doesn't support announced-but-not-yet-effective changes
Approach: Single temporal dimension using valid_from/valid_to
Pros:
- ✅ Simpler than bitemporal (2 columns vs 4)
- ✅ Standard DBT snapshot pattern
Cons:
- ❌ No system time tracking (can't answer "what did we know on date X?")
- ❌ Late corrections would UPDATE old records (losing audit trail)
- ❌ Can't distinguish "when effective" from "when learned"
Why Rejected: Insufficient for regulatory audit requirements
Approach: Store all changes as immutable events, rebuild state on query
Pros:
- ✅ Complete audit trail
- ✅ Immutable by design
Cons:
- ❌ Query performance: Must replay events to get current state
- ❌ Complexity: Requires event store + materialized views
- ❌ Overkill: Reference data changes infrequently (not event-driven domain)
Why Rejected: Over-engineered for reference data use case
-- dbt/macros/bitemporal_scd2.sql
{% macro bitemporal_scd2(source_table, target_table, unique_key, valid_from_col) %}
-- Close old records where specifications changed
UPDATE {{ target_table }} AS target
SET valid_to = source.{{ valid_from_col }} - INTERVAL '1 second'
FROM {{ source_table }} AS source
WHERE target.{{ unique_key }} = source.{{ unique_key }}
AND target.valid_to IS NULL -- Only update current records
AND (
-- Detect changes in specifications
target.tick_size != source.tick_size OR
target.lot_size != source.lot_size OR
-- ... other spec columns
);
-- Insert new records
INSERT INTO {{ target_table }}
SELECT
MD5(exchange || symbol || {{ valid_from_col }}) AS instrument_sk,
exchange,
symbol,
{{ valid_from_col }} AS valid_from,
NULL AS valid_to,
CURRENT_TIMESTAMP() AS record_created_at,
NULL AS record_updated_at,
tick_size,
lot_size,
-- ... other columns
'spec_update' AS change_reason,
'ingestion_job' AS changed_by
FROM {{ source_table }}
WHERE NOT EXISTS (
SELECT 1 FROM {{ target_table }}
WHERE {{ target_table }}.{{ unique_key }} = {{ source_table }}.{{ unique_key }}
AND {{ target_table }}.valid_from = {{ source_table }}.{{ valid_from_col }}
);
{% endmacro %}# src/refdata/query/bitemporal.py
from datetime import datetime
import duckdb
def query_instrument_as_of(
conn: duckdb.DuckDBPyConnection,
exchange: str,
symbol: str,
as_of_business: datetime,
as_of_system: datetime | None = None
) -> dict | None:
"""
Query instrument specification as of a specific business time,
optionally filtered by system knowledge time.
"""
query = """
SELECT *
FROM iceberg_scan('s3://refdata-warehouse/silver/instruments')
WHERE exchange = ?
AND symbol = ?
AND valid_from <= ?
AND (valid_to IS NULL OR valid_to > ?)
"""
params = [exchange, symbol, as_of_business, as_of_business]
if as_of_system:
query += " AND record_created_at <= ?"
params.append(as_of_system)
query += " ORDER BY record_created_at DESC LIMIT 1"
result = conn.execute(query, params).fetchone()
return dict(zip([d[0] for d in conn.description], result)) if result else None- Snodgrass, Richard T. "Developing Time-Oriented Database Applications in SQL" (1999)
- Johnston, Tom. "Bitemporal Data: Theory and Practice" (2014)
- Temporal Tables in SQL:2011
- MiFID II Record Keeping Requirements
- ADR-002: Ingestion Strategy (determines when
record_created_atis set) - ADR-003: DBT vs Spark (affects implementation of SCD Type 2 logic)
- ADR-005: Schema Evolution (late corrections are a form of schema evolution)