DP-900 Practice Test 14 — 15 Questions

Question 1

A company runs an e-commerce application on Azure SQL Database. The application experiences unpredictable traffic spikes during flash sales and promotional events. The company wants to automatically scale compute resources based on actual demand and pay only for the resources consumed. Which Azure SQL Database deployment option best meets these requirements?

Accepted Answer

Serverless. The Serverless deployment option for Azure SQL Database automatically scales compute resources (vCores) based on actual demand, pausing the database during idle periods and resuming on the first connection. This model charges per second for the compute used, making it ideal for unpredictable traffic spikes like flash sales, as it eliminates the need to over-provision and ensures you pay only for consumed resources.

Answer

Provisioned DTU

Answer

Provisioned vCore

Answer

Hyperscale

Question 2

A company runs an e-commerce application on Azure SQL Database. The database has a table named Orders with columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). The application frequently runs the following query: SELECT * FROM Orders WHERE CustomerID = 12345 AND OrderDate BETWEEN '2025-01-01' AND '2025-01-31' ORDER BY OrderDate DESC. The table contains 10 million rows. Which index would best optimize this query?

Accepted Answer

A nonclustered index on (CustomerID, OrderDate DESC) including TotalAmount as included column.. Option B is correct because the query filters on both CustomerID and OrderDate, so a composite nonclustered index on (CustomerID, OrderDate DESC) allows SQL Server to perform an index seek on CustomerID and then an ordered range scan on OrderDate, avoiding a sort operation. Including TotalAmount as an included column makes the index covering, so the query can be satisfied entirely from the index without key lookups to the clustered index.

Answer

A nonclustered index on OrderDate only.

Answer

A clustered index on (OrderDate, CustomerID).

Answer

A nonclustered index on (OrderDate DESC) only.

Question 3

A company uses Azure SQL Database for an order management system. They have a table 'Orders' with columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. Queries that filter on OrderDate are slow. They create a nonclustered index on OrderDate. However, after many inserts, the index becomes fragmented and page splits occur frequently. Which action should the DBA take to maintain query performance?

Accepted Answer

Rebuild the index online. Option A is correct because rebuilding the index online eliminates fragmentation and page splits without blocking concurrent queries, which is critical for a production order management system. The ALTER INDEX REBUILD operation reorganizes the index B-tree structure, consolidating pages and reducing logical fragmentation, thereby restoring query performance on OrderDate filters.

Answer

Drop and recreate the index

Answer

Add a clustered index on OrderDate

Answer

Change the index to a clustered columnstore index

Question 4

Match each data processing term to its definition.

Question 5

Match each Azure storage redundancy option to its description.

Question 6

Match each Azure SQL Database tier to its description.

Question 7

Match each Azure data service to its primary purpose.

Question 8

Match each Azure data security feature to its description.

Question 9

A company stores customer data in a relational table with fixed columns: CustomerID (integer), FirstName (string), LastName (string), Email (string). They also store product images as JPEG files in Azure Blob Storage, and customer feedback as JSON documents where each document may contain fields such as rating, comment, and optional metadata. Which of the following correctly classifies these data types?

Accepted Answer

Relational table – structured, JPEG – unstructured, JSON – semi-structured. Option A is correct because a relational table with fixed columns and data types (CustomerID, FirstName, LastName, Email) stores structured data with a rigid schema. JPEG files in Azure Blob Storage are binary blobs with no internal structure that a database can interpret, making them unstructured. JSON documents with optional fields (like rating, comment, metadata) have a flexible schema that can vary per document, which is the definition of semi-structured data.

Answer

Relational table – structured, JPEG – semi-structured, JSON – unstructured

Answer

Relational table – semi-structured, JPEG – unstructured, JSON – structured

Answer

Relational table – unstructured, JPEG – structured, JSON – semi-structured

Question 10

A company has a suite of 20 e-commerce applications, each with its own SQL Server database. The databases vary in size from 5 GB to 100 GB and have unpredictable usage patterns with bursty peaks. The company wants to migrate to Azure SQL Database to benefit from built-in high availability and automatic backups. They need to minimize costs by only paying for the resources each database actually uses, and they want to avoid over-provisioning for peak loads. 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 to share a fixed pool of resources (DTUs or vCores), enabling cost efficiency by pooling and reallocating resources across databases with unpredictable, bursty usage patterns. This avoids over-provisioning for peak loads while still providing built-in high availability and automatic backups, as each database in the pool benefits from these features without needing individual resource reservations.

Answer

Azure SQL Database (single database) with Serverless compute tier

Answer

Azure SQL Managed Instance

Answer

Azure SQL Database Hyperscale

Question 11

Drag and drop the steps to load data into Azure Synapse Analytics using PolyBase in the correct order.

Question 12

Drag and drop the steps to configure a geo-replication for Azure Cosmos DB in the correct order.

Question 13

Drag and drop the steps to ingest data into Azure Data Explorer (ADX) in the correct order.

Question 14

Drag and drop the steps to create an Azure Stream Analytics job in the correct order.

Question 15

A company stores historical sales data in Azure Blob Storage. The data is accessed about once per month and must be immediately available for read when needed. The company wants to minimize storage cost while meeting the access requirement. Which access tier should they use?

Accepted Answer

Cool tier. The Cool tier is optimal because the data is accessed only once per month, but must be immediately available for reads. Cool tier offers lower storage cost than Hot tier while maintaining low-latency access (no retrieval delay), meeting the requirement at minimal cost.

Answer

Hot tier

Answer

Cold tier

Answer

Archive tier