DP-900 Practice Test 21 — 15 Questions

Question 1

A company needs to ingest data from an on-premises SQL Server database into Azure SQL Database every hour. During the ingestion, they need to filter out rows where Status = 'Inactive' and convert a date column to a different format. They want a cloud-based, code-free solution that can schedule and orchestrate this task. Which Azure service should they use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows is the correct choice because it provides a cloud-based, code-free ETL service that can ingest data from on-premises SQL Server into Azure SQL Database, apply transformations like filtering rows (Status = 'Inactive') and converting date formats, and schedule the task using triggers. Mapping Data Flows run on Spark clusters and allow visual data transformation without writing code, making it ideal for this orchestrated, scheduled ingestion.

Answer

Azure Logic Apps

Answer

Azure Functions

Answer

Azure SQL Database Change Data Capture

Question 2

A media company stores high-definition video files for on-demand streaming. The files are accessed very frequently for the first 30 days after upload, then rarely (about once per month) for the next year, and after one year they are rarely accessed but must be retained for compliance (about once per year). Which set of access tier transitions minimizes cost while meeting access requirements?

Accepted Answer

Hot for 30 days, then Cool for 11 months, then Archive. It aligns the Azure Blob Storage access tier transitions with the access pattern: Hot tier for the first 30 days of frequent access, Cool tier for the next 11 months of monthly access, and Archive tier after one year for rare compliance access. This minimizes cost by moving data to progressively cheaper storage tiers as access frequency drops, while still meeting the access requirements (Cool supports monthly access, Archive supports yearly access with retrieval time).

Answer

Hot for 30 days, then Archive immediately

Answer

Cool for 30 days, then Cool for 11 months, then Archive

Answer

Hot for 365 days, then Archive

Question 3

A company maintains a large 'Transactions' table in Azure SQL Database. The table has a clustered index on a GUID column (TransactionID). Over time, they observe slow insert performance due to index fragmentation and page splits. They also need fast point lookups by TransactionID. Which approach should they take to improve insert performance while still supporting fast lookups?

Accepted Answer

Change the clustered key to an integer IDENTITY column and keep a nonclustered index on TransactionID. Using an integer IDENTITY column as the clustered key eliminates the random insertion order and page splits caused by a GUID clustered index, while the nonclustered index on TransactionID provides fast point lookups. In Azure SQL Database, a clustered index determines the physical order of data; a monotonically increasing integer avoids fragmentation and improves insert throughput.

Answer

Change the clustered index to a nonclustered index on TransactionID and make the table a heap

Answer

Keep the clustered index on TransactionID but rebuild it daily

Answer

Remove the clustered index entirely and create a nonclustered index on TransactionID

Question 4

A company has a table named 'Sales' in Azure SQL Database with columns: SaleID (int, primary key), ProductID (int), SaleDate (datetime), Quantity (int), UnitPrice (decimal), TotalAmount (computed column). Queries frequently run to retrieve the total Quantity and UnitPrice for a specific ProductID over a date range. The query filters on ProductID and SaleDate and selects only Quantity and UnitPrice. Which index would most improve query performance?

Accepted Answer

Nonclustered index on (ProductID, SaleDate) INCLUDE (Quantity, UnitPrice). It creates a covering nonclustered index that supports both the WHERE clause (ProductID, SaleDate) and the SELECT clause (Quantity, UnitPrice) without needing to access the base table. The index key order matches the query filter, and the included columns avoid key lookups, minimizing I/O for the frequent aggregation queries.

Answer

Nonclustered index on (SaleDate) INCLUDE (Quantity, UnitPrice)

Answer

Clustered index on (ProductID, SaleDate)

Answer

Nonclustered index on (ProductID) INCLUDE (Quantity, UnitPrice)

Question 5

A startup is building a global user session store. Each session consists of a simple key (session ID) and a value (user data as a JSON string). The application requires low-latency reads and writes from any Azure region, and the data must be durable. Which Azure service is best suited for this scenario?

Accepted Answer

Azure Cosmos DB (Table API). Azure Cosmos DB (Table API) is the best fit because it provides global, multi-region writes with tunable consistency, guaranteed single-digit-millisecond latency for reads and writes at the 99th percentile, and full durability with automatic replication across any number of Azure regions. The Table API offers a key-value store interface (session ID as partition key, JSON value) while also supporting schema flexibility and SLA-backed performance, which is critical for a global user session store.

Answer

Azure Table Storage

Answer

Azure Redis Cache

Answer

Azure Blob Storage

Question 6

Which TWO are valid deployment options for Azure SQL?

Accepted Answer

Azure SQL Managed Instance. Options A and B are correct. Azure SQL Database and Azure SQL Managed Instance are the two main deployment options for Azure SQL. Options C, D, and E are incorrect: Azure Cosmos DB is a NoSQL database, Azure Database for MariaDB is a separate managed database service, and Azure Synapse Analytics Dedicated SQL Pool is a data warehousing service.

Answer

Azure Cosmos DB

Answer

Azure Database for MariaDB

Answer

Azure Synapse Analytics Dedicated SQL Pool

Question 7

A company uses Azure SQL Database for an e-commerce system. The Orders table has millions of rows with a clustered index on OrderID (the primary key). Queries that filter on OrderDate and CustomerID to find recent orders for a specific customer are very slow. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

Create a nonclustered composite index on (OrderDate, CustomerID). The query filters on both OrderDate and CustomerID, so a composite nonclustered index on (OrderDate, CustomerID) allows SQL Server to perform a single index seek to locate the matching rows without touching the clustered index until the final key lookup. This dramatically reduces I/O compared to scanning the entire clustered index or using multiple separate indexes.

Answer

Create a nonclustered index on OrderID only

Answer

Create separate nonclustered indexes on OrderDate and CustomerID

Answer

Create a clustered index on CustomerID instead of OrderID

Question 8

A marketing company stores years of historical campaign data in Azure Data Lake Storage Gen2 as Parquet files. Data analysts need to run complex SQL queries over this data to identify trends, and they want to visualize results in Power BI dashboards. The company wants to avoid moving data into a separate database to minimize duplication and latency. Which Azure service should they use to query the data directly in the data lake?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows you to run T-SQL queries directly over Parquet files in Azure Data Lake Storage Gen2 without moving or copying the data. It uses a pay-per-query model and supports standard SQL syntax, making it ideal for analysts who need to query historical campaign data and visualize results in Power BI with minimal latency.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure HDInsight

Question 9

A manufacturing company collects sensor data from thousands of IoT devices. The data arrives as a stream of time-stamped readings with a fixed schema (DeviceID, Timestamp, Temperature, Pressure, Vibration). They need to store this data and support both real-time dashboards showing the last hour of data and complex analytical queries over years of historical data. The solution must minimize storage costs and provide sub-second response for real-time queries. Which Azure service is best suited for this workload?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is purpose-built for high-performance analysis of large volumes of streaming telemetry data. It supports ingestion from IoT hubs, automatic indexing for sub-second queries on recent data (e.g., last hour), and cost-effective long-term storage via hot/cold tiering for years of historical analytics. Its columnar storage and Kusto Query Language (KQL) are optimized for time-series and aggregation queries, making it ideal for this mixed real-time and historical workload.

Answer

Azure Cosmos DB with SQL API

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 10

A company uses Azure SQL Database to store order data. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status, TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate descending. Which indexing strategy will most improve query performance for these filters and sort?

Accepted Answer

Create a nonclustered index on (OrderDate DESC, Status) and include TotalAmount. Creates a covering nonclustered index on (OrderDate DESC, Status) that directly supports the frequent filter on OrderDate and Status and the ORDER BY OrderDate DESC. Including TotalAmount as a non-key column makes the index covering, so all needed columns come from the index without key lookups, maximizing query performance.

Answer

Create a clustered index on OrderDate

Answer

Create a nonclustered index on Status alone

Answer

Create a clustered columnstore index on the table

Question 11

A ride-sharing application needs to store real-time GPS location updates from drivers and passengers. The data is ingested as key-value pairs where the key is the user ID and the value is a timestamped location. The application requires low-latency reads and writes for millions of concurrent users, and the data model is simple with no need for complex queries or joins. Which Azure NoSQL database API should be used for this workload?

Accepted Answer

Azure Cosmos DB Table API. Azure Cosmos DB Table API is the correct choice because it provides a key-value store with low-latency reads and writes, ideal for high-throughput scenarios like real-time GPS updates. It supports a simple schema-less data model where each item is a key-value pair, and it offers single-millisecond latency at the 99th percentile for both reads and writes, meeting the requirement for millions of concurrent users without complex queries or joins.

Answer

Azure Cosmos DB SQL (Core) API

Answer

Azure Cosmos DB for MongoDB API

Answer

Azure Cosmos DB for Apache Gremlin API

Question 12

A financial analytics company has two distinct data processing workloads. The first workload ingests real-time stock trade data from a message queue, calculates moving averages every minute, and updates a dashboard for traders. The second workload receives daily CSV files containing end-of-day trade summaries, transforms them using Python scripts, and loads the results into a data warehouse for monthly reporting. Which statement correctly characterizes these workloads?

Accepted Answer

First workload: Stream processing, Second workload: Batch processing. The first workload processes real-time stock trade data from a message queue and calculates moving averages every minute, which is a classic stream processing pattern (continuous, low-latency data ingestion and computation). The second workload handles daily CSV files with end-of-day summaries, transforms them with Python scripts, and loads results into a data warehouse for monthly reporting, which is a classic batch processing pattern (scheduled, high-latency processing of bounded data sets).

Answer

First workload: Batch processing, Second workload: Stream processing

Answer

First workload: OLTP, Second workload: OLAP

Answer

First workload: Transactional processing, Second workload: Analytical processing

Question 13

A company runs individual Azure SQL Databases for each of its departments. The databases experience varying usage patterns; sometimes one database is idle while another is heavily loaded. The company wants to pool resources to reduce cost while ensuring each database gets resources when needed. Which Azure feature should they use?

Accepted Answer

Azure SQL Database elastic pool. Azure SQL Database elastic pools are designed to share resources (eDTUs or eVCores) across multiple databases with varying usage patterns. This allows idle databases to contribute their unused capacity to heavily loaded ones, reducing overall cost while ensuring each database gets resources when needed.

Answer

Azure SQL Database single database with provisioned DTUs

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Question 14

A company is migrating a legacy on-premises database to Azure. They require the ability to run cross-database queries within the same logical server, full control over database collation settings, and want to minimize management overhead for infrastructure patching. The database size is under 1 TB and they do not need instance-level features like SQL Agent jobs or linked servers. Which Azure SQL offering should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it supports cross-database queries within the same logical server via elastic queries, allows full control over database-level collation settings, and is a fully managed Platform-as-a-Service (PaaS) offering that handles infrastructure patching automatically. With a database size under 1 TB and no need for instance-level features like SQL Agent jobs or linked servers, Azure SQL Database meets all requirements while minimizing management overhead.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Answer

Azure Synapse SQL pool

Question 15

A smart building company stores IoT sensor data in Azure Cosmos DB using the NoSQL API. Each document contains fields: deviceId (partition key), timestamp, temperature, and humidity. The most common query is to retrieve all readings for a specific device within a time range, which runs efficiently. However, the analytics team occasionally runs a query to find all devices that reported a temperature above 50 degrees Celsius in the last hour, without specifying deviceId. This query is very slow and consumes a high number of request units (RUs). What is the most likely reason for the slow performance and high RU consumption?

Accepted Answer

The query does not use the partition key, causing a cross-partition scan.. The query does not include the partition key (deviceId) in the filter, so Azure Cosmos DB cannot route it to a single physical partition. Instead, it must fan out the query to every partition, scanning all documents across the container. This cross-partition query consumes significantly more RUs and takes longer because each partition must be queried sequentially or in parallel, and the results are merged server-side.

Answer

The query is not using an index on the temperature field.

Answer

The time range filter is too large, causing a full table scan.

Answer

The document size is too large, increasing RU per read.