Moonlytics.io
Engineering

Building historical crypto timeseries

Storing observations is easy. Storing them so a result computed next year is still the same result takes three decisions, and one trap that catches almost every backtest.

Our observation store currently holds 6.7 million rows, growing by 24,000 a day since November 2025. Nothing about that is large. What makes it useful is three design decisions and one thing we refuse to do.

Decision 1: append-only, no exceptions

A row is written for an asset and an hour, and it is never updated. If a provider revises a figure afterwards, the revision arrives as the next hour's observation. The original stays as it was.

This costs you: the newest data is not always the most accurate, and you cannot fix a bad row. What it buys is the only property that makes historical analysis meaningful — a query against last December returns what was observable last December, not a retrospectively improved version.

Silent restatement is how backtests end up describing a past that never existed. If you have ever seen a strategy work beautifully in backtest and fail in production, in-place correction is one of the more common reasons.

Decision 2: snapshot alignment over write time

Every observation in a given hour carries the top of that hour as its timestamp, regardless of when the job wrote it. A snapshot takes time to write; if each row carried its write instant, a cross-sectional query at a point in time would span a minute of drift, and the drift would correlate with position in the provider's list.

Aligned timestamps also give you the thing that makes this data worth having: market and social values for an asset share a timestamp exactly, so a lead-lag question is a straight join rather than an interpolation.

Decision 3: separate cadences, separate tables

Hourly observations use observed_at; daily series use as_of. Different names on purpose — a join across them is visibly a join across clocks, and nobody writes it by accident. Why forward-filling across them ruins the dataset.

The refusal: no gap filling

Gaps happen. A provider outage, an aborted run, or — most often — an asset leaving the ranked universe and coming back.

a real gap at the coverage boundary
-- What the data actually looks like at the boundary
asset_id | observed_at          | price
---------+----------------------+--------
    4821 | 2026-08-29 14:00:00Z | 0.0412
    4821 | 2026-08-29 15:00:00Z | 0.0447
    4821 | 2026-08-30 09:00:00Z | 0.0391   <- 18 hours later
    4821 | 2026-08-30 10:00:00Z | 0.0388

-- The asset dropped out of the ranked set overnight and
-- came back. Nothing is broken. Do not fill this.

It is tempting to interpolate. Charts look better. Joins get simpler. Every consumer stops asking about holes.

Do not. An interpolated row is indistinguishable from an observed one once it is in the table, and it is perfectly smooth by construction, which biases every volatility, correlation and momentum measure computed over it. If a consumer wants a filled series, they can fill it — at query time, knowingly, with the method their analysis actually tolerates.

The trap: survivorship in a rolling universe

This is the one that catches good analysts, because the query that causes it looks like data hygiene.

the query that quietly selects on outcome
-- Looks reasonable. Is a performance filter.
SELECT asset_id
FROM observation
GROUP BY asset_id
HAVING count(*) = (SELECT count(DISTINCT observed_at) FROM observation);

-- Assets with complete history are assets that never left
-- the ranked universe. You have selected on the outcome.

Our snapshot covers the top 1,000 assets by a combined market-and-social rank. Membership rotates. Filtering to assets with complete history is therefore filtering to assets that stayed ranked for the whole period — which is a performance and attention filter wearing the costume of a completeness filter.

The registry currently holds 7,461 assets against 1,000 slots per snapshot. That ratio is the size of the rotation, and it is the size of the bias.

What to do instead:

  • Define the universe as of a start date, then follow it forward including the assets that dropped out.
  • Treat exit as an event, not as missing data. It carries information.
  • Report coverage per asset alongside any result, so a reader can see which conclusions rest on continuous series.

Storage, briefly

At 24,000 rows a day, a plain relational table with an index on (asset, timestamp) is entirely adequate and will be for years. A specialised timeseries engine would add operational surface without solving a problem we have.

The volume most people think they have is not the volume they have. Measure before choosing infrastructure that assumes otherwise.

Publishing the caveats is part of the design

Every property above is documented on the historical data page, including the survivorship trap, in the section a researcher reads before designing a study. A dataset whose biases are documented is usable. One whose biases are discovered later is worse than none.

Figures measured against the production store on 2026-08-31.

Related