# ClickHouse market data: tables, Kafka and measured tests

URL: https://fincodelabs.com/en/blog/clickhouse-market-data · Publisher: Fincode (Fincode Yazılım ve Teknoloji A.Ş.), Istanbul, Turkey · Category: Fintech development · Published: 2026-11-06 · Updated: 2026-11-06

ClickHouse market data tables for trades, quotes, depth and candles, when Kafka earns its cost, and sizes and query times measured on 30 million trades.

**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.

**Setup and results, 6 November 2026**

| Item | Value |
| --- | --- |
| Server | ClickHouse 26.8.36.3, one node in Docker, default settings |
| Host | Docker Desktop on WSL2, AMD Ryzen 9 5900HX (8 cores), 7.5 GiB for containers, NVMe SSD |
| Data | 29,999,852 synthetic trades, 50 symbols, one UTC day; busiest symbol 5,584,298 rows |
| Load | 218 MB Parquet file over HTTP, candle view attached: 97 seconds with other jobs on the host |
| Merge to one part | 18 seconds |
| On disk | 218.2 MB from 1,290 MB uncompressed (ratio 5.92), 7.27 bytes a row |

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.

> **Hypothetical budget.** 30 million trades a day at 7.27 bytes a row is about 80 GB a year, before quotes, depth, replicas and backups.

## 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](https://github.com/binance/binance-public-data/blob/master/README.md) says Spot timestamps from 1 January 2025 are in microseconds, so convert older millisecond files at load (see the [Binance historical data guide](https://fincodelabs.com/en/blog/binance-historical-data-api)).

**Four tables, one pattern (all created; trades and candles loaded in full)**

| Table | Sort key | Notes |
| --- | --- | --- |
| `trades` | exchange, symbol, ts_exchange, trade_id | The ID separates fills sharing a microsecond |
| `bbo` | exchange, symbol, ts_exchange, update_id | One row per top-of-book change |
| `depth_snapshots` | exchange, symbol, ts_receive, last_update_id | Price and quantity arrays per side, from books built as in the [WebSocket order book guide](https://fincodelabs.com/en/blog/websocket-order-book) |
| `candles_1m` | exchange, symbol, minute | AggregatingMergeTree fed by a materialized view |

## Sort key, partitions and LowCardinality

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

**The same rows under two sort keys: median time and rows read**

| Query | Symbol first | Time first |
| --- | --- | --- |
| One symbol, one hour | 9.6 ms; 49,152 rows | 43.3 ms; 1,417,216 rows |
| All symbols, one hour | 54.0 ms; 2,138,112 rows | 34.1 ms; 1,417,216 rows |
| Disk | 218.2 MB | 269.7 MB, 24% more |

ClickHouse's [partitioning page](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/custom-partitioning-key) says most tables need no partition key and most others nothing finer than a month. Its [LowCardinality page](https://clickhouse.com/docs/reference/data-types/lowcardinality) favors the type below 10,000 distinct values; each symbol column here cost 0.005 bytes a row.

**Bytes a row by codec, same data, run 5 November 2026**

| Column | LZ4 (default) | ZSTD(1) | Delta, ZSTD(1) | DoubleDelta, ZSTD(1) |
| --- | ---: | --- | ---: | ---: |
| `trade_id` | 4.005 | Not run | 0.388 | 0.009 |
| `ts_exchange` | 2.138 | 0.947 | 1.181 | 1.858 |

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';
```

**Decimal64(8) against Float64, same 30 million rows**

| Check | Decimal64(8) | Float64 |
| --- | --- | --- |
| `price` on disk | 0.431 bytes a row (Delta, ZSTD) | 0.695 (Gorilla, ZSTD) |
| `qty` on disk | 2.031 (ZSTD) | 2.597 (ZSTD) |
| Day's notional, query run 7 times | 1 value: 2,612,603,165.0293469 | 2 different values |

The float total moved because threads add in a different order; reconciliation needs one answer. The [Decimal page](https://clickhouse.com/docs/reference/data-types/decimal) 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;
```

**Candle view measurements**

| Check | Result |
| --- | --- |
| One symbol, a day of candles | 13.0 ms from the view; 135.2 ms from trades |
| View against a recalculation | Identical, 1,440 of 1,440 candles; 72,000 stored in 3.6 MB |
| Close chosen by time alone | Differed in 10,197 of 72,000 minutes |

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](https://clickhouse.com/docs/concepts/features/materialized-views/incremental-materialized-view) 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.

**After re-sending one hour, before any merge**

| Query | Plain | FINAL |
| --- | --- | --- |
| `count()`, whole table | 3.2 ms from part metadata; 31,411,121 rows | 1,216 ms; 29,999,852 rows |
| Volume by symbol, full day | 437 ms | 1,640 ms |
| One symbol, the re-sent hour | 9.7 ms | 20.8 ms |

The [engine reference](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/replacingmergetree) says it does not guarantee the absence of duplicates. Its [ReplacingMergeTree guide](https://clickhouse.com/docs/concepts/features/operations/update/replacing-merge-tree) 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.

**100,000 trades sent twice by a second collector**

| Design | Rows after FINAL | What happened |
| --- | --- | --- |
| Key (exchange, symbol, ts_exchange, trade_id) | 100,000 | Correct |
| Key without `trade_id`, sent once | 26,582 | Fills sharing a microsecond were deleted on insert |
| Key on `ts_receive` | 200,000 | The copy arrived 5 ms later, so nothing matched |
| Partition by receive date | 100,000, but 200,000 without FINAL even after OPTIMIZE | Copies in different partitions are never merged |

Merges happen within a partition, the guide notes, so a row's partition key must never change. Insert in batches: the [insert guide](https://clickhouse.com/docs/concepts/best-practices/selecting-an-insert-strategy) 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](https://kafka.apache.org/44/getting-started/introduction/) 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.

**Messages per partition, 6 partitions, 50 symbols; the busiest symbol alone sent 311,981**

| Topic | Smallest partition | Largest partition |
| --- | ---: | --- |
| Keyed by symbol | 97,923 | 593,126 (35% of messages) |
| No key | 251,249 | 325,312 |

- **Mixed clients.** Kafka's Java console producer put 40 of 50 keys in different partitions from ClickHouse. The [librdkafka configuration page](https://github.com/confluentinc/librdkafka/blob/master/CONFIGURATION.md) 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](https://kafka.apache.org/44/design/design/) 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](https://clickhouse.com/docs/reference/engines/table-engines/integrations/kafka) 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.

**Official pages read 6 November 2026 (alphabetical)**

| System | License | Fit, as documented |
| --- | --- | --- |
| ClickHouse | [Apache 2.0](https://github.com/ClickHouse/ClickHouse/blob/master/LICENSE) | Columnar SQL server; the tables above |
| kdb+ | All use needs a license; KDB-X Community Edition is free, with usage restrictions ([KX licensing](https://code.kx.com/q/learn/licensing/)) | The q language, in memory, per [KX](https://kx.com/products/kdb/) |
| QuestDB | [Apache 2.0](https://github.com/questdb/questdb/blob/master/LICENSE.txt) | SQL on a [designated timestamp](https://questdb.com/docs/concepts/designated-timestamp/); out-of-order rows cost write amplification; [deduplication](https://questdb.com/docs/concepts/deduplication/) by UPSERT KEYS |
| TimescaleDB | Apache 2 Edition, or Community Edition under the Tiger Data License, which may not be sold as a service ([editions](https://www.tigerdata.com/docs/get-started/choose-your-path/timescaledb-editions)) | A PostgreSQL extension; columnstore needs Community Edition |

A stored copy does not change your rights in the data. Binance's [Vision Dataset Terms](https://github.com/binance/binance-public-data/blob/master/TERMS_AND_CONDITIONS.md) (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](https://www.okx.com/help/okx-api-agreement) (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](https://fincodelabs.com/en/blog/crypto-price-api) compares both. For stocks see the [stock market data API guide](https://fincodelabs.com/en/blog/stock-market-data-api) and the [Borsa Istanbul API guide](https://fincodelabs.com/en/blog/borsa-istanbul-api-market-data). For integration work in general, see Fincode's [market data pipelines page](https://fincodelabs.com/en/services/market-data-pipelines); 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](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/replacingmergetree)
- [ClickHouse: Working with the ReplacingMergeTree engine (FINAL performance, partitions)](https://clickhouse.com/docs/concepts/features/operations/update/replacing-merge-tree)
- [ClickHouse: Incremental materialized view](https://clickhouse.com/docs/concepts/features/materialized-views/incremental-materialized-view)
- [ClickHouse: Decimal data types (scale rules, ranges, overflow checks)](https://clickhouse.com/docs/reference/data-types/decimal)
- [ClickHouse: Custom partitioning key](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/custom-partitioning-key)
- [ClickHouse: Selecting an insert strategy](https://clickhouse.com/docs/concepts/best-practices/selecting-an-insert-strategy)
- [ClickHouse: Kafka table engine](https://clickhouse.com/docs/reference/engines/table-engines/integrations/kafka)
- [Apache Kafka 4.4 documentation: Introduction (partitions and ordering)](https://kafka.apache.org/44/getting-started/introduction/)
- [Apache Kafka 4.4 documentation: Design (log compaction)](https://kafka.apache.org/44/design/design/)
- [librdkafka: configuration properties (partitioner)](https://github.com/confluentinc/librdkafka/blob/master/CONFIGURATION.md)
- [Binance public data: README (Spot timestamps in microseconds from 1 January 2025)](https://github.com/binance/binance-public-data/blob/master/README.md)
- [Binance Vision Dataset Terms, last updated 26 August 2026](https://github.com/binance/binance-public-data/blob/master/TERMS_AND_CONDITIONS.md)
- [Tiger Data docs: Compare TimescaleDB editions](https://www.tigerdata.com/docs/get-started/choose-your-path/timescaledb-editions)
- [KX: Licensing kdb+](https://code.kx.com/q/learn/licensing/)
- [OKX API Agreement, sections 9.3 and 9.4](https://www.okx.com/help/okx-api-agreement)

> 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](https://fincodelabs.com/en/editorial-policy).

---

Published by Fincode (Fincode Yazılım ve Teknoloji A.Ş.), a software company in Istanbul, Turkey: https://fincodelabs.com/en/blog/clickhouse-market-data

Cite as: Fincode, "ClickHouse market data: tables, Kafka and measured tests", updated November 6, 2026, https://fincodelabs.com/en/blog/clickhouse-market-data