DuckDB stock data Parquet guide

Run SQL directly against OHLCV Parquet files -- no database load step, no CSV parsing, and full-day cross-sectional queries in tens of milliseconds. Every example below was run against real MarketParquet files and shows its actual output.

File layout and schema

MarketParquet files are partitioned by trading date: by_date/{asset}_{timeframe}/YYYY/YYYY-MM-DD.parquet. Each file contains every symbol for one trading day -- a 2024 stock 1-minute file is ~22 MB, 1.28 million rows, 7,180 symbols. 

  columns (intraday):
    timestamp    TIMESTAMP[us]  -- bar OPEN time, US/Eastern, tz-naive
    symbol       VARCHAR        -- 'AAPL'; delisted end in '-DELISTED'
    asset_type   VARCHAR
    open/high/low/close  DOUBLE -- split-adjusted
    volume       DOUBLE

  columns (daily): same, but a DATE column named 'date'
                   (files before 2026-03-27: legacy 'timestamp' instead)

Two things trip people up on day one: stocks and ETFs are separate datasets (SPY lives in etf_* files, not stock_* -- an empty result for SPY usually means you are querying the wrong asset class), and intraday timestamps cover the extended session, 04:00 premarket through 19:59 post-market bar opens.

Query one day

  import duckdb

  con = duckdb.connect()
  bars = con.execute("""
      SELECT timestamp, open, high, low, close, volume
      FROM 'stock_1min_2024-01-03.parquet'
      WHERE symbol = 'AAPL'
      ORDER BY timestamp
  """).fetchdf()

  # 741 rows in ~6 ms. First bars of the premarket:
  #            timestamp    open    high     low   close  volume
  # 0 2024-01-03 04:00:00  185.00  185.00  185.00  185.00   791.0
  # 1 2024-01-03 04:01:00  184.90  184.90  184.90  184.90  1278.0

Note the row count: 741 bars, not 960. Bars exist only for minutes the symbol actually traded -- nothing is forward-filled. If you need a dense grid (e.g. for matrix ops), generate the session minutes with generate_series and LEFT JOIN the bars onto it.

Regular session only

Because timestamps are bar opens in Eastern time, the regular session is a simple range filter -- here computing session VWAP for three symbols on 2024-01-03: 

  SELECT symbol,
         ROUND(SUM(close * volume) / SUM(volume), 2) AS vwap,
         SUM(volume)::BIGINT AS shares
  FROM 'stock_1min_2024-01-03.parquet'
  WHERE timestamp >= '2024-01-03 09:30'
    AND timestamp <  '2024-01-03 16:00'
    AND symbol IN ('AAPL', 'TSLA', 'NVDA')
  GROUP BY symbol ORDER BY symbol;

  -- symbol    vwap      shares
  -- AAPL    184.36  37,331,553
  -- NVDA     47.69 219,800,480     <- 47.69, not 476.9: split-adjusted
  -- TSLA    239.77  97,936,006

Query many files with globs

DuckDB expands globs and pushes filters down into the Parquet scans, reading only the row groups and columns a query needs, so scanning a month or a year of files stays fast without any loading step: 

  -- a month, cross-sectional
  SELECT symbol, AVG(close) AS avg_close, SUM(volume) AS total_volume
  FROM 'by_date/stock_daily/2024/2024-01-*.parquet'
  GROUP BY symbol ORDER BY total_volume DESC LIMIT 20;

  -- a whole year for one symbol
  SELECT * FROM 'by_date/stock_1min/2024/*.parquet'
  WHERE symbol = 'NVDA' ORDER BY timestamp;

Add filename = true inside read_parquet(...) if you want to know which file each row came from -- useful when auditing a suspicious bar.

Resample 1-minute to 5-minute

time_bucket plus ordered first/last aggregates gives correct OHLCV resampling in one query: 

  SELECT time_bucket(INTERVAL 5 MINUTE, timestamp) AS bar_open,
         first(open ORDER BY timestamp)  AS open,
         MAX(high)                       AS high,
         MIN(low)                        AS low,
         last(close ORDER BY timestamp)  AS close,
         SUM(volume)::BIGINT             AS volume
  FROM 'stock_1min_2024-01-03.parquet'
  WHERE symbol = 'AAPL'
  GROUP BY bar_open ORDER BY bar_open;

  --    bar_open              open    high     low   close  volume
  -- 2024-01-03 04:00:00    185.00  185.00  184.90  184.90    3932
  -- 2024-01-03 04:05:00    184.85  184.88  184.73  184.88    1632

That said, MarketParquet already ships native 5-minute, 30-minute, and 1-hour files built by the vendor from the tape -- prefer those for research; resample yourself only for non-standard intervals.

Edge case: legacy daily files

Daily files written since 2026-03-27 have a typed date column; older archive files carry a midnight timestamp instead. When a query spans both eras, read with union_by_name and coalesce: 

  SELECT COALESCE(date, CAST(timestamp AS DATE)) AS d, symbol, close
  FROM read_parquet('by_date/stock_daily/*/*.parquet', union_by_name = true)
  WHERE symbol = 'AAPL' ORDER BY d;

  -- d           symbol   close
  -- 2010-01-04  AAPL     7.6432    <- 214.01 as traded / 28 (two splits)
  -- ...
  -- 2026-07-24  AAPL   333.02

Survivorship bias in one WHERE clause

Delisted tickers stay in every file with a -DELISTED suffix (1,194 of them in the 2024-01-03 1-minute file alone). Include them in backtests; exclude them only when you explicitly want today's survivors: 

  -- the universe as it actually existed that day (default: keep everything)
  SELECT COUNT(DISTINCT symbol) FROM 'stock_daily_2010-01-04.parquet';   -- 5,876

  -- survivors only (this is how survivorship bias sneaks in)
  SELECT COUNT(DISTINCT symbol) FROM 'stock_daily_2010-01-04.parquet'
  WHERE symbol NOT LIKE '%-DELISTED';                                    -- 2,360

How fast is it?

Measured on a 2-core / 2 GB VPS (not a workstation), against the 1.28M-row 2024-01-03 1-minute file:

  • COUNT(*) + distinct symbols over the file: 9 ms
  • Full-day GROUP BY symbol volume ranking (7,180 groups): 22 ms
  • Single-symbol extract, sorted: 6 ms

Filter and projection pushdown mean DuckDB decompresses only the columns and row groups it needs -- which is the entire point of shipping this data as Parquet instead of CSV.

FAQ

Why is SPY / QQQ missing from stock files? They are ETFs -- query etf_1min / etf_daily instead.

Are timestamps UTC? No -- US/Eastern, timezone-naive, bar open time. Convert with timezone('America/New_York', 'UTC', ts) only if your stack requires UTC.

Why do prices differ from Yahoo's "Adj Close"? MarketParquet prices are split-adjusted but not dividend-adjusted, so you see the levels that actually traded. See data quality & methodology.

Can DuckDB read the files over HTTP? Yes -- httpfs works with the presigned URLs the download API returns, but for repeated research downloading once to local disk is faster and kinder to your quota.

Related

stock data hub · browse stock daily · data quality & methodology · Polars guide · local data lake guide · pricing