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

Question 1

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?

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

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 2

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 3

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 4

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 5

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. Round-robin distribution is recommended for staging tables because it distributes data evenly across all distributions without requiring a hash key, minimizing data movement during the load process. Since staging tables are temporary and used for intermediate storage, the even distribution ensures that the subsequent INSERT...SELECT operation into the final table benefits from maximum parallelism and reduced skew.

Answer

Split data into many small files to increase parallelism

Answer

Enable clustered columnstore index during load to improve query performance

Question 6

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 as a native output sink, allowing you to write real-time streaming results directly into dedicated SQL pools for high-performance analytics. This is achieved via the built-in Azure Synapse Analytics output adapter, which uses PolyBase or COPY INTO for efficient bulk ingestion.

Answer

Azure Queue Storage

Answer

Azure Files

Answer

Azure Table Storage

Question 7

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. The Pivot transformation in Mapping Data Flows allows you to rotate data from rows into columns, enabling aggregation and restructuring of datasets. It is a native transformation in Azure Data Factory's Mapping Data Flows, supporting group-by columns, pivot keys, and aggregated column values.

Answer

Union All

Answer

Merge Join

Question 8

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

Question 9

Refer to the exhibit. You are running the KQL query in Azure Data Explorer. The query returns no results, but you know there is data in the table T. What is the most likely issue?

Accepted Answer

The between operator syntax is incorrect; should be 'between (startTime..endTime)' without spaces around the dots. The KQL `between` operator requires the range syntax `between(datetime1..datetime2)` with no spaces around the two dots. The query uses `between (startTime .. endTime)` with spaces, which is invalid syntax and causes the query to return no results even though data exists in table T.

Answer

The bin function is used incorrectly; should be bin(Timestamp, 1h) but placed in the wrong clause

Answer

The datetime format is incorrect; use ISO 8601

Answer

The table T does not exist in the database

Question 10

Refer to the exhibit. You are deploying the ARM template to create an Azure Synapse Analytics workspace. The deployment fails because the managed resource group cannot be created. What is the most likely reason?

Accepted Answer

The managedResourceGroupName property is not allowed; Azure Synapse creates the resource group automatically. The `managedResourceGroupName` property is not allowed in an ARM template for Azure Synapse Analytics. Azure Synapse automatically creates and manages its own managed resource group (e.g., `managed-rg-<workspace-name>`) to hold provisioned resources like Spark pools and SQL pools. Specifying this property in the template causes a validation error, as the service requires full control over the naming and lifecycle of this group.

Answer

The apiVersion is invalid for this resource type

Answer

The location is missing the 'properties' section

Answer

The storage account URL must use the blob endpoint, not DFS

Question 11

You are designing a data pipeline in Azure Synapse Analytics that ingests streaming taxi trip data from Azure Event Hubs. The data must be processed in near real-time and stored in a dedicated SQL pool. The pipeline should handle late-arriving data (up to 30 minutes late) without reprocessing the entire stream. Which Azure service should you use to process the streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing with native support for Event Hubs as an input and dedicated SQL pool as an output. It can handle late-arriving data via its built-in 'late arrival' window (configurable up to 30 minutes) using event time processing, without requiring reprocessing of the entire stream.

Answer

Azure Databricks Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hubs trigger

Question 12

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

Accepted Answer

The storage account firewall does not have a 'Resource instances' exception for the managed identity. When a storage account firewall is enabled with 'Allow Azure services on the trusted services list' turned on, it only allows trusted Azure platform services to access the storage account, but it does not automatically grant access to a specific managed identity. To allow a managed identity to bypass the firewall, you must add a 'Resource instances' exception for that managed identity's resource (e.g., the Synapse workspace). Without this explicit exception, the managed identity's request is blocked by the firewall, resulting in a 'Permission denied' error even though the RBAC role assignment is correct.

Answer

Parquet files require special permissions that are not granted by RBAC roles

Answer

The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'

Answer

The notebook is using an incorrect connection string with account key

Question 13

You need to execute a T-SQL stored procedure in Azure Synapse dedicated SQL pool that performs a large data load. The stored procedure takes approximately 45 minutes to run. You want to monitor the progress and see the statement text currently being executed. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_exec_requests (with status='running'). B is correct because `sys.dm_pdw_exec_requests` with the filter `WHERE status = 'running'` returns the currently executing requests in an Azure Synapse dedicated SQL pool, including the full statement text in the `command` column. This allows you to see the exact T-SQL being executed during the 45-minute data load, enabling progress monitoring.

Answer

sys.dm_workload_management_workload_groups_stats

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_exec_requests

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 are optimizing an Azure Synapse serverless SQL pool query that queries Parquet files in Azure Data Lake Storage. The query takes longer than expected. You notice that the query reads more data than necessary. What is the most effective way to reduce the amount of data scanned?

Accepted Answer

Create external tables with explicit schema and partition by a frequently filtered column. Partitioning external tables in Azure Synapse serverless SQL allows the query engine to perform partition elimination, reading only the subdirectories that match the filter criteria. This directly reduces the amount of data scanned from Parquet files in ADLS, addressing the core issue of reading unnecessary data.

Answer

Split large Parquet files into smaller files of 100 MB each

Answer

Use SELECT with column pruning to only retrieve necessary columns

Answer

Increase the query's resource allocation by using a larger service level objective