Table of contents:
Introduction.
The role of ETL in the design of trading systems.
ETL failure modes and risks.
Technical preliminaries.
Designing an ETL.
Layers, contracts and canonical schema.
ETL vs ELT for trading.
What breaks ETL in trading systems.
Before you begin, remember that you have an index with the newsletter content organized by clicking on “Read the newsletter index” in this image.
Introduction
We enter the part of the quant stack that drives everything: data. We treat data as the machinery that tells a trading system what the market is—minute by minute, venue by venue, revision by revision.
This new serie introduces a sequence of articles on infrastructure—ETLs, scraping, APIs—written from the quant-dev perspective: trading systems need repeatable market states. Research needs to recreate yesterday. Live trading needs to ingest today. Both need to refer to the same definitions.
A familiar pattern shows up in almost every desk, prop shop, or solo setup. Data starts as a handful of scripts, then grows into a maze of just this once fixes: a quick endpoint here, a patched column there, a notebook cell that becomes a dependency because it worked during a good backtest. The system still runs. Results still appear. But the pipeline becomes a moving object—shifting definitions, shifting assumptions, shifting outputs—until the only thing that remains stable is confusion.
That is why ETL deserves a dedicated article. The reason? A pipeline accumulates decisions: what gets stored, what gets normalized, what gets ignored, what gets re-requested, what gets overwritten, what gets trusted… Those decisions compound, and at some point they dominate the behavior of the entire stack. The strategy stops being the bottleneck. The data becomes the bottleneck.
Scraping and APIs deserve equal attention because they are how most modern trading pipelines actually exist in the wild. They impose constraints, quirks, and failure patterns: response truncation, pagination inconsistencies, silent field changes, intermittent outages, caching surprises, and vendor helpfulness.
The goal across this series is to make data infrastructure feel less like maintenance and more like a controlled instrument. A good pipeline behaves like a well-specified system: deterministic when it should be deterministic, conservative when it must be conservative, and transparent when something deviates.
Check this paper to know more about ETLs:
So the promise of this article is concrete. By the end, it becomes natural to answer questions that otherwise stay vague: Where did this feature come from? Which definition did it use? When did it change? Can the dataset be recreated byte-for-byte? What happens when the vendor revises history? How does the system fail when the feed degrades? Those are operational questions, but they decide whether research can be trusted and whether live behavior can be diagnosed.
The role of ETL in the design of trading systems
Three components are typically treated as core in any trading system: strategy logic, execution, and risk. The ETL process—Extract, Transform, Load—is often categorized as a secondary support function. However, this categorization understates the structural role ETL plays in defining the parameters of the trading environment. It is the ETL process that establishes timestamp semantics and interval conventions, just as it defines the mechanics of volume aggregation across venues, determines the handling of missing prints or stale quotes, and establishes the boundaries of the trading day, including the definition of market close during partial sessions or under vendor-specific conventions.
A weak data pipeline permits a model to stay numerically consistent even when it lacks economic coherence. This failure manifests as an epistemic mismatch. The model trains on information unavailable at the time of execution or on data representations that drift between the training environment and the live feed. Backtests appear profitable but lack causal validity. Live trading degradation follows as a predictable consequence of this architecture.
Scale makes ETL automation a key pillar. But it is a challenge that requires automating the whole process. That means, new failures. From a quant dev perspective, this presents a tension between three competing criteria: velocity, correctness, and observability. Velocity is necessary because research iteration and feature publication are time-sensitive. Correctness is critical because a single semantic mismatch can introduce systematic lookahead bias without any explicit error in the code. Observability is essential because, without reproducibility and data lineage, it is impossible to distinguish between a failure of strategy logic and a failure of data construction. Once models are trained on warehouse-ready features, the ETL pipeline becomes an implicit component of the model itself.
ETL failure modes and risks
ETL failure modes exist across domains, but in trading they are amplified because signals are marginal and backtests are sensitive to subtle causal violations. It is useful to name the principal failure modes, as a map from data defects to PnL leakage.
Temporal integrity risk is the most frequent source of silent contamination. Timezone normalization errors—exchange time, broker server time, and UTC being conflated—create misalignment that can masquerade as alpha. Bar timestamp conventions are often ambiguous: some providers label bars by start time, others by end time, and others by a vendor-defined label that is neither. DST discontinuities can duplicate or remove hourly timestamps. APIs frequently return date fields with implicit timezones. Resampling without explicit session calendars can manufacture bars during market closures. None of these defects need an explicit future reference to introduce lookahead, misalignment is sufficient.
Market microstructure risk emerges when sampling and aggregation rules are treated as interchangeable. For example, volume is venue-specific; cross-venue volume is not additive unless definitions match. VWAP computed from incomplete trade prints is not VWAP. OHLC bars from heterogeneous vendors can be numerically similar while being non-equivalent, which means return distributions can look stable while microstructure features drift. Mixing bid/ask and last-trade series destroys interpretability of returns, spread-sensitive features, and execution assumptions. Even quote filtering—removing stale quotes or enforcing spread constraints—changes the effective sampling distribution and can alter feature–target dependence.
Firm actions and instrument definition risk is where many useless backtests are born. Splits, dividends, symbol changes, roll schedules for futures, and specification changes in tick size or lot size all require explicit treatment. A toxic pattern is using adjusted close alongside raw open/high/low, which creates a synthetic object with inconsistent scale.
Data leakage risk is the highest-impact failure mode because it converts structural error into apparent predictive power. Vendor revisions—late prints, corrected OHLC—can enter historical features without an as-of policy. Using the close price for signals intended to be decided at open changes the filtration. Filling missing values using future data is leakage by construction. End-of-day fundamentals used for intraday signals without controlling for publication time inject future information. Even innocuous preprocessing, such as z-scoring using full-sample statistics, can leak information when deployed online.
Heterogeneity risk is both schema and semantics. NaN encodings differ across sources (null, zero, empty string). Identical field names can have different meaning, as when volume is shares, contracts, or notional. Data types differ and can change silently (float versus decimal, integer timestamps versus ISO strings). Precision and rounding differences can alter microstructure-sensitive computations and create spurious stability.
Operational risk appears when automation is treated as job finished equals success. Cron jobs can succeed while returning partial data. API rate limits can truncate responses. Network errors can create gaps, retries can create duplicates. Incremental loads can fail to be idempotent. Failures often cluster on holidays, early closes, vendor maintenance windows, and schedule drift that causes overlapping extraction windows.
Heavy transformations performed repeatedly inside modelling loops change the feasible hypothesis class. Recomputing features that should be materialized wastes iteration budget and induces inconsistent definitions when code evolves. Unbounded joins across large fact tables can make the research loop unstable under scaling.
Technical preliminaries
Trading doesn’t tolerate imprecise definitions because PnL is sensitive to small causal misalignments. Let Pt denote a price process under an explicit convention (bid, ask, mid, last). Let Vt denote an associated volume process with explicit units (shares, contracts, notional). Let It denote the sigma-algebra of information available up to time t. A trading signal St must be It-measurable.
An ETL pipeline can be modeled as a composition of maps,
where ε extracts from raw sources into an event stream, T transforms into conformed representations, and L loads into storage layers (OLTP and DW).
Online transaction processing (OLTP) is a type of database system used in transaction-oriented applications, such as many operational systems. Online refers to the fact that such systems are expected to respond to user requests and process them in real-time (process transactions). The term is contrasted with online analytical processing (OLAP) which instead focuses on data analysis (for example planning and management systems).
Data warehouse (DW), is a system used for reporting and data analysis and is a core component of goberning. Data warehouses are central repositories of data integrated from disparate sources.
The critical property is causal admissibility. For any feature Ft used at decision time t, Ft must be measurable with respect to It. ETL violates this property through mechanisms that look operationally reasonable: aggregations that use end-of-window values while decisions occur at window start; imputation via centered filters; backfills that overwrite previously published features without version control; and “helpful” resampling rules that use future ticks to fill an OHLC bar.
This is why causal admissibility must be enforced by automated tests. If the system can’t prove the property, the property doesn’t hold operationally.
To remove ambiguity, define three timelines. Observation time t(e) (event-time) is when the market produced the observable. Processing time t(p) is when the system ingested it. Decision time t(d) is when the strategy commits to an action.
A bar at index k has an interval [tk,start , tk,end). If a strategy acts at bar open, then tk(d) . Features must not depend on events with t(e)≥tk,start.
Before diving into more complex staff, understanding where ETL pipelines typically struggle helps prioritize improvement efforts. Here’s a breakdown of common bottleneck areas:
Designing an ETL
Every strategy carries implicit assumptions about what constitutes a day, how the open is defined under multi-session trading, which clock governs decisions, what constitutes a trade, for example for a VWAP aggregation, and when information enters It (publication timestamps, vendor revisions, and the latency of ingestion itself). ETL either formalizes these assumptions or smuggles them.
The baseline automation flow is simple and sensible: collect data on a schedule, store it in OLTP, transform and load it into a DW, then use the DW for trading workflows. That pattern improves throughput. The difference from a traditional ETL lies in representing the pipeline as a reproducible computational object with explicit contracts so that performance does not amplify error.
Bounded complexity matters because a system that can’t be described will not remain stable. Verifiable correctness matters because a property that can’t be checked automatically is not a property of the system.
ETL design can be posed as constrained optimization. Let θ parametrize pipeline configuration (batch size, partitioning, retry policy, scheduling, feature versions). Let D(θ) denote the dataset emitted for modelling. We care about generalization performance G(D(θ)), latency L(θ), cost C(θ) (compute, storage, API calls), and data quality risk R(θ) (probability-weighted impact of faults). A compact objective can be written as
subject to causal admissibility, idempotence, and lineage. The key conceptual shift is that ETL is an optimization problem.
A useful operational shift is to treat the pipeline as a parameterized program with a semantic version v. The output dataset is then Dv(θ). If either v or θ changes, you must assume the output is a different dataset, even if schemas appear identical. This rule prevents a large fraction of unreproducible model results.
Scheduling ETL jobs reduces contention with operational systems. However, smart scheduling goes beyond simple timing:
Layers, contracts and canonical schema
A robust architecture is best described as layers governed by explicit contracts. At the perimeter, source connectors interact with APIs, brokers, file drops, and vendor feeds. Their contract is to emit raw events with source metadata, explicit time conventions, and enough information to reproduce the request.
Raw ingestion into OLTP should be append-heavy and minimally transformative. Its contract is to preserve raw information, enforce uniqueness, and preserve provenance. Canonicalization then converts heterogeneous source representations into a canonical schema by enforcing time normalization, type normalization, and symbol mapping. The DW layer provides partitioned fact tables and dimensions (assets, calendars, sources) with stable semantics optimized for analytical access.
Above the DW, a feature store publishes versioned feature vectors and tensors consumed by research and live engines. The feature store is the interface that enforces immutability by version and train/live parity. Finally, model inputs and backtest feeds are derived from the feature store as arrays, parquet datasets, or tight queries.
A canonical schema must minimize semantic ambiguity. At minimum, an asset dimension fixes identifiers, venues, tick sizes, and calendars. A calendar dimension fixes session definitions, holidays, and early closes. A source dimension fixes provider identity, endpoint sampling rules, time conventions, and revision policy. Facts separate raw events from derived bars and derived bars from derived features. This separation prevents circularity and leakage.
Establishing baseline metrics is essential before implementing optimization strategies. Track these KPIs to measure improvement:
A critical design choice is to represent bars with explicit start/end timestamps. Ambiguous labels are a recurring source of leakage.
CREATE TABLE IF NOT EXISTS fact_bar (
asset_id BIGINT NOT NULL,
ts_start_utc TIMESTAMPTZ NOT NULL,
ts_end_utc TIMESTAMPTZ NOT NULL,
open DOUBLE PRECISION,
high DOUBLE PRECISION,
low DOUBLE PRECISION,
close DOUBLE PRECISION,
volume DOUBLE PRECISION,
source_id TEXT NOT NULL,
ingest_ts_utc TIMESTAMPTZ NOT NULL DEFAULT NOW(),
quality_flags INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (asset_id, ts_start_utc, source_id)
);
CREATE INDEX IF NOT EXISTS idx_fact_bar_end
ON fact_bar(asset_id, ts_end_utc);This schema forces a commitment to interval semantics and prevents a broad class of timestamp-induced lookahead errors.
CREATE TABLE IF NOT EXISTS feature_vector (
asset_id BIGINT NOT NULL,
ts_utc TIMESTAMPTZ NOT NULL,
feature_set_version TEXT NOT NULL,
f1 DOUBLE PRECISION,
f2 DOUBLE PRECISION,
f3 DOUBLE PRECISION,
quality_flags INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(asset_id, ts_utc, feature_set_version)
);