DP-900 Practice Test 2 — 30 Questions

Question 1

A retail chain collects daily sales data from hundreds of stores. The data is stored as CSV files in Azure Data Lake Storage Gen2. The analytics team needs to run complex SQL queries that join sales data with product dimensions and aggregate results across petabytes of data. Queries must return results within seconds. Which Azure service is best suited for this analytical workload?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a distributed query engine designed for petabyte-scale data warehousing. It uses Massively Parallel Processing (MPP) to distribute data across compute nodes, enabling complex SQL joins and aggregations on data stored in Azure Data Lake Storage Gen2 to return results in seconds via its SQL pool or serverless SQL endpoint.

Answer

Azure SQL Database

Answer

Azure Analysis Services

Answer

Azure HDInsight

Question 2

A company uses Azure Data Lake Storage Gen2 for a data lake. They implement a folder structure with access control lists (ACLs). A new data scientist needs to read data from a specific folder but not write to it. Which ACL permission should be assigned?

Accepted Answer

Execute. Execute (X) permission on a folder in Azure Data Lake Storage Gen2 is required to traverse the folder and access its contents. Without Execute, a user cannot list or read files inside the folder, even if Read permission is granted. Since the data scientist only needs to read data (not write), assigning Execute on the folder and Read on the files allows traversal and read access without write capability.

Answer

Modify

Answer

Write

Answer

Read

Question 3

A manufacturing company deploys IoT sensors on equipment in a factory. They need to monitor sensor data in real time to detect anomalies and trigger immediate alerts. They also need to store years of historical sensor data for monthly capacity planning reports that involve complex aggregations. The company wants a cost-effective solution that minimizes data movement between storage and compute. Which combination of Azure services should they use for real-time processing and historical batch analytics?

Accepted Answer

A. Azure Stream Analytics for real-time processing, Azure Data Lake Storage Gen2 for historical storage, and Azure Synapse Analytics for batch queries.. Azure Stream Analytics is purpose-built for real-time processing of streaming data from IoT sensors, enabling immediate anomaly detection and alerting. Azure Data Lake Storage Gen2 provides cost-effective, scalable storage for years of historical sensor data, while Azure Synapse Analytics (formerly SQL Data Warehouse) can run complex aggregations directly against that data without moving it, minimizing data movement and cost.

Answer

B. Azure Data Factory for real-time processing, Azure Cosmos DB for historical storage, and Power BI for batch queries.

Answer

C. Azure Functions for real-time processing, Azure Table Storage for historical storage, and Azure Analysis Services for batch queries.

Answer

D. Azure Event Hubs for real-time processing, Azure SQL Database for historical storage, and Azure Machine Learning for batch queries.

Question 4

A mobile gaming company stores player session data as key-value pairs. Each player has a unique PlayerID, and the application needs to read/write the player's current level and score with very low latency. The data does not require complex queries, and the schema (attributes per player) can vary. The company wants a fully managed, globally distributed NoSQL database. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB Table API. Azure Cosmos DB Table API is the correct choice because it provides a fully managed, globally distributed NoSQL database that supports key-value data with schema flexibility. It offers low-latency reads and writes (single-digit milliseconds at the 99th percentile) and automatic global distribution, making it ideal for storing player session data with varying attributes per player.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Cache for Redis

Question 5

A startup develops a mobile application that stores user preferences as simple key-value pairs. The app is only used in North America, and the team needs low-latency reads and writes with minimal cost. They do not require global distribution or complex querying. Which Azure data store should they choose?

Accepted Answer

Azure Table Storage. Azure Table Storage is the correct choice because it provides a cost-effective, low-latency key-value store for simple data like user preferences, with no need for global distribution or complex querying. It offers single-digit millisecond latency for reads and writes within a single region, and its pay-per-request pricing model minimizes cost for a startup. The Table API in Azure Cosmos DB would be overkill and more expensive for this North America-only, non-distributed scenario.

Answer

Azure Cosmos DB (SQL API)

Answer

Azure Cosmos DB (Table API)

Answer

Azure SQL Database

Question 6

A social media company stores user-generated posts as JSON documents. Each post contains fields such as postId, userId, timestamp, and content. The application needs to query posts by userId and timestamp ranges with low latency, and also perform SQL-like queries across all posts. The data volume is growing rapidly and must scale globally. Which Azure data store should the company use?

Accepted Answer

B) Azure Cosmos DB SQL API. Azure Cosmos DB SQL API is the correct choice because it provides native support for querying JSON documents with low-latency, including indexed queries on fields like userId and timestamp. Its global distribution capability ensures data can be replicated across multiple Azure regions for low-latency access worldwide, while its SQL API allows SQL-like queries across all posts, meeting both requirements.

Answer

A) Azure Table Storage

Answer

C) Azure Blob Storage

Answer

D) Azure Cache for Redis

Question 7

A SaaS company hosts a multi-tenant application. Each tenant has a separate Azure SQL Database. The databases are small (1-3 GB) and their workloads vary significantly over time, with some tenants active during business hours and others at night. The company wants to maximize resource utilization and minimize costs by pooling compute resources across tenants while maintaining predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Azure SQL Database Elastic Pool. Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases (tenants) to share a fixed pool of compute and storage resources, enabling cost efficiency through resource pooling while providing predictable performance via per-database resource limits (min/max DTU or vCore). This matches the scenario of small databases with variable, non-overlapping workloads across tenants.

Answer

Azure SQL Database Single Database

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 8

A company runs an e-commerce application on Azure SQL Database. The database experiences high transaction volume during business hours (9 AM to 6 PM) but very low activity at night and on weekends. They want to optimize costs by paying only for the compute resources used, while ensuring the database can automatically scale up during peak periods and scale down (or pause) during idle times. Which Azure SQL Database purchasing model and compute tier should they choose?

Accepted Answer

vCore-based purchasing model with serverless compute tier. The vCore-based purchasing model with serverless compute tier is correct because it automatically scales compute resources based on workload demand and can pause during idle periods, charging only for consumed compute and storage. This matches the requirement of high transaction volume during business hours and low activity at night/weekends, optimizing costs by eliminating charges for unused compute capacity.

Answer

DTU-based purchasing model

Answer

vCore-based purchasing model with provisioned compute tier

Answer

vCore-based purchasing model with Hyperscale service tier

Question 9

A hospital uses Azure SQL Database to store patient records. The database contains tables for patient information, insurance details, and treatment plans. The system must ensure that if a transaction updates a patient's insurance and their treatment plan in two separate rows, either both updates succeed or both fail. Which ACID property guarantees this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In Azure SQL Database, if a transaction updates both the insurance and treatment plan rows, atomicity guarantees that either both updates are committed or both are rolled back, preventing partial updates that could leave data in an inconsistent state.

Answer

Durability

Answer

Consistency

Answer

Isolation

Question 10

A gaming company stores player scores in Azure Cosmos DB using the NoSQL API. Each document contains fields: PlayerID (unique to the player), GameID, Score, Timestamp. The most common query is: 'Retrieve all scores for a specific GameID, ordered by Score descending.' Which property should be chosen as the partition key to minimize Request Unit (RU) consumption?

Accepted Answer

GameID. GameID is the correct partition key because the most common query filters on GameID, and Cosmos DB routes queries to the exact physical partition(s) containing that GameID's data. This minimizes RU consumption by avoiding cross-partition fan-out, as the query engine can target a single partition. Using any other field would force scanning multiple partitions, increasing RU cost.

Answer

PlayerID

Answer

Score

Answer

Timestamp

Question 11

A company ingests raw clickstream data as JSON files into Azure Data Lake Storage Gen2. Data scientists need to explore the data interactively using Python notebooks, and the BI team needs to create reports from aggregated datasets derived from this data. The solution must be serverless, scale automatically, and minimize administration. Which Azure service should they choose?

Accepted Answer

B. Azure Databricks. Azure Databricks is correct because it provides a serverless, interactive Apache Spark environment that data scientists can use with Python notebooks for exploratory analysis, and it can produce aggregated datasets for BI reporting. It scales automatically and minimizes administration by managing the cluster lifecycle, making it ideal for ad-hoc data exploration on raw JSON files in Azure Data Lake Storage Gen2.

Answer

A. Azure Synapse Analytics (serverless SQL pool)

Answer

C. Azure HDInsight with Spark

Answer

D. Azure Data Lake Analytics

Question 12

A retail company operates an e-commerce website that processes customer orders (insert, update, delete) throughout the day. The same company also runs reports on sales trends at the end of each quarter. Which type of data processing workload does the order processing represent?

Accepted Answer

B) Transactional processing (OLTP). Order processing involves inserting, updating, and deleting individual customer orders in real time as they occur. This is the classic definition of an Online Transaction Processing (OLTP) workload, which is optimized for high-volume, low-latency transactions that maintain ACID (Atomicity, Consistency, Isolation, Durability) properties. The e-commerce website requires immediate data consistency for each order, which is the hallmark of transactional processing.

Answer

A) Batch processing

Answer

C) Analytical processing (OLAP)

Answer

D) Stream processing

Question 13

A company updates a customer's address in a database. The update must ensure that all existing orders still reference a valid customer ID. The database checks the foreign key constraint and rejects the update if it would violate referential integrity. Which ACID property does this enforcement represent?

Accepted Answer

Consistency. Consistency ensures that any database transaction brings the database from one valid state to another, preserving all defined rules, including constraints like foreign keys. In this scenario, the foreign key constraint enforcement prevents an update that would leave orphaned order records, directly upholding the consistency property by rejecting the transaction if it violates referential integrity.

Answer

Atomicity

Answer

Isolation

Answer

Durability

Question 14

A large e-commerce company needs to build an analytics solution. They have streaming clickstream data from their website (JSON) and daily sales data from their transactional database (CSV). They need to perform real-time dashboards on clickstream for the current hour, and also run complex historical queries that join sales data with aggregated clickstream data over the past year. They want a single Azure service that can handle both stream processing and batch processing using a unified experience, without moving data between separate systems. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified experience for both stream processing (via Synapse Pipelines or Spark Structured Streaming) and batch processing (via dedicated SQL pools or serverless SQL), enabling real-time dashboards on clickstream data and complex historical queries joining sales data with aggregated clickstream data without moving data between separate systems.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure HDInsight

Question 15

A manufacturing company collects real-time temperature data from thousands of IoT sensors. They need to build an analytics solution that processes the streaming data, computes the average temperature per device every minute, and outputs the results to a Power BI dashboard for near real-time visualization. Which Azure service should they use for the real-time stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed specifically for scenarios like this: ingesting high-velocity data from IoT sensors, performing time-windowed aggregations (e.g., average temperature per device every minute), and outputting results directly to Power BI for near real-time dashboards. It natively supports SQL-like query language for defining windowed computations and has built-in connectors for both IoT Hub/Event Hubs (input) and Power BI (output), making it the most efficient and purpose-built service for this streaming analytics workload.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure SQL Database

Question 16

A retail company receives real-time data from IoT sensors in its warehouses. Each sensor sends a JSON payload containing a device ID, timestamp, and temperature reading. A data engineer needs to classify this data for storage planning. Which data type best describes the JSON payload?

Accepted Answer

Semi-structured data. The JSON payload is considered semi-structured data because it has organizational properties (key-value pairs, nested structure) that provide a schema, but it does not conform to a rigid tabular schema like a relational database. JSON allows flexible fields and varying data types, which is characteristic of semi-structured data.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 17

An e-commerce company has 20 SQL Server databases that each range from 10 GB to 50 GB and experience unpredictable usage patterns with occasional spikes in user activity. The company wants to migrate to Azure SQL Database to reduce management overhead and minimize costs by allowing databases to share resources. Which Azure SQL Database deployment option should they choose?

Accepted Answer

B: Elastic pool. Elastic pools allow multiple SQL databases to share a fixed set of resources (eDTUs or vCores), which is ideal for databases with unpredictable usage patterns and occasional spikes. By pooling resources, the company can reduce management overhead and minimize costs compared to provisioning each database individually, as the pool's total resources are shared among all databases, smoothing out peak demands.

Answer

A: Single database with provisioned throughput

Answer

C: Managed Instance

Answer

D: SQL Server on Azure VM

Question 18

A media company stores large video files in Azure Blob Storage. The videos are accessed frequently for the first 30 days after upload, then rarely for the next 180 days. After that, they are only needed for compliance and are never accessed. Which access tier should be used for the first 30 days to minimize costs while maintaining low latency?

Accepted Answer

Hot tier. The Hot tier is the correct choice for the first 30 days because it provides the lowest latency access and highest throughput for frequently accessed data, which matches the requirement of frequent access during this period. While the Hot tier has the highest storage cost per GB, it has no retrieval costs, making it cost-effective for high-access patterns. The other tiers introduce either retrieval fees (Cool), high latency (Archive), or unnecessary cost (Premium) for this use case.

Answer

Cool tier

Answer

Archive tier

Answer

Premium tier

Question 19

A hospital collects patient vital signs every minute using IoT sensors. Each reading contains a timestamp, patient ID, heart rate, blood pressure, and temperature. This data is ingested continuously for real-time monitoring and alerting. Which type of data workload does this scenario best represent?

Accepted Answer

D. Real-time streaming. This scenario requires continuous ingestion of sensor data with immediate processing for real-time monitoring and alerting. Real-time streaming workloads, such as those handled by Azure Stream Analytics or Apache Kafka, are designed to process unbounded data streams with low latency, making option D correct.

Answer

A. Transactional workload

Answer

B. Analytical workload

Answer

C. Batch processing

Question 20

A company wants to build a modern data warehouse using a lakehouse architecture. They need to store raw data in its native format (e.g., CSV, JSON, Parquet) and also support BI reporting on curated, transformed data. They want to use a single storage layer for both raw and curated data. Which Azure service should they use as the core storage layer?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it provides a single, unified storage layer that can store raw data in its native format (CSV, JSON, Parquet) in a hierarchical namespace, while also serving as the foundation for curated, transformed data used in BI reporting. It combines the scalability and cost-effectiveness of Azure Blob Storage with the file system semantics and ACLs needed for analytics workloads, making it the ideal core storage layer for a lakehouse architecture.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB