Engineering

Normalizing crypto market data across providers

Six things stand between a provider payload and a row you can join. Five are tedious. The sixth is where good pipelines go wrong.

Assume identity is already solved — you know which asset a payload is about (and if you do not, start there). What remains is making the values themselves comparable.

1. Schema

One provider returns a flat list of assets with social figures inline. Another returns an asset resource with social data behind separate sub-resources. A third returns an envelope with the payload under data and its own metadata alongside.

The fix is dull and important: an explicit per-provider mapping in one place. The failure mode to avoid is mapping scattered across consuming code, where two features end up disagreeing about which provider field means what.

2. Naming

volume_24h, total_volume, volume24h. Three names, and the important question is whether they are three names for one quantity or three quantities.

Usually the latter. Each provider aggregates venue volume with its own inclusion rules and its own wash-trade filtering. Mapping them onto one canonical field is a claim of equivalence, and if the claim is false you have created a column whose meaning depends on which provider happened to answer.

Where two providers' versions are not interchangeable, keep two fields. A wider schema is cheaper than an ambiguous one.

3. Types

numeric types are not decoration

// The same quantity, three ways, from two providers
"market_cap": 1568738541274      // number
"market_cap": "1568738541274"    // string
"circulating_supply": "20077215" // string, and not an integer for every asset

// 1568738541274 fits in a double.
// The next order of magnitude does not survive it intact.
// Parse to decimal at the boundary or accept silent rounding.

Large numbers arrive as strings, as floats, and sometimes as either depending on magnitude. Market capitalisations sit near the edge of exact double representation, and cumulative supply figures go past it.

Coerce to a fixed-precision decimal at the ingest boundary and store it as one. A float column is a decision to lose precision quietly, forever, in a way that only shows up as a reconciliation discrepancy months later.

4. Units and scale

Percentages as 0.7 and as 70. Dominance as a fraction and as a percent. Price in USD and in BTC. Supply in whole units and in base units.

Every canonical field gets one documented unit, converted at the boundary. The specific bug to design against is double conversion: a value normalized on ingest and normalized again in a consumer that did not know. One conversion site, documented, is the whole answer.

5. Time

UTC, local time, epoch seconds, epoch milliseconds — and, worse, timestamps that mean "when this response was generated" rather than "when this value was observed". The second kind is not a timestamp for your purposes at all.

Convert everything to UTC on ingest and keep no local time anywhere. Then align: if you are storing snapshots, every asset in a snapshot gets the same timestamp, not the second at which its row happened to be written. Without that, a cross-sectional query at a point in time silently spans a minute of write drift.

6. Frequency — the one that matters

Market data changes continuously. Repository statistics change daily. Follower counts drift. You now have series on incompatible clocks, and the obvious move is to join them into one wide row per asset per hour.

the query that ruins the dataset

-- The join everyone writes, and the reason not to
SELECT m.asset_id, m.observed_at, m.price,
       e.commit_count_4_weeks          -- daily, sampled once
FROM market_snapshot m
LEFT JOIN LATERAL (
  SELECT * FROM ecosystem_snapshot e
  WHERE e.asset_id = m.asset_id AND e.as_of <= m.observed_at::date
  ORDER BY e.as_of DESC LIMIT 1
) e ON true;

-- Result: 24 rows an hour carrying one daily measurement.
-- 23 of them are observations that were never made.

This is the expensive mistake

Forward-filling a daily value across twenty-four hourly rows produces twenty-three observations that were never made. They are indistinguishable from real ones. Any correlation computed against them is inflated by the fill, because the fill is perfectly autocorrelated by construction. A model trained on it learns the fill.

Store each series at its native cadence, in its own table, with its own time field — observed_at for hourly, as_of for daily. The naming difference is a guard rail: a join between them is visibly a join between different clocks, and whoever writes it has to decide consciously what alignment they want.

Do the alignment at query time, where it is explicit and where the person doing it can be held to it. Never at ingest, where it becomes a permanent property of the stored data.

What normalization must not do

Not fill gaps. Missing stays missing.
Not smooth. Smoothing is analysis, and analysis belongs above storage.
Not compute derived metrics. A ratio in the stored record freezes a formula that will change.
Not clip outliers. Crypto produces real 100x moves; clipping them removes exactly the events people are looking for.

The stored record should be what was observed, in one shape. Everything else is a view.

The test

A normalization layer is working when a consumer can answer, for any value: which asset, at what time, in what unit, from what cadence, and was it observed or inferred. If any of those five is ambiguous, the layer has pushed a problem downstream rather than solved it.