Microsoft Azure Data Fundamentals DP-900 practice test

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?

Trap 1: Consistency

Consistency ensures that a transaction brings the database from one valid state to another, maintaining data integrity rules. It does not directly guarantee the all-or-nothing execution of operations.

Trap 2: Isolation

Isolation ensures that concurrent transactions do not interfere with each other. It addresses visibility of intermediate transaction states, not the atomicity of operations within a single transaction.

Trap 3: Durability

Durability guarantees that once a transaction is committed, its changes persist even in the event of a system failure. It does not relate to the all-or-nothing execution of a transaction.

A
Atomicity
Atomicity ensures that a transaction is an all-or-nothing operation. If any part of the transaction fails, the entire transaction is rolled back, preventing partial updates.
B
Consistency
Why wrong: Consistency ensures that a transaction brings the database from one valid state to another, maintaining data integrity rules. It does not directly guarantee the all-or-nothing execution of operations.
C
Isolation
Why wrong: Isolation ensures that concurrent transactions do not interfere with each other. It addresses visibility of intermediate transaction states, not the atomicity of operations within a single transaction.
D
Durability
Why wrong: Durability guarantees that once a transaction is committed, its changes persist even in the event of a system failure. It does not relate to the all-or-nothing execution of a transaction.

Question 2mediummultiple choice

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?

Trap 1: Auto-failover groups

Failover groups enable automatic failover of multiple databases, but they do not primarily focus on offloading read traffic; the secondary in a failover group is not readable unless you also enable geo-replication.

Trap 2: Elastic pools

Elastic pools allow multiple databases to share resources but do not create a separate read-only copy for offloading queries.

Trap 3: Query Performance Insight

Query Performance Insight is a monitoring tool that helps identify performance bottlenecks, but it does not offload queries to a replica.

A
Auto-failover groups
Why wrong: Failover groups enable automatic failover of multiple databases, but they do not primarily focus on offloading read traffic; the secondary in a failover group is not readable unless you also enable geo-replication.
B
Active geo-replication
Correct. Active geo-replication provides a readable secondary database that can serve reporting queries while the primary handles transactional workloads. The replication lag is typically within minutes.
C
Elastic pools
Why wrong: Elastic pools allow multiple databases to share resources but do not create a separate read-only copy for offloading queries.
D
Query Performance Insight
Why wrong: Query Performance Insight is a monitoring tool that helps identify performance bottlenecks, but it does not offload queries to a replica.

Question 3mediummultiple choice

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?

Trap 1: A clustered index on OrderID

This index optimizes lookups by OrderID but does not help queries filtering on CustomerID and OrderDate.

Trap 2: A non-clustered index on Status

Filtering on Status is not the frequent query pattern; this index does not support the CustomerID and OrderDate filters.

Trap 3: A non-clustered index on (OrderDate, TotalAmount)

Although OrderDate is used, the leading column is not CustomerID, so the index is less selective and may not be as effective for this query.

A
A clustered index on OrderID
Why wrong: This index optimizes lookups by OrderID but does not help queries filtering on CustomerID and OrderDate.
B
A non-clustered index on Status
Why wrong: Filtering on Status is not the frequent query pattern; this index does not support the CustomerID and OrderDate filters.
C
A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
This composite index directly supports the filter predicate (CustomerID and OrderDate) and includes TotalAmount to make it a covering index, reducing I/O.
D
A non-clustered index on (OrderDate, TotalAmount)
Why wrong: Although OrderDate is used, the leading column is not CustomerID, so the index is less selective and may not be as effective for this query.

Question 4mediummultiple choice

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?

Trap 1: PlayerID

Using PlayerID would distribute data across many partitions. Queries by GameID would need to fan out to all partitions that contain Players for that game, increasing RU consumption.

Trap 2: Score

Score has high cardinality and is used for ordering, not filtering. Using Score as partition key would not narrow the query to a single partition; the query would need to read all partitions and then sort.

Trap 3: Timestamp

Timestamp is not used in the filter. A partition key on Timestamp would cause the query to scatter to many partitions (since scores for a given game could have many timestamps), leading to high RU consumption.

A
PlayerID
Why wrong: Using PlayerID would distribute data across many partitions. Queries by GameID would need to fan out to all partitions that contain Players for that game, increasing RU consumption.
B
GameID
GameID is the attribute used in the query filter. Choosing it as the partition key ensures that all scores for a given game are co-located in one partition, allowing a point query to that single partition and minimizing RU cost.
C
Score
Why wrong: Score has high cardinality and is used for ordering, not filtering. Using Score as partition key would not narrow the query to a single partition; the query would need to read all partitions and then sort.
D
Timestamp
Why wrong: Timestamp is not used in the filter. A partition key on Timestamp would cause the query to scatter to many partitions (since scores for a given game could have many timestamps), leading to high RU consumption.

Question 5mediummultiple choice

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?

Trap 1: A

The Table API is designed for key-value storage with a fixed schema; it does not support querying nested JSON fields or flexible schema as effectively as the SQL API.

Trap 2: B

The Cassandra API uses a wide-column model and CQL (Cassandra Query Language), which is not the same as SQL and may require more complex modeling to handle optional fields.

Trap 3: C

The MongoDB API supports JSON documents and queries, but it uses MongoDB's query syntax, not standard SQL. While it can meet the requirement, the SQL API is a more natural fit when the team wants SQL-like queries.

A
A. Table API
Why wrong: The Table API is designed for key-value storage with a fixed schema; it does not support querying nested JSON fields or flexible schema as effectively as the SQL API.
B
B. Cassandra API
Why wrong: The Cassandra API uses a wide-column model and CQL (Cassandra Query Language), which is not the same as SQL and may require more complex modeling to handle optional fields.
C
C. MongoDB API
Why wrong: The MongoDB API supports JSON documents and queries, but it uses MongoDB's query syntax, not standard SQL. While it can meet the requirement, the SQL API is a more natural fit when the team wants SQL-like queries.
D
D. SQL (Core) API
The SQL API stores JSON documents and supports querying with a SQL dialect. Point reads by partition key (playerId) are low-latency, and SQL queries can easily filter on optional fields like achievements. This makes it the best choice.

Question 6mediummultiple choice

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?

Trap 1: Dynamic Data Masking

Dynamic Data Masking hides sensitive data in query results for non-privileged users, but DBAs (with the 'UNMASK' permission) can still see the original data.

Trap 2: Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts the database at rest (on disk) but does not protect data from being viewed by users with query permissions, including DBAs.

Trap 3: Row-Level Security

Row-Level Security restricts row access based on user context, but a DBA with database owner privileges can bypass the security policy and see all rows.

A
Dynamic Data Masking
Why wrong: Dynamic Data Masking hides sensitive data in query results for non-privileged users, but DBAs (with the 'UNMASK' permission) can still see the original data.
B
Always Encrypted
Always Encrypted encrypts data on the client side, so the database never sees plaintext. DBAs cannot access the encryption keys and therefore cannot view the sensitive data.
C
Transparent Data Encryption
Why wrong: Transparent Data Encryption (TDE) encrypts the database at rest (on disk) but does not protect data from being viewed by users with query permissions, including DBAs.
D
Row-Level Security
Why wrong: Row-Level Security restricts row access based on user context, but a DBA with database owner privileges can bypass the security policy and see all rows.