Skim Card

One thing to remember: freshness is a schedule you pay for, so pick the slowest tier that meets the decision, and make every tier reach the batch answer.

  • Freshness follows the interval: medians 0.26 s (250 ms flush), 3.8 s (ClickHouse’s 7.5 s default), 31 s (one-minute commits), 31.8 min (hourly).
  • One-minute commits cost files: 61 commits and 288 objects in an hour, metadata three times the data; the hourly batch made 2 commits, 12 objects.
  • Serve the dashboard a published table, not raw streamed lines: the same question made 8 GET requests instead of 308.
  • Compact inside the writing job: Iceberg 1.12’s post-commit rewrite (SinkV2, experimental) cut 47 live files to 15 and 133 GET requests to 40.
  • A 250 ms ClickHouse flush made 5,268 parts an hour; ReplacingMergeTree drops re-sends only when merging, so exact answers need FINAL.
  • Exactly-once sinks publish at checkpoints; a batch gets exactly-once by committing its Kafka offsets with its data.
Use …When …
Hourly batch, publishing a small tableDecisions run on a schedule: replenishment, finance
Streaming into Iceberg every 1–5 minutes, compactedPeople check a screen between tasks; one copy, in the lake
Real-time OLAP reading KafkaMany users need seconds, and you will run a second copy
Paimon or FlussUpsert-heavy streams, or sub-second reads sharing the lake’s storage

Verified against: Apache Flink 2.2.1 (Kafka connector 5.0.0-2.2), Apache Iceberg 1.12.0, ClickHouse 26.8.36.3, Apache Kafka 4.4.0, Apicurio Registry 3.3.3, Apache Polaris 1.8.0, DuckDB 2.0.0, PyIceberg 0.12.0, confluent-kafka 2.16.1 · vendor docs · 30 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill


Story Opening

The store managers liked Part 29’s alerts and asked for the rest: stock levels in their app, “live”. Rohan had the pieces already, Kafka, Flink and the lake, and a plan to match: commit every second, point the app at the table, done.

“Live for whom?” Ingrid asked. “Replenishment runs twice a day. A manager checks the app between customers. Only the empty-shelf alert needs seconds.” Ingrid drew three boxes: hourly, every minute, every second. “Same number in all three. We measure what each one costs before anyone chooses.”


The Quick Map

Backend instinctData engineering realityWhy it matters
Real time is a setting: lower the intervalFreshness is bought with commits and files, always-on compute, or a second copyOne hour: 2 commits and 12 objects for the hourly tier, 61 commits and 288 objects for the minute tier, 5,268 parts in ClickHouse
A table you can write every minute is a real-time tableEach lake commit adds files and metadata; every reader pays for them until compactionThe minute tier’s question made 308 GET requests; the published hourly table, 8
Kafka has the data, so the dashboard is liveSomething turns the topic into an answer: a batch, a commit, a materialized viewClickHouse’s default flush alone made lines up to 8 s old
Faster means more correctEvery tier must reach the batch answer; freshness only says whenAll three matched the raw zone: 13,436 store-SKUs at or below zero

Concepts, with use cases

Of Part 2’s five promises, this part strengthens timeliness: each tier states its freshness and meets it, with the same answer as the batch. It builds on Part 28’s Flink SQL into Iceberg, Part 29’s book stock and Part 13’s ClickHouse.

Freshness and latency tiers

Freshness is the time from an event being produced to it being answerable by the query that needs it. A latency tier is a promise about it, and three cover most needs: hours (a scheduled batch), minutes (a streaming job committing to the lake), seconds (a store that ingests continuously and serves queries itself). Each step down costs commits and files, compute that never stops, or a second copy with its own rules.

What breaks without it. “Real time” gets built for a screen someone reads at 9 a.m., and pays seconds-tier prices for hours-tier decisions. Larkspur: replenishment orders run twice a day (hours), the store managers’ app shows stock (minutes), the “empty shelf now” alert of Part 29 needs seconds. Elsewhere: an airline’s gate screens need seconds, its route profitability a day.

Streaming into the lake: the commit interval

A streaming job writing Iceberg makes its data visible only at a commit, and Flink’s sink commits once per completed checkpoint (Part 28): the checkpoint interval is the lake’s freshness. Every commit writes at least one data file per partition it touched, per writer, plus a manifest, a manifest list and a new metadata.json. That is Part 10’s small-file problem, produced on a timer.

What breaks without it. Ten-second commits into a table partitioned by store write up to 600 files a commit, 8,640 commits a day: five million tiny files a day. Larkspur: one-minute commits, partitioned by business date. Elsewhere: an ad platform landing impression logs every minute compacts them hourly.

Compaction behind a streaming writer

Compaction rewrites many small files into a few large ones in a replace commit; the rows don’t change, so readers following the table’s appends skip it. Behind a streaming writer it must run continuously without fighting the writer’s commits. Iceberg 1.12 does it inside Flink: the TableMaintenance API (RewriteDataFiles, ExpireSnapshots, DeleteOrphanFiles), or a post-commit step of the SinkV2 IcebergSink (experimental) switched on in SQL.

What breaks without it. Query time and requests grow with every commit. Larkspur: Step 6. Elsewhere: a smart-meter platform compacts each day partition once the day closes.

Streaming-first table storage: Paimon and Fluss

Two Apache projects start from streams rather than batch commits. Apache Paimon (2.1, October 2026) is a lake format with “LSM-powered streaming updates”: primary-key tables that absorb upserts cheaply and emit a changelog. Apache Fluss (1.0, September 2026; top-level since July 2026) is “lakehouse-native streaming storage”: low-latency log and primary-key tables for Flink, tiered into lake formats by a Flink job, with “union reads” across both.

What breaks without them. Nothing at Larkspur’s size: Iceberg plus compaction was enough. They earn their place when updates dominate (a CDC mirror of a busy database) or sub-second reads must share the lake’s storage. Elsewhere: a ride-hailing company keeps per-trip state in primary-key tables updated thousands of times a second.

Spark’s streaming modes

Spark Structured Streaming runs micro-batches, which suit the minutes tier; Trigger.AvailableNow turns the same query into the hours tier. Real-Time Mode (Spark 4.1+) processes records continuously, but the 4.2.1 docs say it “supports stateless queries only” (stateful planned for 4.3) and give it no status label. Databricks’ runtime has its own GA version with state.

What breaks if you assume. A team picks Real-Time Mode for deduplication, which open-source Spark can’t run in it yet. Larkspur: the hourly tier could be one Structured Streaming query with Trigger.AvailableNow; the reduced variant has no Spark, so a host batch does the same job. Elsewhere: a fraud team filters card authorisations with it and aggregates in micro-batches.

Streaming databases and real-time OLAP stores

A streaming database keeps SQL views current as changes arrive and serves them: RisingWave (Apache 2.0, PostgreSQL-compatible SQL) and Materialize (self-managed under BSL 1.1: source-available, not open source). A real-time OLAP store (ClickHouse, StarRocks, Apache Pinot, Apache Druid; Part 13) ingests streams into its own storage and answers many concurrent queries in milliseconds. The first maintains answers; the second makes raw facts quick to aggregate.

What breaks without one. User-facing analytics on the lake: every tap pays catalog calls and object-storage round trips. Larkspur: the seconds tier is ClickHouse reading the POS topic itself. Elsewhere: a delivery app shows each restaurant its last hour of orders from Pinot.


Under the Hood

What one lake commit writes

At each checkpoint the writer closes the Parquet file it has open for each partition, and the committer adds them in one Iceberg commit (the manifest, manifest list and metadata.json of Part 7). A checkpoint with no new rows commits nothing (with one exception, in Gotchas), and a minute of sales across two business dates costs two data files and three metadata files, however few rows they hold. A reader opens every live manifest and every data file the filter keeps: the work grows with the commits until compaction folds them.

How the Kafka engine makes a part

A Kafka table in ClickHouse is a consumer, not storage. A background thread polls the topic (kafka_poll_timeout_ms), decodes messages into a block and, when the block is full (kafka_max_block_size) or kafka_flush_interval_ms has passed, pushes it through every materialized view attached to the table; each view’s INSERT writes one new part (Part 13) to its target table, and only then are the offsets committed. The flush interval is the tier’s freshness, and its cost: every flush is a part, and background merges must keep combining them (MergeTree slows inserts above 1,000 active parts in a partition, parts_to_delay_insert, and refuses them above 3,000). Merges are also when ReplacingMergeTree drops rows with the same sorting key: “Data deduplication occurs only during a merge. Merging occurs in the background at an unknown time”, so an exact answer now needs FINAL, which merges at read time.

Exactly-once costs latency

Each tier gets uniqueness differently, and the method sets its floor on freshness:

  • Minutes (Flink → Iceberg): files are invisible until the checkpoint completes and the committer commits them with its ID (Part 29): exactly-once, never fresher than the checkpoint interval. A transactional Kafka sink has the same floor for read_committed readers.
  • Hours (the batch): the Kafka offsets it read up to go into the same Iceberg commit as the lines (a snapshot property); the next run starts from them. Exactly-once without transactions, because the batch owns its offsets.
  • Seconds (ClickHouse): “After a batch is pushed to the dependent materialized views, the consumed offset is committed”, so a crash between the two inserts the block again: at-least-once (Keeper-based offsets, (experimental), exist to deduplicate that case). The copies are harmless only because the table’s key, basket and line, collapses them to one, as it does the tills’ re-sends.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-30). make up-realtime is the reduced variant: core, Kafka, Apicurio, the Flink JobManager with one TaskManager, and ClickHouse; Spark, Trino and Connect stay stopped, and the hourly batch runs on the host in DuckDB and PyIceberg, not on Spark or Trino. It refuses to start while Spark, Trino or Connect run (stop Spark, Trino and Connect first: …, captured with Trino up). Container memory peaked at 3.70 GiB in the hour-long run (limits add up to 10.5 GiB; the VM has 9.8).

Terminal window
make up-realtime
uv run python -m scripts.part30_tiers setup # empty tables, the live topic, ClickHouse's consumers
uv run python -m scripts.part30_tiers clock # the VM's clock against the host's
uv run python -m scripts.part30_tiers live # one hour: the replay, all three tiers, metered
uv run python -m scripts.part30_tiers check
uv run python -m scripts.part30_tiers freshness
uv run python -m scripts.part30_tiers cost
uv run python -m scripts.part30_tiers query
uv run python -m scripts.part30_tiers setup && uv run python -m scripts.part30_tiers live --speed 57.6 --hourly-every 0 --compacted
uv run python -m scripts.part30_tiers compact && uv run python -m scripts.part30_tiers query
uv run python -m scripts.part30_tiers cleanup # drop Part 30's tables, topic, groups and ClickHouse database

Step 0: a day of tills in an hour

A Java producer (kafka-backbone, live) replays upload day 15 August, the 22,276 baskets of Parts 27–29, onto a new topic, larkspur.pos.live, each sent when its own uploaded_at comes round on the clock, 24 times faster: a day of uploads in 60 minutes, re-sends and late uploads included. The record’s Kafka timestamp is the moment it was sent, and every tier measures freshness from it, in wall-clock time: the tiers’ intervals don’t depend on the speed-up. The event rate does: about 6 baskets a second instead of the lab day’s 0.26, so every CPU figure below is a day of uploads processed in an hour. The stock metric is Part 29’s book stock for business day 15 August: opening stock minus every unit sold, each line once, however late within the replayed upload day.

Step 1: three tiers on one topic

Each tier reads the topic itself, with its own consumer, and applies the same rules: the first copy of each basket (Part 28), the store-local business date (Part 5).

TierReads the topicMakes it queryableServes the question from
Hoursa batch on the host, once an hour (confluent-kafka)one Iceberg append of the new lines, then a 121,450-row stock tablestream.stock_level_hourly
MinutesFlink SQL, Part 28’s dedupone Iceberg commit per 60-second checkpointstream.stock_lines_1m, aggregated at read time
SecondsClickHouse’s Kafka table enginea materialized view inserting each flushed block into MergeTreerealtime.sale_lines, aggregated at read time

The minute tier is Part 28’s job with SET 'execution.checkpointing.interval' = '60 s' (flink/sql/part30/minute.sql). The seconds tier is two statements in ClickHouse: a Kafka table, which is a consumer, and a materialized view that turns each block into lines:

sql/part30/clickhouse/01_ingest.sql (excerpt; ClickHouse 26.8)
CREATE TABLE realtime.pos_queue (
basket_id String, store_id String, event_ts DateTime64(3, 'UTC'),
lines Array(Tuple(line_no Int32, sku String, qty Int32, unit_price_minor Int64, discount_minor Int64,
promo_id Nullable(String)))
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:29092', kafka_topic_list = 'larkspur.pos.live',
kafka_group_name = 'clickhouse-stock', kafka_format = 'AvroConfluent',
format_avro_schema_registry_url = 'http://apicurio:8080/apis/ccompat/v7',
kafka_poll_timeout_ms = 100, -- default stream_poll_timeout_ms, 500
kafka_flush_interval_ms = 250; -- default stream_flush_interval_ms, 7500: a block (and a part) per flush
CREATE MATERIALIZED VIEW realtime.sale_lines_mv TO realtime.sale_lines AS
SELECT basket_id, l.line_no AS line_no, store_id, l.sku AS sku, l.qty AS qty,
multiIf(startsWith(store_id, 'GB'), toDate(event_ts, 'Europe/London'),
startsWith(store_id, 'IN'), toDate(event_ts, 'Asia/Kolkata'),
toDate(event_ts, 'Asia/Singapore')) AS business_date,
event_ts, _timestamp_ms AS produced_at, now64(3, 'UTC') AS inserted_at
FROM realtime.pos_queue ARRAY JOIN lines AS l;

sale_lines is a ReplacingMergeTree ordered by store, SKU, basket and line, so it keeps one copy of each basket (without a version column, the last inserted; re-sends are identical). A second consumer group, clickhouse-stock-default, feeds sale_lines_default with the engine’s default flush, to show what the setting buys.

Step 2: the same answer in every tier

After the hour, check compares each tier with a batch answer computed straight from the raw zone in DuckDB: the four figures, the line count, then every store-SKU’s level, EXCEPT both ways:

batch answer from the raw zone: 121,450 store-SKUs, 13,436 at or below zero, availability 88.94%, 75,396 units sold
hours equal: True {'store_skus': 121450, 'at_or_below_zero': 13436, 'availability_pct': 88.94, 'units_sold': 75396}
minutes equal: True {'store_skus': 121450, 'at_or_below_zero': 13436, 'availability_pct': 88.94, 'units_sold': 75396}
seconds equal: True {'store_skus': 121450, 'at_or_below_zero': 13436, 'availability_pct': 88.94, 'units_sold': 75396}
lines: expected 58,119; minutes 58,119, hours 58,119, seconds after FINAL 58,119; store-SKUs sold outside the opening file: 0
row by row (store, SKU, level) against the batch's 121,450 rows, EXCEPT both ways:
hours 121,450 rows; in the tier, not the batch: 0; in the batch, not the tier: 0
minutes 121,450 rows; in the tier, not the batch: 0; in the batch, not the tier: 0
seconds 121,450 rows; in the tier, not the batch: 0; in the batch, not the tier: 0

Three latencies, one answer, row for row. 88.94% is one day’s book stock from independently generated WMS and till data (Part 29), not the Q3 close-of-business 88.5% of Parts 5 and 25.

Step 3: freshness, measured

For every line, produced (the Kafka timestamp) to queryable: ClickHouse’s inserted_at; the commit of the snapshot that added the line’s file; the commit of the stock table that first included it.

produced (Kafka record timestamp) -> queryable, per line
tier lines min median p95 max
seconds 58,123 0.00 s 0.26 s 0.75 s 0.86 s
seconds, default flush 58,126 0.00 s 3.80 s 7.24 s 8.04 s
minutes 58,119 0.08 s 30.57 s 57.37 s 1.0 min
hours 58,119 54.71 s 31.8 min 51.6 min 60.9 min

The medians follow the intervals: about half of 7.5 s, 60 s and 60 minutes; at a 250 ms flush, polling and inserting take a similar share. Freshness is a schedule, not a speed. The hourly row is one hourly cycle, measured: the run fell at minute 60.8 (once the other tiers had caught up), where the next scheduled run would be. The producer and the hourly batch run on the host, so that row needs no clock comparison; ClickHouse and Flink stamp times in the Docker VM, whose clock live compared with the host’s: ClickHouse −0.8 ms (± 0.5), the TaskManager +14.3 ms (± 16.3) after the run; −0.9 and +15.3 ms before.

Step 4: what each tier cost

cost counts what the hour left behind and what each tier’s containers used:

one replayed hour (60.0 min of wall clock), upload day 2026-08-15 at 24x
hours: lines commits 1 {'append': 1} live data files 3 objects 7 (4 metadata), data 730 KiB, metadata 11 KiB
hours: stock table commits 1 {'append': 1} live data files 1 objects 5 (4 metadata), data 412 KiB, metadata 9 KiB
minutes: lines commits 61 {'append': 61} live data files 104 objects 288 (184 metadata), data 952 KiB, metadata 3,054 KiB
seconds: sale_lines new parts 5268, merges 3951, active parts 5, 1,283 KiB, rows 58,123 (58,119 after FINAL)
seconds: sale_lines_default new parts 472, merges 236, active parts 5, 1,257 KiB, rows 58,126 (58,119 after FINAL)
CPU seconds: hourly batch 0.7 over 1 runs ([2.9] s); Flink TaskManager 43 (JobManager 26); ClickHouse 168; kafka 684, apicurio 18, seaweedfs 43, polaris 12, postgres 31
projected month at lab volume, Part 24 list prices (USD): hours 0.05 (CPU 0.00, memory 0.00, PUTs 0.04); minutes 3.65 (CPU 0.29, memory 2.31, PUTs 1.05); seconds 2.66 (CPU 1.10, memory 1.56, PUTs 0.00)
  • Hours: two commits, 12 objects: the lines (a data file per business date: 14, 15, 16 August) and the stock table.
  • Minutes: 61 commits and 288 objects for the same rows: 104 data files averaging 9 KiB, and 3 MiB of metadata, three times the data, because every metadata.json repeats the growing snapshot list.
  • Seconds: 5,268 parts in an hour from a 250 ms flush, 3,951 merges to fold them; the default 7.5 s flush made 472. ClickHouse’s 168 CPU-seconds cover both consumers, both views and about 80 s of background work it also does when idle (23 ms a second, measured separately with the topic quiet), against 43 for the Flink TaskManager.
  • Shared: Kafka’s 684 CPU-seconds are almost all its idle load here (180 ms a second with nothing flowing).

The projection prices a month at Part 24’s AWS us-east-1 list prices (read 29 October 2026): CPU used, memory held (the batch only while it runs), objects written. At 24 times the lab’s real rate it is still a few dollars; the shape is the point: the hourly tier pays for 3 seconds an hour, the others for memory that never sleeps, the minute tier also for files.

Step 5: the question on each tier

The app’s question, how many store-SKUs are out and what is availability, asked of each tier three times:

tier store-SKUs at/below 0 avail % units median requests/query
hours (published stock table) 121,450 13,436 88.94 75,396 0.036s GET 8
minutes (lines, Iceberg) 121,450 13,436 88.94 75,396 0.127s GET 308
seconds (ClickHouse) 121,450 13,436 88.94 75,396 0.039s none (no object storage)

The answers match; the reads don’t: the minute tier’s question made 308 GET requests (metadata, manifests and data files), the published table 8. 600 store apps refreshing the raw lines every minute would make 266 million GET requests a day, about USD 106 at Part 24’s price, for one tile (a projection). Publish a small table from the stream, or serve from the OLAP store.

Step 6: compaction in the writing job

A second replay (the day in 25 minutes; same services, started as up-flink-lake + up-olap, with an earlier part30_tiers.py) ran the minute tier twice side by side: plain, and through Iceberg’s SinkV2 sink with compaction after commits:

flink/sql/part30/minute_compacted.sql (excerpt; Flink SQL)
SET 'table.exec.iceberg.use-v2-sink' = 'true';
SET 'flink-maintenance.rewrite.enabled' = 'true';
SET 'flink-maintenance.rewrite.schedule.commit-count' = '10';

The first line switches to the SinkV2 IcebergSink (experimental); by default it compacts after ten commits or 600 seconds, whichever comes first.

compact and query afterwards (the hourly tier was off in this run):

stream.stock_lines_1m commits 26 {'append': 26}, live data files 47 (781 KiB), objects in the folder 126 (1,545 KiB), rows 58,119
stream.stock_lines_1m_compacted commits 28 {'append': 26, 'replace': 2}, live data files 15 (700 KiB), objects in the folder 157 (2,624 KiB), rows 58,119
rewrite at 06:15:09.573: 20 files -> 2, live data files after: 2
rewrite at 06:25:09.536: 16 files -> 2, live data files after: 3
tier store-SKUs at/below 0 avail % units median requests/query
minutes (lines, Iceberg) 121,450 13,436 88.94 75,396 0.122s GET 133
seconds (ClickHouse) 121,450 13,436 88.94 75,396 0.046s none (no object storage)
minutes, compacted 121,450 13,436 88.94 75,396 0.097s GET 40

Two replace commits, ten minutes apart, folded 20 and 16 files into two each, with no lock to configure for one job and no Spark or Trino. The table ended with 15 live files instead of 47, and the question made 40 GET requests instead of 133. The folder grew, though: 157 objects against 126, because the replaced files stay for older snapshots until they expire (Part 10). The same job can expire them (flink-maintenance.expire-snapshots.enabled, not run here).


The Landscape

Current product names as of 30 October 2026. A cell that couldn’t be verified says so.

NeedOpen sourceAWSGCPAzureSnowflake / DatabricksNotes
Minutes: streaming appends into lake tables, compactedFlink Iceberg sink + TableMaintenance (post-commit via SinkV2 (experimental)); Iceberg Kafka Connect sink; Apache Paimon 2.1; Apache Fluss 1.0Amazon Data Firehose into Apache Iceberg Tables; S3 Tables compaction (on by default)Storage Write API into Apache Iceberg managed tables (Iceberg metadata can lag 90 min)Fabric Eventstream to Lakehouse (Delta)Snowpipe Streaming into Snowflake-managed Iceberg tables; Databricks Zerobus Ingest (GA on AWS, Azure)Confluent Tableflow: Delta GA, Iceberg “available”
Seconds: serving fresh analyticsClickHouse 26.8 LTS (Kafka engine), StarRocks, Apache Pinot 1.6, Apache Druid 38, Apache Doris 4.1Redshift streaming ingestion (Kinesis, MSK)BigQuery Storage Write API default streamFabric Eventhouse; Azure Data Explorer streaming ingestionSnowflake interactive tables (GA) fed by Snowpipe Streaming; Databricks: not verified (2026-10-30)ClickPipes is a ClickHouse Cloud feature
Views kept current, or records processed in millisecondsRisingWave 3.1 (Apache 2.0); Materialize (BSL 1.1); Spark Real-Time Mode (stateless)Amazon Managed Service for Apache FlinkBigQuery continuous queries (stateful operations (preview))not verified (2026-10-30)Snowflake dynamic tables (target lag ≥ 60 s); Databricks real-time mode (GA, stateful on Runtime 18+)Flink SQL: Part 28

Tips & Gotchas

Gotcha — the Kafka engine’s default flush is 7.5 s. Unset, kafka_flush_interval_ms falls back to stream_flush_interval_ms: Step 3’s default-flush row.

Gotcha — a new consumer group isn’t reading yet. ClickHouse’s two consumers held their six partitions 44.5 s after the tables were created; live waits for system.kafka_consumers before producing. Recreating a Kafka table opens the same gap.

Gotcha — a quiet table still commits. Flink’s Iceberg committer skips empty checkpoints, except every tenth in a row (flink.max-continuous-empty-commits, a table property, default 10), which commits an empty snapshot to record the checkpoint ID: 144 snapshots a day from an idle one-minute job. Expire them like the others.

Gotcha — count the copies before trusting a seconds tier. After the hour sale_lines still held 58,123 rows for 58,119 lines: a tile reading without FINAL shows extra units until a merge runs.

Tip — stamp every tile with its tier and as-of time. “Stock as of 10:42 (refreshes every minute)” turns a disagreement between the app and the 9 a.m. report into an explanation.


Troubleshooting

SymptomLikely causeHow to confirmFix
ClickHouse: Unknown type: com.larkspur.pos.BasketLine: while fetching schema id = 3Apicurio’s serializer registered the nested records as references; ClickHouse fetches one schema by ID and can’t follow themGET /apis/ccompat/v7/subjects lists com.larkspur.pos.BasketLine as a subject of its ownProducer setting apicurio.registry.dereference-schema=true (“only supported for Avro”), then register again
Setting kafka_max_block_size is neither a builtin setting … (UNKNOWN_SETTING)Kafka engine settings belong to the table, not to a queryThe setting is listed under the table engine’s settingsPut it in the CREATE TABLE … SETTINGS

Design Drill

The question: “We run 2,000 quick-service restaurants. Product wants a ‘live’ sales dashboard for regional managers and head office. Ask the right questions, then propose the cheapest design that meets the answers.”

Clarify first

  • Who looks at it, how often, and what do they do differently when a number moves?
  • How fresh, for which number: the decision’s deadline, not the screen’s refresh rate?
  • Which event is a sale: placed, paid, or not voided by close? How far may today’s provisional figure drift from finance’s final one?
  • How many viewers at peak, and do franchisees see only their own restaurants?
  • What exists already: a lake, a stream of orders, a nightly batch?

The shape of a strong answer

  • Freshness per tile: head office’s “today versus last week” is fine at 5 minutes, a manager chasing a stalled drive-through needs 1; a kitchen screen needs seconds, but that is an operational system, not analytics.
  • Size it: 2,000 restaurants × about 1,000 orders a day is about 25 orders a second on average, about 100 at a 4× lunch peak (assumptions). Tiny.
  • Cheapest at that size: a micro-batch every 5 minutes (offsets kept with the data, like this part’s hourly tier) or the warehouse’s own streaming ingest, writing a small rollup the dashboard reads. Always-on Flink earns its place at 1 minute or with real streaming logic.
  • One rule set: dedup on order ID, the restaurant’s business date, voids and refunds as their own facts (Part 4); the nightly batch republishes yesterday as “final”, today stays “provisional” with its as-of time.
  • A seconds tier only if a requirement survives the questions: a real-time OLAP store reading Kafka, with dedup it can do and row-level access for franchisees.

Trade-offs to say out loud

  • One-minute commits mean 1,440 commits a day per table: compaction and snapshot expiry become part of the pipeline.
  • A real-time OLAP store is a second copy with its own dedup, retention and access rules, plus a team that runs it.

Common weak answers

  • “Make it all real time” without asking who acts on which number.
  • A seconds-tier store whose totals never reconcile with finance’s.
  • Ten-second commits into a table partitioned by restaurant: 2,000 files per commit.

Further Reading

  1. Apache Iceberg 1.12 documentation, “Flink Maintenance” (TableMaintenance, post-commit maintenance in SQL). https://iceberg.apache.org/docs/latest/flink-maintenance/
  2. ClickHouse documentation, “Kafka table engine” and “ReplacingMergeTree”. https://clickhouse.com/docs/engines/table-engines/integrations/kafka and https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree
  3. Apache Spark 4.2.1 documentation, “Real-time Mode”. https://spark.apache.org/docs/4.2.1/streaming/real-time-mode.html
  4. Apache Fluss documentation, “Streaming Lakehouse overview”. https://fluss.apache.org/docs/streaming-lakehouse/overview/
  5. Apache Paimon documentation. https://paimon.apache.org/docs/master/

Story Closing

Rohan chose the minute tier for the app, with a small stock table published from the stream (Step 5) and the as-of time on every tile. The empty-shelf alerts stayed on Part 29’s job, the only thing that needed seconds.

“Three tiers, one number,” Farah said. “Who decides what ‘availability’ means in each?”

“Right now, three copies of the same SQL,” Ingrid said. “That’s next.”

In Part 31, Larkspur defines net revenue, stock availability and conversion once, in a semantic layer, and serves them to a BI tool and a REST endpoint.


This is Part 30 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 3: Real Time, Serving and AI.