Scale is the second problem
Ten thousand instruments at one-minute bars for ten years is roughly ten billion bars, and each bar is five or six numbers. That is a real engineering problem, and it is not the one that sinks most platforms. The same universe at daily frequency is about twenty-five million rows, which fits comfortably in memory on a laptop, and teams working with it get the answers wrong just as often. The failures that cost real money are semantic: a value stamped with the wrong clock, a price adjusted for a split that had not happened yet, a universe that quietly contains only the instruments that still exist.
So this is an article about both, in that order. Get the semantics right and the storage problem is tractable with ordinary tools. Get the storage right on top of confused semantics and you have built a fast machine for producing history that never happened.
You are probably here because
- A research result stopped reproducing and the code did not change
- Two teams pull the same field from the same vendor and get different numbers
- A query that took nine seconds last year takes four minutes now
- Somebody asked what a value was on a past date and the honest answer was “which version?”
The first, second and fourth are all one defect: a store that keeps the latest value rather than the sequence of values it has believed. The third is layout, and it is further down.
Every observation carries three timestamps
Write them down separately, in every table, forever.
Event time. When the thing happened. The trade printed, the quarter ended, the sensor read. This is the timestamp everyone stores and the only one most schemas have.
Availability time. When you could first have known it. The filing hit the wire, the vendor published the file, the exchange released the reference price. This is the timestamp that decides whether a piece of research is honest, and it is absent from the majority of vendor files we are handed.
Ingestion time. When it landed in your store. Boring, operational, and the only one that lets you reconstruct what a job actually saw when it ran at 06:15 on a Tuesday two years ago.
Event and availability time can be days or quarters apart. A quarterly figure has an event time of the quarter end and an availability time of the filing date six weeks later, and a query written as WHERE period_end <= t is reading six weeks into the future without a single line of it looking wrong. That is the most common serious defect we find, it survives code review easily, and it inflates results in exactly the direction that makes people want to believe them.
Restatements, and why the latest value is not the value
Fundamentals get restated. Vendors correct errors, sometimes silently. Index constituents are revised after the fact. A store that overwrites in place is destroying the only record of what a decision was actually made on.
The fix is bitemporal storage and it is not exotic: keep (entity, field, event_time, asserted_at, value) and never update a row, only append a new assertion. Reads take an as-of parameter. The current value is asserted_at <= now with the latest assertion per event time; the historical view is the same query with a different constant. It costs storage, which is cheap, and it costs one extra predicate in every query, which is a habit rather than a burden.
The alternative people reach for is a nightly snapshot of the whole table. It works, it is simple, and it is enormous — you are storing the unchanged 99% every night to capture the 1% that moved. For a wide reference table that becomes the largest thing in your warehouse within a year, and it still cannot tell you the exact minute a correction arrived.
As-of joins are the primitive, not equality joins
Almost nothing in financial data lines up on the same timestamp. You have a quote at 09:30:00.142 and a trade at 09:30:00.147 and you want the quote in force at the moment of the trade. Equality joins cannot express this and rounding to a common grid throws away the ordering that matters.
The as-of join — for each row on the left, the most recent row on the right at or before its timestamp — is a first-class operation in kdb+, ClickHouse, DuckDB and Polars, and available in pandas. Use it rather than reimplementing it, because the hand-rolled version is usually a merge plus a forward fill, and forward fill across a boundary it should not cross is a leak that produces beautiful results.
Two rules that prevent most of the damage. Decide explicitly whether the join is strictly before or at-or-before, and write which one in the column name if the distinction is load-bearing. And bound the reach: a quote from four hours ago is not the quote in force, it is a missing value wearing the last known value's clothes. Every as-of join should carry a tolerance, and when it is exceeded the answer is null.
| Access pattern | Layout that serves it | What it costs the other pattern |
|---|---|---|
| One instrument, ten years Research on a single name | Partition by instrument, sort by time | A single-day cross-sectional query touches every partition |
| All instruments, one day Daily production run | Partition by date, sort by instrument then time | A ten-year single-name pull opens 2,500 files |
| Both, seriously | Store twice, date-partitioned and instrument-partitioned | Double the storage and a consistency job. Usually correct |
| Intraday tick, one session | Date and symbol-bucket partitions, large row groups | Small-file explosion if buckets are too fine |
| Ad-hoc research, unpredictable | Date partitions plus clustering on instrument | Neither pattern optimal; both acceptable |
Storage layout: two access patterns that want opposite things
This is the fork every platform hits. Research asks for one instrument across ten years. Production asks for every instrument on one day. Those want opposite physical layouts, and there is no clever encoding that makes both fast in one copy.
The answer that keeps working is to store it twice, accept the duplication, and run a reconciliation job that proves the two copies agree. Storage is the cheapest thing in the stack; engineer time spent tuning a single layout to serve both patterns is not, and the tuned compromise tends to be mediocre at both. Where the two copies matter enough to be authoritative, make one the source and derive the other, so a disagreement has a right answer.
Within a partition, the details that move the numbers most: large enough row groups that predicate pushdown has something to skip with, and small enough that a point query does not read fifty megabytes to find one row — a few hundred thousand rows per group is a reasonable starting point. Sort within the file on the column you filter on, because column statistics only help when the data is ordered. Store prices as scaled integers rather than floats where the tick size allows it; generic compressors do much better on delta-encoded integers than on float64, and the difference is often close to half the file. The published Gorilla encoding from 2015, delta-of-delta on timestamps and XOR on floats, is still the reference design for why regular sampling compresses so well.
Where the engineering time goes on a market data platform
Weights sum to 100. Our planning split, not a measurement. Note that the database everyone argues about sits inside the fourth line.
Corporate actions: store the raw series and the factors
A split-adjusted price is not a fact about a day. It is a fact about a day expressed in the units of a later day, and it changes every time a new action occurs. If you store adjusted prices, your history silently rewrites itself, and a chart produced last March cannot be reproduced this March.
Store the raw traded price exactly as it printed, store the action events separately with their ex-dates and ratios, and compute adjustments at read time against an explicit as-of date. It is more work in the access layer and it is the difference between a reproducible history and a moving one. It also makes the distinction between price-only and total-return series explicit, which otherwise turns into two teams comparing numbers that were never meant to match.
The related trap is the instrument master. Tickers are recycled: a symbol that identified one company in 2011 can identify a different one now, and a naive join on ticker across a long history is a join across two unrelated companies. Key everything on an internal instrument id, keep tickers as dated attributes, and make the mapping table a first-class asset with its own tests.
Send us one query and one schema and we will tell you what it is hiding.
Email a table definition and the query your research runs most often to contact@precisionfederal.com. You get back a written note naming the timestamps you are missing, any lookahead the query permits, and the one layout change we would make first. One business day. No charge, no meeting, no deck.
contact@precisionfederal.comCalendars and time zones, which are not the same subject
Store every timestamp in UTC with nanosecond or microsecond precision, and store the exchange separately. Local time is a rendering decision, and a store that keeps local time has thrown away information it cannot recover on the two ambiguous hours a year when clocks go back.
Calendars are the other half and they are worse, because they are data rather than logic. Trading sessions differ by venue and by product, holidays differ by country and move by year, half-days exist, and futures sessions routinely start the evening before the date they are named for. Treat the calendar as a versioned dataset with its own owner and its own tests, not as a helper module somebody wrote once. A resampling job that assumes 390 minutes in every session produces silent nonsense on every early close, and nobody notices until a year-end review.
Missing has at least four meanings and they are not interchangeable
No trade occurred. The instrument was halted. The instrument did not exist yet, or no longer did. The vendor failed to deliver. Encoding all four as a null forces every consumer to guess, and the usual guess is a forward fill, which manufactures a price for an instrument that was not trading. Carry an explicit status alongside the value, and make forward fill something a caller asks for by name.
Survivorship is a schema problem before it is a statistics problem
If your instrument master holds only live instruments, every historical query you run is conditioned on survival, and no amount of care downstream repairs it. The master must contain delisted, merged, expired and renamed instruments with their effective dates, and universe membership must be a dated relation rather than a flag on the instrument row.
The test is easy and worth running today: reconstruct the constituent list of a standard index as of a date five years ago, purely from your own data. If you cannot, or if the list you get is suspiciously similar to today's, your history is optimistic and everything measured on it is too.
Two systems, one contract
The intraday path and the research store are different systems with different guarantees, and pretending otherwise produces a store that is late for trading and lossy for research. Let the live path be optimized for latency and let it be lossy in acceptable, documented ways. Let the research store be complete, immutable and slower.
What must be shared is the contract: identical instrument ids, identical field definitions, identical timestamp semantics, and a daily reconciliation that compares them and raises when they disagree beyond a stated tolerance. Most of the “two teams, different numbers” arguments we get pulled into are this reconciliation never having been built, and the resolution is usually one field defined two ways rather than anything exotic.
What it costs to run
Object storage for a decade of daily and minute data across a broad universe is a few hundred dollars a month before compression and less after — genuinely not the constraint. Tick data is a different order of magnitude and the honest advice is to keep the recent window hot, keep the deep history cold, and make the cost of a full-history scan visible to whoever is about to launch one.
Compute is where the surprises land. A research query that scans ten years of minute bars for a thousand instruments moves real volume, and the cost is per-run rather than per-month. Caching the derived series people actually use, at the grain they use them, tends to cut the bill more than any engine change. So does a query interface that makes the expensive shape awkward to write by accident.
The unglamorous line item is vendor licensing, which frequently exceeds infrastructure by a wide margin and constrains architecture in ways engineering plans ignore — redistribution terms decide whether a derived series can cross a team boundary, and finding that out after the platform is built is an expensive conversation.
The mistakes we are called in to fix
- One timestamp per row, so nobody can distinguish when a fact was true from when it was knowable
- Updating values in place, destroying the record of what a past decision was made on
- Storing adjusted prices, so history rewrites itself with every new corporate action
- Joining on ticker across a long window, silently joining two different companies
- An instrument master with only live instruments, conditioning every historical query on survival
- Unbounded forward fill, manufacturing prices for instruments that were not trading
- A hardcoded session length, quietly wrong on every half-day and holiday
- No reconciliation between the live path and the research store, so the two disagree indefinitely
A ten-week rebuild sequence
Platform sequence
The ordering is deliberate. Everything after step one keys on instrument ids, so getting the master last means rewriting everything built before it. And step six exists because a correct store with an access layer that lets people write an unbounded forward fill is a correct store that produces wrong answers.
Before you call it done
- Event, availability and ingestion time stored separately on every observation
- Append-only assertions; no destructive updates anywhere in the history
- Every read takes an as-of date, and the default is documented
- Raw prices plus action events; adjustment happens at read time
- Internal instrument ids everywhere; tickers are dated attributes
- Delisted and expired instruments present, with effective dates
- Missing carries a status: no trade, halted, not listed, not delivered
- As-of joins are bounded by an explicit tolerance
- Calendars are versioned data with tests, covering half-days
- A daily reconciliation between the live path and the research store
Bottom line
The scale is real and the tooling for it is mature: columnar files, sensible partitions, an engine that does as-of joins, and a second copy when two access patterns genuinely conflict. The part that is not solved by tooling is semantic discipline — three timestamps, append-only assertions, raw prices with separate factors, a master that remembers the dead, and a missing value that says why it is missing. Those decisions are cheap on day one and close to unaffordable in year three, because by then every number anyone has published depends on the ambiguity you are trying to remove.
Frequently asked questions
Usually not at first. Columnar files on object storage with a query engine that supports as-of joins covers daily and minute data for a broad universe comfortably. Specialist engines earn their keep on tick-level intraday work and on low-latency serving, where the access patterns are narrow and the performance difference is large enough to justify a second system to operate.
A store that can answer what you knew, not just what was true. It requires an availability timestamp on every value and an append-only history of assertions, so a query as of a past date returns the values that had actually been published by then, including the version later corrected. Without it, any research that uses restated data is reading the future.
Unadjusted, plus the corporate action events, with adjustment applied at read time against an explicit as-of date. Stored adjusted prices change retroactively every time a new action occurs, which makes yesterday's chart unreproducible today and makes two teams' numbers disagree for reasons nobody can trace.
Append a new assertion rather than updating the row, and record when the revision arrived. Then run a revision report: how often a vendor restates, by field and by lag. That report is worth building early, because it tells you which fields can be trusted at first publication and which need a waiting period before anything downstream consumes them.
Rarely, when both access patterns are genuinely in use. One instrument across ten years and all instruments on one day want opposite physical layouts, and the tuned compromise is mediocre at both. Storing it twice with a reconciliation job costs storage, which is cheap, and buys predictable performance for both, which is not.