How to Design a Data Pipeline for a National Event (System Design Guide)

Real-time ingestion, distributed aggregation, and fault-tolerant serving at national scale

You must have seen this question in data engineering interviews more times than you'd expect: design a data pipeline for a high-stakes, time-bounded national event. Sometimes it's framed as an election. Sometimes it's a census, a sporting event, a nationwide logistics operation. The framing changes. The underlying design problem doesn't.

And most candidates fail it not because they don't know Kafka, but because they jump straight to technology before understanding what they're actually building.

This guide uses India's Lok Sabha General Election as the working case study. Not for political reasons, there are none here but because it is, by raw numbers, the largest democratic exercise on the planet: roughly 970 million registered voters, 543 parliamentary constituencies, over 1 million polling stations, results counted and published within a single day. If you can reason through a system at that scale, you can reason through any national election system anywhere in the world. The engineering constraints are the same. The failure modes are the same.

By the end of this, you'll have a defensible architecture you can draw on a whiteboard, a clear answer to every follow-up question an interviewer can throw at you, and a few honest opinions about where most candidates go wrong.

Step 0: Understand the Scale Before You Touch a Single Technology

This is the step most candidates skip. They hear "election data pipeline" and immediately start naming tools. That's the wrong move.

Before you pick any technology, you need to understand what the system physically has to survive. Let me give you the numbers.

In a national election at this scale, you're looking at:

• ~970 million registered voters
• 543 geographic constituencies (think of these as your primary aggregation unit)
• 1 million+ polling stations distributed across urban centres, rural towns, and remote geographies
• Results that arrive not as a single dump but in rounds, a constituency might report 20 to 25 counting rounds before a final result is declared
• A counting window of approximately 8 to 12 hours on results day, with the bulk of updates arriving in a 4-to-6-hour burst

That last point is the design constraint most people miss. You don't have a uniform load. You have a massive spike window.

Here's something else that changes your design entirely: in most national election systems, results are not streamed automatically from voting machines. A human operator at a counting centre manually enters the result for each counting round into a form. That form submits to your pipeline. You're not receiving a machine feed, you're receiving human-entered data in near-real-time.

That distinction matters. It changes your input validation requirements, your latency guarantees, and what "real-time" actually means in this context. Real-time here means seconds-to-minutes from when an operator submits, not milliseconds from when a machine writes.

Step 1: Define Your Requirements: Functional and Non-Functional

Skipping requirements definition is the number one sign of a junior candidate in a system design interview. The interviewer is not looking for the right answer. They're looking for the right thinking process. That process starts with requirements.

↪Functional Requirements

What does the system need to do?

• Accept result updates from counting centres as they are submitted by operators
• Aggregate votes per candidate, per constituency, per region, and nationally — all in near-real-time
• Serve partial results to clients with a maximum staleness of 5 seconds
• Detect and signal a winner for a constituency once a result meets the threshold condition
• Support historical queries: compare current results with previous election data at any geographic level
• Produce an immutable audit log of every result update, with timestamps and source identifiers

↪Non-Functional Requirements

What does the system need to survive?

• Throughput: ~50,000 result update events per hour across all constituencies at peak. That sounds manageable — and it is on the write side. The read side is a different story.
• Availability: 99.99% during the counting window. No maintenance windows. No restarts.
• Consistency: Monotonically increasing. A result for round N of a constituency cannot be lower than round N-1 for the same candidate. If this invariant breaks, your aggregations are wrong.
• Durability: Zero data loss. Every submitted result must survive infrastructure failures.
• Read Scale: 50 to 100 million concurrent clients during peak hours.
• Latency: A result update must be visible to end clients within 5 seconds of ingestion.

What's Out of Scope (Say This In Your Interview)

Explicitly naming what you're not designing is an authority signal. It shows you understand system boundaries.

Out of scope: voting machine hardware, voter identity or authentication systems, ballot design, anything upstream of the counting centre. This pipeline starts where a human operator submits a result. That's your system boundary.

Step 2: High-Level Architecture: The Bird's Eye View

Before going deep on any layer, give your interviewer the full picture. This is the sketch-on-the-whiteboard moment.

The architecture has five layers. Every decision you make for the rest of the design lives inside one of them.

The key insight driving this design is the separation of write path from read path. They have completely different requirements. The write path demands durability and consistency. The read path demands throughput and low latency. Trying to serve both from a single database is where most architectures fall apart under load.

The three arrows worth explaining to your interviewer:

Why Kafka between counting centres and processing?

Because operators don't wait for your aggregation to complete. The ingestion layer decouples data arrival from data processing. Kafka absorbs the burst, buffers it, and lets Flink process at its own pace.

Why a separate read cache?

Because 100 million people checking results simultaneously is not a database query problem, it's a content delivery problem. Redis serves pre-aggregated snapshots. Your write-path database doesn't see the read traffic at all.

Why a CDN for some responses?

For users who just want the national summary or a constituency's current total which is the majority of traffic, a static JSON snapshot pushed to a CDN every 30 seconds is cheaper, faster, and more reliable than a live API call at scale.

Step 3: Ingestion Layer: Getting Data In Without Losing Any

This is the most failure-prone layer and the one that deserves the most caution in your design.

Every result update from a counting centre operator needs to arrive at Kafka reliably, exactly once, in a way that's safe to replay if something goes wrong. Four decisions define how you achieve that.

Decision 1: Event-driven, not polled.

Counting centre agents push result updates to your system. Your system does not poll them. The moment you flip to polling, you've introduced unnecessary latency and you're making network calls to endpoints you don't control.

Decision 2: Idempotency keys at the event level.

Every result update carries a composite key: constituency_id + candidate_id + round_number. If a network failure causes an operator to retry their submission, your system processes the same event twice. With an idempotency key, the second event is recognised as a duplicate and discarded. Without it, you've double-counted votes in your aggregation and your entire pipeline is producing wrong results.

Decision 3: Schema validation at the entry point.

Before any event touches Kafka, it must pass validation against a defined schema. Use Avro or Protobuf with a schema registry. A malformed event — missing a constituency ID, an invalid candidate reference, a negative vote count — should be dead-lettered to a separate error topic, not silently dropped and not allowed to poison your stream.

Decision 4: Kafka topic partitioning by constituency. Partition your primary result-updates topic by constituency_id. This ensures all events for the same constituency are processed in order by the same Flink operator — which is a requirement for your monotonic consistency invariant.

Here's the wrong pattern and the correct pattern side by side. This is the code example that belongs in your article and in your understanding.

Wrong Pattern: Fire and Forget

# WRONG — no idempotency, no schema validation, no acknowledgment
import requests

def submit_result(constituency_id, candidate_id, round_num, votes):
    payload = {
        "constituency": constituency_id,
        "candidate": candidate_id,
        "round": round_num,
        "votes": votes
    }
    # If this request fails mid-flight, you have no idea if it was received.
    # If the operator retries, you've sent a duplicate event with no way to detect it.
    # If the payload is malformed, it silently enters your pipeline.
    requests.post("https://ingestion.pipeline/results", json=payload)

This is what most junior candidates would write. It works in development. It fails in production.

Correct Pattern: Idempotent Kafka Producer with Schema Validation

# CORRECT — idempotent producer, schema-validated event, explicit acknowledgment
from confluent_kafka import Producer
from confluent_kafka.schema_registry import SchemaRegistryClient
from confluent_kafka.schema_registry.avro import AvroSerializer
import hashlib, json

SCHEMA_STR = """
{
  "type": "record",
  "name": "ResultUpdate",
  "fields": [
    {"name": "idempotency_key", "type": "string"},
    {"name": "constituency_id", "type": "string"},
    {"name": "candidate_id",    "type": "string"},
    {"name": "round_number",    "type": "int"},
    {"name": "votes_this_round","type": "long"},
    {"name": "submitted_at",    "type": "long"}
  ]
}
"""

def build_idempotency_key(constituency_id, candidate_id, round_number):
    # Deterministic key — same input always produces the same key.
    # Safe to retry. Safe to replay from Kafka.
    raw = f"{constituency_id}:{candidate_id}:{round_number}"
    return hashlib.sha256(raw.encode()).hexdigest()

def submit_result(producer, serializer, constituency_id,
                  candidate_id, round_num, votes, submitted_at):
    event = {
        "idempotency_key": build_idempotency_key(
                               constituency_id, candidate_id, round_num),
        "constituency_id":  constituency_id,
        "candidate_id":     candidate_id,
        "round_number":     round_num,
        "votes_this_round": votes,
        "submitted_at":     submitted_at
    }

    def delivery_report(err, msg):
        if err:
            # Log and surface to the operator — don't silently swallow failures.
            print(f"[ERROR] Delivery failed for {event['idempotency_key']}: {err}")
        else:
            print(f"[OK] Event delivered to partition {msg.partition()}")

    producer.produce(
        topic="election.result-updates",
        key=constituency_id,          # Partition by constituency — ordering guaranteed
        value=serializer(event, ...),  # Avro-serialized, schema-validated
        on_delivery=delivery_report
    )
    producer.flush()  # Block until acknowledgment — durability confirmed

Three things the correct pattern does that the wrong one doesn't: it confirms delivery, it makes retries safe, and it rejects bad data before it reaches your stream processor.

Step 4: Stream Processing Layer: Turning Raw Events Into Meaningful Aggregations

Raw events in Kafka are just numbers. Flink turns them into something your end users can read.

Why Flink Over Spark Structured Streaming

Take a position here in your interview. Don't say "it depends" and list both. Say which one you'd pick and why.

I'd pick Apache Flink. Spark Structured Streaming is micro-batch by design — it processes data in small intervals. Flink is genuinely event-driven. For stateful aggregations where you need per-constituency running totals updating continuously, Flink's native stream processing model is the right tool. Spark is the right tool when your latency requirements are looser and your team knows Spark deeply. For this problem, Flink wins.

What Flink Is Actually Doing

Three operations, in order:

1. Deduplication using the idempotency key. Even with Kafka's exactly-once delivery guarantees, failures and replays can surface duplicate events at the consumer level. Flink checks the idempotency key against a keyed state store. Seen it before — discard. Haven't seen it — process.

2. Stateful aggregation per constituency. Flink maintains a running tally for every candidate in every constituency inside RocksDB (Flink's embedded state backend). As each round event arrives, Flink updates the cumulative vote count. Critically, it also validates the monotonic invariant: if the new cumulative total is lower than the previous one for the same candidate, that event is flagged as anomalous and routed to a dead-letter stream.

3. Winner detection as a stream trigger. You don't want to run a batch job to detect winners. You want it to happen the moment the condition is met. Flink checks after every state update: if a candidate holds more than 50% of total votes counted and less than 5% of expected votes remain, emit a winner event. This event flows downstream to the API layer, which can push a notification to clients in real time.

Out-of-Order Events and Watermarking

Counting centres in different locations submit results at different times. Round 4 from one centre might arrive after round 5 from another, not because of any logic failure, but because of network variability. Flink handles this with event-time watermarks.

A 60-second watermark means Flink waits up to 60 seconds for late-arriving events before finalising an aggregation window. Events arriving more than 60 seconds late are routed to a side output for manual review. This is a tradeoff, you're accepting a maximum of 60 seconds of aggregation delay in exchange for handling reasonable network latency. For a counting window that spans hours, that's a good tradeoff.

Step 5: Storage Layer: The Right Database for Each Job

There is no single database that wins here. The write path and the read path have fundamentally different requirements. Design them separately.

Write Path: Apache Cassandra

Cassandra is the right choice for storing the result records that Flink produces. It's built for write-heavy, distributed workloads where you can define your access patterns upfront. Here's the data model you'd use:

Table: constituency_results

constituency_id   → partition key
candidate_id      → clustering key (part 1)
round_number      → clustering key (part 2)
votes_this_round  BIGINT
cumulative_votes  BIGINT
submitted_at      TIMESTAMP
is_final          BOOLEAN
is_winner         BOOLEAN

Partitioning by constituency_id means all data for a constituency lives on the same set of nodes. Querying "give me all rounds for constituency X" is a single partition read — fast, predictable, cheap.

Write consistency level: LOCAL_QUORUM. This guarantees that a write is confirmed by a majority of replicas in the local region before the producer gets an acknowledgment. You're trading write latency (slightly slower) for durability (no silent single-node writes).

Read Path: Redis Cache

Cassandra handles 50,000 writes per hour without breaking a sweat. But it was not designed to serve 100 million concurrent reads per hour for pre-aggregated summaries. That's what Redis is for.

Flink writes pre-aggregated snapshots to Redis every 5 seconds:

Key:   results:constituency:{constituency_id}
Value: {"candidate_a": 142500, "candidate_b": 138200, ...}
TTL:   10 seconds

Key:   results:national:summary
Value: {"party_a_leading": 271, "party_b_leading": 189, ...}
TTL:   10 seconds

A cache miss falls back to a Cassandra read — which is fine, because cache misses during normal operation should be rare. The TTL is slightly longer than the update interval to handle any write lag from Flink.

Cold Storage: A Data Warehouse for Historical and Analytical Queries

After the counting window closes, a batch job writes all result data to a data warehouse (BigQuery, Redshift, Snowflake — your choice). This is where you serve historical comparisons: how did this constituency vote in the last three elections, which regions had the highest swing, and so on. You don't serve these queries from Cassandra. Cassandra is optimised for the access patterns you defined at schema design time. Ad-hoc analytical queries are not one of them.

Step 6: Serving Layer: 100 Million People Checking Results at Once

Here's the part most system design guides skip: the read scaling problem is not a database problem. It's a content delivery problem.

Think about what most of those 100 million people actually want. They want to know the current national tally, or they want to check how one specific constituency is running. Both of those are pre-aggregatable. You don't need to run a live query for either.

Three serving patterns, each for a different use case:

REST API for point queries.

GET /results/constituency/{id} — reads from Redis, falls back to Cassandra on a cache miss. Stateless API servers behind a load balancer. Scale horizontally.

WebSocket for live updates.

Don't make 100 million clients poll your REST API every 5 seconds. That's 20 billion requests per hour. A WebSocket connection means your server pushes an update when the data changes. The client doesn't ask. This is the correct pattern for a live results dashboard. WebSocket servers fan out updates using Redis Pub/Sub — when Flink writes a new snapshot to Redis, it also publishes to a Pub/Sub channel. All WebSocket server instances subscribed to that channel push the update to their connected clients.

CDN snapshots for the national summary.

Every 30 seconds, a lightweight job serialises the national results summary — party standings, seats declared, leads and wins — into a static JSON file and pushes it to a CDN edge network. A user requesting the national summary gets served this file from a CDN node close to them. Your API servers don't see this traffic at all.

I want to be direct about one thing here: managing your own WebSocket fleet at 100 million concurrent connections is a serious infrastructure operation. In a real production system, most teams would evaluate a managed WebSocket service rather than running their own. The design principle is correct. The operational choice is a separate question.

Step 7: Fault Tolerance: What Happens When Things Break

Every system design interview has this follow-up. Most candidates give a vague answer: "we'd use replication." That's not an answer. Name every failure mode and its specific recovery strategy.

The one most candidates forget is the counting centre network failure. That's the most likely real-world failure. Rural and remote areas have unreliable connectivity. The agent-side local buffer with idempotent replay is what saves you.

Step 8: Monitoring and Observability

A pipeline that you cannot observe is a pipeline you cannot manage. Especially during a time-bounded event with no maintenance windows.

Here's what you instrument, and why each one matters:

Kafka consumer lag per constituency partition.

If lag is growing, your Flink job is falling behind ingestion. This is your earliest warning that the processing layer is under strain. Alert if lag exceeds 1,000 events for any single partition.

Flink checkpoint duration.

Checkpoints are Flink's recovery mechanism. If they're taking more than 30 seconds, your state is too large or your storage I/O is saturated. Alert at 30 seconds. Page at 60 seconds.

Redis cache hit rate.

During normal peak operation, this should be above 95%. If it drops, Cassandra is absorbing read traffic it wasn't designed to handle. At 90% hit rate, trigger automatic read replica scaling.

API error rate by endpoint.

Track 4xx and 5xx rates per endpoint separately. A spike in 5xx errors on the WebSocket endpoint is a different problem than a spike in 4xx errors on the REST API. Don't aggregate them.

End-to-end pipeline latency.

Timestamp events at ingestion. Timestamp them again when they become visible via the serving layer. The delta is your actual end-to-end latency. Alert if this exceeds 10 seconds — you have a 5-second SLA and you want headroom.

Tooling: Prometheus and Grafana for metrics, OpenTelemetry for distributed tracing across layers, PagerDuty for alerting.

What I’d Actually Build Differently

Most system design guides give you the clean answer. Here’s what’s actually messy.

Flink might be overkill for the write side.

50,000 result updates per hour across 543 constituencies is roughly 92 events per minute. A well-designed Kafka consumer writing to Postgres with proper indexing could handle the write side without Flink’s operational complexity. Flink earns its place in this design because of stateful aggregations, exactly-once guarantees, and the winner detection logic, not because of raw throughput. In an interview, saying “Flink is overkill if we strip out the stateful requirements” signals more maturity than just adding every big-data tool you know.

Cassandra has a high operational cost.

If I’m building this in a startup or for a country that doesn’t have a dedicated infrastructure team, I’d seriously evaluate DynamoDB or Cosmos DB before committing to self-managed Cassandra. The best database is often the one your team can actually debug at 2 AM during a counting window.

The CDN layer is where the real scale lives.

Most candidates design sophisticated write pipelines and then describe a REST API for 100 million concurrent users. The CDN strategy is not a nice-to-have optimisation, it’s the architectural decision that makes the system not fall over. If you’re going to do one thing well in your answer, make it this.

Actually, Forget what I said about Flink earlier.

There’s a version of this design where you run two stream processors: a lightweight Kafka Streams application for simple aggregations (fast, low-latency, easy to operate) and Flink only for complex stateful operations like winner detection and anomaly checking. Whether that’s simpler depends entirely on your team. I’ve seen both choices made by strong engineers for good reasons.

The Interview Cheat Sheet

Before you leave, here’s the reference card for this entire design. Save it. Sketch it on your whiteboard before you start talking.

The Follow-Up Interviewer Questions That Separate Good Candidates From Great Ones

Most candidates can draw the happy-path architecture. That’s not usually what decides the interview.

The real evaluation starts after the diagram is done.

This is where interviewers begin stress-testing your design:

▢ What happens if schemas change during the counting window? ▢ What happens when Flink starts falling behind Kafka? ▢ What would you cut if leadership demanded a 40% infrastructure cost reduction without sacrificing reliability?

These questions matter because real production systems are rarely judged only on whether they work. They’re judged on whether they continue working under operational pressure, changing requirements, and business constraints.

Let’s walk through the follow-ups that usually come next after the whiteboard design is complete.

Q1. How would you handle schema evolution if new candidate data arrives mid-count?

I’d keep schema evolution backward-compatible using the schema registry already present in the ingestion layer. New fields should be added as optional rather than changing existing required fields.

For example, if candidate metadata changes mid-count, producers can publish the newer schema version while existing Flink consumers continue processing older events safely. I would avoid changing partition keys or idempotency-key structure during the counting window because that risks ordering and deduplication consistency.

Q2. How would you design for backpressure if Flink falls behind Kafka?

Kafka already gives us a buffer layer, so temporary lag is survivable. The first signal I’d watch is Kafka consumer lag per constituency partition.

If Flink starts falling behind, I’d increase Flink task parallelism and rebalance partitions across more operators. I’d also prioritise critical aggregation paths and temporarily reduce non-essential downstream processing.

If checkpoint duration becomes too high, that’s usually a sign that Flink state has grown too large or storage I/O is saturated. At that point, I’d reduce state pressure or slightly relax checkpoint frequency before the system becomes unstable.

The goal is graceful degradation. The system should slow down safely, not stop ingesting events.

Q3. If leadership asks you to cut infrastructure cost by 40%, what would you change first?

I would optimise the serving layer first because that’s where most of the scale cost exists.

More traffic can be shifted toward CDN snapshots and cached constituency summaries instead of live API reads. I’d also reduce unnecessary WebSocket fan-out frequency where real-time updates are not strictly needed.

I would not weaken durability guarantees or remove Kafka just to save infrastructure cost. Those decisions usually create larger operational risks later.

I’d also revisit whether every workload truly needs Flink. As mentioned earlier in the article, parts of this pipeline could be handled by simpler Kafka consumers or Kafka Streams if the stateful requirements become smaller.

What You Should Walk Away With

This is not just an elections problem. The architecture above solves a class of problems: high-stakes, time-bounded events where writes are moderate, reads are enormous, and the cost of data loss or inconsistency is unacceptable.

You’ll see this again in sports scoring systems (IPL), financial market data pipelines, logistics tracking platforms, and live broadcast dashboards. The names change. The layers don’t.

The three decisions that make or break this system are:

(1) Idempotent ingestion (because network failures are guaranteed),

(2) Separation of write and read paths (because they scale differently),

(3) The CDN layer (because most of your traffic doesn’t need real-time data, it needs recent data, fast).

If you can explain those three decisions clearly and defend the tradeoffs, you’re ahead of most candidates who spend their entire interview time debating which version of Spark to use.

I publish practical breakdowns and opinionated pieces backed by my personal experiences on data engineering, analytics and AI every week. If this kind of depth is useful to you, follow me and subscribe to emails so you catch the next piece when it drops.

Want to connect? Feel free to reach out to me on **LinkedIn**.

— Satyam

You may also like reading my previous pieces:

Complete Data Warehousing Series (10 Article): Link

Airflow Practitioner Series: Link

1. Building Agent Ready Data Pipelines From Scratch
2. You’re Overthinking Your First Data Project. Here’s What to Actually Build
3. Local LLMs For Data Work: A Hardware-Honest Guide To What Actually Runs
4. AI Copilots in Data Engineering: What Actually Works, What Doesn’t, and Where Each One Fits
5. Good Data, Bad Decisions: The Analytics Paradox Nobody Talks About
6. Top SQL Patterns to Master for MAANG Data Engineer Interviews
7. How Real Companies Design Their Data Warehouses
8. Star Schema Made Simple: The Data Model Used by Almost Every Company