💼 Business context

Equity Stream Bank's fraud operations team needs to action on flagged transactions within 30 seconds of a transaction occurring — before the customer leaves the merchant. Their ML model runs inline with the payment gateway and produces a fraud score for every transaction. Transactions with fraud_score > 0.7 are written to a Kafka topic for downstream handling.

The data engineering team owns what happens after Kafka: the fraud signals must be stored in Cassandra for instant point lookups by the ops team (when a customer calls in), and simultaneously indexed in OpenSearch so the analytics team can build dashboards showing fraud patterns by geography, time, and transaction type.

What you will build

A Spark Structured Streaming job that reads the transactions Kafka topic at 100 events/second, filters for fraud signals, writes to Cassandra via foreachBatch for sub-second lookup, and simultaneously bulk-indexes into OpenSearch for analytical queries. End-to-end latency target: under 5 seconds from Kafka produce to Cassandra commit.

🏗️ Architecture

This pipeline fans out from a single Kafka source to two sinks with very different access patterns — Cassandra for operational point lookups (low latency, high write throughput) and OpenSearch for analytical range queries (full-text search, aggregations, dashboards).

Equity Stream Bank — Real-Time Fraud Detection Pipeline

ML Gateway
Scores every txn
100 events/sec
→
Kafka Topic
"transactions"
3 partitions
→
Spark Streaming
filter is_fraud
foreachBatch
→
Cassandra
fraud_signals
ops lookups
OpenSearch
fraud-events-*
analyst dashboards
1
Source — Kafka topic "transactions"
Python producer sends 100 events/sec. Each event is a JSON object with txn_id, user_id, amount, currency, is_fraud boolean, fraud_score float (0–1), and ISO timestamp.
2
Processing — Spark Structured Streaming
readStream from Kafka. Parse JSON schema. withWatermark("ts", "30 seconds") for late data. filter is_fraud == True. foreachBatch dispatches to both sinks simultaneously.
3a
Sink A — Cassandra (fraud_signals table)
Idempotent INSERT with IF NOT EXISTS. Primary key: txn_id (unique per transaction). Ops team queries: SELECT * FROM fraud_signals WHERE txn_id = '...' — <2ms.
3b
Sink B — OpenSearch (fraud-events-* index)
Bulk index via opensearch-py. Index pattern: fraud-events-YYYY-MM. Analyst queries: aggregations by hour, currency, fraud_score range. Kibana dashboards refresh every 30 seconds.
4
Checkpoint — ./checkpoints/ directory
Spark writes offset and state to the checkpoint dir after each micro-batch. On restart, the job resumes from the last committed Kafka offset — no reprocessing, no data loss.

🌊 Step 1 — Kafka producer at 100 events/sec

The producer simulates the ML gateway output. It produces JSON events to the transactions topic, with approximately 8% of events flagged as fraud (matching typical bank fraud rates). The event schema is fixed — any consumer relying on this schema must not change without coordinating schema evolution.

producer.py Step 1 · Kafka fraud event producer — 100 events/sec
sample output
Producing to topic 'transactions' at ~100 events/sec. Ctrl+C to stop. Produced 100 events | last fraud_score=0.8312 Produced 200 events | last fraud_score=0.2047 Produced 300 events | last fraud_score=0.9561 Produced 400 events | last fraud_score=0.7823 ^C Stopped. Total produced: 412 events.

⚡ Step 2 — Spark Structured Streaming job

The Spark job reads from Kafka, parses the JSON payload, applies a watermark for late data handling, filters for fraud signals, and uses foreachBatch to write to both sinks in a single micro-batch pass.

streaming_job.py Step 2 · Spark Structured Streaming — Kafka to dual sinks
streaming console output
Streaming query started. Spark UI: http://localhost:4040 Awaiting termination... (Ctrl+C for graceful shutdown) Batch 0: 38 fraud signals → writing to sinks. Cassandra: 38 rows written | OpenSearch: 38 indexed, 0 errors Batch 1: 41 fraud signals → writing to sinks. Cassandra: 41 rows written | OpenSearch: 41 indexed, 0 errors Batch 2: 36 fraud signals → writing to sinks. Cassandra: 36 rows written | OpenSearch: 36 indexed, 0 errors

🗄️ Step 3 — Cassandra DDL & idempotent writes

Cassandra is chosen for the ops sink because it delivers single-digit millisecond reads by primary key — the exact access pattern the fraud ops team needs when a customer calls in. The table is modelled for the primary query: lookup by txn_id.

cassandra_ddl.cql Step 3 · Cassandra keyspace + table DDL

🔍 Step 4 — OpenSearch index mapping

Create the index template before the first Spark micro-batch lands. The template applies automatically to any index matching fraud-events-*. Explicit field mappings prevent OpenSearch from auto-detecting fraud_score as long (integer) instead of float.

opensearch_mapping.sh Step 4 · Create OpenSearch index template

✅ Step 5 — Verify both sinks

After the first micro-batch commits, verify data in both Cassandra and OpenSearch. These commands serve as smoke tests you can add to your CI/CD pipeline.

verify.sh Step 5 · Cassandra cqlsh + OpenSearch curl verification
expected verification output
-- Cassandra count -- count ------- 412 -- OpenSearch count -- {"count": 412, "_shards": {"total": 3, "successful": 3, "skipped": 0, "failed": 0}} -- OpenSearch by_currency aggregation -- { "aggregations": { "by_currency": { "buckets": [ {"key": "KES", "doc_count": 192, "avg_fraud_score": {"value": 0.842}, "total_amount": {"value": 8417295.60}}, {"key": "USD", "doc_count": 128, "avg_fraud_score": {"value": 0.839}, "total_amount": {"value": 1127043.20}}, {"key": "TZS", "doc_count": 57, "avg_fraud_score": {"value": 0.835}, "total_amount": {"value": 4283917.80}}, {"key": "UGX", "doc_count": 35, "avg_fraud_score": {"value": 0.841}, "total_amount": {"value": 3102884.50}} ] } } }
Monitoring — Spark UI at localhost:4040: Watch three metrics on the Streaming tab: Input Rate (should track ~100 events/sec from the producer), Processing Rate (should be > input rate — you have headroom), and Batch Duration (should stay well under the 5-second trigger interval). If batch duration approaches 5 seconds, scale up executors or increase maxOffsetsPerTrigger.

⏱ Watermarking & checkpointing

Two Spark Structured Streaming features ensure correctness under failure and late data.

Watermarking for late data

Mobile transactions in East Africa are sometimes delayed — a transaction initiated at 14:30 might reach Kafka at 14:31 due to intermittent connectivity. The 30-second watermark instructs Spark to keep late-arriving events in state for up to 30 seconds past the current event-time watermark before dropping them.

watermark_window.py Watermark + sliding window — fraud rate per user

Checkpoint location

The checkpoint directory records two things: the last committed Kafka offsets, and any stateful aggregation state (like the windowed fraud rate above). On restart, Spark reads the checkpoint and resumes exactly where it left off — no events are lost or reprocessed.

Production checkpoint location

In production, store checkpoints on a durable, distributed filesystem — S3 (s3a://your-bucket/checkpoints/fraud_pipeline) or HDFS (hdfs://namenode:9000/checkpoints/fraud_pipeline). Local disk checkpoints are lost if the worker node is replaced. An S3 checkpoint adds <50ms per micro-batch for the metadata write — a worthwhile trade for durability.

🔐 Production considerations

Before going to production — security checklist

  • Kafka SSL/SASL: configure security.protocol=SASL_SSL, sasl.mechanism=SCRAM-SHA-512 in Spark's Kafka options. Never run Kafka without auth in a bank environment.
  • Cassandra authentication: enable authenticator: PasswordAuthenticator in cassandra.yaml. Create a dedicated service account with WRITE-only permissions on the fraud_detection keyspace.
  • OpenSearch security: enable the security plugin. Create a service account with index:data/write/* permissions on fraud-events-* and index:admin/create on the same pattern for auto-index creation.
  • Checkpoint on S3: replace ./checkpoints/ with s3a://equitystream-spark-checkpoints/fraud_pipeline/. Configure IAM role or access keys via Hadoop configuration in the SparkSession.
  • Secrets management: store Cassandra and OpenSearch credentials in Vault or AWS Secrets Manager. Retrieve at runtime — never hard-code credentials in job code or DAG config.
  • Monitoring: expose query.lastProgress metrics to Prometheus via the Spark metrics sink. Alert when batchDuration > 80% of trigger interval or numInputRows drops to zero (producer failure).
monitoring_snippet.py Spark UI metrics — check from Python
monitoring output — healthy pipeline
[2026-04-27T09:15:05Z] batchId=42 | inputRows=487 | inputRate=97.4 rows/s | processRate=312.8 rows/s | batchDuration=1,562ms [2026-04-27T09:15:35Z] batchId=43 | inputRows=501 | inputRate=100.2 rows/s | processRate=298.5 rows/s | batchDuration=1,679ms

🏆 Challenge tasks

Extend the capstone
Challenge 1 — Sliding window fraud rate per user

Implement the 5-minute / 1-minute sliding window aggregation from the watermarking section. Write the windowed results to a separate Cassandra table user_fraud_windows with primary key (user_id, window_start). A fraud ops analyst should be able to query: "which users had a fraud rate above 50% in any 5-minute window today?"

Hint: you need outputMode("update") for windowed aggregations with watermarks, not "append". Append mode does not emit updated window results.

Challenge 2 — Slack alert for extreme fraud scores

Inside write_batch(), add a check: if any row in the batch has fraud_score > 0.98, send a Slack alert via webhook.

import requests
SLACK_WEBHOOK = "https://hooks.slack.com/services/T.../B.../..."
high_risk = [r for r in rows if r.fraud_score > 0.98]
if high_risk:
    requests.post(SLACK_WEBHOOK, json={
        "text": f":rotating_light: {len(high_risk)} extreme fraud signals (score > 0.98) in batch {batch_id}. "
                f"Top: txn_id={high_risk[0].txn_id}, score={high_risk[0].fraud_score:.4f}"
    })

🧠 Quiz — 4 questions

Question 1 of 4
Why is foreachBatch used to write to Cassandra and OpenSearch instead of the built-in Kafka or console sinks?
Question 2 of 4
The Cassandra INSERT uses IF NOT EXISTS. What does this ensure in the context of Spark Structured Streaming?
Question 3 of 4
What happens to the streaming job if the checkpoint directory is deleted and the job is restarted?
Question 4 of 4
Why does the sliding window aggregation use outputMode("update") instead of outputMode("append")?
0
/ 4
Streaming pipeline mastered!

You have completed the Big Data Engineering track. Congratulations!

🎤 Interview Q&A

Medium Why Cassandra for the operational sink and OpenSearch for the analytical sink? Why not just use one?
+
Cassandra and OpenSearch are optimised for fundamentally different access patterns. Cassandra excels at high-throughput writes and sub-millisecond point reads by primary key — the ops team query "give me fraud_signals WHERE txn_id = X" is Cassandra's core strength and returns in under 2ms regardless of table size. Cassandra does not support full-text search, range queries on non-primary-key columns, or aggregations efficiently. OpenSearch excels at exactly those workloads: range queries on fraud_score, date histogram aggregations, full-text search on any field, and dashboard visualisations — but it has higher write latency than Cassandra and does not guarantee sub-millisecond point reads. Using both is the polyglot persistence pattern: write to each system in the same micro-batch via foreachBatch, and each consumer team queries the system that fits their access pattern. The data is the same; the access patterns are different.
Hard How does Spark Structured Streaming guarantee that a micro-batch is processed at least once, and what do you do to make it exactly-once end-to-end?
+
Spark Structured Streaming provides at-least-once processing by default. Each micro-batch reads a range of Kafka offsets, processes them, and then commits the offsets to the checkpoint directory. If the Spark driver crashes after processing but before committing the checkpoint, the same offset range is replayed on restart — producing duplicate side effects. To achieve effectively exactly-once end-to-end, the sinks must be idempotent. In this pipeline: Cassandra's INSERT IF NOT EXISTS keyed on txn_id ensures that replaying a batch does not create duplicate rows — the second insert is a lightweight no-op. OpenSearch's bulk index with an explicit document _id set to txn_id also ensures idempotency — indexing the same document twice with the same _id overwrites the first, not appends. So the pipeline achieves effectively exactly-once semantics through at-least-once delivery from Spark plus idempotent writes to both sinks. True EOS (Spark's EXACTLY_ONCE trigger) is available but requires a transactional sink that Cassandra and OpenSearch do not support natively.
Easy What is the purpose of withWatermark in Spark Structured Streaming?
+
withWatermark("ts", "30 seconds") tells Spark how long to retain state for late-arriving events. Without a watermark, Spark would keep state for every window forever — which would cause unbounded memory growth for long-running streaming jobs. With withWatermark("ts", "30 seconds"), Spark tracks the maximum event time seen so far in the stream and drops any event whose ts is more than 30 seconds behind that maximum. This means: events that arrive up to 30 seconds late are still counted in the correct window; events that arrive more than 30 seconds late are silently dropped. The watermark also signals to Spark when it is safe to finalise and emit window results — a window result is emitted once the watermark advances past the window's end time, meaning no more late events can fall into it. Choosing the watermark delay requires understanding your data: mobile transactions in Africa with intermittent connectivity might need a 30-second to 2-minute watermark, while a bank's in-datacenter payment gateway might need only 5 seconds.

✅ Key takeaways

  • Spark Structured Streaming's foreachBatch API is the production pattern for writing to systems that do not have a native Spark sink — it gives you a full Spark DataFrame and the Python ecosystem per micro-batch, enabling fan-out writes to Cassandra and OpenSearch in a single pass.
  • Idempotent writes are the key to safety in streaming pipelines. Cassandra's IF NOT EXISTS and OpenSearch's explicit document _id both ensure that Spark batch retries (which happen on any failure before checkpoint commit) do not create duplicates — achieving effective exactly-once semantics without transactional overhead.
  • The watermark .withWatermark("ts", "30 seconds") bounds the state Spark keeps in memory and defines how long to wait for late events. Choose the watermark delay based on your network characteristics — too short drops legitimate late events; too long grows state memory indefinitely.
  • Checkpoints are not optional in production streaming. Store them on a durable distributed filesystem (S3 or HDFS) so the job can survive driver restarts without replaying from the beginning of the Kafka topic or losing windowed state.
  • The polyglot persistence pattern — Cassandra for operational point reads, OpenSearch for analytical range queries and dashboards — is the correct architecture when two consumer teams have fundamentally different access patterns. Writing to both in the same micro-batch adds negligible latency versus the alternative of two separate streaming jobs.
  • Monitor three streaming metrics proactively: input rate (tracks producer health), processing rate (must exceed input rate to stay stable), and batch duration (must stay below the trigger interval). When batch duration approaches the trigger interval, the job is falling behind and will begin accumulating lag — scale up executors before the lag becomes unrecoverable.
🎉

Track Complete!

You have completed all 35 lessons of the Big Data Engineering with Spark & Kafka track at Ramayai AI Academy. From HDFS fundamentals to real-time fraud detection — you have built the full stack.

35
Lessons completed
7
Arcs mastered
14+
Technologies covered
✓ Lesson 1 — Big Data Fundamentals
✓ Lesson 2 — HDFS Architecture
✓ Lesson 3 — MapReduce & YARN
✓ Lesson 4 — Spark Architecture
✓ Lesson 5 — RDDs & DataFrames
✓ Lesson 6 — Spark SQL Deep Dive
✓ Lesson 7 — Joins & Shuffles
✓ Lesson 8 — Partitioning Strategy
✓ Lesson 9 — Parquet & ORC
✓ Lesson 10 — Data Lake Design
✓ Lesson 11 — Delta Lake Basics
✓ Lesson 12 — Spark Tuning
✓ Lesson 13 — Kafka Fundamentals
✓ Lesson 14 — Kafka Topics & Partitions
✓ Lesson 15 — Producers & Consumers
✓ Lesson 16 — Kafka Connect
✓ Lesson 17 — Kafka Streams
✓ Lesson 18 — Consumer Groups
✓ Lesson 19 — Delivery Guarantees
✓ Lesson 20 — Clickstream Pipeline
✓ Lesson 21 — Cassandra Data Model
✓ Lesson 22 — Cassandra Operations
✓ Lesson 23 — OpenSearch Indexing
✓ Lesson 24 — OpenSearch Aggregations
✓ Lesson 25 — Spark Structured Streaming
✓ Lesson 26 — Streaming Joins
✓ Lesson 27 — Airflow Fundamentals
✓ Lesson 28 — Airflow DAG Patterns
✓ Lesson 29 — dbt Fundamentals
✓ Lesson 30 — dbt Advanced Patterns
✓ Lesson 31 — Data Quality
✓ Lesson 32 — Data Lineage
✓ Lesson 33 — Quality & Lineage Overview
✓ Lesson 34 — Capstone: Batch Pipeline
✓ Lesson 35 — Capstone: Streaming Pipeline
View Track Index →
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

You finished the track. Now build it in production.

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 →