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

Question 1

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?

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

Accepted Answer

Use OPENROWSET with a properly defined schema and file format. 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 2

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

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

Question 4

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

Accepted Answer

Partition the data flow by a key column. Partitioning the data flow by a key column (Option D) enables parallel processing across multiple compute nodes, reducing data shuffling and improving throughput. In Azure Synapse data flows, partitioning distributes the workload evenly, which directly addresses the slow performance and high resource consumption observed in the pipeline.

Answer

Use a staging table to load data before transformation

Answer

Increase the batch size in the data flow

Answer

Use PolyBase to load data into the dedicated SQL pool

Question 5

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

Accepted Answer

Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations. Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Answer

Cache the data in memory after reading

Answer

Use bucketing with a fixed number of buckets

Answer

Partition the data by a high-cardinality column

Question 6

You are developing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion into a dedicated SQL pool. You need to ensure that data from streaming sources is available for queries within 5 seconds. Which approach should you use?

Accepted Answer

Use Azure Stream Analytics with a custom SQL function that writes directly to the dedicated SQL pool. Azure Stream Analytics can output directly to Azure Synapse Analytics dedicated SQL pool using the built-in Azure Synapse Analytics output adapter, which supports high-throughput, low-latency writes. This enables streaming data to be available for queries within seconds, meeting the 5-second latency requirement without intermediate storage or batch processing steps.

Answer

Use Azure Databricks with Structured Streaming, write to Data Lake Storage, and then use PolyBase to load into SQL pool

Answer

Use Azure Data Factory with tumbling window triggers to load data from Event Hubs every 5 seconds

Answer

Use Event Hubs Capture to write to Data Lake Storage, then use PolyBase to load into the SQL pool every 5 seconds

Question 7

You are using Azure Data Factory to copy data from Azure Blob Storage to Azure SQL Database. The copy operation fails with error 'Cannot insert duplicate key'. What is the most likely cause and solution?

Accepted Answer

The sink table has a primary key, and the source contains duplicate rows; enable upsert in the copy activity. The error 'Cannot insert duplicate key' indicates that the sink table in Azure SQL Database has a primary key or unique constraint, and the source data contains rows with duplicate key values. Enabling upsert in the copy activity allows Azure Data Factory to handle duplicates by updating existing rows instead of failing, resolving the conflict.

Answer

The source files are in an incompatible format; convert to CSV

Answer

The sink table schema does not match the source; update the schema

Answer

The copy activity is not using staging; enable staging

Question 8

Refer to the exhibit. You have a managed identity that needs to read data from the 'data' container in Azure Data Lake Storage Gen2. The policy currently denies access. What is the most likely cause?

Accepted Answer

The condition on 'acs:RequestVersion' is preventing access because the request does not use the specified API version. The policy condition requires the request to include the API version '2021-06-08' via the `acs:RequestVersion` condition key. Managed identity requests to Azure Data Lake Storage Gen2 use a default API version that may not match this specific version, causing the deny. The condition explicitly checks the request's API version, and if it does not match, access is denied regardless of other permissions.

Answer

The resource path is malformed; it should include the blob path

Answer

The action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is incorrect; it should be 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read'

Answer

The principal is a managed identity, but the policy requires a user-assigned identity

Question 9

Refer to the exhibit. You run the KQL query in Azure Data Explorer. What is the output?

Accepted Answer

Name: Alice, Age: 30; Name: Charlie, Age: 35. The KQL query uses a `where` clause to filter rows where Age is greater than 25, which selects Alice (Age 30) and Charlie (Age 35) and excludes Bob (Age 25). The `project` operator then selects only the Name and Age columns, so the output consists of those two rows with those columns.

Answer

Id: 1, Name: Alice, Age: 30; Id: 3, Name: Charlie, Age: 35

Answer

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Answer

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Question 10

You are building a data pipeline in Azure Synapse Analytics that ingests JSON files from Azure Data Lake Storage Gen2, transforms the data using a mapping data flow, and loads it into a dedicated SQL pool. The pipeline must support incremental loads based on a LastModified timestamp in the source files. Which configuration should you use in the mapping data flow source transformation?

Accepted Answer

Enable 'Incremental extraction' and specify the timestamp column for filtering.. Mapping data flows in Azure Synapse Analytics support incremental extraction by enabling the 'Incremental extraction' option in the source transformation and specifying a timestamp column (e.g., LastModified) to filter only new or updated rows. This allows the pipeline to load only changed data since the last run, which is essential for efficient incremental loads into a dedicated SQL pool.

Answer

Use a 'Column pattern' to detect new columns and filter rows.

Answer

Configure 'Row sampling' settings to take only new rows.

Answer

Set 'Source' to 'New rows only' in the sink transformation.

Question 11

You are designing a batch processing solution in Azure Databricks that reads Parquet files from Azure Data Lake Storage Gen2, performs aggregations, and writes results to a Delta table. The data volume is expected to grow to 10 TB per batch. You need to minimize shuffle operations during the aggregation step. Which approach should you recommend?

Accepted Answer

Partition the Delta table by the aggregation key and use the same partitioning when reading.. Partitioning the Delta table by the aggregation key and aligning the read partitioning ensures that data with the same key value is co-located in the same Spark partition. This allows the aggregation to be performed within each partition without needing to shuffle data across the network, which is the primary cause of performance bottlenecks in large-scale batch processing.

Answer

Use bucketing on a different column to reduce partition skew.

Answer

Use a broadcast join to avoid shuffle on the aggregation.

Answer

Use coalesce to reduce the number of partitions before aggregation.

Question 12

You are troubleshooting a data processing job in Azure Synapse Pipelines that fails intermittently with the error: 'Operation on target Sink failed: The request was aborted: Could not create SSL/TLS secure channel.' The pipeline reads from Azure Blob Storage and writes to an Azure SQL Database. The source and sink are in the same region. What is the most likely cause?

Accepted Answer

The self-hosted integration runtime is using TLS 1.0, which is not supported by the services.. The error 'Could not create SSL/TLS secure channel' indicates a TLS version mismatch. Azure SQL Database and Azure Blob Storage require at least TLS 1.2 for secure connections. If the self-hosted integration runtime (SHIR) is configured to use TLS 1.0, the handshake fails because the services reject the older protocol. This is the most likely cause given the intermittent nature and the specific error message.

Answer

Azure SQL Database firewall rules blocking the IP address of the integration runtime.

Answer

Transient network connectivity issues between the services.

Answer

The Azure SQL Database DTU limit has been exceeded, causing throttling.

Question 13

Refer to the exhibit. The pipeline fails because the source and sink datasets do not match. The source file is a CSV with columns: CustomerID, Name, City. The sink table dbo.Customer has columns: CustomerID, Name, City, CreatedDate (default GETDATE()). The pipeline uses auto-mapping. Why does the pipeline fail?

Accepted Answer

The pre-copy script is not allowed for SQL sink.. The pipeline fails because a pre-copy script is specified for the SQL sink, but pre-copy scripts are not allowed for SQL sinks in Azure Data Factory. This causes an error during the copy activity, even though auto-mapping could handle the extra column with a default value. The other options are incorrect: auto-mapping can handle extra sink columns with default values (so A is wrong), recursive setting does not affect this scenario, and writeBatchSize is not relevant to the failure.

Answer

Auto-mapping fails because the source and sink have different column counts.

Answer

The recursive setting should be false for CSV files.

Answer

The writeBatchSize is too large for the sink.

Question 14

You are monitoring a production data pipeline in Azure Data Factory that runs hourly. You notice that the pipeline has been failing for the last 3 hours due to a timeout error when writing to the sink Azure SQL Database. The database is a General Purpose tier with 100 DTUs. The pipeline writes approximately 1 million rows per run. What is the most effective way to prevent the timeout?

Accepted Answer

Increase the writeBatchSize in the sink settings to reduce the number of batch inserts.. Increasing the writeBatchSize reduces the number of batch inserts, which decreases round trips and can prevent timeouts. Option A is wrong because reducing copy parallelism increases the time taken per run, worsening timeout. Option B is wrong because using a staged copy with Azure Blob Storage adds additional I/O and does not directly address the timeout issue on the sink side. Option C is wrong because scaling up the DTU tier is more costly and may not be the most effective solution if the issue can be resolved by tuning batch size.

Answer

Reduce the degree of copy parallelism in the copy activity.

Answer

Enable staged copy using Azure Blob Storage as an interim store.

Answer

Scale up the Azure SQL Database to a higher DTU tier.

Question 15

You are developing a streaming solution with Azure Stream Analytics that outputs results to Azure Synapse Analytics. The output requires exactly-once semantics to avoid duplicates. Which configuration should you use?

Accepted Answer

Configure the output to Azure Synapse Analytics with 'Exactly once' output mode.. Azure Stream Analytics supports an 'Exactly once' output mode for Azure Synapse Analytics, which ensures that each event is written exactly once to the destination, preventing duplicates. This mode uses a combination of checkpointing and transactional writes to guarantee idempotent delivery without requiring custom logic.

Answer

Use an Azure Function output to write data idempotently to Synapse.

Answer

Set the event serialization format to Avro and enable 'Exactly once' event delivery.

Answer

Output to Azure Blob Storage and then use a COPY statement in Synapse.