Describe core data concepts practice questions

Structured table, free-text comments, audio recordings

Why wrong: Incorrect. This orders structured first, which is most structured, not least.

Free-text comments, structured table, audio recordings

Why wrong: Incorrect. This places free-text (unstructured) before structured table, but then places audio after structured, mixing order.

Audio recordings, structured table, free-text comments

Why wrong: Incorrect. This places audio (unstructured) first, then structured table, then free-text (unstructured) last, which is not from least to most structured.

Structured and Unstructured data

Why wrong: Structured and unstructured refer to data types, not workload types. The scenario describes workloads, not data formats.

Data ingestion and data transformation

Why wrong: Data ingestion and transformation are steps in a data pipeline, not classifications of the workloads themselves.

A. Structured data

Why wrong: Structured data fits a rigid, predefined schema: each entity is represented by fixed fields such as patient ID, date of birth, or diagnosis code, with strict data types and relationships enforced by tables. A doctor's narrative note is free-form prose that does not map to fixed columns or enforce uniform fields, so it cannot be directly stored as structured data without extensive parsing and data extraction. Even when a note contains recognizable components, the raw record itself lacks the row-and-column organization that defines structured data. Therefore, classifying a doctor's note as structured data is incorrect.

B. Semi-structured data

Why wrong: Semi-structured data, such as JSON, XML, or CSV files, carries self-describing organizational markers like tags, keys, or hierarchy levels that allow a flexible schema to be inferred. A physician's dictation or typed note is a continuous block of natural language with no embedded metadata, tags, or key-value pairs to delineate distinct data elements. While the note may contain sentences that could be annotated manually or programmatically, the data as stored has no implicit structure imposed by format. Thus, it does not qualify as semi-structured data.

D. Relational data

Why wrong: Relational data is a specific form of structured data that is organized into tables with rows, columns, primary keys, and foreign keys to enforce relationships and integrity. A doctor's note is a free-text narrative that cannot be decomposed into relational tuples unless its content is extracted into discrete fields like medication, dosage, and diagnosis. The raw text itself does not fit into relations and has no key-based associations or constraints; it exists as an unstructured document rather than a set of tables. Therefore, classifying it as relational data is incorrect.

Stream processing for alerts, transactional processing for trend analysis

Why wrong: Incorrect. While stream processing could handle alerts, transactional processing (OLTP) is designed for transaction-oriented workloads, not for analytical trend analysis. The proper approach for trend analysis is batch processing.

Batch processing for alerts, real-time processing for trend analysis

Why wrong: Incorrect. This reverses the pairings: alerts need real-time processing, not batch; trend analysis needs batch, not real-time.

OLAP for alerts, OLTP for trend analysis

Why wrong: Incorrect. OLAP is for analytical processing, not real-time alerts; OLTP is for transaction processing, not trend analysis.

Consistency and Partition Tolerance

Why wrong: Incorrect. The system does not prioritize consistency during partitions; it allows stale data. Consistency and Partition Tolerance (CP) would mean the system rejects writes or returns errors during a partition, but the scenario requires accepting writes.

Consistency and Availability

Why wrong: Incorrect. Consistency and Availability (CA) is not possible in the presence of a network partition. Since partitions can occur, the system cannot have both. The scenario explicitly accepts stale data, so consistency is not prioritized during partitions.

Durability and Partition Tolerance

Why wrong: Incorrect. Durability is not one of the CAP theorem properties. The CAP theorem deals with Consistency, Availability, and Partition Tolerance, not durability.

Azure Data Factory

Why wrong: Azure Data Factory orchestrates batch-oriented data movement and transformation pipelines, not real-time stream processing; it lacks native support for unbounded data ingestion or low-latency event handling. It is tempting because Data Factory can schedule recurring data loads and integrate with various sources, making it a correct choice for scheduled, incremental batch processing scenarios where near-real-time latency is acceptable.

Azure Analysis Services

Why wrong: Azure Analysis Services is not a streaming engine; it hosts tabular or multidimensional semantic models used for enterprise BI and reporting. It loads and caches data from sources at rest through partitions, allowing users to define measures, KPIs, and hierarchies for tools like Excel or Power BI, but it cannot consume unbounded event flows or perform event-by-event processing, so it cannot satisfy a real-time streaming requirement.

Azure Synapse Pipelines

Why wrong: Synapse Pipelines are an orchestration and data integration service designed for scheduled, batch-oriented tasks such as copying data, invoking notebooks, or running stored procedures. Pipelines can be triggered by a schedule or an external event, but they have no native stateful streaming capability or low-latency event-by-event execution; instead, they move finite blocks of data, making them unsuitable for true real-time processing.

Support for JSON data

Why wrong: Both services support JSON, so it does not help differentiate them. Azure Cosmos DB natively stores every item as a JSON document, making JSON a first-class data format for storage and querying. Azure SQL Database can also store JSON text in NVARCHAR columns and query it with functions like JSON_VALUE, JSON_QUERY, and OPENJSON, and even combine JSON with relational columns in the same table. Because both options support JSON—albeit differently—this factor alone cannot guide your selection, and the presence of JSON support is not a disqualifier or requirement that is unique to one service.

Support for T-SQL queries

Why wrong: T-SQL support is not a distinguishing factor because both services offer SQL-like query languages. Azure SQL Database uses native T-SQL, which is a complete relational language with JOINs, stored procedures, triggers, and aggregate functions. Azure Cosmos DB's SQL API is a subset of SQL that operates over JSON documents (e.g., SELECT * FROM c) and includes some features like JOINs only on arrays within a single item, but it lacks traditional T-SQL stored procedures and complex relational operations. Since you would have to write queries differently—and often cannot reuse the same code anyway—"support for T-SQL" is not a valid reason to choose one over the other; the queries are similar but not identical.

Azure Analysis Services

Why wrong: Azure Analysis Services is an enterprise-grade semantic modeling engine that performs online analytical processing (OLAP) and provides tabular models for business intelligence. It does not contain any data ingestion, copying, or transfer mechanisms; it simply consumes data that has already been stored in sources like Azure SQL Database or Power BI datasets. Therefore, using it to move data from on-premises to Azure is impossible because it has no connectivity or pipeline components for data movement.

Azure Synapse Serverless SQL pool

Why wrong: Azure Synapse Serverless SQL pool is a query engine that lets you run T-SQL queries directly over files in Azure Data Lake Storage or other external sources on a pay-per-query basis. It is stateless and does not persist data after query execution, nor does it have any copy or ingestion commands. Since it only reads data in place and never writes or moves data to another location, it cannot be used for data transfer from on-premises.

Azure Cosmos DB

Why wrong: Incorrect. Azure Cosmos DB is a NoSQL database that does not support complex joins natively, though it offers a SQL-like API for querying JSON documents.

Azure Table Storage

Why wrong: Incorrect. Azure Table Storage is a NoSQL key-value store without relational capabilities or support for complex joins.

Azure SQL Database

Why wrong: Azure SQL Database is a fully managed relational database service in Microsoft's PaaS model, specifically designed for structured, schema-based data. It stores data in tables with rows and columns, enforces relationships and constraints, and is queried using T-SQL. Although it offers data types like VARBINARY for binary content, it is not a general-purpose object or file storage service and therefore does not qualify as an unstructured data storage solution for this question.

Azure Table Storage

Why wrong: Azure Table Storage is a NoSQL key-value store that persists structured entities in a table format without requiring a fixed schema. It organizes data by partition key and row key and is optimized for fast, scalable access to structured, semi-structured, or even some JSON-like entity data. Because it is designed for structured NoSQL entities rather than unstructured files, text, or binary data, it is not a correct answer here.

Azure Cosmos DB

Why wrong: Azure Cosmos DB is a globally distributed, multi-model NoSQL database service that supports document, key-value, wide-column, and graph data models. It is designed for low-latency access to structured or semi-structured JSON data and provides SLA-backed performance and replication. Because it is fundamentally a transactional database engine, not an object storage service for raw unstructured files, it does not qualify as a correct answer for this question.

Azure Policy

Why wrong: Azure Policy evaluates and enforces compliance rules on Azure resources themselves—like restricting resource locations or enforcing tagging—during create or update operations. It operates at the infrastructure layer, not on file or database contents, and therefore cannot scan or classify sensitive patient records stored within those resources. This is why it is incorrect for data classification.

Microsoft Defender for Cloud

Why wrong: Microsoft Defender for Cloud is a security posture management and workload protection platform that monitors VMs, containers, and databases for vulnerabilities and suspicious activity. It provides threat detection and hardening recommendations, but it does not categorize the content of individual files or tables as patient records. Hence it addresses security monitoring, not data classification.

Azure Information Protection

Why wrong: Azure Information Protection (AIP) is the legacy client for labeling and classifying Office documents and emails, integrated with Microsoft 365 rather than enterprise data stores. Its classification and labeling functions have been consolidated into Microsoft Purview Information Protection, while Purview Data Map is the modern service that scans any source for sensitive data. Thus AIP is not the current, comprehensive solution for classifying patient records in a data catalog.

Azure Analysis Services

Why wrong: Azure Analysis Services is a PaaS offering for building tabular semantic models and running interactive BI queries using in-memory columnar storage, not a transactional database engine. It cannot accept direct OLTP connections or preserve the full T-SQL and DML capabilities required for migrating operational SQL Server databases. For an HTAP migration, you need a database that stores both transactional and analytical data, whereas Analysis Services only analyzes data already extracted and loaded from somewhere else.

Azure SQL Managed Instance

Why wrong: Azure SQL Managed Instance provides near-complete SQL Server engine compatibility for lift-and-shift of on-premises OLTP databases, but it is not designed for hybrid transactional/analytical processing (HTAP). Running heavy analytical queries against the same operational copy can cause performance contention, and you would typically need to add a separate analytics layer or replicas. Since the workload demands unified transaction and analytics support, Managed Instance's OLTP-first architecture makes it the wrong service here.

Azure Data Factory

Why wrong: Azure Data Factory is a cloud ETL and data integration service used to create pipelines that copy and transform data between various sources and destinations. It is not a relational database engine and cannot store transactional data or execute SQL workloads itself, so it cannot be the target of an SQL Server database migration. Choosing Data Factory would confuse the data movement/transformation plane with the data storage and query engine plane, making it incorrect for this scenario.

Enforcing referential integrity

Why wrong: Referential integrity is implemented in relational databases through foreign key constraints that guarantee a child record always points to an existing parent record. NoSQL databases typically avoid such constraints because they favor denormalized data models, where related data is embedded or duplicated rather than referenced. In a distributed system, enforcing foreign keys across partitions would require expensive coordination and would undermine availability and write performance. Therefore, this is a capability of relational systems, not a benefit of NoSQL.

Support for complex joins

Why wrong: Relational databases use SQL joins to combine data across multiple tables on the fly, which is a fundamental query pattern that NoSQL databases do not natively support for complex, ad-hoc relationships. Instead, NoSQL models are designed around pre-joined or denormalized storage, such as embedding related documents within a single record, to avoid the need for server-side joins. When joins are needed in NoSQL, they must be implemented in application code or performed through limited, client-side queries, which is inefficient at scale. Hence, complex joins are a relational strength, not a NoSQL benefit.

Full ACID transactions across multiple documents

Why wrong: Full ACID transactions across multiple documents are not a general benefit of NoSQL; many NoSQL databases relax cross-document atomicity and isolation in favor of eventual consistency and partition tolerance. While some NoSQL systems, including Azure Cosmos DB, now offer multi-item transactions within a single logical partition, these transactions are limited in scope and do not span arbitrary partitions or collections as easily as relational transactions. Relational databases enforce strict ACID guarantees globally, including serializable isolation and rollback across tables, which NoSQL often sacrifices. Therefore, this option describes a feature that is more characteristic of relational platforms, not a core NoSQL benefit.

Enforced referential integrity constraints

Why wrong: Columnar storage is a physical file layout for tables, not a set of logical rules; referential integrity constraints are enforced by a relational database management system using primary keys and foreign keys. Because formats like Parquet or ORC fundamentally lack an engine to validate relationships between tables, they cannot enforce referential integrity on their own. This option confuses a database server's data-governance capability with the on-disk organization of data, so it is not a benefit of columnar storage.

Optimized for frequent row updates

Why wrong: A columnar format scatters the fields of a logical row across many different files or row-group sections, so updating one row means locating and rewriting data in every column involved. Most columnar files are also immutable, forcing a full rewrite of affected row groups or an append with a delete marker, which makes frequent transactional row updates costly. Row-oriented databases are designed for fast single-row changes, whereas columnar storage is optimized for bulk appends and scan-heavy workloads.

Azure Synapse Analytics dedicated SQL pool

Why wrong: Azure Synapse Analytics dedicated SQL pool is a distributed analytics database designed for large-scale data warehousing and big-data workloads, not a typical transactional PaaS service. Because you provision and manage a dedicated cluster of compute nodes, processing capacity (Data Warehouse Units), and scaling behavior, it behaves more like an IaaS-managed infrastructure component. While it offers a raw SQL interface, its primary purpose is analytical processing rather than serving as a fully automated, managed relational database platform.

Azure Data Lake Storage Gen2

Why wrong: Azure Data Lake Storage Gen2 is an enterprise-grade object storage service built on Azure Blob Storage that adds a hierarchical namespace, providing file system semantics. It is categorized as IaaS-level storage because you are responsible for creating the storage account, configuring access, managing redundancy tiers, and designing folder/partition layouts. The platform does not provide managed database features such as automated patching, high-availability failover, or built-in transactional guarantees, so it clearly is not a fully managed PaaS database service.

Azure Virtual Machines with SQL Server

Why wrong: Azure Virtual Machines with SQL Server places the SQL Server instance on a virtual machine that you fully own and must operate. This is Infrastructure as a Service (IaaS) because you control the guest OS, apply SQL Server cumulative updates, handle backup strategies, and manage availability sets or zones. The provider supplies only the compute, storage, and networking, leaving all database administration and the software lifecycle to you, so it is not considered a managed PaaS data service.

Azure Blob Storage

Why wrong: Azure Blob Storage is an object storage service for unstructured data such as text, binaries, and flat files. It provides durable, tiered storage and lifecycle policies, but it has no built-in compute engine to apply transformations to the data it holds. To transform blobs, you must attach a separate service such as Azure Databricks or Azure Data Factory to read, process, and write results back.

Azure SQL Database

Why wrong: Azure SQL Database is a fully managed relational database engine focused on transactional (OLTP) workloads. While you can write T-SQL stored procedures or views to manipulate data within the database, it is not designed as a broad data transformation pipeline for heterogenous sources. Its scaling model is based on database compute and storage, not on parallel, distributed data-flow execution across file stores and data lakes.

Azure Event Hubs

Why wrong: Azure Event Hubs is a real-time data ingestion and event streaming service that can handle millions of events per second. It acts as a buffer or broker for telemetry and streaming data, but it does not include a transformation engine. Downstream consumers—such as Azure Stream Analytics, Databricks, or Functions—must perform any enrichment or reshaping of the event data.

Cost per GB

Why wrong: Although storage pricing always matters operationally, cost per GB is not a differentiator between Cosmos DB and SQL Database because their pricing models are built around completely different units—request units (RU/s) versus DTUs or vCores—and actual cost depends on throughput, indexing, and read/write patterns. The same GB can be cheap or expensive depending on workload, so it's not a fixed factor that helps you choose between the two. You would instead analyze total cost of ownership for a specific use case.

Maximum data size

Why wrong: Neither Azure SQL Database nor Cosmos DB imposes a practically limiting maximum data size that would drive the choice; SQL Database scale tiers and Hyperscale go into the multi-terabyte range, and Cosmos DB automatically partitions your data with no need to shard manually. Both services scale out horizontally on the platform side, so max data size is a capacity-planning concern rather than a fundamental architecture decision. The far more important question is whether the data model and access patterns fit NoSQL or relational.

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.

50 GB

Why wrong: 50 GB is incorrect because the template's byte value (5,368,709,120) does not correspond to 50 GB (which would be 53,687,091,200 bytes). A 50 GB size is a larger DTU-tier allocation (e.g., Standard S1/S2) and would require a distinct maxSize property value in the ARM template. Since the template explicitly lists a 5 GB value, 50 GB is a miscalculation or misreading of the tier.

5 MB

Why wrong: 5 MB is incorrect because 5 MB equals only 5,242,880 bytes, which is over 1,000 times smaller than the 5 GB value in the template, and it falls below the minimum supported storage size for any Azure SQL Database tier. Azure SQL Database does not offer 5 MB as a valid max size; even the smallest Basic tier starts at 5 GB. Therefore, this option misinterprets the GB-to-MB conversion and is not the value in the ARM template.

500 GB

Why wrong: 500 GB is incorrect because the template shows 5,368,709,120 bytes, which is exactly 5 GB, not 500 GB (536,870,912,000 bytes). A 500 GB max size would correspond to Premium, Business Critical, or large Hyperscale tiers, which are much larger than the template's specified setting. The error likely comes from misreading a decimal place or confusing the storage limit of a different service tier.

Azure Front Door

Why wrong: Azure Front Door is a global load balancer but it operates at the HTTP/HTTPS layer and does not integrate with Cosmos DB to route database reads to the nearest region based on the SDK's region awareness. Cosmos DB's native multi-region capabilities are more direct.

Azure Content Delivery Network

Why wrong: Azure Content Delivery Network (CDN) caches static content at edge locations, but it is not designed for dynamic database queries against Cosmos DB. It cannot serve live database reads with consistency guarantees.

Microsoft Traffic Manager

Why wrong: Microsoft Traffic Manager is a DNS-based traffic routing service that can direct traffic to different endpoints, but it does not provide the low-latency SDK-integrated region awareness that Cosmos DB's built-in multi-region support offers.