Refer to the Exhibit Practice Questions

The dataset is too large for aggregations

Why it fails: Aggregations are a performance feature explicitly designed for large datasets, pre-summarizing data to reduce query load. Dataset size alone does not prevent an aggregation from being created or used; rather, large datasets often benefit the most from aggregations. The real problem is a grain mismatch, not data volume. Therefore, this statement incorrectly identifies size as the blocker.

The aggregation is not in DirectQuery mode

Why it fails: Aggregations in Power BI can be configured for both import and DirectQuery storage modes; DirectQuery is not a prerequisite for using aggregations. The mode determines how data is stored and queried, but it does not determine whether an aggregation's granularity matches query requirements. Since the issue is that daily queries cannot leverage a monthly aggregation, the storage mode is irrelevant to this failure.

The aggregation has too many dimensions

Why it fails: The number of dimensions in an aggregation affects its size and potential to answer queries, but a high dimension count does not prevent an aggregation from being used; it may just reduce compression. The core issue described is a granularity mismatch—monthly vs daily—not the count of dimensions. Thus, this misdiagnoses the cause because dimension count and aggregation grain are separate design considerations.

Azure Files

Why it fails: Azure Files is a managed file share service that exposes SMB and NFS endpoints using a different hostname format: storageaccount.file.core.windows.net, not blob.core.windows.net. Additionally, Azure SQL Database does not support external tables that reference Azure Files as an external data source; the supported external storage services are Azure Blob Storage and Azure Data Lake Storage Gen2. The endpoint in the exhibit does not match Azure Files, making it incorrect.

Azure SQL Database

Why it fails: An external table in Azure SQL Database is a schema object that references data stored outside the database in an external storage service, such as Blob Storage. The LOCATION in the exhibit is a URI to an Azure storage container, not a connection string or logical server name that would identify an Azure SQL Database instance. If the data source were Azure SQL Database, the location would resemble a fully qualified database name, not a blob.core.windows.net URL.

Azure Data Lake Storage Gen2

Why it fails: Although Azure Data Lake Storage Gen2 (ADLS Gen2) can be used as an external data source for Azure SQL Database, its primary endpoint uses the dfs.core.windows.net DNS suffix, such as myaccount.dfs.core.windows.net/datalake. The exhibit's URL specifically uses blob.core.windows.net, which is the standard endpoint for Blob Storage even though ADLS Gen2 can sometimes write to a blob endpoint with hierarchical namespace enabled. There is no indication of the DFS endpoint or a hierarchical namespace in the provided URI, so this option is incorrect.

The collation setting is not compatible with Azure Synapse

Why it fails: SQL_Latin1_General_CP1_CI_AS is a standard Windows SQL Server collation that is fully supported by Azure Synapse Analytics, including both dedicated SQL pools and serverless SQL endpoints. Synapse supports a wide range of collations, and this collation is in fact the default for many Azure SQL resources. Therefore, the collation setting is not a barrier to using Synapse; any incompatibility would be due to a different aspect of the template, such as connection policy or storage account configuration.

The database cannot be part of a failover group

Why it fails: Auto-failover groups in Azure SQL Database are defined at the logical server level and allow a set of user databases to fail over as a unit to a secondary server in another region. The ability to create a failover group depends solely on the servers being paired and having the appropriate permissions, not on the storage redundancy option chosen for the individual database. Because GRS is a valid storage redundancy setting, there is no restriction preventing the database from being included in a failover group.

The database will not support Transparent Data Encryption

Why it fails: Transparent Data Encryption (TDE) performs real-time encryption and decryption of database files, backups, and transaction logs at the page level, independently of how the underlying storage is replicated. Whether a database uses locally redundant storage (LRS), geo-redundant storage (GRS), or read-access geo-redundant storage (RA-GRS), TDE can be enabled without any change to the storage redundancy setting. Encryption keys are managed through Azure Key Vault or the service-managed key, and this does not affect or get affected by geographic replication.

The container disables SAS tokens by default

Why it fails: SAS tokens are not a feature that can be toggled on or off at the container level; they are generated at the storage account level using either the account key or a user delegation. The container's publicAccess='None' setting only disables anonymous public read/write access, not SAS-based authenticated access. Additionally, no container property controls SAS existence or validity, so this option incorrectly attributes a storage-account-level mechanism to the container.

The storage account firewall is blocking all traffic

Why it fails: Firewall rules restrict the set of IP addresses or virtual networks that can reach the storage account, but they operate at the network layer rather than the authorization layer. Even if a firewall were misconfigured, a SAS token would still authenticate the request as long as the client's IP is allowed by the firewall rules. Since the exhibit's ARM template does not show any firewall configuration, there is no evidence that network traffic is being blocked, making this an unsubstantiated explanation for SAS failure.

The storage account requires RBAC permissions, not SAS

Why it fails: Azure Blob Storage supports multiple independent authorization methods, including shared key, shared access signature (SAS), and Microsoft Entra ID RBAC, and these are not mutually exclusive. A SAS token is self-contained and carries its own permissions, so no additional RBAC role assignments are required to use it; RBAC would only be necessary if you were using Microsoft Entra ID-based identity instead of a SAS token. Therefore, the storage account does not 'require' RBAC to the exclusion of SAS, and this option mischaracterizes the relationship between the two mechanisms.

Increase maxSizeBytes to 1 TB

Why it fails: Increasing maxSizeBytes to 1 TB in your ARM template only raises the maximum database storage limit; it does not add more compute, memory, or I/O capacity. Your database may still hit the same DTU/vCore limits or storage throughput constraints, so queries won't run faster. In fact, for General Purpose tier, a larger max size can even increase the cost of storage without improving latency or throughput at all.

Set the edition to 'Serverless'

Why it fails: Setting the edition to 'Serverless' changes the compute billing and auto-scaling behavior, not the raw performance ceiling. Serverless can pause and resume, which introduces cold-start latency and may cause connection timeouts for workloads that need consistent low latency. It is not a performance tuning knob; it is an operational model for intermittent, unpredictable usage, and it cannot make an existing general-purpose database 'faster' in a steady state.

Change requestedBackupStorageRedundancy to 'Local'

Why it fails: Changing the requestedBackupStorageRedundancy property to 'Local' alters how your database backups are replicated for durability—it does not affect the live database's compute, memory, or I/O. Local redundancy stores backups in a single data center, which reduces resiliency and may be chosen for cost, but it has zero impact on query execution speed or resource limits. Performance is determined by the service tier, compute size, and replica configuration, not by backup storage redundancy.

partitionId

Why it fails: The partitionId system property only identifies which Event Hubs partition stored the message, not the device that sent it. Partition assignment is handled by the Event Hubs service, either round-robin or by a partition key, and does not carry any device identity information. A message from device-01 can land in any partition, so using partitionId in a WHERE clause would not reliably isolate that device. The property that carries the originating device ID is a different system property.

consumerGroup

Why it fails: A consumer group is a named view of an event stream that allows multiple consumer applications to read the same data independently, without interfering with each other. It is a property of the consumer, not of the message, and it is not included in the message metadata that an Azure Function examines. A message from a device does not have a consumerGroup field, so referencing it in a WHERE clause would fail or filter on an undefined value. This option is unrelated to identifying the device.

deviceId

Why it fails: The deviceId property appears to be a custom application property that a device might place in the message body or properties, but Azure IoT Hub does not reserve that property name. If a device sends a property named deviceId, it is not standardized and could be omitted, misspelled, or set to an incorrect value. Moreover, using it in a WHERE clause would be unreliable because it doesn't come from the IoT Hub's authentication context. The correct, system-managed property is iothub-connection-device-id, which is included by the IoT Hub automatically.

Blob soft delete is enabled

Why it fails: Blob soft delete is a data protection feature that retains deleted blobs for a specified retention period, allowing recovery. It is configured under the Data Protection settings of a storage account, not through the hierarchical namespace (HNS) property. The ARM template would show a separate property such as 'deleteRetentionPolicy' for soft delete, whereas HNS is indicated by 'isHnsEnabled'.

Versioning is enabled for blobs

Why it fails: Blob versioning is an independent Data Protection setting that automatically preserves a previous version of a blob whenever it is modified or deleted. Enabling HNS alone does not turn on versioning; they are configured separately in the Azure portal or ARM template. The exhibit's HNS attribute provides no information about whether versioning is active, as that would require a distinct 'versioning' property.

Geo-redundant storage is configured

Why it fails: Geo-redundant storage (GRS or RA-GRS) is defined by the replication setting within the storage account's SKU, such as Standard_GRS or Premium_LRS. HNS is an orthogonal capability that can be combined with any replication option, so seeing HNS enabled does not imply any specific geo-redundancy. The exhibit only reflects the hierarchical namespace status, not the account's replication configuration.

The query syntax is invalid

Why it fails: The query uses `TumblingWindow(second, 5)` with a correctly formed `GROUP BY` clause, which is a valid and documented windowing function in the Azure Stream Analytics query language. If the syntax were truly invalid, the job would fail during validation or when attempting to start, not silently produce zero rows. Since the job is running, the query has already been accepted by the Stream Analytics service.

The Event Hub name is incorrect

Why it fails: If the Event Hub name were incorrect, the Stream Analytics job would fail to establish an input connection and would log an input-not-ready or authentication error at startup; it would not run silently and produce no output. The input alias references the Event Hub correctly, and because the job is actively running, the input connection is valid. Misconfigured input names typically surface as job-start failures, not missing window results.

The SKU does not support SQL output

Why it fails: Azure Stream Analytics supports Azure SQL Database as an output sink across all service tiers, including the StandardV2 SKU referenced in the exhibit. If the SKU did not support SQL output, the job would fail during output validation with an unsupported output type error, before the query could ever run. Since the job is operational, the SKU is not the reason for the empty result set.

Azure Analysis Services

Why it fails: Azure Analysis Services is not the right choice because it consumes tabular or multidimensional models defined in .bim files or .model files via Visual Studio, not JSON datasource definitions like the one shown. While Analysis Services does connect to data sources, it does not use JSON linked services or pipeline artifacts to orchestrate data movement; its metadata for tables, measures, and relationships resides in a model file rather than an integration configuration.

Power BI

Why it fails: Power BI is wrong because its data connections and queries are stored inside a .pbix binary package, not as standalone JSON configuration files. Power BI uses Power Query M expressions to define data sources, and while it can read JSON as a data format, it does not define linked services or dataset connections with the exact JSON schema shown. The configuration resembles an Azure Data Factory linked service, not a Power BI data source.

Azure Synapse Analytics

Why it fails: Azure Synapse Analytics is not the answer because, although its pipelines inherit Azure Data Factory's JSON-based architecture, the dedicated SQL pool itself relies on T-SQL scripts, external tables, and PolyBase to connect to data sources. The JSON in the exhibit is a linked service definition that belongs to Data Factory's pipeline orchestration model, not to the SQL analytics warehouse interface. If the JSON came from a Synapse pipeline, it would still be a Data Factory artifact, making Azure Data Factory the more direct and correct answer.

Use PolyBase to create external table

Why it fails: PolyBase is a data virtualization feature that creates external tables over Azure Storage, but in Azure Synapse Analytics it is implemented in the dedicated SQL pool—not in the serverless SQL engine. To use it, you must first provision a dedicated SQL pool (billed per second), create an external data source, an external file format, and credential objects, then define an external table before querying. That setup is unnecessary for a quick ad-hoc query against order files, because serverless SQL's OPENROWSET can read the same files directly with no metadata creation.

Copy data to Azure SQL Database using ADF

Why it fails: Using Azure Data Factory Copy Activity to move order data into Azure SQL Database means you are building a data ingestion pipeline that creates a destination table, executes a copy job, and then requires maintenance if the source files are updated. The data would be a point-in-time snapshot, so any new files in the storage account would not be visible until you rerun the pipeline. For an immediate ad-hoc query, this adds latency and incurs costs for ADF activity and SQL DB storage, making it far less efficient than querying the source files directly.

Load data into a dedicated SQL pool

Why it fails: A dedicated SQL pool is a provisioned cluster that charges per second regardless of query activity; before querying data you must either load it with PolyBase/COPY statements or create external tables, both of which require a running pool and careful distribution design. Loading order data into a dedicated pool is a heavy operation that is justified only for large, repeated, low-latency analytical workloads, not for one-off queries. This approach adds provisioning time, load time, and ongoing compute cost, whereas serverless SQL handles the same query instantly without any infrastructure.

SELECT * FROM c WHERE c.category = 'Electronics' OR c.price < 200

Why it fails: The OR operator in this query widens the result set to include every product that either belongs to the Electronics category or has a price below 200. That means inexpensive items from other categories (e.g., Books or Toys) are returned, which violates the required filter of Electronics AND price < 200. Correct filtering requires both conditions to be true simultaneously, which only the AND operator guarantees.

SELECT * FROM c WHERE c.category = "Electronics" AND c.price < 200

Why it fails: In the Azure Cosmos DB SQL API, string literals must be enclosed in single quotes (' '), not double quotes (" "). Double quotes are reserved for escaping system property names or identifiers, so using "Electronics" as a string literal is syntactically invalid and will cause a query compilation error. Replace the double quotes with single quotes to correctly compare the c.category property to the string value.

SELECT * FROM p WHERE p.category = 'Electronics' AND p.price < 200

Why it fails: The FROM clause in a Cosmos DB SQL API query must reference the container with a valid alias, such as 'c' for the source collection. Here, 'p' is used in the SELECT and WHERE clauses but is never defined in the FROM clause, so the query fails with a syntax error because 'p' is not associated with any input row set. You must either use the default alias 'c' consistently or define 'p' as an explicit alias in the FROM clause.

The count of online sales transactions.

Why it fails: This option mistakes the aggregation method. The measure is based on a numeric SUM of the Amount column, not a count of rows or transactions. To count online transactions, you would need COUNTROWS or DISTINCTCOUNT on a transaction key, but the measure clearly uses SUM, so it returns a monetary total, not a transaction count.

The sum of Amount for each product sold online.

Why it fails: This option incorrectly assumes the measure produces a row-level or per-product value. In DAX, a measure is a scalar calculation that returns a single value, and without a GROUPBY or a row context like a product in a visual, SUM(Amount) aggregates across all rows. The CALCULATE filter for Channel='Online' applies to the entire dataset, so the result is one grand total for online sales, not a breakdown per product.

The total sales amount for all channels.

Why it fails: This option ignores the explicit filter inside CALCULATE. The Channel='Online' argument modifies the filter context so only rows where Channel equals 'Online' contribute to the SUM(Amount). If all channels were included, there would be no Channel filter and the measure would simply be SUM(Amount), but the filter specifically excludes non-online channels.

The database will be created and an audit event will be logged

Why it fails: This option incorrectly assumes the policy effect is 'audit', but the definition uses a 'deny' effect. Under a deny effect, Azure Policy evaluates the condition before Resource Manager accepts the request; if the condition matches, the deployment is rejected with a policy violation, and no database resource is created. No database exists, so no audit event for the database itself is emitted — only a failure/denial entry in the activity log would appear. Thus the database is not created and no database-level audit event is logged.

The database will be created and zone redundancy will be automatically enabled

Why it fails: A deny-effect policy cannot modify or set resource properties; it only rejects requests that violate its condition. The Azure Policy effects that can change a property post-deployment are 'modify' or 'deployIfNotExists' combined with a remediation task, neither of which is present in this definition. Because the requested database has zoneRedundant false (which satisfies the policy's deny condition), the creation attempt is blocked outright rather than being silently adjusted to true. Therefore the database is not created, and zone redundancy is not automatically enabled.

The database will be created with zone redundancy disabled

Why it fails: The policy is configured to deny exactly when zoneRedundant equals false, which means a database with redundancy disabled is the disallowed configuration. The deny effect causes the entire create operation to be rejected, so the resource provider never receives the request to create the database. No SQL Database resource is provisioned, with or without zone redundancy, because the policy check happens before the resource is deployed. Thus the database cannot be created with disabled zone redundancy.

SalesAmount

Why it fails: SalesAmount is a numeric measure stored in the fact table, not a dimension key or distribution column. Hash distribution requires a column with high cardinality that is consistently used in joins, such as a surrogate key. Distributing by a monetary value would scatter transactions across distributions arbitrarily, cause uneven distribution due to few high-value orders, and provide no query collocation benefit.

ProductKey

Why it fails: ProductKey distributes data by product, but daily sales transactions require distribution by a column with high cardinality and even access patterns, such as OrderDateKey, to avoid data skew and parallelise loads across distributions. It is tempting because ProductKey is a natural dimension key for joining to a product dimension table, and would be correct in a star-schema fact table where queries frequently filter or aggregate by product rather than by date.

OrderDate

Why it fails: OrderDate has low cardinality (e.g., a distinct value for each day) and can cause data skew because sales volume is not uniform across dates—peak days create distribution hotspots. Distributing a fact table by OrderDate also limits parallelism when loading or querying a narrow date range, since only a few distributions hold all relevant data. For a large fact table, a column like CustomerKey provides far more even distribution and better join performance.

Set supportsHttpsTrafficOnly to false

Why it fails: Setting supportsHttpsTrafficOnly to false disables the requirement for secure HTTPS connections, which exposes data to interception but does not alter how or where storage replicates data. Azure's data durability depends on the SKU's replication setting, not on transport encryption. Therefore this change would reduce security and fail to address the goal of improving cross-region durability.

Change the SKU name to Premium_LRS

Why it fails: Premium_LRS is a performance-tier SKU that delivers low-latency performance using solid-state drives, but it uses locally redundant storage, meaning copies are kept only within a single data center or region. It cannot provide geo-replication because Premium performance tiers are limited to LRS and do not support cross-region copies. Thus it is incorrect because the requirement is about durability across regions, not latency.

Change the kind to BlobStorage

Why it fails: Changing the kind to BlobStorage would convert the account to a legacy blob-only storage type that does not support tables, queues, or files, and it leaves the replication setting unchanged. The storage account kind determines which services and features are available, while replication is controlled separately by the SKU. Since BlobStorage still defaults to the same SKU-based replication, this option does not improve durability across regions.