DP-203 Develop data processing — 10 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?

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

Accepted Answer

Enable Event Hubs Capture to write data to Azure Data Lake Storage and then load using PolyBase.. Event Hubs Capture continuously writes data to Azure Data Lake Storage, providing a durable buffer that prevents data loss during transient failures. Once data is in ADLS, PolyBase reliably loads it into the dedicated SQL pool. This architecture ensures data is persisted at the event hub level before any pipeline processing, minimizing loss.

Answer

Use Azure Synapse Pipeline with Auto-commit and checkpointing to process streaming data.

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.

Question 2

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 Databricks with Structured Streaming. Azure Databricks with Structured Streaming is the correct choice because it natively supports exactly-once semantics when writing to Delta Lake from Event Hubs. Structured Streaming uses checkpointing and a write-ahead log to ensure each record is processed exactly once, even in the face of failures. Azure Databricks runs on Spark, which integrates seamlessly with both Event Hubs (via the Event Hubs connector) and Delta Lake (as a sink). Other options are either batch-oriented or lack the necessary transactional guarantees for exactly-once delivery to Delta Lake.

Answer

Azure Synapse serverless SQL pool

Answer

Azure Synapse Pipeline with Mapping Data Flow

Answer

Azure Stream Analytics

Question 3

You are designing a batch processing solution in Azure Data Factory. The source is an Azure Blob Storage container with CSV files. The target is an Azure SQL Database. The pipeline must run daily and incrementally load only new or changed rows. Which Data Factory feature should you use?

Accepted Answer

Mapping Data Flow with a watermark column. Mapping Data Flows in Azure Data Factory can use a watermark column (e.g., LastModifiedDate or an incrementing ID) to perform incremental loads from Azure Blob Storage CSV files. This approach identifies only new or changed rows since the last pipeline run, avoiding full reloads. Option D (CDC resource) is designed for transactional databases with change tracking, not for flat files like CSV in Blob Storage, making it unsuitable for this scenario.

Answer

Stored Procedure activity to run a merge statement

Answer

Copy activity with a full load every time

Answer

Change data capture (CDC) resource

Question 4

Which TWO Azure services can be used to perform real-time data processing on streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a fully managed stream processing engine that can process high volumes of streaming data with low latency using SQL-like queries. Azure Databricks provides Apache Spark Structured Streaming, which supports real-time stream processing with exactly-once semantics, windowing, and stateful operations. Both services are designed for real-time data processing on streaming data. In contrast, Azure Synapse Analytics dedicated SQL pool is optimized for data warehousing and batch/ interactive queries, not for real-time streaming ingestion or processing; although it can load streaming data, it does not perform continuous real-time processing. Azure Data Factory and Logic Apps are integration and workflow services, not stream processing engines.

Answer

Azure Synapse Analytics (dedicated SQL pool)

Answer

Azure Data Factory

Answer

Azure Logic Apps

Question 5

Which TWO configuration options are required to enable change data capture (CDC) in Azure Data Factory for incremental loading from Azure SQL Database?

Accepted Answer

Enable CDC on the source Azure SQL Database.. To enable change data capture (CDC) in Azure Data Factory for incremental loading from Azure SQL Database, two configuration steps are required. First, CDC must be enabled at the database level on the source Azure SQL Database for the specific tables to be tracked (Option B). Second, within Azure Data Factory, when configuring the source dataset for a copy activity, you must select the 'Change data capture' option in the dataset settings. This is not a separate Azure resource; it is a configuration property within the dataset (Option C). Options A, D, and E are not required: CDC logs are stored internally by SQL Server; no dedicated blob container is needed; a staging database is not necessary; and a full load is not a requirement for CDC, as CDC tracks only changes after it is enabled.

Answer

Create a dedicated folder in Azure Blob Storage for CDC logs.

Answer

Provision a staging Azure SQL Database.

Answer

Configure a full load of the entire table first.

Question 6

You have a pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection closed' error after 1 hour. The data volume is 500 GB over a slow network. What is the most likely cause and solution?

Accepted Answer

The source SQL Server query command timeout is set to 1 hour; increase the command timeout or use staging copy.. The 'Connection closed' error after exactly 1 hour indicates the SQL Server source query command timeout (default 60 minutes) is being hit. When copying 500 GB over a slow network, the query execution exceeds this timeout, causing the connection to drop. Increasing the command timeout or using staging copy (which breaks the query into chunks) resolves this by allowing the query to run longer or reducing the per-query duration.

Answer

The network bandwidth is insufficient; use a faster network connection.

Answer

The copy activity is using a single parallel copy; increase the degree of copy parallelism.

Answer

The Self-Hosted Integration Runtime (SHIR) has a time-to-live (TTL) setting that is too short; extend the TTL.

Question 7

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is slow. Which action would most likely improve performance?

Accepted Answer

Create statistics on the columns used in WHERE clauses. In Azure Synapse Analytics serverless SQL pool, query performance on external data like Parquet files heavily relies on statistics to enable the optimizer to generate efficient query plans. Without statistics, the engine makes default cardinality estimates, often leading to suboptimal joins and filters. Creating statistics on columns used in WHERE clauses allows the serverless SQL pool to accurately estimate row counts and choose better execution strategies, directly improving query performance. While serverless SQL pool does support partition pruning through folder hierarchies, the benefits of partition elimination are limited to reducing data scanned; statistics provide more critical metadata for cost-based optimization, making them the most impactful choice for slow queries.

Answer

Partition the Parquet files by date

Answer

Create a materialized view on the external data

Answer

Create a PolyBase external table and query that

Question 8

Which THREE of the following are best practices for optimizing performance of Delta Lake tables in Azure Synapse Analytics? (Choose three.)

Accepted Answer

Run the OPTIMIZE command to compact small files and improve read performance.. The OPTIMIZE command compacts small files into larger ones, reducing file listing overhead and improving read performance. Option C is correct because partitioning on columns used in WHERE clauses enables partition pruning, which limits the data scanned. Option E is correct because Z-ordering colcates related data on frequently filtered columns, enhancing data skipping. Option B (VACUUM) is not considered a performance optimization best practice; it is primarily for storage management and compliance by removing old file versions, and its impact on query performance is indirect. Option D is incorrect because partitioning on high-cardinality columns leads to many small partitions, causing metadata bloat and poor performance.

Answer

Periodically run VACUUM to remove old versions of files that are no longer needed.

Answer

Partition on high-cardinality columns like UserID to maximize parallelism.

Question 9

You are designing a data ingestion pipeline for Azure Synapse Analytics using PolyBase. The source data is in Azure Blob Storage, with files in the Parquet format. The data volume is approximately 2 TB daily. You need to minimize the time to load data into a dedicated SQL pool table. Which approach should you recommend?

Accepted Answer

Create an external table pointing to the Parquet files and use CREATE TABLE AS SELECT (CTAS) to load the data.. Using CREATE TABLE AS SELECT (CTAS) with an external table pointing to Parquet files leverages PolyBase's massively parallel processing capabilities. While the COPY INTO command does support Parquet format in dedicated SQL pools, CTAS offers superior performance for large-scale daily ingestion (2 TB) as it allows for distribution and index optimizations during the load, and it reads the compressed columnar Parquet data directly, minimizing I/O. CTAS also integrates seamlessly with PolyBase's external table architecture, making it the optimal choice for minimizing load time.

Answer

Split the Parquet files into many small files (e.g., 64 MB each) to maximize parallelism.

Answer

Convert the Parquet files to CSV format before loading using PolyBase.

Answer

Use the COPY INTO command to load the Parquet files directly into the dedicated SQL pool.

Question 10

You are optimizing the performance of a large-scale batch processing job in Azure Databricks. The job reads data from Azure Data Lake Storage Gen2, performs transformations, and writes results back. You notice that the job is I/O bound. Which THREE strategies can improve performance? (Choose three.)

Accepted Answer

Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.. Z-ordering in Delta Lake clusters data on frequently filtered columns, reducing the amount of data scanned during reads from Azure Data Lake Storage Gen2. Option B is correct: increasing the number of partitions can improve parallelism for reading and writing data, which helps in I/O-bound scenarios by allowing more concurrent I/O operations. Option C is correct: caching the DataFrame in memory avoids re-reading from disk when the data is reused, reducing I/O. Option D is incorrect: reducing shuffle partitions minimizes data movement during shuffles, not the initial I/O from storage. Option E is incorrect: enabling autoscaling adds compute nodes, which does not directly address storage I/O bottlenecks.

Answer

Reduce the number of shuffle partitions to minimize data movement.

Answer

Enable autoscaling on the cluster to add more nodes during processing.