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.

• ✓

  Switch to Azure Synapse dedicated SQL pool with proper table partitioning

  Why this is correct

  Switching to Azure Synapse dedicated SQL pool is correct because it uses a massively parallel processing (MPP) architecture that supports table partitioning. Proper partitioning on a frequently filtered column, such as date, enables partition elimination where the query optimizer prunes whole partition sets before scanning, significantly reducing I/O. In contrast, Synapse Serverless has no table partitions to eliminate—it must scan and filter the entire file set unless you use file-path-based pruning, which is far less robust for complex analytical workloads.

• ✗

  Create a clustered columnstore index on the external table

  Why it's wrong here

  Creating a clustered columnstore index on the external table is not possible because external tables in Synapse Serverless only reference files in Azure Data Lake Storage or Blob Storage; they do not store data locally. Clustered columnstore indexes are physical objects that can only be built on internal tables inside a dedicated SQL pool, not on external file-backed tables. Even if you could create one, it would compress and organize rows but would not provide partition elimination, which is the missing optimization needed for queries that filter on a specific column.

• ✗

  Convert the Parquet files to CSV format

  Why it's wrong here

  Converting Parquet files to CSV would be counterproductive because Parquet is a columnar format that applies compression algorithms like Snappy and stores schema in the file, enabling predicate pushdown so only matching columns and rows are read. CSV is a row-oriented text format that requires full parsing of every row and lacks native compression and type information, leading to higher storage costs and much slower scans on billion-row datasets. This conversion would slow down analytical queries rather than improve them, as it removes every performance benefit that Parquet provides.

• ✗

  Use Azure Databricks with Delta Lake for querying

  Why it's wrong here

  Using Azure Databricks with Delta Lake is not the correct answer because it would replace the query engine rather than enhance the existing Synapse Serverless environment. While Delta Lake supports data skipping and partitioning, the data would first need to be ingested into Delta tables and queries rewritten in Spark SQL, making it a separate solution that does not address the partition elimination limitation inherent to Synapse Serverless external tables. The question asks for a way to speed up the current queries; Databricks changes the platform entirely and introduces additional operational overhead, whereas switching to a dedicated SQL pool directly applies partitioning to the same SQL-based approach.

Go deeper

Related to this question