How to store years of minute bars locally

The short way

For one machine and a research workload, the shortest thing that holds up is Parquet files by year, written by DuckDB and read by it in place. Nothing runs in the background, and the files stay readable by pandas, Polars or whatever you move to next.

import os
import duckdb

con = duckdb.connect()

def write_year(year: int) -> None:
    out_dir = f"store/minute/year={year}"
    os.makedirs(out_dir, exist_ok=True)
    tmp = f"{out_dir}/bars.parquet.tmp"
    # raw files: symbol, ts (already UTC), open, high, low, close, volume, unadjusted
    con.sql(f"""
        COPY (
            SELECT symbol, ts, open, high, low, close, volume
            FROM read_csv('raw/{year}/*.csv')
            ORDER BY symbol, ts
        ) TO '{tmp}' (FORMAT parquet)
    """)
    os.replace(tmp, f"{out_dir}/bars.parquet")   # a re-run replaces the year, never adds to it

SELECT ts, open, high, low, close, volume
FROM read_parquet('store/minute/*/bars.parquet', hive_partitioning = true)
WHERE symbol = 'AAPL'
  AND year BETWEEN 2019 AND 2020
ORDER BY ts;

Three decisions are baked into those twenty lines, and each one is on this page because the obvious alternative fails.

The unit of rewrite is the partition, not the row. A year is rebuilt whole from the raw files and swapped in with a rename, so running the job twice leaves the same file, not two. That is deliberately not DuckDB's own PARTITION_BY ... APPEND, which the documentation describes as writing each batch under a new UUID filename: correct for a live feed, and a duplicate of every row for a backfill you ran twice.

Sorted by symbol, then time. A query for one name over a year reads one contiguous run of each file instead of every block in it.

Year, not day and not symbol. DuckDB's documentation says writing many small partitions is expensive and puts the floor at about 100 MB each. A regular US session is 390 minutes, so one symbol-year is about 98,000 rows — a few megabytes — and a day of 500 symbols under 200,000. Both are far below that floor. For ten thousand symbols, about a billion rows a year, a month per partition is the better split.

What the options are

Files plus an embedded engine. DuckDB over Parquet as above: MIT-licensed, no server and no paid edition. A DuckDB database file has one writing process at a time, which is a real limit for a feed and none at all for history held in Parquet.

An embedded store with versions. ArcticDB keeps a pandas DataFrame per symbol in a library on local disk (LMDB) or S3, and every write, append and update creates a new version, which you can read back later. For bars that versioning is the useful part: last month's research can be re-run against last month's data after you have repaired this month's. It is Python only, and source-available under BUSL 1.1. The licence text, the README and the FAQ disagree about commercial use, and the FAQ, the strictest of the three, says business use needs a paid licence.

A time-series server. QuestDB is the answer when something keeps appending while something else queries. Its SQL has SAMPLE BY to build five-minute or hourly bars from the minute table and ASOF JOIN when you add quotes later. TimescaleDB is the same job inside Postgres. Continuous aggregates keep coarser bars current, and every Postgres tool already speaks to it.

A columnar analytics engine. ClickHouse stores bars very compactly and scans them fast. It is more machine than most bar stores need, and its deduplication model is the one below that needs the most care.

Where this breaks

Re-running the backfill writes the rows twice. Every backfill gets re-run: a vendor corrects a day, a job dies halfway through 2019, you add fifty symbols. Whether the second run replaces or duplicates is an engine property, and the defaults are rarely the safe ones.

• ArcticDB's append accepts only data whose first row is at or after the last stored row. It refuses data that starts earlier and accepts data that starts on the last stored timestamp, so a resumed job that re-sends its last bar writes that minute twice. update is the repeatable operation, and its documentation is exact about the cost: the whole range between the first and last index of the new data is replaced. A re-sent day with a gap in it deletes the stored bars inside the gap.
• QuestDB deduplicates only when a table is created with DEDUP UPSERT KEYS, and the designated timestamp must be one of the keys. With (ts, symbol) as keys, a re-sent bar replaces the old one: last write wins. Keys added later do not clean up what is already there: the documentation says turning deduplication on affects new inserts only.
• TimescaleDB needs a unique index for ON CONFLICT to do anything, and on a hypertable that index must include the time column. Once it exists, ON CONFLICT DO UPDATE repairs rows in place, including in compressed chunks.
• ClickHouse's ReplacingMergeTree removes duplicates only when parts merge, at a time the documentation does not promise. Until then a sum over a re-loaded day counts it twice unless the query says FINAL.

Test this before the store holds anything you care about: load one week, load it again, count.

The timestamp is a convention. A minute bar stamped 09:30 covers 09:30 to 09:31 if the vendor labels by the start of the interval, and 09:29 to 09:30 if it labels by the end. Vendors do not agree, and not all of them say. FirstRate Data documents the start of the bar in US Eastern time. Alpaca's bar reference documents the timestamp's format — RFC-3339 with nanosecond precision — and not which edge of the minute it marks. Load an archive from one and top it up from the other and you can get a series that is quietly off by a minute, or by several hours, at the seam.

The hours are the part that bites. US daylight saving time begins on the second Sunday of March and ends on the first Sunday of November, so the 09:30 Eastern open is 13:30 UTC for most of the year and 14:30 UTC for the rest. A store that mixes local and UTC stamps, or one that stores Eastern time with no zone at all, puts the open at two different UTC times depending on the season. Convert everything to UTC at load time and keep the vendor's convention in a column or a note. QuestDB's own documentation says it stores every timestamp in UTC without zone information and tells you to convert local-time sources with to_utc before they are written.

To check a vendor's convention, compare one day they both cover. Find the bar that contains the opening print in each and see which label it carries.

Minute bars do not add up to the official daily bar. FirstRate Data states that its daily bars are a separate series built from the exchange's official figures and cannot be reproduced by aggregating its minute bars. Store the daily series as its own table rather than deriving it from the minute table and trusting the result to match a broker statement.

Adjusted bars go stale in storage. The next split puts every adjusted row already on disk out of step with a fresh pull, so store unadjusted bars beside the corporate action table and adjust when you read. How to backfill years of minute bars makes that case at the download end, and why adjusted close differs is the long version.

A nightly append fragments the store. ArcticDB slices a symbol into segments of 100,000 rows by default, so a year of one symbol's minute bars fits roughly one segment. Appending one day at a time instead writes 252 segments of 390 rows each. Its API has a compaction call for exactly this, compact_data, which replaces the deprecated defragment_symbol_data. The Parquet equivalent is a directory of daily files that should have been one annual file. Either way, the fix is to rewrite the period in bulk once it is complete.

If you outgrow this

When bars stop being enough, the next step is trades and quotes, which is two to three orders of magnitude more data and a different question about engines: choosing a database for tick data. The as-of join matters there and barely at all here.

When the bars are not yet on disk, getting them is its own purchase with its own traps — survivorship, adjustment and the licence. That is how to backfill years of minute bars.

When somebody else will query the store, read the data licence before the storage licence. FirstRate Data, Kibot and Alpaca all restrict redistribution of what they sell, and a shared database is where internal use quietly stops being internal.

When a live feed joins the history, the embedded options stop fitting. DuckDB has one writing process, and ArcticDB does not support concurrent writers to one symbol outside its staging calls. QuestDB and TimescaleDB are built for concurrent writes and reads.

The rest of the field is in tick data storage.