# Real-Time Clickstream Analytics with Kafka, Spark and Delta Lake

> A planned streaming pipeline: website events flow through Kafka into Spark Structured Streaming, land as Delta tables with exactly-once guarantees, and feed a live sessions dashboard — with late data, replays and schema changes handled deliberately.

- Source: https://akshaythakare.com/ideas/realtime-clickstream-analytics
- Stack: Python, Kafka, Apache Spark, Delta Lake, Docker, PostgreSQL, Apache Airflow
- Tags: streaming, kafka, spark, delta-lake, blueprint
- Updated: 2026-10-11

## Overview & goal

## The problem

An e-commerce site wants to see what shoppers are doing **now**, not tomorrow morning: how many people are active, where they drop out of the funnel, and how long sessions last. A nightly batch job answers these questions a day late.

## What this build will deliver

- A stream of website events flowing through **Kafka**.
- **Spark Structured Streaming** jobs that clean, deduplicate and sessionize events.
- **Delta Lake** tables in bronze, silver and gold layers with exactly-once writes.
- A dashboard that refreshes every minute.

> **Note: Blueprint status**
>
> This is a plan. Every component in the architecture explorer is marked **Planned**. When the build is done, results, numbers and code will replace these plans — nothing here should be read as finished work.

## Why this project

Streaming adds problems batch never sees: events that arrive late or twice, consumers that restart mid-stream, and schemas that change while the pipeline runs. This build is designed to face each of those on purpose.

## Scope

| In scope | Out of scope |
| --- | --- |
| Local stack in Docker Compose | Managed cloud deployment |
| Exactly-once writes to Delta | Multi-region replication |
| Late data, duplicates, replays | Machine-learning features |

## Architecture

## Data flow

```mermaid
flowchart TB
  accTitle: Clickstream pipeline architecture
  accDescr: Events go from a generator into Kafka, through bronze, silver and gold Delta tables via Spark Structured Streaming, then to a dashboard; Airflow runs maintenance on the tables.
  G[Event generator] --> K[(Kafka: clickstream.events)]
  K --> B[Bronze: raw events + offsets]
  B --> S[Silver: parsed, validated, deduplicated]
  S --> GD[Gold: sessions and funnel]
  GD --> D[Live dashboard]
  A[Airflow: optimize, quality, replay] -.-> GD
```

## Key design decisions

**Partition Kafka by `user_id`.** Events for one user stay in order, which sessionization needs.

**Bronze stores raw events with their Kafka offsets.** If parsing logic has a bug, silver can be rebuilt from bronze without re-reading Kafka.

**Deduplicate with a watermark.** `dropDuplicates` on `event_id` within a watermark bounds the state Spark keeps; events later than the watermark go to a late-events table instead of disappearing.

**Write gold with `foreachBatch` and MERGE.** Each micro-batch upserts session rows, so reprocessing a batch replaces rather than duplicates.

**Checkpoints are part of the contract.** Each stream has its own checkpoint location; deleting one is treated as a deliberate replay, never a fix.

## Build plan

### Local stack
Docker Compose with Kafka, Spark and a Delta-enabled Spark session; a smoke test produces and reads one event.
  
### Event generator
Python producer with a fixed random seed so test runs are reproducible; flags to inject late, duplicate and malformed events.
  
### Bronze stream
Read Kafka, write raw payload, topic, partition and offset to Delta with a checkpoint.
  
### Silver stream
Parse JSON against an explicit schema, quarantine failures, deduplicate by event_id within a 10-minute watermark.
  
### Gold sessions
Session windows with a 30-minute gap; MERGE session metrics in foreachBatch.
  
### Dashboard
Query gold tables every minute: active users, funnel steps, median session length.
  
### Operations
Airflow DAGs for OPTIMIZE/VACUUM, freshness and volume checks, and a replay from bronze.

## Milestone order

Build bronze end to end before writing any transformation: a working raw layer means later bugs never cost lost data.

## Acceptance criteria

The build counts as done only when each check below passes and the result is recorded here.

| Check | How it is tested | Pass condition |
| --- | --- | --- |
| Exactly-once | Kill the silver job mid-batch and restart | No duplicate event_id in silver |
| Duplicates | Generator sends 5% duplicate events | Silver row count equals unique events |
| Late data | Send events 20 minutes late | They land in the late-events table, not silently dropped |
| Replay | Rebuild silver from bronze | Same row counts and checksums as before |
| Schema change | Add an optional field to events | Pipeline keeps running; new field appears in silver |
| Freshness | Dashboard query | Data no older than 2 minutes under normal load |

## Measurements to record

- End-to-end latency (event time to dashboard), median and 95th percentile.
- Throughput the local stack sustains before latency grows.
- State size of the deduplication and session operators.

## Interview angles

Building this prepares answers to common streaming questions with first-hand evidence:

- **How do you get exactly-once results with Kafka and Spark?** Checkpointed offsets plus idempotent sinks (Delta transactions, MERGE).
- **What is a watermark and what does it cost?** It bounds state for deduplication and windows; events later than it are handled separately.
- **Why partition by user_id?** Ordering per key for sessionization; trade-off is skew if a few users are very active.
- **How do you reprocess after a bug?** Rebuild downstream layers from bronze, which keeps raw events and offsets.

Related reading: [Batch vs streaming](/notes/system-design/batch-vs-streaming), [Delta Lake ACID tables](/notes/databricks/delta-lake-acid-tables), [Spark partitions and file sizes](/notes/apache-spark/partitioning-and-file-sizes).