DP-900 Describe an analytics workload on Azure Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Use a directory structure that enables partition elimination

  Why this is correct

  Directory-scoped partition elimination works because ADLS Gen2's hierarchical namespace lets query engines (Synapse Serverless SQL, Spark, Databricks) use folder paths as partitions. When a query filters on partition columns—say date=2025-03-08 and sensorID=42—the engine enumerates only those subdirectories and ignores all other folders. For IoT data, this transforms a full-dataset scan into a targeted read, dramatically reducing bytes transferred and query time.

• ✗

  Create a view that filters on date and sensor ID

  Why it's wrong here

  A view in SQL merely stores a query definition; it has no physical manifestation in the data lake. When you query the view, the engine still loads and scans every Parquet file under the original root, because the view's predicate is applied only after initial file discovery. Views are useful for permissions and query simplification, but they cannot modify the storage layout or prune directories at the engine's file enumeration stage.

• ✗

  Read all Parquet files and filter using a WHERE clause

  Why it's wrong here

  Using a WHERE clause alone forces the reader to open every Parquet file in the lake, even if predicate pushdown lets Parquet skip some row groups inside each file. The file discovery and listing of tens of thousands of files dominate I/O cost, and network latency scales with the number of files, not just bytes. Partition elimination avoids this by pruning entire directories, whereas WHERE-only filtering already paid the price of listing and opening irrelevant files.

• ✗

  Create an index on the date and sensor ID columns

  Why it's wrong here

  Azure Data Lake Storage Gen2 is object storage and does not support native secondary-index creation as a transactional database would. While you could build a separate index store (for example, in Hive or Azure Data Explorer) to map column values to file paths, that introduces extra metadata management and lookup latency, and it still requires a storage-level read after the index lookup. Parquet files already carry min/max statistics in their footer, enabling some statistics-based pruning—but for time- and sensor-range queries, directory partitioning gives orders of magnitude better I/O reduction without the overhead of maintaining an external index.

Go deeper

Related to this question