One parquet per year, and the glob that ate DuckDB

Three weeks ago we backfilled NSE to 2010. The next morning a query for the full RELIANCE history was tens of seconds on a cold Cloud Run instance, and the DuckDB process got OOM-killed twice under light load. Same query on the 2024-onward data used to take about a second. Nothing in the API had changed. The layout of the data had.

This is a short build log on how we store bhavcopy now: one parquet file per exchange per year, refreshed nightly, and why that is the right shape for both DuckDB at the origin and Polars in your notebook.

The shape we started with

The pipeline wrote one parquet per trading day, hive-partitioned so DuckDB could prune on the path:

nse/year=2010/month=01/date=2010-01-04.parquet
nse/year=2010/month=01/date=2010-01-05.parquet
...
nse/year=2026/month=06/date=2026-06-05.parquet

# about 5,000 files across NSE and BSE

Each file is small: one trading day, a few thousand rows. That is a fine shape for writing, since the cron only ever touches today's file. It is a terrible shape for reading, because DuckDB has to LIST the prefix and open every file the glob matches before it can filter a single row.

SELECT date, close
FROM read_parquet('r2://tej-bazaar/nse/**/*.parquet', hive_partitioning=1)
WHERE symbol = 'RELIANCE'
  AND date BETWEEN '2010-01-04' AND '2026-06-05';

-- One LIST over ~5,000 keys, then one footer read per file,
-- all over HTTPS to a bucket in another region, on every cold query.

Hive pruning helps when the filter is on the partition column. Our filter is on symbol, which lives inside every file. So the year partition prunes nothing for a 16-year range, and DuckDB dutifully reads every footer.

The shape we use now

A new compact command folds one year of dailies into a single rollup, and the nightly cron re-runs it for the current year after appending today's file:

nse/year=2010/nse_2010.parquet     # 1 file, 8 MB
nse/year=2011/nse_2011.parquet
...
nse/year=2025/nse_2025.parquet     # 17 MB, more symbols, more series
nse/year=2026/nse_2026.parquet     # rewritten every trading day

# 17 NSE files + 3 BSE files

# One-time: compact every past year, delete the dailies once the rollup is verified
for y in $(seq 2010 2025); do
  tej-bazaar compact --year $y -e nse --prune
done

# Nightly, in the cron: fold today's file into the current year
tej-bazaar compact --year 2026 -e nse --refresh

One detail in the rollup matters more than it looks. year and month are written as real columns, not only as path segments, so a query against rollups alone can still filter on them without hive parsing. This is also the detail that comes back to bite us below.

The API side changed in step. Instead of one unbounded glob we build one glob per year in the requested range and pass them as a list. DuckDB's httpfs glob does not do brace expansion, so year={2010,2011} is not an option; the list is the canonical fan-out. And we cap memory so a wide query spills instead of dying:

// 2010..2026 -> ['r2://tej-bazaar/nse/year=2010/**/*.parquet', ...]
// The ** matches both the rollup and, for the current year, today's daily file.
parts := make([]string, 0, last-first+1)
for y := first; y <= last; y++ {
    parts = append(parts, fmt.Sprintf("'r2://%s/%s/year=%d/**/*.parquet'", bucket, prefix, y))
}
arg := "[" + strings.Join(parts, ", ") + "]"
// read_parquet(arg, union_by_name=true): the LIST is bounded to the years asked for.

// On connection open:
//   SET memory_limit = '1GB';
//   SET threads = 4;

Seventeen files instead of five thousand. The full 16-year RELIANCE series, 4,047 rows, now comes back in about a second and a half end to end from a warm instance, network included, and the OOM kills stopped. We did not keep a rigorous before number because the before was not stable enough to measure.

The schema mismatch, two months later

Here is the bug we shipped with it. The rollup carries year and month as columns. The daily file for today does not; it gets them from the hive path. For most of the day there are only rollups, and everything is fine. Between the fetch step and the compact step of the nightly cron, though, the current year holds one rollup and one daily side by side, and the derived jobs that read the whole year with Polars hit a SchemaError: the rollup frame is two columns wider than the daily frame, and a plain pl.concat refuses to stack them.

The fix is small and lives in one reader used by every derived step: read each file with hive partitioning off, drop year and month if present, then concatenate with relaxed schema. The path is not the schema. The columns are.

def _read_prices(paths: Iterable[Path]) -> pl.DataFrame:
    frames = []
    for p in paths:
        f = pl.read_parquet(p, hive_partitioning=False)
        f = f.drop([c for c in ("year", "month") if c in f.columns])
        frames.append(f)
    return pl.concat(frames, how="vertical_relaxed")

A second, sillier bug rode along: the year detector looked for month= directories to decide which years existed, and a year that had been fully compacted has none. So the cron thought 2010 through 2025 were empty and skipped them in derived builds. It now treats a rollup file as proof the year exists.

What this means for you

If you read our parquet directly, the layout on R2 and HuggingFace is now the rollup layout. One file per year, so a full-history scan is 17 reads, not 4,000:

import polars as pl

df = pl.scan_parquet(
    "hf://datasets/tejhq/indian-markets/nse/year=*/nse_*.parquet"
).filter(pl.col("symbol") == "RELIANCE").collect()
# 4,047 rows, ~2 s from a cold cache

The API did not change shape at all, only speed. If you had wrapped a retry around wide range queries, you can take it off.