Tick data grows quickly: a year of EURUSD ticks can be several gigabytes. The storage format affects speed and convenience.
A good practice is to keep a small sample file of each format for quick checks, and to validate the data after each download: gaps in timestamps or unrealistic spreads usually point to a feed problem.
Options:
CSV: simple and readable, but large and slow to load.
Parquet: compressed, columnar, fast to read with pandas or polars. Good for analysis.
HDF5: fast, but less convenient for appending.
Databases: SQLite for simple use, time-series databases for large datasets.
Practical tips:
Store one file per symbol per month.
Store timestamps in UTC.
Keep raw data separate from processed data.
What format do you use for tick data, and how much do you store?
Parquet split by symbol and month is what I ended up with too, but the bigger saving for me came from the column types. Storing bid and ask as integer points instead of float64 cut the files a lot and removed the odd rounding surprise.
For EURUSD that means price times 100,000 as an int32, and the timestamp as an int64 in milliseconds UTC. With zstd compression on top, a month of ticks loads in pandas in a second or two on an ordinary laptop.
On UTC, the trap is conversion at download time. Many broker exports come in server time, GMT+2 or GMT+3 by season, and with the wrong offset for a few weeks a year every session filter you run later is off by an hour.
For validation, the gap check needs to know about weekends and holidays, or it flags every Friday close as a feed problem. I keep a small table of expected market closed periods and only flag gaps outside those.
What do you do with several ticks on the same millisecond? I add a sequence number column so the original order survives sorting, but I'm not sure that's the usual approach.
A point I'd add to "keep raw separate from processed": keep the source as well. Ticks from two different brokers aren't the same data, even for the same pair on the same day, and mixing them quietly breaks a scalping backtest.
Spreads differ, tick frequency differs, and the price itself can sit a fraction of a pip apart. I store the feed name as a partition in the path, so EURUSD from one source never ends up in the same query as another unless I ask for it.
For tools, DuckDB has been the biggest change for me. It queries the Parquet files directly, so building M1 bars or checking the median spread per hour across a year is one SQL line without loading everything into memory.
On how much to store, I keep two years for six pairs plus gold, and that's been enough. Older data from before a broker changed its liquidity setup tends to mislead more than it helps for short hold times.
Do you store anything besides bid and ask, like the volume field some feeds send? I've never found a use for it on retail feeds, but I keep it in case.
