DP-900 Practice Test 5 — 25 Questions

Question 1

A data engineer needs to process raw clickstream data from multiple websites that is stored in Azure Blob Storage as JSON files. The processing must run automatically every hour, transform the data into a structured format for reporting, and handle schema changes in the source data without manual intervention. Which Azure service should be used?

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

Accepted Answer

Azure Data Factory with a Mapping Data Flow.. Azure Data Factory with a Mapping Data Flow is correct because it provides a code-free, visual data transformation environment that can run on a scheduled trigger (every hour), handle schema drift automatically via schema drift options in Mapping Data Flows, and process JSON files from Azure Blob Storage into a structured format for reporting. This meets all requirements: scheduled execution, transformation, and schema evolution without manual intervention.

Answer

Azure Stream Analytics with a reference data input.

Answer

Azure SQL Database with a stored procedure.

Answer

Azure Logic Apps with a JSON parser.

Question 2

A gaming company stores player scores in Azure Cosmos DB using the NoSQL API. Each document contains: PlayerID (unique to player), GameID, Score, Timestamp. The most common query is: 'Retrieve all scores for a specific GameID, ordered by Score descending.' Which property should be chosen as the partition key to minimize Request Unit (RU) consumption?

Accepted Answer

GameID. GameID is the correct partition key because the most common query filters on GameID, and Cosmos DB routes each query to the physical partition(s) containing that GameID's data. Using GameID ensures the query touches only the relevant partition(s), minimizing RU consumption by avoiding cross-partition fan-out. A partition key that matches the filter predicate is essential for efficient, single-partition queries.

Answer

PlayerID

Answer

Score

Answer

Timestamp

Question 3

A company runs an online booking system on Azure SQL Database. The system handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the booking transactions. The company needs a solution to separate the analytical workload from the transactional workload without duplicating data manually. Which Azure SQL Database feature should they use?

Accepted Answer

Read Scale-out (readable secondary replica). Read Scale-out (readable secondary replica) is the correct choice because it allows the company to offload complex reporting queries to a read-only replica of the primary database, thereby isolating the analytical workload from the OLTP transactions. This feature is built into Azure SQL Database and does not require manual data duplication or ETL processes, directly addressing the requirement to separate workloads without manual effort.

Answer

Active Geo-Replication

Answer

Elastic pools

Answer

Hyperscale service tier

Question 4

A company uses Azure SQL Database for an HR system. The Employees table has a clustered index on EmployeeID. Queries frequently filter on DepartmentID and LastName and also retrieve the Salary column. The table contains over a million rows. Which index strategy will most improve query performance for these filters?

Accepted Answer

A: Create a nonclustered index on (DepartmentID, LastName) INCLUDE (Salary). It creates a covering nonclustered index on the filter columns (DepartmentID, LastName) and includes the Salary column as an included column. This allows the query to be fully satisfied by scanning only the nonclustered index pages, avoiding key lookups to the clustered index. The order of columns in the index key matches the query filter pattern, maximizing seek efficiency.

Answer

B: Create a nonclustered index on LastName only

Answer

C: Create a nonclustered index on DepartmentID and another nonclustered index on LastName

Answer

D: Change the clustered index to (DepartmentID, LastName)

Question 5

A company stores customer orders. Each order has a unique order ID, customer ID, a list of items (each item contains product ID, quantity, and price), and an order date. They frequently query orders by customer ID and also need to filter by order date ranges. The data volume is high and schema flexibility is desired because items can vary in structure. Which type of data store is best suited for this scenario?

Accepted Answer

Document database. A document database (e.g., Azure Cosmos DB for NoSQL) is ideal because it stores each order as a self-contained JSON document, allowing the items array to vary in structure per order (schema flexibility). It supports efficient queries by customer ID (using a partition key) and filtering by order date ranges (using indexing on the date field), while handling high data volumes with horizontal scaling.

Answer

Relational database

Answer

Key-value store

Answer

Graph database

Question 6

A retail company wants to analyze years of historical sales data stored as CSV files in Azure Blob Storage. The analytics solution must be serverless, allow T-SQL queries without managing infrastructure, and integrate directly with Power BI. Which Azure service should the company use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it provides a serverless, on-demand query service that can directly query CSV files stored in Azure Blob Storage using T-SQL without requiring any infrastructure management. It integrates natively with Power BI via the T-SQL endpoint, enabling direct data visualization from the queried files.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Analysis Services

Question 7

A company uses Azure SQL Database for a customer management system. The Customers table has columns: CustomerID (int, primary key), FullName (varchar(100)), Email (varchar(200)), SignUpDate (date), LastLoginDate (date). Queries frequently filter on LastLoginDate to find customers who have not logged in for over a year for a promotional campaign. The table has 10 million rows. Which type of index should they create to optimize these queries?

Accepted Answer

Non-clustered index on LastLoginDate. A non-clustered index on LastLoginDate allows the query to quickly locate rows where LastLoginDate is older than one year without scanning the entire 10-million-row table. Azure SQL Database uses B-tree structures for non-clustered indexes, enabling efficient range scans and key lookups for the filtered rows. This directly supports the promotional campaign query pattern.

Answer

Clustered index on CustomerID

Answer

Non-clustered index on FullName

Answer

Columnstore index on SignUpDate and LastLoginDate

Question 8

A marketing team wants to analyze social media sentiment in near real-time. They will use Azure Event Hubs to capture tweets and need to aggregate sentiment scores over 5-minute windows. The aggregated results must be stored in Azure Blob Storage for later analysis. Which Azure service should they use to perform the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a real-time stream processing engine designed to ingest data from sources like Azure Event Hubs, apply temporal aggregations (e.g., 5-minute tumbling windows), and output results directly to Azure Blob Storage. It provides built-in support for windowed functions and exactly-once delivery semantics, making it ideal for near-real-time sentiment analysis without requiring custom code.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Analytics

Question 9

A company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. Every day, they need to incrementally load 100 GB of new sales data from CSV files stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The load should use PolyBase for efficient parallel data transfer and must be orchestrated on a recurring schedule. Which Azure service should they use to create and manage this pipeline?

Accepted Answer

A: Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it provides native orchestration and scheduling capabilities for data pipelines. It supports PolyBase as a sink to load data into Azure Synapse dedicated SQL pool in parallel, and it can directly read CSV files from ADLS Gen2. ADF's built-in triggers allow you to schedule the daily incremental load without additional coding.

Answer

B: Azure Stream Analytics

Answer

C: Azure Databricks

Answer

D: Azure Logic Apps

Question 10

A marketing company collects real-time clickstream data from their website using Azure Event Hubs. They need to perform two tasks: (1) aggregate the number of clicks per advertising campaign every 5 minutes and display the results in a live dashboard, and (2) run complex historical queries on months of aggregated click data to identify trends. They want to minimize data movement and use serverless compute where possible. Which combination of Azure services should they use?

Accepted Answer

Azure Stream Analytics for live aggregation and Power BI for the dashboard; Azure Synapse Analytics (serverless SQL pool) for historical queries. Azure Stream Analytics is ideal for real-time aggregation of clickstream data from Event Hubs, outputting to Power BI for a live dashboard. Azure Synapse Analytics serverless SQL pool allows querying months of aggregated data stored in Azure Data Lake Storage without provisioning compute, minimizing data movement and using serverless compute.

Answer

Azure Data Factory for live aggregation; Azure Analysis Services for historical queries

Answer

Azure HDInsight (Spark) for both live and historical processing

Answer

Azure Functions for real-time aggregation; Azure SQL Database for historical queries

Question 11

A logistics company collects data from fleet sensors. Each sensor sends a JSON message containing the vehicle ID, timestamp, and a variable set of measurements such as engine temperature, tire pressure, and fuel level. The structure of the JSON message differs between sensor types and sometimes includes optional fields. How should this data be classified?

Accepted Answer

Semi-structured data. The data is semi-structured because it conforms to a schema (JSON format with fields like vehicle ID and timestamp) but allows variability in structure, such as optional fields and different sets of measurements per sensor type. This flexibility is a hallmark of semi-structured data, which does not require a rigid tabular schema like structured data but still contains tags or markers to separate data elements.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 12

A company ingests streaming data from IoT devices into Azure Event Hubs. They need to perform real-time analytics on the data, such as aggregating temperature readings over 5-minute windows and triggering alerts when thresholds are exceeded. They also want to store the processed data in a data warehouse for historical analysis. Which Azure service should they use for the real-time processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is purpose-built for real-time stream processing, allowing you to define SQL-like queries that aggregate data over tumbling or hopping windows (e.g., 5-minute windows) and trigger alerts based on thresholds. It integrates directly with Azure Event Hubs as a source and can output processed results to Azure Synapse Analytics or other data warehouses for historical storage, making it the correct choice for this real-time analytics workload.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Logic Apps

Question 13

A financial company is migrating a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for data validation and cleanup, and it performs cross-database queries using three-part names (e.g., DB1.schema.table). The company requires a fully managed PaaS service that supports these features with minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and cross-database queries using three-part names (e.g., DB1.schema.table). As a fully managed PaaS service, it minimizes application changes while offloading infrastructure management, making it ideal for migrating a 2-TB database with these specific requirements.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

Question 14

A retail company uses a point-of-sale (POS) system that records each sales transaction in a database. Each transaction involves reading the current inventory, updating the stock level, and recording the sale. The database must ensure that concurrent transactions do not interfere with each other, so that one transaction does not see partially updated data from another. Which property of a database transaction ensures this isolation?

Accepted Answer

Isolation. Isolation ensures that concurrent transactions do not interfere with each other, so each transaction sees a consistent snapshot of the database as if it were the only transaction running. In the POS scenario, isolation prevents one transaction from reading partially updated inventory data from another transaction, which could lead to overselling or stock discrepancies. This property is typically implemented through locking mechanisms or multi-version concurrency control (MVCC).

Answer

Atomicity

Answer

Consistency

Answer

Durability

Question 15

A company stores customer reviews for an e-commerce site. Each review contains a product ID, user ID, rating, and optional comments and images. The reviews are written once and rarely updated. The company needs to query reviews by product ID with low latency and also perform simple key-value lookups. They want a cost-effective, serverless solution that requires no scaling management. Which Azure data store should they choose?

Accepted Answer

Azure Table Storage. Azure Table Storage is a cost-effective, serverless NoSQL key-value store that supports simple key-value lookups and querying by partition key (e.g., ProductID) with low latency. It requires no scaling management, as it automatically scales based on demand, and is ideal for immutable, rarely-updated data like customer reviews. The pay-per-request pricing model makes it highly cost-effective for this workload.

Answer

Azure Cosmos DB SQL API

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 16

A retail company stores product inventory data in a SQL database, customer reviews as JSON files, and product images as JPEG files. Which of the following accurately describes the types of data stored?

Accepted Answer

D. Structured, semi-structured, and unstructured data are stored.. The company stores product inventory data in a SQL database, which enforces a fixed schema (tables, rows, columns) and is therefore structured data. Customer reviews stored as JSON files are semi-structured because they have a flexible schema (key-value pairs) but no rigid table structure. Product images as JPEG files are unstructured because they lack any predefined schema or organization. Option D correctly identifies that all three data types are present.

Answer

A. Only structured data is stored because the SQL database contains the primary records.

Answer

B. Only semi-structured and unstructured data is stored because JSON and images are not purely structured.

Answer

C. Only unstructured data is stored because images have no predefined schema.

Question 17

A startup has an application with unpredictable usage patterns on Azure SQL Database. They want to minimize cost by paying only for the compute they use and the database should automatically pause during idle periods. Which Azure SQL Database option should they choose?

Accepted Answer

Serverless. Azure SQL Database Serverless is designed for applications with unpredictable usage patterns, as it automatically scales compute resources based on demand and pauses the database during idle periods to eliminate compute costs. This model charges per second for the compute used, making it the most cost-effective choice for workloads that have periods of inactivity.

Answer

Provisioned (DTU or vCore)

Answer

Hyperscale

Answer

Business Critical

Question 18

A company operates an online store that processes customer orders. When a customer places an order, the system must immediately reduce the inventory count for the purchased items and record the order details. At the end of each month, the company runs reports that aggregate sales data over the past month to analyze trends. Which type of data processing workload best describes the order placement activity?

Accepted Answer

Transactional processing. Order placement requires immediate inventory reduction and order recording, which demands ACID (Atomicity, Consistency, Isolation, Durability) guarantees. This is a classic transactional processing workload, typically handled by OLTP (Online Transaction Processing) systems like SQL Server or Azure SQL Database, ensuring data integrity even under concurrent access.

Answer

Analytical processing

Answer

Batch processing

Answer

Stream processing

Question 19

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and relies on linked servers to query data from another SQL Server instance. It also performs cross-database queries within the same instance. The company wants a fully managed PaaS service that requires minimal application changes and provides automated backups and patching. Which Azure SQL service should they choose?

Accepted Answer

B. Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. It is a fully managed PaaS service that offers automated backups and patching, minimizing application changes during migration.

Answer

A. Azure SQL Database (single database)

Answer

C. SQL Server on Azure Virtual Machines

Answer

D. Azure SQL Database elastic pool

Question 20

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table containing 5 TB of sales transactions. New data arrives continuously and is loaded daily. The company needs to load 500 GB of new data each day while allowing concurrent read queries on the most recent data without performance degradation. Which loading strategy optimizes both load speed and query performance?

Accepted Answer

Load data into a staging table, then use partition switching to swap the latest partition. Partition switching allows you to load new data into a staging table, then instantly swap the staging partition with the target table's latest partition using ALTER TABLE SWITCH. This minimizes metadata changes and avoids data movement, enabling fast loads while keeping the fact table online for concurrent read queries without blocking or performance degradation.

Answer

Use INSERT statements to add rows incrementally

Answer

Use CREATE TABLE AS SELECT (CTAS) to build a new table and rename it

Answer

Use PolyBase to load data directly into the fact table