Replacing the HDB: ClickHouse for historical ticker data
In financial services, market data systems typically split into two tiers: real-time and historical.
The real-time tier handles the live feed and the current trading day, serving queries that need answers in microseconds. And then, there's the historical tier, a durable archive of every prior day, that serves queries for research, backtesting, transaction-cost analysis, and regulatory reconstruction.
The historical tier is also the lowest-risk place to try a new database - it doesn't touch the live trading path, doesn't need microsecond latency, and the workload is well-understood (which makes it easy to test).
In this post, we load real tick data from Binance's public archive and show that ClickHouse stores it cheaply, queries it quickly, and handles the operations that matter to a trading desk: VWAP, OHLC candles, and as-of joins.
Why ClickHouse for historical ticker data?
Before the worked example, a brief word on why ClickHouse is well-suited to this workload. It comes down to two things: how it stores data, and how you query it.
Cost-efficiency
Unlike row-oriented databases, which store each record separately, ClickHouse stores data as columns, with values stored together on disk. For analytical workloads, this has a significant consequence for storage efficiency.
Storing similar data next to each other allows us to apply compression techniques to reduce the space they occupy. For example:
- Dictionary encoding saves space when a column has low cardinality. Instead of storing the same string thousands of times, we store a dictionary of distinct values and an integer reference per row.
- Delta encoding works well for timestamps or any column where adjacent values are monotonically increasing and differ by small amounts. Rather than storing the full value each time, we store only the difference from the previous value.
- Run-length encoding replaces a sequence of identical adjacent values with a single value and a count.
Tick data tends to compress particularly well because it has exactly these properties: timestamps that increment in small regular steps, prices that drift slowly, symbols that repeat millions of times, and trade IDs that increase monotonically.
Beyond these specialized encodings, storing similar values together creates highly repetitive byte patterns that general-purpose compression algorithms such as ZSTD and LZ4 can exploit. These are applied to every column to reduce the amount of data stored.
Query accessibility
The second reason to consider ClickHouse for historical tick data is its ease of querying. ClickHouse uses SQL - the common language across data engineering, analytics, and business intelligence. According to the 2025 Stack Overflow Developer survey, 58.6% of developers use it.
There's no specialist language to learn, no small talent pool to hire from, and no bottleneck of having to route every question through the people who can write the queries. We'll see this in action in the worked examples below.
A worked example: Using ClickHouse for historical ticker data
Now we're going to have a look at a worked example showing how to use ClickHouse to query historical ticker data. We'll use Binance's public market-data archive, a free, openly redistributable dataset containing billions of records.
One month of data on clickhouse-local
We'll start with an example that you can run on your own computer - one month of USD-M perpetual futures (from January 2024) across five of the most active symbols - BTC, ETH, SOL, BNB, and XRP.
That gives us hundreds of millions of trades and nearly 2 billion quotes. You can use this script to download the data, which consists of 155 ZIP files totaling 21 GB.
Trades
Let's start with trades. A trade is a single executed transaction - the moment a buyer and seller agree on a price and quantity changed hands.
Each trade has a symbol, a timestamp (down to the millisecond), and the price and quantity that changed hands. We also have the notional value of the trade, quote_qty (price × quantity, in USDT), a unique, monotonically increasing trade ID, and a flag, is_buyer_maker, indicating which side was the aggressor.
CREATE TABLE trades
(
symbol LowCardinality(String),
ts DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
price Decimal(12, 2) CODEC(ZSTD(1)),
qty Decimal(16, 6) CODEC(ZSTD(1)),
quote_qty Decimal(20, 6) CODEC(ZSTD(1)),
trade_id UInt64 CODEC(DoubleDelta, ZSTD(1)),
is_buyer_maker Boolean CODEC(ZSTD(1))
)
ENGINE = MergeTree
ORDER BY (symbol, ts);Next, let's ingest the trades into our table:
INSERT INTO trades SELECT
splitByChar('-', _file)[1] AS symbol,
fromUnixTimestamp64Milli(time),
price, qty, quote_qty, id, is_buyer_maker
FROM file('data/*-trades-*.zip :: *.csv', CSVWithNames);You'll notice that we can query the archive files directly, a feature that's been in ClickHouse since version 23.8. It takes a little under 90 seconds to ingest this data:
373502424 rows in set. Elapsed: 81.706 sec. Processed 373.50 million rows, 19.38 GB (4.57 million rows/s., 237.17 MB/s.)
Peak memory usage: 226.02 MiB.Now that we've got the trades data loaded, let's compute the Volume-Weighted Average Price (VWAP) across all symbols for a single day.
VWAP is the average price at which an asset traded, weighted by the volume traded at each price level. It's one of the most-used benchmarks on a trading desk: funds measure execution quality against it, algorithms try to track it, and it's the standard answer to "what was the fair price for this asset today?"
It's computed by summing the notional value of every trade (quote_qty, which is price × qty) and dividing it by the total quantity traded. In the following query, we order symbols by notional volume traded (the amount of money that changed hands) rather than the number of units:
WITH
sum(quote_qty) AS raw_volume_usdt,
sum(qty) AS raw_volume_base
SELECT
symbol,
round(sum(quote_qty) / sum(qty), 2) AS vwap,
formatReadableQuantity(raw_volume_base) AS volume_base,
formatReadableQuantity(raw_volume_usdt) AS volume_usdt,
bar(raw_volume_usdt, 0, 13440000000, 20) AS chart
FROM trades
WHERE toDate(ts) = '2024-01-31'
GROUP BY symbol
ORDER BY raw_volume_usdt DESC;┌─symbol──┬─────vwap─┬─volume_base─────┬─volume_usdt────┬─chart────────────────┐
│ BTCUSDT │ 42940.53 │ 313.07 thousand │ 13.44 billion │ ████████████████████ │
│ ETHUSDT │ 2314.4 │ 2.61 million │ 6.04 billion │ ████████▉ │
│ SOLUSDT │ 99.82 │ 36.56 million │ 3.65 billion │ █████▍ │
│ XRPUSDT │ 0.5 │ 1.47 billion │ 740.63 million │ █ │
│ BNBUSDT │ 303.86 │ 883.20 thousand │ 268.37 million │ ▍ │
└─────────┴──────────┴─────────────────┴────────────────┴──────────────────────┘
5 rows in set. Elapsed: 0.126 sec. Processed 12.36 million rows, 308.92 MB (97.74 million rows/s., 2.44 GB/s.)
Peak memory usage: 621.91 KiB.In terms of volume, XRP has the most activity, but each one is worth only $0.50, putting it fourth in terms of money traded. BTC's $13.4 billion dwarfs the rest despite having the fewest units.
Next, let's write a query for an OHLC (Open, High, Low, Close) candle for a given symbol on a given day. The following query computes this for Bitcoin for each hour on the 16th January 2024:
SELECT toStartOfInterval(ts, INTERVAL 1 HOUR)::Time AS bucket,
argMin(price, ts) AS open,
max(price) AS high,
min(price) AS low,
argMax(price, ts) AS close,
round(sum(qty), 2) AS volume
FROM trades
WHERE symbol = 'BTCUSDT' AND toDate(ts) = '2024-01-16'
GROUP BY bucket
ORDER BY bucket;┌───bucket─┬────open─┬────high─┬─────low─┬───close─┬───volume─┐
│ 00:00:00 │ 42515 │ 42679 │ 42466.1 │ 42604.7 │ 4807.99 │
│ 01:00:00 │ 42604.6 │ 42733.7 │ 42556.6 │ 42591.7 │ 4728.84 │
│ 02:00:00 │ 42591.7 │ 42732.8 │ 42552 │ 42702.1 │ 5519.02 │
│ 03:00:00 │ 42702.1 │ 42942 │ 42695.7 │ 42921.2 │ 7032.13 │
│ 04:00:00 │ 42919.3 │ 42945 │ 42759.9 │ 42805.2 │ 5516.79 │
│ 05:00:00 │ 42805.2 │ 42932.5 │ 42759 │ 42765.3 │ 4148.77 │
│ 06:00:00 │ 42765.4 │ 42779.1 │ 42648.3 │ 42744.4 │ 5399.94 │
│ 07:00:00 │ 42744.5 │ 42788.1 │ 42600.5 │ 42788 │ 4965.14 │
│ 08:00:00 │ 42788.5 │ 43029.7 │ 42718.8 │ 43021.5 │ 8975.58 │
│ 09:00:00 │ 43021.5 │ 43164.9 │ 42899.6 │ 42937 │ 12361.57 │
│ 10:00:00 │ 42937.1 │ 42969.1 │ 42850.1 │ 42870 │ 4996.4 │
│ 11:00:00 │ 42870 │ 42944 │ 42753.6 │ 42895.3 │ 5799.53 │
│ 12:00:00 │ 42894.6 │ 43170 │ 42854.7 │ 43160.1 │ 10657.02 │
│ 13:00:00 │ 43160.1 │ 43181.7 │ 42705.6 │ 42805.7 │ 13452.14 │
│ 14:00:00 │ 42805.6 │ 42954.9 │ 42050 │ 42700.1 │ 55063.01 │
│ 15:00:00 │ 42700.1 │ 43333 │ 42545.6 │ 43251.7 │ 42560.65 │
│ 16:00:00 │ 43250.6 │ 43450 │ 43003.2 │ 43249.8 │ 24381.08 │
│ 17:00:00 │ 43249.8 │ 43249.8 │ 43027.6 │ 43046.6 │ 8896.75 │
│ 18:00:00 │ 43051.4 │ 43159.9 │ 42825.5 │ 43008.8 │ 10532.47 │
│ 19:00:00 │ 43008.8 │ 43182.3 │ 42940.8 │ 43164.8 │ 6539.24 │
│ 20:00:00 │ 43164.8 │ 43376.2 │ 43061.7 │ 43195.9 │ 13191 │
│ 21:00:00 │ 43195.9 │ 43589 │ 43181.6 │ 43427.5 │ 14845.85 │
│ 22:00:00 │ 43427.4 │ 43499.2 │ 43200 │ 43208.6 │ 6772.76 │
│ 23:00:00 │ 43208.6 │ 43285 │ 43105.2 │ 43133 │ 6063.55 │
└──────────┴─────────┴─────────┴─────────┴─────────┴──────────┘
24 rows in set. Elapsed: 0.041 sec. Processed 3.62 million rows, 90.24 MB (87.39 million rows/s., 2.18 GB/s.)
Peak memory usage: 5.28 MiB.The price drifted up throughout the day, opening at $42,515 and closing at $43,133, a 1.5% intraday gain.
The 14:00 hour is the most interesting. During that hour, the price briefly crashed to $42,050 ( about $750 below where it started) before recovering. Trading activity was also unusually heavy: 55,063 BTC changed hands, more than 5x the typical hourly volume.
A wave of sellers drove the price down sharply, but enough buyers stepped in to push it back up within the same hour. What we can't see is when, within that hour, the selling was most intense.
That's what we'll look at next. The following query finds the one-minute interval where the price was at its lowest point between 14:00 and 15:00
SELECT
toStartOfMinute(ts) AS minute, count() AS trades,
round(min(price), 2) AS low, round(max(price), 2) AS high,
round(sum(qty), 3) AS volume
FROM trades
WHERE symbol = 'BTCUSDT'
AND ts >= '2024-01-16 14:00:00'
AND ts < '2024-01-16 15:00:00'
GROUP BY minute
ORDER BY low ASC
LIMIT 10;┌──────────────minute─┬─trades─┬─────low─┬────high─┬───volume─┐
│ 2024-01-16 14:44:00 │ 17191 │ 42050 │ 42167 │ 2494.94 │
│ 2024-01-16 14:45:00 │ 16390 │ 42090.2 │ 42212.8 │ 2053.689 │
│ 2024-01-16 14:43:00 │ 15723 │ 42115 │ 42244.4 │ 1914.875 │
│ 2024-01-16 14:46:00 │ 16682 │ 42143.2 │ 42275.2 │ 2163.598 │
│ 2024-01-16 14:42:00 │ 18611 │ 42152 │ 42273.4 │ 2241.841 │
│ 2024-01-16 14:47:00 │ 9863 │ 42175 │ 42278.6 │ 1106.466 │
│ 2024-01-16 14:48:00 │ 6575 │ 42187.3 │ 42265.3 │ 793.072 │
│ 2024-01-16 14:41:00 │ 10948 │ 42243.2 │ 42341.2 │ 1268.806 │
│ 2024-01-16 14:40:00 │ 15944 │ 42247.5 │ 42389.5 │ 1858.954 │
│ 2024-01-16 14:49:00 │ 9880 │ 42256.9 │ 42321.9 │ 1225.524 │
└─────────────────────┴────────┴─────────┴─────────┴──────────┘
10 rows in set. Elapsed: 0.017 sec. Processed 524.29 thousand rows, 12.69 MB (30.45 million rows/s., 736.98 MB/s.)
Peak memory usage: 62.55 KiB.The sell-off wasn't over in a single moment - the five minutes from 14:42 to 14:46 all show heavy trading activity and big price swings, as selling intensified and then gradually eased. But 14:44 looks like the worst of it. The price hit its lowest point of the entire hour ($42,050), and 2,494 BTC changed hands in sixty seconds.
But knowing when it happened only gets us so far. The hourly view can't tell us how it happened: was the price dropping because there were barely any buyers around, meaning each sale pushed it down further? Or were there genuinely buyers and sellers active on both sides, holding the price steady even under pressure?
To answer that, we need to look at each trade individually, alongside the market price at the time it was executed.
Quotes
While the trades table recorded what actually happened, the quotes table records what could have happened at each instant. Each record captures the best bid (the highest price a buyer was willing to pay) and the best ask (the lowest price a seller was willing to accept) at every moment the order book changed.
Our table will also include bid_qty, the amount someone wants to buy at the highest price, and ask_qty, the amount someone wants to sell at the lowest price. symbol and ts are self-explanatory! update_id is a monotonically increasing integer.
Every time the best bid or best ask changes (e.g., when an order is placed, canceled, or filled), Binance adds a new record to the quotes table.
The schema for the quotes table is shown below:
CREATE TABLE quotes
(
symbol LowCardinality(String),
ts DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
bid_price Decimal(12, 4) CODEC(ZSTD(1)),
bid_qty Decimal(16, 3) CODEC(ZSTD(1)),
ask_price Decimal(12, 4) CODEC(ZSTD(1)),
ask_qty Decimal(16, 3) CODEC(ZSTD(1)),
update_id UInt64 CODEC(DoubleDelta, ZSTD(1))
)
ENGINE = MergeTree
ORDER BY (symbol, ts);And we can ingest the data using the following query:
INSERT INTO quotes SELECT
splitByChar('-', _file)[1] AS symbol,
fromUnixTimestamp64Milli(transaction_time),
best_bid_price, best_bid_qty, best_ask_price, best_ask_qty, update_id
FROM file('data/*-bookTicker-*.zip :: *.csv', CSVWithNames);To ingest just under 2 billion records takes around 8 minutes:
1997061823 rows in set. Elapsed: 509.946 sec. Processed 2.00 billion rows, 185.91 GB (3.92 million rows/s., 364.57 MB/s.)
Peak memory usage: 273.32 MiB.Now that we've loaded the quotes, we can resume investigating the trade activity at 14:44 on 16th January 2024.
To analyze this activity, we'll use an ASOF JOIN - a join that matches each trade to the most recent quote available at the moment it happened. This tells us not just what price a trade executed at, but what price was being advertised in the market at that exact instant.
The query breaks that one-minute window into per-second snapshots, showing for each second: how many trades happened, the price range, total volume, and average slippage.
Slippage measures how far the actual trade price strayed from the advertised price
Fetched July 15, 2026
