Microsoft Azure Data Fundamentals DP-900 (DP-900) — Questions 151–225

Azure Cosmos DB for Table is designed for key-value and wide-column data with a tabular schema, not for JSON documents. It does not support SQL-like queries on nested JSON fields or efficient point reads by 'userName' as a custom key.

Azure Cosmos DB for Apache Cassandra uses the Cassandra Query Language (CQL), not SQL-like syntax, and is optimized for wide-column stores, not JSON documents. It does not natively support point reads by a single field like 'userName' with the same low-latency guarantees as the SQL API.

A question where an application stores structured, non-relational data (e.g., sensor readings or user preferences) in a table format, requires fast point reads by a partition key, and uses OData-like queries instead of SQL. The team prefers a simple key-value API with table storage semantics.

A question where the application requires high-throughput writes and reads with a schema that is columnar (e.g., time-series data), and the team is familiar with Cassandra's CQL. For example: 'A telemetry system stores device readings with columns like deviceId, timestamp, and value. The team knows CQL and needs a globally distributed, low-latency database.'

Candidates may confuse Azure Cosmos DB for Table with the Table API in Azure Storage, thinking it supports JSON-like data, or they may assume 'Table' implies a general-purpose NoSQL option suitable for any schema.

Candidates may associate Cassandra with high scalability and low latency, and mistakenly think its CQL is similar enough to SQL for the query requirement, overlooking that the data model (JSON documents) and the need for SQL-like syntax are better served by the NoSQL API.

The MongoDB API is designed for document storage with rich queries, but it lacks native graph traversal capabilities needed for efficient 'friends of friends' queries.

Azure Table Storage is a key-value store that does not support graph queries like traversing 'friends of friends' relationships efficiently. It lacks native graph traversal capabilities, making it unsuitable for this use case.

The SQL API is designed for document-based queries using SQL syntax, not for graph traversal like 'friends of friends'. It lacks native graph traversal capabilities such as recursive queries or Gremlin steps.

A question requiring a document database with flexible schema, high availability, and MongoDB compatibility (e.g., storing user profiles with complex nested data) would make Azure Cosmos DB with MongoDB API the correct answer.

A question requiring a cost-effective, schema-less NoSQL store for storing large amounts of structured, non-relational data (e.g., device logs, metadata) with simple key-based lookups and no complex querying needs would make Azure Table Storage correct.

A question requiring storing and querying JSON documents with SQL-like queries, such as an e-commerce product catalog where items are retrieved by category or price range, and no graph relationships need to be traversed.

Candidates may associate MongoDB with social applications due to its popularity and flexibility, overlooking that graph-specific APIs are required for relationship traversal.

Candidates may confuse Table Storage with a general-purpose NoSQL option, overlooking its lack of graph or relational query support, and assume it can handle any non-relational workload.

Candidates may assume the SQL API is versatile enough for any query pattern, or they may be more familiar with SQL and overlook the specific graph traversal requirement.

The status update is triggered by each scan event and reflects changes immediately, which is real-time processing, not batch. The daily report aggregates data after the fact, which is batch processing.

The daily report aggregates historical data at a scheduled time, which is batch processing, not real-time. The status update is immediate, making it real-time, so both cannot be real-time.

The status update is immediate upon scanning, which is real-time, not batch. The daily report aggregates data over a day, which is batch, not real-time. Option D reverses these definitions.

This option would be correct if the question described a scenario where all data is collected throughout the day and then processed together at the end of the day, such as a system that logs all scans and updates delivery statuses only after a nightly batch job runs.

If the question described a system where package scans trigger immediate updates to the report (e.g., streaming analytics) and the daily job was also real-time (e.g., continuous aggregation), then both could be considered real-time workloads.

This option would be correct if the status updates were collected throughout the day and processed in a nightly batch job (e.g., scanning data logged and updated in bulk), while the daily report was generated on-demand with live data (e.g., streaming aggregation).

Candidates may confuse 'immediate update' with batch processing if they think the database update is part of a larger scheduled job, or they may not distinguish between transactional updates and analytical aggregation.

Candidates may confuse 'immediate' updates with 'real-time' processing for both workloads, overlooking that the daily report is scheduled and not continuous.

Candidates may confuse 'daily report' with real-time reporting or think that any scheduled job is real-time, leading them to reverse the workload types.

Azure Cosmos DB SQL API does not support JavaScript arrow functions like `some()` in queries. The correct syntax uses `ARRAY_CONTAINS` with partial document matching.

In Azure Cosmos DB SQL API, c.inventory.name == 'sword' is invalid because inventory is an array of objects, not a single object. This syntax would only work if inventory were a single object with a name property, not an array.

The IN operator checks if a scalar value exists in an array, but c.inventory is an array of objects, not strings. 'sword' is a string, not an object, so the query will never match.

If the question were about a MongoDB API query using the `$where` operator or a client-side filter in application code (e.g., LINQ in C#), then using `some()` or similar array iteration would be valid.

This option would be correct if the inventory field were a single object (not an array) containing a name property, e.g., each document has inventory: {name: 'sword', type: 'weapon'}. The query would then check if that single object's name equals 'sword'.

If the inventory array contained only item names as strings (e.g., ['sword', 'shield']), then WHERE 'sword' IN c.inventory would correctly filter documents where the array includes that string.

Candidates familiar with JavaScript array methods may mistakenly assume they can use similar syntax in Cosmos DB SQL queries, not realizing the API uses a restricted SQL-like language.

Candidates may mistakenly think that array properties can be accessed directly with dot notation, similar to nested object properties, not realizing that arrays require special functions like ARRAY_CONTAINS or JOIN to query elements.

Candidates may be familiar with the IN operator from SQL or other languages and assume it works for checking values within arrays of objects, overlooking that it only works for primitive values.

JSON documents are semi-structured, not more structured than a relational table. Relational tables have a fixed schema, making them the most structured, followed by JSON (semi-structured), then JPEG (unstructured).

JPEG files are unstructured binary data, not more structured than relational tables or JSON documents. The correct order from most to least structured is relational table (schema-defined), JSON documents (semi-structured), JPEG files (unstructured).

JPEG files are unstructured binary data, not semi-structured like JSON. Relational tables are most structured, JSON is semi-structured, and JPEG is unstructured, so the correct order is relational table, JSON, JPEG.

If the question asked to order from least to most structured, or if the relational table had a flexible schema (e.g., NoSQL), then JSON could be considered more structured than a table. For example: 'Which orders data types from least to most structured?'

If the question asked to order by storage size from largest to smallest, JPEG files (high resolution) might be largest, followed by relational table (with many rows), then JSON documents (small). Or if ordering by query performance, relational table might be fastest, then JPEG (no query), then JSON (requires parsing).

If the question asked to order by 'data size' or 'storage efficiency' rather than structure, or if JPEG files were described as having EXIF metadata (semi-structured) while JSON was purely unstructured, then D could be correct.

Candidates may mistakenly think JSON is highly structured because it has key-value pairs, or they confuse 'structured' with 'flexible' or 'complex'.

Candidates may mistakenly think JPEG files have a fixed structure (like headers) and thus are more structured than JSON, or they may confuse 'structured' with 'binary format'.

Candidates may mistakenly think JSON is unstructured because it lacks a fixed schema, or they may confuse 'structured' with 'binary' and rank JPEG as more structured than JSON.

The MongoDB API supports JSON documents and key lookups, but it does not natively support SQL-like queries on optional fields; it uses MongoDB query language instead.

The Cassandra API uses CQL (Cassandra Query Language) and is optimized for high-throughput writes and partition-based queries, not for SQL-like queries on optional fields or flexible JSON documents. It lacks native support for querying arbitrary nested fields without predefined schema.

The Table API is designed for key-value and tabular data with a fixed schema, not for JSON documents with nested optional fields. It does not support SQL-like queries on nested properties or efficient indexing of arbitrary fields.

If the question specified that the application requires MongoDB-compatible drivers, uses MongoDB-specific features like aggregation pipelines, or needs to migrate an existing MongoDB workload to Azure Cosmos DB, then the MongoDB API would be correct.

A company needs a globally distributed, low-latency, high-throughput database for time-series data (e.g., IoT sensor readings) with a fixed schema, requiring strong consistency and the ability to query by partition key and clustering columns using CQL.

A company needs to store structured, non-relational data (e.g., customer records with a fixed set of properties) and requires fast point lookups by partition key and row key, with no need for complex queries or nested JSON. The Table API would be correct for such a scenario.

Candidates see JSON documents and think of MongoDB, which is a popular NoSQL database for JSON, but overlook that the SQL (Core) API also supports JSON and provides SQL querying.

Candidates may confuse Cassandra's wide-column store with document databases, or assume its CQL supports SQL-like queries, not realizing it lacks the flexible schema and query capabilities needed for JSON documents with optional fields.

Candidates may think the Table API is suitable because it supports schema-less data and key-based lookups, but they overlook its lack of support for nested JSON and SQL-like querying on optional fields.

Consistency ensures that a transaction transforms the database from one valid state to another, but the scenario specifically describes the 'all-or-nothing' execution of the transfer, which is the definition of atomicity.

Isolation ensures concurrent transactions do not interfere with each other, but the scenario describes a single transaction's all-or-nothing execution, which is Atomicity.

Durability ensures that once a transaction is committed, its changes persist even after a system failure. The scenario describes a rollback on failure, not persistence after commit.

A question that asks: 'A bank transfer ensures that the total balance across accounts remains the same before and after the transaction. Which ACID property does this guarantee?' would make Consistency correct, as it maintains database invariants.

A question where two transactions run simultaneously, e.g., one reading a balance while another updates it, and the system prevents dirty reads or lost updates. The correct answer would be Isolation.

A question stating: 'After a fund transfer is successfully committed, the system crashes. The bank later confirms the transfer was recorded permanently. Which ACID property is demonstrated?'

Candidates often confuse atomicity with consistency because both involve correctness; they think 'all-or-nothing' ensures data remains consistent, but consistency is about rules/invariants, not the transaction's indivisibility.

Candidates may confuse 'consistency' with the idea of accounts staying balanced, but the scenario's focus on 'both succeed or both fail' directly points to Atomicity, not Isolation.

Candidates may confuse durability with the 'all-or-nothing' nature of atomicity, or think that ensuring no partial updates implies durability.

Batch processing is for large volumes of data at scheduled times, not for real-time transactions; stream processing is for continuous data flows, not for nightly aggregated reports.

OLAP is designed for analytical queries on aggregated data, not for real-time transaction processing. OLTP is for transactional workloads, not for nightly summary reports.

ETL and ELT are data integration processes, not processing workloads. The question describes transaction processing (OLTP) and analytical reporting (OLAP), not data extraction, transformation, and loading.

If the question described a system where transactions are collected in batches and processed later (e.g., end-of-day settlement) and reports are generated from a continuous stream of data (e.g., real-time dashboards), then option B would be correct.

A question where a system uses OLAP for real-time dashboards on historical data and OLTP for batch updates to a data warehouse would make this option correct.

A question asks: 'A data warehouse team extracts data from a source system, transforms it, and loads it into a staging area before moving to the warehouse. Which process does this describe?' Then ETL would be correct.

Candidates may confuse 'real-time' with 'stream processing' and 'nightly job' with 'batch processing', not realizing that OLTP/OLAP are the standard terms for transaction and analytical workloads in databases.

Candidates may confuse the terms OLTP and OLAP, or mistakenly think that 'transactions' implies analytical processing and 'reports' implies transactional processing.

Candidates may confuse data integration methods (ETL/ELT) with processing types (OLTP/OLAP) because both involve data movement and transformation, leading to a mistaken association with batch and real-time workloads.

Azure SQL Database does not support SQL Server Agent for scheduled maintenance jobs or cross-database queries within the same logical server, which are required by the question.

SQL Server on Azure Virtual Machine is an IaaS solution, not PaaS, requiring the customer to manage patching, backups, and the OS, contradicting the requirement to minimize management overhead.

Azure Database for MySQL is a PaaS service for MySQL databases, not SQL Server. It does not support SQL Server Agent, cross-database queries within the same logical server, or SQL Server-specific features required by the question.

An exam scenario where the company needs a fully managed PaaS database with built-in high availability and elastic scaling, but does not require SQL Server Agent or cross-database queries, and can accept a single database model.

This option would be correct if the question required full control over the SQL Server environment, including custom configurations, specific SQL Server versions, or third-party software installation, and the customer is willing to manage patching and backups.

A company is migrating a 500 GB on-premises MySQL database to Azure and needs a PaaS service with minimal management overhead for patching and backups, while preserving MySQL-specific features like stored procedures and scheduled events. Azure Database for MySQL would be the correct choice.

Candidates may assume Azure SQL Database is the default PaaS choice for SQL Server migration, overlooking its limitations with agent jobs and cross-database queries that are available in SQL Managed Instance.

Candidates may think that since it supports all SQL Server features including SQL Server Agent and cross-database queries, it is a valid choice, overlooking the PaaS requirement and management overhead.

Candidates may confuse Azure Database for MySQL as a generic PaaS database option, overlooking that the question specifies SQL Server features like SQL Server Agent and cross-database queries, which are not supported by MySQL.

Azure SQL Database (single database) does not support SQL Server Agent, Service Broker, or cross-database queries within the same instance, which are required by the scenario.

Azure SQL Database elastic pool does not support SQL Server Agent, Service Broker, or cross-database queries within the same instance, which are required by the question.

Azure SQL Database Hyperscale is designed for large databases with high scalability and performance needs, but it does not support SQL Server Agent, Service Broker, or cross-database queries within the same instance, which are required by the question.

A company needs a fully managed PaaS database for a new application with no dependencies on SQL Server Agent, Service Broker, or cross-database queries, and wants minimal management overhead and built-in high availability.

A company needs to manage multiple databases with varying and unpredictable usage patterns, seeking cost-effective resource sharing and performance isolation without needing instance-level features like SQL Agent or cross-database queries.

A company needs to migrate a very large database (over 4 TB) with high transaction throughput and requires rapid scaling of compute and storage resources without downtime. The database does not use SQL Server Agent, Service Broker, or cross-database queries, and the company prioritizes scalability over full feature compatibility.

Candidates may assume that 'fully managed PaaS' always means Azure SQL Database, overlooking the specific feature requirements that only Azure SQL Managed Instance supports.

Candidates may confuse elastic pools with managed instances, thinking both offer multi-database management, but overlook that elastic pools lack instance-scoped features required for the migration.

Candidates may associate 'Hyperscale' with high performance and scalability, assuming it can handle any workload, but overlook that it lacks key SQL Server features like Agent and Service Broker, which are critical for the given requirements.

Images (JPEG) are unstructured binary data, not semi-structured. JSON documents have some structure (key-value pairs) but are less structured than relational data with fixed schema. Thus, ordering relational > JSON > images is correct, not relational > images > JSON.

Images (JPEG) are unstructured binary data, while JSON documents have some structure (key-value pairs), so JSON is more structured than images. Ordering images before JSON is incorrect.

JSON documents have a schema (even if flexible) and are semi-structured, while images are unstructured binary data. The order from most to least structured should be relational (structured), JSON (semi-structured), images (unstructured), not JSON then relational.

If the question asked to order data types by storage size (e.g., from largest to smallest) or by typical access latency, then images (large files) might come before JSON (smaller documents). For example: 'Order the following data types by average file size: relational data (rows), JSON documents, images.'

If the question asked to order data types from least structured to most structured, then images (unstructured), JSON (semi-structured), relational data (structured) would be correct, making option B the right answer.

If the question asked for the order from least structured to most structured, then D (JSON, relational data, images) would be correct because images are least structured, JSON is semi-structured, and relational data is most structured.

Candidates may mistakenly think JSON is unstructured because it allows varying fields, or they may confuse 'structured' with 'binary format', placing images as more structured than JSON.

Candidates may mistakenly think JSON is completely unstructured because it allows varying fields, or they may confuse the order direction and assume images are more structured than JSON.

Candidates may mistakenly think JSON is less structured than relational data because it allows varying fields, but they overlook that images are completely unstructured, leading them to place JSON before relational data.

The application prioritizes availability and partition tolerance over strong consistency, meaning it does not guarantee that all users see the same like count immediately. Strong consistency requires that all reads return the most recent write, which contradicts the eventual visibility described.

Consistent prefix guarantees that reads see writes in order, but does not guarantee that all replicas will eventually have the same value; it only ensures no gaps in the sequence. The question describes a system where updates propagate to all replicas over time, which is eventual consistency, not consistent prefix.

Bounded staleness requires a bound on how stale data can be (e.g., within 5 seconds), but the question states no time bound, only eventual consistency across regions.

In a scenario where a banking application requires that a balance update is immediately visible to all subsequent read operations, even at the cost of reduced availability during network partitions, strong consistency would be the correct choice.

A question asks: 'A messaging application requires that all users see messages in the exact order they were sent, even if some messages are delayed. Which consistency model ensures that reads never see out-of-order writes?' In that scenario, consistent prefix is correct.

A financial trading application requires that all reads see writes within a guaranteed time window (e.g., 1 second) to prevent arbitrage, but can tolerate some delay. Bounded staleness would be correct here.

Candidates may confuse the need for accurate data (like counts) with strong consistency, not realizing that the application's design explicitly sacrifices immediate consistency for availability and partition tolerance.

Candidates may confuse 'eventual' with 'prefix' because both involve delays, but consistent prefix focuses on ordering guarantees rather than convergence of all replicas to the same value.

Candidates may confuse 'eventual' with 'bounded staleness' because both allow delays, but bounded staleness has a strict time limit, which is not mentioned in the question.

Azure Stream Analytics is optimized for real-time stream processing but lacks native support for ad-hoc T-SQL queries on combined streaming and historical data. It cannot directly query Azure SQL Database in a T-SQL interactive manner, failing the data scientists' requirement.

Azure Databricks is optimized for big data processing and machine learning, but it does not natively support T-SQL queries. The requirement for ad-hoc T-SQL queries makes Azure Synapse Analytics more suitable.

Azure Analysis Services is a semantic modeling and OLAP engine, not designed for real-time streaming or ad-hoc T-SQL queries on raw data. It cannot directly ingest streaming data from Event Hubs or execute T-SQL queries against combined streaming and historical datasets.

If the question only required real-time anomaly detection on streaming data without the need for ad-hoc T-SQL queries on combined datasets, and the output could be directed to a separate storage for analysis, then Azure Stream Analytics would be the correct choice.

Azure Databricks would be correct if the question required advanced machine learning model training on the combined dataset, or if the team needed to perform complex data transformations using Spark and Python/Scala, and T-SQL was not a requirement.

A question where the requirement is to create a tabular or multidimensional semantic model for business users to perform interactive analysis (e.g., with Excel or Power BI) on pre-aggregated data from a data warehouse, without needing real-time streaming or direct T-SQL access.

Candidates may focus on the 'near real-time' streaming requirement and assume Stream Analytics is sufficient, overlooking the explicit need for ad-hoc T-SQL queries on the combined dataset.

Candidates may choose Databricks because it is a popular platform for big data analytics and streaming, and they might overlook the specific need for T-SQL query support, assuming Databricks can handle all analytics workloads.

Candidates may confuse Azure Analysis Services with a general analytics platform because of the word 'Analysis' in its name, and assume it supports T-SQL queries and real-time data processing.

Active geo-replication is designed for disaster recovery and regional failover, not for offloading read-only queries to a secondary replica. It does not improve read performance for the same queries without changing application code.

Elastic pools are designed to manage and share resources among multiple databases to optimize cost, not to improve read performance for a single database without increasing its tier.

Query Performance Insight is a diagnostic tool for analyzing query performance, not a feature to improve read performance. It does not offload read traffic or reduce load on the primary database.

A company needs to ensure business continuity and disaster recovery across Azure regions, with the ability to fail over to a secondary database in another region in case of an outage. The question would specify a requirement for high availability and regional redundancy.

A company has multiple Azure SQL databases with varying and unpredictable usage patterns, and wants to optimize cost while ensuring each database gets resources when needed, without changing the application code.

A company wants to identify the most resource-intensive and frequently run queries in their Azure SQL Database to optimize performance. They need a tool that provides query-level metrics, wait statistics, and recommendations for indexing or query tuning.

Candidates may confuse 'read scale-out' with 'geo-replication' because both involve replicas, but geo-replication's primary purpose is disaster recovery, not read performance improvement.

Candidates may confuse elastic pools with a performance-boosting feature because pools can provide more resources to databases, but they don't specifically address read-heavy workloads on a single database.

Candidates may confuse performance monitoring with performance improvement, thinking that analyzing queries will directly speed up reads, but it only provides insights without automatically enhancing throughput.

Order placement requires immediate, atomic updates to inventory and order records, which is the hallmark of transactional processing, not analytical processing, which focuses on querying and aggregating historical data.

Order placement requires immediate inventory reduction and recording, which is real-time, interactive, and ACID-compliant—characteristics of transactional processing, not batch processing. Batch processing would delay these updates, causing inventory inconsistencies.

Order placement requires immediate inventory reduction and recording, which is transactional processing (ACID properties). Stream processing handles continuous data flows but does not guarantee immediate, consistent updates per transaction.

If the question asked about the activity of generating monthly sales trend reports, analytical processing would be correct, as it involves aggregating and analyzing historical data to support decision-making.

Batch processing would be correct for the end-of-month reporting activity described in the same question, where large volumes of historical sales data are aggregated periodically without real-time requirements.

A question describing a system that continuously ingests real-time sensor data from IoT devices and must detect anomalies within milliseconds would make stream processing correct.

Candidates may confuse the need to process data (orders) with the eventual use of that data for analysis, mistakenly thinking that any data processing is analytical.

Candidates may confuse the monthly reporting (which is batch) with the order placement activity, or think that any data processing involving multiple records (like orders) is batch, overlooking the real-time transactional nature of order placement.

Candidates may confuse 'real-time' order placement with stream processing, not realizing that transactional processing is designed for immediate, atomic updates to a database.

Audio recordings are unstructured data (free-form, no predefined schema), not structured. Structured data has a rigid schema like rows and columns, which applies only to the spreadsheet.

Audio recordings lack a predefined data model or schema, making them unstructured, not semi-structured. Semi-structured data (e.g., JSON, XML) has tags or markers to separate data elements, which audio does not.

The spreadsheet data is structured because it has a fixed schema (columns: Name, Address, Phone Number), not semi-structured. Semi-structured data has tags or markers but no rigid schema, like JSON or XML.

If the question described the spreadsheet as having a fixed schema (e.g., Name, Address, Phone) and the audio recordings as having metadata tags (e.g., speaker, date, topic) that impose some structure, then both could be considered structured or semi-structured. For example: 'A spreadsheet with columns Name, Address, Phone, and audio files with metadata tags for speaker and date.'

If the question described the spreadsheet as having a flexible schema (e.g., some rows missing columns) and the audio recordings as having metadata tags (e.g., speaker labels, timestamps), then the spreadsheet could be semi-structured and the audio semi-structured, but that scenario is not given here.

If the spreadsheet contained free-form text in a single column (e.g., a 'Notes' column with paragraphs) and the audio recordings were tagged with metadata (e.g., timestamps, speaker labels), then the spreadsheet would be semi-structured and the audio recordings unstructured.

Candidates may think that any data stored in a file (like audio) is structured, or they confuse 'structured' with 'organized' rather than the technical definition of having a predefined schema.

Candidates may confuse 'semi-structured' with 'unstructured' because both lack rigid schemas, or they might think audio files with metadata (like ID3 tags) qualify as semi-structured, overlooking that the raw audio content itself is unstructured.

Candidates may confuse 'semi-structured' with any data that is not fully normalized or contains some variability, mistakenly thinking a simple spreadsheet without a strict relational schema qualifies as semi-structured.

Azure SQL Database (single database) does not support cross-database queries using three-part names or SQL Server Agent jobs, which are required by the company's existing database.

Azure SQL Database elastic pool does not support cross-database queries using three-part names or SQL Server Agent jobs, which are required by the company.

SQL Server on Azure VM is an IaaS solution, not fully managed PaaS, and requires the company to manage the VM, SQL Server, and backups, which contradicts the requirement for a fully managed service.

A company needs a fully managed PaaS database for a new application with no cross-database dependencies or agent jobs, and wants the lowest cost and simplest management for a single database workload.

A company needs to manage multiple databases with varying and unpredictable usage patterns, and wants to optimize cost by sharing resources among them, without requiring cross-database queries or SQL Agent jobs.

A company needs full control over the SQL Server instance, including custom configurations, third-party agents, or specific OS-level dependencies, and is willing to manage the underlying infrastructure.

Candidates may think Azure SQL Database is the default PaaS option and overlook its limitations with cross-database queries and agent jobs, assuming all SQL Server features are available.

Candidates may think an elastic pool is a fully managed PaaS option that can handle multiple databases, but overlook its limitations on cross-database queries and agent jobs.

Candidates may think that because the database uses SQL Server Agent jobs and cross-database queries, only a full SQL Server instance on a VM can support these features, overlooking that Azure SQL Managed Instance also supports them.

Azure Cosmos DB Core (SQL) API provides low latency and high throughput, but for a real-time leaderboard requiring sub-millisecond read/write latency and frequent updates, Azure Cache for Redis is more suitable due to its in-memory data store and built-in sorted set data structure for leaderboards.

Azure Table Storage does not support sub-millisecond read/write latency or built-in leaderboard ranking operations like sorted sets, making it unsuitable for real-time leaderboard updates and top-100 retrieval.

Azure Blob Storage is designed for storing large amounts of unstructured data like images, videos, and backups, not for low-latency, high-frequency updates of leaderboard scores. It lacks sub-millisecond read/write latency and does not support real-time ranking queries efficiently.

Azure Cosmos DB Core (SQL) API would be correct for a globally distributed leaderboard that requires multi-region writes, strong consistency, and complex querying (e.g., filtering by date range or player attributes) while still needing low latency (though not sub-millisecond).

A question requiring a cost-effective, schema-less NoSQL store for large volumes of structured data (e.g., logging telemetry from millions of devices) where latency requirements are in the millisecond range (not sub-millisecond) and complex queries are not needed.

A question requiring storage of large binary files (e.g., game replays, screenshots) with high throughput and low cost, where latency is not critical. For example: 'Which Azure service should be used to store video clips of game highlights for archival and batch processing?'

Candidates may associate Cosmos DB with low latency and high performance, overlooking that Redis is specifically optimized for in-memory, real-time leaderboard scenarios with sub-millisecond latency.

Candidates may associate Table Storage with fast key-value lookups and scalability, overlooking its lack of native sorted set operations and higher latency compared to in-memory caches like Redis.

Candidates may think Blob Storage can handle any data type because it's a general-purpose storage solution, overlooking its unsuitability for real-time, low-latency transactional workloads.

A clustered index on SaleDate alone does not support the equality filter on CustomerID, so queries filtering by both CustomerID and SaleDate would require a full scan or inefficient lookup for CustomerID.

Including SaleDate as an included column (not a key column) means the index is ordered by CustomerID only, so it cannot efficiently support range queries on SaleDate for a given customer; the database would still need to scan all rows for that CustomerID to filter by date range.

For queries filtering by both CustomerID and SaleDate, a composite index on (CustomerID, SaleDate) is optimal because it allows equality on CustomerID and range on SaleDate. The index on (SaleDate, CustomerID) would require scanning all rows for a given date range before filtering by CustomerID, which is less efficient.

If queries frequently filter only by SaleDate (e.g., retrieving all sales for a date range) and the table is large, a clustered index on SaleDate would improve range scan performance.

If queries always filter by CustomerID and then retrieve SaleDate (but never filter or sort by SaleDate), a nonclustered index on CustomerID with SaleDate as an included column would be optimal because it covers the query without needing to order by SaleDate.

If the query pattern were to filter primarily by SaleDate (e.g., retrieve all sales for a date range) and only secondarily by CustomerID, or if the query used a range on SaleDate without equality on CustomerID, then (SaleDate, CustomerID) would be the better index.

Candidates may think a clustered index on a frequently filtered column always improves performance, ignoring that the query also filters by CustomerID, which is not covered.

Candidates may think that including SaleDate in the leaf level is sufficient for date filtering, not realizing that without SaleDate as a key column, the index cannot perform efficient range seeks on the date column.

Candidates may think that since SaleDate is used in a range query, it should be the leading column, but they overlook that equality conditions (CustomerID) should come first in a composite index for optimal seek operations.