SQL Cookbook

DuckDB + Parquet recipes for Valuein

Seven query patterns that cover ~80% of what quants and analysts ask the warehouse. Every snippet runs against the real Parquet schema — copy, paste, edit the ticker, run.

Start with references Latest filing per company Pivot concepts QUALIFY ROW_NUMBER()Quarterly cash flow Point-in-time screen Defensive ratio math

Before you start

All snippets assume Parquet files are local — pulled from the Bulk Data API or via the Python SDK. You can also point read_parquet() directly at an HTTPS URL with DuckDB's httpfs extension.

setup.sqlsql

1-- One-time setup2INSTALL httpfs;3LOAD httpfs;4 5-- Read directly from a presigned R2 URL (compute_ready_stream)6SELECT COUNT(*)7FROM read_parquet('https://r2.example.com/full/fact.parquet?...');

Start every cross-company query with `references`

`references` is a pre-joined entity + security table — one row per security. Start here, then JOIN `index_membership` on `cik` for any membership filter.

Why this works ↓

The `references` table is a flat join of entity + security — one row per security with `cik`, `sector`, `is_active`, and ticker metadata already populated. Joining entity and security yourself works too, but adds two scans and an order-of-magnitude planner cost. For index membership, JOIN with `index_membership` on `cik = cik` (same column name on both sides since migration 0015). The old `is_sp500` flag was dropped 2026-05-02 — it was snapshot-only and single-index, so all membership questions go through `index_membership`.

sql

1SELECT2  r.symbol,3  r.name,4  r.sector,5  r.industry6FROM read_parquet('references.parquet') r7JOIN read_parquet('index_membership.parquet') im8  ON im.cik = r.cik9WHERE im.index_name = 'SP500'10  AND im.removal_date IS NULL  -- current member11  AND r.is_active = TRUE12ORDER BY r.sector, r.symbol;

Note · Use this as the spine of any screen, peer search, or universe construction.

Latest filing per company with LATERAL

Use a LATERAL join to correlate one row per company — never pull all filings and dedupe in pandas. `fact` keys on `entity_id` (= references.cik), not ticker.

Why this works ↓

The most common bug in cross-sectional fundamentals queries: pulling all filings then deduplicating in pandas. DuckDB's LATERAL join lets you correlate one row per company without a self-join. Note: the `fact` table keys on `entity_id` (= references.cik), not on ticker.

sql

1WITH r AS (2  SELECT r.cik, r.symbol, r.sector3  FROM read_parquet('references.parquet') r4  JOIN read_parquet('index_membership.parquet') im5    ON im.cik = r.cik6  WHERE im.index_name = 'SP500'7    AND im.removal_date IS NULL8    AND r.is_active = TRUE9)10SELECT11  r.symbol,12  r.sector,13  f.fiscal_year,14  f.fiscal_period,15  f.numeric_value AS revenue16FROM r,17LATERAL (18  SELECT fiscal_year, fiscal_period, numeric_value19  FROM read_parquet('fact.parquet') AS f020  WHERE f0.entity_id        = r.cik21    AND f0.standard_concept = 'TotalRevenue'22    AND f0.fiscal_period    = 'FY'23  ORDER BY f0.fiscal_year DESC, f0.accepted_at DESC24  LIMIT 125) f26ORDER BY revenue DESC NULLS LAST27LIMIT 25;

Note · LATERAL evaluates the inner query once per outer row but stops at LIMIT 1 — much cheaper than a full self-join.

Pivot multiple concepts in a single fact scan

One MAX(CASE) pivot scans `fact` once and returns revenue, net income, and EPS as columns — the difference between 200ms and 2s across 111M+ rows.

Why this works ↓

Naively, pulling revenue + net income + EPS means three queries against the fact table. A single MAX(CASE) pivot scans `fact` once and returns all three as columns. For 111M+ rows, that's the difference between 200ms and 2s. The fact table joins on entity_id (= references.cik) — there is no ticker column on fact.

sql

1WITH ids AS (2  SELECT cik, symbol3  FROM read_parquet('references.parquet')4  WHERE symbol IN ('AAPL', 'MSFT', 'GOOGL', 'NVDA')5)6SELECT7  ids.symbol,8  fa.fiscal_year,9  MAX(CASE WHEN fa.standard_concept = 'TotalRevenue'      THEN fa.numeric_value END) AS revenue,10  MAX(CASE WHEN fa.standard_concept = 'NetIncomeLoss'     THEN fa.numeric_value END) AS net_income,11  MAX(CASE WHEN fa.standard_concept = 'EarningsPerShareDiluted' THEN fa.numeric_value END) AS eps_diluted,12  MAX(CASE WHEN fa.standard_concept = 'NetCashProvidedByUsedInOperatingActivities' THEN fa.numeric_value END) AS ocf13FROM read_parquet('fact.parquet') fa14JOIN ids ON ids.cik = fa.entity_id15WHERE fa.fiscal_period = 'FY'16  AND fa.fiscal_year >= 202017GROUP BY ids.symbol, fa.fiscal_year18ORDER BY ids.symbol, fa.fiscal_year;

Note · Add as many concepts as you need — the cost is one extra CASE per concept, not one extra scan.

Use QUALIFY ROW_NUMBER() instead of correlated subqueries

QUALIFY ROW_NUMBER() returns the latest accepted_at per (entity, period) far cleaner than a correlated MAX subquery — the idiomatic way to dedupe original-vs-amended filings.

Why this works ↓

When you need the latest accepted_at for each (entity, period) — to dedupe original-vs-amended filings — QUALIFY is dramatically cleaner than a correlated MAX subquery. Resolve a ticker to its CIK via references first; fact has no ticker column.

sql

1WITH tsla AS (2  SELECT cik FROM read_parquet('references.parquet') WHERE symbol = 'TSLA'3)4SELECT5  fa.entity_id,6  fa.fiscal_year,7  fa.fiscal_period,8  fa.standard_concept,9  fa.numeric_value,10  fa.accepted_at11FROM read_parquet('fact.parquet') fa12JOIN tsla ON tsla.cik = fa.entity_id13WHERE fa.standard_concept = 'NetIncomeLoss'14QUALIFY ROW_NUMBER() OVER (15  PARTITION BY fa.entity_id, fa.fiscal_year, fa.fiscal_period, fa.standard_concept16  ORDER BY fa.accepted_at DESC17) = 118ORDER BY fa.fiscal_year DESC, fa.fiscal_period;

As reported vs restatedFY2020, $M. A point-in-time query never silently swaps one for the other.

Metric	as reported	restated
Revenue	10,388	10,388
Operating income	2,114	1,902
Net income	1,640	1,431
Diluted EPS	3.05	2.66

verify_fact_lineage() → both versions retained · each keyed to its filing

Note · Returns one row per fiscal period — the most recently accepted (i.e. post-amendment) value. Drop the QUALIFY clause to see all amendments. Tip: fact also exposes value_current and value_as_filed if you want both restated and originally-filed values pre-computed.

Quarterly cash flow without YTD bleed

Q2/Q3 10-Q cash flows are year-to-date. COALESCE on `derived_quarterly_value` to get the true quarter-only figure the pipeline pre-computes.

Why this works ↓

Q2 and Q3 10-Q cash flow figures are year-to-date, not quarter-only. The pipeline computes the incremental quarterly figure as `derived_quarterly_value`. Use COALESCE to prefer the derived value where present.

sql

1WITH amzn AS (2  SELECT cik FROM read_parquet('references.parquet') WHERE symbol = 'AMZN'3)4SELECT5  fa.entity_id,6  fa.fiscal_year,7  fa.fiscal_period,8  COALESCE(fa.derived_quarterly_value, fa.numeric_value) AS quarterly_ocf9FROM read_parquet('fact.parquet') fa10JOIN amzn ON amzn.cik = fa.entity_id11WHERE fa.standard_concept = 'NetCashProvidedByUsedInOperatingActivities'12  AND fa.fiscal_period IN ('Q1', 'Q2', 'Q3', 'Q4')13  AND fa.fiscal_year >= 202214ORDER BY fa.fiscal_year, fa.fiscal_period;

Note · For annual numbers (FY rows) `numeric_value` is correct — the COALESCE just falls back to it when no quarterly derivation exists.

Point-in-time screen: what was investable on a date

Filter `index_membership` by effective/removal date AND `fact.accepted_at` so both the company and its data were knowable — survivorship-free and look-ahead-free.

Why this works ↓

Backtesting a strategy requires the universe that existed on the trade date — not today's universe. Filter `index_membership` by `effective_date` / `removal_date` AND `fact.accepted_at` to ensure both the company AND its data were knowable. index_membership keys on cik (since migration 0015) and uses half-open [) interval semantics — a company removed on date D is NOT a member on date D.

sql

1WITH universe_at AS (2  SELECT r.cik, r.symbol3  FROM read_parquet('references.parquet') AS r4  JOIN read_parquet('index_membership.parquet') AS im5    ON im.cik = r.cik6  WHERE im.index_name = 'SP500'7    AND im.effective_date <= DATE '2018-01-01'8    AND (im.removal_date IS NULL OR im.removal_date > DATE '2018-01-01')9),10known_facts AS (11  SELECT12    fa.entity_id,13    fa.fiscal_year,14    MAX(CASE WHEN fa.standard_concept = 'TotalRevenue'  THEN fa.numeric_value END) AS revenue,15    MAX(CASE WHEN fa.standard_concept = 'NetIncomeLoss' THEN fa.numeric_value END) AS net_income16  FROM read_parquet('fact.parquet') fa17  WHERE fa.accepted_at <= TIMESTAMP '2018-01-01 00:00:00'18    AND fa.fiscal_period = 'FY'19    AND fa.fiscal_year = 201720  GROUP BY fa.entity_id, fa.fiscal_year21)22SELECT23  u.symbol,24  k.revenue,25  k.net_income,26  k.net_income / NULLIF(k.revenue, 0) AS net_margin27FROM universe_at u28JOIN known_facts k ON k.entity_id = u.cik29ORDER BY net_margin DESC NULLS LAST30LIMIT 50;

The dead are still in the indexDelisted, bankrupt, merged, taken private — kept, not survivorship-pruned.

ENRNbankrupt · 2001
LEHbankrupt · 2008
WAMUseized · 2008
BBBYbankrupt · 2023
SIVBfailed · 2023
FTXcollapsed · 2022

get_pit_universe(date) → the index as it stood, not as it survived

Note · The universe filter prevents survivorship bias; the accepted_at filter prevents look-ahead bias. Both are required.

Defensive ratio math: ABS and NULLIF every divisor

Wrap every cash-flow denominator in ABS (capex is reported negative) and every divisor in NULLIF (zero-revenue quarters) — make it a reflex.

Why this works ↓

Capex is reported as negative on cash flow statements; some companies have zero revenue in a given quarter. Wrap every divisor in NULLIF and every cash-flow denominator in ABS.

sql

1WITH ids AS (2  SELECT cik, symbol3  FROM read_parquet('references.parquet')4  WHERE symbol IN ('AAPL', 'MSFT', 'GOOGL', 'AMZN', 'META')5),6pivoted AS (7  SELECT8    ids.symbol,9    fa.fiscal_year,10    MAX(CASE WHEN fa.standard_concept = 'TotalRevenue'                                THEN fa.numeric_value END) AS revenue,11    MAX(CASE WHEN fa.standard_concept = 'NetCashProvidedByUsedInOperatingActivities' THEN fa.numeric_value END) AS ocf,12    MAX(CASE WHEN fa.standard_concept = 'PaymentsToAcquirePropertyPlantAndEquipment' THEN fa.numeric_value END) AS capex13  FROM read_parquet('fact.parquet') fa14  JOIN ids ON ids.cik = fa.entity_id15  WHERE fa.fiscal_period = 'FY'16    AND fa.fiscal_year   = 202417  GROUP BY ids.symbol, fa.fiscal_year18)19SELECT20  symbol,21  fiscal_year,22  ocf,23  capex,24  -- ABS handles companies that report capex as negative;25  -- NULLIF prevents division-by-zero on revenue gaps.26  ocf - ABS(capex) AS free_cash_flow,27  (ocf - ABS(capex)) / NULLIF(revenue, 0) AS fcf_margin28FROM pivoted29ORDER BY fcf_margin DESC NULLS LAST;

Note · Make NULLIF on every divisor a habit — DuckDB returns NULL on divide-by-zero, but errors on integer divide-by-zero in some compilation modes.

More patterns?

The Python SDK ships with pre-built query templates for the most common workflows — factor screens, DCF inputs, peer comparables, insider ownership.

Python SDK templates Parquet schema reference