Microsoft DP-750: Prepare and process data Practice Questions

Liquid clustering is the recommended modern approach for multi-dimensional query patterns on large Delta tables in Unity Catalog. It incrementally reorganizes data as new records arrive, avoids over-partitioning pitfalls, and efficiently co-locates data for any combination of the specified clustering columns. Partitioning on event_date plus Z-order is a valid older pattern but requires manual OPTIMIZE runs and Z-order does not colocate data as effectively for highly varied multi-column queries. Composite partitioning on region and product_category would create a very large number of small partitions and does not help with date-based filters. Z-order alone without partitioning on a 2 TB table would make every query scan the entire table before benefiting from data skipping.

Delta time travel with TIMESTAMP AS OF allows querying a Delta table as it existed at any prior timestamp within the retention window (default 30 days), requiring zero additional ETL. This is the native, built-in mechanism for point-in-time reads. Deletion vectors track row-level deletes for performance purposes but do not independently reconstruct historical table states for querying. Delta clones create a copy at a moment in time but must be created proactively before the need arises — you cannot retroactively create a clone 72 hours in the past. An SCD Type 2 approach would work conceptually but requires significant schema and ETL redesign upfront and does not leverage the existing table.

Liquid clustering is the modern replacement for Z-ordering in Delta Lake. It incrementally clusters data as writes occur, eliminating the need for periodic full OPTIMIZE runs to maintain data layout. This directly addresses the file fragmentation caused by frequent merges. Nightly OPTIMIZE with ZORDER is a viable but less efficient approach — it requires manual scheduling and performs a full rewrite of files each run, which is costly. Increasing shuffle partitions affects Spark task parallelism and may slightly influence file sizes but does not address the root cause of clustering degradation. Deletion vectors reduce write amplification for deletes by marking rows as deleted rather than rewriting files, which helps delete performance but does not compact existing small files or improve scan performance for large queries.

The PIVOT clause in Spark SQL is the direct, idiomatic way to transpose rows into columns. The syntax SELECT * FROM sales PIVOT (SUM(sales_amount) FOR month IN ('Jan', 'Feb', 'Mar')) aggregates sales_amount per category for each specified month value, producing one output column per month. The CASE WHEN approach using conditional aggregation is a valid but verbose alternative that would require writing a separate CASE expression for every month, making it harder to maintain. UNPIVOT performs the reverse operation — it converts columns into rows, the opposite of what is needed here. Window functions like RANK() calculate ordinal positions within partitions; they do not transpose rows into columns and cannot produce the pivoted output.

A Lakeflow Spark Declarative Pipeline with Auto Loader in batch trigger mode scheduled every 15 minutes is the best fit. It processes all new files accumulated since the last run, meets the 15-minute latency SLA, minimizes infrastructure management through the managed pipeline service, and handles Avro ingestion natively. Structured Streaming with a 5-minute trigger would also meet the latency requirement but runs a persistent cluster even when no files arrive, increasing cost and management burden. COPY INTO in a scheduled workflow every 5 minutes adds operational overhead of managing a job schedule and does not benefit from Auto Loader's file-tracking and scalability improvements. Continuous trigger mode processes data with millisecond latency and requires a continuously running cluster, which is massively over-engineered for a 15-minute latency tolerance and incurs unnecessary cost.