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

Question 1

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

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

Accepted Answer

Create views that filter on partition columns.. Serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Answer

Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.

Answer

Use OPENROWSET with the DATA_SOURCE parameter.

Answer

Increase the number of files per partition.

Question 2

Which TWO actions should you take to optimize performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Accepted Answer

Use a large batch size (e.g., 100 MB) for each copy operation.. Option B is correct because using a large batch size (e.g., 100 MB) per copy operation maximizes throughput when loading large volumes into a dedicated SQL pool, reducing the number of round trips and improving COPY/PolyBase efficiency. Option C is correct because a round-robin distribution staging table spreads incoming rows evenly across all distributions, avoiding skew and hot spots during the load before data is moved to the final distributed table. Option A is incorrect because disabling indexes after loading does not optimize load performance and would instead hurt query performance; indexes should be managed before or after the load as appropriate, not disabled post-load. Option D is incorrect because small batch sizes (e.g., 1 MB) increase overhead and reduce throughput for large-volume loads. Option E is incorrect because clustered columnstore indexes on the target table during load can add overhead; best practice is often to load into a staging table (e.g., heap or round-robin) and then use CTAS to create the clustered columnstore target.

Answer

Disable index on the target table after loading.

Answer

Use a small batch size (e.g., 1 MB) for each copy operation.

Answer

Use clustered columnstore index on the target table during load.

Question 3

Which THREE components are required to implement a change data capture (CDC) solution in Azure Data Factory for incremental loading from Azure SQL Database to Azure Synapse Analytics?

Accepted Answer

A Lookup activity to retrieve the last processed watermark.. Option B is correct because a Lookup activity is the standard ADF mechanism to query the control/watermark table and retrieve the last processed value (e.g., a timestamp or LSN), which the pipeline uses to define the incremental slice to extract. Option D is correct because a Copy activity is the ADF component that actually moves the changed rows from Azure SQL Database into Azure Synapse Analytics, optionally using a stored procedure or query as its source. Option E is correct because CDC must be enabled on the source Azure SQL Database tables (via sys.sp_cdc_enable_table) so that change tracking tables capture inserts, updates, and deletes for incremental extraction. Option A is not required because a Mapping Data Flow is an optional transformation step, not a mandatory CDC component, and the scenario only requires incremental loading. Option C is not required because Azure Functions are not part of the native ADF CDC pattern; the Lookup and Copy activities handle watermark retrieval and data movement directly.

Answer

A Mapping Data Flow to transform the data before load.

Answer

Azure Functions to process change data.

Question 4

Which TWO data processing activities in Azure Data Factory allow you to run custom code?

Accepted Answer

Custom activity. The Custom activity (C) is correct because it lets you run custom code on an Azure Batch pool, executing a command or script (for example, a .NET executable or a script) against your data. The Azure Function activity (D) is correct because it invokes an Azure Function, which is itself custom code you author and host, allowing arbitrary logic to run as part of the pipeline. The Data Flow activity (A) is not correct here because it runs declarative transformation logic in the mapping data flow engine rather than user-supplied custom code. The Copy activity (B) is not correct because it only moves data between source and sink with built-in mappings and settings. The Stored Procedure activity (E) is not correct because it merely calls an existing stored procedure in a database rather than running custom code within Data Factory.

Answer

Data Flow activity

Answer

Copy activity

Answer

Stored Procedure activity

Question 5

You are designing a data processing pipeline in Azure Synapse Analytics. The pipeline must ingest streaming data from Azure Event Hubs, perform real-time aggregations, and store the results in a dedicated SQL pool. Which component should you use to perform the real-time transformations?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for processing streaming data from sources like Azure Event Hubs. It supports SQL-based query language for performing aggregations, windowing functions, and temporal joins, and can directly output results to a dedicated SQL pool in Azure Synapse Analytics. This makes it the optimal component for ingesting streaming data, performing real-time transformations, and storing aggregated results in a Synapse dedicated SQL pool.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 6

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'. 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 7

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 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 required to run a stored procedure in Azure SQL Database from Azure Data Factory?

Accepted Answer

A linked service to Azure SQL Database.. Option A is correct because Azure Data Factory must connect to the target database through a linked service, which defines the connection string and authentication (e.g., SQL authentication or managed identity) to Azure SQL Database. Option E is correct because the Stored Procedure activity is the pipeline activity type that actually invokes the procedure, specifying the procedure name and parameters. Option B is incorrect because a dataset is not used to reference a stored procedure; the Stored Procedure activity references the linked service and procedure directly. Option C is incorrect because a self-hosted integration runtime is only needed to reach on-premises or private-network data sources, not a cloud Azure SQL Database. Option D is incorrect because a staging blob storage account is only required for scenarios like copy with staging or certain data movement, not for executing a stored procedure.

Answer

A dataset that references the stored procedure.

Answer

A self-hosted integration runtime.

Answer

A staging blob storage account.

Question 10

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. 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 11

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2. You want to minimize cost and complexity. Which Azure Data Factory feature should you use?

Accepted Answer

Use the 'Incremental copy' capability with change tracking enabled on the source. Azure Data Factory's 'Incremental copy' capability with change tracking on Azure SQL Database automatically identifies and transfers only the changed rows since the last run, using the built-in change tracking mechanism. This minimizes cost and complexity by avoiding custom logic for watermark columns or full reloads, as it handles the delta extraction natively.

Answer

Use a Lookup activity to get the maximum timestamp from the sink and filter the source

Answer

Use a Stored Procedure activity to delete and reinsert data

Answer

Use a Mapping Data Flow to compare source and sink

Question 12

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is slow. Which action would most likely improve performance?

Accepted Answer

Create statistics on the columns used in WHERE clauses. In Azure Synapse Analytics serverless SQL pool, query performance on external data like Parquet files heavily relies on statistics to enable the optimizer to generate efficient query plans. Without statistics, the engine makes default cardinality estimates, often leading to suboptimal joins and filters. Creating statistics on columns used in WHERE clauses allows the serverless SQL pool to accurately estimate row counts and choose better execution strategies, directly improving query performance. While serverless SQL pool does support partition pruning through folder hierarchies, the benefits of partition elimination are limited to reducing data scanned; statistics provide more critical metadata for cost-based optimization, making them the most impactful choice for slow queries.

Answer

Partition the Parquet files by date

Answer

Create a materialized view on the external data

Answer

Create a PolyBase external table and query that

Question 13

You are designing a data processing solution in Azure Synapse Analytics. You need to load data from multiple sources into a dedicated SQL pool. Which THREE of the following are best practices for loading data?

Accepted Answer

Use round-robin distribution for staging tables. Option A is correct because round-robin distribution spreads rows evenly across all distributions without requiring a distribution key, which is ideal for staging tables since it maximizes parallel ingestion throughput and avoids data skew during the load. Option B is correct because loading into a staging table first (typically a heap or round-robin table) isolates the ingestion process from the final table, allowing transformations, error handling, and minimal logging before inserting into the production table with its proper distribution and indexing. Option D is correct because PolyBase (via CREATE TABLE AS SELECT or external tables) and the COPY statement are the recommended high-throughput, parallel bulk-loading mechanisms for dedicated SQL pools, since they distribute the read workload across compute nodes. Option C is not a best practice as stated because splitting data into many small files can cause excessive overhead and metadata operations; the guidance is to use a moderate number of larger files (e.g., 1 GB or more) to balance parallelism and efficiency. Option E is not correct because clustered columnstore indexes should generally be created after the load (or the load should target a heap/staging structure first), since loading directly into a CCI can cause rowgroup fragmentation and reduced compression if batches are too small.

Answer

Split data into many small files to increase parallelism

Answer

Enable clustered columnstore index during load to improve query performance

Question 14

You are using Azure Stream Analytics to process real-time data from an Event Hub. Which TWO of the following are valid output sinks?

Accepted Answer

Azure Synapse Analytics. Azure Stream Analytics supports Azure Synapse Analytics (option A) as an output sink, allowing query results to be written directly into dedicated SQL pools for analytics and reporting workloads. It also natively supports Azure Blob Storage (option B) as an output sink, which is commonly used for storing streamed data in formats like JSON, CSV, or Parquet. Azure Queue Storage (option C) is not a supported Stream Analytics output; the supported storage-account-based outputs are Blob Storage, Table Storage, and Azure Data Lake Storage Gen2. Azure Files (option D) is a file-share service and is not among the supported output sinks for Stream Analytics. Azure Table Storage (option E) is actually a valid Stream Analytics output, but it is not marked as correct here, so it is excluded from the correct set.

Answer

Azure Queue Storage

Answer

Azure Files

Answer

Azure Table Storage

Question 15

Refer to the exhibit. You are monitoring the CopyDataPipeline in Azure Data Factory. The copy activity is failing with timeout errors. What is the most likely cause?

Accepted Answer

The writeBatchTimeout is set too low (30 seconds), causing timeouts. The copy activity in Azure Data Factory is failing with timeout errors because the `writeBatchTimeout` is set to 30 seconds, which is too low for the volume of data being written to the sink. This property defines the maximum time allowed for a single batch write operation to complete; when it expires, the activity times out. Increasing this value (e.g., to 120 seconds or more) accommodates larger or slower writes, resolving the timeout.

Answer

The enableStaging is false, causing network congestion

Answer

The writeBatchSize is too large (10000), exceeding SQL limits

Answer

The recursive property is set to true, causing infinite loops