DP-900 Practice Question: Identify considerations for relational data on Azure

Answer analysis

Option-by-option breakdown

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

• ✗

  Azure SQL Database (Hyperscale tier)

  Why it's wrong here

  Hyperscale is an architecture for scaling storage and compute on a single logical SQL Server instance, but it is still fundamentally an OLTP engine: query execution happens on a single node, and although it supports columnstore indexes and read replicas, it does not use a distributed, massively parallel processing (MPP) plan to partition and scan large fact tables. Complex analytical queries typically require a query optimizer that can push aggregation and joins across many compute nodes, which Hyperscale does not provide. Thus, while Hyperscale handles very large transactional workloads, it is not an appropriate engine for heavy, ad-hoc analytical queries.

• ✗

  Azure Analysis Services

  Why it's wrong here

  Azure Analysis Services is an analytical data modeling and in-memory OLAP engine, not a query offload engine for running complex T-SQL workloads against Azure SQL Database. It imports or caches data into a compressed tabular model and exposes that model for reporting tools, so queries run against a pre-built semantic level rather than the raw relational source. That means it does not replace the underlying data warehouse or provide the scale-out query execution needed for large relational analytic processing; it is best paired with a dedicated data store such as Synapse, not used instead of one.

• ✗

  Azure Data Lake Storage

  Why it's wrong here

  Azure Data Lake Storage (Gen2) is a massively scalable hierarchical storage service for raw, semi-structured, and unstructured data; it stores files and objects and has no native query execution engine or SQL interface for answering complex analytic queries. You can point analytics compute services like Azure Synapse or Databricks at it, but by itself it cannot perform joins, aggregations, or distributed scans. Therefore, selecting ADLS as the place to run complex analytical queries confuses storage with compute and would not meet the relational analytics requirement.

• ✓

  Azure Synapse Analytics dedicated SQL pool

  Why this is correct

  Azure Synapse Analytics dedicated SQL pool is a purpose-built, massively parallel processing (MPP) data warehouse service that distributes each table across 60 compute distributions and uses clustered columnstore indexes to scan and aggregate large relational datasets efficiently. Unlike Azure SQL Database, it separates compute and storage and uses a control node to create and parallelize a distributed execution plan across compute nodes, making it ideal for complex analytical queries that would overwhelm an OLTP database. It is the correct choice when an organization needs to consolidate data from a source like Azure SQL Database into a scalable warehouse optimized for reporting and analytics.

Go deeper

Related to this question