DP-900 Practice Test 13 — 15 Questions

Question 1

A company develops an IoT device registry that stores device metadata as JSON documents. Each device has a unique DeviceID, and the attributes vary per device type (e.g., sensors, actuators). The application requires low-latency reads by DeviceID and needs global distribution to support devices worldwide. The team wants to use a fully managed NoSQL database in Azure. Which API should they choose for Azure Cosmos DB?

Accepted Answer

SQL API. The SQL API (formerly DocumentDB API) is the native API for Azure Cosmos DB, providing full support for querying JSON documents with a SQL-like syntax. It offers the lowest latency reads by ID (point reads) and native global distribution, making it ideal for a device registry where each device has a unique DeviceID and variable attributes. The SQL API also supports indexing all properties automatically, which is critical for the varied device types.

Answer

MongoDB API

Answer

Cassandra API

Answer

Table API

Question 2

A company has 15 SQL Server databases, ranging from 50 GB to 200 GB each. The databases experience unpredictable load spikes during the day. They want to migrate to Azure SQL Database to minimize management overhead and reduce costs by allowing databases to share resources, while ensuring each database can burst to higher performance when needed. Which deployment option should they choose?

Accepted Answer

B) Elastic pool. Elastic pools allow multiple databases to share a fixed pool of resources (DTUs or vCores), which reduces costs by pooling unused capacity and enables each database to automatically burst to higher performance when needed. This matches the company's need to minimize management overhead while handling unpredictable load spikes across 15 databases ranging from 50 GB to 200 GB.

Answer

A) Single database with Provisioned throughput tier

Answer

C) SQL Managed Instance

Answer

D) SQL Server on Azure Virtual Machine

Question 3

A company has an existing on-premises SQL Server database that is 500 GB in size. The database uses SQL Server Agent jobs for scheduled maintenance and linked servers to query data from a remote SQL Server instance. The company wants to migrate to Azure with minimal application changes and needs automated backups and patching. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while offering automated backups and patching. This minimizes application changes during migration, unlike other Azure SQL options that lack these features.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for PostgreSQL

Question 4

A company is migrating a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance, relies on linked servers to query data from another SQL Server instance, and requires cross-database queries within the same instance. The company wants a fully managed PaaS service that minimizes application code changes and provides automatic backups and patching. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks instance-scoped features.

Answer

Azure SQL Database (Single Database)

Answer

Azure SQL Database (Elastic Pool)

Answer

SQL Server on Azure Virtual Machine

Question 5

A company needs to store archived log files that are rarely accessed but must be retained for regulatory compliance. The logs are text-based and each file is about 10 MB. They want the lowest storage cost while ensuring the data is durable and can be read when needed. Which Azure Blob Storage access tier should they choose?

Accepted Answer

Archive. The Archive tier is the correct choice because it offers the lowest storage cost for data that is rarely accessed and must be retained for long periods. Archived log files that are text-based and 10 MB each fit this profile perfectly, as the Archive tier is designed for data that can tolerate a retrieval latency of several hours (up to 15 hours for standard priority) while providing the same high durability (99.9999999999% or 11 nines) as other tiers. The data remains fully durable and can be read when needed by first rehydrating it to an online tier (Hot, Cool, or Cold) before access.

Answer

Hot

Answer

Cool

Answer

Cold

Question 6

A company stores customer reviews for an e-commerce site. Each review contains a product ID, user ID, rating, and optional comments and images. The reviews are written once and rarely updated. The company needs to query reviews by product ID with low latency and also perform simple key-value lookups. They want a cost-effective, serverless solution that requires no scaling management. Which Azure data store should they choose?

Accepted Answer

Azure Table Storage. Azure Table Storage is a cost-effective, serverless NoSQL key-value store that supports simple key-value lookups and querying by partition key (e.g., ProductID) with low latency. It requires no scaling management, as it automatically scales based on demand, and is ideal for immutable, rarely-updated data like customer reviews. The pay-per-request pricing model makes it highly cost-effective for this workload.

Answer

Azure Cosmos DB SQL API

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 7

A company stores historical sensor data in Azure Blob Storage. The data is accessed only a few times per year for compliance audits, but when requested, it must be available for reading within 15 minutes. The company wants to minimize storage costs. Which blob access tier should they use?

Accepted Answer

Cool. The Cool tier is the optimal choice because it balances low storage cost with the ability to retrieve data within minutes, meeting the 15-minute availability requirement. Archive would incur a retrieval delay of up to 15 hours, which violates the compliance audit SLA. Hot and Premium tiers are more expensive and unnecessary for data accessed only a few times per year.

Answer

Hot

Answer

Archive

Answer

Premium

Question 8

A company stores terabytes of customer support chat transcripts in JSON format. The data is rarely modified and needs to be accessed by analysts using SQL queries. The analysts do not want to manage servers or provision throughput. Which Azure service should be used to store and query this data?

Accepted Answer

Azure Blob Storage (with Azure Data Lake Storage Gen2) and query using Azure Synapse Serverless SQL. Azure Blob Storage with Azure Data Lake Storage Gen2 provides a cost-effective, scalable solution for storing large volumes of JSON data in its native format. By using Azure Synapse Serverless SQL, analysts can query this data directly with standard T-SQL without provisioning any infrastructure or managing throughput, meeting the requirement for serverless, on-demand querying of rarely modified data.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 9

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries frequently filter on OrderDate and OrderStatus, and sort by OrderDate descending. Which indexing strategy will most improve query performance?

Accepted Answer

Create a non-clustered index on (OrderDate DESC, OrderStatus) and keep the existing clustered index on OrderID. Option D creates a covering index for the most common query pattern: filtering on OrderDate and OrderStatus, and sorting by OrderDate descending. By specifying DESC in the index key, the index is ordered in the same direction as the sort, allowing SQL Server to avoid a sort operation and retrieve rows in order directly from the index. This non-clustered index can satisfy the query entirely without touching the clustered index (OrderID), reducing I/O and improving performance.

Answer

Create a clustered index on OrderDate and a non-clustered index on OrderStatus

Answer

Create a non-clustered index on (OrderDate, OrderStatus) and keep the existing clustered index on OrderID

Answer

Create a clustered index on OrderID and a non-clustered index on (OrderStatus, OrderDate)

Question 10

A company uses Azure SQL Database to store order data. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status, TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate descending. Which indexing strategy will most improve query performance for these filters and sort?

Accepted Answer

Create a nonclustered index on (OrderDate DESC, Status) and include TotalAmount. Option B creates a covering nonclustered index on (OrderDate DESC, Status) that directly supports the frequent filter on OrderDate and Status and the ORDER BY OrderDate DESC. Including TotalAmount as a non-key column makes the index covering, so all needed columns come from the index without key lookups, maximizing query performance.

Answer

Create a clustered index on OrderDate

Answer

Create a nonclustered index on Status alone

Answer

Create a clustered columnstore index on the table

Question 11

A company uses Azure SQL Database for an e-commerce system. The Orders table has millions of rows with a clustered index on OrderID (the primary key). Queries that filter on OrderDate and CustomerID to find recent orders for a specific customer are very slow. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

Create a nonclustered composite index on (OrderDate, CustomerID). The query filters on both OrderDate and CustomerID, so a composite nonclustered index on (OrderDate, CustomerID) allows SQL Server to perform a single index seek to locate the matching rows without touching the clustered index until the final key lookup. This dramatically reduces I/O compared to scanning the entire clustered index or using multiple separate indexes.

Answer

Create a nonclustered index on OrderID only

Answer

Create separate nonclustered indexes on OrderDate and CustomerID

Answer

Create a clustered index on CustomerID instead of OrderID

Question 12

A company uses Azure SQL Database for an order processing system. The Orders table has columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. The Customers table has CustomerID (PK), Name, Email. The database administrator wants to ensure that when a customer record is deleted, all orders for that customer are also automatically deleted. Which database constraint should be implemented?

Accepted Answer

ON DELETE CASCADE on Orders.CustomerID. Option B is correct because ON DELETE CASCADE on the foreign key (Orders.CustomerID) automatically deletes all child rows in the Orders table when the parent row in the Customers table is deleted. This ensures referential integrity without requiring additional code or triggers, and is the standard SQL mechanism for cascading deletes in Azure SQL Database.

Answer

ON DELETE SET NULL on Orders.CustomerID

Answer

ON UPDATE CASCADE on Customers.CustomerID

Answer

A trigger on Customers table

Question 13

A company is migrating a SQL Server database to Azure SQL Database. The database uses CLR (Common Language Runtime) integration for business logic and has database mail configured. The company needs full instance-level functionality while still benefiting from the platform-as-a-service model. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with SQL Server on-premises, including support for CLR integration and Database Mail, while still offering a platform-as-a-service (PaaS) model. Single databases and elastic pools lack these instance-scoped features, and Hyperscale is a scaling option for single databases, not a separate deployment type that adds instance-level functionality.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database Hyperscale

Question 14

A company must archive customer correspondence PDFs that are rarely accessed but must be retained for seven years. The documents must be available for read within seconds if requested. Which Azure Blob Storage access tier should be used to minimize storage cost while meeting the availability requirement?

Accepted Answer

Cool. The Cool tier is optimal because it balances low storage cost with high availability for data that is infrequently accessed but must be retrievable within seconds. It offers the same low-latency retrieval as the Hot tier (milliseconds) but at a lower storage price, making it ideal for archived correspondence that still requires immediate read access.

Answer

Hot

Answer