Design and implement data models — DP-420 Practice Quiz

Question 1

You maintain a Cosmos DB container named 'orders' partitioned by /customerId. A reporting dashboard runs the query SELECT VALUE MAX(c.orderTotal) FROM c to display the single largest order value across all customers. The dashboard runs this query once every few seconds and RU consumption has become a concern. Which approach reduces the RU cost of repeatedly obtaining this value most effectively?

Accepted Answer

Maintain a separate 'stats' item updated via the change feed that stores the running maximum orderTotal, and issue a point read against it from the dashboard.

Answer

Add an ORDER BY c.orderTotal DESC clause with a TOP 1 to the existing query so the engine can use the index more efficiently.

Answer

Enable autoscale on the container so the query automatically gets more RU/s when the dashboard runs.

Answer

Switch the SDK from gateway mode to direct mode so the aggregate query bypasses cross-partition fan-out.

Question 2

You maintain a Cosmos DB container of blog posts where each document has a 'tags' property that is an array of strings, for example ["azure", "cosmosdb", "nosql"]. Your application must return all posts that include the tag "azure" anywhere in the array. You want a single SQL query expression that correctly filters these documents. Which WHERE clause should you use?

Accepted Answer

WHERE ARRAY_CONTAINS(c.tags, "azure")

Answer

WHERE c.tags = "azure"

Answer

WHERE ARRAY_LENGTH(c.tags) > 0 AND c.tags[0] = "azure"

Answer

WHERE CONTAINS(c.tags, "azure")

Question 3

You manage a Cosmos DB container for an e-commerce catalog. Traffic is highly variable: it sits near 800 RU/s for most of the day but spikes to roughly 9,000 RU/s for two to three unpredictable hours during flash sales. The team wants to avoid throttling during spikes while minimizing cost during idle periods, and they do not want to manually adjust throughput or write scaling code. Which throughput configuration should you choose?

Accepted Answer

Configure autoscale throughput with a maximum of 10,000 RU/s so the container scales between 1,000 and 10,000 RU/s automatically.

Answer

Provision standard (manual) throughput at 9,000 RU/s to guarantee capacity for the spikes.

Answer

Use serverless mode so you only pay per request consumed with no capacity planning.

Answer

Provision standard (manual) throughput at 1,000 RU/s and rely on burst capacity to absorb the flash-sale spikes.

Question 4

An e-commerce team needs to ingest 500,000 order documents nightly into a Cosmos DB container partitioned by /customerId. The documents span thousands of different customerId values, and each document is independent — partial failures are acceptable and should simply be retried. The team wants maximum ingestion throughput with the least code complexity. Which approach should they use?

Accepted Answer

Enable bulk execution (AllowBulkExecution = true) in the SDK and issue concurrent CreateItemAsync calls

Answer

Group the documents and use TransactionalBatch operations across the different customerId values

Answer

Wrap all inserts in a single stored procedure executed once per night

Answer

Insert documents sequentially with individual CreateItemAsync calls to preserve ordering

Question 5

A developer is writing a .NET application to migrate 50 million JSON documents into an Azure Cosmos DB for NoSQL container that has been provisioned with autoscale up to 40,000 RU/s. The migration runs from an Azure VM in the same region as the Cosmos DB account and must complete as quickly as possible while maximizing throughput utilization. Which combination of SDK configuration settings should the developer use?

Accepted Answer

Enable Bulk mode by setting AllowBulkExecution = true on CosmosClientOptions, and use Direct connection mode.

Answer

Use Gateway connection mode with AllowBulkExecution = true to reduce the number of open TCP connections.

Answer

Wrap all inserts in a single stored procedure and call it repeatedly to guarantee transactional consistency across the import.

Answer

Issue individual CreateItemAsync calls sequentially with Direct connection mode and rely on automatic retries for throttled requests.

Question 6

A retail catalog container stores product documents, each with a nested 'reviews' array containing objects like { rating, verified, text }. A developer needs a query that returns each product's id and title along with only the count of reviews that have a rating of 5 AND verified = true. The team wants to minimize RU charge by avoiding returning the full reviews array to the client and by evaluating the filter server-side. Which SQL query approach best meets these requirements?

Accepted Answer

Use a correlated subquery in the SELECT: SELECT p.id, p.title, (SELECT VALUE COUNT(1) FROM r IN p.reviews WHERE r.rating = 5 AND r.verified = true) AS topReviews FROM p

Answer

SELECT p.id, p.title, p.reviews FROM p, then filter and count the reviews in application code after retrieval

Answer

SELECT p.id, p.title, COUNT(r) FROM p JOIN r IN p.reviews WHERE r.rating = 5 AND r.verified = true GROUP BY p.id, p.title, with no subquery

Answer

SELECT p.id, p.title, ARRAY_LENGTH(p.reviews) AS topReviews FROM p WHERE ARRAY_CONTAINS(p.reviews, { "rating": 5, "verified": true }, true)

Question 7

You maintain a Cosmos DB for NoSQL container named 'Orders' partitioned on '/customerId'. A reporting dashboard runs the query: SELECT * FROM c WHERE c.status = 'Shipped' AND c.region = 'EU'. Neither 'status' nor 'region' is the partition key, and the container spans 40 physical partitions. Users complain the query consumes excessive RUs. Which explanation and remediation is most accurate?

Accepted Answer

The query is a cross-partition (fan-out) query that must be evaluated on all physical partitions; adding a composite index on (status, region) or restructuring to include customerId in the filter reduces the cost.

Answer

The query is a point read that is inefficient because status is not indexed; enabling the default indexing policy will convert it to a single-partition lookup.

Answer

The high cost is caused by the gateway connection mode; switching to direct mode eliminates the fan-out entirely.

Answer

The query already targets a single partition because status equals a constant; the RU cost is unrelated to partitioning and can only be lowered by increasing provisioned throughput.

Question 8

You design a Cosmos DB for NoSQL solution for an e-commerce site. Product documents are stored in a 'products' container, and customer reviews are stored in a separate 'reviews' container partitioned by /productId. The most common query on the reviews page displays each review along with the product's current display name. Product names are edited rarely (a few times per year), but review pages are read millions of times per day. Which data modeling approach minimizes RU cost for the high-volume read?

Accepted Answer

Denormalize (embed a copy of) the product display name into each review document, and update the copies with a background process when a product name changes.

Answer

Store only productId in each review and perform a separate point read against the products container for each review displayed.

Answer

Use a cross-partition JOIN query across the products and reviews containers to combine the data at read time.

Answer

Move reviews into the products container as an embedded array so every review is retrieved with the product.

Question 9

You maintain an Azure Cosmos DB for NoSQL container storing customer orders, partitioned by /customerId. Each order document contains an array named items, where each element has a productId and quantity. A reporting service must return, for a single specified customerId, the number of distinct products that customer has ever ordered across all their order documents. You want the most RU-efficient query that returns a single scalar value. Which query approach should you use?

Accepted Answer

SELECT VALUE COUNT(1) FROM (SELECT DISTINCT i.productId FROM c JOIN i IN c.items WHERE c.customerId = 'C123') AS d

Answer

SELECT c.items FROM c WHERE c.customerId = 'C123', then count distinct productIds in application code after loading all documents

Answer

SELECT DISTINCT i.productId FROM c JOIN i IN c.items WHERE c.customerId = 'C123' and read the returned row count from the SDK

Answer

SELECT COUNT(DISTINCT i.productId) FROM c JOIN i IN c.items WHERE c.customerId = 'C123'

Question 10

You are designing a Cosmos DB data model for an e-commerce platform. Each order contains between 1 and 30 line items. Line items are always read together with the order, are never queried independently, and are never updated after the order is placed. Orders are read frequently and the entire order document averages 8 KB. Which data modeling approach best fits this access pattern?

Accepted Answer

Embed the line items as an array inside the order document.

Answer

Store each line item as a separate document referencing the order by orderId.

Answer

Create a separate container for line items and join it to orders at query time.

Answer

Store line items in a normalized relational-style structure with foreign keys across three containers.

Question 11

A startup is building a proof-of-concept application and wants to minimize Azure Cosmos DB costs during initial development. The team decides to enable the free tier when creating a new Azure Cosmos DB for NoSQL account. Their expected steady-state workload needs about 800 RU/s of provisioned throughput and 20 GB of storage. Which statement correctly describes what the free tier provides and the impact on this workload?

Accepted Answer

The free tier provides the first 1000 RU/s and 25 GB of storage free per account, so this workload runs entirely without throughput or storage charges.

Answer

The free tier only applies to serverless accounts, so the team must switch to serverless to receive any discount.

Answer

The free tier can be enabled on multiple accounts per Azure subscription, allowing the team to spread the workload across accounts to stay free.

Answer

The free tier waives request charges but still bills for all provisioned storage, so the 20 GB will incur storage costs.

Question 12

You are developing a .NET application that uses the Azure Cosmos DB SDK to perform high-volume point reads and writes with the lowest possible latency. The application runs inside a corporate network where outbound traffic is restricted by a strict firewall that only permits HTTPS traffic on port 443. During load testing in this environment you notice that the SDK cannot establish connections when configured for the fastest connectivity mode. Which change should you make to allow the SDK to connect through the firewall while understanding the trade-off involved?

Accepted Answer

Switch the SDK to Gateway mode, accepting slightly higher latency because all requests route through the gateway over port 443.

Answer

Keep Direct mode and ask the network team to open the TCP port range 10000–20000 for the account.

Answer

Switch to Gateway mode, which uses direct TCP connections to replica nodes and provides lower latency than Direct mode.

Answer

Enable bulk execution mode in the SDK, which automatically bypasses the firewall by tunneling over port 443.

Question 13

You maintain a Cosmos DB container for an e-commerce platform that stores orders, partitioned by /customerId. A reporting feature must return, for a single specified customer, the total amount spent per month. The query currently uses GROUP BY on month and executes across all partitions, consuming more RUs than expected. Which change will most effectively reduce the RU cost of this specific query?

Accepted Answer

Add a WHERE clause filtering on customerId so the query targets a single logical partition.

Answer

Switch the container's partition key to /month to co-locate all orders by month.

Answer

Move the GROUP BY logic into a stored procedure so aggregation runs server-side.

Answer

Enable autoscale throughput so the query has more RUs available during peak reporting.

Question 14

A gaming company stores telemetry in a Cosmos DB container partitioned by /gameId. One extremely popular game generates over 40 GB of telemetry for its single gameId value, and writes for that game are now failing. The team wants to keep gameId as the primary way to group data while resolving the storage ceiling. Which redesign best addresses the problem?

Accepted Answer

Recreate the container with a hierarchical partition key of /gameId then /sessionId, distributing each game's data across many logical partitions.

Answer

Increase the provisioned throughput (RU/s) on the container so the large partition can accept more writes.

Answer

Enable autoscale on the container to automatically expand the storage limit of the logical partition.

Answer

Switch the account to serverless mode, which removes the 20 GB per-logical-partition storage limit.

Question 15

You are designing a Cosmos DB container for a logistics platform that stores shipment events. Each shipment belongs to a specific carrier, and there are only 8 carriers. Within a carrier, events are queried almost exclusively by shipmentId. The largest carrier will generate roughly 400 GB of event data over the container's lifetime. Most read queries filter by both carrierId and shipmentId, while a nightly report scans all events for a single carrier. Which partitioning strategy best balances query efficiency and avoids exceeding logical partition limits?

Accepted Answer

Use a hierarchical partition key of carrierId then shipmentId

Answer

Use carrierId alone as the partition key

Answer

Use shipmentId alone as the partition key

Answer

Use a synthetic partition key concatenating a random 1–100 suffix with carrierId

Question 16

An e-commerce container stores order documents where each order has a nested array named 'items', with each item containing a 'category' field. A developer needs a query that returns one row per order-item combination, filtered to only items where category = 'Electronics', so downstream reporting can list each electronics line individually. Which SQL query correctly accomplishes this in Azure Cosmos DB for NoSQL?

Accepted Answer

SELECT o.id, i.category FROM orders o JOIN i IN o.items WHERE i.category = 'Electronics'

Answer

SELECT o.id, o.items.category FROM orders o WHERE o.items.category = 'Electronics'

Answer

SELECT o.id, i.category FROM orders o WHERE ARRAY_CONTAINS(o.items, 'Electronics')

Answer

SELECT o.id, i.category FROM orders o JOIN orders i WHERE i.category = 'Electronics'

Question 17

You are modeling a blogging platform in Azure Cosmos DB for NoSQL. Each Post can have many Tags, and each Tag applies to many Posts (a many-to-many relationship). Tag metadata (name, description, color) is edited rarely but read constantly. Users frequently query 'all posts for a given tag' and 'all tags on a given post'. Tag descriptions must stay reasonably current across the site. Which modeling approach best balances read performance and update maintainability?

Accepted Answer

Store Tags in their own container as the source of truth, and embed only lightweight tag references (tag id plus denormalized name) in each Post.

Answer

Embed the full Tag document (name, description, color) inside every Post that uses it, with no separate Tag container.

Answer

Embed the full list of Post documents inside each Tag document so a tag lookup returns all its posts in one read.

Answer

Create a single container where every Post-Tag combination is stored as a separate item duplicating both the full post body and full tag metadata.

Question 18

You are designing a Cosmos DB container for a social media platform. Each user document embeds an array of posts. Product analytics show that active users can accumulate tens of thousands of posts over time, and the document grows continuously. Users frequently read their own profile but only occasionally query all their posts at once. What is the most appropriate data modeling decision?

Accepted Answer

Reference posts as separate items in a posts container partitioned by userId, and keep only a small profile summary embedded in the user document.

Answer

Keep embedding all posts in the user document and rely on autoscale throughput to absorb the larger reads.

Answer

Embed the posts but enable TTL on the user document so old posts are automatically purged.

Answer

Split the posts array across multiple properties in the same document to stay under the size limit.

Question 19

A retail company runs an Azure Cosmos DB for NoSQL container configured with autoscale throughput at a maximum of 20,000 RU/s. During most of the day, actual utilization stays near 2,000 RU/s, but a nightly batch job briefly pushes usage to the configured maximum. The finance team asks the developer to explain how billing works for this autoscale configuration so they can forecast costs. Which statement correctly describes the hourly billing behavior?

Accepted Answer

You are billed each hour for the highest RU/s the container scaled to within that hour, with a minimum floor of 10% of the maximum (2,000 RU/s).

Answer

You are billed a flat rate for the full 20,000 RU/s maximum every hour regardless of actual utilization.

Answer

You are billed only for the RU consumed per request, identical to serverless pricing, with no throughput floor.

Answer

You are billed for the average RU/s consumed across the entire hour, so brief spikes have negligible cost impact.

Question 20

An e-commerce application stores shopping cart documents in an Azure Cosmos DB for NoSQL container. Multiple client sessions can update the same cart concurrently. The development team must ensure that when two users update the same cart at nearly the same time, one update does not silently overwrite the other, without introducing pessimistic locking that would reduce throughput. Which approach should the team implement?

Accepted Answer

Read the item, capture its _etag, and include an If-Match header with that value on the replace operation, retrying if a 412 Precondition Failed is returned.

Answer

Set a Time to Live (TTL) on the container so stale cart documents automatically expire before conflicting writes occur.

Answer

Use a transactional batch that groups the read and write operations for the cart into a single atomic call.

Answer

Enable the strong consistency level on the account so all writes are serialized and conflicts cannot happen.

More DP-420 practice