Hard Difficulty Questions

Data lakes guarantee ACID transactions across all data.

Why wrong: ACID transactions require coordinated atomicity, consistency, isolation, and durability, which a generic data lake does not natively provide across all stored objects. Standard object-store commits are not transactional, so concurrent reads/writes can yield partial or inconsistent results unless a transactional layer such as Delta Lake, Apache Iceberg, or Apache Hudi is added. Even then, ACID guarantees apply only to tables that opt into those formats, not to every file in the lake.

Data lakes store only structured data.

Why wrong: This claim contradicts the core design of data lakes, which intentionally ingest structured, semi-structured, and unstructured data without discrimination. A system that stores only structured data is a relational database or classic data warehouse, not a data lake. Unstructured and semi-structured inputs (documents, sensor streams, clickstream logs, multimedia) are common lake payloads.

Read-access geo-redundant storage (RA-GRS)

Why wrong: RA-GRS would use Standard_RAGRS.

Zone-redundant storage (ZRS)

Why wrong: ZRS would use Standard_ZRS.

Geo-redundant storage (GRS)

Why wrong: GRS would use Standard_GRS.

The dataset is too large for aggregations

Why wrong: 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 wrong: 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 wrong: 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.

Batch input (e.g., Azure Blob Storage)

Why wrong: Azure Blob Storage stores unstructured batch files, not continuous event streams, so it cannot serve as a real-time input that pushes data to Stream Analytics. While Stream Analytics can reference Blob Storage for static reference data or read batch files, doing so requires a manual trigger or scheduler and introduces latency that violates the real-time requirement. A batch input is therefore neither a component of a real-time architecture nor a substitute for a streaming source.

Machine learning model

Why wrong: Machine learning models are optional enhancements that can be embedded in Stream Analytics as user-defined functions to score streaming data, but they are not a prerequisite for real-time processing. A basic real-time solution can simply filter, aggregate, or route data using a standard SQL-like query without any ML inference. Since the question asks for the three components that are required, including ML would misunderstand the scope of a minimal streaming pipeline.

Consistency (C) and Partition Tolerance (P)

Why wrong: Choosing Consistency and Partition Tolerance would mean that during a network partition, the system cannot guarantee availability (e.g., it might refuse writes or reads from some regions to maintain consistency). This is not what the scenario describes—the system must keep accepting bids.

Consistency (C) and Availability (A)

Why wrong: According to the CAP theorem, it is impossible to guarantee both Consistency and Availability in the presence of a network partition. The system must tolerate partitions, so this combination is not achievable in practice.

Durability and Availability

Why wrong: Incorrect. Although Durability is important for recording bids, it is not part of the CAP theorem. The question asks for properties according to CAP, so this option is invalid.

Azure Content Delivery Network (CDN)

Why wrong: CDN caches content at edge locations for faster delivery, but does not reduce storage costs or change tiering.

Azure Files shares

Why wrong: Azure Files is a fully managed file share in the cloud, not designed for cost tiering of blobs.

Geo-redundant storage (GRS)

Why wrong: GRS provides geographic replication for durability, not cost savings related to access patterns.

Azure Files

Why wrong: 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 wrong: 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 wrong: 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.

Output to Azure Data Explorer and then use a Power BI dashboard.

Why wrong: Incorrect. Outputting to Azure Data Explorer and then to Power BI does not provide exactly-once semantics directly from Stream Analytics, and adds complexity without guaranteed exactly-once delivery.

Output to Azure Event Hubs and then use Azure Functions to write to Power BI.

Why wrong: Incorrect. While Event Hubs and Azure Functions can be used to build an exactly-once pipeline, it is not a direct output configuration of Stream Analytics for Power BI, and it introduces additional components and complexity.

Configure the Power BI output with 'Exactly once' delivery mode.

Why wrong: Incorrect. Azure Stream Analytics does not support exactly-once delivery mode for Power BI output; the only available mode is at-least-once.

Use multi-region writes with strong consistency

Why wrong: Incorrect. Strong consistency is not supported when multiple write regions are enabled. Multi-region writes only support eventual consistency.

Use multi-region writes with bounded staleness consistency

Why wrong: Incorrect. Bounded staleness is not available with multi-region writes. Only eventual consistency is supported in that configuration.

Use single-region writes with eventual consistency and implement application-level conflict resolution

Why wrong: Incorrect. Eventual consistency cannot guarantee that all reads see the latest write immediately, which violates the requirement for strong global consistency.

Store all telemetry in Azure Data Lake Storage Gen2 with a hierarchical namespace, and use Azure Synapse Serverless SQL for real-time queries.

Why wrong: ADLS Gen2 is not optimized for sub-second queries on individual records.

Ingest telemetry into Azure Stream Analytics, output to Azure SQL Database for 90 days, then export to Azure Data Lake Storage.

Why wrong: Azure SQL Database is relational and may not handle JSON efficiently; sub-second latency may be challenging.

Ingest telemetry into Azure Event Hubs and then store in Azure Blob Storage with a lifecycle management policy to delete after 90 days.

Why wrong: Blob Storage does not provide sub-second query latency on JSON fields.

Import the JSON files into Azure Cosmos DB and use the SQL API to search.

Why wrong: Cosmos DB SQL API does not natively support partial text search and would require additional indexing.

Use Azure Data Lake Analytics with U-SQL to query the files.

Why wrong: Data Lake Analytics is for batch processing, not real-time search.

Load the data into Azure SQL Database and create full-text indexes.

Why wrong: This requires significant data movement and schema management, increasing overhead.

Use stored procedures to aggregate data on read.

Why wrong: Stored procedures run on the server but still process full documents, not optimizing read cost.

Increase the provisioned RU/s on the source container.

Why wrong: Increasing RU only helps with throughput, not with read efficiency for partial data.

Enable Time-to-Live (TTL) on the source container to automatically expire old data.

Why wrong: TTL is for data retention, not for read performance.

Transparent Data Encryption (TDE)

Why wrong: Transparent Data Encryption encrypts the entire database at rest, not individual columns.

Dynamic Data Masking

Why wrong: Dynamic Data Masking obfuscates data but does not encrypt it.

Row-Level Security

Why wrong: Row-Level Security restricts row access but does not encrypt columns.

Modify the Azure Stream Analytics job to write to Azure Data Lake Storage Gen2, then use Azure Databricks to run batch transformations and load into a separate Azure Synapse Analytics dedicated SQL pool for historical queries. Keep the real-time dashboard connected to Azure SQL Database.

Why wrong: Batch processing adds latency, not sub-second response for historical queries.

Add Azure Analysis Services on top of Azure SQL Database and create a cube for historical queries. Use Azure Stream Analytics to write to Analysis Services directly.

Why wrong: Analysis Services is not designed for sub-second queries on raw historical data; also direct writes are not supported.

Partition the existing Azure SQL Database and add columnstore indexes to improve query performance. Keep the current architecture and increase the DTU/vCore limit to handle both workloads.

Why wrong: This might not solve the write performance issue and still could degrade under high load.

Modify

Why wrong: Modify is not a native POSIX ACL permission in Azure Data Lake Storage Gen2; where tools expose it, it acts as a composite right that bundles read, write, and delete/remove actions. Granting Modify on a folder would let the user create, overwrite, or delete files inside it, which goes far beyond the minimal, traverse-only access needed to read data. This violates least privilege and can lead to unintended data changes or loss, so Execute is the correct choice.

Write

Why wrong: Write (w) on a folder controls changes to the directory's contents, such as creating new files or deleting existing entries, but it does not grant the right to traverse the folder hierarchy. A user with Write but no Execute cannot move through the directory to reach nested files, and Write provides no ability to open an existing file for reading. Thus Write is oriented toward data modification and creation, not toward passing through a path to read data.

Read

Why wrong: Read (r) on a folder in ADLS Gen2 POSIX ACLs permits listing the names of entries in that folder, but it does not allow opening or accessing those entries. To actually traverse the directory and resolve a path to a file, the execute (x) permission is also required; with only Read, the path lookup is blocked and the user cannot reach files in that folder or its subfolders. Therefore Read alone cannot read file contents—Execute must be granted on the folder as well.

Azure Data Factory

Why wrong: Azure Data Factory is primarily a data integration and orchestration service. While it can move and transform data, it does not natively support running complex SQL queries with row-level security.

Azure Databricks

Why wrong: Azure Databricks is an Apache Spark-based analytics platform. It excels at data engineering and machine learning but does not provide built-in row-level or column-level security for SQL queries. Security is typically managed at the file or workspace level.

Azure Analysis Services

Why wrong: Azure Analysis Services is an analytical engine used to create semantic models. It does not directly query ADLS Gen2; data must be loaded from a source. It also does not include ETL capabilities.

DTU-based purchasing model

Why wrong: The DTU-based purchasing model bundles compute, memory, and I/O into fixed performance units (DTUs) sold in Basic, Standard, and Premium service tiers, with no option to pause or automatically scale compute. This model was designed for predictable workloads with a steady baseline of usage, and because it bills at a flat, reserved rate regardless of actual consumption, an application that sees significant idle periods will pay for capacity it never uses. Although elastic database pools can share DTUs across multiple databases, they still do not provide the per-query or per-second auto-scaling and auto-pause behavior found in the serverless vCore tier. Therefore, it does not meet the cost-minimization requirement for a workload that has sharp demand peaks and long quiet stretches.

vCore-based purchasing model with provisioned compute tier

Why wrong: The vCore-based provisioned compute tier allocates a fixed set of resources (vCores and memory) that remain running continuously, regardless of actual workload. This fails the scenario because it cannot automatically scale down or pause during idle nights and weekends, nor scale up dynamically during peak hours—it requires manual resizing or scheduled scaling. This option is tempting because provisioned compute is designed for predictable, steady-state workloads where consistent performance is critical, making it correct for applications with stable, high-volume traffic that never idles.

vCore-based purchasing model with Hyperscale service tier

Why wrong: Hyperscale is designed for very large databases (over 1 TB) with high storage and read scalability. It does not offer auto-pause or compute auto-scaling based on idle periods, and it is more expensive for small to medium databases.

SQL API for both profiles and logs

Why wrong: While the SQL API can store both, it is not optimized for simple key-value lookups with the same performance as the Table API. Using the SQL API for profiles would still work but would be less efficient and more costly for simple reads.

MongoDB API for profiles and Cassandra API for logs

Why wrong: The MongoDB API is document-based and can handle JSON, but it is not as simple as the Table API for key-value. The Cassandra API is column-family and does not natively support SQL-like querying on JSON documents; it is designed for wide-column workloads.

Table API for both profiles and logs

Why wrong: Using the Table API for both profiles and logs would simplify infrastructure but sacrifices query capability. The Table API is optimized for fast point lookups and simple range scans on a fixed partition key/row key schema, not for ad hoc SQL-style queries over nested JSON properties. Session logs typically need filtering by player ID, timestamp ranges, and possibly aggregation of event attributes; the Table API lacks the indexing and query engine to support these operations efficiently. While player profiles are a perfect fit, forcing logs into the Table API would require service-side denormalization and still fail to meet the analytics requirements.

A. Azure Synapse Analytics (serverless SQL pool)

Why wrong: Azure Synapse Analytics' serverless SQL pool is designed for ad-hoc T-SQL queries over files in a data lake, not for Python notebook development. Although Synapse offers notebook experiences through its Spark pools, the serverless SQL pool specifically has no Python runtime, so it cannot meet the data scientists' need for interactive Python exploration. It remains fundamentally SQL-only, making it a mismatch for this workload.

C. Azure HDInsight with Spark

Why wrong: Azure HDInsight with Spark gives you Spark clusters, but these are long-lived, provisioned resources that you must manually size, configure, scale, and patch. Even with auto-scale, you remain responsible for cluster lifecycle management, which introduces significant administration overhead. Since the requirement explicitly calls for minimal administration and serverless-style operation, HDInsight's operational burden makes it a less appropriate choice.

D. Azure Data Lake Analytics

Why wrong: Azure Data Lake Analytics is a batch analytics service that uses U-SQL, a SQL-like language extended with C# expressions, rather than Python notebooks. It does not deliver an interactive collaborative notebook environment where data scientists can explore raw clickstream data cell-by-cell. The service processes queued analytics jobs, whereas the scenario demands notebook-based interactive discovery and transformation.

Azure Stream Analytics

Why wrong: Stream Analytics is purpose-built for near-real-time event processing, allowing you to write continuous SQL queries over data as it streams from sources like Event Hubs. However, it does not provide the ability to interactively query large stored datasets or to combine real-time and historical data without writing results to a separate destination. As a result, it cannot serve as the single unified platform needed for both streaming and batch analytics, leaving a gap in the solution.

Azure Data Factory

Why wrong: Azure Data Factory is a cloud-based ETL and data orchestration service that automates the movement and transformation of data between supported sources and destinations. It does not provide a compute layer capable of serving interactive BI dashboards or running complex analytical queries, nor does it ingest and process unbounded streaming events in real time. While you could use it to build pipelines that feed data into analytics engines, on its own it is not an analytics platform that meets the end-to-end requirements.

Azure HDInsight

Why wrong: Azure HDInsight offers managed Apache Hadoop and Spark clusters that can handle both batch and streaming workloads, but it requires ongoing cluster lifecycle management, including configuring autoscaling, securing connectivity, and monitoring session health. In contrast to Azure Synapse Analytics, HDInsight lacks a built-in serverless SQL engine, a unified workspace, and integrated data governance, so you must stitch together many separate components manually. This extra operational complexity makes it a less seamless choice for a comprehensive analytics solution.