DP-203 Develop data processing Practice Test 18 — 15 Questions

Question 1

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query returns fewer rows than expected. What should you check first?

Accepted Answer

Verify the file path and pattern in the OPENROWSET query.. When using OPENROWSET in Azure Synapse serverless SQL pool to query Parquet files, the most common reason for fewer rows than expected is an incorrect file path or pattern. If the path or pattern is too restrictive (e.g., missing a wildcard or pointing to a subfolder instead of the root), the query will only read a subset of the files, resulting in fewer rows. This is the first thing to verify before investigating schema or permissions issues.

Answer

Ensure the external table has the correct schema definition.

Answer

Check that the Azure AD identity has read permissions on the storage account.

Answer

Check the compression codec used in the Parquet files.

Question 2

You are monitoring an Azure Stream Analytics job that processes data from an IoT hub. The job's output to Azure Synapse Analytics is experiencing high latency. The job's SU% utilization is at 90%. Which action will most likely reduce the latency?

Accepted Answer

Increase the number of Streaming Units (SUs) allocated to the job.. The job's SU% utilization is at 90%, indicating that the current Streaming Units (SUs) are nearly saturated, causing a processing bottleneck. Increasing the number of SUs allocates more compute resources (CPU and memory) to the Stream Analytics job, allowing it to process incoming IoT data faster and reduce the latency to Azure Synapse Analytics. This directly addresses the high utilization issue, which is the most likely root cause of the latency.

Answer

Decrease the watermark delay interval.

Answer

Increase the late arrival tolerance window.

Answer

Increase the number of partitions in the output table.

Question 3

You have an Azure Data Factory pipeline that executes a stored procedure in Azure SQL Database. The pipeline fails with an error indicating that the stored procedure ran out of memory. What change should you make to the pipeline to resolve this?

Accepted Answer

Scale up the Azure SQL Database to a higher service tier.. The error indicates that the stored procedure ran out of memory, which is a resource limitation at the database level, not a transient failure or timeout issue. Scaling up the Azure SQL Database to a higher service tier (e.g., from Standard to Premium or increasing DTU/vCore count) provides more memory and compute resources, directly resolving the out-of-memory condition.

Answer

Add a retry policy to the stored procedure activity.

Answer

Increase the pipeline activity timeout.

Answer

Use a Self-Hosted Integration Runtime instead of Azure IR.

Question 4

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2 using Azure Data Factory. Which approach is the most efficient?

Accepted Answer

Use a lookup activity to retrieve the last watermark value, then copy only new records with a filter.. It uses a lookup activity to retrieve the last watermark value (e.g., a timestamp or incrementing key), then copies only new or changed records via a filter in the Copy activity. This minimizes data movement and processing time, making it the most efficient incremental loading approach in Azure Data Factory.

Answer

Use a tumbling window trigger with a data flow that processes all data each time.

Answer

Use a mapping data flow with a full load and then use Azure Databricks to deduplicate.

Answer

Copy the entire table every time and use Azure Synapse serverless SQL to filter duplicates.

Question 5

Which TWO are valid ways to process data in Azure Synapse Analytics?

Accepted Answer

Use Synapse SQL pool to run T-SQL queries.. Option C is correct because a Synapse SQL pool (dedicated or serverless) is a core Synapse Analytics engine that executes T-SQL queries directly against data in the workspace, making it a valid data-processing method. Option E is correct because Synapse Spark notebooks run Apache Spark code, including Scala, Python, SQL, and R, and are a first-class way to transform and process data in Azure Synapse Analytics. Option A is not appropriate because Logic Apps is a workflow/automation service for orchestrating triggers and connectors, not a data transformation engine. Option B is not the intended Synapse processing method because Azure Functions is a separate serverless compute service outside Synapse's built-in SQL and Spark engines. Option D is not correct because Power BI is a visualization and reporting tool; its data transformation (Power Query) is for modeling/reporting, not a Synapse data-processing workload.

Answer

Use Logic Apps to run data transformations.

Answer

Use Azure Functions to process data in a serverless manner.

Answer

Use Power BI to transform data.

Question 6

Which TWO are benefits of using Azure Databricks Auto Loader for incremental data ingestion?

Accepted Answer

It can process new files as they arrive in cloud storage.. Option A is correct because Auto Loader is designed to incrementally and efficiently detect and process new files as they land in cloud storage (e.g., Azure Data Lake Storage or Blob Storage) using file notification or directory listing modes, making it ideal for streaming ingestion of arriving data. Option B is correct because Auto Loader manages the ingestion state, including checkpoints and the schema/state tracking, so it can scale to large volumes of files without requiring manual checkpoint management by the user. Option C is not correct because while Auto Loader supports schema inference and schema evolution, evolution typically requires enabling and configuring options such as cloudFiles.schemaEvolutionMode and related settings, so it is not automatic without any configuration. Option D is not correct because Auto Loader is a file-based incremental ingestion mechanism and does not guarantee sub-second latency real-time streaming. Option E is not correct because Auto Loader does not provide built-in record-level deduplication; deduplication must be implemented separately, for example with dropDuplicates or Delta Lake MERGE logic.

Answer

It automatically evolves the schema without any configuration.

Answer

It provides sub-second latency for real-time streaming.

Answer

It provides built-in deduplication of records.

Question 7

You are designing a data pipeline to ingest streaming data from IoT devices into Azure Synapse Analytics. The data must be available for querying with minimal latency, but you also need to handle spikes in throughput without data loss. Which service should you use as the ingestion layer?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct choice because it is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from millions of IoT devices. It provides at-least-once delivery, supports partitioning for massive scale, and integrates natively with Azure Synapse Analytics via the Synapse Pipeline or Event Hubs Capture to handle throughput spikes without data loss.

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Answer

Azure IoT Hub

Question 8

You need to transform data in Azure Databricks using Apache Spark. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. Which method should you use to read the data into a Spark DataFrame?

Accepted Answer

spark.read.format('delta').load('abfss://container@storage.dfs.core.windows.net/path'). The data is stored in Delta Lake format, which requires using the 'delta' format reader in Spark to properly read the transaction log and schema. The `spark.read.format('delta').load()` method is the standard way to read Delta tables, leveraging the Delta Lake protocol for ACID transactions and time travel capabilities.

Answer

spark.read.parquet('abfss://container@storage.dfs.core.windows.net/path')

Answer

spark.read.csv('abfss://container@storage.dfs.core.windows.net/path')

Answer

spark.read.json('abfss://container@storage.dfs.core.windows.net/path')

Question 9

You are designing a data processing solution for a global company. Data must be processed in near real-time and aggregated by region. You need to minimize latency for downstream consumers. Which Azure service should you use for stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed for near real-time analytics on high-volume data streams. It can ingest data from sources like Azure Event Hubs or IoT Hub, apply SQL-based transformations, and output aggregated results to sinks such as Azure Synapse or Power BI with sub-second latency, meeting the requirement for minimal downstream latency.

Answer

Azure Batch

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 10

You are optimizing a pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure Synapse Analytics. The pipeline uses a copy activity with PolyBase. The data is partitioned by date in Blob Storage. You notice that the load is slow. What is the most likely cause?

Accepted Answer

The source files are too many and too small (e.g., thousands of 1 MB files). PolyBase in Azure Synapse Analytics performs best when reading large, contiguous files. When the source contains thousands of small files (e.g., 1 MB each), PolyBase must initiate a separate read operation for each file, causing excessive overhead from file open/close operations and metadata requests. This dramatically reduces throughput compared to reading fewer, larger files.

Answer

The source files are stored in Azure Blob Storage instead of Data Lake Storage Gen2

Answer

The source files are in CSV format instead of Parquet

Answer

The sink table has a clustered columnstore index

Question 11

You are designing a data processing solution using Azure Databricks with Delta Lake. You need to ensure ACID transactions and schema enforcement. Which feature should you enable?

Accepted Answer

Delta Lake format. Delta Lake is the correct choice because it provides ACID transactions (atomicity, consistency, isolation, durability) and schema enforcement (schema-on-write) on top of cloud storage like Azure Data Lake Storage. These features are inherent to the Delta Lake format, which uses a transaction log to track changes and enforce data integrity, making it ideal for reliable data processing in Azure Databricks.

Answer

Auto Loader

Answer

Photon engine

Answer

Unity Catalog

Question 12

You are running a pipeline in Azure Data Factory that uses a Mapping Data Flow. The data flow reads from Azure SQL Database and writes to Azure Synapse Analytics. You find that the data flow is very slow. Which configuration change would most likely improve performance?

Accepted Answer

Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'. Mapping Data Flows in Azure Data Factory execute on Spark clusters. The default compute configuration may not provide sufficient memory or parallelism for large data volumes. Increasing the 'Compute type' to 'Memory Optimized' and raising the 'Core count' directly allocates more memory and processing cores to the Spark cluster, which accelerates transformations and data movement between Azure SQL Database and Azure Synapse Analytics.

Answer

Set the 'Staging' option to 'Use staging'

Answer

Enable staging for the sink and use PolyBase

Answer

Set the 'Partition option' to 'Round robin' on the source

Question 13

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Accepted Answer

Pivot. In Azure Data Factory Mapping Data Flows, the Pivot transformation (A) is a built-in transformation that reshapes data by turning unique row values into columns, which is why it is correct. The Derived Column transformation (B) is also available and is used to create new columns or modify existing ones using expression builder logic, making it correct. The Aggregate transformation (D) is likewise a native Mapping Data Flows transformation that performs group-by operations and aggregations such as SUM, AVG, COUNT, and MIN/MAX, so it is correct. Union All (C) is not a Mapping Data Flows transformation; combining multiple streams is done with the Union transformation, which behaves like a union (not specifically 'Union All' as a named transformation). Merge Join (E) is not a Mapping Data Flows transformation either; joins are performed with the Join transformation, and the term 'Merge Join' refers to a SQL Server/SSIS-style operator rather than an ADF Mapping Data Flows transformation.

Answer

Union All

Answer

Merge Join

Question 14

You are designing a data transformation solution for a retail company. The company receives daily CSV files from 200 stores via SFTP. The files must be cleaned, validated, and aggregated before loading into Azure Synapse dedicated SQL pool. The solution must minimize administrative overhead and support easy monitoring. Which approach do you recommend?

Accepted Answer

Use Azure Data Factory with Mapping Data Flows to clean, validate, and aggregate the data, then load into Synapse SQL pool. Azure Data Factory (ADF) with Mapping Data Flows provides a fully managed, code-free ETL service that can read CSV files from SFTP, perform cleaning, validation, and aggregation at scale using Spark clusters, and load the results directly into Azure Synapse dedicated SQL pool via the PolyBase sink. This minimizes administrative overhead by eliminating infrastructure management and supports easy monitoring through ADF’s built-in integration with Azure Monitor and pipeline run views.

Answer

Use Azure Functions to process each file and write to Synapse via REST API

Answer

Use PolyBase external tables to load raw data and then use T-SQL stored procedures for transformation

Answer

Use Azure Databricks with Python notebooks to process the files and write to Synapse

Question 15

You have a pipeline in Azure Data Factory that copies data from on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection timed out' error. You have already verified that the Integration Runtime is running and the SQL Server firewall allows connections from the Integration Runtime. What should you check next?

Accepted Answer

Check if the SQL Server is configured to allow remote connections and that TCP/IP is enabled. The 'Connection timed out' error, despite the Integration Runtime being running and the firewall allowing connections, typically indicates that SQL Server is not listening on the expected TCP port. This often happens when TCP/IP is disabled in SQL Server Configuration Manager or remote connections are not enabled. Without TCP/IP enabled, the Integration Runtime cannot establish a network connection to the SQL Server instance, leading to a timeout.

Answer

Ensure the Integration Runtime is registered and online

Answer

Check if the Blob Storage endpoint is accessible from the Integration Runtime

Answer

Verify that the SQL Server login credentials are correct