Microsoft DP-700: Ingest and transform data Practice Questions

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.

The correct KQL pattern uses summarize with count() grouped by bin(EventTimestamp, 1m). The bin() function rounds EventTimestamp values down to the nearest 1-minute boundary, effectively creating 1-minute tumbling windows, and summarize count() counts the events within each bin. The where clause correctly filters to the last hour. Summarizing by the raw EventTimestamp column (without bin()) produces one row per unique timestamp value rather than grouping into 1-minute windows — this would produce tens of thousands of rows with counts of 1, not windowed aggregations. Summarizing correctly but then adding order by without bin() still suffers from the ungrouped timestamp issue; ordering does not substitute for binning. Using extend with bin() and then distinct Window only returns the distinct window boundaries — it loses the count of events within each window, which is the actual requirement.

The two most likely reasons are: (1) The new table lacks a primary key — Fabric mirroring for Azure SQL Database requires source tables to have a primary key to track changes via change feed; tables without a primary key are excluded from replication. (2) The mirroring configuration was set up before the table was created, and newly added tables are not automatically included — the engineer must update the mirroring configuration to explicitly include the new table. A paused Fabric capacity would halt ALL mirroring activity, not just a single new table, and would affect previously mirrored tables as well — making it a less specific explanation for this symptom. OneLake mirroring replicates base tables, not views; this statement is incorrect as stated (it reverses the actual behavior). Azure SQL Database firewall blocking outbound connections would prevent mirroring from functioning at all for any table, not just the newly added one — and the existing tables are presumably replicating fine, ruling this out as the specific cause.

withWatermark('eventTime', '10 minutes') is the correct Spark Structured Streaming mechanism for handling late data. It tells the engine to wait up to 10 minutes beyond the maximum observed event time before finalizing a window, including late records that arrive within that threshold and automatically discarding (not including in output) records that arrive later than the watermark boundary. Setting trigger(processingTime='10 minutes') controls how frequently micro-batches are processed — it does not handle late-arriving data or event-time windows; it is about processing cadence, not event-time semantics. Manually filtering with current_timestamp() minus eventTime would drop late records entirely rather than including them in their correct event-time windows, and it conflates processing time with event time. Checkpointing is a fault-tolerance mechanism that saves streaming query progress for restarts; it does not control how late data is handled in windowed aggregations.

Options B and D are correct together. The Get Metadata activity with child items discovers all partition folders in the ADLS Gen2 container, and the Filter activity then compares those folder names/dates against the stored watermark to isolate only new partitions — forming the core of an efficient, idempotent incremental file ingestion pattern. Option C is incorrect because the Validation activity checks for file existence or size before downstream activities but does not help identify which partitions are new; it adds latency without solving the incremental filtering problem. Option E is incorrect because copying all files recursively from the root folder on every run is a full load, not an incremental pattern, and will duplicate data unless the target is truncated first. Option A is incorrect because deleting and reloading partitions on every run is a destructive pattern that negates incremental loading benefits and can cause data availability gaps during the pipeline window.