Ingest and transform data: free practice questions

Option A is correct because a broadcast join sends the small dimension table to every executor as an in-memory copy, completely eliminating the shuffle exchange that occurs when Spark tries to co-locate matching rows across partitions. This is the standard PySpark optimization for skewed or slow joins when one side is small enough to fit in memory. Option B is incorrect because increasing shuffle partitions only redistributes the shuffle work across more partitions; it does not eliminate the shuffle itself and may introduce overhead for small datasets. Option C is incorrect because repartitioning the large fact table to match the small table's partition count would reduce parallelism dramatically and still does not remove the shuffle; it would likely make performance worse. Option D is incorrect because caching the large fact table helps if it is reused multiple times, but does not address the root cause — the shuffle during the join itself.

A Spark Structured Streaming notebook is the only option that cleanly satisfies all four requirements in a true streaming fashion. Requirement 1 (enrichment with Delta table metadata): Spark supports stream-static joins, where a streaming DataFrame is joined against a static Delta table DataFrame — this is a well-supported pattern. Requirement 2 (5-minute tumbling window aggregations): Structured Streaming's window() + groupBy() + withWatermark() pattern handles exactly this. Requirements 3 and 4 (dual sinks): Structured Streaming supports foreachBatch() or multiple writeStream sinks to write simultaneously to a Lakehouse Delta table and a KQL Database. The Eventstream approach (Option A) has limitations: Eventstream derived stream transformations support basic filtering and projection but do not natively support stream-to-static Delta table joins for enrichment at the required scale; also routing to both Lakehouse and KQL from the same stream would require forking, which adds complexity and the enrichment join is not supported in Eventstream's transformation layer. The pipeline/Copy Data approach (Option C) is fundamentally a batch approach — copying every 5 minutes is not true streaming and introduces up to 5 minutes of latency, violating the spirit of a streaming architecture. The KQL-based approach (Option D) — using KQL update policies and scheduled exports — is a reasonable approximation but KQL update policies are triggered on ingestion, not on true time windows, and exporting back to Lakehouse on a schedule is again batch behavior. KQL update policies also cannot perform stream-static joins against a Lakehouse Delta table.

df.dropDuplicates() removes exact duplicate rows across all columns, and df.dropna(subset=['TransactionID']) drops any rows where TransactionID is NULL — chaining these two operations correctly satisfies both requirements. df.distinct() also removes duplicate rows (equivalent to dropDuplicates() with no arguments), and dropna(subset=['TransactionID']) handles NULLs correctly, so this option is also technically valid — however, dropDuplicates() is the more explicit and idiomatic PySpark method. df.dropDuplicates() combined with filter(df.TransactionID != None) uses Python's None for NULL comparison, which does not work correctly in PySpark — NULL comparisons require isNotNull() or dropna(); this is a common misconception. df.fillna({'TransactionID': 0}).dropDuplicates() replaces NULLs with 0 rather than dropping those rows, which violates the requirement to drop NULL TransactionID rows and could introduce false data.

Watermarking in Spark Structured Streaming tells the engine the maximum expected lateness of data. Setting a 15-minute watermark means the engine retains state for each window for 15 minutes beyond the window's end time, allowing late events to be included in the correct window, after which old state is safely dropped. Option A (reducing shuffle partitions) affects parallelism but has no bearing on late-data handling or state management. Option C (`trigger(once=True)`) processes available data in a batch-like fashion but does not handle lateness logic — late records would simply be missed if they arrive after the trigger fires. Option D (Delta merge-on-read) is a storage-level concept unrelated to streaming late-data handling. Option E (checkpoint + failOnDataLoss=false) only prevents stream failure when source offsets are missing; it does not implement any late-data buffering.

Writing invalid records to a quarantine (or 'reject') Delta table satisfies both requirements: the bad records are preserved for investigation without being propagated to the Gold layer. This is the standard pattern for handling missing required keys in medallion architectures. Option A is wrong because dropping records and only logging a count permanently discards data needed for investigation, violating the preservation requirement. Option C is wrong because loading records with a sentinel key into the Gold layer violates the business rule that these records must not reach that layer, and it pollutes the fact data for downstream consumers. Option D is wrong because halting the entire pipeline on any NULL would block valid records from loading and is operationally impractical for a 3% error rate; isolation and quarantine is preferred over a full stop.