Scenario practice questions

Data warehouse

Why wrong: A data warehouse stores data that has been extracted, transformed, and loaded (ETL), organizing it into structured star schemas for business intelligence and reporting. This schema-on-write approach requires defining the schema before loading, making it impossible to preserve raw data in its original form; instead, warehouse data is curated and aggregated to answer known analytical questions.

Relational database

Why wrong: A relational database enforces a rigid schema and ACID-compliant transactions, which are essential for OLTP applications like order processing but ill-suited for ingesting raw IoT streams. Storing unstructured or semi-structured sensor data would force a transformation into tables, losing fidelity and creating scalability bottlenecks for high-velocity, heterogeneous data.

Data mart

Why wrong: A data mart is a logically separated subset of a data warehouse, tailored to a single business unit or process, such as sales or finance. It typically contains aggregated and pre-transformed data, so it inherits the warehouse's schema-on-write constraints and cannot accommodate raw, unmodified IoT streams; its scope is too narrow and its data too curated for exploratory big data analytics.

The collation setting is not compatible with Azure Synapse

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

Azure Cosmos DB (SQL API)

Why wrong: Azure Cosmos DB SQL API is a document-oriented NoSQL database that automatically indexes every property and supports rich SQL-style queries over JSON documents. This flexible querying engine and its associated Request Unit (RU) consumption drive up cost, especially when you only need a simple key lookup. Without a requirement for document querying, global distribution, or a SQL query language, this offering is much more expensive than Azure Table Storage for the same basic key-value data.

Azure Cosmos DB (Table API)

Why wrong: Azure Cosmos DB Table API offers the same table row schema as Azure Table Storage but runs on Cosmos DB's globally distributed platform with RU-based throughput provisioning. Even without enabling multi-region writes, the account requires a minimum amount of provisioned throughput per container, making the baseline cost higher than Azure Table Storage alone. For a single-region, non-distributed workload, the added multi-region and SLA capabilities are simply unnecessary overhead.

Azure SQL Database

Why wrong: Azure SQL Database is a fully managed relational database engine with ACID transactions, T-SQL queries, and a rigid schema. Using it to store simple key-value pairs would force you to design tables, define primary keys, and pay for compute (DTUs/vCores) and storage that are far beyond the need for lightweight point lookups. The operational and licensing costs cannot compete with a dedicated NoSQL key-value store for this scenario.

Azure SQL Database Single Database

Why wrong: With Azure SQL Database Single Database, each tenant database is provisioned with a fixed service tier and compute size, so you pay for dedicated resources even when a tenant is idle. For many small SaaS tenants with intermittent demand, you must size every database to handle its own peak load, leaving most reserved capacity unused. This model has no mechanism for one tenant’s burst to consume another tenant’s idle capacity, making it far more expensive than an elastic pool that shares a resource set among all tenant databases.

Azure SQL Managed Instance

Why wrong: Azure SQL Managed Instance provides near-full SQL Server engine compatibility and is designed for lift-and-shift migrations of existing applications, not for pooling compute across small, variable-workload databases. It fails here because each managed instance has a fixed set of resources (vCores and storage) that cannot be elastically shared among multiple tenant databases; you would need to over-provision for peak loads per instance. It is tempting because it offers high compatibility and built-in high availability, which would be correct if the company needed to migrate an on-premises SQL Server application with minimal changes, rather than optimising cost through resource pooling.

SQL Server on Azure Virtual Machine

Why wrong: SQL Server on Azure Virtual Machine is a lift-and-shift IaaS option where you manage the operating system, SQL Server installation, patching, high availability, and licensing. You cannot use Azure SQL Database elastic pools with it; instead, you would need to manually build resource-sharing mechanisms, such as a cluster of VMs and SQL Server Availability Groups, and still each VM has a fixed number of vCPUs and memory that must be over-provisioned for peak. The administrative burden and lack of native elastic resource sharing across tenant databases makes this unsuitable for a multi-tenant SaaS cost-optimization goal.

Atomicity

Why wrong: Atomicity means a transaction is treated as an all-or-nothing unit: if any part of it fails, all changes are rolled back. A foreign-key violation causes the update statement to be rejected before it writes data, so no partial change exists to roll back. Atomicity alone does not validate data; it would still allow an invalid row to remain if the statement completed, so this failure is not about the A property.

Isolation

Why wrong: Isolation controls how concurrent transactions interact, preventing dirty reads, lost updates, and write skew via locks or snapshot mechanisms. In this scenario, the update is invalid even if it is the only transaction executing; no concurrency or interleaving is involved. The rejection results from checking a database constraint, not from any isolation level or locking behavior, so this is not an I property issue.

Durability

Why wrong: Durability ensures that once a transaction commits, its changes persist even after a system crash, typically through write-ahead logging or similar techniques. Because the foreign-key check fails before the update is committed, the transaction never reaches the commit point, so there is no successful change to be made durable. Durability cannot retrospectively validate data; it only preserves the state of already-committed transactions.

Azure Data Factory

Why wrong: Azure Data Factory is an extract-transform-load (ETL) orchestration service designed for scheduled, batch-oriented data movement and pipelined transformations, not for continuous processing of live telemetry. It lacks native temporal windowing functions and cannot maintain a low-latency connection to Power BI for dashboard refreshes every few seconds; instead, it would be used to periodically copy raw temperature files into storage before separate processing.

Azure Databricks

Why wrong: Azure Databricks is a unified analytics platform designed for batch and stream processing using Apache Spark, but it lacks native integration with Power BI for sub-minute dashboard refreshes; the correct service, Azure Stream Analytics, directly outputs to Power BI via its built-in sink without requiring additional orchestration. It is tempting because Databricks excels at complex transformations and machine learning on streaming data, making it the right choice if the scenario demanded advanced analytics like anomaly detection rather than simple aggregation and direct dashboard output.

Azure SQL Database

Why wrong: Azure SQL Database is a managed relational database system that stores and queries persistable tabular data using standard T-SQL; it is not a stream processing platform and cannot ingest unbounded real-time events directly from sensors. Even though Power BI can query SQL Database, the absence of built-in windowing and event-time handling means you would need another service to aggregate the temperature stream before it can be visualized sub-minute.

A. Transactional workload

Why wrong: Transactional workloads involve OLTP systems where individual records are inserted, updated, or deleted in real time with strict consistency. While the sensor data is ingested in real time, the primary purpose is monitoring and alerting, not transactional updates that require ACID guarantees across multiple related tables.

B. Analytical workload

Why wrong: Analytical workloads typically involve querying and aggregating large volumes of historical data to support business intelligence or reporting. The sensor data here is used for real-time monitoring rather than historical analysis, so this does not fit an analytical workload.

C. Batch processing

Why wrong: Batch processing is a data processing model where data is accumulated over a period (e.g., hourly, daily) and then processed in bulk at scheduled intervals. The hospital's IoT sensors emit a continuous stream of vital-sign readings every minute, and the workload requires immediate ingestion and action—such as triggering alerts or updating dashboards—rather than waiting for a batch window. Even if a batch job could later analyze historical trends, the primary processing pattern here is one of continuous, low-latency handling, so calling it 'batch processing' mischaracterizes the system's real-time operational requirement. A batch approach would introduce unacceptable latency for time-critical patient monitoring.

Consistency

Why wrong: Consistency ensures that a transaction transforms the database from one valid state to another by enforcing constraints, triggers, and referential integrity—for example, a negative account balance may be rejected. In the bank scenario, consistency would catch a state where the destination account was credited without a corresponding debit if the database has a rule stating that transfers must balance, but it does not manage the sequencing or rollback of operations that is the essence of atomicity.

Isolation

Why wrong: Isolation controls how concurrent transactions see each other's uncommitted changes, governing read phenomena such as lost updates or dirty reads. If two transfers touch the same account, isolation levels (e.g., READ COMMITTED, SERIALIZABLE) determine whether one transaction's intermediate debits are visible to the other. However, isolation does not bundle multiple statements into one unit; it addresses interleaved executions, not the rollback of a failed transfer sequence, so it is not the decisive property here.

Durability

Why wrong: Durability focuses on what happens after a COMMIT is acknowledged, not on preventing partial work during execution. Once the transfer’s commit record is written to the transaction log and the acknowledgment is sent, durability guarantees the updated balances survive subsequent power loss, crash, or restart. In the scenario, if the system fails before commit, durability does not require undoing a partial $500 debit—that responsibility belongs to atomicity, not durability.

Consistency

Why wrong: Consistency ensures that a transaction moves the database from one valid state to another by enforcing all integrity constraints, such as foreign keys, unique indexes, check constraints, and custom business rules. In a fund transfer, consistency could verify that the sum of all account balances is preserved or that a negative balance is not allowed, but it does not specify what happens when a statement fails halfway through. A database can be consistent before and after a transaction yet still leave partial writes if the transaction is not atomic. Thus, consistency is a state-oriented guarantee, whereas the question describes a process-oriented all-or-nothing guarantee.

Isolation

Why wrong: Isolation governs how the database handles the temporary intermediate state of a transaction while it is running, specifically shielding it from concurrently executing transactions. In the fund-transfer scenario, isolation would prevent another transaction from reading the account balance in the brief window after the debit but before the credit, avoiding inconsistent observations. However, isolation does not guarantee that the transfer itself will be rolled back if something goes wrong; it only serializes or flags concurrent access. The scenario is about failure atomicity, not about interference between simultaneous operations.

Durability

Why wrong: Durability is the ACID property that only comes into play after the transaction has been successfully committed, not during its execution. It promises that once the database acknowledges the completed fund transfer, the changes will survive subsequent crashes, power losses, or media failures because the log records have been flushed to stable storage. Durability does not enforce any rollback semantics or all-or-nothing rule; it assumes the transaction already succeeded. Therefore, it cannot explain the requirement that a failure during the transfer leaves no partial debit.

Consistency

Why wrong: Consistency ensures that a transaction brings the database from one valid state to another, but it does not govern the reversal of partial operations after a crash. The automatic rollback of the debit is enforced by the **Atomicity** property, which guarantees that all steps of a transaction complete or none are applied. Consistency is tempting because it also concerns data correctness, but it would be the correct choice only if the scenario involved violating a business rule—such as allowing the debit to create a negative balance when a constraint forbids it.

Isolation

Why wrong: Isolation is wrong because it governs how transactions interact with each other when they run concurrently, not how a single transaction behaves after a crash. It ensures that uncommitted changes from one transaction are invisible to other transactions and prevents issues like dirty reads or lost updates. In this scenario, there is no competing transaction interfering with the debit, and the problem is that the transaction itself crashed mid-execution. Since the rollback of the debit is required regardless of any other concurrent activity, isolation is not the ACID property that explains this outcome.

Durability

Why wrong: Durability does not apply because it only guarantees that once a transaction is committed, its changes will survive subsequent crashes or power failures. Here, the transaction never reached a commit point—the debit was written but the credit was not, and the transaction aborted. Durability would require the debit to remain permanently applied only if a COMMIT had occurred, but no commit happened. The automatic reversal of the uncommitted debit is a rollback, which is the opposite of a durability guarantee.

Single database (DTU model)

Why wrong: A single database in the DTU model is provisioned with its own isolated DTU allocation and is billed at that full fixed tier even when it is idle. Keeping each business unit as its own single database would require choosing a DTU/performance tier for every database independently, and those separate reservations cannot be shared among units. With several independently scalable databases, this typically results in wasted capacity and higher total cost, so it is not the best fit for this scenario.

Managed Instance

Why wrong: Azure SQL Managed Instance delivers near 100% SQL Server engine compatibility, isolated VNet placement, and dedicated compute/storage, making it a lift-and-shift target for existing SQL Server workloads rather than a multi-database pooling service. You cannot place multiple business-unit databases into a single elastic pool inside a Managed Instance; each instance is independently sized and typically hosts SQL Agent jobs, cross-database queries, and instance-level features. Though it can host many databases, its pricing and governance model are tied to a dedicated instance, so it does not provide the shared, elastic per-database resource allocation this scenario needs.

Database per server (hyperscale)

Why wrong: Hyperscale is an architecture within Azure SQL Database for a single heavily-scaled database, separating compute, storage, and log so a database can scale to many TB with fast provisioning. Calling this option 'database per server (hyperscale)' is misleading because it still amounts to one independently managed database, not a shared pool; each business unit would get its own isolated hyperscale capacity and be billed separately. It is designed for extreme OLTP scale and availability on one database, not for pooling the resource demands of several small, variable business units.

Eventual consistency

Why wrong: Eventual consistency is a replication model where replicas asynchronously converge, so reads may return stale data until all replicas are updated. During that window, the like count could display an incorrect value that changes across refreshes, or a user's like might be missing for others. For a feature that demands immediate, accurate feedback like a like count, this model is unsuitable because it tolerates those temporary inconsistencies.

Session consistency

Why wrong: Session consistency, also known as read-your-writes consistency, ensures that within the same session a user's own writes are immediately visible to that user, but writes from other sessions may not be visible until later. Applied to like counts, a user would see their own like reflected instantly, but other users might not see that like, and the user might not see likes made by others until a later read. This yields different views of the like count across users, failing the requirement for globally accurate instantaneous totals.

Bounded staleness consistency

Why wrong: Bounded staleness consistency allows reads to be stale by a defined limit, such as at most 'K' versions or 'T' seconds behind the latest write. For example, a read could return the like count as it was up to five seconds ago, which would be technically 'consistent' within that bound. Because the product requires the like count to be accurate at the moment it is displayed, even a small bounded lag would show an outdated total to some users, so this model does not meet the requirement.

Relational database

Why wrong: A relational database could store this order data, but it would require decomposing the order into multiple normalized tables—Customers, Orders, and OrderItems—linked by foreign keys. Queries to retrieve a full order with its items demand complex JOIN operations and a fixed schema enforced upfront. This makes it more cumbersome than a document database for this embedded, nested order structure, even though it's technically possible.

Key-value store

Why wrong: A key-value store, such as Azure Table Storage or Redis, is optimized for point lookups using a single primary key, like an order ID. It does not natively support filtering on multiple attributes such as customer ID or order date; you would need to scan all items or maintain separate secondary index tables manually. This makes it a poor fit for querying orders by customer or date, which the scenario implies.

Graph database

Why wrong: A graph database, such as Azure Cosmos DB Gremlin or Azure Neptune, models data as nodes and edges and excels at traversing many-to-many relationships. Orders, customers, and items are not inherently relationship-centric—they form a simple hierarchy that is better served by a document structure. Using a graph database would require modeling every order as a node with edges to items and customers, which adds unnecessary complexity and performance overhead for simple range or equality queries.

Atomicity

Why wrong: Atomicity guarantees that a multi-step transaction is treated as a single, indivisible unit: either all its operations (like updating inventory, recording payment, and printing a receipt) commit successfully, or none are applied. It prevents partial updates when a failure occurs mid-transaction, but it has nothing to do with how different transactions interact when run at the same time. Interference between concurrent POS transactions is a matter of isolation, not atomicity.

Consistency

Why wrong: Consistency ensures that every transaction leaves the database in a valid state that satisfies all defined rules, such as foreign keys, unique constraints, check constraints, and data types. For a POS system, this might mean that a product's quantity on hand can never drop below zero and that a sale must reference an existing customer. This correctness of data relative to schema rules does not control how concurrent transactions observe or interfere with each other's uncommitted changes; that is the role of isolation.

Durability

Why wrong: Durability guarantees that after a transaction is committed, its changes are permanently recorded even if the system immediately crashes, loses power, or experiences a storage failure. In a POS context, this means a completed sale's record and inventory deduction survive a reboot or network outage. However, durability says nothing about the visibility of uncommitted data or the interaction between transactions executing concurrently; those issues are governed by isolation.

Key-value store

Why wrong: Key-value stores treat each item as an opaque value addressed only by a unique key, offering extremely fast point lookups but no built-in query engine for filtering, aggregation, or joins. To obtain a customer's order history you would need to store all orders under that customer's key or maintain a separate index and then perform join-like logic in application code, which is inefficient and error-prone. Because the data lacks a fixed schema and cross-record relationships are not first-class, this model is poorly suited to the relational querying this scenario requires.

Document database

Why wrong: Document databases store semi-structured data as JSON or BSON, and the common design is to embed order history inside a customer document to avoid joins. While this works for displaying a customer with their orders, it becomes problematic for write-heavy workloads, cross-customer analytics, or queries like 'total orders per zip code', which would require scanning and transforming many documents. There is no native join across collections, so combining independent customer and order datasets would either require denormalizing data or client-side merging. This makes a document store less appropriate than a relational database for this requirement.

Graph database

Why wrong: Graph databases excel when the primary value is traversing ad-hoc relationships, such as friends-of-friends or network paths, using nodes and edges with a query language like Cypher. For this customer/order scenario, however, the relationships are fixed and straightforward: a customer has many orders with known attributes like address and order date. Expressing a simple tabular join in a graph model requires extra application logic, and the overhead of graph traversal provides no benefit over the direct, set-based SQL joins a relational database offers. Thus, a graph database is not the standard choice for this traditional transactional workload.

Azure Cosmos DB with SQL API

Why wrong: Azure Cosmos DB with SQL API is a multi-model, globally distributed NoSQL database that can store leaderboard entries and index them, but it is not purpose-built for real-time ranked set operations. Every read and write consumes Request Units (RUs), and producing a top-N list requires queries that scan or use composite indexes, resulting in higher latency and cost than an in-memory sorted set. While Cosmos DB can technically power a leaderboard, the overhead and sub-millisecond performance gap make it a less efficient choice than a dedicated caching layer.

Azure Table storage

Why wrong: Azure Table storage is a schema-less key-value store that organizes data into partitions and rows, allowing fast point reads by partition key and row key. However, it lacks native support for sorted sets or range-based ranking queries; retrieving a leaderboard would require scanning rows and sorting client-side, which is slow and inefficient. It is also disk-backed, so latency is significantly higher than an in-memory cache, making it unsuitable for a high-performance leaderboard that needs constant score updates and instant top-score retrieval.

Azure Blob Storage

Why wrong: Azure Blob storage is designed for storing massive numbers of unstructured binary or text files, such as images, videos, logs, and backups. It provides no built-in query engine, no server-side sorted-set operations, and no in-memory data structures; to update a leaderboard stored as a blob, you would have to rewrite the entire blob with every score change. This leads to exorbitant latency, high cost, and no concurrency control, making it completely unsuitable for a high-performance, real-time leaderboard.

The order processing is an OLAP workload; the weekly reporting is an OLTP workload.

Why wrong: This is exactly reversed. Order processing is transactional in nature: each customer action reliably creates or modifies a single order record, requiring ACID guarantees and immediate response — the hallmark of OLTP. Weekly reporting is analytical, scanning millions of order rows to compute trends, averages, and totals for business decisions — the hallmark of OLAP. Swapping them confuses write-heavy operational systems with read-heavy decision-support systems.

Both workloads are OLTP workloads.

Why wrong: This option fails because it ignores the fundamentally different query patterns. While order processing issues many small INSERT/UPDATE/DELETE statements against current state, weekly reporting executes complex SELECT statements with GROUP BY and SUM/COUNT aggregations over weeks of stored order history. OLTP is designed for transactional integrity on individual records; OLAP is designed for multidimensional analysis across large datasets, so reporting cannot be classified as OLTP.

Both workloads are batch processing workloads.

Why wrong: This is incorrect because only the weekly reporting is a batch-oriented job. Order processing occurs in real time or near real time as customers browse and check out; a store cannot wait for an overnight batch to accept a payment or reserve inventory. The presence of one periodic batch task does not make the entire workload batch, and OLTP transactions are fundamentally interactive rather than scheduled bulk operations.

Online Transaction Processing (OLTP)

Why wrong: These systems are optimized for high-volume, low-latency writes and reads of individual records (e.g., order transactions), not for scanning and aggregating years of historical data. OLTP stores enforce ACID properties and maintain a normalized current state, so complex analytical queries over large time ranges would cause severe lock contention and performance degradation. The scenario is a classic analytical read-heavy workload, not a transactional OLTP workload.

Batch processing

Why wrong: Batch processing is an execution methodology—running jobs on collected data at scheduled or periodic intervals—rather than a workload type. While historical sales data is often processed in batches as part of an ETL pipeline, the underlying workload that serves the analysis is still OLAP. The question asks what kind of processing best describes the analysis itself, not how the data happens to be delivered to the analytical system.

Stream processing

Why wrong: Stream processing is built for unbounded data that arrives continuously and needs near-real-time analysis (e.g., IoT telemetry, click streams, fraud detection). Historical sales data is finite, at rest, and already stored, so it is not an incoming event stream. Applying stream processing would be architecturally wrong because the data is not being generated in real time, and the analysis requires iterative exploration over long historical spans.

ACID compliance

Why wrong: ACID compliance is a guarantee provided by an individual database engine for its own transactions—atomicity, consistency, isolation, and durability within that single system. When account balances live in separate or heterogeneous stores, no single ACID transaction spans them; each store might comply with ACID independently, but there is no global atomic commit. Achieving all-or-nothing behavior across those stores requires a distributed transaction manager or a saga-based compensation flow, not just reliance on each store's local ACID properties.

Idempotency

Why wrong: Idempotency means that repeating the same operation (e.g., retrying a credit after a network timeout) produces the same outcome and does not apply the transaction twice. It protects against duplicate writes, but it does not make two separate writes in different stores atomic or roll back one if the other fails. Cross-store consistency requires a coordinator or a compensation flow, not just retry-safe handlers.

Schema flexibility

Why wrong: Schema flexibility is the ability of a data store to accept records with different fields or evolve its structure without rigid constraints—common in document databases like Cosmos DB or MongoDB. This is helpful for storing account balances with varying metadata, but it is unrelated to transactional behavior. The problem of keeping a debit and credit consistent across stores is about write atomicity, not about how the data is shaped or migrated.

Use interactive clusters with autoscaling

Why wrong: Interactive clusters with autoscaling are optimized for ad hoc exploration and collaborative notebook development, not for recurring production jobs. They remain warm between workloads to support interactivity, which incurs continuous per-second or per-minute costs even during idle gaps. Autoscaling only adjusts the number of worker nodes within a live cluster; it does not deallocate the cluster between scheduled runs. This makes them far more expensive and operationally inefficient than transient job clusters for retail data transformation jobs.

Use Azure Synapse Serverless SQL pools

Why wrong: Azure Synapse Serverless SQL pools are a distributed query engine for executing on-demand T-SQL queries directly against data files in the data lake, priced per TB of data scanned. They do not provide the Databricks runtime, Spark APIs, or a cluster lifecycle management interface needed to run scheduled Databricks notebooks or JAR jobs. Attempting to use them for Databricks processing would be architecturally incorrect, because serverless SQL cannot execute Spark code or Databricks job workflows and has no concept of job clusters.

Provision a dedicated SQL pool in Azure Synapse

Why wrong: A dedicated SQL pool in Azure Synapse is a provisioned, persistent data warehouse cluster you pay for per hour regardless of query load, and it is designed for relational MPP query workloads, not general-purpose data transformation with Spark. It cannot run Databricks clusters or execute Databricks job definitions, and scaling or pausing it is separate from running scheduled Databricks jobs. For cost-effective Databricks compute on ADLS Gen2, this option introduces unrelated infrastructure and continuous charges, so it is the wrong answer.