DP-900 Practice Exam 1 — 20 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

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.