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

Question 1

You are processing CSV files in Azure Synapse Analytics serverless SQL pool. The files contain newline characters within quoted fields, causing parsing errors. Which file format option should you specify to handle this correctly?

Accepted Answer

Use PARSER_VERSION = '2.0'. Option D is correct because PARSER_VERSION = '2.0' in Azure Synapse serverless SQL pool supports quoted fields that contain embedded newline characters (CR/LF). The parser version 2.0 is designed to handle CSV files with complex quoting rules, including newlines within quoted fields, which the older parser version 1.0 cannot parse correctly.

Answer

Use ROWTERMINATOR = '0x0a'

Answer

Use FORMAT = 'CSV'

Answer

Use FIELDQUOTE = '"'

Question 2

You have a pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection closed' error after 1 hour. The data volume is 500 GB over a slow network. What is the most likely cause and solution?

Accepted Answer

The source SQL Server query command timeout is set to 1 hour; increase the command timeout or use staging copy.. The 'Connection closed' error after exactly 1 hour indicates the SQL Server source query command timeout (default 60 minutes) is being hit. When copying 500 GB over a slow network, the query execution exceeds this timeout, causing the connection to drop. Increasing the command timeout or using staging copy (which breaks the query into chunks) resolves this by allowing the query to run longer or reducing the per-query duration.

Answer

The network bandwidth is insufficient; use a faster network connection.

Answer

The copy activity is using a single parallel copy; increase the degree of copy parallelism.

Answer

The Self-Hosted Integration Runtime (SHIR) has a time-to-live (TTL) setting that is too short; extend the TTL.

Question 3

You are designing a data processing solution that uses Azure Databricks to transform large datasets. You need to ensure that the processing is cost-effective and can scale to handle variable workloads. Which cluster configuration should you recommend?

Accepted Answer

Use an auto-scaling cluster with spot instances.. Option A is correct because auto-scaling clusters in Azure Databricks dynamically adjust the number of workers based on workload demands, ensuring cost-effectiveness by scaling down during low activity. Spot instances (Azure Spot VMs) further reduce costs by using unused Azure capacity at a significant discount, making this combination ideal for variable workloads where fault tolerance is acceptable.

Answer

Use a fixed-size cluster with premium tier.

Answer

Use a Photon-accelerated cluster with premium tier.

Answer

Use an interactive cluster with a large number of workers.

Question 4

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.. Option D is correct because 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 5

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 6

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 7

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.. Option A is correct because 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 8

You are designing a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow with Azure Integration Runtime (IR). The pipeline runs slowly and you notice that the IR's data movement is limited by the number of cores. Which configuration should you adjust to improve performance?

Accepted Answer

Increase the 'Data Flow Compute Type' and 'Core Count' in the Azure IR settings.. The Azure Integration Runtime (IR) for mapping data flows uses a Spark cluster, and its performance is directly tied to the compute resources allocated. By increasing the 'Data Flow Compute Type' (e.g., from General Purpose to Memory Optimized) and the 'Core Count' (e.g., from 4 to 8 or 16 cores), you provide more parallel processing power, which directly addresses the core-limited data movement bottleneck.

Answer

Enable staging for the copy activity within the data flow.

Answer

Use a Self-Hosted IR instead of Azure IR for data flows.

Answer

Increase the 'Number of partitions' in the source transformation.

Question 9

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 Synapse SQL pool (formerly SQL DW) is a dedicated or serverless SQL engine within Azure Synapse Analytics that allows you to run T-SQL queries for data transformation, loading, and querying. It is a first-class compute resource designed for large-scale data warehousing workloads, making T-SQL queries a valid and primary method for processing data in Synapse.

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 10

Which THREE are required to run a stored procedure in Azure SQL Database from Azure Data Factory?

Accepted Answer

A linked service to Azure SQL Database.. A linked service to Azure SQL Database is required because it defines the connection string and authentication method (e.g., SQL authentication or managed identity) that Azure Data Factory (ADF) uses to connect to the database. Without this connection configuration, ADF cannot authenticate or communicate with the database to execute the stored procedure.

Answer

A self-hosted integration runtime.

Answer

A staging blob storage account.

Question 11

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.. Azure Databricks Auto Loader is designed to incrementally ingest new data files as they arrive in cloud storage (e.g., Azure Data Lake Storage Gen2 or Blob Storage) by using a notification-based or directory-listing approach. It automatically tracks which files have already been processed using a structured streaming checkpoint mechanism, eliminating the need for manual checkpoint management even at high data volumes. This makes options A and B correct because Auto Loader's core value is its ability to discover and process new files without manual intervention and to handle large-scale ingestion reliably.

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 12

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 13

You are troubleshooting a slow-running pipeline in Azure Data Factory. The pipeline copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. The copy activity is using the 'Auto' copy method. You notice that network bandwidth is limited. Which configuration change would most likely improve performance?

Accepted Answer

Enable staging using Azure Blob Storage and use PolyBase to load into Synapse. Option A is correct because when network bandwidth is limited, staging data in Azure Blob Storage allows the copy activity to use PolyBase, which leverages Azure's internal high-speed network for the final load into Synapse. This bypasses the constrained on-premises-to-cloud link for the bulk of the data transfer, significantly improving throughput.

Answer

Increase the Data Integration Units (DIU) for the copy activity

Answer

Change the copy method to 'Bulk insert'

Answer

Set the Fault Tolerance option to skip incompatible rows

Question 14

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'). Option B is correct because 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 15

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