๐ก How to use this guide
- Say answers aloud. Data engineers are hired partly on communication. If you can't explain Spark lazy evaluation clearly in 60 seconds, you haven't learned it yet โ you've just read it. Talk to yourself, record a voice memo, or find a study partner.
- Time yourself. For EASY questions, aim to answer in under 30 seconds. MEDIUM questions should take 60โ90 seconds. HARD and system design questions can take 3โ5 minutes โ that's expected.
- Cover system design with a whiteboard (or paper). System design questions require drawing components, data flows, and failure modes. Practice sketching the architecture before looking at the answer.
- Recommended order: Do all EASY questions on Day 1 to build confidence and vocabulary. Do all MEDIUM questions on Day 2โ3. Tackle HARD and system design questions on Day 4โ5 after you have the fundamentals solid.
- Add your own examples. The best interview answers include a real project you built. Replace "a payments company" in these answers with your actual track project.
Applicant tracking systems (ATS) score your resume against the job description. Include these exact terms:
๐ Spark / PySpark
Lazy evaluation means that when you call a transformation on a DataFrame โ like filter(), select(), or withColumn() โ Spark does not execute any computation immediately. Instead, it records the operation in a logical plan and waits. Execution is deferred until you call an action such as count(), show(), write(), or collect().
This matters for two reasons. First, it allows Spark's Catalyst optimizer to analyze the entire chain of operations before deciding how to execute them. It may reorder filters to push them closer to the data source, eliminate unnecessary columns early (predicate pushdown), or merge consecutive transformations into a single stage โ optimizations that would be impossible if each step executed immediately. Second, lazy evaluation means that a typo in a transformation name or a bad column reference is not caught until an action runs, which surprises engineers who come from pandas.
In practice: always call df.explain() before running a heavy job to inspect what the optimizer produced. A good physical plan will show Filter pushed before Scan and unnecessary projections eliminated.
A transformation takes a DataFrame (or RDD) and returns a new DataFrame without executing any computation. It is lazy โ it just adds a step to the logical plan. Examples: filter(), select(), withColumn(), groupBy().agg(), join(), map(), flatMap().
An action triggers the actual execution of all accumulated transformations and returns a result to the driver (or writes data to storage). Examples: count(), show(), collect(), first(), take(n), write(), save().
Every time you call an action, Spark compiles the full logical plan into a physical plan, schedules jobs and stages across executors, and runs the computation. Calling two actions on the same DataFrame executes the full plan twice โ which is why you should cache() or persist() a DataFrame you need to act on multiple times.
A shuffle is when Spark must redistribute data across partitions โ moving records from one executor to another across the network. It is the most expensive operation in Spark because it requires serialization, network I/O, and disk spill. Operations that cause a shuffle: groupBy(), join() (unless broadcast), distinct(), repartition(), orderBy(), and window functions with a partitionBy() clause.
To minimize shuffles: (1) Use broadcast joins (F.broadcast(small_df)) when one side of a join is small enough to fit in memory โ the small DataFrame is sent to every executor, and no shuffle is needed on the large side. (2) Use partition pruning by filtering on partition columns before joins or aggregations. (3) Pre-aggregate data before joining. (4) Tune spark.sql.shuffle.partitions โ the default of 200 is too high for small datasets and too low for large ones. (5) Enable AQE (spark.sql.adaptive.enabled=true) which dynamically coalesces shuffle partitions based on actual data size.
A sign of too many shuffles in production: your Spark UI shows stages with many small tasks and high shuffle read/write bytes per stage. Check the event timeline โ a wide stage that takes most of the job time almost always involves an avoidable shuffle.
Adaptive Query Execution (AQE) is a feature introduced in Spark 3.0 (enabled by default in 3.2+) that re-optimizes the physical plan at runtime based on statistics collected during execution, rather than relying solely on the static plan generated at compile time. Enable with spark.sql.adaptive.enabled=true.
AQE helps in three main scenarios. First, dynamic shuffle partition coalescing: instead of always producing spark.sql.shuffle.partitions (default 200) output partitions after a shuffle, AQE merges small partitions into larger ones. This prevents the common problem of 200 tasks where 190 are tiny and finish in milliseconds while 10 are large and take minutes. Second, dynamic skew join handling: when AQE detects that one partition is significantly larger than the median (a "skewed" partition), it automatically splits that partition and processes it in smaller chunks. Third, dynamic join strategy switching: AQE can switch a sort-merge join to a broadcast hash join at runtime if it discovers at runtime that one side is actually smaller than the broadcast threshold after filtering.
When AQE does not help: highly skewed group-by aggregations where the skew is in the key space rather than a join, or streaming jobs where the micro-batch size is too small to collect meaningful statistics. Always check the Spark UI's SQL tab โ AQE-optimized plans show re-optimized stages with a blue "AQE" label.
Data skew in a join occurs when a small number of join key values appear disproportionately often โ for example, a merchant_id for the biggest merchant representing 40% of all transactions. The partition holding that key gets far more data than others, creating a single slow task that holds up the entire stage (a "straggler").
Option 1 โ Broadcast join: If the smaller table fits in memory (default threshold 10 MB, configurable via spark.sql.autoBroadcastJoinThreshold), use F.broadcast(small_df). No shuffle occurs on the large side, so there's no partition to become skewed.
Option 2 โ Salting: Add a random "salt" integer (e.g., 0โ9) to the join key on the large side, then explode the small side to create one row per salt value for each key. The join key becomes (merchant_id, salt). This spreads the skewed key across 10 partitions instead of 1. After the join, remove the salt column. Effective but adds code complexity.
Option 3 โ AQE skew handling: Enable AQE (spark.sql.adaptive.enabled=true, spark.sql.adaptive.skewJoin.enabled=true). AQE detects skewed partitions at runtime and automatically splits them. This is the simplest approach and works well for moderate skew. Tune spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes if the default detection threshold is too aggressive.
Option 4 โ Separate the hot key: Filter out the skewed key(s), process them separately (often with a broadcast join or custom logic), and union the results back with the non-skewed portion.
cache() is a shortcut for persist(StorageLevel.MEMORY_AND_DISK). The two calls are equivalent in behavior โ cache() simply calls persist() internally with the default storage level.
persist() allows you to specify the storage level explicitly. The key options are: MEMORY_ONLY โ stores as deserialized Java objects in JVM heap, fastest access but evicted entirely if memory is full; MEMORY_ONLY_SER โ serialized, more space-efficient, slightly slower to access; MEMORY_AND_DISK (the default, same as cache()) โ spills to disk if memory is full, so nothing is evicted and re-computed; DISK_ONLY โ always on disk, slowest but uses zero heap.
In practice: use cache() for most cases. Use persist(StorageLevel.MEMORY_ONLY_SER) when you have a large DataFrame and want to save heap space at the cost of CPU for serialization/deserialization. Always call unpersist() when you're done with a cached DataFrame to free memory for subsequent stages. Caching is only useful if you act on the DataFrame at least twice โ caching for a single action adds overhead with no benefit.
Structured Streaming, introduced in Spark 2.0, treats a live data stream as an unbounded table that keeps growing. You write the same DataFrame/SQL API you use for batch jobs โ filter(), groupBy(), join() โ and Spark automatically increments the computation as new data arrives. The engine handles checkpointing, exactly-once semantics (with supported sinks), and watermarking for late data. It runs on top of the Spark SQL engine with full Catalyst optimizer support.
DStreams (Discretized Streams) were the original streaming API from Spark 1.x. They work by micro-batching RDDs at a fixed interval. DStreams use the lower-level RDD API, lack native SQL optimization, have weaker exactly-once guarantees, and require separate code paths for batch and streaming jobs โ you cannot reuse the same logic.
The practical differences: Structured Streaming is the only officially supported API going forward โ DStreams are legacy. Structured Streaming supports event-time processing and watermarks natively, which DStreams do not. Structured Streaming integrates directly with Kafka through readStream.format("kafka"). For any new project, use Structured Streaming โ DStreams exist only for maintaining old code.
PySpark code is tested with a combination of unit tests that run against a local SparkSession and integration tests that run against real or containerized infrastructure.
For unit tests: create a shared SparkSession in a pytest fixture with master("local[2]") and reuse it across tests (session-scoped to avoid the 10โ30 second startup overhead on every test). Create small in-memory DataFrames with spark.createDataFrame() and assert on the output using the chispa library: assert_df_equality(actual, expected, ignore_row_order=True) gives a clean diff if columns or values don't match. Test each transformation function in isolation by extracting it from your job file.
For integration tests: spin up the full Docker Compose stack, run the job end-to-end, and assert on the data written to the sink (Cassandra row count, Parquet schema, Kafka message count). Use pytest markers (@pytest.mark.integration) to separate these slow tests from fast unit tests and run them only in CI.
Common pitfall: testing with local[1] can mask parallelism bugs that only appear with multiple partitions. Use local[2] or higher to expose them in unit tests.
๐ Kafka
A partition is the fundamental unit of parallelism and ordering in Kafka. A topic is split into one or more partitions, each of which is an ordered, immutable log of messages stored on disk. Messages within a partition are guaranteed to be delivered in the order they were produced. Messages across partitions are not ordered relative to each other.
Why partition count matters: a consumer group can have at most one consumer per partition actively reading at a time. If you have 3 partitions and 5 consumers in a group, 2 consumers will be idle. If you have 3 partitions and 1 consumer, that consumer reads all 3 โ potentially a bottleneck. The rule of thumb: set partition count to the maximum number of parallel consumers you expect to need. More partitions increase throughput but also increase end-to-end latency (more coordination overhead) and resource consumption on the broker (each partition is a directory with files). You cannot decrease partition count after creation โ only increase it โ so plan ahead but don't over-partition (20โ50 partitions per topic is a sensible starting point for most pipelines).
At-least-once guarantees that every message is delivered, but the same message may be delivered more than once. This happens when a consumer processes a message and then crashes before committing its offset โ on restart, the message is re-fetched and processed again. To make at-least-once safe, your processing logic must be idempotent (applying it twice must have the same result as applying it once). This is the default for most production Kafka pipelines and is achieved by setting enable_auto_commit=False and committing offsets only after successful processing.
Exactly-once guarantees that each message is processed exactly one time โ no loss, no duplicates โ even across crashes and retries. Kafka implements this through the transactional API: a producer with a transactional_id can atomically publish to multiple partitions and commit consumer offsets in a single transaction. Either everything commits or nothing does. This is more expensive (adds 50โ200 ms per batch for the transaction log write) and more operationally complex. Use it only when duplicate effects are truly unacceptable โ inter-bank transfers, financial reconciliation records. For most data engineering pipelines, at-least-once + idempotent consumers achieves the same correctness guarantee at lower cost.
A rebalance occurs when the assignment of partitions to consumers in a group changes โ triggered by a consumer joining, leaving, or timing out (missing a heartbeat). During a rebalance, all consumers in the group stop processing until the coordinator has redistributed all partitions. This "stop the world" pause can last from a few hundred milliseconds to several minutes depending on the number of partitions and the complexity of the state being handed off.
How to minimize impact: (1) Increase session.timeout.ms and max.poll.interval.ms to give slow consumers more time before being considered dead โ avoids spurious rebalances caused by legitimate slowdowns. (2) Use the Cooperative Sticky assignor (partition.assignment.strategy=CooperativeStickyAssignor) instead of the default Eager assignor โ cooperative rebalancing reassigns only the partitions that need to move, so consumers that keep their partitions never pause. (3) Ensure consumers poll frequently enough (don't do long blocking work between polls โ offload processing to a thread pool). (4) Use the static group membership feature (group.instance.id) so that a consumer that restarts rejoins with its previous assignment without triggering a full rebalance.
Kafka guarantees ordering only within a single partition. Messages in a partition are written to an append-only log and read in the exact order they were written. There is no ordering guarantee across partitions of the same topic.
If you need all messages for a given entity to be ordered โ for example, all payment events for a specific merchant โ you must route them to the same partition by setting a message key. The default partitioner hashes the key and sends all messages with the same key to the same partition. This guarantees that merchant_id=M001 events always arrive in order at the consumer, regardless of how many partitions the topic has or how many other merchants are in the system.
Important edge cases: if you increase the partition count of a topic, the hash-to-partition mapping changes, so a key that was going to partition 2 may now go to partition 5. Old messages for that key remain in partition 2, new ones go to partition 5 โ ordering is broken for events straddling the resize. Plan partition counts carefully upfront, or use a custom partitioner that provides stable routing across partition count changes.
__consumer_offsets is an internal Kafka topic that the broker uses to durably store the committed offset for every consumer group, topic, and partition combination. It replaced ZooKeeper-based offset storage in Kafka 0.10 and is always present in any modern Kafka cluster.
When a consumer calls commit() (manually) or Kafka auto-commits on its behalf, the committed offset is written as a record to __consumer_offsets with the key (group_id, topic, partition) and the value being the offset + metadata. On restart, the consumer reads its committed position from this topic to know where to resume. The topic is compacted (only the latest record per key is retained) so it doesn't grow unboundedly.
In production debugging: if a consumer's committed offset appears stuck or is resetting, inspect __consumer_offsets by using kafka-consumer-groups.sh --describe --group <group>. If the LAG column shows a large and growing number, the consumer is falling behind. If the offset is moving backwards (committed offset older than current position), you may have a misconfigured auto-offset reset or a bug in manual commit logic. Never delete or modify __consumer_offsets records manually โ use the reset-offsets command instead.
Good Kafka topic design is driven by access patterns, ordering requirements, retention needs, and consumer independence. For a payments system I would use the following approach.
Topics by event type, not by consumer: payments.raw (raw inbound events), payments.validated (passed schema validation), payments.settled, payments.failed. Each is a separate topic because they have different retention requirements (raw might keep 7 days for replay; settled events might keep 30 days for audit) and different consumer groups that care about different stages.
Partition key = merchant_id: all events for a merchant go to the same partition, guaranteeing order for that merchant's event stream. This allows a consumer that processes merchant settlement to read from a single partition without cross-partition coordination.
Schema registry + Avro or Protobuf: never use plain JSON for a payments topic in production. Register the schema in a Schema Registry (Confluent or AWS Glue) so producers and consumers agree on field names and types. Schema evolution rules (BACKWARD, FORWARD, FULL compatibility) prevent producers from breaking existing consumers when the schema changes.
Partition count: start at 12 โ enough for 12 parallel consumers and headroom to scale. For a 10 billion transaction per day volume, you might go to 50โ100. Set replication factor to 3 for durability. Set min.insync.replicas=2 and acks=all on producers so no message is acknowledged until 2 replicas have written it.
An idempotent producer is a Kafka producer configured with enable_idempotence=True (Java: enable.idempotence=true). It guarantees that even if a produce request is retried due to a network error or broker timeout, the broker will not write the same message twice.
Internally, the broker assigns the producer a Producer ID (PID) โ a unique 64-bit integer โ and the producer attaches a monotonically increasing sequence number to every batch of messages, per partition. When the broker receives a batch, it checks the sequence number against its state for that PID and partition. If the sequence number matches what it expects (one higher than the last committed), the batch is written. If it sees the same sequence number again (a retry of an already-committed batch), it silently acknowledges without writing a duplicate. If the sequence number is out of order, it returns an error.
Setting enable_idempotence=True automatically implies acks='all' and retries=5 โ you do not need to set these manually. Idempotent producers protect against producer-retry duplicates only. They do not protect against consumer-side duplicates (which require idempotent consumer logic) or cross-partition duplicates (which require transactions).
A poison pill is a message that consistently causes the consumer to throw an exception during processing โ a malformed JSON, an unexpected null in a required field, a schema version the consumer doesn't support. Because Kafka consumers must commit offsets sequentially per partition, a poison pill at offset 100 blocks all messages at offsets 101, 102, etc. from being processed. The consumer retries, fails, retries, fails โ indefinitely.
Strategy 1 โ Dead Letter Queue (DLQ): Wrap the message processing in a try/catch. On exception, produce the raw (unprocessable) message to a separate payments.dlq topic, then commit the offset and move on. The DLQ topic can be monitored, messages inspected manually, and re-processed after a fix is deployed. This is the most common production pattern.
Strategy 2 โ Retry with backoff + DLQ: Retry the failed message 3 times with exponential backoff (in case it's a transient error โ e.g., downstream database temporarily unavailable). Only move to the DLQ after all retries are exhausted. Libraries like kafka-retry (Python) or Spring Kafka (Java) implement this pattern.
Strategy 3 โ Schema validation at ingest: Validate every message against a registered schema as it enters the pipeline (before the main consumer). Reject malformed messages at the boundary โ they never reach the processing consumer. This works well with a Schema Registry + Avro/Protobuf setup. In practice, use all three: schema validation at ingest, retry with backoff in the consumer, and a DLQ for messages that survive all retries.
๐๏ธ Cassandra / NoSQL
The partition key determines which node stores a row and how data is distributed across the cluster. A good partition key must satisfy two properties: high cardinality (many distinct values so data spreads evenly across nodes) and alignment with your query access pattern (you must always filter on the partition key โ queries without it do a full-cluster scan and time out).
Start by listing your most critical queries. For a payments table where the primary access pattern is "get all payments for merchant X in the last 7 days," the partition key should be merchant_id. All rows for that merchant live on the same node, making the query fast. The clustering key (event_time) then sorts rows within the partition so range queries are efficient.
Pitfalls to avoid: (1) Using a column with very low cardinality (e.g., status with values "settled"/"failed") โ this creates 2 hot partitions holding all your data. (2) Using a column with such high cardinality that partitions are too small (e.g., txn_id โ each row is its own partition, making range queries across merchants impossible). (3) "Hotspot" keys โ if one merchant processes 40% of all transactions, partition key = merchant_id will create a hot node for that merchant. Consider a composite partition key like (merchant_id, date_bucket) to spread load.
The partition key determines which node (or nodes, in multi-DC) stores the data. It is hashed to produce a token, which maps to a node in the consistent hash ring. All rows with the same partition key value are stored together on the same node. You must include the full partition key in every query โ Cassandra cannot efficiently query rows spread across nodes without it.
The clustering key determines the sort order of rows within a partition. Rows in the same partition are physically stored on disk sorted by the clustering key. You can run range queries on clustering keys (WHERE event_time > '2026-01-01') because the data is sorted. You can also sort in ascending or descending order at table creation time with WITH CLUSTERING ORDER BY (event_time DESC).
Together they form the primary key: PRIMARY KEY ((partition_key), clustering_key_1, clustering_key_2). The partition key is in the inner parentheses. A composite partition key (multiple columns) is also possible: PRIMARY KEY ((merchant_id, date_bucket), event_time, txn_id).
Cassandra's consistency level controls how many replicas must acknowledge a read or write before the coordinator returns success to the client. It is set per-query, not per-table, so you can mix consistency levels in the same application.
ONE: Only 1 replica must respond. Fastest and most available โ the operation succeeds as long as at least 1 replica is alive. For reads, you may get stale data if a replica hasn't received recent writes (read repair runs asynchronously). Use ONE for high-throughput writes or reads where slightly stale data is acceptable (e.g., caching a merchant's configuration).
QUORUM: A majority of replicas must respond: floor(replication_factor / 2) + 1. For RF=3, that's 2 replicas. QUORUM reads and QUORUM writes together guarantee that you always read the most recent write โ there is at least 1 replica in common between the write quorum and the read quorum. This is the recommended consistency level for most production workloads that need strong consistency without sacrificing too much availability.
ALL: All replicas must respond. Strongest consistency โ reads always return the most recent data and writes are only confirmed when all replicas have written. However, a single replica failure causes the entire operation to fail. Use ALL only for extremely consistency-sensitive operations where data loss is unacceptable and latency is not a concern. In practice, QUORUM provides the same effective consistency guarantee with much better availability.
Cassandra's data model is designed around query-first design โ you define your tables to match specific, known access patterns, not based on entities or relationships. This is the opposite of the relational model where you design normalized tables and then query them flexibly with SQL joins and WHERE clauses on any column.
The fundamental constraint is that every query must include the full partition key. Without it, Cassandra would have to ask every node in the cluster "do you have data matching this filter?" โ a full-cluster scatter/gather โ which in a 100-node cluster means 100 network round trips and 100 disk reads before you get a result. This does not scale. Cassandra sacrifices query flexibility for write throughput and predictable latency at scale.
Secondary indexes exist in Cassandra but are generally discouraged for high-cardinality columns โ they scatter reads across nodes and degrade under load. ALLOW FILTERING overrides the partition key restriction but explicitly warns you that it triggers a full-cluster scan. For ad hoc analytics, export data to DuckDB, BigQuery, or Spark instead. In Cassandra, if you need a new access pattern, create a new table designed for that query.
A tombstone is a special marker written to Cassandra's SSTables to represent a deletion. Because Cassandra is an append-only distributed system, you cannot physically delete a row immediately โ other replicas may not be available to receive the delete at that moment. Instead, the delete is written as a tombstone with a timestamp. During reads, Cassandra merges data from multiple SSTables and uses tombstones to exclude deleted rows from results. After gc_grace_seconds (default 10 days), tombstones are removed during compaction.
The problem: if a partition accumulates a large number of tombstones โ for example, if you frequently delete and re-insert rows in a wide partition โ every read of that partition must scan through all the tombstones to filter them out. This dramatically slows reads and increases garbage collection pressure on the JVM. If tombstone count per read exceeds tombstone_warn_threshold (1000) or tombstone_failure_threshold (100,000), Cassandra logs warnings or aborts the query entirely with a TombstoneOverwhelmingException.
How to avoid: (1) Design tables to avoid frequent deletions โ use TTL (INSERT ... USING TTL 86400) instead of explicit deletes when data should expire. TTL creates tombstones too, but they're bounded and predictable. (2) Use TRUNCATE instead of row-level deletes when you need to clear an entire table โ truncate drops the SSTable files directly. (3) Monitor tombstone count per partition using nodetool cfstats and ensure compaction is running regularly to remove expired tombstones.
Cassandra uses a peer-to-peer architecture with no single master โ all nodes are equal. Data is replicated across multiple nodes (determined by the replication factor). When a node fails, Cassandra handles it through two mechanisms.
For reads: the coordinator node routes the query to one of the remaining replica nodes. As long as the consistency level can be satisfied (e.g., QUORUM with RF=3 requires 2 of 3 replicas โ one failure is tolerated), reads continue without interruption. Cassandra also performs read repair: if it reads from multiple replicas and they return different values (the failed node had un-replicated writes), it reconciles using the latest timestamp and repairs the stale replica.
For writes: hinted handoff handles temporarily unavailable replicas. If a replica is down, the coordinator stores a "hint" โ a copy of the write โ on behalf of the failed node. When the node comes back online, the coordinator replays the hints so the node catches up on the writes it missed. For extended outages, repair (nodetool repair) must be run manually to ensure full consistency โ hinted handoff only retains hints for a limited time (max_hint_window_in_ms, default 3 hours).
Cassandra and OpenSearch are both distributed NoSQL stores but are optimized for very different access patterns.
Choose OpenSearch when: you need full-text search (tokenized, scored, fuzzy matching); you need to query across many fields without knowing the partition key in advance (ad hoc queries); you need aggregations by any field (count by country, histogram by amount range, date histogram); you need to power a search UI or API where users enter free-form queries; or you need real-time log/event analytics (OpenSearch Dashboards replaces Kibana for visualization).
Choose Cassandra when: you need extremely high write throughput (millions of writes per second); you have a known, fixed access pattern (always query by the same partition key); you need predictable, low-latency point reads and range reads on clustering columns; or you need linear horizontal scalability for a key-value or wide-row workload.
In production, they are often used together: Cassandra as the primary write-optimized store (raw transaction records) and OpenSearch as the secondary index for search and analytics (merchants searching their transaction history, dashboards, monitoring). The pipeline writes to Cassandra first (primary of record), then asynchronously indexes into OpenSearch for query flexibility.
These are two distinct problems that can occur separately or together.
A wide partition (also called a "large partition") is a partition that contains an unusually large number of rows or a very large total byte size. For example, a payments table partitioned by merchant_id where one merchant has 10 million transactions โ that single partition might be 50 GB. Wide partitions are problematic because: (1) compaction must process the entire partition in memory, increasing JVM GC pressure; (2) repair is slower; (3) any read on that partition loads more data than needed; (4) the node holding the partition uses significantly more disk than neighboring nodes (imbalanced cluster). The fix is usually a composite partition key with a date or time bucket: (merchant_id, month) splits the data into smaller, bounded partitions.
A hot partition is a partition that receives a disproportionate share of read or write traffic โ creating a "hot node" in the cluster. Even if the partition is small in terms of bytes, the node responsible for it handles far more requests than its peers, becoming a CPU/network bottleneck. For example, if a partition key is merchant_id and one merchant generates 80% of all API calls, every read for that merchant hits the same 2โ3 nodes (RF=3). The fix: add a random "shard" suffix to the partition key to spread hot keys across more nodes, or pre-aggregate at the application level to reduce the query rate to Cassandra for that merchant.
๐๏ธ General Data Engineering
Batch processing collects data over a period of time and processes it all at once as a discrete job. The job has a clear start and end. Data at rest is read from a file system or database, transformed, and written to a sink. Examples: a nightly Spark job that computes yesterday's settlement totals from a Parquet lake. Batch is efficient for large historical datasets, simpler to reason about, and cheaper to operate โ but it has latency equal to the batch interval (results may be hours or days old).
Streaming processing processes data continuously as it arrives, with very low latency (milliseconds to seconds). The job runs indefinitely, reading from an event source like Kafka. Examples: a Spark Structured Streaming job that updates a merchant's daily spend total every few seconds as new transactions arrive. Streaming is harder to build and operate correctly (you must handle late data, exactly-once semantics, and stateful aggregations) but necessary when business decisions require near-real-time data.
In most mature data platforms, both exist: streaming pipelines produce low-latency operational metrics, while batch pipelines produce authoritative daily/monthly aggregates that are used for billing, reporting, and regulatory compliance. The streaming layer catches the signal fast; the batch layer provides the ground truth.
An operation is idempotent if applying it multiple times produces the same result as applying it once. f(f(x)) = f(x). In data pipelines, this means: if a job runs twice with the same input data, the output data is the same as if it had run once โ no duplicates, no double-counts, no double-charges.
Idempotency is critical because distributed systems fail. A Spark job crashes mid-write and is restarted. A Kafka consumer processes a message but crashes before committing the offset. A task in an Airflow DAG times out and is retried. In all these cases, the same data will be processed twice. If your pipeline is not idempotent, you will corrupt your data.
How to implement it: (1) Use upserts keyed on a stable, unique identifier from the source data (e.g., txn_id). If the same row arrives twice, the second write updates the same record โ net result is one record, same as if it arrived once. (2) Use overwrite semantics in Spark: df.write.parquet("output/", mode="overwrite") on a partition โ re-running the job rewrites the same partition, not a second copy. (3) Track which events have been processed using a dedicated "processed IDs" table, and skip any event whose ID has already been recorded.
Late data is a fundamental challenge in streaming: events are produced at one time but may arrive at the processing system minutes, hours, or even days later due to network partitions, mobile apps coming online, or slow upstream producers. If your pipeline computes aggregates (e.g., total payments per merchant per hour), late data means the aggregates computed at the window's close are incomplete.
In Spark Structured Streaming, handle late data with watermarking: df.withWatermark("event_time", "30 minutes"). This tells Spark to maintain state for an event-time window for up to 30 minutes after the window closes. Events that arrive within the watermark delay are included in the aggregate. Events that arrive after the watermark has passed are dropped. The watermark also determines how long state is kept in memory โ without it, state grows unboundedly. Tune the watermark based on your actual P99 data latency โ if 99% of events arrive within 15 minutes, set 20 minutes to catch almost all late data while bounding state size.
For batch pipelines: schedule a reprocessing run for each partition after a lag window. For example, the daily partition for 2026-01-15 is first written at midnight on 2026-01-16, then reprocessed at midnight on 2026-01-17 to pick up any events that arrived late. Mark the first run as "preliminary" and the second as "final" in your data catalog. This is a common pattern for financial settlement where you need an authoritative final number but want a fast preliminary estimate too.
The medallion architecture is a layered data lake design pattern that organizes data into three quality tiers, each adding progressively more structure and value.
Bronze (raw): Data is landed exactly as it arrives from source systems โ no transformation, no schema enforcement, no cleaning. JSON files from Kafka, CSV exports from production databases, raw API responses. Bronze is the system of record. It should be append-only and retained indefinitely (or per regulatory requirements) so you can always reprocess from raw if a downstream bug is discovered. Schema-on-read applies here.
Silver (cleaned & conformed): Bronze data is validated, deduplicated, enriched, and given a consistent schema. Parsing errors are handled, data types are cast correctly, null values are handled. Silver is where business logic is applied. It is still row-level โ one row per event or entity. Used by analysts and data scientists for exploration and feature engineering. In dbt terms, silver = staging + intermediate models.
Gold (aggregated & business-ready): Silver is aggregated into metrics, KPIs, and dimensional models for specific use cases โ daily merchant settlement totals, monthly active user counts, fraud rate by region. Gold tables are optimized for reporting and are read by dashboards, finance teams, and external APIs. Schema-on-write. In dbt terms, gold = mart models.
Data lineage is the ability to trace a data point โ a number in a dashboard, a row in a report, an ML feature โ back through every transformation and pipeline stage to its original source. A complete lineage graph answers: where did this data come from, what transformations changed it, which jobs wrote it, and who consumes it downstream?
It matters for three reasons. First, debugging and incident response: when a KPI drops unexpectedly or a compliance report shows an anomalous number, lineage lets you trace it back to a broken upstream job or a schema change in a source table in minutes instead of days of manual investigation. Second, impact analysis: when you need to change a source table or a transformation, lineage shows you every downstream table, dashboard, and model that will be affected โ so you can notify owners and update consumers before breaking them. Third, regulatory compliance: GDPR right-to-erasure and financial audit requirements mandate that you know exactly which data came from which individual or transaction and can demonstrate a chain of custody.
Tools: dbt generates lineage graphs automatically from the SQL DAG (dbt docs generate). For cross-system lineage (Kafka โ Spark โ Cassandra โ dashboard), use OpenMetadata, DataHub, or Marquez. Great Expectations adds a validation layer to the lineage graph showing where quality checks pass or fail.
Data quality is not a single check โ it's a layered strategy applied at multiple stages of the pipeline.
At ingestion: schema validation using a Schema Registry (Avro/Protobuf) ensures that malformed events never enter the pipeline. Any event that fails schema validation is routed to a dead letter queue for investigation rather than silently dropped or passed through.
At transformation (dbt or Spark): use column-level assertions โ not-null checks on required columns, uniqueness checks on IDs, accepted-values checks on enum-like fields (status IN ('settled', 'failed', 'pending')), and referential integrity checks between tables. In dbt, these are defined in schema.yml and run with dbt test. In Spark, use the great_expectations library to define and evaluate expectations as part of the job.
At the sink: row count checks (did the number of rows written match the number of rows read from source?), null rate checks on key metrics, and business rule validation (total settled amount for the day must be positive, average transaction value must be in a reasonable range).
Monitoring and alerting: track quality metrics over time โ null rates, duplicate rates, value distribution โ and alert on anomalies. A 5x spike in null rates in a column is a data quality incident even if no explicit check fails. Tools: Great Expectations, dbt, Monte Carlo, Soda Core.
A data contract is a formal, versioned agreement between a data producer (the team or system that generates data) and data consumers (the teams or systems that use it) that defines what the data looks like, what quality guarantees it carries, and what the producer commits to maintaining. Think of it as an API contract, but for data.
A data contract typically specifies: the schema (field names, types, required vs. optional); semantic meaning of each field; quality guarantees (null rate below 1%, uniqueness, freshness SLA โ data updated within 15 minutes); backward compatibility policy (how many versions the producer will maintain before breaking changes); and ownership (who to contact if there's a problem).
Data contracts solve a recurring organizational problem: the payments team changes the schema of their Kafka topic without notice, and three downstream teams discover their pipelines are broken only when dashboards go red. With a contract, schema changes are versioned, consumers are notified, and breaking changes require a deprecation period. In practice, data contracts are implemented as YAML or JSON files stored in a Git repository, validated in CI/CD (the producer's deploy pipeline checks that the new schema is backward-compatible with the contract version in use by consumers). Tools: Soda (data contracts), AsyncAPI, OpenAPI for event schemas, or custom schema registry tooling.
A data warehouse (e.g., Snowflake, BigQuery, Redshift) stores data in a structured, schema-on-write format optimized for SQL analytics. Data is validated, transformed, and loaded via ETL before it is queryable. Warehouses are fast for analytics queries on structured data, enforce data quality at write time, and are easy for non-engineers to query. Downsides: expensive for storing raw or semi-structured data, difficult to work with unstructured data (images, audio, logs), and require all data to be modeled before it can be queried โ slow to onboard new data sources.
A data lake (e.g., S3 + Parquet, HDFS) stores any data โ raw JSON, CSV, Parquet, images โ in a schema-on-read format. Cheap object storage. Highly flexible โ you can always re-process historical data when requirements change. Downsides: no enforced schema means data quality degrades over time ("data swamp"), query performance is worse than a warehouse for ad hoc SQL, and governance is harder (who owns what? what's current?).
The modern answer is the lakehouse (Delta Lake, Apache Iceberg, Apache Hudi): object storage with warehouse-quality query performance, ACID transactions, schema enforcement, and time travel. You get cheap storage with fast SQL, the ability to update and delete records (not possible with raw Parquet), and schema evolution. DuckDB, Spark, Athena, and Trino can all query Iceberg/Delta tables directly. Most new data platform architectures in 2025โ2026 are lakehouse-first.
๐๏ธ System Design
Requirements clarification: 50,000 transactions per second at peak; fraud decision must be returned in under 100 ms (synchronous, blocks the payment); false positive rate below 0.1%; system must be auditable.
Architecture: Payment Gateway โ Kafka topic payments.raw (partitioned by card_id for ordering) โ Fraud Scoring Service (Flink or Spark Structured Streaming with micro-batches of 50 ms) โ feature extraction (velocity checks: how many transactions in last 60 seconds for this card? location mismatch? merchant category vs. user history) โ ML model scoring (a gradient-boosted model served in-process for low latency) โ decision written to Kafka topic fraud.decisions โ Payment Gateway reads decision synchronously via Kafka consumer with 100 ms timeout.
Feature store: real-time features (card velocity, running 5-minute spend) stored in Redis with TTL. Historical features (30-day merchant category distribution, country frequency) pre-computed in a batch Spark job and served from Redis or Cassandra. The streaming job reads features from Redis + the current transaction to score.
Exactly-once semantics: every fraud decision is keyed on txn_id and written as an upsert โ retries don't create duplicate decisions. The model version and feature values used for each decision are stored in Cassandra for audit and model retraining purposes.
Failure handling: if the scoring service is down or takes more than 80 ms, default to "approve with flag for async review" โ rejecting payments during a service outage is worse for the bank than allowing a small number of fraudulent ones. Circuit breaker pattern at the gateway level.
Purpose: every night, compare the company's internal records of settled transactions against the bank's clearing file to find discrepancies (missing transactions, amount mismatches, duplicate settlements). Produce a reconciliation report for the finance team by 6 AM.
Architecture: (1) Ingestion: at midnight, pull the bank clearing file (CSV or SWIFT MT940) from SFTP into MinIO/S3. Airflow DAG orchestrates this. (2) Bronze layer: parse the raw file with PySpark and write to Parquet, partitioned by settlement_date. (3) Silver layer: join the bank clearing records against the internal payments table (source: Cassandra or a data warehouse extract). Join key is txn_id. Produce one row per transaction with columns: internal_amount, bank_amount, match_status (MATCHED / AMOUNT_MISMATCH / MISSING_IN_BANK / MISSING_IN_INTERNAL). (4) Gold layer: aggregate mismatch counts and total mismatched amounts by merchant and currency. Write to a PostgreSQL reporting database. (5) Alerting: if total mismatch amount exceeds a threshold (e.g., USD 10,000), send an alert to the operations team via PagerDuty or Slack webhook before the report is delivered.
Idempotency: the Spark job writes using mode="overwrite" for the date partition โ re-running the job for the same date overwrites the previous output, not adds to it. The Airflow task is marked idempotent: running it twice for the same date produces the same result.
SLA management: Airflow SLA miss callbacks notify ops if any task in the DAG doesn't complete by 5:30 AM โ giving 30 minutes to diagnose and fix before the 6 AM delivery deadline.
Scale: 200 microservices, each logging ~10,000 lines/second at peak. 2 TB of raw logs per day. Retention: 7 days hot (searchable), 90 days cold (archival). Query latency: full-text search within 2 seconds for the last 24 hours.
Ingestion: each service writes structured JSON logs (fields: service_name, level, trace_id, message, timestamp). A Filebeat or Fluentd sidecar ships logs to Kafka (logs.raw topic, partitioned by service_name, 50 partitions).
Processing: Logstash or a custom Kafka consumer reads from logs.raw, enriches each log with environment metadata (cluster, region, deploy version), and indexes into OpenSearch. Use index aliases with date-based indices (logs-2026.01.15) for efficient rollover and deletion. A separate Spark Structured Streaming job reads from Kafka and aggregates error rates per service per minute, writing to a time-series store (InfluxDB or TimescaleDB) for alerting dashboards.
OpenSearch configuration: 3 hot nodes (SSD) for the last 7 days of indices. 3 warm nodes (HDD) for days 8โ90. Use Index Lifecycle Management (ILM) to automatically move indices from hot to warm and delete after 90 days. Disable _source compression for hot indices for fast retrieval; enable for warm.
Search UI: OpenSearch Dashboards (Kibana-compatible) for log search. Trace ID field is a clickable link that shows all log lines for a distributed request across all services โ critical for debugging in a microservices environment.
A consumer that is falling behind has growing consumer lag โ the difference between the latest produced offset and the consumer's committed offset is increasing rather than staying stable or decreasing. First, diagnose before scaling.
Diagnosis: check kafka-consumer-groups.sh --describe --group my-group. Is lag growing on all partitions or just a few? Growing on all partitions = the consumer is simply too slow. Growing on one or two = skewed partition processing (one partition has a hot key consuming all CPU in one consumer instance). Check CPU, memory, and I/O on the consumer host โ is the bottleneck in the processing logic or the downstream write?
Horizontal scaling: if partitions > current consumer instances, simply add more instances to the same consumer group โ Kafka will rebalance and assign idle partitions to the new instances. Maximum parallelism = partition count. If you already have one consumer per partition, you need to increase the partition count first (then add consumers). Note: you cannot go below the current partition count, and increasing partitions may break message ordering for keys.
Vertical / processing optimization: batch the downstream writes (instead of one DB write per message, collect 500 messages and bulk-insert โ typically 10โ50x faster). Parallelize the processing within a single consumer using a thread pool. Remove unnecessary blocking I/O in the processing logic. Use async commits instead of synchronous commits if at-least-once is acceptable. For extreme cases (10M messages behind), use a dedicated catch-up consumer with a different auto.offset.reset configuration to process historical data in parallel with the live consumer.
Requirements: 500 customers (tenants), each with their own data that must not be visible to other tenants. Some tenants have 10 rows/day; one has 50 million rows/day. Self-service analytics via a SQL interface. Cost must scale with usage, not tenant count.
Isolation strategy โ shared tables with tenant column: all tenants share the same physical tables. Every table has a tenant_id column. Row-level security (RLS) in the query engine ensures each tenant's SQL session can only see their own rows. This is cost-efficient (one table, not 500) and operationally simple. Risk: a misconfigured RLS policy leaks data across tenants โ requires rigorous testing and a security review for every schema change. Implement defense-in-depth: RLS in the warehouse + tenant ID scoping in every application query + a test suite that verifies isolation for every table.
Storage: Parquet on S3 partitioned by tenant_id/year/month/day/. Small tenants share a partition file; large tenants get dedicated sub-partitions. Use Iceberg for ACID, time travel (tenants can query "as of yesterday"), and partition evolution. Query layer: Athena or Trino for ad hoc SQL; dbt for transformation.
Cost isolation: tag every query with the tenant ID. Use query metering to bill tenants per compute second consumed. Large tenants pay more; small tenants pay almost nothing. Set per-tenant query timeout and max result set limits to prevent a single tenant from consuming all resources.
Migrating a monolithic ETL (a single large PL/SQL script or a legacy Informatica job that does everything) to a modern lakehouse is a multi-month project. The key is to do it incrementally, not as a big-bang rewrite.
Phase 1 โ Understand the current system: document every data source the ETL reads from, every transformation it applies, and every downstream table or report it produces. Run the existing system and the new system in parallel and compare outputs. You cannot migrate what you don't understand.
Phase 2 โ Build the bronze layer first: replicate all raw sources into object storage (S3/MinIO) using CDC (Change Data Capture with Debezium for databases) or batch exports. This builds the data lake without changing anything the business sees. Bronze = exact copy of source with audit fields (ingested_at, source_system).
Phase 3 โ Migrate transformations to dbt, starting from gold: counterintuitively, start with the final output (the reports the business uses) and work backwards. Define the gold models in dbt and verify they match the current ETL output exactly. Then migrate silver (cleaning, deduplication) and bronze is already done. This approach finds gaps in business logic documentation early and keeps business stakeholders engaged (they can see their reports improving).
Phase 4 โ Cut over one domain at a time: migrate payments first (since you know it best), verify for 4 weeks, then migrate merchants, then customers. Never cut over everything at once. Keep the old ETL running in parallel until each domain is validated. When you decommission the old ETL for a domain, you celebrate โ not before.
A data quality monitoring system has three parts: definition (what should the data look like?), measurement (does the data meet that definition?), and alerting (notify the right people when it doesn't).
Definition: codify quality expectations as rules in a declarative format. In dbt: schema.yml with not-null, unique, accepted-values, and relationships tests. For statistical rules: Great Expectations expectations suites (mean within 2 standard deviations of historical mean, null rate below 1%, row count within 10% of yesterday's count). Store rule definitions in Git so changes are versioned and reviewed.
Measurement: run quality checks as part of every pipeline run โ after each dbt model builds, run dbt test --select that_model. For streaming pipelines, run quality checks as a Spark Structured Streaming micro-batch aggregation in parallel with the main pipeline. Collect metrics (pass/fail per rule, failure count, failure rate) and write them to a quality metrics table in PostgreSQL or the warehouse.
Alerting and observability: build a Grafana dashboard on the quality metrics table showing quality scores over time per table and column. Alert (PagerDuty/Slack) if a quality score drops below threshold or if a critical column goes from 0% null rate to any null rate. Implement anomaly detection for volume: if the payments table receives 50% fewer rows than the 7-day average for the same hour, alert immediately โ this indicates an upstream ingestion problem, not a quality problem in the data itself.
True end-to-end exactly-once semantics across a full pipeline (Kafka โ Spark โ Cassandra) requires coordinating deduplication at every boundary, because exactly-once is a property of the entire system, not just individual components.
At the Kafka producer (source โ Kafka): enable enable_idempotence=True and use acks='all'. The idempotent producer uses PID + sequence numbers to prevent duplicate messages caused by producer retries. Use transactional_id if writing to multiple partitions atomically.
At the Kafka consumer (Kafka โ Spark): in Spark Structured Streaming, use trigger(once=True) or trigger(availableNow=True) for micro-batch processing with checkpointing. Spark writes offsets to a checkpoint directory and only commits them after the output is written atomically. This provides exactly-once between Kafka and the Spark output โ as long as the sink supports idempotent or transactional writes.
At the sink (Spark โ Cassandra): Cassandra does not support distributed transactions with Spark, so use the upsert pattern: write every row as an INSERT (which is always an upsert in Cassandra), keyed on the stable txn_id. Re-running the job with the same data overwrites the same rows โ net result is identical to exactly-once. This is "effectively exactly-once" โ the end-state of the sink is correct even if the job ran twice.
The full picture: true transactional exactly-once from Kafka to external databases is possible only if the external database supports distributed transaction coordination (two-phase commit). In practice, production systems use idempotent producer + Spark checkpointing + upsert sink, which is operationally simpler and achieves the same correctness guarantee for all stateless transformations.
โ Key takeaways
- Practice saying answers aloud โ the gap between reading an answer and being able to deliver it clearly under pressure is enormous. Record yourself and listen back.
- Do EASY questions first to build vocabulary and confidence, MEDIUM next, and tackle HARD and system design on Day 2 with a whiteboard or paper.
- The strongest interview answers include a real example from something you built โ replace the generic examples in these answers with your actual track project.
- For system design questions: always clarify scale requirements first, draw the components, identify failure modes, then discuss trade-offs. Interviewers reward structured thinking over memorized answers.
- ATS keyword tip: every term in the "Resume Keywords" callout above should appear at least once in your CV in context. Don't just list them โ describe a project where you used each one.
Ready to go further?
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 โ