Free Microsoft DP-420 practice questions

Correct answer: B, D

B. SELECT TOP i.name, i.types, i.orders FROM items i WHERE EXISTS(SELECT VALUE t FROM t IN i.types WHERE t.name = 'fruit') ORDER BY i.orders DESC D. SELECT TOP i.name, i.types, i.orders FROM items i WHERE ARRAY_CONTAINS(i.types, {name: 'fruit'}) ORDER BY i.orders DESC Both options B and D correctly retrieve the top five most ordered fruits in descending order. Option B uses the EXISTS operator with a subquery on the types array, checking if any element has name = 'fruit', then ordering by orders in descending order. Option D uses the ARRAY_CONTAINS function, which is a more direct method to check if the types array contains an object with name = 'fruit', also ordering by orders descending. Both approaches properly filter for items where 'fruit' is in the types array and sort by order count from highest to lowest, which is required to identify the top ordered items.

Why the other options are wrong

• A. This option orders by i.orders and then i.types, placing types as a secondary sort criterion, which does not produce the correct descending order by orders needed for the top fruits report.
• C. This option orders by i.types DESC instead of i.orders DESC, sorting alphabetically by type rather than by order count, which fails to identify the most ordered fruits.

Correct answer: D. Create a new synthetic key that contains deviceId and a random number.

A partition key must distribute writes evenly across partitions to avoid hot partitions and skew. Using timestamp (A) would concentrate all writes to a single partition at any given moment, creating severe hot partitions. Using deviceManufacturer (C) would skew data toward devices from the most common manufacturer. Using only deviceId (B) would work better but still risks uneven distribution if some devices send data more frequently. Adding a random number to deviceId (D) ensures even distribution of writes across partitions regardless of device frequency patterns, device manufacturer distribution, or time-based clustering, which is essential for IoT scenarios with continuous writes every second.

Why the other options are wrong

• A. Timestamp as partition key would route all simultaneous writes to the same partition, creating an immediate hot partition bottleneck.
• B. Adding deviceManufacturer creates skew because writes would cluster by manufacturer; if one manufacturer produces most devices, that partition receives disproportionate load.
• C. deviceManufacturer alone results in poor distribution since all writes from a manufacturer go to one partition, violating the requirement to minimize partition skew.

Correct answer: C. Create a container that contains a document for each Author and a document for each Book. In each Book document, embed authorId.

Author and a document for each Book. In each Book document, embed authorId. For the most common query that lists books for a given authorId, option C provides optimal latency and cost by storing Author and Book documents in a single container with authorId embedded in each Book document. This enables a single, efficient partition-key query to retrieve all books by an author without joins or cross-partition operations. Denormalizing authorId into Book documents eliminates the need for separate Bookauthorlnk lookups while keeping the container design simple and cost-effective, as queries execute within a single partition when properly keyed.

Why the other options are wrong

• A. Embedding bookId in Author documents creates a one-to-many relationship stored on the parent, making it inefficient for querying books by author and causing large document sizes when authors have many books.
• B. Storing Author, Book, and Bookauthorlnk as separate documents in the same container requires multiple queries or joins to retrieve books for an author, increasing latency and read costs.
• D. Embedding Bookauthorlnk data into both Author and Book documents creates redundant denormalization without solving the core query problem and adds unnecessary complexity and storage overhead.

Correct answer: A. [{"A": false, "B": true, "C": false}]

The IS_NUMBER function in Azure Cosmos DB checks if a value is a number type. Column A evaluates IS_NUMBER("1234") which is a string literal, returning false. Column B evaluates IS_NUMBER(1234) which is a numeric literal, returning true. Column C evaluates IS_NUMBER({prop: 1234}) which is an object/document type, not a number, returning false. The query returns a single document with these three boolean results.

Why the other options are wrong

• B. Incorrectly assumes the string "1234" is recognized as a number by IS_NUMBER.
• C. Incorrectly assumes the object literal {prop: 1234} would be evaluated as a number type.
• D. Incorrectly assumes all three expressions would evaluate to true, which contradicts how IS_NUMBER handles string and object types.

Correct answer: C, E

C. Register the trigger as a pre-trigger. E. For each create request, set the trigger name in RequestOptions. To ensure a pre-trigger executes before an item is inserted into a Cosmos DB container, you must both register the trigger as a pre-trigger (option C) and specify the trigger name in the RequestOptions for each create request (option E). The trigger must be explicitly registered with pre-trigger semantics, and the SDK requires you to reference the trigger by name in the RequestOptions when making the create call so it knows which trigger to execute.

Why the other options are wrong

• A. Appending 'pre' to the function name is not a valid Cosmos DB trigger naming convention; registration type is determined programmatically.
• B. Access conditions in RequestOptions are not used to invoke triggers; they control conditional writes based on entity tags.
• D. Session consistency level in RequestOptions does not affect whether a trigger executes; it only controls read consistency semantics.

Correct answer: A. Yes

Configuring an Azure Monitor alert to trigger an Azure function meets the goal. Azure Monitor can track Cosmos DB metrics including normalized request units per second through diagnostic settings and metric alerts. When the alert condition is met (normalized RU/s exceeds the threshold), it can trigger an Azure function via an action group webhook, achieving the stated requirement.

Correct answer: B. No

Configuring an Azure function with a Cosmos DB trigger does not meet the goal. A Cosmos DB trigger in Azure Functions is designed to execute when documents change in the change feed (on insert, update, or delete), not based on performance metrics like normalized request units per second. Monitoring RU consumption requires metric-based alerting, not data change triggers.

Correct answer: B. No

Using the change feed processor to read the change feed does not meet the goal. The change feed processor monitors data changes in documents (inserts, updates, deletes), not performance metrics like normalized request units per second. While the change feed can trigger application logic, it cannot monitor or respond to RU consumption thresholds, which require metric-based monitoring instead.

Correct answer: B. customerId

The partition key should be customerId because the application's primary access pattern is customers frequently viewing their past order history. Selecting customerId as the partition key ensures that all orders for a given customer are stored together in the same logical partition, enabling efficient queries for a customer's order history with minimal cros
-artition overhead. This minimizes costs by reducing RU consumption for the most common query pattern. While orderId might seem logical, it would scatter related customer orders across multiple partitions, making historical lookups inefficient and expensive. orderDate and id are poor choices as they either don't align with the access patterns or lack the necessary cardinality and distribution properties.

Why the other options are wrong

• A. orderId as partition key would scatter a single customer's orders across many partitions, making customer order history queries expensive and cross-partition.
• C. orderDate has poor cardinality and would create hot partitions, making it inefficient for the stated access patterns and expensive to query.
• D. The id field (likely the document ID) is not an appropriate partition key for this workload and would not optimize for customer history queries.

Correct answer: C, D

C. There will always be a hot partition D. Queries will run cross-partition Using orderDate as the partition key creates two critical problems. First, there will always be a hot partition because all orders received on a given day are concentrated on that date's partition, while older date partitions receive no new writes. This creates uneven load distribution. Second, queries will run cross-partition whenever customers view their order history, since a single customer's orders span multiple dates across different partitions. This defeats the purpose of partitioning and reduces query performance. The orderDate lacks sufficient cardinality for even distribution and does not align with the primary query pattern (customer viewing their own orders).

Why the other options are wrong

• A. There is no maximum limit on the number of partition key values; orderDate can have thousands of unique values without exceeding any system constraint.
• B. Storage per partition would not be exceeded; with one million orders daily at 2 KB each, the daily load distributes across date partitions, and Azure Cosmos DB can handle much larger partition sizes before hitting storage limits.