A mobile payment platform in Southeast Asia is the hardest real-time analytics workload I know of that is not an ad exchange. Tens of millions of wallets, QR and account-to-account rails in six countries, top-ups, merchant settlements, cash-in and cash-out agents, and a traffic curve with cliffs on payday, Ramadan, Tet and the double-digit shopping festivals.
Every one of those transactions has to be visible to a risk engine within seconds, to a merchant dashboard within a minute, and to finance, regulators and the data science team by the next morning. This post is the reference model we use for real-time payments analytics on ClickHouse with the Apache stack around it, written from the platform side rather than the vendor side, with the schema, the ingestion path and the guardrails we would put in front of a team building it.
A note on what this is not. It is not a case study of one customer, and the capacity numbers in it are illustrative sizing points, labelled as such, that you should replace with your own measurements. It is the design we would argue for in the first architecture workshop, and the reasons we would give.
The workload, stated in numbers the architecture has to survive
The shape of a real-time payments analytics workload matters more than the totals, so here is the shape. Peak transaction rate is ten to twenty times the daily average, and the peak arrives in minutes rather than hours when a promotion drops. Each payment generates several events, not one: authorisation, risk decision, ledger posting, settlement, notification, and often a reversal or dispute later.
A platform doing 30 million payments a day is therefore ingesting 150 to 250 million events a day, with sustained bursts above 50,000 events per second. Roughly a third of the analytical queries are the same few hundred dashboard shapes served thousands of times a minute; the rest are ad hoc investigations by risk, finance and product that touch months of history.
Two regional constraints shape everything else. Data residency: Indonesia, Vietnam and, in practice, the Philippines and Thailand expect certain categories of payment and personal data to stay in country, so a single regional cluster is not an option for raw events. And PCI DSS: primary account numbers never enter the analytics estate at all, only tokens, and the analytics platform is out of scope for card data precisely because we keep it that way at the schema level.
The real-time payments analytics architecture in one picture
The stack is Apache end to end except for the store at the centre. Apache Kafka is the event backbone, Apache Flink does stream enrichment and windowed risk features, ClickHouse is the hot analytical store, Apache Iceberg on object storage is the lakehouse tier that Apache Spark and the data science team work from, Apache Airflow orchestrates the batch edges, and Apache Superset is the BI layer for everyone who is not a risk analyst with a SQL client. Debezium feeds change data from the core ledger databases into Kafka so that the analytics estate never queries the ledger directly.
Why ClickHouse and not Apache Pinot or Apache Druid at the centre, given that the rest is Apache? We have run all three for customers. Pinot and Druid are excellent at the narrow dashboard-serving problem, and if that were the only workload either would do. The payments workload is not narrow: the same store has to serve sub-second dashboards, joins against merchant and customer dimensions, months-deep ad hoc SQL, materialised rollups and the risk team’s feature queries.
ClickHouse does all of that from one MergeTree table family with one SQL dialect, and its cost per query at this scale is lower than either alternative in every estate we have measured. The trade-off is that you own ingestion discipline, which is what most of this post is about.
Layer 1: Kafka topics and the event contract
Everything downstream in a real-time payments analytics platform depends on getting the topic design right. We use one topic per event class rather than one giant payments topic, keyed by wallet or merchant so that a consumer sees a given entity’s events in order, with enough partitions for the peak, not the average. Schemas are Avro or Protobuf under a registry, and the contract includes the fields the analytics estate needs for correctness: an idempotency key per event, an event time set by the producing service, and the country code that drives residency routing. Debezium publishes ledger changes in the same registry so that the analytics side can rebuild a balance at any point in time without touching the ledger.
# Real-time payments analytics reference model, layer: Kafka topics and CDC
# Topic design for the payments backbone (illustrative partition counts)
kafka-topics.sh --bootstrap-server ${KAFKA_BROKERS} --create \
--topic payments.authorized --partitions 96 --replication-factor 3 \
--config retention.ms=604800000 --config min.insync.replicas=2 \
--config cleanup.policy=delete
kafka-topics.sh --bootstrap-server ${KAFKA_BROKERS} --create \
--topic payments.ledger.posted --partitions 96 --replication-factor 3 \
--config retention.ms=604800000 --config min.insync.replicas=2
# Debezium connector for the ledger (PostgreSQL logical decoding), one connector per country database
curl -s -X PUT ${CONNECT_URL}/connectors/ledger-id-cdc/config -H 'Content-Type: application/json' -d '{
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "${LEDGER_ID_HOST}",
"database.dbname": "ledger",
"database.user": "${DBZ_USER}",
"database.password": "${DBZ_PASSWORD}",
"plugin.name": "pgoutput",
"publication.autocreate.mode": "filtered",
"table.include.list": "public.ledger_entries,public.wallet_balances",
"topic.prefix": "ledger.id",
"tombstones.on.delete": "false",
"transforms": "unwrap",
"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
"transforms.unwrap.add.fields": "op,ts_ms,lsn"
}'Layer 2: Flink for enrichment and windowed risk features
Flink sits between Kafka and ClickHouse for two jobs that a database should not be doing on the write path. The first is enrichment: joining each authorisation with the current merchant category, the wallet’s KYC tier and the device fingerprint, so that ClickHouse receives a denormalised event and never has to join on the hot path.
The second is windowed features for the risk engine: transaction count and amount per wallet over the last five minutes, distinct merchants per device over the last hour, first-seen flags. Flink computes these with event-time windows and watermarks so that late events are handled correctly, and publishes both the enriched event and the feature vector back to Kafka, where ClickHouse and the risk service consume them independently.
-- Real-time payments analytics reference model, layer: Flink enrichment
-- Flink SQL: enrich authorisations and compute a 5-minute velocity feature per wallet
CREATE TABLE payments_authorized (
event_id STRING,
event_time TIMESTAMP(3),
wallet_id STRING,
merchant_id STRING,
device_id STRING,
country STRING,
amount_minor BIGINT,
currency STRING,
channel STRING,
WATERMARK FOR event_time AS event_time - INTERVAL '10' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'payments.authorized',
'properties.bootstrap.servers' = '${KAFKA_BROKERS}',
'format' = 'avro-confluent',
'avro-confluent.url' = '${SCHEMA_REGISTRY}',
'scan.startup.mode' = 'group-offsets'
);
CREATE TABLE merchant_dim (
merchant_id STRING,
mcc STRING,
merchant_tier STRING,
onboarded_at TIMESTAMP(3),
PRIMARY KEY (merchant_id) NOT ENFORCED
) WITH (
'connector' = 'upsert-kafka',
'topic' = 'ledger.id.public.merchants',
'properties.bootstrap.servers' = '${KAFKA_BROKERS}',
'key.format' = 'avro-confluent',
'value.format' = 'avro-confluent',
'key.avro-confluent.url' = '${SCHEMA_REGISTRY}',
'value.avro-confluent.url' = '${SCHEMA_REGISTRY}'
);
CREATE TABLE payments_enriched (
event_id STRING,
event_time TIMESTAMP(3),
wallet_id STRING,
merchant_id STRING,
mcc STRING,
merchant_tier STRING,
device_id STRING,
country STRING,
amount_minor BIGINT,
currency STRING,
channel STRING,
wallet_txn_5m BIGINT,
wallet_amt_5m BIGINT
) WITH (
'connector' = 'kafka',
'topic' = 'payments.enriched',
'properties.bootstrap.servers' = '${KAFKA_BROKERS}',
'format' = 'avro-confluent',
'avro-confluent.url' = '${SCHEMA_REGISTRY}'
);
INSERT INTO payments_enriched
SELECT
p.event_id,
p.event_time,
p.wallet_id,
p.merchant_id,
m.mcc,
m.merchant_tier,
p.device_id,
p.country,
p.amount_minor,
p.currency,
p.channel,
COUNT(*) OVER w AS wallet_txn_5m,
SUM(p.amount_minor) OVER w AS wallet_amt_5m
FROM payments_authorized AS p
LEFT JOIN merchant_dim FOR SYSTEM_TIME AS OF p.event_time AS m
ON m.merchant_id = p.merchant_id
WINDOW w AS (
PARTITION BY p.wallet_id
ORDER BY p.event_time
RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW
);The decision that teams argue about is whether ClickHouse could compute those features itself with materialised views. It can, and for the dashboard rollups it does, but the risk engine needs the feature within a second of the event with exactly-once semantics on a Kafka consumer group, and Flink’s checkpointed state is the right tool for that. ClickHouse’s job is to be the store everyone queries, not the stream processor everyone depends on.
Layer 3: the ClickHouse schema for payments events
The hot table is where most of the real-time payments analytics performance is decided, so it gets the most care. Amounts are integers in minor units with a currency column, never floats. The PAN never appears; the token does. Country is part of the partition key so that residency can be enforced by placing partitions, and so that a regulator’s request for one country’s data is a partition operation. The sort key leads with merchant and then event time, because merchant-scoped time-range queries dominate both the dashboards and the investigations; wallet-scoped queries are served by a projection rather than by compromising the primary sort order.
-- Real-time payments analytics reference model, layer: hot store schema
-- Hot store: replicated, per-country partitions, merchant-first sort key
CREATE TABLE payments.events_local ON CLUSTER 'payments_cluster'
(
event_id UUID,
event_time DateTime64(3, 'UTC'),
event_date Date MATERIALIZED toDate(event_time),
event_type LowCardinality(String), -- authorized, posted, reversed, settled, disputed
country LowCardinality(String), -- ID, VN, PH, TH, MY, SG
wallet_id UInt64,
merchant_id UInt64,
mcc LowCardinality(String),
merchant_tier LowCardinality(String),
device_id String CODEC(ZSTD(3)),
card_token String CODEC(ZSTD(3)), -- token from the vault; never the PAN
channel LowCardinality(String), -- qr, a2a, card, agent
amount_minor Int64 CODEC(T64, ZSTD(1)),
currency LowCardinality(String),
status LowCardinality(String),
risk_score UInt16,
wallet_txn_5m UInt32,
wallet_amt_5m Int64,
ingested_at DateTime64(3, 'UTC') DEFAULT now64(3),
INDEX idx_wallet wallet_id TYPE minmax GRANULARITY 4,
INDEX idx_device device_id TYPE bloom_filter(0.01) GRANULARITY 8,
PROJECTION by_wallet
(
SELECT *
ORDER BY (wallet_id, event_time)
)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/payments/events_local', '{replica}')
PARTITION BY (country, toYYYYMMDD(event_date))
ORDER BY (merchant_id, event_time, event_id)
TTL event_date + INTERVAL 90 DAY TO VOLUME 'cold',
event_date + INTERVAL 400 DAY DELETE
SETTINGS
index_granularity = 8192,
storage_policy = 'hot_cold',
min_bytes_for_wide_part = 10485760,
non_replicated_deduplication_window = 0,
replicated_deduplication_window = 10000,
replicated_deduplication_window_seconds = 3600;
CREATE TABLE payments.events ON CLUSTER 'payments_cluster' AS payments.events_local
ENGINE = Distributed('payments_cluster', 'payments', 'events_local', cityHash64(merchant_id));
-- Row policies: a Vietnam analyst sees Vietnam; the regional risk team sees everything
CREATE ROW POLICY country_vn ON payments.events_local
FOR SELECT USING country = 'VN' TO analysts_vn;
CREATE ROW POLICY country_all ON payments.events_local
FOR SELECT USING 1 TO risk_regional, finance_regional;Three choices in that DDL are worth defending. Daily partitions per country keep the part count sane at this volume and make residency deletions a DROP PARTITION with a confirmation gate rather than a mutation. The deduplication window is what makes Kafka at-least-once delivery safe: an insert block that arrives twice inside the window is dropped by the replica, which is why the ingestion path must produce deterministic blocks. And T64 on amounts with ZSTD is the codec pair that has compressed payment amounts best in our labs, because minor-unit amounts cluster tightly within a merchant.
Layer 4: ingestion into ClickHouse without a parts explosion
Ingestion is where real-time payments analytics platforms fail in their first month, and it is almost always the same failure: too many small inserts producing too many parts. We use the Kafka table engine for the enriched topic, with consumer count and block size set so that each consumer flushes a block of a few hundred thousand rows, and a materialised view that writes into the hot table.
Since 26.3 LTS async_insert is on by default, which is the right setting for the small API-driven inserts from operational services, but for the Kafka path the batching is done by the engine and the setting is irrelevant. The dashboards that need freshness within a few seconds read from the hot table directly; the ones that can tolerate a minute read from rollups.
-- Real-time payments analytics reference model, layer: Kafka engine ingestion
-- Kafka engine consumer for the enriched topic, one per shard
CREATE TABLE payments.events_queue ON CLUSTER 'payments_cluster'
(
event_id UUID,
event_time DateTime64(3, 'UTC'),
event_type String,
country String,
wallet_id UInt64,
merchant_id UInt64,
mcc String,
merchant_tier String,
device_id String,
card_token String,
channel String,
amount_minor Int64,
currency String,
status String,
risk_score UInt16,
wallet_txn_5m UInt32,
wallet_amt_5m Int64
)
ENGINE = Kafka
SETTINGS
kafka_broker_list = '${KAFKA_BROKERS}',
kafka_topic_list = 'payments.enriched',
kafka_group_name = 'clickhouse-payments-hot',
kafka_format = 'AvroConfluent',
format_avro_schema_registry_url = '${SCHEMA_REGISTRY}',
kafka_num_consumers = 4,
kafka_max_block_size = 262144,
kafka_poll_max_batch_size = 65536,
kafka_flush_interval_ms = 2000,
kafka_handle_error_mode = 'stream',
kafka_thread_per_consumer = 1;
CREATE MATERIALIZED VIEW payments.mv_events_ingest ON CLUSTER 'payments_cluster'
TO payments.events_local AS
SELECT
event_id, event_time, event_type, country, wallet_id, merchant_id, mcc, merchant_tier,
device_id, card_token, channel, amount_minor, currency, status, risk_score,
wallet_txn_5m, wallet_amt_5m
FROM payments.events_queue
WHERE length(_error) = 0;
-- Dead-letter capture: rows the Avro decoder rejected, kept for replay
CREATE MATERIALIZED VIEW payments.mv_events_dlq ON CLUSTER 'payments_cluster'
TO payments.events_dlq AS
SELECT _topic, _partition, _offset, _timestamp, _raw_message, _error
FROM payments.events_queue
WHERE length(_error) > 0;
-- The freshness SLO, measured rather than assumed: seconds from event to queryable
SELECT
country,
quantile(0.50)(dateDiff('millisecond', event_time, ingested_at)) / 1000 AS p50_s,
quantile(0.99)(dateDiff('millisecond', event_time, ingested_at)) / 1000 AS p99_s
FROM payments.events
WHERE event_time >= now() - INTERVAL 15 MINUTE
GROUP BY country;The guardrail is an alert on active parts per partition from system.parts and on merge backlog from system.merges, well below parts_to_delay_insert. If you upgrade to 26.8 LTS, remember that max_insert_threads now defaults to auto; the Kafka path is unaffected, but any Airflow backfill that does INSERT ... SELECT will suddenly write parts from every core, and it needs an explicit thread count and larger insert blocks in its profile.
Layer 5: serving dashboards at festival peak
The real-time payments analytics dashboards that merchants and operations staff watch at peak are a few hundred query shapes served tens of thousands of times a minute, and the way to keep them at sub-second p99 is not more hardware. It is a chain of materialised views into AggregatingMergeTree rollups at minute and hour grain, the query condition cache and query cache for the identical-query storm that a festival produces, and parallel replicas for the heavier shapes. Superset points at the rollups for the operational dashboards and at the base table only for the drill-down.
-- Real-time payments analytics reference model, layer: serving rollups
-- Minute-grain rollup that the merchant dashboard actually reads
CREATE TABLE payments.merchant_minute_local ON CLUSTER 'payments_cluster'
(
country LowCardinality(String),
merchant_id UInt64,
minute DateTime,
event_type LowCardinality(String),
txns AggregateFunction(count),
amount_minor AggregateFunction(sum, Int64),
wallets AggregateFunction(uniq, UInt64),
declined AggregateFunction(countIf, UInt8)
)
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/payments/merchant_minute_local', '{replica}')
PARTITION BY (country, toYYYYMM(minute))
ORDER BY (merchant_id, minute, event_type)
TTL minute + INTERVAL 13 MONTH DELETE;
CREATE MATERIALIZED VIEW payments.mv_merchant_minute ON CLUSTER 'payments_cluster'
TO payments.merchant_minute_local AS
SELECT
country,
merchant_id,
toStartOfMinute(event_time) AS minute,
event_type,
countState() AS txns,
sumState(amount_minor) AS amount_minor,
uniqState(wallet_id) AS wallets,
countIfState(status = 'declined') AS declined
FROM payments.events_local
GROUP BY country, merchant_id, minute, event_type;
-- What Superset runs for the merchant's live panel (served from the rollup, cached for 5 s)
SELECT
toStartOfMinute(minute) AS m,
countMerge(txns) AS txns,
sumMerge(amount_minor) / 100 AS amount,
uniqMerge(wallets) AS wallets,
countIfMerge(declined) / countMerge(txns) AS decline_rate
FROM payments.merchant_minute
WHERE merchant_id = {merchant_id:UInt64}
AND minute >= now() - INTERVAL 3 HOUR
GROUP BY m
ORDER BY m
SETTINGS
use_query_cache = 1,
query_cache_ttl = 5,
query_cache_nondeterministic_function_handling = 'save',
use_query_condition_cache = 1;
-- Profile for the BI service account: bounded memory, no runaway scans
CREATE SETTINGS PROFILE superset_readers
SETTINGS
max_memory_usage = 8589934592,
max_execution_time = 30,
max_rows_to_read = 2000000000,
max_result_rows = 1000000,
readonly = 2;The one number I would set an SLO on before launch is the ratio of dashboard queries served from rollups to those hitting the base table. A new estate typically starts well under half, and the way to push it past ninety percent is a month of reading system.query_log by normalized_query_hash and moving each expensive shape into a materialised view. That is the single biggest lever on both p99 and hardware cost, and it costs nothing but discipline.
Layer 6: Iceberg, Spark and the lakehouse edge
ClickHouse holds ninety days hot on NVMe and a further year on object storage through the tiered storage policy, and that covers every operational and most finance use cases. It does not cover the data science team training fraud models on three years of history with Spark, or the regulator who wants a reconstructable archive.
Those needs are served by Apache Iceberg on S3-compatible object storage in each country, written nightly by Airflow through Spark, catalogued in a REST catalog, and readable from ClickHouse through the Iceberg table engine when an analyst needs to reach back beyond the hot tier. Since 25.8 ClickHouse reads Iceberg equality deletes correctly, and partition pruning against the Iceberg partition spec is on with use_iceberg_partition_pruning, so a query for one merchant-month does not scan the archive.
# Real-time payments analytics reference model, layer: Iceberg export
# Airflow DAG: nightly export of the previous day from ClickHouse to Iceberg via Spark (per country)
from datetime import datetime, timedelta
from airflow import DAG
from airflow.providers.apache.spark.operators.spark_submit import SparkSubmitOperator
from airflow.providers.common.sql.operators.sql import SQLCheckOperator
COUNTRIES = ["ID", "VN", "PH", "TH", "MY", "SG"]
with DAG(
dag_id="payments_events_to_iceberg",
start_date=datetime(2026, 1, 1),
schedule="15 1 * * *", # 01:15 local, after the day's late events have landed
catchup=False,
max_active_runs=1,
default_args={"retries": 2, "retry_delay": timedelta(minutes=10)},
) as dag:
for country in COUNTRIES:
export = SparkSubmitOperator(
task_id=f"export_{country.lower()}",
application="/opt/jobs/events_to_iceberg.py",
conn_id=f"spark_{country.lower()}", # Spark runs in-country; data never leaves the region
application_args=["--country", country, "--day", "{{ ds }}"],
conf={
"spark.sql.catalog.lake": "org.apache.iceberg.spark.SparkCatalog",
"spark.sql.catalog.lake.type": "rest",
"spark.sql.catalog.lake.uri": f"${{ICEBERG_REST_{country}}}",
"spark.sql.catalog.lake.warehouse": f"s3a://payments-lake-{country.lower()}/",
},
)
reconcile = SQLCheckOperator(
task_id=f"reconcile_{country.lower()}",
conn_id=f"clickhouse_{country.lower()}",
sql=f"""
SELECT
(SELECT count() FROM payments.events
WHERE country = '{country}' AND event_date = toDate('{{{{ ds }}}}'))
= (SELECT count() FROM iceberg('s3://payments-lake-{country.lower()}/payments/events',
'${{S3_KEY}}', '${{S3_SECRET}}')
WHERE country = '{country}' AND event_date = toDate('{{{{ ds }}}}'))
""",
)
export >> reconcileThe reconcile step is not optional. A payments archive that does not match the hot store by row count and by summed amount is a compliance problem waiting for an audit, and the cheapest time to catch a Spark job that silently dropped a partition is the morning after.
Residency, replication and the multi-country topology
The residency rules push a real-time payments analytics topology into a pattern we now use by default: one ClickHouse cluster per country that holds raw events, and a regional cluster that holds only aggregates that regulators have agreed are not personal or payment data.
The per-country clusters are three shards by two replicas with a three-node Keeper ensemble each, sized to the country’s peak rather than the region’s, and the regional cluster is fed by materialised views over the country rollups through the Distributed engine and a scheduled INSERT ... SELECT rather than by replicating raw data across borders. Cross-border joins for group finance happen on the aggregate tier, and the regional team never has a credential on a raw-event cluster.
<!-- Real-time payments analytics reference model, layer: storage policy -->
<!-- Storage policy for the per-country clusters: 90 days hot on NVMe, then object storage in-country -->
<clickhouse>
<storage_configuration>
<disks>
<nvme><path>/var/lib/clickhouse/</path></nvme>
<s3_cold>
<type>s3</type>
<endpoint>https://s3.ap-southeast-3.amazonaws.com/payments-cold-id/</endpoint>
<use_environment_credentials>true</use_environment_credentials>
<metadata_path>/var/lib/clickhouse/disks/s3_cold/</metadata_path>
</s3_cold>
<s3_cold_cache>
<type>cache</type>
<disk>s3_cold</disk>
<path>/var/lib/clickhouse/disks/s3_cold_cache/</path>
<max_size>500Gi</max_size>
</s3_cold_cache>
</disks>
<policies>
<hot_cold>
<volumes>
<hot><disk>nvme</disk></hot>
<cold><disk>s3_cold_cache</disk></cold>
</volumes>
<move_factor>0.15</move_factor>
</hot_cold>
</policies>
</storage_configuration>
</clickhouse>Capacity and the freshness budget, illustratively
These are real-time payments analytics sizing points, not a promise. For a country doing 30 million payments a day, 200 million events, an average enriched event compressing to about 60 bytes on disk with the codecs above, the hot tier holds roughly 12 GB per day and just over 1 TB for 90 days per shard-set before replication, which fits comfortably on three shards of 2 TB NVMe each with headroom for merges.
Sustained ingest of 50,000 events per second is well inside what a single shard of this class absorbs through the Kafka engine with the block sizes shown, so the shard count is driven by query concurrency at peak and by the residency-driven fault domain, not by write throughput.
The freshness budget we design to is: producer to Kafka under 100 ms, Flink enrichment and windowing under 500 ms at p99, Kafka engine flush at two seconds, merge-independent visibility in the hot table immediately on insert, and rollup materialisation in the same insert. That gives an event-to-dashboard p99 under five seconds, which is the number the merchant panel is contracted to. The risk engine does not wait for ClickHouse at all; it reads the Flink feature topic directly, which is why it sees the feature in well under a second.
Operating it: what the runbook has to contain
A real-time payments analytics platform is only as real-time as its worst incident, so the operating model matters as much as the design. Per-country clusters mean per-country Keeper ensembles, per-country backup schedules to in-country object storage with a rehearsed restore, and a quarterly failover drill that measures RPO and RTO rather than asserting them.
The alerting specification we ship with an estate like this covers active parts per partition, merge backlog, Kafka consumer lag per partition from system.kafka_consumers, replication delay from system.replicas, Keeper latency, and the freshness p99 query above, with thresholds justified from a month of baseline data rather than from vendor defaults. The eight incidents in our CHT-400 troubleshooting catalogue are exactly the ones a payments platform at this scale should rehearse, and the diagnostic queries there are the ones we would put in the runbook.
Real-time payments analytics FAQ
Why not ClickHouse Cloud for this? Residency. At the time of writing the managed service does not have regions in every country where raw payment data must stay, and a per-country self-managed estate is the only way to satisfy all six regulators with one architecture. Where a country is served by a compliant region, Cloud is a reasonable choice for that country’s cluster and the design above does not change.
Could Kafka Connect replace the Kafka table engine for real-time payments analytics? Yes, the ClickHouse Kafka Connect sink with exactly-once semantics is the alternative, and we use it where the team already operates Connect at scale. The table engine keeps the moving parts inside ClickHouse; the sink keeps them inside Kafka. Both need the same batching discipline.
Where does the fraud model run? Training on Iceberg with Spark; features online from Flink; scoring in the risk service. ClickHouse holds the labelled history and the feature snapshots for analysis, and the 26.x line’s ai functions are not on the critical path.
What about Apache Pinot or Druid for the serving tier only? Adding a second store for serving means a second copy of the hot data, a second ingestion path and a second failure domain. The rollup and cache pattern above achieves the same p99 from ClickHouse alone; we would only add a serving store if a workload needed sustained sub-100-millisecond point lookups at extreme concurrency, which merchant dashboards do not.
How do you handle reversals and disputes in the rollups? As events, not as updates. A reversal is an event of type reversed with a negative amount that flows through the same materialised views, so the rollups net out without mutations. Lightweight UPDATE exists in 26.x but has no place on the hot path of a ledger-derived store.
Where I land
Real-time payments analytics at Southeast Asian scale is not a ClickHouse problem or a Kafka problem; it is an ingestion-discipline and residency problem with a very good columnar store in the middle. Get the event contract right in Kafka, let Flink carry the stream-shaped work, treat the ClickHouse schema and its parts budget as a product, serve peaks from rollups and caches rather than from the base table, keep raw data in country and aggregates in the region, and archive to Iceberg with a reconcile step you would be happy to show an auditor. That is the model, and every piece of it is open source.
The standing caveat applies with force here: nothing in this post has been tested on your workload, your peak or your regulators. Prototype it on a lab cluster with a replay of your own event stream, measure the freshness and p99 numbers before you promise them to anyone, keep a verified backup and a rehearsed restore in every country, and treat DR as part of the architecture rather than an afterthought. If you would like a second pair of eyes on the design, that is what the ChistaDATA consulting team does.
Sources: ClickHouse Kafka engine, ClickHouse Iceberg engine, ReplicatedMergeTree, Flink SQL windows, Debezium PostgreSQL connector, Iceberg Spark configuration.
