Microsoft DP-700: Monitor and optimize an analytics solution Practice Questions

Incremental refresh on FactSales means only new or changed rows (e.g., the last N days) are imported on each scheduled run, reducing the per-refresh volume from 800 million rows to a fraction. This is the primary recommended approach for large fact tables. Semantic model partitioning (XMLA/TMSL-based table partitioning) allows the refresh to process multiple partitions of FactSales in parallel, further reducing wall-clock refresh time. Switching to DirectQuery eliminates import refresh but degrades query performance and removes in-memory aggregation benefits — it also fundamentally changes the dataset behavior, which is not the stated goal. Reducing refresh frequency does not fit the data within the 60-minute window — it just refreshes less often. Splitting FactSales into a separate dataset adds architectural complexity and still requires refreshing 800 million rows; it does not reduce the refresh time. Increasing the query timeout does not speed up the refresh — it only changes when the engine gives up waiting; it does not address the root cause.

Increasing the Eventstream partition count increases parallelism in the ingestion processing layer, directly reducing per-partition queue depth and latency at high throughput. Enabling streaming ingestion on the Eventhouse KQL destination reduces the default micro-batching delay, allowing data to be available for query sooner. Pre-aggregating with a Tumbling Window reduces write volume but also reduces data granularity and doesn't specifically target latency. Switching to a Lakehouse destination typically increases latency rather than reducing it, as Lakehouse writes are batched. Replacing Eventstream with a polling pipeline is a batch approach and would dramatically worsen latency. Filtering events may reduce volume but is a data-loss approach and does not address architectural latency.

The Fabric Monitoring Hub provides a centralized view of all item activity across a workspace, including pipeline runs, notebook executions, and dataflow refreshes, with status, duration, and error details. It is the correct single-location tool for this requirement. The Workspace Settings Governance tab manages sensitivity labels and endorsements, not run history. Azure Monitor Logs integration is possible but requires additional configuration and is not a native single-location Fabric feature for pipeline monitoring. The individual pipeline Run History panel requires navigating to each pipeline separately, so it does not satisfy the 'single location' requirement.

Running OPTIMIZE on the Silver table compacts 900,000 small files into a manageable number of large files. The Gold notebook's MERGE operation must scan the Silver table, and small files cause excessive Parquet footer reads and task overhead — this is a primary contributor to the timeout. Enabling Delta log checkpointing (log compaction) creates periodic checkpoint Parquet files that summarize the transaction log state, eliminating the need to replay 2.5 million JSON log entries on every Delta table read. This directly reduces the metadata overhead that slows both the Silver table state computation and the Gold MERGE planning phase. Running OPTIMIZE with ZORDER BY (customerId) physically co-locates customer records within files, which directly accelerates the SCD Type 2 MERGE join predicate (typically joining on customerId), enabling data skipping to reduce the number of files the MERGE must read. VACUUM removes files that are no longer referenced by the Delta log, but it does not reduce the number of active transaction log entries or compact active small files — it addresses storage cost, not the timeout root cause. Switching to INSERT OVERWRITE avoids MERGE complexity but breaks SCD Type 2 logic (which requires tracking historical versions) and is architecturally incorrect. Increasing the session timeout is a workaround that masks the root cause and does not fix the underlying performance problem.

OPTIMIZE compacts small files into larger, more efficient Parquet files, directly addressing the 500-files-per-hour accumulation. VACUUM RETAIN 168 HOURS (7 days × 24 hours) removes Parquet files that are no longer referenced by any Delta log entry within the 7-day retention window, reclaiming storage. This combination is the standard maintenance pattern. MERGE INTO is a DML operation, not a maintenance command, and VACUUM RETAIN 0 HOURS is dangerous as it breaks time travel and may remove files needed by active readers. DESCRIBE HISTORY is a diagnostic command, not maintenance. Running VACUUM before OPTIMIZE would remove files that OPTIMIZE might still need to reference during compaction planning.