Data Pipelines and ETL

A data pipeline is the set of steps that moves data from its sources into a form you can use: downloading prices, cleaning them, adjusting for corporate actions and storing them in a database or files. ETL stands for extract, transform and load, the classic three stages. In trading, the pipeline is the foundation under every backtest and live decision. If it silently drops a day, double counts a bar or applies a split twice, every result built on top of it is wrong, and nobody may notice for months.

The three stages#

A common modern pattern is to store the raw data untouched first, then transform it into clean tables. If a transform has a bug, you can fix it and rebuild from the raw copy.

A typical daily equity pipeline#

1. After the close, download the day's bars for every instrument in the universe.
2. Download corporate actions such as splits and dividends.
3. Save the raw files with the download date.
4. Validate: row counts, price ranges, missing symbols, duplicate rows.
5. Transform: convert to UTC, map symbols to instrument IDs, compute adjustment factors.
6. Load into clean tables.
7. Report a summary and alert on any failed check.

See Database Design for Market Data and Splits and Dividends in Price Data.

Idempotent loads#

A load is idempotent if running it twice gives the same result as running it once. This matters because jobs fail and get rerun. Techniques:

• Unique keys on (instrument, interval, timestamp) so duplicates are rejected.
• Upserts that insert new rows or update existing ones.
• Replace by partition: delete and reload a whole day in one transaction.

Validation checks#

See Cleaning Market Data.

Scheduling and orchestration#

Simple pipelines run from cron jobs or scheduled tasks. Larger ones use orchestration tools such as Apache Airflow, Dagster or Prefect, which handle dependencies between steps, retries and alerting. Whatever the tool, every job should log what it did and raise an alert when it fails. See Monitoring Positions, P&L and Risk.

Point in time and versioning#

Vendors revise data: fundamental figures get restated and prices get corrected. If your pipeline overwrites old values, backtests can use information that was not known at the time. Keeping dated snapshots or recording when each value became known prevents this. See Point-in-Time and Survivorship-Free Data and Data Versioning, Lineage and Schemas.

Streaming pipelines#

Live trading data flows continuously rather than in daily batches. Streaming pipelines use message queues to pass ticks from feed handlers to bar builders, strategies and storage. See Message Queues and Feed Handlers and Normalization.

Frequently asked questions#

What is ETL in trading?#

Extract, transform and load: the process of collecting raw market data, cleaning and standardising it and storing it for research and trading.

Why do data pipelines need validation?#

Sources contain errors, gaps and missing corporate actions. Validation catches them before they corrupt backtests and live decisions.

What does idempotent mean for a data load?#

Running the load more than once produces the same result as running it once, so reruns after failures do not create duplicates.

Next, learn how to fix bad data in Cleaning Market Data.