DP-900 Timed Practice Test 1 — 10 Questions

Question 1

An e-commerce application processes customer orders. When an order is placed, the system must decrement the inventory count and process the payment. The application ensures that either both operations complete successfully or both are rolled back if any error occurs. Which database property does this guarantee?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work: either all operations within it (decrement inventory and process payment) complete successfully, or none are applied. If any part fails, the database rolls back all changes, maintaining the 'all-or-nothing' guarantee. This is the core property described in the scenario.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 2

A company runs an e-commerce application on Azure SQL Database. The application experiences heavy read traffic from reporting dashboards that query the same tables as the transactional workload. This causes performance degradation for the application. The company needs a solution that offloads reporting queries to a read-only copy that stays synchronized within minutes, without impacting transactional performance. Which Azure SQL Database feature should they use?

Accepted Answer

Active geo-replication. Active geo-replication creates a readable secondary replica of the Azure SQL Database that can be used for read-only workloads like reporting dashboards. The secondary replica stays synchronized with the primary database using asynchronous replication, typically within seconds to minutes, ensuring near-real-time data without impacting transactional performance on the primary.

Answer

Auto-failover groups

Answer

Elastic pools

Answer

Query Performance Insight

Question 3

A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?

Accepted Answer

A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount). The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.

Answer

A clustered index on OrderID

Answer

A non-clustered index on Status

Answer

A non-clustered index on (OrderDate, TotalAmount)

Question 4

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 5

A gaming company stores player profiles as JSON documents. Each profile includes standard fields like playerId, username, and email, as well as optional fields such as achievements, gamePreferences, and friendsList. The application needs to look up profiles by playerId with low latency (under 10 ms) and also run SQL-like queries to find players who have a specific achievement. Which Azure Cosmos DB API should they choose?

Accepted Answer

D. SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports JSON documents with flexible schemas (including optional fields like achievements) and provides low-latency point reads by playerId (partition key) under 10 ms. It also enables SQL-like queries (e.g., SELECT * FROM c WHERE ARRAY_CONTAINS(c.achievements, 'specificAchievement')) to find players with a specific achievement, which aligns directly with the requirement.

Answer

A. Table API

Answer

B. Cassandra API

Answer

C. MongoDB API

Question 6

A company is migrating an on-premises SQL Server database to Azure. They want to ensure that database administrators (DBAs) can perform administrative tasks but cannot view sensitive customer data in query results. Which Azure SQL feature should they implement?

Accepted Answer

Always Encrypted. Always Encrypted ensures that sensitive data is encrypted at all times, including during query processing, and that the encryption keys are never revealed to the database engine. This allows DBAs to perform administrative tasks (e.g., backups, index maintenance) while being unable to view the plaintext data in query results, because the decryption occurs only on the client side.

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption

Answer

Row-Level Security

Question 7

A global e-commerce company uses Azure SQL Database for its product catalog. The application experiences high read traffic for product detail pages, often running the same queries for popular items. The database’s write workload is moderate. The company wants to improve read performance without increasing the cost of the primary database tier and without changing the application code. Which Azure SQL Database feature should they implement?

Accepted Answer

Read scale-out. Read scale-out (A) is correct because it offloads read-only queries to a secondary replica of the Azure SQL Database without changing the application code. By setting `ApplicationIntent=ReadOnly` in the connection string, the database routes read queries to a read-only replica, improving performance for high-read workloads like product detail pages while keeping the primary tier unchanged and avoiding additional cost for a higher tier.

Answer

Active geo-replication

Answer

Azure SQL Database elastic pool

Answer

Query Performance Insight

Question 8

A global e-commerce company uses Azure SQL Database for its product catalog. The database is hosted in the West US region. To ensure the catalog remains available if West US experiences an outage, the company wants to configure a secondary database in East US that can be used for reads and can be automatically promoted to primary during a disaster. They require a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 minutes. Which feature should they implement?

Accepted Answer

Auto-failover groups. Auto-failover groups (Option B) are the correct choice because they provide automatic, orchestrated failover of a primary Azure SQL Database to a secondary region (East US) during an outage, meeting the RPO of less than 5 seconds (typically 5–10 seconds for active geo-replication) and RTO of less than 30 minutes (usually under 1 hour). The secondary database can be used for read-only queries, and the failover group ensures the entire group of databases fails over as a unit, maintaining the same connection string.

Answer

Active geo-replication

Answer

Geo-restore

Answer

Transactional replication

Question 9

A manufacturing company stores IoT sensor data as JSON documents in Azure Cosmos DB. Each document has fields: deviceId (high cardinality, many unique values), timestamp, temperature, and humidity. The most frequent query is: 'Retrieve all readings for a specific deviceId from the last hour.' To minimize Request Unit (RU) consumption, which combination of partition key and indexing policy should be chosen?

Accepted Answer

Partition key: deviceId, Indexing: automatic on all properties. Option A is correct because deviceId is the most frequently filtered attribute (in the WHERE clause), making it an ideal partition key that ensures queries are scoped to a single physical partition, minimizing cross-partition fan-out. Automatic indexing on all properties allows efficient filtering on timestamp within the partition, while the index on deviceId is not strictly needed since the partition key itself routes the query, but it does not harm RU consumption significantly. This combination balances query performance and RU cost for the described workload.

Answer

Partition key: timestamp, Indexing: automatic on all properties

Answer

Partition key: deviceId, Indexing: none

Answer

Partition key: temperature, Indexing: automatic on all properties

Question 10

A social media application stores user posts in Azure Cosmos DB using the NoSQL API. Each document includes: PostID (unique), UserID, Timestamp, Content. The most common query is: 'Get all posts for a specific UserID, sorted by Timestamp descending.' Which partition key should be chosen to distribute load evenly across physical partitions while also supporting this query efficiently?

Accepted Answer

UserID. UserID is the correct partition key because it evenly distributes write operations across physical partitions (each user has a unique ID) and directly supports the most common query: filtering by UserID. With UserID as the partition key, the query 'Get all posts for a specific UserID, sorted by Timestamp descending' becomes a single-partition query (using the partition key in the WHERE clause), which is efficient and avoids cross-partition fan-out. This design also allows Cosmos DB to use the Timestamp field as a sort key within each logical partition, enabling efficient sorting without additional indexing overhead.

Answer

PostID

Answer

Timestamp

Answer

Content