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

Question 1

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

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

Accepted Answer

Azure Event Hubs. Azure Event Hubs is Microsoft's managed, real-time event-ingestion service designed for high-throughput streaming scenarios, and it integrates natively with Azure Synapse Analytics (via the Synapse Event Hubs connector or Spark Structured Streaming) to land streaming data into dedicated SQL pools, Spark pools, or the lake. It is the canonical answer for streaming ingestion into Synapse.

Answer

Azure Data Factory

Answer

Azure Blob Storage

Answer

Azure Analysis Services

Question 2

You are troubleshooting a failed Azure Synapse Pipeline execution. The pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The error indicates a 'Connection timeout' to the on-premises source. The Integration Runtime is Self-Hosted and has been running successfully for months. What is the most likely cause?

Accepted Answer

The on-premises network configuration has changed.. A 'Connection timeout' error when using a Self-Hosted Integration Runtime (SHIR) that has been running successfully for months indicates a network-level issue rather than an authentication or configuration problem. The most likely cause is a change in the on-premises network configuration (e.g., firewall rules, proxy settings, or DNS changes) that prevents the SHIR from reaching the SQL Server on the specified port (typically TCP 1433). Since the SHIR is already installed and was working, the timeout points to a connectivity break, not a credential or installation issue.

Answer

The SQL Server authentication credentials have expired.

Answer

The Self-Hosted Integration Runtime is not installed.

Answer

The Azure Storage account firewall is blocking access.

Question 3

You are designing a data processing pipeline in Azure Data Factory. The pipeline must copy data from Azure Blob Storage to Azure SQL Database and transform the data using a mapping data flow. The data flow includes a Derived Column transformation. What is the purpose of the Derived Column transformation?

Accepted Answer

Create new columns or modify existing columns using expressions.. The Derived Column transformation in Azure Data Factory mapping data flows is used to create new columns or modify existing columns by applying expressions. This allows you to perform calculations, string manipulations, or conditional logic directly within the data flow, enabling in-flight data transformation before writing to the sink.

Answer

Aggregate data by grouping rows.

Answer

Sort data in ascending or descending order.

Answer

Rename or drop columns.

Question 4

You are implementing a data processing solution in Azure Databricks. The solution must read data from Azure Data Lake Storage Gen2, transform it using PySpark, and write the results back to a different location in the same storage account. You need to authenticate to the storage account securely without storing secrets in the notebook. What should you use?

Accepted Answer

Azure Key Vault-backed secret scope. Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option A is wrong because storing a client secret directly in the notebook is insecure. Option C is wrong because SAS tokens can be exposed and are less secure. Option D is wrong because storage account access keys are long-lived and should not be used in notebooks.

Answer

Service Principal with a client secret stored in the notebook

Answer

Shared access signature (SAS) token

Answer

Storage account access key

Question 5

You are designing a batch processing solution in Azure Synapse Analytics using pipelines. The solution must load data from multiple sources (Azure Blob Storage, Azure SQL Database, and REST API) into a dedicated SQL pool. After loading, you need to run a stored procedure to aggregate the data. Which two activities should you include in the pipeline? (Choose two.)

Accepted Answer

Copy activity. The Copy activity (B) is correct because it is the native Azure Synapse Analytics/Data Factory pipeline activity designed to ingest data from disparate sources such as Azure Blob Storage, Azure SQL Database, and REST API connectors into a dedicated SQL pool sink, handling schema mapping and parallel loading. The Stored procedure activity (D) is correct because it invokes a stored procedure in the dedicated SQL pool after the load completes, which is exactly what is needed to run the aggregation logic on the loaded data. The Execute Pipeline activity (A) only calls another pipeline and does not itself move or transform data, so it does not satisfy the load or aggregation requirement. The Azure Function activity (C) runs custom code in an Azure Function, which is unnecessary here since built-in Copy and Stored procedure activities cover the scenario. The Data Flow activity (E) performs code-free transformations in a Spark-based execution engine, but the requirement is to load data and then aggregate via a stored procedure, not to transform data in a mapping data flow.

Answer

Execute Pipeline activity

Answer

Azure Function activity

Answer

Data Flow activity

Question 6

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

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

Accepted Answer

Derived Column. Options B and E are correct. Derived Column can change data types through expressions, and Select can cast types during column projection. Option C (Assert) is incorrect because it only validates and routes rows based on conditions; it does not convert data types. Option A (Conditional Split) and Option D (Sort) are also incorrect.

Answer

Conditional Split

Answer

Assert

Answer

Sort

Question 7

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Answer

Azure HDInsight with Hive

Answer

Azure Analysis Services

Answer

Azure Databricks with PySpark

Question 8

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

Accepted Answer

Increase the number of Streaming Units (SUs) for the Stream Analytics job.. Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help. Using Azure Functions for output may add overhead.

Answer

Add more partitions to the Event Hubs.

Answer

Replace the output with Azure Functions for each event.

Answer

Change the input to a reference data input.

Question 9

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

Accepted Answer

Azure Synapse Analytics dynamic data masking. Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs. Azure Data Lake Storage does not provide masking.

Answer

Azure API Management policies

Answer

Azure Data Lake Storage access control lists (ACLs)

Answer

Azure Purview classification and labeling

Question 10

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

Accepted Answer

Review the job logs in Azure Data Lake Analytics to identify the specific script error.. The most effective first step is to examine the detailed job logs in Data Lake Analytics, which contain the actual script error. Increasing parallelism or changing output format may not address the underlying script error. Moving to Azure Synapse is a larger architectural change.

Answer

Change the output format to Parquet to reduce file size.

Answer

Migrate the U-SQL script to Azure Synapse Spark pool.

Answer

Increase the degree of parallelism for the U-SQL job.

Question 11

Which THREE options are valid ways to transform data in Azure Synapse Analytics?

Accepted Answer

Use T-SQL scripts in a dedicated SQL pool.. T-SQL scripts are a native and primary method for transforming data within a dedicated SQL pool in Azure Synapse Analytics. You can use CREATE TABLE AS SELECT (CTAS), INSERT...SELECT, and other T-SQL statements to perform complex transformations like aggregations, joins, and data cleansing directly on the distributed data, leveraging the MPP (Massively Parallel Processing) engine for high performance.

Answer

Use Power Query online in Synapse pipelines.

Answer

Use Azure Machine Learning pipelines for data wrangling.

Question 12

Which TWO options are correct approaches to handle schema drift in Azure Data Factory Mapping Data Flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation.. Enabling 'Allow schema drift' in the source transformation is the primary mechanism in Mapping Data Flows to handle incoming columns that are not defined in the dataset schema. This setting allows the data flow to dynamically adapt to changes in the source data structure, such as new or missing columns, without requiring manual schema updates.

Answer

Use a conditional split to route rows with different schemas to separate sinks.

Answer

Define a rigid schema in the source dataset and reject rows that don't match.

Answer

Disable schema drift to improve performance.

Question 13

A company uses Azure Synapse Analytics dedicated SQL pool. The data engineering team notices that queries against a large fact table are running slowly. The table uses round-robin distribution and has a columnstore index. The team wants to improve query performance without adding more resources. Which action should the team take?

Accepted Answer

Change the distribution to hash on the column that is most frequently used in joins.. Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling. Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Answer

Keep round-robin distribution but increase the degree of parallelism.

Answer

Change the distribution to hash on multiple columns.

Answer

Rebuild the table as a heap to improve insert performance.

Question 14

You are monitoring an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The pipeline fails intermittently with the error: 'Operation on target SQL table failed: String or binary data would be truncated.' Which action should you take to resolve this issue?

Accepted Answer

Increase the length of the destination columns in the SQL table to accommodate the source data.. The error indicates that source data length exceeds destination column length. Increasing column size resolves it. Option B is incorrect because the table already exists. Option C is incorrect because the error is not about connection. Option D is incorrect because the error is not about identity insert.

Answer

Set 'enable identity insert' to true.

Answer

Use auto-create table option in the copy activity.

Answer

Enable staging copy to use PolyBase.

Question 15

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

Accepted Answer

The file was uploaded to the workspace filesystem, not to DBFS.. The most likely cause is that the file was uploaded to the workspace filesystem (Workspace), not to DBFS. In interactive notebooks, the workspace filesystem is accessible via a symlink that makes it appear under `dbfs:/`, but when running as a job, the path `dbfs:/tmp/data.csv` does not resolve to workspace files. The file must be in the DBFS root or mounted location to be accessible via that path. Option D is correct. Option A is incorrect because both interactive and job clusters support Python. Option B is incorrect because file size is not indicated as an issue. Option C is incorrect because the job cluster automatically has permission to access DBFS.

Answer

The job is using a different runtime that does not support Python.

Answer

The file is too large for DBFS.

Answer

The job cluster does not have permission to access DBFS.