DP-900 Practice Test 36 — 15 Questions

Question 1

A data engineering team needs to transform large datasets stored in Azure Data Lake Storage Gen2 using Apache Spark with Python code. They want a fully managed service that provides serverless Spark pools, meaning no clusters to manage and automatic scaling. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics with serverless Spark pools. Azure Synapse Analytics with serverless Spark pools is the correct choice because it provides a fully managed, serverless Apache Spark environment that automatically scales and eliminates the need to manage clusters. This service directly supports transforming large datasets in Azure Data Lake Storage Gen2 using Python code with Spark, meeting the team's requirement for a no-cluster-management, auto-scaling solution.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure Machine Learning

Question 2

Which TWO Azure services can be used to host a relational database that is compatible with SQL Server?

Accepted Answer

Azure SQL Database. Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance both provide SQL Server compatibility. Option B is wrong because Azure Cosmos DB is NoSQL. Option C is wrong because Azure Database for MySQL is MySQL. Option E is wrong because Azure Database for PostgreSQL is PostgreSQL.

Answer

Azure Database for PostgreSQL

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 3

A data engineering team is designing a modern data warehouse on Azure. They have raw data landing in Azure Data Lake Storage Gen2 (ADLS Gen2) as Parquet files. They need to perform transformations using Apache Spark, and then load the transformed data into Azure Synapse Analytics for high-performance analytical queries. The team wants to use a single orchestration service to schedule, monitor, and manage the entire pipeline. Which Azure service should they choose for orchestration?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and orchestration service designed to schedule, monitor, and manage data pipelines at scale. It natively supports triggers (e.g., time-based, event-based) and can orchestrate Apache Spark transformations via Azure Databricks or HDInsight, then load the transformed data into Azure Synapse Analytics using built-in copy activities or pipelines. ADF provides a single pane of glass for end-to-end pipeline management, including dependency handling and error monitoring.

Answer

Azure Databricks

Answer

Azure Logic Apps

Answer

Azure Data Lake Analytics

Question 4

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 5

A financial services company stores years of market trade data as Parquet files in Azure Data Lake Storage Gen2. The data volume is terabytes and growing rapidly. Data analysts need to run complex SQL queries that join multiple tables (e.g., trades, instruments, counterparties) and return results within seconds. The company also wants to integrate with Power BI for visualization and Azure Data Factory for orchestration of ETL pipelines. Which Azure service should they choose as the primary analytics platform?

Accepted Answer

Azure Synapse Analytics (serverless SQL pool). Azure Synapse Analytics serverless SQL pool is the correct choice because it provides a distributed SQL query engine that can directly query Parquet files in Azure Data Lake Storage Gen2 using T-SQL, enabling complex joins across multiple tables with fast performance via automatic query optimization and pushdown computation. It integrates natively with Power BI for visualization and Azure Data Factory for ETL orchestration, making it the ideal primary analytics platform for large-scale, schema-on-read data lake scenarios.

Answer

Azure SQL Database

Answer

Azure HDInsight with Spark

Answer

Azure Analysis Services

Question 6

A financial services company runs critical end-of-day reports in an Azure Synapse Analytics dedicated SQL pool. These reports require guaranteed resource allocation and must complete within a fixed time window. However, ad-hoc analytical queries from data scientists often consume resources, causing contention and delaying the critical reports. Which feature should the company implement to ensure the critical reports always receive sufficient resources?

Accepted Answer

A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.. Option A is correct because workload groups in Azure Synapse Analytics dedicated SQL pool allow you to assign a minimum percentage of resources (e.g., CPU and memory) to a specific workload, ensuring guaranteed resource allocation. By setting high importance for the critical reports, the system prioritizes them over ad-hoc queries, preventing resource contention and ensuring they complete within the fixed time window.

Answer

B. Enable result set caching on all queries to reduce execution time.

Answer

C. Implement materialized views for the aggregations used in the critical reports.

Answer

D. Use hash distribution for the fact tables to improve query parallelism.

Question 7

A multinational corporation needs to deploy a relational database in Azure that supports global distribution and low-latency reads and writes across multiple regions. The data model is primarily key-value with occasional relational queries. Which Azure service should they choose?

Accepted Answer

Azure Cosmos DB with PostgreSQL-compatible API. Option D is correct because Azure Cosmos DB offers multiple consistency levels and global distribution with multi-region writes, and its API for NoSQL can handle key-value data. However, it also provides a PostgreSQL-compatible API (Citus) for relational scenarios, making it the best fit for global distribution. Option A is wrong because Azure SQL Database's geo-replication supports only one writable region. Option B is wrong because Azure SQL Managed Instance is region-bound. Option C is wrong because Azure Database for PostgreSQL with Hyperscale (Citus) is a good option but does not offer the same multi-region write capabilities as Cosmos DB, and the question emphasizes global distribution.

Answer

Azure SQL Database with active geo-replication

Answer

Azure SQL Managed Instance

Answer

Azure Database for PostgreSQL – Hyperscale (Citus)

Question 8

A financial services company uses a dedicated SQL pool in Azure Synapse Analytics to run large-scale analytical queries. During peak hours, complex aggregations consume excessive resources, causing slower performance for other users. The company needs to ensure that critical scheduled management reports always receive guaranteed resources and complete within a predictable timeframe, while less important ad-hoc queries do not interfere. Which feature should they implement to manage query resource allocation?

Accepted Answer

Workload management. Workload management in Azure Synapse Analytics allows you to classify, assign, and govern resources for queries by using workload groups and importance levels. By configuring workload groups, you can guarantee resources for critical scheduled management reports (e.g., assigning high importance) while limiting or deprioritizing less important ad-hoc queries, ensuring predictable completion times during peak hours.

Answer

Result set caching

Answer

Columnstore indexes

Answer

Table distribution

Question 9

A healthcare analytics company receives continuous streams of patient monitoring data from IoT devices. The data must be processed in near real-time to detect critical events (e.g., abnormal heart rate). Processed data is then stored in a columnar format for historical analysis and reporting by data analysts using SQL. Which combination of Azure services should they use for ingestion, processing, and storage?

Accepted Answer

Azure Event Hubs, Azure Stream Analytics, Azure Synapse Analytics. Azure Event Hubs is designed for high-throughput, low-latency ingestion of streaming data from millions of IoT devices. Azure Stream Analytics provides a SQL-based, near real-time processing engine to detect critical events like abnormal heart rates. Azure Synapse Analytics (formerly SQL Data Warehouse) offers a columnar storage format (e.g., columnstore indexes) optimized for historical analysis and SQL-based reporting by data analysts.

Answer

Azure IoT Hub, Azure Data Factory, Azure SQL Data Warehouse

Answer

Azure Event Hubs, Azure Stream Analytics, Azure Cosmos DB

Answer

Azure Blob Storage, Azure Databricks, Azure Table Storage

Question 10

A financial services company has raw transaction data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) as Parquet files, partitioned by date. The analytics team needs to run complex SQL queries that join multiple datasets, including reference data from an Azure SQL Database, to generate risk reports. They require enterprise-grade security features such as row-level security (RLS) and column-level security. They also want to use the same service for data transformation and loading (ETL) into a curated layer. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that combines enterprise data warehousing with big data analytics. It directly supports complex SQL queries across multiple datasets (including Parquet files in ADLS Gen2 and Azure SQL Database), offers built-in row-level security (RLS) and column-level security for enterprise-grade access control, and includes a built-in pipeline orchestration engine (via Synapse Pipelines) for ETL/ELT transformations into a curated layer. This single service eliminates the need to stitch together separate tools for querying, security, and data transformation.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 11

A retail company needs to analyze clickstream data from their website in real time to detect fraudulent activity and also run complex historical queries on months of data to identify shopping trends. They want a single service that can handle both streaming and batch analytics using a unified query language, minimizing data movement. Which Azure service should they use?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for real-time analytics on streaming data and can also handle complex historical queries over large volumes of data using the Kusto Query Language (KQL). It minimizes data movement by ingesting streaming data directly and storing it in a columnar format optimized for both real-time and batch queries, making it the ideal single service for this scenario.

Answer

Azure Stream Analytics

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Question 12

A retail company collects streaming clickstream data from its website into Azure Event Hubs. They need to aggregate the data in real-time to count page views per product every minute and store the results in Azure SQL Database for a live dashboard. Which Azure service should they use to perform this real-time aggregation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is purpose-built for real-time stream processing, allowing you to define a query that aggregates clickstream data from Event Hubs using a tumbling window of one minute to count page views per product. The result can be written directly to Azure SQL Database, enabling a live dashboard without additional orchestration.

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Answer

Azure Databricks

Question 13

A retail company receives daily sales data as CSV files in Azure Data Lake Storage Gen2. They need to load this data into an Azure Synapse Analytics dedicated SQL pool every night. The process must be automated, scheduled, and include error handling for failed loads. Which Azure service should they use to orchestrate this pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data integration service designed specifically for orchestrating and automating data pipelines. It supports scheduled triggers, can copy CSV files from Azure Data Lake Storage Gen2 into an Azure Synapse dedicated SQL pool, and provides built-in error handling via retry policies, activity-level error outputs, and pipeline failure notifications.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Logic Apps

Question 14

A manufacturing company collects real-time temperature data from thousands of IoT sensors. They need to build an analytics solution that processes the streaming data, computes the average temperature per device every minute, and outputs the results to a Power BI dashboard for near real-time visualization. Which Azure service should they use for the real-time stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed specifically for scenarios like this: ingesting high-velocity data from IoT sensors, performing time-windowed aggregations (e.g., average temperature per device every minute), and outputting results directly to Power BI for near real-time dashboards. It natively supports SQL-like query language for defining windowed computations and has built-in connectors for both IoT Hub/Event Hubs (input) and Power BI (output), making it the most efficient and purpose-built service for this streaming analytics workload.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure SQL Database

Question 15

A retail company receives a continuous stream of customer orders from their website via Azure Event Hubs. They also receive daily inventory updates from suppliers as CSV files uploaded to Azure Blob Storage. The company needs to calculate real-time order fulfillment availability by joining the streaming orders with the latest inventory snapshot. Additionally, they generate nightly sales reports from historical order data. Which Azure service should they use for the real-time processing component?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time data processing, allowing you to join streaming data from Event Hubs with static or reference data (like the latest inventory snapshot from Blob Storage) using SQL-like queries. This enables the calculation of real-time order fulfillment availability as orders arrive, which is the core requirement.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Pipelines