DP-900 Describe an analytics workload on Azure Practice Question
A financial institution needs to run complex queries against petabytes of historical trading data stored in Azure Data Lake Storage. The queries must be efficient and use columnar storage format. Which technology should they use to process this data?
⚠ Common exam trap
Many candidates confuse Azure SQL Database (a transactional system) with Azure Synapse Analytics (an analytical system), assuming both can handle petabyte-scale analytics, but only Synapse provides the columnar storage and MPP engine required for efficient historical data queries.
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
Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a cloud-based analytics service designed for petabyte-scale data warehousing. It supports PolyBase to query data directly from Azure Data Lake Storage and uses a columnar storage format (via clustered columnstore indexes) to enable efficient, high-performance analytical queries on massive historical datasets.
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
Why it's wrong here
Azure SQL Database is a fully managed relational engine built around SQL Server's traditional rowstore architecture, primarily tuned for high-concurrency OLTP transactions. While it does offer some columnstore index support, it lacks Synapse's Massively Parallel Processing (MPP) architecture, so complex analytic scans cannot be distributed across many compute nodes at petabyte scale. Its database size limits and expected query patterns make it a poor fit for the bank's large-scale data warehouse workloads.
- ✓
Azure Synapse Analytics
Why this is correct
Azure Synapse Analytics is a cloud data warehouse built on Massively Parallel Processing (MPP), which automatically distributes T-SQL queries across multiple compute nodes to scan and aggregate huge volumes of data. It natively supports columnar storage formats like Parquet, along with its own clustered columnstore indexes, enabling high-compression and efficient analytic reads. Integration with Azure Data Lake Storage and serverless SQL pools means the bank can run complex analytical queries directly over petabyte-scale historical data without loading it into a busy OLTP system.
- ✗
Azure Cosmos DB
Why it's wrong here
Azure Cosmos DB is a multi-model NoSQL database engineered for low-latency, globally distributed transactional apps, offering single-digit-millisecond reads and writes across regions. Its schema-flexible document, graph, and key-value APIs are designed for real-time operations, not for multidimensional SQL analytics; queries are limited to API-specific patterns and cannot leverage MPP or columnar data layout. Storing petabyte-scale historical data here would be costly and force analysts to work outside standard relational tools, making it inappropriate for complex analytical requirements.
- ✗
Azure Table Storage
Why it's wrong here
Azure Table Storage is a schemaless NoSQL key-value store in which each entity is addressed only by partition key and row key. It supports simple point queries and range scans on the primary key, but it does not provide join operations, aggregations, or distributed query execution, and it lacks columnar storage. Consequently, it cannot satisfy the bank's need to run complex analytical SQL-style queries over petabytes of historical data.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Service
A service is a software component or system that performs a specific function and is available to be used by other programs or users over a network.
Key term
Data warehouse
A data warehouse is a central repository that stores large amounts of structured data from multiple sources, optimized for querying and analysis rather than day-to-day transactions.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.