DP-203 Develop data processing — Timed 100 Questions

Question 1

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 volume is approximately 500 GB per hour with peak spikes. The pipeline must minimize data loss during transient failures. Which feature should you implement?

Accepted Answer

Use Azure Synapse Pipeline with Auto-commit and checkpointing to process streaming data.. Option A is correct because Azure Synapse Pipeline with Auto-commit and checkpointing provides exactly-once processing semantics for streaming data from Event Hubs, ensuring no data loss during transient failures by committing offsets only after successful writes to the dedicated SQL pool. This feature is designed for high-volume streaming (500 GB/hour) and handles peak spikes through parallelization and retry logic, making it the optimal choice for minimizing data loss.

Answer

Use PolyBase to load data directly from Event Hubs to the dedicated SQL pool.

Answer

Use COPY INTO statement to ingest data from Event Hubs into the dedicated SQL pool.

Answer

Enable Event Hubs Capture to write data to Azure Data Lake Storage and then load using PolyBase.

Question 2

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 3

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.. Option C is correct because 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 4

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.. Option D is correct because 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 5

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.. Option C is correct because 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 6

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.. Option A is correct because `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 7

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 8

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.. Option C is correct because 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 9

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.. Option C is correct because the Until activity with a dynamic pagination logic is appropriate for looping until all pages are fetched. Option A is incorrect because the Copy activity alone does not handle pagination. Option B is incorrect because ForEach is for iterating a fixed set of items, not dynamic pagination. Option D is incorrect because Lookup activity returns a single value, not suitable for pagination.

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 10

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). Option B is correct because 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)

Question 11

You are working with Azure Synapse Analytics serverless SQL pool. You need to query a set of Parquet files located in ADLS Gen2. The files have nested columns (structs and arrays). Which function should you use to flatten the nested data?

Accepted Answer

OPENJSON. OPENJSON is the correct function because it parses JSON text and returns objects and properties from JSON input as rows and columns. In Azure Synapse serverless SQL pool, when Parquet files contain nested columns (structs and arrays), they are exposed as JSON strings, and OPENJSON can flatten these nested structures into a relational format. This allows you to query complex nested data directly without needing to pre-process the files.

Answer

PIVOT

Answer

UNNEST

Answer

CROSS APPLY

Question 12

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is ingested from multiple sources and needs to be deduplicated based on a composite key (source_id, record_id). New data may have duplicates within the same batch. Which write mode and table property should you use to handle this efficiently?

Accepted Answer

Use 'append' mode and perform a MERGE operation after write to deduplicate.. Option A is correct because using 'append' mode writes all incoming data as new files, and then performing a MERGE operation (upsert) based on the composite key (source_id, record_id) allows you to efficiently deduplicate both within the batch and against existing data. This approach leverages Delta Lake's ACID transactions and avoids the cost of rewriting entire partitions, making it suitable for handling duplicates from multiple sources.

Answer

Use 'overwrite' mode and enable 'delta.autoOptimize.optimizeWrite' = true.

Answer

Use 'ignore' mode and set 'delta.autoCompact' = true.

Answer

Use 'error' mode and enable 'delta.merge.onSchemaMismatch' = true.

Question 13

You are configuring a data pipeline in Azure Data Factory that uses a Mapping Data Flow. The source is a SQL Server table with 50 million rows. The sink is a Delta table in ADLS Gen2. The pipeline runs slowly. You need to improve performance by reducing the number of partitions in the data flow. Which setting should you adjust?

Accepted Answer

In the sink transformation, set the 'Optimize' tab to 'Single partition'.. Option C is correct because setting the sink transformation's 'Optimize' tab to 'Single partition' forces all data to be written into a single partition, which reduces the number of output files and the associated metadata overhead. This can improve performance when the downstream processing or storage benefits from fewer, larger files, especially for small-to-medium datasets or when the sink is a Delta table that must be compacted later.

Answer

In the source transformation, set the 'Optimize' tab to 'Use current partitioning'.

Answer

Decrease the cluster size to 4 cores.

Answer

In the pipeline activity, set the 'Data flow' property 'partitionOption' to 'Single partition'.

Question 14

You are using Azure Synapse Analytics to process streaming data from Azure Event Hubs. The data must be written to a Delta Lake table in ADLS Gen2 with exactly-once semantics. Which processing engine should you use?

Accepted Answer

Azure Synapse Pipeline with Mapping Data Flow. Option C is correct because Azure Synapse Pipeline with Mapping Data Flow supports Delta Lake as a sink and can be configured to use Spark-based execution for streaming data from Event Hubs, enabling exactly-once semantics through checkpointing and idempotent writes. Mapping Data Flow runs on Spark clusters within Synapse, providing the necessary transactional guarantees for Delta Lake tables.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Synapse serverless SQL pool

Answer

Azure Stream Analytics

Question 15

You are designing a batch processing pipeline in Azure Databricks. The data is stored in Delta Lake and you need to perform a time-series join between two tables: 'events' (100 billion rows) and 'sessions' (10 billion rows). The join condition is on 'device_id' and a timestamp range (event_time BETWEEN session_start AND session_end). Which join strategy would be most efficient?

Accepted Answer

Use a range join with interval threshold using Delta Lake's optimized join.. Option B is correct because Delta Lake's optimized range join leverages interval threshold pruning and data skipping to efficiently handle time-series joins on large datasets. This strategy avoids full shuffles by using min/max statistics and Bloom filters to eliminate non-matching partitions, making it far more efficient than generic join methods for 100B and 10B row tables.

Answer

Broadcast the smaller table (sessions) to all nodes.

Answer

Use a sort-merge join by repartitioning both tables on device_id.

Answer

Bucket both tables on device_id with 500 buckets.

Question 16

You are monitoring an Azure Data Factory pipeline that runs every hour. The pipeline uses a Copy activity to copy data from Azure SQL Database to Azure Blob Storage. Recently, the pipeline has been failing with a 'Timeout' error. The source SQL database has a large number of records. What should you do to resolve the timeout?

Accepted Answer

Enable staging and use PolyBase or COPY statement for the copy activity.. Option A is correct because enabling staging with PolyBase or the COPY statement offloads the data transfer to Azure Data Lake or Blob Storage, bypassing the bottleneck of the Copy activity's default data movement. This approach is specifically designed for large-scale data loads from Azure SQL Database, as it uses the database's bulk export capabilities and avoids the timeout by not relying on the Copy activity's internal query execution.

Answer

Decrease the 'writeBatchSize' to 1000.

Answer

Increase the 'timeout' value in the copy activity settings.

Answer

Use a query with 'queryTimeout' set to 7200 seconds.

Question 17

You are designing a data pipeline that uses Azure Data Factory to load data from an FTP server to Azure Data Lake Storage. The FTP server requires authentication with username and password. Which type of linked service should you create?

Accepted Answer

FTP. Option A is correct because FTP linked service supports username/password authentication. Option B is incorrect because HTTP linked service is for HTTP endpoints. Option C is incorrect because Azure Blob Storage linked service is for Azure blobs. Option D is incorrect because Rest service is for REST APIs.

Answer

Azure Blob Storage

Answer

HTTP

Answer

Rest service

Question 18

You are using Azure Synapse Analytics dedicated SQL pool to run a query that joins a large fact table (10 billion rows) and a small dimension table (1 million rows). The query is slow. Which distribution strategy should you use for the dimension table to improve performance?

Accepted Answer

Replicate the dimension table to all compute nodes.. Replicating the small dimension table (1 million rows) to all compute nodes eliminates data movement during the join with the large fact table (10 billion rows). In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so the join can be performed locally on every node without shuffling data across the network, drastically reducing query latency.

Answer

Round-robin distribute the dimension table.

Answer

Hash-distribute the dimension table on its primary key.

Answer

Hash-distribute the dimension table on the foreign key column.

Question 19

You are designing a data processing solution in Azure Synapse Analytics. The solution must support incremental loading of data from an Azure SQL Database to a dedicated SQL pool using PolyBase. Which approach should you use to minimize data movement and maximize performance?

Accepted Answer

Create external tables in the dedicated SQL pool that reference the source data, then use CREATE TABLE AS SELECT (CTAS) to load incrementally.. Option B is correct because using external tables with PolyBase in Azure Synapse Analytics allows you to directly query the source Azure SQL Database without moving the data first. The CREATE TABLE AS SELECT (CTAS) statement then loads only the incremental data into the dedicated SQL pool, minimizing data movement by leveraging PolyBase's parallel streaming capability for maximum performance.

Answer

Use the bcp utility to export data from Azure SQL Database to a text file, then bulk insert into the dedicated SQL pool.

Answer

Use Azure Data Factory with a copy activity to load data into staging tables, then merge into the target table.

Answer

Use Azure Databricks to read the source data, apply transformations, and write to the dedicated SQL pool using the Spark connector.

Question 20

You are troubleshooting a pipeline in Azure Data Factory that copies data from an Azure Blob Storage to an Azure Synapse Analytics dedicated SQL pool. The pipeline fails with the error: 'PolyBase requires a varchar(max) column to be less than 1 MB.' Which action should you take to resolve this issue?

Accepted Answer

Configure the copy activity to use staging via Azure Blob Storage.. The error indicates that PolyBase is being used for the copy operation, and it has a limitation that varchar(max) columns must be less than 1 MB. Configuring staging via Azure Blob Storage (option B) allows the copy activity to use PolyBase with staging, which automatically splits large varchar(max) values into manageable chunks, bypassing the 1 MB limit. This is the recommended approach in Azure Data Factory for loading large string data into Synapse dedicated SQL pools.

Answer

Modify the source data to truncate varchar(max) columns to 8000 characters.

Answer

Increase the 'batchSize' property in the copy activity to 10000.

Answer

Disable PolyBase in the sink settings and use bulk insert instead.