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

Question 1

Your company uses Azure Synapse Analytics and has deployed a pipeline that uses a Mapping Data Flow to transform data. The data flow reads from a source in Azure Blob Storage and writes to a dedicated SQL pool. You notice that the data flow is running slowly and consuming a lot of Data Flow cluster resources. You need to improve performance without increasing the cluster size. Which action should you take?

Accepted Answer

Add a partitioning step in the data flow to distribute the data across partitions based on a key column.. Adding a partitioning step in the Mapping Data Flow distributes data across partitions based on a key column, which allows parallel processing across the cluster's nodes. This reduces data shuffling and improves throughput without increasing the cluster size, directly addressing the performance bottleneck caused by skewed or unpartitioned data.

Answer

Use a self-hosted integration runtime instead of the default auto-resolve IR.

Answer

Increase the batch size in the data flow settings to reduce the number of round trips.

Answer

Change the source format to Delta Lake to leverage optimizations.

Question 2

You are designing a data processing solution in Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 and you need to perform transformations using Apache Spark. The security requirements mandate that all data in transit must be encrypted and that the storage account must not be accessible from the public internet. What should you configure?

Accepted Answer

Enable the storage account firewall and add a private endpoint for Azure Databricks to use.. Option A is correct because it satisfies both security requirements: encrypting data in transit and preventing public internet access. A private endpoint uses Azure Private Link to connect Azure Databricks to the storage account over the Microsoft backbone network, ensuring all traffic stays within the Azure network and is encrypted via TLS. The storage account firewall is then configured to deny all public traffic, so only the private endpoint can access the storage account, meeting the 'not accessible from the public internet' mandate.

Answer

Disable TLS on the storage account and use a shared access signature (SAS) token for authentication.

Answer

Use Azure Databricks with VNet injection and configure a service endpoint for the storage account.

Answer

Configure the storage account to use HTTPS only and enable firewall rules to allow only Azure services.

Question 3

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

Accepted Answer

Enable staging and use PolyBase as the copy method for the sink.. Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Answer

Change the copy behavior to 'sequential' to reduce load on the source.

Answer

Increase the degree of copy parallelism (DOP) to the maximum value supported.

Answer

Split the source data into multiple smaller files and use multiple copy activities running in parallel.

Question 4

Your team is building a real-time dashboard using Azure Stream Analytics. The data source is an Azure Event Hub that receives clickstream events. You need to output aggregated data (counts per page per minute) to an Azure SQL Database for reporting. The query must handle late-arriving events and ensure exactly-once semantics. Which Stream Analytics feature should you use?

Accepted Answer

Use the Input Order section in the Stream Analytics job configuration to set a late arrival tolerance window.. Option C is correct because the temporal window functions (e.g., TumblingWindow) with a late arrival policy and exactly-once semantics are built into Stream Analytics. Option A is wrong because watermarks are a concept in Spark, not Stream Analytics. Option B is wrong because the Input Order policy in Stream Analytics allows handling late events, but exactly-once semantics are guaranteed by the combination of checkpointing and output adapters. Option D is wrong because the event ordering policy is for out-of-order events, not for late arrival.

Answer

Use a temporal window function with a 'late arrival' policy specified in the query.

Answer

Define a watermark in the query to specify a maximum out-of-order tolerance.

Answer

Set the event ordering policy to 'Adjust' to reorder events within a certain time window.

Question 5

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

Accepted Answer

Use the Azure Databricks dbutils.fs.mv() to move the file.. Option A is correct because `dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Answer

Use Azure Data Factory with a Copy activity to move the file, then delete the source.

Answer

Change the file's metadata to update its directory path.

Answer

Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.

Question 6

You are designing a data processing pipeline in Azure Data Factory that uses a Mapping Data Flow. You need to handle errors gracefully, such as when a row fails to convert a column value. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure the sink transformation to allow errors and log error rows to a separate file.. Options A and D are correct. Option A: Using a conditional split to route error rows is a common pattern. Option D: Configuring the sink to allow errors and logging them ensures fault tolerance. Option B is wrong because try-catch is not available in Mapping Data Flows. Option C is wrong because aborting the activity is not graceful. Option E is wrong because schema drift does not handle conversion errors.

Answer

Wrap the data flow in a Try-Catch activity in the pipeline.

Answer

Set the data flow's error handling to 'Abort on error' to stop processing on first failure.

Answer

Enable schema drift on the source to automatically handle data type mismatches.

Question 7

You are optimizing the performance of a large-scale batch processing job in Azure Databricks. The job reads data from Azure Data Lake Storage Gen2, performs transformations, and writes results back. You notice that the job is I/O bound. Which THREE strategies can improve performance? (Choose three.)

Accepted Answer

Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.. Options A, B, and E are correct. Option A: Increasing the number of partitions can improve parallelism. Option B: Using Delta Lake and optimizing the file layout reduces I/O. Option E: Caching intermediate results reduces repeated reads. Option C is wrong because reducing shuffle partitions can cause OOM errors. Option D is wrong because autoscaling may not help if the bottleneck is I/O.

Answer

Reduce the number of shuffle partitions to minimize data movement.

Answer

Enable autoscaling on the cluster to add more nodes during processing.

Question 8

You are using Azure Stream Analytics to process real-time data from an IoT hub. The output is sent to Azure Blob Storage for long-term storage. You need to ensure that the output files are partitioned by date and hour for easy querying. Which THREE configurations should you set? (Choose three.)

Accepted Answer

Use a path pattern that includes {date} and {time} tokens.. Options A, D, and E are correct. Option A: Setting the output format to Avro or Parquet is common for partitioned data. Option D: Enabling 'DateTime format' in the output allows partitioning. Option E: Using a path pattern with {date} and {time} creates folder structure. Option B is wrong because partitioning is not part of event ordering. Option C is wrong because compatibility level does not affect output partitioning.

Answer

Configure the event ordering policy to adjust late events.

Answer

Set the compatibility level to 1.2 or higher.

Question 9

Refer to the exhibit. You are an Azure data engineer responsible for ensuring that all storage accounts used in data pipelines enforce HTTPS traffic. You apply the Azure Policy definition shown above. Later, a data engineer creates a new storage account with 'Enable secure transfer' set to Disabled. What will happen when the policy is evaluated?

Accepted Answer

The storage account creation will be denied and the request will fail.. Option A is correct because the policy denies creation of storage accounts that do not have HTTPS enforced (supportsHttpsTrafficOnly = false). The policy is evaluated at creation time and will deny the request. Option B is wrong because the policy has deny effect, not audit. Option C is wrong because the policy does not set the property automatically. Option D is wrong because the policy is evaluated during creation, not after.

Answer

The storage account will be created, but the policy will be evaluated later during a compliance scan.

Answer

The storage account will be created with HTTPS enabled automatically.

Answer

The storage account will be created, but an audit event will be logged.

Question 10

Refer to the exhibit. You are querying the sys.external_tables view in an Azure Synapse Analytics serverless SQL pool. The query returns no rows, but you know that external tables have been created. What is the most likely reason?

Accepted Answer

The external tables were created using OPENROWSET, not CREATE EXTERNAL TABLE, so they do not appear in sys.external_tables.. In Azure Synapse Analytics serverless SQL pool, external tables are created using the CREATE EXTERNAL TABLE statement, and they are listed in the sys.external_tables view. However, if you query data directly via OPENROWSET without creating an external table, those ad-hoc queries do not register any metadata in sys.external_tables. Since the question states that external tables have been created but the view returns no rows, the most likely reason is that the tables were actually created using OPENROWSET, not CREATE EXTERNAL TABLE, so they are not cataloged in the system view.

Answer

The external tables are using PolyBase, which is not supported in serverless SQL pool.

Answer

Serverless SQL pool does not support external tables; you must use a dedicated SQL pool.

Answer

The user does not have permission to view the sys.external_tables view.

Question 11

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure Synapse Analytics workspace. The template defines an integration runtime. A colleague asks whether this integration runtime can be used to copy data from an on-premises SQL Server database to Azure Blob Storage. What should you answer?

Accepted Answer

Yes, because the integration runtime is self-hosted and can access on-premises data sources.. Option A is correct because a self-hosted integration runtime is designed for on-premises and VM-based data sources. Option B is wrong because managed VNet IR is for Azure-only sources. Option C is wrong because Azure IR is for cloud data sources. Option D is wrong because it is possible with a self-hosted IR.

Answer

No, you need an Azure integration runtime that is configured for on-premises access.

Answer

No, you need a managed virtual network integration runtime for on-premises sources.

Answer

Yes, but only if the integration runtime is installed on a domain-joined machine.

Question 12

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hub trigger

Question 13

In Azure Synapse Analytics serverless SQL pool, you query Parquet files stored in Azure Data Lake Storage Gen2. You notice that queries are slow. Which configuration change is most likely to improve performance?

Accepted Answer

Use OPENROWSET with a properly defined schema and file format. Option C is correct because OPENROWSET with an explicitly defined schema and file format (e.g., FORMAT='PARQUET') enables the serverless SQL pool to bypass schema inference, which is a costly runtime operation. By providing a proper schema and file format, the query engine can directly read the Parquet metadata and column statistics, significantly reducing I/O and CPU overhead. This is the most direct and effective performance tuning change for querying Parquet files in a serverless SQL pool.

Answer

Partition the data in Azure Data Lake Storage Gen2

Answer

Create a pipeline in Azure Synapse to preprocess the data

Answer

Increase the DWU setting of the serverless SQL pool

Question 14

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data in Azure Databricks with encryption at rest and in transit, and must also support row-level security (RLS). Which combination of features should you use?

Accepted Answer

Use Azure Databricks with Delta Lake and enable row-level security on the Delta table. Option D is correct because Delta Lake in Azure Databricks now supports row-level security (RLS) natively through Delta Sharing and Spark SQL predicates, allowing you to enforce data access policies directly on the Delta table without external systems. Combined with Azure Databricks' built-in encryption at rest (using Azure Storage Service Encryption or customer-managed keys) and in transit (TLS 1.2+), this meets all requirements without introducing additional services.

Answer

Use Azure SQL Database with row-level security and connect Databricks via JDBC

Answer

Use Azure Databricks with Azure Key Vault for encryption and implement RLS in application code

Answer

Use Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted

Question 15

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with timeout errors. You need to improve reliability. What should you do?

Accepted Answer

Enable fault tolerance and use staging. Option B is correct because enabling fault tolerance with staging in Azure Data Factory allows the copy activity to automatically retry transient failures (such as timeout errors) by staging intermediate data in Azure Blob Storage. This mechanism uses a two-phase commit approach: data is first written to a staging location, and then committed to the final sink only after successful validation, which isolates the pipeline from intermittent source or sink failures.

Answer

Use a self-hosted integration runtime with high availability

Answer

Change the source to Azure SQL Database

Answer

Increase the degree of copy parallelism