A Spark job joining a 2 TB fact table to a small dimension runs for hours, and the Spark UI shows one task in the final stage processing 400x more shuffle data than the median task while 199 tasks finished quickly. AQE is enabled. The join key `customer_id` has a null/`'UNKNOWN'` value for ~30% of fact rows. Which fix directly addresses the root cause?
- A. Increase `spark.sql.shuffle.partitions` from 200 to 2000 so the hot key spreads across more partitions
- B. Split the join: filter out the null/`'UNKNOWN'` keys, broadcast-join the remainder, and union the skewed rows handled separately (or salt the hot key) ✓
- C. Enable `spark.sql.adaptive.coalescePartitions.enabled` so AQE merges the small partitions
- D. Cache the dimension table with `.persist(MEMORY_AND_DISK)` before the join
Correct answer: B. All rows sharing the single hot key hash to one partition regardless of partition count, so only isolating/salting that key (or broadcasting) redistributes its load.
You maintain an SCD Type 2 dimension. The source system updates records in place and reuses the same natural key, and there is NO reliable `updated_at` timestamp. You must detect real changes and close/open versions correctly. Which approach is most robust?
- A. Compare the full row to the current version using a hash of all tracked attributes; if the hash differs, close the current row (set end_date) and insert a new current version ✓
- B. Trust the source's `updated_at` column and only process rows where it is greater than the last load watermark
- C. Truncate the dimension each run and reload it fully from the source's current state
- D. Use `SELECT DISTINCT` on the natural key to remove duplicates before loading
Correct answer: A. With no trustworthy timestamp, a hash-based comparison of tracked attributes against the current version is the standard way to detect genuine changes and drive SCD2 version transitions.
A Kafka consumer group processes payments with `enable.auto.commit=true` and default `auto.commit.interval.ms=5000`. The application reads a batch, writes to a database, and occasionally crashes mid-batch. On restart you observe some payments were processed twice. What is the correct cause and fix?
- A. Auto-commit can commit offsets for records that were fetched but not yet fully processed; disable auto-commit and commit offsets manually only after the DB write succeeds (ideally in the same transaction / idempotent write) ✓
- B. The consumer has too few partitions; add partitions so each record is processed once
- C. Set `isolation.level=read_committed` on the consumer to prevent duplicate processing
- D. Increase `max.poll.interval.ms` so the consumer is not kicked out of the group during processing
Correct answer: A. Auto-commit advances offsets on a timer regardless of processing completion, so a crash after commit but before the DB write replays; manual commit after the sink write (plus idempotency) fixes it.
In a Flink event-time pipeline computing 1-minute tumbling windows of clickstream data, you set a bounded-out-of-orderness watermark of 10 seconds. Events routinely arrive up to 3 minutes late due to mobile clients going offline. What is the observable consequence of this configuration?
- A. Windows will never fire because the watermark can never advance past late events
- B. Late events beyond the 10-second bound are dropped (or diverted to side output), so window aggregates for those minutes are silently undercounted ✓
- C. The watermark forces all late events into the current processing-time window, corrupting later windows
- D. Flink automatically extends the watermark to 3 minutes once it detects the late events
Correct answer: B. A watermark defines when a window is considered complete and fired; events later than the 10s bound arrive after their window closed and are dropped unless captured via allowed lateness or a side output.
A nightly backfill job re-runs the last 30 partitions of a Delta table and appends rows with `INSERT INTO`. Operators frequently retry failed runs. You are seeing duplicated rows for reprocessed dates. Which change makes the job idempotent for safe reruns?
- A. Add `OPTIMIZE` and `VACUUM` after each write to compact and remove duplicates
- B. Switch the write to a `MERGE INTO ... WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT` keyed on the business+partition key, or replace-by-partition with `replaceWhere` ✓
- C. Wrap the `INSERT INTO` in a `try/except` so failed runs do not append
- D. Enable Delta's automatic schema evolution with `mergeSchema=true`
Correct answer: B. An append is inherently non-idempotent on retry; a keyed MERGE or a partition-scoped overwrite (`replaceWhere`) makes reprocessing produce the same final state regardless of how many times it runs.
A real-time fraud system must return a decision in under 100ms at ~5,000 events/sec with strict correctness on account balances. A teammate proposes exactly-once end-to-end via a two-phase-commit sink to the downstream API. Under the latency SLA, which reasoning is soundest?
- A. Exactly-once via 2PC is always required for money, so accept the latency cost of the transactional coordinator
- B. Use at-least-once delivery with an idempotent sink (dedup by event_id/request key), since 2PC's coordination adds latency and the idempotent sink prevents duplicate effects ✓
- C. Use at-most-once to guarantee sub-100ms, accepting that some fraud events are dropped
- D. Exactly-once and at-least-once are identical in throughput, so pick based on team familiarity
Correct answer: B. Distributed transactional commit adds coordination latency that threatens the SLA; at-least-once plus an idempotent (dedup-keyed) sink achieves effectively-once results without the 2PC cost.
You register Avro schemas in a Schema Registry set to BACKWARD compatibility. A producer team wants to add a new field `loyalty_tier` to an event that existing consumers must keep reading without redeployment. What must they do for the change to be accepted and safe?
- A. Add `loyalty_tier` as a required field with no default; BACKWARD compatibility allows adding required fields
- B. Add `loyalty_tier` with a default value so consumers using the new schema can read old data lacking the field ✓
- C. Rename the event's existing `tier` field to `loyalty_tier`; renames are backward compatible
- D. Remove an unused optional field in the same change to offset the addition
Correct answer: B. Under BACKWARD compatibility a new-schema reader must read data written with the old schema, which requires the added field to have a default so missing values resolve.
A dbt model deduplicates a CDC stream keyed on `order_id`, keeping the latest state per key. The current SQL is `SELECT DISTINCT * FROM raw_orders`. Analysts report both stale and current versions of some orders appear. What is the correct fix?
- A. `SELECT DISTINCT ON (order_id) * FROM raw_orders ORDER BY order_id` in standard ANSI SQL
- B. Use `ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY event_ts DESC, op_seq DESC)` and keep rows where the rank = 1 ✓
- C. Add `GROUP BY order_id` and select the remaining columns without aggregation
- D. Replace `SELECT DISTINCT *` with `SELECT DISTINCT order_id`
Correct answer: B. `DISTINCT *` only removes fully identical rows, so different versions of the same order survive; windowing by key ordered by event time/sequence and keeping rank 1 selects the true latest state.
A daily pipeline technically succeeds every run (no errors, no failed tasks), but a downstream revenue dashboard shows a 40% drop for one region. Investigation reveals an upstream API silently started returning `null` for `amount` on ~40% of that region's rows. Which control would have caught this BEFORE the dashboard did?
- A. A retry policy with exponential backoff on the extraction task
- B. A data-quality assertion on null-rate / distribution of `amount` per region that fails or alerts when it deviates from the historical baseline ✓
- C. Increasing the Airflow task timeout so the job has more time to complete
- D. A schema check that the `amount` column still exists and is numeric
Correct answer: B. The job succeeded and the schema was unchanged, so only a value-level quality check (null-rate/volume anomaly vs. baseline) detects a silent correctness failure of this kind.
An Airflow DAG has a task `load_gold` that reads yesterday's silver partition. It is configured with `retries=3` and `depends_on_past=False`, and the sensor waiting on the silver partition uses `poke` mode with a 6-hour timeout. During a Spark cluster outage, dozens of DAG runs pile up and worker slots are exhausted. What is the primary design flaw?
- A. `depends_on_past=False` caused the runs to execute out of order
- B. `retries=3` is too low for a production pipeline and should be increased
- C. The `poke`-mode sensor holds a worker slot for up to 6 hours while idle-waiting, causing slot starvation; use `reschedule` mode (or a deferrable sensor) ✓
- D. The DAG needs `catchup=False` to prevent it from scheduling multiple runs
Correct answer: C. A poke-mode sensor occupies a worker slot the entire time it waits, so long timeouts across many runs exhaust the pool; reschedule/deferrable sensors free the slot between checks.
In Spark, why can a broadcast join outperform a sort-merge join?
- A. It always uses less total memory
- B. It avoids shuffling the large table by sending the small table to every executor ✓
- C. It requires both tables to be bucketed
- D. It disables serialization overhead
Correct answer: B. Broadcasting the small table to all executors lets each partition join locally, eliminating the large-table shuffle.
A CDC pipeline using log-based capture must handle a source row deleted then re-inserted with the same PK. What ensures correct final state downstream?
- A. Applying events by arrival time only
- B. Ordering and applying events by the source log sequence/LSN ✓
- C. Ignoring delete events entirely
- D. Deduplicating solely on primary key
Correct answer: B. Applying changes in source log-sequence (LSN/SCN) order guarantees the final state reflects the true event order.
In a lakehouse table format like Delta/Iceberg, how is a consistent snapshot read achieved during concurrent writes?
- A. Locking the entire table for readers
- B. Reading an immutable manifest/version so readers see a fixed snapshot ✓
- C. Disabling writes while any read runs
- D. Reading directly from the write-ahead log
Correct answer: B. These formats use versioned metadata/manifests, so a reader pins a snapshot and is isolated from in-flight commits.
Your Airflow DAG backfills 2 years daily but downstream API rate-limits you. Which is the most correct control?
- A. Increase parallelism to finish faster
- B. Set max_active_runs and task pool/concurrency limits ✓
- C. Remove retries to reduce calls
- D. Switch the schedule to @once
Correct answer: B. Limiting max_active_runs plus pools caps concurrent task execution to respect the downstream rate limit.
When is the 'exactly-once' claim of a system actually 'effectively-once' at the sink?
- A. When the sink is non-idempotent and retries occur
- B. When end-to-end idempotency/dedup makes duplicate deliveries harmless ✓
- C. When offsets are never committed
- D. When the producer disables acknowledgments
Correct answer: B. Most systems achieve effectively-once by making the sink idempotent/deduplicating, so redelivered records don't change state.
In a columnar format, predicate pushdown with min/max statistics primarily reduces cost by:
- A. Skipping row groups whose value range can't match the filter ✓
- B. Caching row groups in memory for reuse across queries
- C. Compressing string columns better
- D. Forcing a full table scan for accuracy
Correct answer: A. Per-row-group min/max stats let the reader skip groups that cannot satisfy the predicate, cutting I/O.
A star-schema fact table joins to a Type-2 dimension. To get the attribute value 'as of' the event, you should join on:
- A. The dimension's current surrogate key only
- B. The surrogate key captured at load time (point-in-time key) ✓
- C. The natural/business key alone
- D. The most recent dimension row by max effective date
Correct answer: B. Storing the SCD2 surrogate key valid at event time gives correct point-in-time attributes rather than current values.
You see duplicate rows after a Kafka-to-warehouse pipeline restart despite at-least-once semantics. The cleanest fix is:
- A. Lower the consumer's session timeout
- B. Use an idempotent upsert keyed on a stable event id ✓
- C. Increase the number of partitions
- D. Enable auto-commit before processing
Correct answer: B. Upserting on a stable unique event id makes reprocessed messages idempotent, eliminating duplicates.
For a slowly growing dimension queried heavily by BI, why might bucketing/clustering on the join key beat plain partitioning?
- A. It removes the need for statistics
- B. It co-locates matching keys to reduce shuffle/scan on joins without exploding partition counts ✓
- C. It guarantees ACID transactions
- D. It compresses better than any codec
Correct answer: B. Clustering/bucketing groups equal keys together, improving join and filter locality without high-cardinality partition sprawl.
In a distributed shuffle, which factor most directly drives spill-to-disk and OOM in a wide aggregation?
- A. Number of output columns
- B. High-cardinality group keys exceeding executor memory for the hash map ✓
- C. Using Parquet as input
- D. The presence of a LIMIT clause
Correct answer: B. Very high-cardinality grouping inflates the in-memory aggregation state, forcing spills and risking out-of-memory.
In a lakehouse table format like Apache Iceberg or Delta Lake, how is snapshot isolation for concurrent writers typically achieved?
- A. By locking the entire table for every write
- B. Via optimistic concurrency control with atomic metadata/manifest commits ✓
- C. By serializing all writers through a single node
- D. By disabling concurrent writes entirely
Correct answer: B. These formats use optimistic concurrency: writers prepare files then atomically swap metadata, retrying on conflict.
A Spark job suffers severe data skew where one key holds 80% of records, causing one task to run far longer than others. Which technique specifically addresses this?
- A. Increasing spark.sql.shuffle.partitions only
- B. Salting the skewed key to distribute it across multiple reducers ✓
- C. Switching to a broadcast join for the large-large join
- D. Caching the input RDD
Correct answer: B. Salting adds a random prefix to the hot key so its records spread across multiple partitions/tasks, balancing load.
In exactly-once stream processing with Kafka, what mechanism prevents duplicate side effects when a producer retries after an ambiguous failure?
- A. Consumer auto-commit
- B. Idempotent producer with a producer ID and sequence numbers plus transactional writes ✓
- C. Increasing replication factor
- D. Larger batch sizes
Correct answer: B. The idempotent producer deduplicates via PID+sequence numbers, and transactions make read-process-write atomic for exactly-once.
You must design a merge (upsert) into a partitioned Delta table where only a few partitions change per batch. What optimization minimizes rewritten data?
- A. Rewriting the entire table each merge
- B. Partition pruning in the merge predicate so only affected partitions are touched ✓
- C. Disabling the transaction log
- D. Converting the table to CSV before merging
Correct answer: B. Including partition columns in the merge condition lets the engine prune and rewrite only the affected partitions.
In an event-time streaming pipeline, what is the primary role of a watermark?
- A. To encrypt event payloads
- B. To bound how long the system waits for late data before finalizing a window ✓
- C. To assign partitions to consumers
- D. To compress state stored in memory
Correct answer: B. A watermark tracks event-time progress and defines the lateness threshold after which windows can be finalized and state cleared.
When implementing Change Data Capture from a relational source, which log-based approach is generally preferred over query-based polling and why?
- A. Polling a modified_at column, because it captures deletes reliably
- B. Reading the database transaction/WAL log, because it captures all changes including deletes with low source overhead ✓
- C. Full table snapshots each cycle, because they are cheapest
- D. Trigger-based capture, because triggers never affect write latency
Correct answer: B. Log-based CDC reads the WAL/binlog to capture inserts, updates, and deletes with minimal load and without missing changes between polls.
A star-schema query aggregating a 10-billion-row fact table by month is slow. Beyond partitioning by date, which storage-layout choice most improves scan performance for filtered analytical queries?
- A. Storing the fact table as row-oriented JSON
- B. Clustering/sorting (Z-ordering) columnar data on frequently filtered columns ✓
- C. Adding more OLTP-style B-tree indexes
- D. Increasing the block size to 1 GB uniformly
Correct answer: B. Sorting/Z-ordering colocates related values so predicate pushdown and min/max statistics skip more row groups during scans.
In Airflow, two tasks in the same DAG run must share a large intermediate dataset. Why is passing it through XCom generally a poor choice?
- A. XCom cannot pass any data between tasks
- B. XCom stores values in the metadata database and is meant for small values, not large datasets ✓
- C. XCom only works across separate DAGs
- D. XCom bypasses task dependencies
Correct answer: B. XCom persists to the metadata DB and is designed for small control values; large data should be passed via external storage with references.
You need at-least-once delivery from Kafka but observe data loss during broker failover. Which producer/broker configuration combination prevents acknowledged messages from being lost?
- A. acks=0 with replication.factor=1
- B. acks=all with min.insync.replicas>=2 and replication.factor>=3 ✓
- C. acks=1 with unclean.leader.election enabled
- D. acks=all with min.insync.replicas=1 and unclean.leader.election enabled
Correct answer: B. acks=all plus min.insync.replicas>=2 on a replication.factor>=3 topic ensures a write survives a single broker failure without loss.
A batch pipeline recomputes daily aggregates but late-arriving events for prior days must be corrected without full reprocessing. Which pattern best fits?
- A. Append-only immutable output with no recomputation
- B. Incremental merge/upsert into affected partitions using a lookback window ✓
- C. One-time full snapshot at pipeline start
- D. Storing all data only in memory
Correct answer: B. A bounded lookback window with idempotent upserts into affected partitions corrects late data without reprocessing everything.