What we're building
This mini-build wires together two systems you have studied separately: Kafka as the ingest bus and Cassandra as the write-optimised datastore. The use case is a payments pipeline for a fintech — high-volume, write-heavy, latency-sensitive.
By the end of this lesson you will have: a Kafka producer generating realistic payment events, a Python consumer that writes them to Cassandra using prepared statements, a CQL schema designed for write-heavy workloads, and idempotency enforced via Cassandra's Lightweight Transactions (LWT).
Local environment with Docker Compose
Start Kafka and Cassandra together with a single command. This compose file includes Zookeeper, a single Kafka broker, and a single Cassandra node — enough to validate the full pipeline locally before scaling to a real cluster.
CQL schema — design for write-heavy workloads
The schema is the most important decision in a Cassandra project. The partition key determines which node holds the data. The clustering key determines the sort order within a partition. A bad choice here causes hot partitions, which destroy throughput.
Partition key choice: user_id, not payment_id
Beginners often use payment_id as the partition key because it is unique. This is wrong for a read use case: every query for "all payments by user u-99" would have to scatter across every node. Instead, use user_id as the partition key so all payments for a user are co-located on the same node, and payment_ts as the clustering key so they are sorted by time within the partition.
TWCS groups SSTables by time window (here: 1 day). Once a window is closed, its SSTables are compacted into one and never rewritten again — perfect for append-only time-series workloads. LeveledCompactionStrategy (LCS) would cause excessive read amplification and write amplification on time-ordered inserts.
| Design choice | Rationale |
|---|---|
user_id as partition key |
All payments for one user are co-located — efficient range reads by user |
payment_ts DESC clustering |
Most recent payments come first without a client-side sort |
default_time_to_live = 7776000 |
Cassandra automatically tombstones rows after 90 days — no manual purge job needed |
| TWCS compaction | Optimal for time-ordered inserts; avoids rewriting cold SSTables |
payment_id in clustering key |
Makes the primary key unique even if two payments land at the same millisecond |
Step 1 — Kafka producer
The producer sends realistic payment events at a configurable rate. Each event includes a unique payment_id, a user_id (mapped to a Kafka partition so all events for one user are ordered), an amount, currency, and ISO 8601 timestamp.
Step 2 — Cassandra consumer
The consumer reads from Kafka, prepares a single INSERT statement at startup (so the Cassandra cluster only parses it once), and writes each event using that prepared statement. Manual offset commit ensures at-least-once delivery, and LWT (IF NOT EXISTS) on the primary key makes writes idempotent.
Health check — verify the pipeline
After the producer and consumer have run for a minute, open cqlsh and verify that rows are landing in the table. Also check partition distribution to catch hot partitions early.
Restart the consumer while the producer is running. Some messages will be re-processed (at-least-once). Because the INSERT uses IF NOT EXISTS, the second write to the same (user_id, payment_ts, payment_id) primary key is a no-op — Cassandra returns [applied] = false but does not create a duplicate row. Run SELECT COUNT(*) FROM payments.events before and after the restart; the count should not increase for already-processed payment IDs.
Throughput — what to expect
With the default consumer config (LOCAL_QUORUM, IF NOT EXISTS, no batching), expect ~3 000–5 000 writes/sec on a single Cassandra node. Removing IF NOT EXISTS (LWT) and switching to LOCAL_ONE consistency brings this to ~15 000–20 000 writes/sec per node. On a 3-node cluster with those settings, ~50 000 writes/sec is achievable on commodity hardware.
| Configuration | Single node write/sec | Notes |
|---|---|---|
| LOCAL_QUORUM + IF NOT EXISTS (LWT) | ~3 000–5 000 | Most durable; LWT adds a Paxos round-trip |
| LOCAL_QUORUM, no LWT | ~10 000–15 000 | Idempotency via consumer logic (upsert semantics) |
| LOCAL_ONE, no LWT, UNLOGGED BATCH | ~20 000–25 000 | Max throughput; lowest durability guarantee |
| 3-node cluster, LOCAL_QUORUM, no LWT | ~50 000 (aggregate) | Production target for a write-heavy payments pipeline |
payments.events, you can run 6 consumer processes in parallel — each handling its own partition subset, writing to Cassandra concurrently.
Common pitfalls
payment_id as the only partition key creates uniform distribution but makes every "get all payments for user X" query a full cluster scan. Using user_id can create hot partitions if a small number of users generate most traffic. Mitigate by bucketing: PRIMARY KEY ((user_id, bucket), payment_ts) where bucket = payment_ts.strftime('%Y-%m').user_id and no time component will grow forever as payments accumulate. Cassandra recommends partitions stay under 100 MB. Add a date or month bucket to the partition key, or set default_time_to_live so old rows are automatically purged.default_time_to_live at the table level (as shown in the DDL above) or pass a TTL on each INSERT: INSERT … USING TTL 7776000.IF NOT EXISTS uses Cassandra's Paxos protocol — roughly 4× more expensive than a regular write. For truly high-throughput pipelines, implement idempotency at the application layer (check for duplicates in Redis before writing) instead of paying the LWT tax on every message.Challenge — extend the pipeline
In cassandra_consumer.py, the except block currently just logs the error. Extend it to produce the failed message to a payments.events.dlq dead-letter topic using a separate confluent_kafka.Producer instance. Include the original message value, the partition, offset, and the exception message as metadata. Add a separate DLQ consumer that reads the dead-letter topic and alerts an on-call channel (print to stdout is fine for the exercise).
Hint: initialise the DLQ producer at startup alongside the Cassandra session. Never block the main write loop waiting for DLQ delivery.
The current schema sets a table-level default TTL of 90 days. Extend the schema to support different retention windows based on currency: KES and UGX events expire after 30 days, USD events are retained for 365 days. Update the prepared statement to pass a per-row TTL instead of relying on the table default. You will need to change the INSERT to INSERT … USING TTL ? and add the TTL as the last bind parameter.
Hint: use Python to compute the TTL in seconds based on event["currency"] before executing the prepared statement.
Quiz — 4 questions
INSERT … IF NOT EXISTS in Cassandra?enable.auto.commit=False and commits manually after every 500 messages. What delivery guarantee does this provide?Next: OpenSearch Fundamentals (Lesson 27).
Interview Q&A
Start by identifying the primary read pattern — "all payments for a user, sorted by time" — and design the primary key around that. The partition key should be user_id so all of a user's payments are on one node. The clustering key should be payment_ts DESC so recent payments come first. Include payment_id in the clustering key to ensure uniqueness at the millisecond level.
For write-heavy workloads, choose TimeWindowCompactionStrategy with a 1-day window — it groups SSTables by time, compacts closed windows into a single file, and never rewrites cold data. Set default_time_to_live at the table level to automatically purge old data and prevent unbounded partition growth. Use LOCAL_QUORUM for durability, and avoid LWT (IF NOT EXISTS) unless you truly need Paxos-level idempotency guarantees, because LWT reduces write throughput by roughly 4×.
A hot partition occurs when a single partition key receives a disproportionate share of reads or writes, causing one Cassandra node (or set of replica nodes) to become a bottleneck while others sit idle. This defeats Cassandra's horizontal scaling model.
Common causes: using a low-cardinality partition key (e.g., currency with only 3 values), or having a Pareto distribution of activity where a few users generate most traffic. Prevention strategies: (1) Add a time bucket to the partition key — PRIMARY KEY ((user_id, month), payment_ts) — so a high-volume user's data is spread across monthly partitions on different nodes. (2) Use a synthetic salt — append a random suffix to the partition key and scatter writes across N virtual partitions, then read from all N and merge client-side. (3) Monitor partition sizes with nodetool tablestats and set alerts when any partition exceeds 50 MB.
Logging a failed message writes it to a log file that nobody may be actively watching, in a format that is hard to replay automatically. A dead-letter topic (DLT) stores the failed message in Kafka — a durable, replayable, observable stream. This means: (1) an operator can subscribe to the DLT with a separate consumer, apply a fix, and re-produce messages to the original topic; (2) the failure is observable in the same monitoring stack as the main topic (Kafka consumer lag, offset tracking); (3) the original message bytes are preserved exactly, so no data is lost even if the schema evolves; (4) failed messages can be automatically retried with exponential backoff by a dedicated retry consumer.
In production, a DLT is essential for any pipeline where losing a message is unacceptable — payments, ledger events, audit logs. The DLT message should include the original topic, partition, offset, exception class, exception message, and a timestamp of failure.
The clustering key determines the sort order of rows within a single partition. All rows with the same partition key are stored together on disk, sorted by the clustering key. This means range queries on the clustering key — "payments between 2025-01-01 and 2025-01-31" — are efficient because they scan a contiguous slice of sorted data on a single node, without touching other nodes. Choosing payment_ts DESC as the clustering key means the most recent payments are physically stored first within each user partition, making "get last 10 payments for user X" a minimal-I/O operation that reads the first few rows without a sort step.
Key takeaways
- Design the Cassandra partition key around your primary read pattern, not uniqueness. For a payments pipeline,
user_idas the partition key co-locates all of a user's events on one node and enables efficient range reads. - Use TimeWindowCompactionStrategy for time-ordered event data and set
default_time_to_liveto prevent unbounded partition growth — both choices have a large impact on write performance and operational stability. - Prepared statements parse CQL once at startup and re-use the execution plan for every subsequent write, reducing per-message CPU overhead significantly at high throughput.
IF NOT EXISTS(LWT) makes Cassandra writes idempotent on the primary key but uses Paxos and costs roughly 4× the write latency. For maximum throughput, implement idempotency at the application layer and drop LWT.- At-least-once delivery (manual Kafka offset commit after processing) combined with idempotent Cassandra writes is the standard production pattern — effective exactly-once without the complexity or latency of Kafka transactions.
The foundation our AI platform engineers stand on
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →