DP-900 Practice Test 2 — 10 Questions

Question 1

A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows with a TransactionDate column. Queries frequently aggregate sales totals for the current month, but historical data must be retained for 7 years. Currently, queries scan the entire table, causing performance issues. The company also wants to simplify archiving of old data. Which design should they implement?

Accepted Answer

Implement table partitioning by month on TransactionDate.. Table partitioning by month on TransactionDate allows Azure SQL Database to efficiently manage and query large tables by splitting data into manageable segments. Queries that filter on TransactionDate for the current month will only scan the relevant partition(s), eliminating full table scans. Additionally, partitioning simplifies archiving by enabling swift partition switching to move old data to archive tables without complex ETL processes.

Answer

Create a non-clustered index on the TransactionDate column.

Answer

Create a materialized view for the current month's data.

Answer

Convert the table to use a clustered columnstore index.

Question 2

A retail company wants to analyze customer clickstream data in real-time to detect patterns and trigger personalized offers. They also store the raw clickstream data in Azure Data Lake Storage for later batch analysis. Which Azure service should they use for the real-time processing component?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time data processing and analytics on streaming data, such as clickstream events. It can ingest data from sources like Azure Event Hubs, apply SQL-like queries to detect patterns, and output results to triggers or storage, all with sub-second latency. This matches the requirement for real-time pattern detection and personalized offer triggering.

Answer

Azure Data Factory

Answer

Azure Batch

Answer

Azure Data Lake Analytics

Question 3

A smart building company stores IoT sensor data in Azure Cosmos DB using the NoSQL API. Each document contains fields: deviceId (partition key), timestamp, temperature, and humidity. The most common query is to retrieve all readings for a specific device within a time range, which runs efficiently. However, the analytics team occasionally runs a query to find all devices that reported a temperature above 50 degrees Celsius in the last hour, without specifying deviceId. This query is very slow and consumes a high number of request units (RUs). What is the most likely reason for the slow performance and high RU consumption?

Accepted Answer

The query does not use the partition key, causing a cross-partition scan.. The query does not include the partition key (deviceId) in the filter, so Azure Cosmos DB cannot route it to a single physical partition. Instead, it must fan out the query to every partition, scanning all documents across the container. This cross-partition query consumes significantly more RUs and takes longer because each partition must be queried sequentially or in parallel, and the results are merged server-side.

Answer

The query is not using an index on the temperature field.

Answer

The time range filter is too large, causing a full table scan.

Answer

The document size is too large, increasing RU per read.

Question 4

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 5

A smart building company stores sensor data from thousands of IoT devices as JSON documents in Azure Cosmos DB using the NoSQL API. Each document contains fields: deviceId (string), timestamp (datetime), temperature (float), humidity (float), and additional device-specific fields (e.g., motionDetected, CO2level). The most common query is: SELECT * FROM c WHERE c.deviceId = 'sensor-123' AND c.timestamp >= '2025-01-01' AND c.timestamp < '2025-02-01' ORDER BY c.timestamp DESC. Which indexing strategy will provide the best performance for this query?

Accepted Answer

Create a composite index on (deviceId ASC, timestamp DESC). Option B is correct because the query filters on `deviceId` (equality) and `timestamp` (range with ORDER BY DESC). A composite index on `(deviceId ASC, timestamp DESC)` allows Cosmos DB to efficiently locate the partition for the device and then scan the timestamp range in descending order without an in-memory sort, minimizing RU consumption and latency.

Answer

Use the default indexing policy that automatically indexes all properties

Answer

Disable indexing for all properties to speed up writes

Answer

Create a spatial index on the deviceId field

Question 6

A startup is developing a web application that requires a relational database with PostgreSQL compatibility. They want a fully managed service that automatically handles backups, patching, and provides high availability with a 99.99% SLA. Which Azure service should they choose?

Accepted Answer

Azure Database for PostgreSQL. Azure Database for PostgreSQL is the correct choice because it is a fully managed relational database service that offers PostgreSQL compatibility, automatic backups, patching, and high availability with a 99.99% SLA. This meets the startup's requirements for a managed PostgreSQL solution without the need for manual administration.

Answer

Azure SQL Database

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB for PostgreSQL

Question 7

A small online retailer wants to migrate its single on-premises SQL Server database to Azure. They require a fully managed relational database service with built-in high availability, automated backups, and no need to manage virtual machines. They do not need features like multiple databases with cross-database queries or SQL Agent. Which Azure service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that provides built-in high availability (99.99% SLA with zone-redundant configuration), automated backups with point-in-time restore, and eliminates the need to manage virtual machines or operating system patches. It is the ideal choice for a single database migration when features like cross-database queries and SQL Agent are not required.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL

Question 8

A software company develops a multi-tenant SaaS application. They deploy a separate Azure SQL Database for each tenant. The databases are small (2-5 GB) and have highly variable loads — some tenants use the app heavily during the day, others at night. The company wants to maximize resource utilization and minimize costs by allowing databases to share a pool of resources, while still maintaining a predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. C is correct because an elastic pool allows multiple Azure SQL databases with variable and unpredictable usage patterns to share a fixed pool of resources (eDTUs or vCores), maximizing resource utilization and minimizing cost. The pool provides a predictable performance per database through per-database min/max resource limits, which is ideal for the described multi-tenant SaaS scenario with small databases and highly variable loads.

Answer

Single database with DTU purchasing model

Answer

Single database with vCore purchasing model

Answer

Azure SQL Managed Instance

Question 9

A company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database has a large fact table that is partitioned by date (monthly partitions) to improve query performance and simplify data archiving. The company wants to maintain the same partitioning strategy in Azure to avoid rewriting queries. Which feature in Azure SQL Managed Instance should they use to achieve this?

Accepted Answer

Table partitioning with partition functions and schemes. Azure SQL Managed Instance supports table partitioning using partition functions and partition schemes, which is the same feature available in SQL Server. This allows you to define monthly partitions on the fact table using a date column, preserving the existing partitioning strategy and query logic without modification. The partition function maps rows to partitions based on the date boundary values, and the partition scheme assigns those partitions to filegroups.

Answer

Sharding across multiple Azure SQL Managed Instances

Answer

Index partitioning only

Answer

Federated tables

Question 10

A company maintains a database of customer orders that are updated frequently. They also store aggregated monthly sales reports that are generated once and then only read. Which statement correctly distinguishes these two types of data workloads?

Accepted Answer

Transactional data is optimized for write operations, and analytical data is optimized for read operations.. Option A is correct because transactional workloads (like the frequently updated customer orders) are optimized for write-heavy operations, ensuring ACID compliance and data integrity, while analytical workloads (like the read-only monthly sales reports) are optimized for read-heavy operations, often using columnar storage or pre-aggregated data to speed up queries. This distinction aligns with the core difference between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems in Azure, such as Azure SQL Database for transactional data and Azure Synapse Analytics for analytical data.

Answer

Transactional data must always be stored in non-relational databases, and analytical data in relational databases.

Answer

Analytical data always requires real-time processing, whereas transactional data is batch-processed.

Answer

Transactional data is read-only and analytical data is frequently updated.