DP-900 Practice Test 41 — 15 Questions

Question 1

A data analytics team stores sales transaction data in Parquet files in Azure Data Lake Storage Gen2. They want to run complex analytical queries that join this data with dimension tables stored in Azure Synapse Analytics dedicated SQL pool. The team prefers not to move or copy the data from the data lake. Which feature should they use to query the data lake data directly?

Accepted Answer

PolyBase external tables. PolyBase external tables in Azure Synapse Analytics dedicated SQL pool allow you to query data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) directly using T-SQL, without moving or copying the data. This is the correct feature because it enables complex analytical joins between the Parquet files in the data lake and the dimension tables in the dedicated SQL pool, leveraging the external table's ability to read Parquet format natively.

Answer

Azure Data Factory pipelines

Answer

Azure Stream Analytics

Answer

Azure Databricks notebooks

Question 2

A financial analytics company has petabytes of transaction data stored as Parquet files in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple tables and return results within seconds. The company wants to query the data directly without moving it to another store. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without moving or loading the data. It uses a distributed query engine that can process petabytes of Parquet files and return results in seconds by leveraging pushdown computation and columnar storage formats.

Answer

Azure SQL Database

Answer

Azure HDInsight

Answer

Azure Databricks

Question 3

A data engineer needs to build an analytics solution to transform large volumes of streaming data from IoT devices. The transformations involve complex Python and Spark code, and the results will be stored in Azure Data Lake Storage Gen2 for further analysis. Which Azure service is best suited for executing these transformations?

Accepted Answer

Azure Databricks. Azure Databricks is best suited because it provides an Apache Spark-based analytics platform that can execute complex Python and Spark code on large-scale streaming data. It integrates natively with Azure Data Lake Storage Gen2 for reading streaming IoT data and writing transformed results, offering optimized performance for big data transformations.

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Answer

Azure Analysis Services

Question 4

A data engineering team needs to build a batch ETL pipeline that transforms large volumes of clickstream data stored as CSV files in Azure Data Lake Storage Gen2. The transformations require running distributed Python and Scala code using Apache Spark. The transformed data will be loaded into a data warehouse for reporting. The team wants a serverless compute environment that automatically scales and charges per second. Which Azure service should they use to run the Spark transformations?

Accepted Answer

Azure Synapse Analytics (Spark pools). Azure Synapse Analytics (Spark pools) is the correct choice because it provides a serverless Apache Spark compute environment that automatically scales and charges per second, perfectly matching the requirement for running distributed Python and Scala transformations on large volumes of clickstream data stored in Azure Data Lake Storage Gen2. The service integrates directly with the data lake and can load transformed results into a dedicated SQL pool for data warehouse reporting.

Answer

Azure Data Factory

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 5

You are deploying a high-availability solution for an Azure SQL Database that must have a Recovery Point Objective (RPO) of less than 5 seconds. Which deployment option meets this requirement?

Accepted Answer

Azure SQL Database Business Critical tier with zone redundancy. Option C is correct because Azure SQL Database Business Critical tier with zone redundancy provides synchronous replication achieving RPO of 0-5 seconds. Option A is wrong because active geo-replication has asynchronous replication. Option B is wrong because Azure SQL Database Hyperscale has asynchronous replication. Option D is wrong because Azure SQL Managed Instance General Purpose has asynchronous replication.

Answer

Azure SQL Database Hyperscale tier

Answer

Azure SQL Managed Instance General Purpose tier

Answer

Azure SQL Database with active geo-replication

Question 6

A data engineering team is building a batch analytics pipeline. Raw clickstream data is stored as Parquet files in Azure Data Lake Storage Gen2. The team needs to transform the data using Apache Spark (Python code) and then load the results into Azure Synapse Analytics for high-performance reporting. They want to use a serverless compute option for Spark to avoid managing clusters. Which combination of Azure services should they use for the transformation and loading?

Accepted Answer

Use Azure Synapse Analytics serverless Spark pools for transformations and load into the Synapse dedicated SQL pool.. Option B is correct because Azure Synapse Analytics serverless Spark pools provide a serverless compute option for running Apache Spark transformations without managing clusters, and the transformed data can be directly loaded into the Synapse dedicated SQL pool for high-performance reporting. This combination meets all requirements: serverless Spark for transformations, and Synapse dedicated SQL pool for optimized analytics workloads.

Answer

Use Azure Databricks with a serverless cluster for transformations and load into Azure SQL Database.

Answer

Use Azure Data Factory with a Spark activity to run transformations and load into Azure Synapse Analytics.

Answer

Use Azure HDInsight with Apache Spark for transformations and load into Azure Blob Storage.

Question 7

A financial institution needs to run complex queries against petabytes of historical trading data stored in Azure Data Lake Storage. The queries must be efficient and use columnar storage format. Which technology should they use to process this data?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a cloud-based analytics service designed for petabyte-scale data warehousing. It supports PolyBase to query data directly from Azure Data Lake Storage and uses a columnar storage format (via clustered columnstore indexes) to enable efficient, high-performance analytical queries on massive historical datasets.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Question 8

A financial services company needs to build a data pipeline that ingests daily transaction files from multiple sources. The pipeline must perform data quality checks, transform data using complex business logic, and load it into Azure Synapse Analytics. The transformations involve conditional branching (e.g., if a transaction amount exceeds a threshold, apply additional validation). The company wants to minimize coding effort and prefers a visual, configuration-based approach. Which Azure service should they use as the primary orchestration and transformation engine?

Accepted Answer

Azure Data Factory with Data Flows. Azure Data Factory with Data Flows is the correct choice because it provides a visual, configuration-based interface for both orchestration and transformation, including support for complex business logic like conditional branching (e.g., via conditional split transformations). It natively integrates with Azure Synapse Analytics for loading transformed data, minimizing coding effort compared to code-heavy alternatives.

Answer

Azure Databricks with notebooks

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Question 9

A retail company ingests daily sales data from multiple stores as CSV files stored in Azure Blob Storage. The data must be cleaned and transformed using Spark, then loaded into Azure Synapse Analytics for large-scale reporting. The pipeline must run on a schedule, handle failures with retries, and minimize manual intervention. Which combination of Azure services should they use to orchestrate and execute this pipeline?

Accepted Answer

Azure Data Factory, Azure Databricks, and Azure Synapse Analytics.. Option A is correct because Azure Data Factory provides the orchestration and scheduling layer, Azure Databricks executes the Spark-based cleaning and transformation, and Azure Synapse Analytics serves as the target data warehouse for large-scale reporting. This combination supports retry policies for failure handling and minimizes manual intervention through automated pipeline execution.

Answer

Azure Stream Analytics, Azure Data Lake Storage, and Power BI.

Answer

Azure Functions, Azure SQL Database, and Azure Analysis Services.

Answer

Azure Logic Apps, Azure HDInsight, and Azure Cosmos DB.

Question 10

A multinational corporation is deploying a global application using Azure SQL Database. They need to ensure that users in different geographic regions experience low latency reads. The application can tolerate slightly stale data for reads, but writes must be strongly consistent and must occur in a single primary region. Which feature should they implement?

Accepted Answer

Active geo-replication. Option C is correct because active geo-replication allows creating readable secondaries in other regions for low-latency reads, while writes go to the primary. Option A is wrong because failover groups provide automatic failover but not necessarily low-latency reads from multiple regions. Option B is wrong because Cosmos DB with multi-master is NoSQL and not relational. Option D is wrong because read scale-out uses local replicas, not global.

Answer

Azure Cosmos DB with multi-master

Answer

Failover groups

Answer

Read scale-out

Question 11

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?

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 12

A manufacturing company ingests real-time sensor data from assembly line machines into Azure Event Hubs. The company needs to calculate a 5-minute rolling average of temperature readings for each machine and compare it against a static threshold value stored in a CSV file in Azure Blob Storage. If the average exceeds the threshold, an alert must be triggered. Which Azure service should be used for this real-time data processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, including windowed aggregations like a 5-minute rolling average. It can directly ingest data from Azure Event Hubs, perform the calculation using a TumblingWindow or HoppingWindow function, and reference static data (the threshold CSV) from Azure Blob Storage via a reference data input. If the computed average exceeds the threshold, Stream Analytics can output the alert to a sink like Azure Functions or a notification service.

Answer

Azure Data Factory

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Question 13

A financial analytics company stores petabytes of transaction data in Parquet files in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple large tables and return results within seconds. The company also wants to integrate with Power BI for visualization and Azure Data Factory for ETL orchestration. They require a massively parallel processing (MPP) engine to handle the scale. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is the correct choice because it provides a massively parallel processing (MPP) engine that distributes data across 60 distributions, enabling fast execution of complex SQL queries on petabyte-scale data stored in Parquet files in Azure Data Lake Storage Gen2. It natively integrates with Power BI for visualization and Azure Data Factory for ETL orchestration, meeting all stated requirements.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Analysis Services

Question 14

A financial institution runs complex analytical queries on trading data stored in Parquet files in Azure Data Lake Storage Gen2. The data is partitioned by date and contains billions of rows. Analysts frequently query within a specific date range, and the queries must return results in under 5 seconds. The current solution uses Azure Synapse Serverless SQL pool, but queries are slow because the serverless pool scans all partitions even when the WHERE clause filters on the date column. Which optimization should be implemented to improve query performance?

Accepted Answer

Switch to Azure Synapse dedicated SQL pool with proper table partitioning. Azure Synapse Serverless SQL pool does not support partition elimination based on the partitioning of the underlying Parquet files in Azure Data Lake Storage Gen2. By switching to an Azure Synapse dedicated SQL pool with proper table partitioning on the date column, the query engine can perform partition pruning, scanning only the relevant partitions for the specified date range, which drastically reduces I/O and improves query performance to meet the sub-5-second requirement.

Answer

Create a clustered columnstore index on the external table

Answer

Convert the Parquet files to CSV format

Answer

Use Azure Databricks with Delta Lake for querying

Question 15

Which TWO of the following are benefits of using Azure SQL Database over SQL Server on Azure Virtual Machines?

Accepted Answer

Built-in high availability with automatic failover. Options B and D are correct. Azure SQL Database automates patching and provides built-in high availability. Option A is incorrect because Azure SQL Database does not provide full control over the OS. Option C is incorrect because you cannot install custom software. Option E is incorrect because you cannot configure the exact number of vCores in some tiers, but that is not a benefit over VMs.

Answer

Lower cost because you can choose any number of vCores

Answer

Ability to install custom software on the database server

Answer

Full control over the operating system