Data Pipelines and Storage

Garbage in, garbage out. The “garbage” in quant usually means three things: survivorship bias, lookahead bias, and stale fundamentals. A proper data pipeline kills all three before they reach your backtest. This whole article hangs on that one question: where does data lie to a backtest, and how do you design the pipeline so it can’t.

Three Data Types, Three Ways of Lying

Every quant pipeline ingests three categories of data — market, fundamental, alternative. They have different temporal characteristics, different vendor quirks, and their traps sit in different places. Joining them naively, without respecting those differences, is one of the top three causes of backtest lies.

Market data — OHLCV, intraday, dividends, splits — is the cleanest of the three; vendors have standardized open, high, low, close, volume, and corporate actions. But clean doesn’t mean trap-free. The first trap is adjustment methodology: vendors differ on back- versus forward-adjustment, some adjust for splits but not dividends, some adjust both from different bases — and if you don’t pin your adjustment convention, backtests from different runs are not comparable. The second trap is that intraday data is far messier than daily: missing bars on half-days, late prints, venue aggregation choices — the “official” 4:00 PM ET close isn’t always the last trade’s price; it may be the closing auction price. Most long-short equity strategies do fine on daily data; HFT and stat-arb need intraday, and the data volumes differ by 100x. The third trap hides in volume reporting: off-exchange volume (dark pools, ATS) often never reaches the consolidated tape, so computing “fraction of ADV traded” from consolidated volume undercounts actual liquidity — typically by 20-40% for liquid names. As for tick data — every trade and quote, about 1 TB per day for US equities — unless you’re doing HFT or very short-horizon alpha, you don’t need it, so don’t build storage around it.

Fundamental data — earnings, balance sheets, ratios — is the trickiest of the three, because the “report date” and the “available date” are entirely different things. Three dates need separating. The report date $T_r$ is the period the data describes (e.g., Q4 2024), always a quarter-end or fiscal-year-end. The filing date $T_f$ is when the company filed with the SEC — the legal disclosure date. The available date $T_a$ is when your vendor ingested the data and made it queryable. In practice $T_a \geq T_f \geq T_r$, and the lag $T_f - T_r$ varies by company: large-cap utilities file within two weeks, small-cap biotechs can take six weeks or more. At decision time the only information you have is what existed by $T_a$, so joins must use $T_a$. A surprising number of strategies look great in backtest and fail live because this one date is wrong — the next section walks the failure through a full example.

Alternative data is the wild west: sentiment scores, insider transactions, satellite imagery of parking lots, credit card panels, options flow, web traffic, patent filings, supply chain data. It’s where more and more of the edge comes from in 2025, because the published factors are crowded. But its traps outnumber the other two categories combined. Coverage: most alternative datasets cover only a subset of your universe, and the gap is not random — when a sentiment dataset covers 800 of 1000 names, the missing 200 tend to be smaller, less-tracked names with different return profiles, so without tracking which names are covered when, “survivorship-free” is a fiction. Quality drift: vendors change methodology — a 2018 sentiment score and a 2024 one may be computed entirely differently, and some vendors have changed methods three-plus times in five years with no version history. Your model has to be refit when that happens, or the backtest is void; ask before you buy. Lookahead: sentiment scores often carry publication lag, insider transactions must be filed within two business days but your vendor may not ingest them for a week — data “as of” date X that only becomes available at X + 7 means trading on it at X uses information you didn’t have. And pricing: $50K-$500K per year per dataset is common, and the pricing models change often. The right way to evaluate whether a dataset is worth it comes down to three numbers — IC, IC IR, and capacity-adjusted Sharpe. If they’re good, the data is worth buying. If they’re not, even cheap data is too expensive.

The Point-in-Time Problem: The Biggest Single Lie

The traps above come in different shapes, but the deadliest one is the same across all three categories: using data that wasn’t available at decision time. Walk the classic example end to end.

Company X reports Q4 earnings on February 14, 2024, beating consensus by 15%. The naive backtest joins “Q4 earnings surprise” to “stock return on Feb 14” and declares that the surprise predicted the move — looks great. The reality: the earnings were released after the close on Feb 14, and the price reaction happened on Feb 15. If you traded on Feb 14 using “today’s earnings,” you traded before the news existed. The correct point-in-time treatment: the earnings become available at $T_a$ — Feb 14 evening or Feb 15 morning, depending on your vendor — so you trade on Feb 15, not Feb 14.

That one-day error manufactures a slice of Sharpe in backtest that doesn’t exist in reality. It happens roughly four times a year per stock, every earnings season. Over 500 stocks and 20 years, that’s 40,000 fake signals.

At the pipeline level, the defense is to make timestamps mandatory: every data point carries a (value, as_of) pair, and joins use as_of semantics, never period semantics. Code that joins on period (say, quarter-end) should draw a warning from the pipeline. An explicit override for academic replication on $T_r$ is fine — but it gets logged.

On the vendor side, the problem is that most vendors expose “earnings reported at $T$” as a join key, conflating $T_r$ (the period) with $T_a$ (when you could know it). The fix has three layers. If the vendor provides a point-in-time table — CRSP, Compustat, and Bloomberg all carry PIT identifiers — use it; some vendors call these “as-of” dates, others “available” dates, so ask. If they don’t, track filing dates and add a buffer for the vendor’s ingestion lag, sized to their track record — some are real-time, others lag by days. For alternative data, ask the vendor for coverage dates and methodology history, and if they can’t tell you when they had which data, don’t buy. Bottom line: a vendor that doesn’t offer point-in-time timestamps is telling you they don’t want you doing rigorous backtests. Find a different vendor.

The property is also mechanically verifiable. Pick a known event — Apple’s Q1 2023 earnings, reported February 2, 2023. Look up the filing date. Check your dataset: does the row for that filing carry an as_of of Feb 2, 2023 or later? If it’s earlier — Jan 31, say — your vendor is leaking future information. This check belongs in your pipeline’s CI, run across a sample of known events, after every vendor refresh.

Storage Formats

Clean data still has to live somewhere sensible. Storage choice is downstream of access pattern, and three options cover 95% of quant use cases.

Parquet is the default for OHLCV, fundamentals, and most alternative data. Columnar means a query like “all close prices for ticker X” reads one column off disk, not whole rows. Compression runs 5-10x smaller than CSV for typical market data — ten years of daily OHLCV for 1000 tickers is roughly 50MB in Parquet versus 500MB in CSV. Types are explicit, so no more guessing whether a column is int64 or float64 — that alone catches bugs. Query engines (DuckDB, Polars, Spark) get predicate pushdown, skipping row groups that can’t match a filter like “close > $100”. Partitioning can go by date, by ticker, or both; date partitioning is standard for time series, with year=YYYY/month=MM/day=DD/ticker=SYM a common scheme. zstd or snappy compression both work — pick one and stop switching.

HDF5 serves a different access pattern: very large time-series datasets needing fast random access to specific (date, ticker) combinations. Pandas to_hdf and read_hdf work out of the box, and single-file deployment is convenient. The costs: it doesn’t play well with distributed compute, it has a single-writer limitation, and schema evolution is awkward. For single-machine research, fine. For a cluster, switch to Parquet plus Iceberg or Delta. HDF5 is for prototyping, not production at scale.

Postgres and ClickHouse handle the third thing: metadata and queries. Universe definitions, ticker symbols, sector classifications, delisting events; pipeline run records, data quality checks, freshness timestamps; and the ad-hoc questions — “which tickers had earnings this week?”, “which delisted stocks closed above $50?”. ClickHouse is faster for analytical queries over metadata; Postgres is more flexible for operational data — JSON columns, transactions, foreign keys. A common split is Postgres for metadata, ClickHouse for analytics. But don’t store OHLCV in Postgres without a very good reason: it’s row-based, and analytical queries run 10-50x slower than on Parquet.

And CSV? CSV is technically a format and technically works, and for serious quant work it is wrong. It has no schema — a column can be string in one row and float in the next, and pandas’ silent type coercion produces wrong results without raising, which you’ll discover six months after publishing the backtest. It’s slow — parsing CSV runs 10-50x slower than reading Parquet, turning a seconds-long backtest into minutes. Its missing values are ambiguous — is "" missing? "NA"? "null"? CSV doesn’t say, and different parsers disagree. It has no compression, which hurts badly at scale. And its date parsing is a nightmare — “2024-01-15” or “01/15/2024” or “15-Jan-2024”? Time zones? UTC or local? Nothing is enforced. A pipeline can accept CSV for compatibility, but it should log a warning — and that warning should be treated as a TODO.

The Five Cleaning Operations

Before anything gets stored, every market data pipeline runs the same five cleaning steps. The order matters: fix the price series first (splits, dividends), then fix the universe (delistings), then handle day-level data quality (liquidity, gaps).

First, apply split adjustments, back-adjusted by default. After a 2-for-1 split, historical pre-split prices get halved — same dollar value, different share count. Back-adjustment scales all history by the split ratio: the series is continuous, but absolute pre-split price levels are meaningless — standard for analytical use. Forward-adjustment only touches post-split prices: the series has a discontinuity at the split date, which is what you want for execution logic that needs actual price levels. Mixing the two is a common source of backtest errors; know which one you’re holding. And adjustment isn’t only about splits — reverse splits, spin-offs, and special dividends all need handling, uniformly, through the corporate actions table your vendor should provide.

Second, dividend adjustments. Cash dividends knock the price down by the dividend amount on the ex-date, and the logic mirrors splits: back-adjusted for analysis, forward-adjusted for execution. For factor returns you almost always want the total-return series with dividends reinvested — price returns carry a mechanical bias against dividend-paying stocks. Special dividends (large one-offs) are tricky: some vendors treat them as regular dividends, others as return-of-capital. Confirm your vendor’s treatment matches your assumption.

Third, keep delisted tickers — no silent drops. Delisted names stay in the historical universe with their last-known price and a delisted=True flag. The reason is blunt: if you only see survivors, your backtest’s average return is biased upward. A 30% delisting rate over 20 years means missing 30% of the worst performers, and survivorship bias can inflate apparent backtest return by 1-3% per year. The check: your universe data should carry delisted and delist_date columns; if it doesn’t, your vendor is feeding you survivorship-biased data. Note also that the last-known price of a delisted ticker is often wrong — it may be the last trade rather than the final close, or stale by days — so the cleaning stage should flag suspicious delisting prices for a human to confirm or override.

Fourth, mark illiquid days as NaN. Some days have no prints, some have stale prints, some have a tenth of normal volume. The rules: NaN in the price column, no forward-filling the price (that creates lookahead in volume calculations), and a separate illiquid boolean column for filtering later. The threshold is configurable; a reasonable default calls a day illiquid when volume is under 10% of the 60-day median volume and the spread exceeds twice the 60-day median spread. Why mark rather than drop? Dropping illiquid days changes the time-series properties of the data — a factor that works on liquid days may not work on illiquid ones. Marking preserves the choice: include all days, or filter to liquid-only.

Fifth, forward-fill missing values — with a hard limit. Real data has gaps: holidays, exchange outages, vendor outages. Forward-fill covers them, but cap it — say, at most 5 days, then NaN. The reason for the cap: a 30-day forward-filled gap is a synthetic data point, not real data, and the model deserves to know. The cap value matters — too small and you NaN out too many gaps, too large and you pretend gaps don’t exist. Five is a reasonable default for daily data; tune it for your universe.

Run all five and the output has earned the labels “point-in-time correct, survivorship-free, properly cleaned” — ready for feature engineering.

From One-Off Script to Production Pipeline

The canonical pipeline has four stages: pull raw vendor data, clean it, store it, validate it. The first three are the material above, chained together. The fourth is the one that gets skipped and shouldn’t be: after every run, produce a validation report — universe size, delisted-ticker count, median history coverage in years, point-in-time timestamp coverage — so that a vendor quietly dropping delisted names, or PIT coverage slipping under 95%, surfaces immediately instead of six months later.

Three more things separate a one-off script from a production pipeline. Scheduling: in production the pipeline runs nightly, after the vendor refreshes — cron or a workflow scheduler, either is fine. Error handling: when the vendor returns a partial file, the pipeline must detect it and alert — raise an explicit data-quality error on missing days, missing tickers, or anomalies, rather than swallowing them. Schema versioning: when the vendor changes their schema the pipeline breaks, so have a migration mechanism instead of hand-patching field names forever.

Above that sit three operational concerns. Reliability: a pipeline that runs 95% of the time isn’t good enough — a missed refresh means trading on stale data — so every run ends with a health check, failures route to Slack, PagerDuty, or email, and a backfill mechanism re-runs the cleaning for any missed dates. Data versioning: a backtest depends on a specific version of the data, and a month later — after new splits and restated fundamentals — the same backtest produces different results; fine for production, fatal for reproducibility. The fix is storing data under version tags and pinning the version in backtest code, so you can load the exact snapshot as of any given date. Latency: daily strategies can run overnight and not care; intraday strategies care a lot, and need a streaming mode that emits data as it arrives — useful for live trading, less so for backtesting.

The Honest Trap

If your data vendor’s “delisted returns” table is empty, you’re about to lose 1-3% per year to survivorship bias. Check before you trust the backtest.

A 20-year backtest on the current S&P 500 constituents looks dramatically better than the same strategy on a survivorship-free universe — the difference is the 30-40% of names that got delisted along the way. If your vendor pretends they don’t exist, your Sharpe is fake. Quick test: pick a major crisis (2008, 2020) and look at the stocks that fell more than 80%. How many are in your backtest universe? If the answer is “most of them got delisted and dropped from the index,” you have survivorship bias.

The same logic applies to fundamentals: a vendor that joins on report date instead of available date hands you lookahead bias on earnings surprises, guidance changes, and restatements. The pipeline’s job is to make both biases visible. If yours doesn’t surface them, change the vendor — or change the pipeline.

Pricing the Damage Honestly

One closing note of discipline on the arithmetic: rough estimates must stay in return-stream units. A stated 1% annual survivorship bias or 1% annual lookahead bias does not determine a Sharpe number — the effect depends on volatility, timing, serial dependence, turnover, and how the biased observations enter the strategy. Don’t spin those percentages into a Sharpe 0.8 → 1.1 → 1.4 story on your own. To compare honestly, build the survivorship-free, point-in-time return stream and the flawed comparison stream, then compute each annualized Sharpe as $\sqrt{252}\,\operatorname{mean}(r_t)/\operatorname{std}(r_t)$ under the same sampling and cost conventions — and let the numbers speak.

This is how quant shops lose money without knowing it, and the loss starts at the data layer. The fix is rigorous data infrastructure from day one. Data is the foundation: without point-in-time correctness and survivorship-free coverage, every downstream metric — Sharpe, IC, deflated Sharpe — is suspect. The next article covers the backtest engine that consumes this data, and the one after that covers the research-to-production lifecycle.

References

• López de Prado, M. Advances in Financial Machine Learning, Ch. 2 (Financial Data Structures). Wiley, 2018. ISBN: 978-1-119-48208-6.
• Aronson, D. Evidence-Based Technical Analysis, Ch. 6 (Statistical Inference). Wiley, 2006. ISBN: 978-0-470-00874-4.
• Campbell, J., Lo, A., MacKinlay, A. The Econometrics of Financial Markets, Ch. 2-3. Princeton University Press, 1997. ISBN: 978-0-691-04301-2.