Scenario practice questions

Consistency

Why wrong: Consistency ensures that a transaction brings the database from one valid state to another, maintaining data integrity rules. It does not directly guarantee the all-or-nothing execution of operations.

Isolation

Why wrong: Isolation ensures that concurrent transactions do not interfere with each other. It addresses visibility of intermediate transaction states, not the atomicity of operations within a single transaction.

Durability

Why wrong: Durability guarantees that once a transaction is committed, its changes persist even in the event of a system failure. It does not relate to the all-or-nothing execution of a transaction.

Single database with DTU purchasing model

Why wrong: Incorrect. A single database with DTU charges separately per database and does not share resources across the set of databases.

Single database with vCore purchasing model

Why wrong: Incorrect. Like DTU, vCore-based single databases are isolated and do not allow resource pooling across databases.

Azure SQL Managed Instance

Why wrong: Incorrect. Managed Instance is intended for instance-scoped features and is not designed for pooling resources among many small databases.

Azure Cosmos DB (SQL API)

Why wrong: Azure Cosmos DB SQL API is a fully managed NoSQL database with global distribution and low latency, but it is more expensive than Azure Table Storage for a simple single-region key-value workload.

Azure Cosmos DB (Table API)

Why wrong: Azure Cosmos DB Table API provides the same Table Storage features but with global distribution and higher throughput, resulting in higher cost. It is not the most cost-effective choice here.

Azure SQL Database

Why wrong: Azure SQL Database is a relational database management system intended for structured data with complex queries. Using it for simple key-value pairs would be unnecessarily expensive and not aligned with its design.

Key-value

Why wrong: Incorrect. Key-value stores treat data as an opaque value accessed by a key. Here, the content is structured JSON with nested fields, not an opaque blob.

Column-family

Why wrong: Incorrect. Column-family databases (e.g., HBase) store data in rows with varying columns per row, but they do not natively support nested JSON objects as documents do.

Graph

Why wrong: Incorrect. Graph databases focus on storing nodes (entities) and edges (relationships). The scenario describes posts with arbitrary structure, not a graph of relationships.

ACID compliance

Why wrong: Incorrect. ACID compliance applies to individual databases but cannot be enforced across unrelated stores without custom coordination.

Idempotency

Why wrong: Incorrect. Idempotency ensures repeated operations produce the same result but does not address cross-store transactional consistency.

Schema flexibility

Why wrong: Incorrect. Schema flexibility refers to the ability to handle varying data structures, not transactional consistency.

A. Partition key: PlayerID; Consistency: Eventual

Why wrong: PlayerID is a good partition key for point lookups, but eventual consistency does not guarantee that a write is immediately visible to reads, which violates the requirement.

C. Partition key: Achievements; Consistency: Strong

Why wrong: Achievements is an array property; using it as a partition key would create many partitions with unpredictable cardinality and cannot be used for point lookups by PlayerID.

D. Partition key: Email; Consistency: Bounded staleness

Why wrong: Email might be a candidate but could lead to hot partitions if many players share email domains. Bounded staleness is not the strongest consistency level and may not meet the immediate visibility requirement.

Azure SQL Database Single Database

Why wrong: Single database provisions dedicated resources per database. With many small databases and variable loads, this would lead to unused reserved capacity and higher costs.

Azure SQL Managed Instance

Why wrong: Managed Instance is a fully managed SQL Server instance with many instance-level features but is designed for larger, more complex databases or lift-and-shift scenarios. It does not offer the same granular resource sharing as elastic pools.

SQL Server on Azure Virtual Machine

Why wrong: Running SQL Server on a VM provides the most flexibility but requires managing the OS and SQL Server. It does not natively support resource pooling across multiple tenant databases without additional configuration.

Batch processing

Why wrong: Batch processing collects data over a period and processes it later, which does not meet the requirement for immediate analysis.

Online transaction processing (OLTP)

Why wrong: OLTP is designed for high-volume transactional operations (e.g., order entry), not for real-time analytic processing of sensor data.

Data warehousing

Why wrong: Data warehousing is used for storing and analyzing historical aggregated data, not for real-time stream processing.

Azure Table Storage

Why wrong: Azure Table Storage is a cost-effective key-value store but is not globally distributed by default. Achieving global distribution would require complex application-level replication, and latency would not be as low as Cosmos DB across regions.

Azure Redis Cache

Why wrong: Azure Redis Cache is an in-memory data store, excellent for caching but not designed for durable storage. Data can be lost on node failure unless persistence is configured, and it does not natively support global distribution across regions.

Azure Blob Storage

Why wrong: Azure Blob Storage is for storing large binary or text files, not optimized for high-throughput key-value access or low-latency session management. It also lacks native global distribution for low-latency requests.

Strong

Why wrong: Strong consistency guarantees linearizability (reads always see the latest write globally), but this introduces higher latency and is more expensive. It is overkill for this requirement where only the writer needs immediate consistency.

Bounded staleness

Why wrong: Bounded staleness allows reads to lag behind writes by a configurable time interval or number of versions. While it could work, it is a stricter guarantee than needed (the application only needs session-level consistency) and may have higher latency than session consistency.

Eventual

Why wrong: Eventual consistency does not guarantee that a user will read their own write immediately. There could be a delay, so the user might not see their own post right after publishing, which breaks the requirement.

Azure Blob Storage

Why wrong: Azure Blob Storage is optimized for storing large amounts of unstructured object data, but it does not support the SMB protocol natively. The application would need to be modified to use a different access method (e.g., REST API).

Azure Queue Storage

Why wrong: Azure Queue Storage is a messaging service for storing and retrieving messages. It is not a file storage service and does not support SMB. It is used for decoupling components in distributed applications.

Azure Disk Storage

Why wrong: Azure Disk Storage provides persistent block storage for Azure Virtual Machines. It is not a shared file service and is typically attached to a single VM, not suitable for multiple application instances requiring shared access.

Atomicity

Why wrong: Incorrect. Atomicity ensures the transaction completes entirely or not at all, not that it survives failures after commit.

Consistency

Why wrong: Incorrect. Consistency ensures that the database remains in a valid state before and after the transaction, but does not address persistence after failure.

Isolation

Why wrong: Incorrect. Isolation deals with the visibility of uncommitted changes to other transactions, not with durability of committed data.

DTU-based purchasing model

Why wrong: DTU-based model bundles compute, storage, and IO into a fixed unit, preventing independent scaling of storage.

Serverless compute tier

Why wrong: Serverless is an auto-scaling compute option that pauses during inactivity; it does not isolate storage scaling from compute.

Hyperscale service tier

Why wrong: Hyperscale is a service tier that uses a vCore-based purchasing model but is designed for very large databases, not specifically for independent scaling of storage relative to compute.

Single database (DTU model)

Why wrong: Incorrect. Single databases are isolated and pay for their own resources, which would be more expensive for several small databases compared to an elastic pool.

Managed Instance

Why wrong: Incorrect. Managed Instance is designed for lift-and-shift scenarios with near 100% SQL Server compatibility; it does not offer pooled databases like elastic pools.

Database per server (hyperscale)

Why wrong: Incorrect. Hyperscale is for single databases that need massive scalability and fast recovery, not for pooling multiple databases.

B) Consistency

Why wrong: Consistency ensures data integrity constraints are maintained, but it does not guarantee that multiple steps succeed or fail together.

C) Isolation

Why wrong: Isolation controls visibility of intermediate states to other transactions, not the all-or-nothing outcome.

D) Durability

Why wrong: Durability guarantees that committed changes survive failures, but it does not enforce the rollback of partial transactions.

Cool tier

Why wrong: Incorrect. Cool tier has lower storage cost but higher retrieval cost and is intended for infrequently accessed data. Using Cool for frequent access would incur high retrieval charges.

Archive tier

Why wrong: Incorrect. Archive tier is for long-term backup and data that is rarely accessed. Retrieval can take hours, which is unsuitable for frequently accessed photos.

Premium tier

Why wrong: Incorrect. Premium tier provides low latency and high throughput but at a significantly higher cost. It is designed for high transaction workloads, not general-purpose frequent access.

Hyperscale service tier

Why wrong: Hyperscale is a service tier that provides scalable storage and compute, but it does not automatically create a read-only replica for offloading reporting queries.

Geo-replication

Why wrong: Geo-replication creates a readable secondary in a different region for disaster recovery, not primarily for offloading read workloads in the same region.

Elastic query

Why wrong: Elastic query enables querying across multiple databases, but it does not create a read-only replica; it is used for cross-database queries.

Denormalization

Why wrong: Denormalization intentionally adds redundancy to improve read performance, the opposite of what is described.

Partitioning

Why wrong: Partitioning splits tables for performance and manageability, not specifically to reduce redundancy.

Indexing

Why wrong: Indexing speeds up queries but does not affect redundancy or data integrity.

Azure Cosmos DB with SQL API

Why wrong: Incorrect. Cosmos DB can serve leaderboards but adds overhead and cost; it is not purpose-built for high-speed sorted set operations like Redis.

Azure Table storage

Why wrong: Incorrect. Table storage is key-value storage with limited querying capabilities and higher latency, not ideal for real-time leaderboards.

Azure Blob Storage

Why wrong: Incorrect. Blob storage is for large binary objects and does not support in-memory operations or sorted data structures needed for leaderboards.

Blob soft delete

Why wrong: Incorrect. Soft delete protects against accidental deletion by retaining deleted blobs for a retention period. It does not provide directory-level manipulation capabilities.

Change feed

Why wrong: Incorrect. The change feed records creation and modification events on blobs. It does not support atomic directory operations.

Immutable storage

Why wrong: Incorrect. Immutable storage prevents data from being modified or deleted for a specified period. It does not enable efficient directory operations.