At 11:39 UTC on 2 November, someone sold 0.32504 Bitcoin on Binance for 77,882.93 USDT each. It was trade number 6,811,339,926 on that pair, and Binance pushed it to me the instant it happened.
I built a small pipeline that catches trades like that one, for Bitcoin, Ethereum and Solana, and turns them into one-minute price candles in a table you can query with plain SQL. It runs on a laptop with one command. The code is on GitHub.
If you have heard the names Kafka, Spark and Delta Lake and never been quite sure what each one is for, this is a small, real example. Let's follow that trade through it.
What it is built on
- Source: Binance's public trade stream (no account or API key)
- Message log: Apache Kafka 3.9.0, one broker
- Processing: Apache Spark 3.5.3, Structured Streaming
- Storage: Delta Lake 3.2.1 tables
- Querying: DuckDB
- Running it: Docker Compose
What comes out the other end: candles
If you have seen a price chart made of little bars, each bar is a candle. One candle sums up one minute of trading in six numbers: the first price (open), the highest, the lowest, the last price (close), how much was traded (volume) and how many trades there were.
Everything below exists to build that table, candles_1m, correctly and keep it up to date.
Step 1: a WebSocket, the phone line that stays open
Most APIs work like asking a shop assistant a question. You ask, you get one answer, and if you want to know again a second later, you ask again.
A WebSocket is a phone call that stays open. You connect once, and the other side talks whenever it has news. Binance has a public trade stream that speaks every time a trade happens. Our trade arrived as this message:
{"e":"trade","E":1793619565538,"s":"BTCUSDT","t":6811339926,
"p":"77882.93000000","q":"0.32504000","T":1793619565538,"m":true,"M":true}
s is the pair, t the trade id, p the price, q the quantity and T the time of the trade in milliseconds. Remember that t and T are different fields. It comes back later.
A 44-line Python script, the producer, holds the line open. For each message it adds the time it received it and hands it to Kafka, otherwise untouched. If the connection drops, it reconnects and backs off a little longer each time.
Step 2: Kafka, the till roll in the middle
Think of the paper roll in a shop's till. Every sale is printed on it in order. Nothing gets erased, and anyone can unroll it later and read it back.
Kafka is that roll for software. Programs that produce data append messages to the end of a log. Programs that read keep their own bookmark, called an offset, and move through the log at their own speed. Reading a message does not remove it.
That is why it sits between Binance and everything else. The market moves at its own pace, around 75 messages a second across my three pairs, and the producer only has to write each one down. If the next step is slow or broken, messages simply wait on the roll.
I tested this by killing Spark in the middle of a stream and starting it again about five minutes later. It picked up from its bookmark, and no trade was missed or counted twice.
I ran a single Kafka broker in KRaft mode, which means Kafka manages itself without a separate ZooKeeper service.
Step 3: Spark, turning a stream into small batches
Spark is an engine for processing large amounts of data. It is built to split work across many machines, and here it runs on one, using two cores.
Structured Streaming is the part of Spark that reads a stream. Every ten seconds it takes whatever new messages have arrived in Kafka and processes them as one small batch, a bit like a postman who empties the box every ten seconds instead of running each letter over separately. After each batch it writes down how far it got, in a checkpoint. That checkpoint is the bookmark that made the restart above safe.
For every batch, Spark does three jobs:
-
Checks each trade. A message that is not valid JSON or has a missing field, a price of zero or less, or a trade more than ten minutes old when it arrived goes to a
trades_rejectedtable with the reason next to it. Nothing is thrown away silently. - Removes duplicates. Streams deliver the same message twice more often than you would like, for example when a batch is retried after a crash. The trade id is the key, so a second copy is never inserted. It goes to the rejected table marked as a duplicate.
- Rebuilds the candles. For each minute the batch touched, Spark recalculates that minute's candle from all its trades. If a trade turns up late, its minute is simply recalculated.
That last point was a deliberate choice. Spark has a built-in way to group a stream into time windows, which uses a watermark: a rule that says "stop waiting for data older than this." Anything that arrives after the watermark is dropped. I did not want a late trade to vanish, so I recalculate touched minutes instead. It costs a little more work per batch and never loses a row.
Remember t and T? Spark treats field names as case-insensitive by default, so it saw two fields with the same name and stopped. One setting, spark.sql.caseSensitive=true, fixed it.
Step 4: Delta Lake, files with a logbook
The tables live in Delta Lake. A Delta table is a folder of ordinary Parquet files, a compact column-based file format, plus a logbook that records every change.
The logbook is what makes the table safe to update. A write either fully happens or does not happen at all, so a crash halfway through never leaves half a batch behind. You can also look at the table as it was at an earlier version. Without it, the "insert this trade only if it is new" step above would not be safe. I have written about how open these table formats really are when different tools read them.
There is one cost to know about. A batch every ten seconds writes new files every ten seconds, and streaming tables pile up lots of small files. I measured why that hurts in the small file problem. This build does not compact them yet.
The pipeline ends in three tables: trades_clean, trades_rejected and candles_1m.
Step 5: ask it a question
DuckDB is a database that runs inside your program with no server, and it can read Delta tables straight from the folder:
INSTALL delta; LOAD delta;
SELECT symbol, minute_ts, open, high, low, close, volume, trade_count
FROM delta_scan('data/lake/candles_1m')
WHERE symbol = 'BTCUSDT'
ORDER BY minute_ts DESC
LIMIT 5;
To see whether the candles were right, I compared them with Binance's own one-minute candles. Across 372 complete minutes, all six numbers matched.
Run it yourself
You need Docker. From the repo:
docker compose up -d --build
That starts Kafka, the producer and Spark. Give it a few minutes, then run the query above with DuckDB. The README covers the rest.
One trade, four tools, and now each one has a job you can name.

Top comments (0)