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

Question 1

You are building a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow to perform a lookup transformation. The lookup source is a dimension table with 10 million rows. You need to optimize the lookup performance. Which THREE actions should you take?

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

Accepted Answer

Enable the 'Broadcast' option on the lookup source transformation if the dimension table is less than 100 MB.. To optimize lookup performance in a mapping data flow, the recommended actions are: C. Enable the 'Broadcast' option on the lookup source if the dimension table is less than 100 MB. This avoids shuffling the large fact table across nodes. D. Select only the necessary columns in the lookup source transformation to reduce data transfer and memory usage. E. Ensure the dimension table has an index on the columns used for the lookup to speed up the join operation. Options A and B are not optimal: Increasing batch size (A) does not improve lookup performance; it affects sink writes. Partitioning the dimension table on the lookup key before reading (B) can be helpful, but in a data flow, partitioning is typically applied to the source or within the data flow itself, and it is not one of the top three recommended actions for lookup optimization in Synapse mapping data flows.

Answer

Increase the batch size in the lookup transformation settings.

Answer

Partition the dimension table on the lookup key before reading.

Question 2

You are implementing a data processing solution using Azure Data Factory. You have a pipeline that copies data from Azure Blob Storage to Azure Data Lake Storage Gen2. You need to ensure that the copy activity uses managed identity for authentication and that the data is transferred securely. Which TWO configurations should you apply?

Accepted Answer

Configure the source and sink linked services to use managed identity authentication.. Options C and D are correct. Option C is correct because managed identity authentication is configured in the linked service, not in the copy activity itself. Option D is correct because setting the integration runtime to 'AutoResolveIntegrationRuntime' uses Azure's network, ensuring data transfer remains within Azure and does not go over the public internet if using managed virtual network. Option A is wrong because 'enableDataIntegrityValidation' is for integrity checks, not authentication. Option B is wrong because the copy activity does not have a 'UseManagedIdentity' property; managed identity is configured in the linked service. Option E is wrong because staging is not required for secure transfer; it's used for performance or polybase.

Answer

Set the copy activity's 'enableDataIntegrityValidation' to true.

Answer

Set the copy activity's 'UseManagedIdentity' property to true.

Answer

Enable staged copy with a temporary blob store to improve security.

Question 3

You are designing a data processing pipeline in Azure Synapse Analytics that ingests streaming data from Azure Event Hubs and stores it in a dedicated SQL pool. The data must be available for querying within 5 minutes of ingestion. Which processing approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a dedicated SQL pool output and configure a 1-minute window.. Azure Stream Analytics is purpose-built for real-time stream processing and can output directly to a dedicated SQL pool. By configuring a 1-minute window, you ensure data is materialized in the SQL pool well within the 5-minute SLA, meeting the latency requirement with headroom.

Answer

Use Azure Data Factory with a tumbling window trigger set to 5 minutes.

Answer

Use PolyBase to load data from Event Hubs into the dedicated SQL pool every 5 minutes.

Answer

Use Spark Structured Streaming in Azure Synapse to write micro-batches every 5 minutes.

Question 4

You are troubleshooting a slow-running Azure Synapse Pipeline that loads data from Azure Blob Storage into a dedicated SQL pool using a Copy activity. The source is a set of CSV files totaling 500 GB. The sink is a staging table with a clustered columnstore index. The pipeline takes 4 hours to complete. You need to reduce the execution time to under 1 hour. What should you do?

Accepted Answer

Enable PolyBase in the Copy activity sink settings.. Enabling PolyBase in the Copy activity sink settings allows the data to be loaded into the dedicated SQL pool using the high-throughput PolyBase technology, which leverages the SQL pool's distributed architecture and can read data directly from Azure Blob Storage in parallel. This bypasses the bottleneck of row-by-row inserts, drastically reducing load time for large datasets like 500 GB.

Answer

Increase the Data Integration Units (DIU) in the Copy activity to the maximum.

Answer

Increase the dedicated SQL pool's DWU setting to the highest tier.

Answer

Partition the staging table on a date column.

Question 5

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

Accepted Answer

Rely on Delta Lake's built-in ACID transactions.. Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Answer

Enable Optimized Write on the Delta table.

Answer

Enable Auto Optimize on the Delta table.

Answer

Use Dynamic Partition Pruning in your Spark jobs.

Question 6

You are designing a data processing solution for a financial services company. They need to process sensitive customer data in Azure Databricks while complying with GDPR. The data must be encrypted at rest and in transit, and access must be audited. You need to recommend a configuration that meets these requirements. Which combination of actions should you take?

Accepted Answer

Enable double encryption using Azure Key Vault with customer-managed keys and enable Azure Databricks audit logs.. Enabling double encryption (via Azure Disk Encryption and Azure Storage Service Encryption with customer-managed keys) ensures encryption at rest and in transit; Azure Key Vault manages the keys, and Azure Databricks audit logs capture access for auditing. Option A (SSE with platform-managed keys) provides encryption at rest but not with customer-managed keys and does not include audit logs. Option B (Azure Policy with GDPR blueprint) enforces compliance but does not directly implement encryption or auditing. Option C (Azure Security Center and Defender for Storage) provides monitoring but does not encrypt data or provide access auditing.

Answer

Enable Azure Storage Service Encryption (SSE) with platform-managed keys and configure Azure Databricks to use a VNet injection.

Answer

Apply an Azure Policy to require encryption and assign a built-in GDPR blueprint.

Answer

Use Azure Security Center to monitor for data exposure and enable Azure Defender for Storage.

Question 7

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

Accepted Answer

Power BI with DirectQuery mode and configure automatic page refresh.. Power BI with DirectQuery mode is the correct choice because it allows real-time queries directly against Azure SQL Database, supporting automatic page refresh every 30 seconds with minimal latency. Azure Analysis Services requires data processing and is not designed for sub-minute refreshes. Azure Data Explorer is optimized for time-series data, not for direct connection to Azure SQL Database for real-time dashboards. Azure Synapse Serverless SQL pool is meant for querying data lakes, not for real-time visualization with frequent refreshes.

Answer

Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.

Answer

Azure Data Explorer (ADX) with a continuous export to Power BI.

Answer

Azure Synapse Serverless SQL pool with Power BI import mode.

Question 8

You have an Azure Data Factory pipeline that uses a Copy activity to move data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with a timeout error. You need to improve the reliability of the data transfer. Which configuration change should you make?

Accepted Answer

Enable fault tolerance and configure skip incompatible rows.. Enabling fault tolerance and configuring 'skip incompatible rows' allows the Copy activity to continue processing even when some rows cause errors (e.g., type conversion failures), which can manifest as timeouts when the activity repeatedly retries the same problematic rows. This setting improves reliability by skipping rows that cannot be copied, preventing the entire pipeline from failing on intermittent data issues.

Answer

Use staged copy with an intermediate Azure Blob Storage.

Answer

Use PolyBase as the sink.

Answer

Increase the retry count in the pipeline activity.

Question 9

You are designing a near-real-time data processing solution that ingests millions of events per second from IoT devices. The data must be aggregated on a per-minute basis and stored in Azure Data Lake Storage Gen2 for long-term analytics. The solution must also support alerting when certain thresholds are exceeded. Which combination of Azure services should you use?

Accepted Answer

Azure IoT Hub, Azure Stream Analytics, and Azure Functions.. Azure IoT Hub is designed for secure, bidirectional communication with IoT devices and can ingest millions of events per second. Azure Stream Analytics provides low-latency, SQL-based stream processing to aggregate data on a per-minute basis and can output to Azure Data Lake Storage Gen2 for long-term analytics. Additionally, Stream Analytics can trigger Azure Functions for alerting when thresholds are exceeded, enabling near-real-time responses.

Answer

Azure Event Hubs, Azure Data Factory, and Azure SQL Database.

Answer

Azure IoT Hub, Azure Databricks with Structured Streaming, and Azure Data Lake Storage Gen2.

Answer

Azure Event Hubs, Azure Data Explorer, and Power BI.

Question 10

You are developing a data processing pipeline in Azure Synapse Analytics that uses a SQL script to transform data in a dedicated SQL pool. The pipeline currently runs in the built-in serverless pool, but you want to migrate it to a dedicated SQL pool for better performance. What must you change in the pipeline?

Accepted Answer

Modify the SQL script to use T-SQL compatible with dedicated SQL pool.. Dedicated SQL pool uses T-SQL that has specific syntax differences from the serverless SQL pool used in Azure Synapse Analytics. When migrating from serverless to dedicated, you need to modify your SQL script to be compatible with dedicated SQL pool's T-SQL (e.g., different supported functions, data types, and performance features). Option A (changing the linked service) might also be necessary as part of the migration but is not the primary change needed to ensure the script runs correctly. Option B (replacing with Mapping Data Flow) is not required; the script itself can still be used. Option C (self-hosted IR) is not relevant for SQL script activities in Synapse pipelines.

Answer

Change the linked service from serverless to dedicated SQL pool.

Answer

Replace the SQL script with a Mapping Data Flow activity.

Answer

Update the integration runtime to a self-hosted IR.

Question 11

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

Accepted Answer

Run OPTIMIZE to compact small files.. The OPTIMIZE command in Delta Lake compacts many small files into larger ones by rewriting data files based on the table's partitioning scheme. This reduces the number of files that need to be read during queries, significantly improving read performance. Since the notebook is slow due to many small files, OPTIMIZE directly addresses the root cause.

Answer

Run CONVERT TO DELTA on the underlying Parquet files.

Answer

Run DESCRIBE HISTORY to analyze file sizes.

Answer

Run VACUUM to delete old files.

Question 12

You are designing a data processing solution that must handle both batch and stream processing in a unified manner. You need to choose a technology that supports a single code base for both processing modes. Which TWO technologies meet this requirement?

Accepted Answer

Azure Databricks with Delta Live Tables. Azure Databricks with Delta Live Tables (DLT) supports both batch and stream processing using a single code base by allowing you to define streaming and batch data sources declaratively in the same pipeline. DLT automatically handles incremental processing for streaming and full refreshes for batch, enabling unified logic without separate code paths.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure SQL Database with Change Tracking

Question 13

You are building a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow to perform a lookup transformation. The lookup source is a dimension table with 10 million rows. You need to optimize the lookup performance. Which THREE actions should you take?

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

Accepted Answer

Enable schema drift in a Mapping Data Flow activity.. Mapping Data Flows in Azure Synapse Analytics support schema drift, which allows the pipeline to dynamically handle changes in source data structure (e.g., new columns, changed data types) without failing. By enabling schema drift, the data flow can automatically detect and propagate these changes downstream, ensuring resilience against schema evolution.

Answer

Use staged copy with an intermediate storage.

Answer

Define a fixed schema in the source dataset.

Answer

Use PolyBase to load data into the dedicated SQL pool.

Question 14

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

Accepted Answer

Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.. A 'Connection closed' error during large data transfers (50 GB) often occurs due to resource exhaustion or network instability on the self-hosted integration runtime (IR) machine. Insufficient memory or CPU on the IR node or unreliable network connectivity can cause the connection to drop. This is the first thing to troubleshoot. Option B (increasing connection timeout) might help if the error is due to timeouts, but 'Connection closed' typically indicates an abrupt termination, not a timeout. Option C (staged copy) can improve reliability by using Azure Blob as an intermediate store, but it adds complexity and is not the first step. Option D (disabling fault tolerance) would make the pipeline less resilient and is not recommended; fault tolerance helps handle transient errors.

Answer

Increase the 'connection timeout' setting in the linked service to 30 minutes.

Answer

Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.

Answer

Disable fault tolerance in the copy activity to improve performance.

Question 15

You are designing a batch processing solution for a data lake. Source files arrive daily in Parquet format in Azure Data Lake Storage Gen2. The data must be cleaned, aggregated, and loaded into an Azure Synapse SQL pool. The solution should minimize compute costs and management overhead. Which technology should you use for the transformation?

Accepted Answer

Azure Synapse Pipelines with mapping data flows.. Azure Synapse Pipelines with mapping data flows provide a serverless, code-free transformation service that runs on managed Spark clusters, minimizing management overhead and costs. Mapping data flows can directly read Parquet from ADLS Gen2, perform cleaning and aggregation, and load into Azure Synapse SQL pool without requiring cluster management. Option A (Azure HDInsight with Spark) requires manual cluster provisioning and management, increasing operational overhead. Option C (custom SSIS package) is legacy, not cloud-native, and requires an integration runtime for execution. Option D (Azure Databricks with Auto Loader) provides powerful stream and batch processing but incurs higher costs for a simple batch job due to cluster management and DBU consumption.

Answer

Azure HDInsight with Spark jobs scheduled in Azure Data Factory.

Answer

Azure Data Factory with a custom SSIS package.

Answer

Azure Databricks with an Auto Loader pipeline.