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

Question 1

A small business uses Azure SQL Database as their operational data store. They want to build a simple reporting solution that refreshes daily. The data volume is about 10 GB. They have limited budget and no dedicated data engineering team. They need to extract data from Azure SQL Database, perform basic transformations (e.g., aggregations, joins), and load into a reporting table in the same database. They want to minimize administrative effort and cost. Which approach should they take?

Option A: Use Azure Data Factory with a copy activity to copy data from the source to a staging table, then use a stored procedure activity to run T-SQL transformations.

Option B: Use Azure Logic Apps to periodically query the source and insert transformed data into the reporting table.

Option C: Use Azure Databricks notebooks scheduled via Azure Data Factory to perform the ETL.

Option D: Use Azure SQL Database elastic job to run a T-SQL script that performs the ETL within the database.

Accepted Answer

Option D. Using an Azure SQL Database elastic job to run a T-SQL script directly within the database is the simplest, lowest-cost, and lowest-administration approach for this scenario. It avoids external services, requires no data movement, and leverages existing SQL skills, making it ideal for a small business with limited budget and no dedicated data engineering team. (Note: Option C corresponds to the approach listed as Option D in the original question options.)

Answer

Option B

Answer

Option A

Answer

Option C

Question 2

You are implementing a data pipeline in Azure Synapse Analytics that uses PolyBase to load data from Azure Data Lake Storage Gen2 into a dedicated SQL pool. The pipeline runs nightly and processes approximately 500 GB of data. You notice that the load operation is slow and frequently times out. What should you do to improve performance?

Accepted Answer

Increase the resource class for the loading user.. Increasing the resource class in dedicated SQL pool provides more resources (CPU, memory) to the PolyBase load operation, which directly addresses the timeout issue. Option A is wrong because columnstore indexes are relevant for query performance, not load speed. Option B is wrong because the data type of the clustered index does not impact PolyBase load performance. Option D is wrong because round-robin distribution can improve load speed, but the primary issue is resource allocation.

Answer

Create a columnstore index on the target table.

Answer

Use a clustered index on the target table.

Answer

Change the distribution of the target table to round-robin.

Question 3

You are a data engineer at a financial services company. You are developing a data processing pipeline that uses Azure Data Factory to copy transactional data from an Azure SQL Database to Azure Data Lake Storage Gen2. The pipeline runs daily and processes about 10 GB of data. You need to implement error handling for the pipeline. Specifically, if the copy activity fails due to a transient error, the pipeline should retry automatically. If the retry fails, the pipeline should log the error and send an email alert to the operations team. What should you do?

Accepted Answer

Configure the copy activity with retry policy (retry count = 2, retry interval = 30 seconds). Add a failure path to a web activity that calls an Azure Logic App to send an email.. Azure Data Factory allows you to set retry count and retry interval on the copy activity, and you can use a web activity to call an Azure Logic App to send an email alert on failure. Option B is incorrect because while Azure Functions could be used, it adds unnecessary complexity; Azure Data Factory's built-in retry and web activity provide a simpler solution. Option C is incorrect because Azure Monitor alerts are for monitoring after pipeline runs, not for handling errors within the pipeline itself. Option D is incorrect because pipeline-level retry applies to all activities, not specific to the copy activity, and using a storage event trigger on an error file does not send an email directly.

Answer

Use Azure Functions to implement custom retry logic and send email.

Answer

Create an Azure Monitor alert for failed pipeline runs and configure an action group to send an email.

Answer

Set the pipeline retry to 2 and add a storage event trigger on the error file.

Question 4

You are designing a data processing solution for a media company that uses Azure Synapse Analytics. The solution must process video metadata stored in Azure Cosmos DB and combine it with user interaction data from Azure Data Lake Storage Gen2. The combined data must be stored in a dedicated SQL pool for reporting. The data volume is moderate, and the processing should be done using T-SQL. Which approach should you use?

Accepted Answer

Use PolyBase to create external tables on Cosmos DB and ADLS Gen2, then use INSERT...SELECT to load into dedicated SQL pool.. You can use PolyBase in the dedicated SQL pool to create external tables that point to Azure Cosmos DB (via Synapse Link analytical store) and Azure Data Lake Storage Gen2. Then, using T-SQL INSERT...SELECT, you can load the combined data from these external tables into a local table in the dedicated SQL pool for reporting. Option A is incorrect because Azure Data Factory uses Copy activities, not T-SQL. Option B is incorrect because Azure Databricks uses Spark SQL, not T-SQL. Option C is incorrect because CREATE EXTERNAL TABLE AS SELECT (CETAS) writes to an external table in the data lake, not to a table in the dedicated SQL pool.

Answer

Use Azure Data Factory with Copy activities to load data from Cosmos DB and ADLS Gen2 into the dedicated SQL pool, then use a stored procedure to merge.

Answer

Use Azure Databricks to read from Cosmos DB and ADLS Gen2, transform with Spark SQL, and write to the dedicated SQL pool using JDBC.

Answer

Use the serverless SQL pool to create external tables on Cosmos DB and ADLS Gen2, then use CREATE EXTERNAL TABLE AS SELECT (CETAS) to write the combined data to a table in the dedicated SQL pool.

Question 5

You are designing a data processing solution for a retail company that uses Azure Synapse Analytics. The solution must process point-of-sale (POS) data from multiple stores. The data arrives in CSV files in Azure Data Lake Storage Gen2. Each store sends a file every hour. You need to process the files as they arrive and load the data into a dedicated SQL pool. The solution must handle late-arriving files (files that arrive after the scheduled processing time) and ensure that the data is consistent. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a Copy activity to load data into a staging table in the dedicated SQL pool, then use a Stored Procedure activity to merge the data into the final table.. The correct approach because it uses a staging table and a stored procedure with merge logic to handle upserts and late-arriving data efficiently within Azure Synapse Analytics. Azure Data Factory orchestrates the copy to staging and the execution of the stored procedure. Option A is incorrect because using a Data Flow activity for upserts on a dedicated SQL pool is less efficient and more complex than using a stored procedure; Data Flow does not natively support late-arriving file handling as well as a merge stored procedure. Option B is incorrect because Azure Databricks introduces unnecessary overhead and complexity for a task that can be accomplished with Azure Data Factory and Synapse's native capabilities. Option C is incorrect because PolyBase with CREATE TABLE AS SELECT (CTAS) does not directly support upserts; it loads data as a new table, requiring additional steps to merge late-arriving data.

Answer

Use Azure Data Factory with a Copy activity to load data into a staging table, then use a Data Flow activity to perform upserts.

Answer

Use Azure Databricks to read the CSV files, perform upserts, and write to the dedicated SQL pool using JDBC.

Answer

Use PolyBase to create external tables on the CSV files and then use CREATE TABLE AS SELECT to load into the dedicated SQL pool.

Question 6

Your company is building a real-time dashboard for monitoring website traffic. The data is ingested from web servers into Azure Event Hubs. You need to design a stream processing solution using Azure Stream Analytics that computes the number of unique visitors per minute, per country, and outputs the results to Azure Synapse Analytics for reporting. The solution must handle out-of-order events with a maximum late arrival of 10 seconds. You also need to ensure exactly-once semantics for the output. Which combination of settings should you use?

Accepted Answer

Use a Tumbling window of size 1 minute, set late arrival policy to 10 seconds, and configure output to Azure Synapse Analytics with `Exactly once` semantics.. A Tumbling window of size 1 minute creates non-overlapping windows that count events per minute, which fits the requirement of unique visitors per minute per country. The late arrival policy of 10 seconds handles out-of-order events within the allowed lateness. Azure Synapse Analytics supports exactly-once semantics when configured properly (e.g., using idempotent writes). Option A is incorrect because a Sliding window emits results continuously based on a sliding time interval, not per-minute windows, and 'At least once' does not guarantee exactly-once semantics. Option B is incorrect because a Session window is used to group events that are close in time with periods of inactivity; it does not provide fixed-minute windows, and the timeout/max duration settings are misapplied. Option C is incorrect despite the Hopping window with 1-minute size and hop being equivalent to a Tumbling window, because the output mode 'Exactly Once' is a job-level setting that is not sufficient by itself; exactly-once semantics must be supported by the output adapter, and while Azure Synapse Analytics can support it, option C does not specify the output configuration details needed.

Answer

Use a Sliding window of length 1 minute, set out-of-order policy to 10 seconds, and use 'At least once' output mode.

Answer

Use a Session window with timeout of 1 minute and maximum duration of 10 seconds, set late arrival to 10 seconds, and use 'Exactly once' output mode.

Answer

Use a Hopping window of size 1 minute and hop 1 minute, set late arrival policy to 10 seconds, and use 'Exactly Once' output mode.

Question 7

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

Accepted Answer

Broadcast the smaller table (1 million rows) to all worker nodes.. Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Answer

Increase the cluster size to reduce shuffle overhead.

Answer

Create bucketed tables on the join key for both datasets.

Answer

Use Delta Lake and optimize file layout with OPTIMIZE command.

Question 8

You are running a Spark job in Azure Synapse Analytics that reads from a Delta Lake table and performs multiple transformations. The job fails with an out-of-memory error on the executors. Which action should you take first to resolve the issue?

Accepted Answer

Increase the executor memory setting in the Spark configuration.. An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Answer

Enable checkpointing to truncate the lineage.

Answer

Decrease the number of partitions to reduce overhead.

Answer

Use the cache() action on intermediate DataFrames.

Question 9

You are designing a data pipeline in Azure Data Factory (ADF) that copies data from an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The pipeline must run daily and handle incremental loads efficiently. Which sink dataset type and copy method should you use?

Accepted Answer

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use Stored Procedure with staging table and PolyBase.. It uses a staging table and PolyBase to efficiently load incremental data into Azure Synapse Analytics dedicated SQL pool. PolyBase provides high-throughput parallel loading, and the stored procedure handles the merge logic (upsert) to manage incremental changes. This approach is recommended for large-scale, daily incremental loads to Synapse.

Answer

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use the Copy activity with PolyBase enabled.

Answer

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and enable the built-in Upsert option.

Answer

Use Azure Blob Storage as the sink dataset, then use PolyBase to load into the dedicated SQL pool.

Question 10

You are implementing a streaming solution using Azure Stream Analytics. The input is from an IoT Hub receiving telemetry from thousands of devices. The output is to Azure Synapse Analytics dedicated SQL pool. The requirement is to compute rolling averages over a 5-minute tumbling window and write results every minute. Which windowing function and output configuration should you use?

Accepted Answer

Use a HoppingWindow with size 5 minutes and hop 1 minute.. A HoppingWindow with a size of 5 minutes and a hop of 1 minute allows you to compute rolling averages over a 5-minute window while producing results every minute. This satisfies the requirement of outputting results at a higher frequency than the window duration, which is not possible with a TumblingWindow (which only outputs at the end of the window) or a SlidingWindow (which outputs on each event, not at fixed intervals).

Answer

Use a TumblingWindow with duration of 5 minutes and output every 5 minutes.

Answer

Use a SlidingWindow with duration 5 minutes and output every 1 minute.

Answer

Use a SessionWindow with timeout 5 minutes and maximum duration 10 minutes.

Question 11

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Accepted Answer

Use coalesce() to reduce the number of partitions without a shuffle.. `coalesce()` reduces the number of partitions without triggering a full shuffle, which minimizes the number of output files while preserving performance. Since the DataFrame already has 100 million rows and 20 columns, coalescing to fewer partitions directly reduces the number of files written, addressing the small-file problem efficiently.

Answer

Enable caching on the DataFrame before writing.

Answer

Apply bucketing on a column to group data.

Answer

Increase the number of partitions using repartition() with a larger number.

Question 12

You are designing a data processing solution using Azure Databricks. The data is stored in Delta Lake format. You need to ensure that when you read the latest version of the table, you only see committed data and not uncommitted transactions. Which isolation level should you use?

Accepted Answer

SnapshotIsolation. Snapshot isolation is the correct choice because it provides a consistent view of the table by reading only the latest committed data, ignoring any uncommitted transactions. In Delta Lake, snapshot isolation ensures that readers see a snapshot of the table at a specific version, which includes only committed changes, making it ideal for read consistency without blocking concurrent writes.

Answer

WriteSerializable

Answer

Serializable

Answer

ReadUncommitted

Question 13

You are monitoring an Azure Synapse Pipeline that uses a Mapping Data Flow. The data flow processes 2 GB of data from a CSV source and writes to a Delta sink. The pipeline fails with a 'DataFlowException: Operation aborted' error after running for 45 minutes. The cluster is configured with 8 cores. What is the most likely cause?

Accepted Answer

The data flow cluster's time-to-live (TTL) is set to 45 minutes and the job exceeded it.. The error 'Operation aborted' after exactly 45 minutes aligns with the default time-to-live (TTL) setting for Azure Synapse Mapping Data Flow clusters. When the TTL expires, the cluster is terminated, and any running job is aborted. The 8-core cluster and 2 GB data volume are not inherently problematic for a 45-minute window, but the TTL default of 45 minutes causes the abort if the job runs longer than that.

Answer

The cluster size is too small for the data volume.

Answer

The CSV source contains malformed rows that cause parsing errors.

Answer

The data flow is using the Spark cluster's default timeout setting.

Question 14

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

Accepted Answer

Use a Copy activity with pagination rules enabled in the source.. The Copy activity in Azure Data Factory supports pagination rules, allowing it to automatically iterate through API pages without a separate loop activity. Option A is incorrect because a ForEach activity would need to know the exact number of pages upfront, which is not dynamic. Option B is incorrect because the Lookup activity retrieves a single value and cannot iterate through pages. Option C is incorrect because an Until activity would require custom logic to check for the next page, whereas the Copy activity with pagination rules handles this natively.

Answer

Use a ForEach activity to iterate over a fixed number of pages.

Answer

Use a Lookup activity to retrieve the total number of pages and then use a ForEach.

Answer

Use an Until activity to loop until the API returns no more pages.

Question 15

You are designing a streaming job in Azure Stream Analytics. The job needs to count the number of events per device type every 10 seconds. The input is from Event Hubs. Which query should you use?

Accepted Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, TumblingWindow(second, 10). A TumblingWindow(second, 10) produces non-overlapping, fixed-size 10-second windows, which is exactly what is needed to count events per device type every 10 seconds. The GROUP BY clause groups by DeviceType and the window, ensuring each device type gets its own count per window. This query meets the requirement without overlapping or sliding behavior.

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SessionWindow(second, 10, 30)

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, HoppingWindow(second, 10, 1)

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SlidingWindow(second, 10)