┌─────────────────────────────────────────────────────────────────────┐
│ BRONZE LAYER (Raw Events) │
└─────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────┐ ┌──────────────────────────────────┐
│ bronze_instruments_binance │ │ bronze_instruments_kraken │
├──────────────────────────────────┤ ├──────────────────────────────────┤
│ PK: ingestion_id │ │ PK: ingestion_id │
│ ingestion_timestamp │ │ ingestion_timestamp │
│ api_endpoint │ │ api_endpoint │
│ api_response_raw (JSON) │ │ api_response_raw (JSON) │
│ response_size_bytes │ │ response_size_bytes │
│ http_status_code │ │ http_status_code │
└──────────────────────────────────┘ └──────────────────────────────────┘
│ │
│ │
└──────────────┬───────────────────────┘
│
│ DBT Transformation (SCD Type 2)
▼
┌─────────────────────────────────────────────────────────────────────┐
│ SILVER LAYER (Dimensions) │
└─────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────────┐
│ silver_instruments (Bitemporal SCD Type 2) │
├──────────────────────────────────────────────────────────────────────┤
│ PK: instrument_sk │
│ NK: (exchange, symbol) │
│ │
│ BITEMPORAL DIMENSIONS: │
│ valid_from TIMESTAMP -- Business time start │
│ valid_to TIMESTAMP -- Business time end (NULL=current) │
│ record_created_at TIMESTAMP -- System time created │
│ record_updated_at TIMESTAMP -- System time updated │
│ │
│ CORE ATTRIBUTES: │
│ exchange STRING │
│ symbol STRING │
│ instrument_type STRING │
│ base_asset STRING │
│ quote_asset STRING │
│ status STRING │
│ │
│ SPECIFICATIONS: │
│ tick_size DECIMAL(38,18) │
│ lot_size DECIMAL(38,18) │
│ min_notional DECIMAL(38,18) │
│ max_leverage INT │
│ funding_interval_hours INT │
│ settlement_asset STRING │
│ │
│ EXTENSIBILITY: │
│ vendor_data STRING -- JSON for exchange-specific fields │
│ │
│ AUDIT TRAIL: │
│ source_system STRING │
│ source_record_id STRING │
│ change_reason STRING │
│ changed_by STRING │
└──────────────────────────────────────────────────────────────────────┘
│
│ DBT Symbology Mapping
▼
┌─────────────────────────────────────────────────────────────────────┐
│ GOLD LAYER (Analytics) │
└─────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────────┐
│ gold_symbology_master │
├──────────────────────────────────────────────────────────────────────┤
│ PK: canonical_id │
│ │
│ NORMALIZED CLASSIFICATION: │
│ base_asset STRING -- Normalized (XBT→BTC) │
│ quote_asset STRING -- Normalized (USDT→USD) │
│ instrument_class STRING -- spot, perpetual, future │
│ │
│ FUTURES/OPTIONS DETAILS: │
│ expiry_date DATE │
│ settlement_asset STRING │
│ strike_price DECIMAL(38,18) │
│ option_type STRING -- call, put │
│ │
│ CROSS-EXCHANGE MAPPINGS: │
│ binance_symbol STRING -- 'BTCUSDT' or NULL │
│ kraken_symbol STRING -- 'XBT/USD' or NULL │
│ bybit_symbol STRING -- 'BTCUSDT' or NULL │
│ coinbase_symbol STRING -- 'BTC-USD' or NULL │
│ okx_symbol STRING -- 'BTC-USDT' or NULL │
│ │
│ HIERARCHY: │
│ parent_canonical_id STRING -- Links perps to spot │
│ │
│ LIFECYCLE: │
│ first_seen_at TIMESTAMP │
│ last_seen_at TIMESTAMP │
│ is_active BOOLEAN │
│ created_at TIMESTAMP │
│ updated_at TIMESTAMP │
│ metadata STRING -- JSON │
└──────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ OPERATIONAL TABLES (PostgreSQL) │
└─────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────────┐
│ refdata.ingestion_state (State Store) │
├──────────────────────────────────────────────────────────────────────┤
│ PK: exchange │
│ last_response_hash STRING │
│ last_ingestion_timestamp TIMESTAMP │
│ last_response_size_bytes INT │
│ last_error STRING │
│ updated_at TIMESTAMP │
└──────────────────────────────────────────────────────────────────────┘
Purpose: Raw event log of Binance API responses
Schema:
CREATE TABLE refdata.bronze_instruments_binance (
-- Primary key (deterministic)
ingestion_id STRING NOT NULL,
-- MD5(api_response + ingestion_timestamp.isoformat())
-- Ensures idempotency: same response + timestamp = same ID
-- Metadata
ingestion_timestamp TIMESTAMP NOT NULL,
-- System time when ingestion occurred
-- Used for partitioning and retention
api_endpoint STRING NOT NULL,
-- e.g., '/api/v3/exchangeInfo'
-- Allows multi-endpoint ingestion in future
-- Raw data (forward compatible)
api_response_raw STRING NOT NULL,
-- Complete JSON response from Binance API
-- Stored as-is for full fidelity and replay capability
response_size_bytes INT,
-- Size of api_response_raw in bytes
-- Monitoring: alert if size grows unexpectedly
http_status_code INT,
-- HTTP status code (200, 429, 500, etc.)
-- Useful for debugging API failures
-- Primary key constraint
PRIMARY KEY (ingestion_id)
) USING iceberg
PARTITIONED BY (days(ingestion_timestamp))
-- Daily partitions (not hourly) - reference data changes infrequently
-- Typical partition size: 1-10 MB/day
LOCATION 's3a://refdata-warehouse/bronze/instruments/binance'
TBLPROPERTIES (
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'zstd',
'format-version' = '2',
'write.metadata.delete-after-commit.enabled' = 'true',
'write.metadata.previous-versions-max' = '7' -- 7-day retention
);Retention Policy: 7 days
- Sufficient for replay if Silver schema needs updates
- Bronze → Silver transformation typically runs hourly
- After 7 days, data compacted into Silver
Partitioning Strategy:
days(ingestion_timestamp): Daily granularity- Not hourly: Reference data changes ~10x/day, not 240x/day
- Enables efficient pruning: "Replay last 2 days" scans only 2 partitions
Indexes (Iceberg metadata tracks automatically):
- Primary key:
ingestion_id(unique constraint) - Time-range queries:
ingestion_timestamp(partition pruning)
Schema: Identical to bronze_instruments_binance
Differences:
api_endpoint: '/0/public/AssetPairs'api_response_raw: Kraken-specific JSON structure
Purpose: Bitemporal dimension table (SCD Type 2) for instrument specifications
Schema:
CREATE TABLE refdata.silver_instruments (
-- Surrogate key (SCD Type 2 requirement)
instrument_sk STRING PRIMARY KEY,
-- MD5(exchange || symbol || valid_from)
-- Uniquely identifies a specific version of an instrument
-- Example: MD5('binance' || 'BTCUSDT' || '2024-01-15 00:00:00')
-- Natural key (business identifier)
exchange STRING NOT NULL,
-- Exchange identifier: 'binance', 'kraken', 'bybit'
-- Partition key for query pruning
symbol STRING NOT NULL,
-- Exchange-native symbol: 'BTCUSDT', 'XBT/USD'
-- NOT normalized at this layer (preserve fidelity)
-- ⭐ BITEMPORAL DIMENSIONS
-- Business time: When the specification was effective in reality
valid_from TIMESTAMP NOT NULL,
-- Effective start time (inclusive)
-- Example: "Tick size change effective Jan 15 00:00 UTC"
-- Can be future: announced on Jan 10, effective Jan 15
valid_to TIMESTAMP,
-- Effective end time (exclusive)
-- NULL = currently effective (latest specification)
-- Non-NULL = superseded by newer specification
-- System time: When we learned about it
record_created_at TIMESTAMP NOT NULL,
-- When this record was first inserted into Silver
-- Tracks when we became aware of the specification
-- Used for late correction handling
record_updated_at TIMESTAMP,
-- When this record was last modified
-- NULL = never updated (original record)
-- Non-NULL = late correction applied
-- Instrument classification
instrument_type STRING,
-- 'spot', 'perpetual', 'future', 'option'
-- Determines which specifications are relevant
base_asset STRING,
-- Base currency: 'BTC', 'ETH', 'SOL'
-- Extracted from exchange API (not yet normalized)
quote_asset STRING,
-- Quote currency: 'USDT', 'USD', 'BTC'
-- Extracted from exchange API (not yet normalized)
status STRING,
-- 'active', 'suspended', 'delisted', 'pre_trading'
-- Lifecycle state of the instrument
-- Contract specifications (core fields)
tick_size DECIMAL(38, 18),
-- Minimum price increment
-- Example: 0.01 means prices must be multiples of 0.01
-- NULL if not applicable (some instruments don't have tick size)
lot_size DECIMAL(38, 18),
-- Minimum quantity increment
-- Example: 0.00001 BTC means orders must be multiples of 0.00001
min_notional DECIMAL(38, 18),
-- Minimum order value (price × quantity)
-- Example: 10 USDT means orders must be >= $10
max_leverage INT,
-- Maximum leverage allowed
-- NULL for spot instruments (no leverage)
-- Example: 100 means up to 100x leverage
-- Perpetual-specific fields (NULL for spot/futures)
funding_interval_hours INT,
-- Hours between funding payments
-- Example: 8 (funding every 8 hours = 3x/day)
settlement_asset STRING,
-- Asset used for settlement
-- Example: 'USDT' for USDT-margined contracts
-- ⭐ VENDOR DATA ESCAPE HATCH
vendor_data STRING,
-- JSON map of exchange-specific fields not promoted to columns
-- Example: {"marginTradingAllowed": true, "riskLimits": [...]}
-- Forward compatible: new fields stored here until promoted
-- Audit trail (regulatory compliance)
source_system STRING NOT NULL,
-- 'binance', 'kraken', 'manual_override'
-- Tracks origin of the data
source_record_id STRING,
-- Link back to Bronze ingestion_id
-- Enables full lineage: Silver → Bronze → Kafka → API
change_reason STRING,
-- 'initial_load', 'spec_update', 'manual_correction', 'delisting'
-- Why this record was created
changed_by STRING,
-- 'dbt_transformation', 'admin_user_id'
-- Who made the change (system or human)
-- Data quality
is_validated BOOLEAN DEFAULT TRUE,
-- FALSE if validation rules failed
-- Example: tick_size <= 0 would set this to FALSE
validation_errors STRING
-- JSON array of validation errors if is_validated=FALSE
-- Example: ["tick_size must be positive", "base_asset is NULL"]
) USING iceberg
PARTITIONED BY (
exchange, -- First partition: 90% of queries filter by exchange
months(valid_from) -- Second partition: monthly granularity (not daily!)
)
LOCATION 's3a://refdata-warehouse/silver/instruments'
TBLPROPERTIES (
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'zstd',
'format-version' = '2'
);Partitioning Strategy:
-
By exchange (first level):
- Query pattern: "Show Binance instruments" → only scan
exchange=binancepartition - Cardinality: 3-5 exchanges (low, good for partition pruning)
- Query pattern: "Show Binance instruments" → only scan
-
By months(valid_from) (second level):
- Reference data changes ~1-10x/day, not continuous
- Daily partitions would create too many small files
- Monthly partitioning: ~300-1000 changes/month → 10-50 MB partitions
- Target: 100+ MB per partition (Iceberg best practice)
Query Patterns:
- Point-in-Time Query (most common):
-- "What was tick_size for BTCUSDT on 2024-01-15 10:00?"
SELECT tick_size
FROM refdata.silver_instruments
WHERE exchange = 'binance' -- Partition pruning
AND symbol = 'BTCUSDT'
AND valid_from <= '2024-01-15 10:00:00'
AND (valid_to IS NULL OR valid_to > '2024-01-15 10:00:00')
ORDER BY record_created_at DESC -- Latest correction wins
LIMIT 1;- Current State Query:
-- "What is the current tick_size for BTCUSDT?"
SELECT tick_size
FROM refdata.silver_instruments
WHERE exchange = 'binance'
AND symbol = 'BTCUSDT'
AND valid_to IS NULL -- Current record
ORDER BY record_created_at DESC -- Latest correction
LIMIT 1;- Audit Trail Query:
-- "Show all historical changes to BTCUSDT"
SELECT
valid_from,
valid_to,
tick_size,
record_created_at,
change_reason
FROM refdata.silver_instruments
WHERE exchange = 'binance'
AND symbol = 'BTCUSDT'
ORDER BY record_created_at DESC;SCD Type 2 Lifecycle:
Timeline:
Jan 10: Binance announces tick_size change effective Jan 15
Jan 11: We ingest announcement
Record 1 (inserted Jan 11):
valid_from = 2024-01-15 00:00:00
valid_to = NULL -- Currently effective
record_created_at = 2024-01-11 15:00:00
tick_size = 0.05
Jan 16: Correction received: "Actually effective Jan 14 23:00"
Record 1 (unchanged):
valid_from = 2024-01-15 00:00:00
valid_to = NULL
record_created_at = 2024-01-11 15:00:00
tick_size = 0.05
Record 2 (inserted Jan 16):
valid_from = 2024-01-14 23:00:00 -- Corrected effective time
valid_to = NULL -- Now current
record_created_at = 2024-01-16 08:00:00
tick_size = 0.05
Query "as of Jan 14 22:00":
Returns old tick_size (before corrected valid_from)
Query "as of Jan 15 10:00":
Returns Record 2 (latest record_created_at)
Purpose: Canonical symbology mapping across exchanges
Schema:
CREATE TABLE refdata.gold_symbology_master (
-- Primary key: canonical identifier
canonical_id STRING PRIMARY KEY,
-- Format: {BASE}-{QUOTE}-{TYPE}[-{EXPIRY}]
-- Examples:
-- BTC-USD-SPOT
-- ETH-USDT-PERP
-- BTC-USD-FUT-20240329
-- Normalized classification
base_asset STRING NOT NULL,
-- Normalized base currency: 'BTC' (not 'XBT')
-- Normalization rules in config/symbology_rules.yaml
quote_asset STRING NOT NULL,
-- Normalized quote currency: 'USD' (not 'USDT')
-- Treats stablecoins as USD equivalents
instrument_class STRING NOT NULL,
-- 'spot', 'perpetual', 'future', 'option'
-- Normalized from exchange-specific terminology
-- Futures/Options details (NULL for spot/perpetual)
expiry_date DATE,
-- Expiry date for futures/options
-- Example: '2024-03-29'
-- NULL for spot and perpetuals (no expiry)
settlement_asset STRING,
-- Settlement currency for derivatives
-- Example: 'USD', 'BTC'
strike_price DECIMAL(38, 18),
-- Strike price for options
-- NULL for spot/perpetuals/futures
option_type STRING,
-- 'call' or 'put'
-- NULL for non-options
-- ⭐ CROSS-EXCHANGE MAPPINGS (bidirectional lookup)
binance_symbol STRING,
-- Binance-native symbol or NULL if not listed
-- Example: 'BTCUSDT'
kraken_symbol STRING,
-- Kraken-native symbol or NULL
-- Example: 'XBT/USD' (note: uses XBT!)
bybit_symbol STRING,
-- Bybit-native symbol or NULL
-- Example: 'BTCUSDT'
coinbase_symbol STRING,
-- Coinbase-native symbol or NULL
-- Example: 'BTC-USD'
okx_symbol STRING,
-- OKX-native symbol or NULL (future)
-- Example: 'BTC-USDT'
-- Hierarchy (link derivatives to underlying)
parent_canonical_id STRING,
-- Links perpetuals/futures to underlying spot
-- Example: BTC-USD-PERP → parent = BTC-USD-SPOT
-- NULL for spot instruments (root of hierarchy)
-- Lifecycle tracking
first_seen_at TIMESTAMP NOT NULL,
-- When first observed across any exchange
-- Example: First time BTCUSDT appeared (any exchange)
last_seen_at TIMESTAMP,
-- When last observed (if delisted)
-- NULL = still active on at least one exchange
is_active BOOLEAN DEFAULT TRUE,
-- FALSE if delisted on all exchanges
-- TRUE if listed on at least one exchange
-- Metadata
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- When this record was created in Gold layer
updated_at TIMESTAMP,
-- When this record was last modified
metadata STRING
-- JSON for additional attributes
-- Example: {"primary_exchange": "binance", "aliases": ["XBTUSD"]}
) USING iceberg
PARTITIONED BY (truncate(base_asset, 1))
-- Partition by first letter of base_asset: B, E, S, etc.
-- Low cardinality (~26 partitions)
-- Query pattern: "Show all BTC instruments" → scan only 'B' partition
LOCATION 's3a://refdata-warehouse/gold/symbology_master';Example Records:
-- BTC-USD Spot (listed on multiple exchanges)
INSERT INTO gold_symbology_master VALUES (
'BTC-USD-SPOT',
'BTC', -- Normalized from XBT
'USD', -- Normalized from USDT
'spot',
NULL, NULL, NULL, NULL, -- No expiry/strike (spot)
'BTCUSDT', -- Binance
'XBT/USD', -- Kraken (uses XBT!)
'BTCUSDT', -- Bybit
'BTC-USD', -- Coinbase
NULL, -- OKX (not yet supported)
NULL, -- No parent (spot is root)
'2024-01-01 00:00:00',
NULL, -- Still active
TRUE,
NOW(), NULL, NULL
);
-- BTC-USD Perpetual (linked to spot)
INSERT INTO gold_symbology_master VALUES (
'BTC-USD-PERP',
'BTC',
'USD',
'perpetual',
NULL, 'USD', NULL, NULL, -- Settlement in USD
'BTCUSD', -- Binance (no 'T' for perps)
NULL, -- Kraken (no BTC perps)
'BTCUSD', -- Bybit
NULL, -- Coinbase (no perps)
NULL, -- OKX
'BTC-USD-SPOT', -- Parent is spot
'2024-01-01 00:00:00',
NULL,
TRUE,
NOW(), NULL, NULL
);
-- BTC-USD Future (March 2024 expiry)
INSERT INTO gold_symbology_master VALUES (
'BTC-USD-FUT-20240329',
'BTC',
'USD',
'future',
'2024-03-29', 'USD', NULL, NULL, -- Expires Mar 29
'BTCUSD_240329', -- Binance
NULL, -- Kraken
'BTC-29MAR24', -- Bybit
NULL, -- Coinbase
NULL, -- OKX
'BTC-USD-SPOT', -- Parent is spot
'2024-01-01 00:00:00',
'2024-03-29 08:00:00', -- Expired
FALSE, -- No longer active
NOW(), NOW(), NULL
);Query Patterns:
- Canonical → Exchange Lookup:
SELECT binance_symbol, kraken_symbol, coinbase_symbol
FROM refdata.gold_symbology_master
WHERE canonical_id = 'BTC-USD-SPOT';
-- Result: {binance_symbol: 'BTCUSDT', kraken_symbol: 'XBT/USD', coinbase_symbol: 'BTC-USD'}- Exchange → Canonical Lookup:
SELECT canonical_id, base_asset, quote_asset
FROM refdata.gold_symbology_master
WHERE kraken_symbol = 'XBT/USD';
-- Result: {canonical_id: 'BTC-USD-SPOT', base_asset: 'BTC', quote_asset: 'USD'}- Search by Base Asset:
SELECT canonical_id, instrument_class, binance_symbol
FROM refdata.gold_symbology_master
WHERE base_asset = 'BTC'
AND instrument_class = 'perpetual'
AND is_active = TRUE;
-- Result: BTC-USD-PERP, BTC-USDT-PERP, etc.Purpose: State store for ingestion jobs (change detection)
Schema:
CREATE TABLE refdata.ingestion_state (
-- Primary key
exchange VARCHAR(50) PRIMARY KEY,
-- 'binance', 'kraken', 'bybit'
-- Change detection
last_response_hash VARCHAR(32) NOT NULL,
-- MD5 hash of last API response
-- Used to detect if response changed since last ingestion
last_ingestion_timestamp TIMESTAMP NOT NULL,
-- System time of last successful ingestion
-- Used for monitoring: alert if too old
-- Monitoring
last_response_size_bytes INT,
-- Size of last response in bytes
-- Alert if size changes dramatically (>50%)
last_error TEXT,
-- Last error message if ingestion failed
-- NULL if last ingestion succeeded
-- Metadata
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- When this record was last updated
);
CREATE INDEX idx_ingestion_state_timestamp
ON refdata.ingestion_state(last_ingestion_timestamp);Example Records:
INSERT INTO refdata.ingestion_state VALUES (
'binance',
'a3f5d8c9e1b2...', -- MD5 hash
'2024-01-23 10:00:00',
152384, -- ~150 KB
NULL, -- No error
'2024-01-23 10:00:05'
);
INSERT INTO refdata.ingestion_state VALUES (
'kraken',
'f2c8a1e9d3b4...',
'2024-01-23 10:01:00',
84210, -- ~80 KB
NULL,
'2024-01-23 10:01:03'
);Iceberg Metadata Indexes (automatic):
- Partition pruning:
exchange,months(valid_from) - Min/max filtering:
valid_from,valid_to,record_created_at
Query Optimization Tips:
- Always filter by
exchange(partition pruning) - Use
valid_to IS NULLfor current state queries - Order by
record_created_at DESCfor latest corrections - Limit results with
LIMITto avoid full table scans
Iceberg Metadata Indexes:
- Partition pruning:
truncate(base_asset, 1) - Min/max filtering:
canonical_id,base_asset
Query Optimization Tips:
- Primary key lookups by
canonical_idare fast (metadata lookup) - Filtering by
base_assetuses partition pruning - Searches on exchange columns (e.g.,
binance_symbol) require full scan (consider secondary index if query volume high)
| Layer | Table | Retention | Rationale |
|---|---|---|---|
| Bronze | bronze_instruments_* |
7 days | Replay capability; compact to Silver |
| Silver | silver_instruments |
Indefinite | Full history required for audit trail |
| Gold | gold_symbology_master |
Indefinite | Active + delisted instruments |
| State | ingestion_state |
Indefinite | Operational metadata |
Iceberg Snapshots (automatic):
- Bronze: 7-day retention via
write.metadata.previous-versions-max=7 - Silver/Gold: Retain all snapshots (time-travel capability)
-- Step 1: New field appears in Bronze (automatically stored in JSON)
-- Step 2: After validation, promote to Silver column
ALTER TABLE refdata.silver_instruments
ADD COLUMN IF NOT EXISTS margin_trading_allowed BOOLEAN;
-- Step 3: Update DBT model to extract from Bronze
-- (DBT config: on_schema_change='append_new_columns')
-- Step 4: Old queries continue to work (column is nullable)-- Add column for new exchange (backward compatible)
ALTER TABLE refdata.gold_symbology_master
ADD COLUMN IF NOT EXISTS okx_symbol STRING;
-- Update DBT model to populate new column
-- (Existing columns unaffected)- ARCHITECTURE.md - Overall system architecture
- ADR-001: Bitemporal Modeling - Temporal dimensions rationale
- ADR-004: Symbology Mapping - Canonical ID design
- ADR-005: Schema Evolution - Forward compatibility strategy