Microsoft Azure Data Fundamentals DP-900 (DP-900) — Questions 601–675

Scaling requires the `--service-objective` parameter to carry the new tier value; without it, Azure CLI leaves the existing S2 objective unchanged, so no scaling occurs. The command satisfied the resource group and server constraints but omitted the property that defines the target performance level, leaving the database at S2.

Azure SQL Managed Instance is correct because it provides near-complete SQL Server instance compatibility, including SQL Server Agent for scheduled maintenance, instance-level features, and cross-database queries, all within a fully managed PaaS environment. It supports auto-failover groups to deliver automated disaster recovery without the manual configuration required by infrastructure-as-a-service deployments. This makes it the lowest-effort managed option for modernizing a legacy order management system with minimal application changes.

The Table API is built for key-value workloads and stores data as items with a partition key and row key. It allows efficient point reads and range queries, making it ideal for IoT sensor data.

NoSQL databases use a flexible, schema-less data model where each document or item can have its own set of attributes, and fields can be added, removed, or changed without running ALTER TABLE migrations or coordinating schema changes across the team. This flexibility supports evolving data shapes, rapid development cycles, and storing heterogeneous records in the same container, which is especially useful for IoT telemetry, user profiles, and content feeds. Because the database does not enforce a uniform structure, application code governs shape and validation.

Azure SQL Database elastic query runs T-SQL across multiple Azure SQL Databases through an external data source, returning one combined result set. It directly satisfies the stem's single-query constraint without copying data into a separate analytics store, unlike Synapse Analytics or Data Factory pipelines.

This policy correctly matches the access pattern: Hot tier for frequent initial access, Cool for infrequent intermediate access (still retained for 2 years but accessed rarely), and Archive for long-term compliance retention where data is rarely accessed and retrieval latency is acceptable.

Azure Blob Storage is Microsoft's object storage service, ideal for storing massive amounts of unstructured and semi-structured data as blobs. It accepts any file type—JSON, Parquet, CSV, Avro—without a predefined schema, making it a correct choice for semi-structured data. Blob Storage provides tiered storage, lifecycle management, and high durability, and it serves as the foundation for Azure Data Lake Storage Gen2's hierarchical namespace.

Azure Stream Analytics is built specifically for real-time stream processing over sources such as Azure Event Hubs and IoT Hub. It continuously consumes telemetry events and executes a declarative SQL-based query engine that can apply tumbling, hopping, or sliding windows to detect patterns like speeding while joining live data with reference data from Azure SQL Database. Its low-latency, in-memory processing and native outputs to alerts, Azure Functions, or Power BI make it the natural fit for this scenario.

Archive tier is the most cost-effective storage tier in Azure Blob Storage, designed specifically for long-term retention of data that is rarely accessed. For terabytes of historical log data, it offers the lowest storage price per GB, despite requiring manual rehydration (taking up to 15 hours) to retrieve. This aligns perfectly with the scenario's archival requirements, making it the correct choice.

Azure Data Factory provides mapping data flows, a visual designer for building data transformations at scale. It integrates with on-premises data via self-hosted integration runtime, supports scheduling, and requires no code, making it the ideal choice.

This T-SQL statement creates an external table in Azure Synapse Analytics (dedicated SQL pool). The EXTERNAL keyword, combined with LOCATION pointing to an ADLS Gen2 path and a FILE_FORMAT specifying PARQUET, defines metadata that allows T-SQL queries to read the Parquet files directly in place. No data is copied into the database; the table is a read-only schema abstraction over the files.

A Power BI dataset—specifically a streaming or push dataset—is the correct target because Azure Stream Analytics includes a native output connector that pushes rows to Power BI in near-real time. Power BI then updates tile visualizations automatically without manual refresh or intermediate storage, and the dataset's in-memory analytics engine is optimized for interactive slicing, filtering, and drill-down. This direct path minimizes latency and is purpose-built for live dashboards fed by streaming clickstream data.

Append blobs are an Azure Blob Storage variant purpose-built for high-frequency append operations: each append writes a new block at the end without modifying existing data, making them ideal for shipment tracking logs. They provide low-cost, immutable storage, and using Azure Data Lake Storage Gen2 or serverless SQL, you can run queries over the entire blob to reconstruct the full route history. Unlike the other options, append blobs give you native append semantics, no per-event write cost beyond storage, and direct integration with analytics tools.

Horizontal scalability is a core design goal of NoSQL databases, which distribute data and query load across many commodity servers using sharding and consistent hashing. Unlike a single relational server that must be upgraded vertically (scale-up), NoSQL systems can add more nodes dynamically to handle increased traffic and data volume (scale-out). Azure Cosmos DB, for example, automatically splits partitions based on partition keys and can elastically scale throughput and storage across regions. This makes horizontal scalability a primary advantage for global, high-velocity applications.

This scenario perfectly fits batch processing because the daily sales data from all stores is accumulated over a fixed period and then processed as a single, scheduled bulk job. Batch jobs such as nightly ETL pipelines in Azure Data Factory or scheduled Spark jobs in Azure Databricks ingest a finite, predefined dataset and transform it in one go, making it ideal for periodic reporting and analytics.

Read scale-out uses the built-in readable secondary replica of Azure SQL Database, which resides in the same region as the primary and is kept transactionally consistent through asynchronous replication. By setting the connection string's ApplicationIntent parameter to ReadOnly, read-only queries are automatically routed to this secondary, offloading CPU, IO, and memory pressure from the primary without requiring schema changes or application rewrites. This directly satisfies the scenario's requirements of isolating read workloads in the same region while leaving the existing database schema untouched.

Active Geo-Replication creates readable secondaries in other Azure regions, letting each region serve reads locally and cutting latency. Writes still go to the primary in West US, keeping write latency low, and it needs no application changes beyond connection strings.

Read scale-out is the correct feature because it provisions a transparent, read-only replica on a separate compute resource within the same region as the primary database. When enabled on Premium, Business Critical, or Hyperscale service tiers, clients can use ApplicationIntent=ReadOnly in their connection string to have their queries automatically routed to that replica, offloading reporting workloads and reducing contention on the primary. This directly addresses the requirement of improving OLTP performance by separating read-only queries from the transactional workload, with low lag and minimal configuration overhead.

Azure Synapse Analytics provides a cloud-based data warehouse that can scale to petabytes. It uses dedicated SQL pools for high-performance analytical queries and has built-in integration with Power BI, Azure Data Lake Storage, and other Azure services.

Read Scale-out in Azure SQL Database leverages a built-in readable secondary replica in the same region. When a connection string specifies ApplicationIntent=ReadOnly, connections are automatically routed to that replica, allowing reporting and analytical queries to execute without consuming primary CPU and I/O. For an online booking system, this directly separates read-only load from transactional write traffic while maintaining the same logical database endpoint.

Azure Database Migration Service is a purpose-built tool for migrating entire database schema and data to Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure Virtual Machines. It performs pre-migration assessments, generates automation scripts, and supports online migrations with minimal downtime via continuous replication. Unlike generic ETL tools, it is optimized for database-specific concerns like constraints, indexes, and transaction consistency.

Using userId as the partition key is correct because it has high cardinality — each user has a unique ID, so every document maps to a distinct logical partition. This evenly spreads data across the physical partitions, preventing hot spots. It also makes point reads by userId highly efficient: the container can route directly to the partition containing that document, typically consuming a minimal number of Request Units (RUs) and delivering low-latency lookups.

Because the videos themselves are unstructured binary content, the practical approach is to keep them in Blob Storage and extract searchable attributes such as speaker, topic, and duration into a companion index. That index can be a relational table or a document store, enabling efficient queries. This is the standard pattern for cataloguing unstructured assets while preserving the original files.

Azure Databricks is essential for data transformation and processing because it provides a managed Apache Spark platform that scales out for large-scale batch and streaming workloads. It enables the ELT/ETL steps that convert raw data into clean, curated datasets, often using Delta Lake for transactional integrity. In the modern data warehouse (lakehouse) architecture, Databricks acts as the compute engine that prepares data for downstream analytics, making it a core component.

Semi-structured data has some organizational structure, such as keys and nested hierarchies, but no rigid schema shared by all records. The JSON drafts match this exactly: fields differ by article type and nesting exists, yet the data is still machine-parseable by key. Azure services such as Azure Blob Storage combined with Azure Synapse Analytics or Azure Cosmos DB handle this format well.

Azure SQL Managed Instance is a full PaaS deployment of the SQL Server database engine that offers nearly 100% surface-area compatibility with on-premises SQL Server. It supports SQL Server Agent, linked servers, cross-database queries, and CLR, while relieving you of patching and backup management. It runs inside your Azure virtual network to support private IP addresses, making it the preferred target for lift-and-shift migrations without redesigning applications.

Batch processing is the correct fit because the nightly job processes a large, accumulated volume of purchase records at a scheduled time, using a bounded dataset. This aligns with the classic batch pattern of ingesting data over a period, then running a job (e.g., Azure Data Factory or Spark) to aggregate and analyze patterns offline without requiring sub-second latency.

Relational tables enforce a fixed schema of columns, data types, and constraints, which is the defining trait of structured data. JPEG files are binary image encodings with no queryable schema or row/column organization, so they are unstructured. JSON documents use named fields and nested objects but allow fields to vary across documents, making them semi-structured rather than fully rigid or schema-free.

Partitioning on GameID co-locates every score for one game in a single logical partition, so the query reads one partition and returns results already ordered by score, avoiding cross-partition fan-out that inflates Request Unit consumption.

Azure Databricks provides a unified analytics platform with Apache Spark, offering collaborative notebooks, full Git integration, and auto-scaling clusters. It supports both Python and SQL natively, making it ideal for interactive data exploration and large-scale transformation of data stored in Azure Data Lake Storage Gen2. Its managed infrastructure and notebook environment allow data engineers to prepare and process data efficiently, which aligns perfectly with the requirement.

This index is ordered by CustomerID then OrderDate descending, allowing efficient seeks for a specific CustomerID and range scans over OrderDate in descending order. Including TotalAmount covers the SELECT clause without needing to access the base table.

Azure Blob Storage is Microsoft's highly scalable object storage service for unstructured data, including text, binary files, images, videos, and application backups. It organizes data into containers with flat namespaces and supports tiered storage (hot, cool, cold, archive) to optimize cost and access patterns. This makes it a core building block for storing and serving unstructured content at massive scale, which is why it is a correct answer.

This pattern uses Blob Storage's Cool tier, which is priced for infrequently accessed data, to hold IoT records older than 30 days while keeping them readily retrievable. An Azure Data Factory pipeline can copy the requested entities from the Cool-tier blobs back into Azure Table Storage on demand, restoring them for queries without requiring the data to stay in the high-priced table tier. This optimizes cost while maintaining availability, typically within the Cool tier's 24-hour retrieval-time SLA.

Azure Databricks with collaborative notebooks is the correct choice because it provides a serverless, auto-scaling Apache Spark platform purpose-built for interactive data exploration and data science. Notebooks support Python, R, SQL, and Scala, and allow multiple data scientists to share and co-edit in real time with integrated version control, while the cluster can auto-start, scale, and terminate to minimize cost. Databricks also includes built-in data visualization and integration with the lakehouse architecture, making it ideal for EDA.

Azure Cosmos DB is a globally distributed, multi-model NoSQL database supporting document, key-value, graph and column-family APIs. Its schema-agnostic design satisfies the non-relational requirement, unlike Azure SQL Database, which enforces relational tables, schemas and joins.

Choosing deviceId as the partition key is optimal because it has high cardinality and aligns directly with the query's equality filter (WHERE deviceId = ?). Automatic indexing on all properties ensures the timestamp field is indexed, so the time-range filter within the selected partition uses a precise index seek rather than a scan, minimizing request-unit (RU) consumption. This combination targets a single physical partition and uses an index for the most selective predicates, making it the most efficient design for this IoT workload.

Azure Synapse Analytics serverless Spark pool is a fully managed, serverless Spark environment that automatically provisions and scales compute on demand without requiring any cluster setup or management. You can submit PySpark, Scala, or Spark SQL jobs directly against files in Azure Data Lake Storage Gen2, and you pay only for the seconds the job executes. This makes it the ideal service for ad-hoc, large-scale data transformations with zero infrastructure overhead.

Azure Blob Storage is the correct choice because it is purpose-built for storing massive amounts of unstructured data, such as high-resolution video files. Blobs are accessible via HTTP/HTTPS URLs, enabling direct streaming and integration with Azure CDN for low-latency delivery. The service scales to petabytes and supports tiers like Hot, Cool, and Archive, making it both cost-effective and performant for media workloads.

The 'Encrypt connection' setting in connection strings instructs the client driver to negotiate TLS encryption for the entire connection to Azure SQL Database. When Encrypt=True and TrustServerCertificate=False are used, the driver validates the server certificate and creates an encrypted channel, preventing eavesdropping and man-in-the-middle attacks on all data sent between the application and the database. This is a direct, connection-level mechanism for securing data in transit.

Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Integration Services (SSIS) via Azure-SSIS Integration Runtime. As a Platform as a Service (PaaS) offering, it automates critical maintenance tasks such as patching, backups, and high availability, while preserving instance-scoped features like SQL Agent, linked servers, and CLR. This minimizes administrative overhead while supporting SSIS, making it the ideal target for a direct migration of a SQL Server database with integration services workloads.

This composite index covers both filter columns in the correct sort order and includes the TotalAmount column, making the query fully covered without needing to access the table. This yields the best performance for the described queries.

Power BI is a business analytics service that natively connects to Azure SQL Database through built-in connectors, enabling you to create interactive dashboards and reports directly from your operational data. It supports DirectQuery and import modes, providing live or cached data access for rich, dynamic visualizations that can be refreshed on demand. With features like row-level security and natural language queries, it is the ideal tool for lightweight, user-facing dashboards at the retail company, offering immediate insights without an intermediate data transformation layer.

PatientID is an ideal partition key because it is the natural filtering attribute for the most common query: retrieving all vital signs for a specific patient. It has high cardinality, since each patient has a unique identifier, so data is spread evenly across logical partitions. Queries that include PatientID are single-partition queries, which are the fastest and most cost-effective in Azure Cosmos DB, avoiding cross-partition fan-out.

GPS ingestion is correctly classified as stream processing because telematics devices emit position records as a continuous, unbounded sequence of events that must be captured and processed with low latency to support live tracking. In contrast, the nightly aggregation job is batch processing because it operates on a bounded, finite set of data already collected, executing on a fixed schedule to compute summaries like daily mileage or route efficiency.

Archive is an offline tier with the lowest storage cost in Azure Blob Storage, specifically built for long-term retention of data that is rarely accessed. To read archived log files, you first rehydrate them to an online tier, a process that typically takes minutes to hours, but that latency is completely acceptable given the access pattern described. This combination of minimal cost and the ability to eventually retrieve the data makes Archive the correct choice.

Azure SQL Database Elastic Pool is the correct choice because it lets all 20 databases share a single pool of eDTUs or vCores, with adjustable per-database minimum and maximum limits. This avoids over-provisioning each app separately and smooths out intermittent usage spikes across tenants. You pay only for the pooled resources actually allocated, not for 20 individually sized databases, which is exactly what unpredictable e-commerce workloads need.

Azure Cosmos DB using the NoSQL API is the correct choice because it provides schema-agnostic document storage that adapts to varying product attributes without migrations. It also guarantees single-digit millisecond latency for point reads (under 10 ms) at any scale, supported by a 99.999% availability SLA. Its turnkey global distribution allows replicas across Azure regions, ensuring low-latency access for e-commerce customers worldwide, and the SQL-like query engine supports rich filtering and projection over flexible JSON documents.

This is the correct classification because OLAP is designed specifically for multidimensional, historical analysis—slicing, dicing, drilling down, and rolling up across dimensions such as time, region, and product. Data is typically stored in columnar, denormalized schemas (star or snowflake) that make full-table scans and aggregations fast, even on billions of rows. A data scientist analyzing historical sales trends matches this analytical workload precisely.

This is correct because the Aggregate transformation in the data flow groups rows by OrderID and applies a count expression, such as count(OrderID), to calculate occurrences per OrderID. Rows with a count greater than 1 are duplicates, allowing the definition to identify (and subsequently remove) duplicate OrderIDs. This matches the requirement to remove duplicate OrderIDs by counting them.

Azure SQL Database serverless is a fully managed PaaS offering with vCore-based compute that automatically scales to match the workload and pauses during periods of inactivity. While paused, you are billed only for storage, not compute, which makes it the most cost-effective choice for a small business with light, intermittent database usage. It remains a relational database with no administrative overhead, and the auto-pause delay can be configured to suit the application's needs.

The hierarchical namespace (HNS) setting, when enabled, transforms a Blob Storage account into an Azure Data Lake Storage Gen2 account. This provides a true file system hierarchy with directories, atomic rename, and POSIX-based access control lists. The ARM template's 'isHnsEnabled' property set to true is the definitive indicator that the account supports ADLS Gen2, which is the answer shown in the exhibit.

Azure SQL Managed Instance is a fully managed PaaS service that delivers near 100% SQL Server compatibility, including native support for SQL Server Agent, which the company needs for scheduled maintenance. It supports storage capacities up to 16 TB for a 2 TB migration, and its auto-failover groups enable cross-region high availability with automatic replication and failover, making it the appropriate target.

Serverless compute tier automatically scales compute resources based on actual demand, scaling down or even pausing the database during periods of inactivity to control costs. It bills per-second for compute and storage separately, making it ideal for workloads with unpredictable, intermittent spikes where manual provisioning or pre-scaling would be wasteful. This meets the requirement because it handles those spikes without intervention.

Azure Synapse Serverless SQL pool is the correct choice because it lets the analyst run standard T-SQL queries directly against files in Azure Data Lake Storage using the OPENROWSET function or external tables, with no infrastructure to provision. It is truly serverless: compute scales automatically and billing is per-query on data processed, which fits ad hoc exploration of large datasets. It also supports creating views for reuse and connects directly to Power BI, making it a low-friction, cost-effective solution for interactive SQL analysis.

Azure SQL Database provides full ACID transactions with row-level security and compatibility, making it a robust operational store for healthcare applications. Azure Synapse Analytics offers a large-scale analytics platform with dedicated SQL pools, massively parallel processing, and integrated data warehousing, capable of running complex queries across relational and data lake sources. Together they deliver an integrated, high-performance OLTP/OLAP solution that supports transactional integrity and advanced analytics.

Configuring a readable secondary replica offloads the Power BI refresh and report queries to a separate compute node that maintains a read-only copy of the data. With read-scale enabled, you can point the connection string to the listener and specify ApplicationIntent=ReadOnly; Azure SQL Database then routes these read requests to the secondary, sparing the primary replica's CPU for writes and other OLTP activity. This directly lowers CPU utilization on the primary without reducing functionality or throttling the BI workload.

JSON is a classic example of semi-structured data. It uses key-value pairs and can have nested structures, but it does not enforce a rigid schema. This flexibility is ideal for IoT payloads where fields may vary over time.

A Kusto query that uses structurally valid operators will fail with a semantic recognition error when it references a table or column that does not exist in the current database or schema. Since the syntax of where, summarize, and top is correct, the most plausible cause is a misspelled table name or an incorrect column name (e.g., a missing quotation mark or wrong casing). Verify the exact schema from the Azure Data Explorer or Log Analytics schema pane to resolve the issue.

Azure Cosmos DB with SQL API natively stores JSON documents as its core data model, making it schema-agnostic so user sessions with varying fields can be ingested without any upfront schema design. It automatically indexes every JSON property by default, and its SQL-like query language can directly filter, project, and traverse nested objects—for example, WHERE sessionId = @id. Combined with single-digit-millisecond latency for point reads and horizontal partitioning, it is specifically engineered to serve flexible, queryable session data at global scale.

UserID is the ideal partition key because all posts belonging to the same user are colocated in a single logical partition, allowing the query for a user's posts to be served from one partition with minimal request units and low latency. Since the application has many users, data is spread evenly across physical partitions, preventing hot spots. Additionally, using UserID aligns with the natural query pattern and enables efficient pagination of results.

Changing the distribution key to a high-cardinality column such as TransactionID is the correct fix. In a hash-distributed table, rows are assigned to distributions by hashing the distribution key, so a key with millions of unique values spreads rows nearly uniformly across all compute nodes. This directly eliminates the data skew on the existing key and also ensures that subsequent joins and aggregations on TransactionID can occur with minimal data movement, allowing the massive table to be processed in parallel.

Azure Data Factory is the cloud-backed ETL/ETL orchestration engine that connects to 90+ on-premises and cloud sources, moves data into Data Lake Storage Gen2, and triggers transformation activities on Azure Databricks or HDInsight. Its scheduled or event-driven pipelines are what make repeatable batch data processing possible. Without it, the modern data warehouse cannot automate the ingestion and transformation steps that feed the serving layer.

Azure Synapse Serverless SQL is an on-demand query engine that uses T-SQL and OPENROWSET to query files directly from Blob Storage or Azure Data Lake Storage Gen2 without provisioning dedicated compute. It supports various file formats such as Parquet, CSV, and JSON, and charges per query based on bytes scanned. This enables interactive, schema-on-read analysis of log files, exactly matching the requirement to query stored log files.

Azure SQL Managed Instance is a fully managed PaaS offering that maintains near-complete SQL Server engine compatibility, including linked servers and native cross-database queries. It also provides built-in high availability, automated backups, and automatic patching, which eliminates the operational overhead of managing virtual machines. For a company moving an existing SQL Server workload with multiple interdependent databases, this option gives the required functionality while minimizing management burden.

Azure Cosmos DB SQL (Core) API is a document model that natively stores JSON documents and exposes a SQL-enabled query language specifically designed to query those documents. It automatically indexes every property within the JSON, allowing flexible queries on optional and nested fields, which is ideal for a social media app's user profiles that vary in structure. Because it supports SQL-like syntax that can filter on userId and any other key with minimal effort, it best matches the requirement for querying JSON profiles.

Azure Databricks is a managed Apache Spark platform that provides interactive workspaces and clusters for large-scale data processing. It lets you transform data using Python, Scala, SQL, or R, and supports both batch and streaming workloads. This makes it a primary Azure service for complex ETL and ELT transformations, especially when combining structured and unstructured data across data lakes.

Azure Stream Analytics is a fully managed stream-processing engine that ingests events directly from Azure Event Hubs or IoT Hub, applies SQL-like queries with temporal windows such as tumbling, hopping, and sliding, and emits results to Blob Storage or other sinks. This gives the marketing team near-real-time sentiment analytics without provisioning clusters or writing distributed-streaming logic. Its low-latency, event-time-aware processing is exactly what this social media sentiment scenario requires.

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct migration target because it is built on Azure Blob Storage but adds a hierarchical namespace that mirrors HDFS. It exposes a native HDFS-compatible ABFS driver plus a REST API, enabling Hadoop, Spark, and Databricks to read and write data with full file-system semantics like atomic rename and POSIX file permissions. ADLS Gen2 is specifically designed for big data analytics and is the Azure service that most closely and natively replaces an on-premises Hadoop HDFS cluster.

Mapping data flows provide a visual, code-free canvas for conditional branching and complex transformations, satisfying the requirement to minimise coding effort. Azure Data Factory orchestrates the nightly ingestion and loading into Azure Synapse Analytics, so no separate compute engine is needed.

Cool tier is the correct choice for healthcare data older than one year because it provides low storage costs while keeping data immediately readable without rehydration. It is designed for data that is infrequently accessed but needs to be held for at least 30 days, which aligns with a seven-year retention requirement. Although access fees are higher than Hot, the overall cost for rarely read data is significantly lower, and there is no 180-day minimum or retrieval latency as in Archive.