Microsoft Azure Data Fundamentals DP-900 (DP-900) — Questions 751–820

Structured data is inherently organised with a predefined schema, which mandates its storage in a highly organised format. This characteristic directly aligns with being stored in rows and columns, a hallmark of relational databases. Each row represents a unique record, while columns define specific attributes or fields, ensuring data consistency and enabling efficient querying and analysis. This precise tabular structure is a defining feature of structured data, satisfying the requirement for its organised nature.

Always Encrypted encrypts selected columns client-side using a column encryption key protected by a column master key held outside Azure SQL Database. The database engine stores and processes only ciphertext, so sensitive data is never exposed to SQL Server administrators or to Azure personnel. With deterministic encryption the server can support equality operations (e.g., WHERE clauses and joins) while randomized encryption avoids leaks. The client application and driver must be compatible, and the application must supply the keys.

A nonclustered index on (DepartmentID, LastName) INCLUDE (Salary) is a covering index specifically designed for this query: it contains every column needed in the WHERE, SELECT, and ORDER BY clauses within the index leaf level. Because the index is sorted by DepartmentID first and LastName second, the SQL Server query optimizer can perform an index seek for the specific department, then navigate to the exact last names, and retrieve Salary directly from the included column. This completely avoids the need for expensive key lookups into the clustered index, making it the most efficient access path for this workload.

Azure Data Factory is the intended ETL/ELT orchestration service for this workload. A scheduled trigger can launch a copy activity that uses the dedication SQL pool's COPY/PolyBase path for high-throughput ingestion of 500 GB, while the Azure Integration Runtime scales to handle large data volumes and provides built-in retry, monitoring, and custom error handling.

Azure Data Factory is correct because it represents linked services, datasets, and pipelines as JSON objects. The exhibit shows a linked service definition with a type and typeProperties containing connection details, which is the standard way ADF stores source and destination connection information. This serialized JSON enables version-controlled, repeatable deployment of data integration artifacts.

Azure SQL Database is a fully managed relational database service that enforces a fixed schema and guarantees ACID transactions. For e-commerce order data, this means foreign keys can maintain referential integrity between orders, customers, and line items, while row-level locking supports high concurrency without lost updates. Its built-in indexing and transaction logging make it the appropriate choice for transactional workloads where consistency is non-negotiable.

The WHERE clause filters only on timestamp and temperature, not on deviceId—the container's partition key. To satisfy the query, Cosmos DB must issue the query to every physical partition and merge results, a pattern called a cross-partition query or fan-out, which consumes significantly more request units and has higher latency than a query scoped to a single partition key. Adding deviceId to the filter, or redesigning the model to avoid needing to scan all partitions, aligns the query with the partition-key distribution and reduces scanning.

Azure SQL Database is a fully managed relational database engine built on SQL Server, offering T-SQL, enforced schemas, relationships via primary and foreign keys, and rich querying with joins. It also includes built-in graph table features (node and edge tables) that allow you to model many-to-many relationships, and JSON query support for semi-structured data. This makes it the most appropriate service when you need a relational store that can also handle graph-style relationships and flexible data formats.

Azure Stream Analytics is a fully managed, purpose-built stream-processing service that handles near real-time IoT telemetry with low latency. It provides a SQL-like query language that natively supports temporal windows, sliding windows, and event-time processing, allowing filters, aggregations, and even anomaly detection directly on the stream. Crucially, it has a native Cosmos DB sink and built-in connectors to Event Hubs, IoT Hub, and other Azure services, eliminating the need for custom glue code. Because it processes each event as it arrives rather than in micro-batches, it is the ideal choice for real-time IoT scenarios that require prompt alerts or continuous output.

Azure Blob Storage is Microsoft's object storage solution optimized for massive volumes of unstructured binary data, perfect for high-resolution photos. Each block blob can hold up to 4.75 TB and you can store millions of blobs, each directly addressable via a unique HTTPS URL—making it trivial to serve images to browsers or mobile apps and integrate with Azure CDN for global scalability. It also offers tiered storage (hot, cool, archive) to balance cost and access frequency, which is essential for media companies.

Structured data has a fixed, predefined schema: every row in this POS dataset contains the same columns (TransactionID, ProductID, Quantity, Price) with consistent data types, allowing direct querying with SQL. This tabular format—organized into rows and columns with strict formatting—is the defining characteristic of structured data. Because the schema is known ahead of time and every record conforms to it, this dataset clearly fits the structured data classification.

Correct. Relational tables have a fixed schema (structured), JSON documents allow varying fields (semi-structured), and image files lack a predefined schema (unstructured).

Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent, linked servers, and other instance-scoped features, all in a fully managed platform. It supports lift-and-shift migrations without rearchitecting applications, and its built-in high availability and patching make it ideal for production workloads that rely on these advanced capabilities.

Azure Databricks is a unified analytics platform built on Apache Spark. While it supports both batch and real-time streaming, its core capability for distributed in-memory data processing makes it a primary choice for big data batch workloads such as large-scale ETL, data transformation, and machine learning over historical data. It manages clusters automatically and provides Databricks File System (DBFS) and Delta Lake for reliable, high-throughput batch jobs.

Azure Cosmos DB is a globally distributed, multi-model database that natively supports JSON documents with low-latency reads and writes.

Azure Data Lake Storage Gen2 is a purpose-built data lake that combines Blob Storage's low-cost object storage with a hierarchical namespace, enabling efficient directory-level operations and POSIX-compliant access control. It supports schema-on-read, so raw data in any format (JSON, CSV, Parquet, etc.) can be ingested without transformation, and it natively integrates with Azure Synapse, Databricks, and Data Factory for analytics workloads.

Correct. This index matches the filter columns in the correct order and includes the ORDER BY direction. Included columns cover additional columns needed, making it a covering index for many queries.

Enabling the hierarchical namespace (HNS) on a storage account is exactly what turns on Azure Data Lake Storage Gen2. The HNS reorganizes blob objects into a directory hierarchy, enabling POSIX-like access control lists and efficient rename/move operations that are foundational to the Data Lake Gen2 offering.

Customer records in a relational table are strictly structured (fixed schema), product reviews as JSON are semi-structured (schema-on-read), and marketing images are unstructured (binary files). This is the correct order from most to least structured.

As the table's primary storage structure, a clustered columnstore index organizes data column-wise, so an aggregation query reads only the needed column segments. This design delivers high compression and batch-mode processing, which drastically reduces I/O and CPU for full-table scans and GROUP BY operations on large fact tables. It is the default recommended indexing strategy for analytical and data warehousing workloads in Azure SQL Database.

Azure Cosmos DB is a globally distributed, multi-model database service with native support for JSON documents and automatic indexing of every property. Its index engine allows filtering, ordering, and joining on any field without pre-defining schemas or secondary indexes, ensuring low-latency queries even at scale. With tunable consistency levels and a SQL API, it is the ideal choice for a collection of JSON documents that require flexible querying and indexing on multiple fields.

A planned failover is the only supported way to reverse geo-replication roles without data loss. It synchronizes the secondary to the primary's latest committed transaction, then promotes the secondary to primary, and automatically re-establishes the replication link in the opposite direction. This is the correct failback procedure when the original region is healthy, because it preserves transactional consistency and avoids re-seeding.

Archive Blob Storage is the correct choice because it offers the lowest storage cost of any Azure Blob access tier, which aligns with the archival requirement. Data is stored offline without instant access, but the service supports rehydration within up to 15 hours—well within the stated 24-hour retrieval window. The latency is acceptable here because the workload prioritizes cost minimization over immediate availability.

Azure Cosmos DB is the correct choice because the Azure Mobile Apps SDK provides first-class offline data sync for Cosmos DB as the backend. The SDK embeds a local SQLite cache on the device, tracks all pending create, update, and delete operations, and automatically reconciles them with the cloud when connectivity returns. It also supports multi-master writes with conflict-resolution policies, such as last-write-wins or custom resolution, making it uniquely suited for a mobile app that must function offline and re-sync later.

Azure Synapse Analytics is the correct choice because it unifies real-time stream ingestion and historical data analytics in one platform. It provides a SQL pool (dedicated or serverless) that runs standard T-SQL queries against both live streaming data (ingested via Event Hubs) and data lake files like Parquet or Delta, enabling ad-hoc reporting on the combined dataset. Synapse also natively integrates with Power BI, so the delivery-fleet dashboard can be built directly from the same query engine. Hence, it satisfies the requirements for real-time dashboards, T-SQL ad-hoc queries, and historical data access without additional services.

A contained database user mapped to a Microsoft Entra ID identity is created with CREATE USER [user] FROM EXTERNAL PROVIDER. This provisions a database-level principal that is directly tied to a user or group in Microsoft Entra ID, enabling authentication with an Entra ID access token. This is the required approach for using Microsoft Entra ID authentication with Azure SQL Database.

Replication stores a full copy of the Product dimension table on every distribution in the dedicated SQL pool. When the fact table joins Product on ProductID, each distribution can perform the join locally using its own copy, eliminating all data movement and shuffle across distributions. This is the recommended approach for small-to-medium dimension tables in a star schema and directly resolves the join performance problem described.

Azure Synapse Serverless SQL pool is a serverless, on-demand T-SQL query engine built for directly reading data from Azure Data Lake Storage (ADLS Gen2) and Blob Storage. It uses OPENROWSET with AUTO_TYPE detection to query Parquet, CSV, Delta, and JSON files in place, with no data movement and no provisioning — you are billed only for bytes scanned. Its ability to create external tables and metadata over lake files makes it the right fit for a centralized analytics platform that must query the lake with standard SQL.

Azure Files is the correct choice because it provides fully managed, cloud-native file shares accessible via the SMB protocol, which supports mandatory file and byte-range locking. This enables multiple VMs or processes to safely read from and write to the same large file concurrently without application-level coordination. Azure Files handles the underlying storage, offering high availability and low latency, while the SMB locking semantics ensure that writes are serialized appropriately. This directly satisfies the requirement for concurrent access to a single large binary.

OLTP and OLAP are the two standard workload categories in data processing. OLTP systems handle high-volume, low-latency transactional operations such as order entry and inventory updates, emphasizing ACID guarantees and row-level integrity. OLAP systems support analytical queries that aggregate and summarize large historical datasets, often using columnar storage and multidimensional schemas for business intelligence. Together they capture the fundamental divide between running day-to-day operations and analyzing those operations afterward.

Because older MRI scans are rarely accessed but must be instantly retrievable during follow-ups or legal review, the Cool tier is the optimal trade-off. Cool charges lower per-gigabyte storage fees than Hot while preserving zero-latency reads, so you avoid the high cost of Hot without accepting the multi-hour rehydration delay of Archive. This matches the requirement to minimize cost while maintaining immediate availability.

Azure SQL Managed Instance is the correct PaaS choice because it provides near-complete compatibility with on-premises SQL Server, including SQL Server Agent jobs, linked servers, Service Broker, and cross-database queries. These instance-scoped features are preserved while Azure automatically handles patching, backups, and high availability, making it a fully managed lift-and-shift target with minimal application changes.

Eventual consistency is the default in Cosmos DB for multi-region writes and offers the lowest latency and highest availability because replicas converge asynchronously without waiting for quorum. For mobile user preferences, the profile is typically read frequently and written occasionally; if a user updates a preference and reads it a moment later, eventual might show the old value briefly, but it will converge quickly. Because user preferences are non-critical and tolerate a brief stale read, eventual provides the optimal balance of performance and cost, making it the correct choice.

In Azure Synapse Analytics, a dedicated SQL pool uses massively parallel processing (MPP) across distributed compute nodes and defaults to columnstore indexes, which compress data and scan only relevant columns for aggregations. This architecture is purpose-built for complex, read-intensive analytical queries over large relational datasets, delivering far faster response times than a traditional transaction-optimized OLTP database.

Azure Blob Storage is the correct option because it is a durable, massively scalable object storage service that can hold event data from any number of sources in its native format, such as JSON, Avro, or CSV, without requiring a predefined schema. It supports schema-on-read, meaning the structure can be inferred or defined later during analysis, and it integrates with Azure Data Lake Gen2 for big data workloads. Storing raw events in Blob Storage also preserves them for long-term retention, auditing, and reprocessing, which is exactly what this scenario requires.

Azure SQL Database is a PaaS offering that is built on the SQL Server engine, so it is directly compatible with SQL Server features like T-SQL, stored procedures, and transparent data encryption. It provides built-in high availability with a 99.99% SLA, automated backups, patching, and monitoring, removing the need for manual infrastructure management. This matches the requirement for a relational database in Azure that is both managed and SQL Server-compatible.

Azure Databricks is an Apache Spark-based analytics platform that offers collaborative notebooks, autoscaling clusters, and supports Python, Scala, SQL, and R. It allows data engineers to read CSV files from Azure Data Lake Storage or Blob storage, perform complex transformations using DataFrames or SQL, and write results back — exactly the code-first, scalable batch processing required for a global e-commerce workload. Its serverless option removes infrastructure management while providing the flexibility to write custom transformation logic in Python, making it the ideal choice.

Supports real-time data ingestion at sub-second intervals.

The Table API is designed for key-value storage with simple queries by partition key and row key, providing low-latency access at global scale. It is ideal for this type of high-throughput, simple data access pattern.

A core benefit of a data lake is its ability to ingest data in its original, raw form without requiring pre-defined schemas or transformation. Whether the data is CSV, JSON, Parquet, Avro, images, or video, the data lake stores it exactly as it arrives, preserving granular detail for future analysis. This schema-on-read approach allows data engineers and scientists to define and apply structures when needed, enabling agile exploration and preventing the loss of potentially valuable raw information.

Power Query is Microsoft's data connection and transformation engine that supports hundreds of data sources, including databases, files, and web services. Its query editor allows merging tables (like SQL JOINs) and appending rows (like UNIONs) to combine sources into a single dataset before loading into the data model. This makes it the precise tool for the analyst's requirement to bring multiple data sources together for a report.

Azure Synapse Analytics is the analytical serving engine of a modern data warehouse, providing dedicated SQL pools for massive parallel processing and serverless SQL endpoints for on-demand querying. It unifies data warehousing with big data analytics via Apache Spark, making it the place where curated data is structured into tabular models for high-performance relational queries. Without a purpose-built query engine like this, the lake alone cannot deliver fast, consistent relational performance.

Azure Stream Analytics is a fully managed real-time analytics service designed specifically for stream processing. It ingests high-throughput data from sources like IoT Hub or Event Hubs, applies SQL-like queries with built-in windowing functions (tumbling, hopping, sliding, session), and can perform aggregations, filtering, and alerting with sub-second latency. Its output sinks include Azure Data Lake, Synapse Analytics, and Power BI, making it the ideal choice for real-time IoT telemetry processing without managing infrastructure.

In the serverless compute tier of Azure SQL Database, compute capacity automatically scales between a configurable minimum and maximum vCore range and can pause the database entirely after a user-defined period of inactivity. While running, billing is per second for the vCores actually consumed, and while paused no compute charges accrue, although storage and backups continue to incur cost. That makes it the best choice for startup traffic patterns, because an idle database can stop incurring compute costs and resume automatically when a request arrives.

Azure Cache for Redis is the correct choice because Redis natively supports sorted sets, a data structure perfect for leaderboards. Commands like ZADD and ZINCRBY update scores in O(log N) time, and ZREVRANGE retrieves the top scores in O(log N+M), all while data is held in RAM for sub-millisecond latency. This purpose-built in-memory design handles thousands of concurrent player updates and queries per second, making it the standard solution for real-time gaming leaderboards. Although Redis persistence is optional and typically not the primary concern, it can be configured to maintain data across restarts if needed.

The Hot tier optimizes the initial 30 days when end users actively upload and retrieve video files; its storage cost is higher but it has no retrieval penalty, making frequent data movement inexpensive. A lifecycle management policy then automatically transitions each blob to Cool after 30 days because Cool reduces storage charges for the 180-day period of rare access, despite a small per-GB retrieval cost. After 210 days, the policy moves the blob to Archive, which provides the lowest storage cost for the compliance period when files are never accessed but must be retained. This staged approach exactly aligns costs with the actual access pattern while avoiding manual intervention and the risk of premature transitions.

Normalization decomposes the unnormalized enrollment table into Student, Course, and Enrollment relations, moving StudentName into Students and CourseName into Courses so each value is stored only once. The Enrollments table then holds only foreign keys (StudentID, CourseID) plus enrollment-specific attributes, eliminating the partial dependencies on composite keys and the transitive dependency of CourseName on CourseID. This design enforces referential integrity via foreign key constraints, preventing orphaned records and reducing update anomalies to a single-row change.

Schema-on-read applies a logical structure to data only when it is accessed by a query engine, such as Azure Synapse Serverless SQL, Spark, or a metastore catalog that overlays schema metadata on raw files. Raw log files can be stored as-is in a data lake in open formats like JSON, CSV, or Parquet, and the schema is interpreted or inferred at read time. This is the correct answer because it matches the data engineer's workflow of loading raw files into storage without imposing structure until analysis.

When a blob is uploaded without an explicit access tier, Azure applies the default access tier of its parent container. Here the container's default tier is Cool, so the blob inherits Cool regardless of the storage account's own tier setting. This inheritance ensures consistent billing for all blobs in the container that do not have a per-blob tier override. The blob's effective tier is Cool, making this answer correct.

Querying with both PartitionKey and RowKey forms a point query that uniquely identifies a single entity, since their combination is the primary key of the table. The PartitionKey narrows the search to a specific partition, and the RowKey directly locates the entity within that partition's index. This is the most efficient, lowest-latency, and least costly operation Azure Table Storage supports for data retrieval.

Azure Machine Learning is the correct choice because it is Microsoft's dedicated cloud service for the complete machine learning lifecycle. It provides managed notebooks for training, integrated experiment tracking with metrics and parameters, a central model registry, and one-click deployment to compute targets. This end-to-end support makes it specifically designed for data scientists to train, track, and operationalize models in a production context.

Eventual consistency is the weakest consistency level, prioritizing availability and low latency. It guarantees that if no new writes are made, all replicas will converge to the same state over time. This aligns with the platform's design goals.

OLTP (Online Transaction Processing) is the correct workload because order placement involves multiple concurrent, short-duration transactions—inserting the order, adjusting inventory, and recording payment—that must each be executed atomically and with ACID guarantees. These systems use row-based, normalized storage to provide fast writes, strict data integrity, and very low response times even under heavy user concurrency. This directly matches the operational need for immediate, reliable processing of each customer action.

By using a date as the PartitionKey, all videos uploaded on the same date are stored in the same partition. A query filtering by date can then fetch all rows from that single partition using the PartitionKey, which is extremely fast and cost-efficient.

Azure Stream Analytics is a fully managed stream-processing service that queries live data using a SQL-like language without requiring custom code. It reads from high-throughput sources such as Event Hubs or IoT Hub, applies temporal windows, filters, joins, and aggregates, and writes results to Power BI, Azure SQL, Cosmos DB, or Data Lake Storage. Its declarative model and built-in time handling make it the natural choice for low-latency social media analytics, letting you continuously compute metrics like mentions, sentiment, or trending hashtags in near-real time.

Correct. Azure Synapse Analytics unifies data ingestion, processing, and analytics, supporting both streaming (via Event Hubs integration) and batch (via PolyBase or serverless SQL pool to query data lake directly). It provides near real-time and historical analytics capabilities.

Azure Synapse Analytics is the purpose-built cloud data warehouse service that uses a massively parallel processing (MPP) engine across multiple compute nodes, automatically distributing tables and using clustered columnstore indexes for high compression and scan performance. It provides full T-SQL support, PolyBase connectors to Azure Data Lake Storage Gen2 and other sources, and integrations with Azure Data Factory and Synapse Pipelines for end-to-end data movement. Synapse Link also enables real-time analytics on operational data, making it the closest technical equivalent to replacing an on-premises SQL Server data warehouse.

The Filter transformation in Azure Data Factory mapping data flows is the row-level predicate operation that keeps only rows satisfying a specified condition. By setting the condition to something like year(OrderDate) == year(currentDate()) or to_date(OrderDate) >= '2025-01-01', you can restrict the dataset to the current year. This transformation is purpose-built for row selection and does not alter the schema or group data, making it the correct choice for this requirement.

Azure Cosmos DB with the SQL API is the correct choice because it is a schema-agnostic document database that natively stores JSON, automatically indexes every property for efficient point reads and SQL-style queries, and scales horizontally with guaranteed single-digit-millisecond latency and throughput managed in request units. Unlike relational databases, it does not require a fixed schema and is designed for high write and read throughput on document workloads. The SQL API also supports rich queries over nested JSON fields, making it ideal for patient records with varying structures that require fast, interactive access.

Azure Synapse Serverless SQL pool enables serverless querying of data stored in Azure Data Lake Storage (Parquet, CSV, etc.) without needing to load data into a separate store. It scales automatically and charges per query.

GameID is the filter in the dominant query, so it is the natural partition key. Cosmos DB places all items with the same GameID in the same logical partition, allowing a query such as 'SELECT * FROM scores s WHERE s.GameID = @gameId' to target a single partition and complete efficiently. Provided the number of games is large enough to avoid a single game exceeding the 10 GB logical partition limit, GameID gives good distribution and aligns perfectly with the workload.

The Hyperscale service tier is built on a distributed architecture with separate compute nodes and page servers, allowing compute to be scaled up to 100 vCores in seconds with no downtime. Because e-commerce demand spikes are often sudden and unpredictable, Hyperscale lets you add compute power—and optionally additional read replicas—dynamically without re-provisioning storage, making it the only listed option that directly and rapidly increases write-transaction capacity.

The serverless compute tier for Azure SQL Database is engineered precisely for unpredictable workloads: it automatically scales the compute resources between a configured minimum and maximum vCores based on actual demand, and it can pause the database entirely during prolonged inactivity while continuing to store data. This tier bills per-second for the compute actually used, so a sudden surge in write or read activity triggers immediate scale-up without manual intervention, then scales back down when demand subsides. This is the only listed option that directly provides automatic, demand-driven compute scaling for the primary workload.

Azure Data Factory with mapping data flows is serverless, automatically scales, and charges per second. It natively supports complex data transformations and can read from and write to Azure Data Lake Storage Gen2 as Parquet.

Lambda architecture is correct because it deliberately combines a batch layer for accurate, comprehensive historical processing and a speed layer for real-time stream processing over the same sensor data. The batch layer computes precise trends and baseline models from all collected data, while the speed layer provides low-latency anomaly detection and feeds both results into a serving layer for unified querying. This design satisfies both the real-time alerting and historical analysis requirements, with the tradeoff of maintaining two separate code paths.

Workload management is the correct choice because it directly governs how compute resources are allocated across queries in services like Azure Synapse Analytics dedicated SQL pools. Workload groups and classifiers let you assign CPU, memory, and concurrency slots to different workloads, so critical analytical queries get predictable performance even when the system is under heavy load. This is resource governance, not just a performance optimization.

Correct. The system must accept writes during partitions (Partition Tolerance) and ensure every request gets a response (Availability), even if read inconsistency occurs.

JSON documents consist of key-value pairs, nested objects, and arrays, but each waybill may have a different set of fields—some optional, some nested. This self-describing format provides inherent organization through keys and hierarchical structure, yet it does not enforce a rigid, predefined schema. That combination of organizational properties without a fixed tabular schema is the defining characteristic of semi-structured data, which is why this is the correct classification.

Azure Synapse Analytics Spark pools are the correct choice because they provide a managed, distributed Apache Spark compute engine that can execute arbitrary batch ETL code written in Python, Scala, or SQL. These pools read and write directly from Azure Data Lake Storage Gen2 with optimized in-memory processing, and serverless pools offer per-second billing and automatic pausing, which is ideal for intermittent batch workloads. This is the actual compute environment needed for Spark-based transformations, not merely an orchestration or streaming service.