ClickHouse market data: tables, Kafka and measured tests

Short answer: ClickHouse market data tables work best as one table per dataset, sorted by exchange, symbol and exchange time, because most queries start from a symbol and a time range. In a local test on 6 November 2026, 30 million synthetic trades took 218 MB on disk (7.27 bytes a row), and one symbol's hour read in 9.6 ms. Kafka earns its cost when several consumers or replays need the same feed; one bot writing one table does not need it.

Files stop being enough when several programs need the same history or one question scans a whole day. Replication, clusters and live latency were not tested.

Figures use synthetic data shaped like a public trade file, because Binance's dataset terms limit its files to non-commercial uses; real feeds compress differently. This guide gives no trading signals and recommends no vendor. Rules depend on your jurisdiction and each source's terms. For readers in Turkey, Article 99/A of Capital Markets Law No. 6362 treats activity by foreign crypto-asset platforms directed at Turkish residents as unauthorized.

ClickHouse for market data: test setup and results

One local server stored a synthetic day of 30 million trades in 218 MB and answered single-symbol queries in milliseconds.

A seeded generator copied the shape of a public trade file: sequential IDs, microsecond times, fills of one order sharing a timestamp, plus a receive time. Times are medians of 7 warm runs on a shared host; read them as relative.

Tables for trades, quotes, depth and candles

Use one table per dataset with the same leading sort key, and store both the exchange's time and your receive time.

CREATE TABLE trades
(
    exchange     LowCardinality(String),
    symbol       LowCardinality(String),
    trade_id     UInt64               CODEC(Delta, ZSTD(1)),
    ts_exchange  DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
    ts_receive   DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
    price        Decimal64(8)         CODEC(Delta, ZSTD(1)),
    qty          Decimal64(8)         CODEC(ZSTD(1)),
    buyer_maker  Bool                 CODEC(ZSTD(1))
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(ts_exchange)
ORDER BY (exchange, symbol, ts_exchange, trade_id);

• Two clocks. ts_exchange orders events; ts_receive records when you could have known. It cost 3.08 of the 7.27 bytes, and 2.42 as an Int32 microsecond offset.
• One unit. Binance's public data README (opens in a new tab) says Spot timestamps from 1 January 2025 are in microseconds, so convert older millisecond files at load (see the Binance historical data guide).

Sort key, partitions and LowCardinality

Put the symbol before time unless your main query scans every symbol over short windows.

ClickHouse's partitioning page (opens in a new tab) says most tables need no partition key and most others nothing finer than a month. Its LowCardinality page (opens in a new tab) favors the type below 10,000 distinct values; each symbol column here cost 0.005 bytes a row.

No codec won everywhere: DoubleDelta made sequential IDs almost free, while plain ZSTD won on exchange time because fills repeat a timestamp. Those two changes would save about 0.61 bytes a row; test on your own data first.

Decimal or Float64 for market data

Store Decimal64(8) and widen to Decimal128 before multiplying, because price times quantity on two Decimal64(8) columns overflows.

The product type is Decimal(18, 16): 922 × 1 returned a value and 923 × 1 failed (the 64-bit limit at 16 decimals), so sum(price * qty) on the trade columns stopped with DECIMAL_OVERFLOW. Widening one side works, and nullIf turns an empty window's division-by-zero error into NULL:

SELECT sum(toDecimal128(price, 8) * qty) / nullIf(sum(qty), 0) AS vwap
FROM trades
WHERE exchange = 'venue_a' AND symbol = 'SYM07USDT'
  AND ts_exchange >= '2026-01-01 12:00:00' AND ts_exchange < '2026-01-01 13:00:00';

The float total moved because threads add in a different order; reconciliation needs one answer. The Decimal page (opens in a new tab) sets Decimal64(S) limits at ±10^18-S, so Decimal64(8) stays below 10 billion; very large token quantities need a smaller scale or Decimal128. Decimal128 has no overflow check, the page adds: here an out-of-range product returned a wrong negative number, no error.

Candles from a materialized view, and the replay trap

A materialized view makes one-minute candles cheap to read, but it counts every insert, so a replay inflates volume even after trades drops the duplicates.

CREATE TABLE candles_1m
(
    exchange LowCardinality(String),
    symbol   LowCardinality(String),
    minute   DateTime('UTC'),
    open     AggregateFunction(argMin, Decimal64(8), Tuple(DateTime64(6, 'UTC'), UInt64)),
    high     SimpleAggregateFunction(max, Decimal64(8)),
    low      SimpleAggregateFunction(min, Decimal64(8)),
    close    AggregateFunction(argMax, Decimal64(8), Tuple(DateTime64(6, 'UTC'), UInt64)),
    volume   SimpleAggregateFunction(sum, Decimal128(8)),
    trades   SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (exchange, symbol, minute);

CREATE MATERIALIZED VIEW candles_1m_mv TO candles_1m AS
SELECT exchange, symbol,
       toStartOfMinute(ts_exchange)                AS minute,
       argMinState(price, (ts_exchange, trade_id)) AS open,
       max(price)                                  AS high,
       min(price)                                  AS low,
       argMaxState(price, (ts_exchange, trade_id)) AS close,
       sum(qty)                                    AS volume,
       count()                                     AS trades
FROM trades
GROUP BY exchange, symbol, minute;

Read it with argMinMerge, argMaxMerge, max, min and sum by minute. The trap: re-sending one hour (1,411,269 rows), as a retry would, left trades FINAL correct at 29,999,852, but that hour's candles counted 2,822,538 trades and double the volume, and stayed doubled after OPTIMIZE cleaned trades. ClickHouse's materialized view page (opens in a new tab) calls a view a trigger on blocks as they are inserted, so it never sees deduplication. What worked: pause ingest, ALTER TABLE candles_1m DROP PARTITION 202601, rebuild from trades FINAL. That took 5.2 seconds and left 0 wrong candles.

ReplacingMergeTree and FINAL: duplicates are eventual

ReplacingMergeTree removes duplicates only when parts merge, at a time you cannot plan, so use FINAL where counts must be exact.

The engine reference (opens in a new tab) says it does not guarantee the absence of duplicates. Its ReplacingMergeTree guide (opens in a new tab) calls the FINAL overhead small and most noticeable without key filters; here it was 3.8 times on the full-day volume query and 2.1 times on the key-filtered hour.

Merges happen within a partition, the guide notes, so a row's partition key must never change. Insert in batches: the insert guide (opens in a new tab) recommends at least 1,000 rows, ideally 10,000 to 100,000.

Kafka market data: when it earns its cost

Kafka earns its cost when several consumers need the same feed, when you must replay after a bug, or when collectors must outlive a database outage; one bot writing one table can insert batches directly.

Key messages by symbol: Kafka's introduction (opens in a new tab) says events with the same key go to the same partition and are read in the order written. On 5 November 2026, ClickHouse's Kafka engine wrote 1,680,358 synthetic trades, keyed by symbol, to a 6-partition Apache Kafka 4.4.0 topic. On 6 November each of 50 symbols sat in one partition, with 0 trade IDs out of order.

• Mixed clients. Kafka's Java console producer put 40 of 50 keys in different partitions from ClickHouse. The librdkafka configuration page (opens in a new tab) lists a CRC32 hash (consistent_random) as default and murmur2_random as Java-compatible, so give all producers one partitioner.
• Latest values. Kafka's design page (opens in a new tab) says compaction keeps at least the last value per key: right for latest quotes, wrong for trade history.

CREATE TABLE trades_queue
(
    exchange String, symbol String, trade_id UInt64,
    ts_exchange_us Int64, ts_receive_us Int64,
    price String, qty String, buyer_maker Bool
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'trades_keyed',
         kafka_group_name = 'ch_trades',
         kafka_format = 'JSONEachRow',
         kafka_num_consumers = 6;

CREATE MATERIALIZED VIEW trades_queue_mv TO trades AS
SELECT exchange, symbol, trade_id,
       fromUnixTimestamp64Micro(ts_exchange_us, 'UTC') AS ts_exchange,
       fromUnixTimestamp64Micro(ts_receive_us, 'UTC') AS ts_receive,
       toDecimal64(price, 8) AS price,
       toDecimal64(qty, 8)   AS qty,
       buyer_maker
FROM trades_queue;

Re-run on 6 November with the broker and group renamed, this DDL delivered all 1,680,358 rows, none differing from the source. With bad input, one unparsable price blocked ingestion, nothing committed; two same-microsecond fills lacking trade_id became one; 1.2e-9 loaded as 0. The Kafka engine page (opens in a new tab) says to use one group name everywhere if you do not want duplicates; replays still duplicate rows, hence a ReplacingMergeTree target. It also warns that consumed rows can vanish silently if the host loses its page cache before parts reach disk, and recommends fsync_after_insert and fsync_part_directory on target tables.

Time series database open source options and data rights

The choice turns on license, query language and what you already run; only ClickHouse was tested, so this is not a ranking.

A stored copy does not change your rights in the data. Binance's Vision Dataset Terms (opens in a new tab) (updated 26 August 2026) bind you on download, caching or querying, allow only non-commercial uses such as personal, non-production backtesting, and bar hosting the files or derived feeds. OKX's API Agreement (opens in a new tab) (section 9.4, updated 28 July 2026) allows market data only for your own personal, non-commercial trading and account management, public endpoints included; the crypto price API guide compares both. For stocks see the stock market data API guide and the Borsa Istanbul API guide. For integration work in general, see Fincode's market data pipelines page; naming Binance, OKX, ClickHouse or Kafka here does not mean Fincode integrates them or has built on them.

Frequently asked questions

Is ClickHouse good for market data?

It suits append-only trades, quotes and candles queried by symbol and time.

Do I need Kafka in front of ClickHouse?

Not for one collector writing one table. Kafka pays off with several consumers, replays or collectors outliving a database outage.

Should prices be Decimal or Float64 in ClickHouse?

Decimal64(8) for storage, widened to Decimal128 before multiplying. Float64 used more disk here and gave two different totals over seven identical runs.

Can I load downloaded exchange data into my own database?

Storing it does not change the license. Binance's Vision terms limit the files to non-commercial use and bar hosting them, so check each source's terms first.

Sources

• ClickHouse: ReplacingMergeTree table engine (opens in a new tab)
• ClickHouse: Working with the ReplacingMergeTree engine (FINAL performance, partitions) (opens in a new tab)
• ClickHouse: Incremental materialized view (opens in a new tab)
• ClickHouse: Decimal data types (scale rules, ranges, overflow checks) (opens in a new tab)
• ClickHouse: Custom partitioning key (opens in a new tab)
• ClickHouse: Selecting an insert strategy (opens in a new tab)
• ClickHouse: Kafka table engine (opens in a new tab)
• Apache Kafka 4.4 documentation: Introduction (partitions and ordering) (opens in a new tab)
• Apache Kafka 4.4 documentation: Design (log compaction) (opens in a new tab)
• librdkafka: configuration properties (partitioner) (opens in a new tab)
• Binance public data: README (Spot timestamps in microseconds from 1 January 2025) (opens in a new tab)
• Binance Vision Dataset Terms, last updated 26 August 2026 (opens in a new tab)
• Tiger Data docs: Compare TimescaleDB editions (opens in a new tab)
• KX: Licensing kdb+ (opens in a new tab)
• OKX API Agreement, sections 9.3 and 9.4 (opens in a new tab)

This guide is general information about software and markets, not investment, legal or tax advice. Automated trading can lose money, past or backtested performance does not guarantee future results, and the rules that apply depend on your jurisdiction and your exchange's terms. Sources, checks and corrections: see the editorial policy.