Phase 17 - Lesson 17.6

Data Pipelines, SQL, and Research-to-Production

Where quant work really lives: clean data flowing through reliable stages, queried with SQL, and promoted from a notebook to a system that runs unattended.

⏱ 55 min● Intermediate🔗 Prereqs: 17.1, 17.3, 17.4

↖ Phase 17 hub

Builds on: 17.4 gave you modular design; a pipeline is that design made to run on real, messy data.
Leads to: The final capstone (18.8) is a research-to-production project: data → code → validation → report.

Learning Objectives

Click a status chip to cycle: Not started → In progress → Studied → Practiced → Needs review → Mastered.

Describe the stages of a research data pipeline and the guarantees each stage must provide.
Write SQL SELECT/WHERE/GROUP BY/JOIN queries and explain what each clause does.
Implement an in-memory SQL-style group-by aggregation with pandas and verify it against a manual computation.
Explain point-in-time correctness and how survivorship and look-ahead bias enter through the data layer.
Contrast a research notebook with a production job and list what must change to promote one.

Key Vocabulary

Data pipeline: An ordered set of stages - ingest, clean, transform, store, serve - each with a defined input, output, and guarantee.

SQL: Structured Query Language: a declarative language for selecting, filtering, aggregating, and joining tabular data.

GROUP BY: A SQL clause that partitions rows into groups so an aggregate (SUM, AVG, COUNT) is computed per group.

JOIN: Combining rows from two tables on a key (e.g. ticker+date), the tabular analogue of a merge.

Point-in-time (PIT): Data as it was known on a past date, with no later revisions leaked in - the antidote to look-ahead bias.

Idempotence: Re-running a stage yields the same result and no duplicates; essential for reliable, restartable pipelines.

Intuition & Motivation

Intuition

Most of a quant’s real time is not spent on clever math - it is spent getting clean, correctly-timed data to the math. A pipeline is the plumbing: raw ticks come in, get cleaned and aligned, get stored, and get served to research and production through the same queries. If the plumbing leaks - a revised price, a delisted stock silently dropped, tomorrow’s data used in today’s signal - every downstream result is poisoned, no matter how good the model.

SQL is the lingua franca of that data: a declarative way to say what you want (average return per sector this month) and let the engine figure out how. And research-to-production is the discipline of promoting a notebook that ran once, on your laptop, into a job that runs every day, unattended, correctly.

Anatomy of a research data pipeline

Stage	Input → Output	Guarantee it must provide
Ingest	vendor feed → raw store	capture exactly once; record source + timestamp
Clean	raw → validated	handle missing/duplicate/outlier; schema enforced
Align	validated → PIT panel	every value tagged with when it was known
Store	panel → database/table	immutable history; queryable by date/ticker
Serve	table → research/prod	same query path for both; no laptop-only steps

Key Idea

Research and production should read the same data through the same query. If your backtest reads a hand-cleaned CSV that production never sees, you are testing a system you will never run.

SQL fundamentals in five clauses

A SQL query is declarative: you describe the result set. The core clauses, in the order they logically apply:

FROM / JOIN - which table(s), combined on a key.
WHERE - keep only rows matching a condition (applied before grouping).
GROUP BY - partition rows into groups.
SELECT with aggregates - SUM, AVG, COUNT computed per group.
HAVING / ORDER BY - filter on the aggregate, then sort.

Example: average daily return per sector in 2023, for sectors with at least 20 names.

SELECT sector, AVG(ret) AS avg_ret, COUNT(*) AS n FROM prices WHERE dt >= '2023-01-01' GROUP BY sector HAVING COUNT(*) >= 20 ORDER BY avg_ret DESC;

This maps almost one-to-one onto pandas: WHERE→boolean mask, GROUP BY→groupby, aggregates→.agg, ORDER BY→sort_values. Learning one teaches the other.

Worked Example - A GROUP BY, by hand and in SQL/pandas

Data: rows of (sector, ret). We want mean return and count per sector.

SQL: SELECT sector, AVG(ret), COUNT(*) FROM t GROUP BY sector; - the engine partitions by sector, then averages.

pandas: t.groupby('sector')['ret'].agg(['mean','count']) - identical semantics.

By hand for ‘Tech’ = {0.01, 0.03, -0.02}: mean = \((0.01+0.03-0.02)/3=0.00667\), count = 3.

Verify the pandas/SQL result against this manual number with a tolerance (17.2).

Point-in-time correctness: where bias sneaks in

Two classic biases enter through the data layer, not the model:

Look-ahead bias: using data not yet knowable at decision time - e.g. a restated earnings figure, or today’s close to trade at today’s open. PIT storage (tagging each value with its knowledge date) prevents it.
Survivorship bias: a universe built from today’s index members omits companies that went bankrupt or were delisted, flattering every historical return. The universe must be reconstructed as it was on each past date.

\[\text{decision at }t \text{ may use only } \mathcal F_t=\{\text{data timestamped}\le t\}.\] (17.7)

Research to production: what must change

Concern	Research notebook	Production job
Execution	run by hand, top to bottom	scheduled, unattended, restartable
Data	a cleaned CSV on the laptop	the shared PIT database, same query
Errors	you see and fix them live	logged, alerted, must fail safe
Idempotence	re-run overwrites in place	re-run must not duplicate or corrupt
Config	hard-coded at top	injected; recorded per run (17.3)

Common Mistakes to Avoid

Building the backtest universe from today’s index - survivorship bias inflates every return.
Using revised/restated data instead of point-in-time values - silent look-ahead bias.
A JOIN that fans out (one-to-many key) and silently duplicates rows, doubling volumes.
Hand-editing a CSV for research that production never applies - you validate a system you won’t run.
Non-idempotent loads: re-running the pipeline appends duplicates instead of replacing a partition.
Filtering after aggregating when you meant before (WHERE vs HAVING) - wrong groups, wrong averages.

Quant Practitioner Tips

Store data point-in-time: every row tagged with the date it became known; query with ‘as of’ that date.
Reconstruct the historical universe date-by-date to kill survivorship bias.
Make every stage idempotent: writing partition dt=2023-05-01 replaces, never appends.
Test SQL/groupby results against a tiny hand-computed example (17.2) before trusting them at scale.
Promote to production by removing laptop-only steps: same data source, same query, injected config, logging + alerts.

Interactive: an in-memory SQL-style GROUP BY, verified by hand

Reproduce a GROUP BY sector aggregation with pandas and confirm it matches a manual computation on a tiny dataset - the same discipline you’d use before trusting a query on millions of rows.

Knowledge Check

Q1 Medium

In SQL, WHERE and HAVING differ because:

They are synonyms

WHERE filters rows before grouping; HAVING filters groups after aggregation

HAVING is faster

WHERE only works on numbers

Q2 Medium

Building a backtest universe from today’s index constituents causes:

Look-ahead bias

Survivorship bias - delisted/bankrupt names are omitted, inflating historical returns

No bias

A syntax error

Q3 Medium

The key data guarantee that prevents look-ahead bias is:

Faster queries

Point-in-time storage: each value tagged with the date it was known, so a decision at t uses only data timestamped ≤ t

Using more data

Deleting old data

Practical Exercise

Write the SQL for: ‘For each month in 2023, the equal-weight average return across all stocks in the Financials sector, only for months with at least 10 names.’ Then (a) name the two data-layer biases you must guard against for this query to be trustworthy, and (b) state one change required to run this daily in production rather than in a notebook.

▶ Show full solution

SQL:

SELECT DATE_TRUNC('month', dt) AS mon, AVG(ret) AS ew_ret, COUNT(*) AS n FROM prices WHERE sector = 'Financials' AND dt >= '2023-01-01' AND dt < '2024-01-01' GROUP BY DATE_TRUNC('month', dt) HAVING COUNT(*) >= 10 ORDER BY mon;

(a) Survivorship bias: if prices only contains names still listed today, failed Financials are missing and the average is inflated; the table must include historically-listed names with point-in-time membership. Look-ahead bias: ret and sector must be point-in-time values (no restated classifications or revised returns leaking in).

(b) To run daily in production: read from the shared point-in-time database via this same query (not a hand-cleaned CSV), schedule it as an idempotent job that rewrites each date’s partition, inject the date range as config, and add logging/alerting so a failed or empty run is caught rather than silently producing wrong numbers.

After the reveal, answer for yourself: Which single data-layer defect would most quietly invalidate a year of research, and why?

Lesson Summary

Quant results are only as good as the data plumbing beneath them. A pipeline moves data through ingest → clean → align → store → serve, each stage with a guarantee; SQL (SELECT/WHERE/GROUP BY/JOIN/HAVING) is how you query it, mapping directly onto pandas. Point-in-time storage and correct historical universes prevent look-ahead and survivorship bias, and research-to-production means the backtest and the live job read the same data through the same query, run idempotently, unattended, and logged.

Retrieval Practice

Close the lesson and answer from memory before checking. This is deliberate, effortful recall - the single highest-yield study action.

▶ Show retrieval prompts & answers

Q: What do WHERE, GROUP BY, and HAVING each do in a SQL query?
A: WHERE filters individual rows before grouping; GROUP BY partitions the surviving rows into groups; HAVING filters those groups after the aggregate is computed (e.g. keep groups with COUNT≥20).

Q: Name the two data-layer biases and how the data layer prevents each.
A: Survivorship bias (fix: reconstruct the historical universe as of each date, including delisted names) and look-ahead bias (fix: point-in-time storage, so a decision at t uses only data timestamped ≤ t).

Completion Checklist

I can explain the core ideas in my own words
I worked the derivations/examples by hand
I completed the interactive workbench(es)
I passed the knowledge check

Confidence / mastery rating

Personal notes

Source References

This lesson synthesizes and paraphrases concepts from the sources below. No copyrighted text, problem sets, or solutions are reproduced. Return to the originals for full depth.

The Pragmatic Programmer (Thomas & Hunt, 2nd/20th Anniv. ed., 2019) current - Ch. 3, 7, 9 - Automation, idempotent/reproducible processes, and promoting prototypes to reliable systems.
Time Series Analysis (James Hamilton, 1994) foundational - Ch. 1-2 - Time-indexed data and the discipline of using only information available up to the decision date.

← 17.5 Next: 18.1 →