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.