Microsoft Azure Data Fundamentals DP-900 (DP-900) — Questions 226–300

Why this answer

How to eliminate wrong answers

Option A is wrong because it only supports batch analytics via the serverless SQL pool and lacks a real-time analytics component, failing the requirement for real-time analytics. Option B is wrong because it uses Azure SQL Database and a dedicated SQL pool, which are provisioned (not serverless) compute options, increasing costs and violating the preference for a serverless option; it also moves data to a separate database, increasing data movement. Option C is wrong because it copies data to Azure Blob Storage (which lacks the hierarchical namespace and optimized analytics features of Data Lake Storage Gen2) and uses only Stream Analytics for real-time analytics, missing the batch analytics requirement and the serverless SQL pool for ad-hoc querying.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Database (single database) does not support SQL Server Agent, Service Broker, or cross-database queries within the same instance, requiring significant application redesign. Option C is wrong because Azure SQL Database elastic pool is a resource-sharing model for multiple single databases and inherits the same limitations as single databases, lacking instance-level features like SQL Server Agent and Service Broker. Option D is wrong because Azure SQL Database Hyperscale is a scaling architecture for single databases and does not provide instance-scoped features such as SQL Server Agent or cross-database query support.

Why this answer

How to eliminate wrong answers

Option B is wrong because it incorrectly labels CSV files as semi-structured (they are structured with fixed columns) and API JSON responses as structured (they are semi-structured due to varying fields). Option C is wrong because it misclassifies API JSON responses as unstructured (they have key-value pairs, making them semi-structured) and TIFF images as semi-structured (they are unstructured binary data). Option D is wrong because it calls CSV files unstructured (they have a fixed schema) and TIFF images structured (they have no predefined data model).

Why this answer

How to eliminate wrong answers

Option A is wrong because denormalization is the opposite process—it intentionally introduces redundancy (e.g., combining tables) to improve read performance, often at the cost of data integrity, and is not used to reduce redundancy. Option B is wrong because indexing is a performance optimization technique that creates data structures (e.g., B-trees) to speed up query execution, not a method for organizing data to eliminate redundancy. Option D is wrong because partitioning splits a table into smaller physical segments (e.g., horizontal or vertical partitioning) for manageability or performance, but does not inherently reduce data redundancy or improve integrity.

Why this answer

How to eliminate wrong answers

Option A is wrong because an index on OrderDate only would require a full scan to find rows matching a specific CustomerID, as it does not include the filter column; the query would still need to perform a key lookup or scan to apply the CustomerID predicate. Option B is wrong because a filtered index on CustomerID WHERE Status = 'Active' is too narrow—it only helps queries that include the Status filter, and the original query does not filter on Status, so it would be ignored by the optimizer for this workload. Option D is wrong because an index on (OrderID, OrderDate) does not include CustomerID as the leading key; the query filter on CustomerID cannot use this index efficiently, and it would still require a scan or lookup to satisfy the CustomerID condition.

Why this answer

How to eliminate wrong answers

Option B (Azure Stream Analytics) is wrong because it is designed for real-time stream processing (e.g., from Event Hubs or IoT Hub), not for scheduled batch loading of CSV files from ADLS Gen2. Option C (Azure Databricks) is wrong because while it can process data and load into Synapse, it is a Spark-based analytics platform that requires manual pipeline orchestration or integration with ADF; it does not natively provide the simple scheduling and PolyBase integration that ADF offers out of the box. Option D (Azure Logic Apps) is wrong because it is a low-code workflow service for integrating SaaS applications and APIs, not designed for high-throughput data movement or PolyBase-based parallel loading into a dedicated SQL pool.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store that does not natively support JSON documents with flexible schema; it stores entities as rows with a fixed set of properties and lacks global distribution with low-latency guarantees. Option B is wrong because Azure SQL Database is a relational database that requires a predefined schema and does not offer native JSON document storage with flexible schema; it also lacks built-in global distribution for low-latency access. Option C is wrong because Azure Blob Storage is an object storage service for unstructured binary data and does not provide native JSON document querying, indexing, or global distribution with low-latency access.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a fully managed relational database service that requires data to be imported and stored within its own storage engine, not queried in place from Blob Storage. Option B is wrong because Azure Analysis Services is a semantic modeling and analytics engine that requires data to be loaded into an in-memory tabular model, not queried directly from Blob Storage. Option C is wrong because Azure Cosmos DB is a NoSQL database service with its own storage and query APIs (SQL, MongoDB, Cassandra, etc.), and it cannot query external data in Blob Storage without first ingesting it.

Why this answer

How to eliminate wrong answers

Option A is wrong because the query groups by state, not by event type, so it returns damage per state, not per event type. Option B is wrong because the query uses 'top 5 by' to return only the highest damage states, not all states, and it sorts by damage descending, not alphabetically. Option D is wrong because the query groups by state, not by individual flood events, so it returns aggregated damage per state, not per event.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because page-level filters do not improve query performance; they only change the scope of filter application in the UI, and the underlying query still hits the same large fact table. Option B is wrong because creating a calculated table that aggregates the fact table at a higher granularity would lose detail data and prevent users from drilling down, which is not a performance optimization but a data reduction that changes the analytical capability. Option D is wrong because converting to a composite model with DirectQuery for some tables often introduces latency from the source system and can degrade performance due to the lack of in-memory compression and the need for cross-engine joins, making it a poor choice for improving dashboard responsiveness.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because it places images (unstructured binary) before JSON (semi-structured), incorrectly suggesting that binary files have more structure than key-value documents. Option B is wrong because it reverses the entire order, claiming images are most structured and relational data is least structured, which contradicts the fundamental definition of structured vs. unstructured data. Option D is wrong because it ranks JSON as more structured than relational data, but relational databases enforce a rigid schema with primary keys and data types, making them more structured than JSON's flexible schema.

Why this answer

How to eliminate wrong answers

Option B is wrong because the index key starts with OrderDate, which does not support the equality filter on CustomerID efficiently; the database would need to scan all rows matching the OrderDate range and then filter by CustomerID. Option C is wrong for the same reason—leading with OrderDate DESC fails to support the equality predicate on CustomerID, leading to unnecessary scans. Option D is wrong because the index key is only CustomerID, so the sort by OrderDate DESC cannot be satisfied from the index order, requiring an explicit sort operation that degrades performance.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because strong consistency requires all reads to return the most recent write immediately, which would sacrifice availability and partition tolerance—contradicting the application's design priorities. Option C is wrong because consistent prefix guarantees that reads see writes in the order they occurred, but it does not allow for temporary staleness in the like count; it is used in systems like Cosmos DB with a specific consistency level that still imposes ordering constraints. Option D is wrong because bounded staleness guarantees that reads are at most a fixed number of versions or time interval behind the latest write, which imposes a strict upper bound on staleness—not the 'eventually consistent' behavior described where no time bound is guaranteed.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Stream Analytics is a real-time stream processing engine that can join streaming data with reference data (e.g., from SQL Database), but it does not support ad-hoc T-SQL queries for data scientists; it uses a SQL-like query language for continuous queries, not interactive T-SQL. Option B is wrong because Azure Databricks is an Apache Spark-based analytics platform that can handle streaming and batch workloads, but it does not natively support T-SQL queries; it uses Spark SQL or Python, not the T-SQL dialect required for ad-hoc queries by data scientists. Option D is wrong because Azure Analysis Services is an OLAP engine for semantic modeling and business intelligence, not designed for real-time stream processing or direct T-SQL queries on raw combined datasets; it focuses on pre-aggregated data and MDX/DAX queries.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because SSIS over the internet would be severely throttled by the limited network bandwidth, making the transfer of 2 TB extremely slow and impractical. Option C is wrong because a site-to-site VPN still relies on the same limited internet bandwidth, so using Azure Data Factory over it would not reduce transfer time. Option D is wrong because Azure Data Factory with a self-hosted integration runtime over the internet still depends on the available network bandwidth, which is insufficient for a 2 TB transfer in a timely manner.

Why this answer

How to eliminate wrong answers

Option B (Active geo-replication) is wrong because it is designed for disaster recovery and regional failover, not for offloading read-only queries; it requires separate connection strings and does not automatically route read traffic to a secondary replica without application changes. Option C (Azure SQL Database elastic pool) is wrong because it is a cost-management feature for pooling multiple databases with varying usage patterns, not a performance optimization for read-heavy queries on a single database. Option D (Query Performance Insight) is wrong because it is a monitoring and diagnostic tool that identifies performance bottlenecks but does not directly improve read performance or offload read traffic.

Why this answer

Why this answer

How to eliminate wrong answers

Option B (Analytical processing) is wrong because it focuses on querying and aggregating historical data for reporting and trend analysis, not on real-time, atomic updates of operational data. Option C (Batch processing) is wrong because it processes data in scheduled, bulk intervals (e.g., nightly runs), whereas order placement must happen immediately upon customer action. Option D (Stream processing) is wrong because it handles continuous, unbounded data flows (e.g., sensor data or clickstreams) with low latency, but it does not inherently enforce transactional consistency for individual record updates like inventory deduction.

Why this answer

How to eliminate wrong answers

Option A is wrong because audio recordings are not structured; they lack a fixed schema and cannot be easily queried with SQL. Option B is wrong because audio recordings are unstructured, not semi-structured (semi-structured data has tags or markers, like JSON or XML). Option D is wrong because the spreadsheet data is structured, not semi-structured; it has a rigid schema with defined columns and data types.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Database (single database) does not support SQL Server Agent jobs or cross-database queries using three-part names; it only supports elastic query for cross-database access, which requires significant application changes. Option C is wrong because Azure SQL Database elastic pool is a resource-sharing model for multiple single databases, inheriting the same limitations as single databases (no Agent jobs, no three-part name cross-database queries). Option D is wrong because SQL Server on Azure Virtual Machine is an IaaS solution, not a fully managed PaaS service; it requires manual patching, backups, and high-availability setup, contradicting the requirement for minimal management overhead.

Why this answer

Why these pairings

Why this answer

How to eliminate wrong answers

Option A is wrong because INSERT statements for 500 GB of data would generate excessive transaction log overhead, cause locking and blocking, and degrade concurrent read query performance on the dedicated SQL pool. Option B is wrong because CTAS creates a full copy of the entire 5 TB table plus the new data, which is resource-intensive, slow, and unnecessary for daily incremental loads; it also requires renaming and dropping the old table, causing downtime. Option D is wrong because PolyBase loads data directly into the fact table, which can cause fragmentation, locking, and poor query performance during the load, and it does not isolate the new data for efficient partition management.

Why this answer

How to eliminate wrong answers

Option B (Azure Backup for Blob Storage) is wrong because it provides point-in-time recovery and protection against accidental deletion, but it does not prevent intentional modification or deletion of the original blobs before the retention period ends. Option C (Soft delete for Blob Storage) is wrong because it only retains deleted blobs for a configurable period (e.g., 7 days) and allows recovery, but it does not block deletion or modification during the retention period. Option D (Azure Blob Storage lifecycle management) is wrong because it automates tiering or deletion based on age, but it cannot enforce a write-once, read-many lock to prevent modification or deletion before the retention period expires.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB with SQL API is a NoSQL document database optimized for transactional workloads with low-latency reads/writes, but it is not designed for complex analytical queries over years of historical data and its storage costs are significantly higher than ADX for large telemetry volumes. Option B is wrong because Azure SQL Database is a relational OLTP engine that provides strong consistency and indexing, but it struggles with sub-second response on streaming time-series data at scale and its storage costs are higher for high-ingestion-rate telemetry. Option D is wrong because Azure Table Storage is a simple key-value store with no native support for time-series analytics, complex aggregations, or sub-second query performance on streaming data, and it lacks indexing for efficient range queries over timestamps.

Why this answer

How to eliminate wrong answers

Option A is wrong because changing the clustered index to CategoryID would reorganize the entire table by CategoryID, which may fragment the data and does not directly optimize the sort by Price descending; the clustered index should remain on the primary key for uniqueness and row lookup efficiency. Option B is wrong because while it includes Price as an included column, the index is ordered only by CategoryID, so SQL Server would still need to sort the matching rows by Price after the seek, adding a costly Sort operator to the execution plan. Option D is wrong because a clustered columnstore index is designed for large-scale analytical workloads with aggregations and scans, not for point lookups or ordered retrieval on a 200,000-row table; it would degrade performance for the described transactional queries.

Why this answer

How to eliminate wrong answers

Option B is wrong because the collation setting is not inherently incompatible with Azure Synapse; Synapse SQL pools support a variety of collations, and the default SQL_Latin1_General_CP1_CI_AS is commonly used. Option C is wrong because Azure Synapse SQL databases can be part of a failover group when configured appropriately, though the failover group feature is more commonly associated with Azure SQL Database; the GRS setting does not prevent failover group membership. Option D is wrong because Transparent Data Encryption (TDE) is supported in Azure Synapse SQL pools regardless of the storage replication type (GRS, LRS, etc.), as TDE operates at the database level and is independent of storage redundancy.

Why this answer

Why this answer

How to eliminate wrong answers

Option B (Azure Data Factory) is wrong because it is primarily a cloud-based ETL and data integration service that orchestrates data movement and transformation, but it does not provide a native SQL query engine for complex analytical queries or built-in row-level/column-level security on the data itself. Option C (Azure Databricks) is wrong because while it excels at big data processing and machine learning using Apache Spark, it does not natively support enterprise-grade row-level security (RLS) and column-level security at the storage or query layer without additional configuration, and its primary interface is not SQL-first for complex joins across relational and file-based sources. Option D (Azure Analysis Services) is wrong because it is a semantic modeling and BI engine that provides tabular models with RLS, but it is not designed for direct ETL/ELT data transformation or loading into a curated layer, nor does it directly query raw Parquet files in ADLS Gen2 without additional data ingestion steps.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB Core (SQL) API, while fast, typically has single-digit millisecond latencies and is not optimized for the sub-millisecond, in-memory throughput required for real-time leaderboard updates. Option B is wrong because Azure Table Storage is a key-value store with higher latency (10-50 ms) and lacks native sorted set operations, making it unsuitable for frequent score updates and top-N queries. Option D is wrong because Azure Blob Storage is designed for large, unstructured data with high latency (100+ ms) and no support for real-time atomic score updates or ranking queries.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database with a fixed schema, requiring predefined columns for attributes, which does not support varying attributes without complex schema changes or JSON columns that add overhead, and its global read latency is typically higher than 10 ms without additional geo-replication configurations. Option C is wrong because Azure Table Storage is a NoSQL key-value store that can handle varying attributes, but it does not guarantee single-digit-millisecond read latencies globally; its latency is higher (often 10-50 ms) and it lacks the built-in global distribution and low-latency SLAs of Cosmos DB. Option D is wrong because Azure Blob Storage is designed for unstructured binary or text data (e.g., files, images) and is not optimized for low-latency point reads of individual player profiles by ID; it typically has higher latency (tens to hundreds of milliseconds) and does not support querying by PlayerID natively without additional indexing or metadata layers.

Why this answer

How to eliminate wrong answers

Option A is wrong because the on-premises data gateway is used to connect on-premises data sources to Power BI, but Azure SQL Database is a cloud service that can be accessed directly without a gateway. Option B is wrong because scheduled refresh imports data into the Power BI dataset, which introduces latency and requires manual configuration; even with a 15-minute schedule, the data is only as current as the last import, not near real-time. Option D is wrong because a Power BI dataflow is used for data preparation and transformation in the cloud, not for live querying; it still requires a separate import or DirectQuery connection to serve reports.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Databricks with a serverless cluster is a valid serverless Spark option, but it loads into Azure SQL Database, not Azure Synapse Analytics, which does not provide the high-performance reporting capabilities of a dedicated SQL pool. Option C is wrong because Azure Data Factory with a Spark activity still requires a managed Spark cluster (e.g., HDInsight or Databricks) and does not offer a serverless Spark compute option; Data Factory orchestrates but does not run Spark natively in a serverless manner. Option D is wrong because Azure HDInsight requires explicit cluster management (not serverless) and loads into Azure Blob Storage, which is not a high-performance reporting target like Synapse dedicated SQL pool.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is an ETL and data integration service, not a reporting or visualization tool; it would be used to move or transform data before reporting, but not to create the interactive report itself. Option B is wrong because Azure Synapse Studio is an analytics workspace for big data and data warehousing, not a dedicated interactive reporting tool; while it can query data, it lacks the rich visualization and dashboard features of Power BI. Option C is wrong because Azure Analysis Services is a semantic modeling engine that provides analytical data models, but it does not create interactive reports; it would typically serve as a data source for Power BI, not replace it.

Why this answer

How to eliminate wrong answers

Option A is wrong because a clustered index on SaleDate alone would order the entire table by SaleDate, which does not help with equality filtering on CustomerID; the query would still need to scan all rows for a specific customer across all dates. Option B is wrong because a nonclustered index on CustomerID with SaleDate as an included column does not order the index by SaleDate, so the query cannot efficiently perform a range scan on SaleDate; it would require scanning all rows for that customer and then filtering by date. Option D is wrong because a nonclustered index on (SaleDate, CustomerID) orders by SaleDate first, which is less selective for queries that filter by CustomerID first; the query would need to scan multiple date ranges to find the specific customer, reducing performance.

Why this answer

How to eliminate wrong answers

Option B is wrong because Azure Data Factory is an orchestration and ETL tool, not a real-time stream processing engine; it cannot perform live aggregation. Option C is wrong because HDInsight (Spark) requires provisioning and managing clusters, which does not minimize data movement or use serverless compute as efficiently as the chosen combination. Option D is wrong because Azure Functions are stateless and not designed for continuous real-time stream aggregation; Azure SQL Database is not serverless for historical queries and requires provisioning.

Why this answer

How to eliminate wrong answers

Option A is wrong because adding a clustered index on AppointmentID (the primary key) does not help queries filtering by DoctorID and AppointmentDate; it only speeds up lookups by AppointmentID. Option C is wrong because a columnstore index is designed for large-scale analytical queries and aggregations, not for point lookups or range scans on transactional tables; it would not improve the performance of these specific queries. Option D is wrong because the index on (AppointmentDate, DoctorID) would require scanning all appointments in the date range before filtering by DoctorID, which is less efficient than leading with the equality column (DoctorID) to narrow the search space first.

Why this answer

How to eliminate wrong answers

Option B is wrong because write-ahead logs on the Event Hubs namespace are used for Event Hubs internal durability and replication, not for checkpointing the consumer's offset state in Databricks. Option C is wrong because the Hive metastore is designed for storing table metadata and schema information, not for storing streaming checkpoint data or offsets. Option D is wrong because DBFS is a local ephemeral storage tied to the cluster; if the cluster is terminated or fails, checkpoint data stored in DBFS is lost, making it unsuitable for reliable failure recovery.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure SQL Database (single database) does not support CLR integration or Database Mail; it is a fully managed database service that lacks instance-scoped features like SQL Agent and cross-database queries. Option B is wrong because an elastic pool is a collection of single databases sharing resources and inherits the same feature limitations as single databases, so it also cannot run CLR or Database Mail. Option D is wrong because Azure SQL Database Hyperscale is a service tier for single databases that provides high scalability and fast backup/restore, but it does not add instance-level features such as CLR or Database Mail.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB is a NoSQL database designed for low-latency, globally distributed applications, not for cost-effective storage of raw JSON files for exploratory analytics, and it lacks the relational query capabilities needed for BI reports. Option C is wrong because Azure Table Storage is a NoSQL key-value store unsuitable for schema-on-read analytics on raw JSON, and Azure Data Lake Storage is not a relational database for aggregated transactional reporting. Option D is wrong because Azure SQL Database is a relational store that requires a predefined schema, making it inappropriate for raw, schema-less clickstream data, and Azure Blob Storage lacks the relational querying and aggregation features needed for weekly sales reports.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a key-value store designed for structured data, not for large binary files like images, and it does not support SQL-like queries for JSON logs. Option C is wrong because Azure Files is a file share service for SMB protocols, not optimized for high-throughput image delivery with CDN, and Azure SQL Database is a relational store that requires a fixed schema, making it unsuitable for logs with varying structures. Option D is wrong because Azure Disk Storage provides block-level storage for VMs, not a globally distributed object store for images, and Cosmos DB with MongoDB API uses MongoDB query syntax, not SQL-like syntax.

Why this answer

How to eliminate wrong answers

Option B is wrong because the leading column is OrderDate, which is less selective than CustomerID for equality filters, making the index less efficient for the primary filter on CustomerID. Option C is wrong because changing the clustered index to (CustomerID, OrderDate DESC) would require rebuilding the table and could impact other queries that rely on the current OrderID clustered index, and it would not eliminate key lookups for TotalAmount. Option D is wrong because it does not include TotalAmount, forcing key lookups to retrieve that column, and the index order (OrderDate DESC) does not support the equality filter on CustomerID efficiently.

Why this answer

How to eliminate wrong answers

Option B is wrong because increasing the RU/s only improves throughput (request rate) but does not solve the underlying issue of a single logical partition exceeding the 20 GB storage limit, which causes throttling and performance degradation. Option C is wrong because splitting the container by 'userId' range does not help; the problem is that one specific user's data is too large, and splitting by range would still place all that user's data in one partition. Option D is wrong because changing the API (e.g., to MongoDB) does not alter the Cosmos DB logical partition size limit of 20 GB; the same storage constraint applies regardless of the API used.

Why this answer

How to eliminate wrong answers

Option B is wrong because active geo-replication provides continuous data replication to a secondary region for disaster recovery, not extended backup retention. Option C is wrong because auto-failover groups manage automatic failover between primary and secondary databases for high availability, not backup retention. Option D is wrong because geo-redundant backup storage (RA-GRS) replicates backups to a paired region for durability but does not extend the retention period beyond the default 7-day PITR window.

Why this answer

How to eliminate wrong answers

Option B is wrong because Elastic pools are designed to manage and share resources among multiple databases with varying usage patterns, not to offload read traffic from a single database. Option C is wrong because In-memory OLTP accelerates transaction processing by storing tables in memory, but it does not create separate read replicas to handle concurrent read queries. Option D is wrong because Columnstore indexes improve analytical query performance on large datasets, but they do not provide additional read capacity or offload read traffic from the primary database.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is a cloud-based ETL and data orchestration service, not a direct data transfer tool; it adds unnecessary complexity and cost for a simple bulk copy task, and is not optimized for single-shot, high-volume transfers like AzCopy. Option B is wrong because Azure PowerShell is a scripting environment for managing Azure resources, not a dedicated data transfer tool; it lacks the parallelization and resume capabilities needed for efficient 500 GB file transfers. Option C is wrong because Azure Import/Export service is designed for physical shipment of hard drives to Azure datacenters, not for transferring data over the internet; it is intended for very large datasets (terabytes to petabytes) where network transfer is impractical.

Why this answer

How to eliminate wrong answers

Option A (Table API) is wrong because it is designed for key-value and tabular data with a fixed schema, not for JSON documents with nested optional fields like achievements or friendsList, and it lacks native SQL-like querying for array containment. Option B (Cassandra API) is wrong because it uses CQL (Cassandra Query Language) and a wide-column store model, which does not natively support JSON document structures or SQL-like queries for array elements; it also requires a predefined schema. Option C (MongoDB API) is wrong because while it supports JSON documents and flexible schemas, it uses MongoDB's query language (e.g., db.collection.find({achievements: 'specificAchievement'})) rather than SQL-like queries, and the question explicitly requires SQL-like query capability.

Why this answer

How to eliminate wrong answers

Option A is wrong because partition key design distributes data across physical partitions for scalability and write performance, but it does not create a pre-computed, denormalized copy of data optimized for a specific query pattern; a poorly chosen partition key can even increase RU costs for queries. Option B is wrong because the change feed is a mechanism to capture incremental changes (inserts, updates, deletes) to items in a container, enabling event-driven processing or replication, but it does not itself provide a query-optimized, persisted view of the data. Option C is wrong because composite indexes improve query performance by indexing multiple properties in a specific order, but they do not create a separate, pre-materialized dataset; they still require the query engine to scan indexed data at query time, which may not be as efficient as a materialized view for frequent aggregation or filtering.

Why this answer

How to eliminate wrong answers

Option B (Azure Analysis Services) is wrong because it is a semantic modeling service that uses tabular models and DAX/MDX queries, not T-SQL, and it does not support serverless compute or direct execution of complex T-SQL joins against raw data. Option C (Azure Databricks) is wrong because it is an Apache Spark-based analytics platform that primarily uses Python, Scala, or SQL (Spark SQL), not native T-SQL, and it requires provisioning clusters even with auto-scaling, not a true serverless option for T-SQL workloads. Option D (Azure SQL Database creating a secondary replica) is wrong because while a readable secondary replica can offload read-only queries, it does not support independent compute scaling (it mirrors the primary's compute) and lacks a serverless option for the secondary; it also still uses the same underlying database engine, which may not handle massive aggregation workloads efficiently.

Why this answer

How to eliminate wrong answers

Option B (Azure Stream Analytics) is wrong because it is a real-time stream processing service that cannot directly query batch data in Data Lake Storage Gen2 using SQL for historical reports; it lacks a unified SQL query layer over both streaming and batch sources. Option C (Azure Data Lake Analytics) is wrong because it is a batch-only analytics service that processes data using U-SQL, not SQL, and does not support real-time streaming ingestion from Event Hubs. Option D (Azure HDInsight) is wrong because it is a managed Hadoop/Spark cluster that requires manual setup and management for both streaming and batch workloads, and it does not provide direct SQL querying of data in the data lake without additional tools like Hive or Spark SQL, making it less integrated and more complex than Synapse Analytics.

Why this answer

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes data evenly without any logical grouping, which would still cause data movement for filtered queries and likely worsen performance. Option C is wrong because rowstore is optimized for point lookups and small transactions, not for analytical queries on large fact tables; columnstore is already the correct choice for data warehousing workloads. Option D is wrong because replicating the entire fact table to all compute nodes would consume excessive storage and memory, and is only practical for small dimension tables, not large fact tables.

Why this answer

How to eliminate wrong answers

Option A is wrong because a single database with provisioned throughput tier allocates dedicated resources per database, which does not allow sharing resources across databases and would likely increase costs due to over-provisioning for peak loads. Option C is wrong because SQL Managed Instance provides near-100% SQL Server compatibility with isolated resources, not shared resource pooling, and is designed for lift-and-shift scenarios rather than cost-efficient bursting across multiple databases. Option D is wrong because SQL Server on Azure Virtual Machine requires full management of the VM and SQL Server, including patching and backups, which contradicts the goal of minimizing management overhead, and does not natively support resource sharing or bursting across databases.

Why this answer

How to eliminate wrong answers

Option A is wrong because Azure Database for MySQL serverless, while consumption-based and serverless, is not a relational database service that natively integrates with the mobile app ecosystem as seamlessly as Azure SQL Database, and it lacks the same level of built-in features for activity logs and goals tracking that SQL Server provides. Option B is wrong because SQL Server on Azure Virtual Machines requires significant administration (patching, backups, scaling) and has a fixed pricing model (pay for provisioned VMs), not consumption-based, making it unsuitable for a startup with limited budget and minimal administration needs. Option D is wrong because Azure SQL Managed Instance is designed for lift-and-shift migrations with full SQL Server compatibility and is provisioned with fixed compute and storage, not consumption-based pricing, and it requires more administration than serverless options.

Why this answer

How to eliminate wrong answers

Option B is wrong because Azure Synapse dedicated SQL pool requires provisioning and managing dedicated compute resources (e.g., Data Warehouse Units) with a fixed hourly cost, not a pay-per-query model, and does not meet the 'no infrastructure management' requirement. Option C is wrong because Azure SQL Database is a managed relational database service for transactional workloads, not designed for petabyte-scale analytics on Data Lake Storage Gen2, and it incurs ongoing compute costs regardless of query usage. Option D is wrong because Azure HDInsight requires managing a cluster of virtual machines (e.g., for Spark or Hive) with persistent costs, and does not offer a serverless, pay-per-query model for SQL queries on Data Lake Storage Gen2.

Why this answer

How to eliminate wrong answers

Option A is wrong because structured data requires a strict schema (e.g., tables with rows and columns), whereas doctor's notes are free-form text. Option B is wrong because semi-structured data (e.g., JSON, XML) has tags or markers to separate elements and enforce hierarchy, but doctor's notes have no such organizational metadata. Option D is wrong because relational data is a subset of structured data organized into tables with defined relationships, which does not apply to free-text notes.

Why this answer

How to eliminate wrong answers

Option A is wrong because a clustered index on OrderID does not help filter by CustomerID and OrderDate; it only speeds up lookups by OrderID, leaving the query to scan all rows for the desired customer and date range. Option C is wrong because indexing on (OrderDate, CustomerID) first orders by date, which is less selective than customer; the query filters by a specific customer first, so the index would need to scan many date ranges to find that customer's rows. Option D is wrong because an index on TotalAmount is irrelevant to the query's filter conditions on CustomerID and OrderDate, providing no performance benefit for these queries.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Read-access geo-redundant storage (RA-GRS) requires explicit configuration of the 'sku.name' property to 'Standard_GRS' and setting 'supportsHttpsTrafficOnly' or enabling read-access via properties, which are absent in the snippet. Option B is wrong because Zone-redundant storage (ZRS) requires the 'sku.name' to be set to 'Standard_ZRS' and the storage account to be deployed in a region supporting availability zones, neither of which is indicated in the snippet. Option D is wrong because Geo-redundant storage (GRS) also requires explicit 'sku.name' of 'Standard_GRS' and is not the default; the snippet shows no such property, so the default LRS applies.

Why this answer

How to eliminate wrong answers

Option B is wrong because it places unstructured (JPEG) as the most structured, which is incorrect — JPEG files have no schema and are the least structured. Option C is wrong because it places semi-structured (JSON) as the most structured, but relational tables with fixed schemas are more structured than JSON documents. Option D is wrong because it places unstructured (JPEG) in the middle, but JPEG files are the least structured of the three data types.

Why this answer

Why this answer

How to eliminate wrong answers

Option A (Active geo-replication) is wrong because while it provides a readable secondary database with an RPO of less than 5 seconds, it does not support automatic failover; failover must be initiated manually, which violates the RTO requirement of less than 30 minutes. Option C (Geo-restore) is wrong because it restores a database from a geo-replicated backup with an RPO of 1 hour (not less than 5 seconds) and an RTO that can take hours, failing both the RPO and RTO requirements. Option D (Transactional replication) is wrong because it is designed for one-way or bidirectional data synchronization with higher latency and manual failover, not for automatic disaster recovery with sub-5-second RPO and sub-30-minute RTO.

Why this answer

How to eliminate wrong answers

Option B (Azure SQL Database) is wrong because it requires a fixed relational schema, making it cumbersome to store products with varying attributes per category without extensive use of EAV (Entity-Attribute-Value) patterns or JSON columns, which negate the benefits of a relational store. Option C (Azure Blob Storage) is wrong because it is designed for unstructured binary or text data, not for querying individual JSON documents by fields like category and price range; it lacks native indexing and query capabilities for document-level attributes. Option D (Azure Table Storage) is wrong because it is a key-value store that does not support native JSON document storage or querying by nested attributes; it requires flat schemas and cannot efficiently handle queries on multiple properties like price range across different product categories.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because the Table API is a key-value store optimized for simple lookups by partition and row key, and it cannot perform graph traversals like 'friends of friends'. Option C is wrong because Azure Cache for Redis is an in-memory cache, not a persistent data store, and while it supports data structures like sets, it lacks a native graph query language for multi-hop traversals. Option D is wrong because Azure Blob Storage is an object store for unstructured binary data (e.g., images, videos) and has no query engine for relationship traversal.