DP-900 Practice Test 19 — 15 Questions

Question 1

A manufacturing company uses IoT sensors to collect temperature and vibration data from machinery. They need to analyze the streaming data in real time to detect anomalies and trigger alerts. Additionally, they need to run complex historical queries on months of sensor data to identify equipment failure patterns. They want a single Azure service that can handle both real-time stream processing and large-scale batch analytics using a unified query language, minimizing the need for separate technologies. Which Azure service should they use?

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

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified platform that combines both real-time stream processing (via Synapse Pipelines and Spark Streaming) and large-scale batch analytics (via Synapse SQL and Spark) using a single query language (T-SQL or Spark SQL). This eliminates the need for separate technologies, directly addressing the requirement for a single service to handle both streaming and historical batch analysis.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage Gen2

Answer

Azure HDInsight

Question 2

A social networking application uses Azure Cosmos DB to store user posts. When a user publishes a new post, they immediately refresh their feed and expect to see their own post. However, the application can tolerate temporary staleness for posts from other users (e.g., a few seconds delay). Which Azure Cosmos DB consistency level should the application use for read operations that display the feed?

Accepted Answer

Session. Session consistency is the correct choice because it guarantees that the user who writes a post will read their own write within the same session, while allowing other users to see slightly stale data. This matches the requirement: the author immediately sees their new post, but the application can tolerate a few seconds of staleness for other users' posts. Session consistency uses a session token to ensure monotonic reads and writes for the same client, making it ideal for per-user feed scenarios.

Answer

Strong

Answer

Bounded staleness

Answer

Eventual

Question 3

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?

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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