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

Question 1

You are working with a Delta Lake table in Azure Databricks. The table is updated frequently with new data and occasionally with updates to existing rows. You need to optimize read performance for queries that filter on a specific date column. The table is partitioned by date. Which optimization technique should you apply?

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

Accepted Answer

Run ZORDER BY on the date column.. ZORDER BY on the date column co-locates related data within each partition, significantly reducing the amount of data scanned for queries that filter on that column. This is especially beneficial for a frequently updated Delta table where data is already partitioned by date, as ZORDER BY optimizes the layout within partitions without changing the partition scheme.

Answer

Run OPTIMIZE on the table.

Answer

Run ANALYZE STATISTICS on the table.

Answer

Run VACUUM to clean up old versions.

Question 2

Your organization has an Azure Data Factory pipeline that executes a series of activities to transform data. One of the activities is an Azure Databricks notebook that should run only if the previous activity succeeds. You need to configure the pipeline to handle failures gracefully and send an email alert if the Databricks activity fails. What should you do?

Accepted Answer

Add a failure output path from the Databricks activity to a Web activity that calls an email API.. In Azure Data Factory, activity dependencies are defined by output paths. By adding a failure output path from the Databricks activity to a Web activity, the pipeline can execute the Web activity only when the Databricks activity fails. The Web activity can then call an email API to send an alert, enabling graceful failure handling.

Answer

Configure a retry policy and a timeout for the Databricks activity.

Answer

Use a Schedule trigger to run the pipeline and check for failures using Azure Monitor.

Answer

Set a dependency condition on the Databricks activity to 'Succeeded' and add a Send Email activity on the success path.

Question 3

Which THREE considerations are important when designing a data processing solution using Azure Databricks for ETL workloads? (Select three.)

Accepted Answer

Auto-scaling of compute resources. Auto-scaling of compute resources ensures that the cluster can adjust to workload demands, optimizing cost and performance. Option B is correct because Delta Lake provides ACID transactions, schema enforcement, and optimization features that enhance data reliability in ETL pipelines. Option E is correct because proper cluster configuration and autoscaling are essential for managing resources efficiently. Option C is incorrect because Azure Databricks uses a pay-as-you-go model, not a fixed pricing model, making it more flexible. Option D is incorrect because minimum network bandwidth is not a typical design consideration for Databricks ETL workloads, as network performance is generally sufficient.

Answer

Fixed pricing model

Answer

Minimum network bandwidth

Question 4

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The copy activity uses a preCopyScript to truncate the destination table before writing. During a recent run, the copy activity failed after the truncation, leaving the destination table empty. You need to prevent data loss in future failures. What should you modify?

Accepted Answer

Use a staging table and then a stored procedure to swap tables.. Using a staging table with a stored procedure swap ensures atomicity: if the copy fails, the staging table is discarded and the original table remains intact. Option A is wrong because a retry policy would re-run the entire activity, including the preCopyScript truncation, and would fail again, causing data loss. Option B is wrong because fault tolerance only skips incompatible rows but does not prevent the truncation from occurring; data loss still happens. Option D is wrong because removing the preCopyScript avoids the truncation but setting writeBatchSize to 0 is invalid; even if fixed, without atomicity, a partial write could lead to data loss on failure.

Answer

Add a retry policy to the copy activity.

Answer

Enable fault tolerance in the copy activity source.

Answer

Remove the preCopyScript and set writeBatchSize to 0.

Question 5

You are designing a data processing pipeline in Azure Synapse Analytics that reads streaming data from Azure Event Hubs, performs aggregations in real time, and writes results to Azure Cosmos DB for a dashboard. The data volume is 10,000 events per second with 2 KB each. The latency requirement is under 5 seconds from event ingestion to dashboard visibility. Which technology should you use for the real-time aggregation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for low-latency stream processing. It can ingest data from Azure Event Hubs, perform windowed aggregations (e.g., tumbling, hopping, sliding windows) with sub-second latency, and output directly to Azure Cosmos DB, meeting the 5-second latency requirement for the dashboard.

Answer

Azure Synapse Spark with Structured Streaming

Answer

Azure Data Factory mapping data flows

Answer

Azure Synapse dedicated SQL pool with T-SQL queries

Question 6

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to process CSV files that arrive in a 'raw' container, transform them into Parquet format, and write them to a 'curated' container. The transformation includes filtering out rows with null values in the 'customer_id' column and adding a partition column 'year' based on the 'order_date'. You use Azure Synapse Pipelines. Which activity should you use for the transformation?

Accepted Answer

Data flow activity. The Data Flow activity in Azure Synapse Pipelines is designed for code-free, visual data transformations at scale. It can directly read CSV files from the 'raw' container, filter out rows with null 'customer_id' values using a conditional split or filter transformation, derive a 'year' column from 'order_date' using a derived column transformation, and write the results as Parquet files to the 'curated' container—all without writing code. This makes it the optimal choice for this ETL scenario.

Answer

Stored procedure activity

Answer

Notebook activity with PySpark

Answer

Copy data activity

Question 7

Your team is developing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs. The output must be written to Azure SQL Database. You need to ensure that the processing guarantees exactly-once semantics for the output. Which output configuration should you use?

Accepted Answer

Set output mode to 'Exactly once'. Azure Stream Analytics supports exactly-once semantics for output to Azure SQL Database when the output mode is set to 'Exactly once'. This ensures that each event is processed and written exactly once, preventing duplicates even in the event of failures or retries. The 'Exactly once' mode uses internal checkpointing and transactional writes to guarantee this behavior.

Answer

Set output mode to 'When possible'

Answer

Set output mode to 'At least once'

Answer

Set output mode to 'Best effort'

Question 8

You are designing a data processing pipeline in Azure Data Factory that ingests data from an on-premises SQL Server database to Azure Data Lake Storage Gen2. The data volume is large (500 GB). The network connection between on-premises and Azure is limited to 100 Mbps. You need to minimize the time to transfer the initial full load while ensuring data integrity. Which approach should you recommend?

Accepted Answer

Use Azure Data Box to copy the data offline. Azure Data Box physically ships the data, bypassing network bandwidth limitations for large initial loads. Option A is wrong because it would take over 11 hours even at full bandwidth, and network may not be stable. Option B is wrong because it compresses but still uses network. Option C is wrong because VPN adds overhead.

Answer

Use Azure Data Factory copy activity with parallel connections

Answer

Use Azure ExpressRoute to increase bandwidth

Answer

Compress the data using GZip and use copy activity

Question 9

Which THREE Azure services can you use together to build a serverless, event-driven data processing pipeline that ingests data from Azure Blob Storage, transforms it using custom code, and loads it into Azure Cosmos DB?

Accepted Answer

Azure Functions. Options A, B, and D are correct. Azure Functions can be triggered by Blob Storage events to run custom code, and the output can be written to Cosmos DB via the Cosmos DB output binding (option B). Azure Event Grid (option D) enables event-driven triggers for the pipeline. Option C (Azure Batch) is wrong because it is designed for large-scale parallel computing, not event-driven triggers. Option E (Azure Logic Apps) is wrong because it is more for workflow orchestration with connectors, not custom code execution.

Answer

Azure Batch

Answer

Azure Logic Apps

Question 10

You are debugging an Azure Data Factory pipeline that copies CSV files from Azure Blob Storage to Azure SQL Database. The pipeline is defined as shown in the exhibit. The first row of each CSV file contains column headers. After a run, you notice that the first data row is missing from the target table. What is the most likely cause?

Accepted Answer

The 'skipLineCount' is set to 1, which skips the first data row after the header. In Azure Data Factory, when copying CSV files with headers, the 'skipLineCount' property is used to skip lines at the beginning of the file. If set to 1, it skips the first line, which is typically the header. However, if the source file does not have a header row, or if the header is handled separately, setting skipLineCount to 1 will skip the first data row. The exhibit likely shows skipLineCount set to 1, causing the first data row to be omitted.

Answer

The 'writeBehavior' is set to 'insert', which does not overwrite existing rows

Answer

The 'recursive' setting is true, causing the pipeline to read files in subdirectories and duplicate data

Answer

The 'enableStaging' is false, which prevents PolyBase from loading data

Question 11

You are a Data Engineer at Contoso Ltd. The company operates an e-commerce platform that generates streaming data from user interactions (clicks, page views, purchases) at a rate of 50,000 events per second. The data is ingested into Azure Event Hubs. You need to design a data processing solution that meets the following requirements:

1. Real-time dashboards must display aggregated metrics (e.g., total sales, active users) with a latency of less than 2 seconds.
2. Historical data must be stored in Azure Data Lake Storage Gen2 in Delta Lake format for batch analytics.
3. The solution must support exactly-once semantics for the streaming output to both the dashboard and the data lake.
4. You must use Azure Stream Analytics as the primary processing engine.
5. The output to the dashboard should use Azure Power BI, while the output to the data lake should use Azure Synapse Analytics serverless SQL pool for querying.

You have configured an Azure Stream Analytics job with Event Hubs as input. For output, you added a Power BI dataset and an Azure Data Lake Storage Gen2 output. However, you discover that the Power BI dataset is being updated with duplicate records, and the data lake output sometimes misses small windows of data during job restarts. Which two actions should you take to meet the requirements? (Choose two.)

A. Change the Power BI output to use the 'Exactly once' output mode.
B. Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once.
C. Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output.
D. Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Accepted Answer

Change the Power BI output to use the 'Exactly once' output mode.. Power BI output supports 'Exactly once' output mode, which prevents duplicate records in the dashboard. Option C is correct because enabling checkpointing and using 'Exactly once' output mode for Azure Data Lake Storage Gen2 ensures exactly-once delivery to the data lake and prevents data loss during job restarts. Option B is incorrect because changing Event Hubs compatibility level to 1.0 does not guarantee exactly-once semantics; it only affects the serialization format. Option D is incorrect because using a second Stream Analytics job adds unnecessary complexity, and 'At least once' mode for Power BI would still allow duplicates.

Answer

Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once.

Answer

Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Question 12

You are building a data processing solution using Azure Databricks. The solution must process streaming data from Azure Event Hubs, join it with a static reference table stored in Azure Data Lake Storage Gen2 (Parquet format), and write the output to Azure Synapse Analytics. The reference table is updated daily. Which approach minimizes latency and ensures data consistency?

Accepted Answer

Use Spark Structured Streaming with a streaming join and cache the reference table as a static DataFrame.. Using Spark Structured Streaming with a streaming join and caching the reference table as a static DataFrame minimizes latency and ensures consistency by reading the reference table once and caching it for the duration of the stream. This avoids reloading the reference table in each micro-batch (as in option C) and avoids the latency of batch processing (as in option D). Option B, foreachBatch, is useful for writing to sinks like Synapse but does not address the join performance or consistency of the reference data.

Answer

Use Spark Structured Streaming with foreachBatch to write to Synapse.

Answer

Use Spark Structured Streaming with a streaming join and load the reference table in each micro-batch.

Answer

Use a batch job that runs every hour to process the data.

Question 13

You are using Azure Stream Analytics to process real-time temperature data from IoT devices. The output must be written to Azure SQL Database. The job has been running successfully for weeks, but recently you notice that the output data has duplicate rows. The input events are unique. The job uses a windowed aggregation (TumblingWindow). What is the most likely cause of duplicates?

Accepted Answer

The job is being restarted and reprocessing data.. When an Azure Stream Analytics job is restarted, it may reprocess data from the last checkpoint or from the beginning of the input stream, depending on the configured start time and output policy. This reprocessing can cause duplicate rows in the output, especially when using windowed aggregations like TumblingWindow, because the same events are aggregated again and written to Azure SQL Database without deduplication logic.

Answer

The job is not handling late-arriving events.

Answer

The input event hub is receiving duplicate events.

Answer

The tumbling window size is too small.

Question 14

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution must query data stored in Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and vary greatly. Which feature should you use to optimize query performance for frequently accessed data partitions?

Accepted Answer

Use OPENROWSET with explicit file path filtering.. Using OPENROWSET with explicit file path filtering is the correct optimization for ad-hoc queries against Parquet files in ADLS Gen2. Serverless SQL pool charges per byte scanned, so filtering the file paths (and using partition columns in the path) minimizes the data read and improves performance. This is the primary tuning technique for serverless SQL pool.

Answer

Implement workload management to prioritize queries.

Answer

Enable result-set caching on the serverless SQL pool.

Answer

Create materialized views on the Parquet files.

Question 15

You are tuning a Spark job in Azure Synapse Analytics that processes large Parquet files. The job currently takes too long due to data skew. Which three actions can improve performance? (Choose three.)

Accepted Answer

Use bucketing on the join key when writing intermediate data.. Options A, B, and E are correct. Repartitioning redistributes data. Salting adds a random key to break skew. Bucketing pre-partitions data. Option C is wrong because coalesce reduces partitions but does not address skew. Option D is wrong because increasing executor memory may help but does not directly solve skew.

Answer

Use coalesce to reduce the number of partitions.

Answer

Increase executor memory to handle larger partitions.