Describe considerations for working with non-relational data on Azure →mediumMultiple ChoiceObjective-mapped
Azure Cosmos DB SQL API for JSON Documents
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?
Quick Answer
The answer is the SQL (Core) API. This is the correct choice because Azure Cosmos DB’s SQL API natively supports JSON documents with flexible schemas, allowing optional fields like achievements and friendsList without requiring schema changes. It provides low-latency point reads under 10 ms when querying by playerId, which serves as the partition key, and enables SQL-like queries using functions like ARRAY_CONTAINS to filter players with specific achievements. On the DP-900 exam, this scenario tests your understanding of which API aligns with JSON document storage and querying needs; a common trap is confusing the Table API (for key-value) or MongoDB API (for BSON) when the requirement explicitly mentions SQL-like syntax. Remember that the SQL API is the default, universal choice for JSON documents in Cosmos DB, and its name directly reflects its ability to run familiar T-SQL queries against schemaless data. A helpful memory tip: think “SQL = Structured Query Language for JSON,” and if the question says “SQL-like queries” or “JSON documents,” the answer is always the SQL (Core) API.
⚠ Common exam trap
Candidates often choose the MongoDB API because it is associated with JSON documents, but they overlook the explicit requirement for SQL-like queries, which only the SQL (Core) API supports natively among the Azure Cosmos DB APIs.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A. Table API
Why it's wrong here
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.
When this WOULD be correct
A company needs to store large amounts of structured, non-relational data (e.g., sensor readings) with a simple key-based lookup and does not require complex queries or JSON support. The Table API offers low-latency access and a familiar tabular interface.
- ✗
B. Cassandra API
Why it's wrong here
The Cassandra API uses the Cassandra Query Language (CQL) and a wide-column store, which cannot natively query nested JSON fields like achievements without flattening the schema. This fails the requirement to run SQL-like queries directly against optional JSON properties. It is tempting because Cassandra offers low-latency lookups by partition key, which would satisfy the playerId requirement, and it would be correct for a high-write, horizontally scalable key-value or wide-column workload without JSON document queries.
When this WOULD be correct
A company needs a globally distributed, low-latency, linearly scalable database for time-series data (e.g., IoT sensor readings) that requires high write throughput and uses the Cassandra Query Language (CQL) for queries.
- ✗
C. MongoDB API
Why it's wrong here
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.
When this WOULD be correct
A company stores JSON documents and needs to use MongoDB drivers and tools (e.g., aggregation pipeline) for queries, with no requirement for SQL syntax. They also need compatibility with existing MongoDB applications.
- ✓
D. SQL (Core) API
Why this is correct
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.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓D. SQL (Core) APICorrect answer▾
Why this is correct
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.
✗A. Table APIWrong answer — click to see why▾
Why this is wrong here
The Table API provides key-value storage with a schema-less design, but it does not support SQL-like queries or JSON documents natively, making it unsuitable for querying nested fields like achievements.
★ When this WOULD be the correct answer
A company needs to store large amounts of structured, non-relational data (e.g., sensor readings) with a simple key-based lookup and does not require complex queries or JSON support. The Table API offers low-latency access and a familiar tabular interface.
Why candidates choose this
Candidates may think the Table API is appropriate because it is simple and supports key-value lookups, overlooking the need for SQL-like queries on nested JSON fields.
✗B. Cassandra APIWrong answer — click to see why▾
Why this is wrong here
The Cassandra API does not support SQL-like queries on JSON documents; it uses CQL (Cassandra Query Language) and is optimized for wide-column stores, not for querying nested JSON fields like achievements.
★ When this WOULD be the correct answer
A company needs a globally distributed, low-latency, linearly scalable database for time-series data (e.g., IoT sensor readings) that requires high write throughput and uses the Cassandra Query Language (CQL) for queries.
Why candidates choose this
Candidates may confuse the Cassandra API's support for JSON-like data with the ability to query JSON documents, or they might think that any NoSQL API can handle JSON queries equally well.
✗C. MongoDB APIWrong answer — click to see why▾
Why this is wrong here
The MongoDB API supports JSON documents and SQL-like queries, but it uses MongoDB's query language, not SQL. The question requires SQL-like queries, which is a native feature of the SQL (Core) API, not the MongoDB API.
★ When this WOULD be the correct answer
A company stores JSON documents and needs to use MongoDB drivers and tools (e.g., aggregation pipeline) for queries, with no requirement for SQL syntax. They also need compatibility with existing MongoDB applications.
Why candidates choose this
Candidates see JSON documents and think of MongoDB, which is popular for JSON storage. They may overlook that the SQL API also supports JSON and provides native SQL querying, which is explicitly required.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Cosmos DB API
Cosmos DB API is a collection of interfaces that lets applications interact with Azure Cosmos DB, a globally distributed NoSQL database, using different data models and query languages.
Key term
Partition key
A partition key is a value used by database systems to distribute data across multiple storage partitions, enabling faster queries and efficient scaling.
About these practice questions
This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A gaming company stores player profiles as JSON documents. Each profile can have different attributes; for example, some profiles include an 'achievements' field while others include a 'purchaseHistory' field. The application must retrieve profiles by player ID with single-digit-millisecond latency and also support SQL-like queries on any attribute. Which Azure data store should the company use?
medium- A.A. Azure Table Storage
- ✓ B.B. Azure Cosmos DB Core (SQL) API
- C.C. Azure Blob Storage
- D.D. Azure Database for PostgreSQL
Why B: Azure Cosmos DB Core (SQL) API is the correct choice because it natively stores JSON documents with flexible schemas, supports indexing on any attribute for SQL-like queries, and guarantees single-digit-millisecond latency for point reads by player ID. This meets the requirement for both fast key-based lookups and ad-hoc querying across varying profile attributes.
Variation 2. A company stores user profiles as JSON documents. Each profile includes standard fields (userId, name, email) and optional fields (preferences, history). The application needs fast key lookups by userId and SQL-like queries on optional fields. Which Azure Cosmos DB API should they choose?
medium- ✓ A.SQL (Core) API
- B.MongoDB API
- C.Cassandra API
- D.Table API
Why A: The SQL (Core) API is the correct choice because it natively supports JSON documents with flexible schemas, enabling fast key-value lookups on the `userId` field (via automatic indexing) and rich SQL-like querying (e.g., `SELECT * FROM c WHERE c.preferences.theme = 'dark'`) on optional fields. It is the only Azure Cosmos DB API that provides a SQL query syntax directly over JSON, making it ideal for mixed workloads of point reads and ad-hoc queries on nested or optional properties.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.