Question 283 of 982

Describe an analytics workload on Azure →hardMultiple ChoiceObjective-mapped

Quick Answer

The answer is Azure Synapse Analytics with a dedicated SQL pool. This is the correct choice because it provides a massively parallel processing (MPP) engine that separates compute from storage, allowing the analytics team to run complex, long-running T-SQL queries joining millions of rows without impacting the OLTP performance of Azure SQL Database. Additionally, its serverless option means you only pay for queries executed, avoiding the need to provision fixed resources. On the DP-900 exam, this scenario tests your understanding of the difference between transactional (OLTP) and analytical (OLAP) workloads, with a common trap being to select Azure SQL Database itself or Azure Analysis Services. Remember the key differentiator: Synapse is built for petabyte-scale data warehousing with independent compute scaling, while Azure SQL Database is optimized for row-level transactions. A helpful memory tip is "Synapse for scale, SQL DB for sales"—Synapse handles heavy analytical lifting, while SQL DB manages daily transactions.

DP-900 Describe an analytics workload on Azure Practice Question

This DP-900 practice question tests your understanding of describe an analytics workload on azure. Match the stated requirement to the specific cloud service, access model, or configuration option — many options are valid in isolation but not for this scenario. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.

A retail company has an Azure SQL Database that handles OLTP transactions for its e-commerce platform. The analytics team needs to run complex reporting queries that join multiple tables (e.g., orders, products, customers) and aggregate millions of rows. These queries are long-running and would negatively impact the performance of the OLTP database if run directly. The company wants to use a separate analytics service that supports T-SQL queries, can scale compute independently, and provides a serverless option to avoid provisioning fixed resources. Which Azure service should they choose?

Question 1hardmultiple choice

Full question →

A
Azure Synapse Analytics (dedicated SQL pool)
Synapse Analytics provides a dedicated SQL pool with MPP architecture for complex queries on large datasets, supports T-SQL, and can be scaled independently. A serverless option is also available, but the dedicated pool is suited for consistent heavy workloads.
B
Azure Analysis Services
Why wrong: Azure Analysis Services is a semantic modeling platform that provides in-memory tabular models; it does not directly execute T-SQL queries against large datasets like a data warehouse.
C
Azure Databricks
Why wrong: Azure Databricks is a Spark-based analytics platform that uses Spark SQL (which is not standard T-SQL) and requires a different skill set; it is not the best fit for an organization that wants to use T-SQL directly.
D
Azure SQL Database (creating a secondary replica)
Why wrong: Even if a secondary replica is used for reporting, Azure SQL Database is not optimized for massive parallel processing of complex joins over billions of rows; it would still compete for resources and lacks the MPP architecture of Synapse.

Full breakdown with real-world context →

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Azure Synapse Analytics (dedicated SQL pool)

Azure Synapse Analytics (dedicated SQL pool) is the correct choice because it is a cloud-based analytics service that supports T-SQL queries, can scale compute independently from storage, and offers a serverless option (Synapse Serverless SQL pool) that eliminates the need to provision fixed resources. This allows the analytics team to run complex, long-running reporting queries against large datasets without impacting the performance of the OLTP Azure SQL Database.

Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.

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 Synapse Analytics (dedicated SQL pool)
Why this is correct
Synapse Analytics provides a dedicated SQL pool with MPP architecture for complex queries on large datasets, supports T-SQL, and can be scaled independently. A serverless option is also available, but the dedicated pool is suited for consistent heavy workloads.
Related concept
Read the scenario before looking for a memorised answer.
✗
Azure Analysis Services
Why it's wrong here
Azure Analysis Services is a semantic modeling platform that provides in-memory tabular models; it does not directly execute T-SQL queries against large datasets like a data warehouse.
✗
Azure Databricks
Why it's wrong here
Azure Databricks is a Spark-based analytics platform that uses Spark SQL (which is not standard T-SQL) and requires a different skill set; it is not the best fit for an organization that wants to use T-SQL directly.
✗
Azure SQL Database (creating a secondary replica)
Why it's wrong here
Even if a secondary replica is used for reporting, Azure SQL Database is not optimized for massive parallel processing of complex joins over billions of rows; it would still compete for resources and lacks the MPP architecture of Synapse.

Common exam traps

Common exam trap: answer the scenario, not the keyword

The trap here is that candidates may confuse Azure Analysis Services (a semantic layer) with a T-SQL query engine, or assume that a secondary replica of Azure SQL Database can independently scale compute and handle heavy analytics workloads without performance impact.

Detailed technical explanation

How to think about this question

Azure Synapse Serverless SQL pool uses a distributed query engine that reads data directly from data lake storage (e.g., Parquet, CSV) or Cosmos DB, paying only for the data processed per query, not for reserved compute. Under the hood, it leverages the same T-SQL surface area as Azure SQL Database but with a massively parallel processing (MPP) architecture for large-scale analytics, making it ideal for ad-hoc reporting without fixed resource provisioning. A real-world scenario is when the analytics team needs to run a multi-table join aggregating millions of rows from exported OLTP data stored in Azure Data Lake Storage Gen2, using T-SQL without managing any infrastructure.

KKey Concepts to Remember

Read the scenario before looking for a memorised answer.
Find the constraint that changes the correct option.
Eliminate answers that are true in general but not in this case.

TExam Day Tips

Watch for words such as best, first, most likely and least administrative effort.
Review why wrong options are wrong, not only why the correct option is correct.

Key takeaway

Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.

Real-world example

How this comes up in practice

A media company stores terabytes of video archives that are accessed once a year for audit purposes. Moving these objects to a cold storage tier (Azure Archive, S3 Glacier, or Google Nearline) costs a fraction of hot storage. Questions like this test whether you understand storage tiers, access frequency tradeoffs, and retrieval latency requirements.

What to study next

Got this wrong? Here's your next step.

Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.

Related DP-900 practice-question pages

Use these pages to review the topic behind this question. This is how one missed question becomes focused revision.

Describe core data concepts practice questions

Practise DP-900 questions linked to Describe core data concepts.

Describe an analytics workload on Azure practice questions

Practise DP-900 questions linked to Describe an analytics workload on Azure.

Identify considerations for relational data on Azure practice questions

Practise DP-900 questions linked to Identify considerations for relational data on Azure.

Describe considerations for working with non-relational data on Azure practice questions

Practise DP-900 questions linked to Describe considerations for working with non-relational data on Azure.

DP-900 fundamentals practice questions

Practise DP-900 questions linked to DP-900 fundamentals.

DP-900 scenario practice questions

Practise DP-900 questions linked to DP-900 scenario.

DP-900 troubleshooting practice questions

Practise DP-900 questions linked to DP-900 troubleshooting.

Practice this exam

Start a free DP-900 practice session

Short sessions build daily habit. Longer sessions build exam-day stamina. Try a timed session to simulate real conditions.

10 questions 20 questions 30 questions 50 questions Timed 30

DP-900 practice-test guide →Study guide →Browse all practice tests

FAQ

Questions learners often ask

What does this DP-900 question test?

Describe an analytics workload on Azure — This question tests Describe an analytics workload on Azure — Read the scenario before looking for a memorised answer..

What is the correct answer to this question?

The correct answer is: Azure Synapse Analytics (dedicated SQL pool) — Azure Synapse Analytics (dedicated SQL pool) is the correct choice because it is a cloud-based analytics service that supports T-SQL queries, can scale compute independently from storage, and offers a serverless option (Synapse Serverless SQL pool) that eliminates the need to provision fixed resources. This allows the analytics team to run complex, long-running reporting queries against large datasets without impacting the performance of the OLTP Azure SQL Database.

What should I do if I get this DP-900 question wrong?

Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.

What is the key concept behind this question?

Read the scenario before looking for a memorised answer.

About these practice questions

Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →

How Courseiva writes practice questions · Editorial policy

Same concept, more angles

1 more ways this is tested on DP-900

These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.

Variation 1. Your company is migrating an on-premises SQL Server data warehouse to Azure. The solution must support both historical analytics and real-time reporting. Which Azure service should you recommend as the primary data store?

easy

A.Azure Analysis Services
B.Azure Data Lake Storage Gen2
C.Azure SQL Database
✓ D.Azure Synapse Analytics

Why D: Azure Synapse Analytics is the correct choice because it is a cloud-native analytics service that unifies big data and data warehousing, supporting both historical analytics (via dedicated SQL pools for large-scale relational data warehousing) and real-time reporting (via serverless SQL pools or Apache Spark pools for streaming and interactive queries). It is designed to handle the migration of an on-premises SQL Server data warehouse while providing integrated capabilities for batch and real-time workloads.

Keep practising

Question Discussion

Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.

Loading comments…

This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.