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

20 minute time limit — choose an answer to begin.

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.

Question 11

Refer to the exhibit. You run the KQL query in Azure Data Explorer. What is the output?

Accepted Answer

Name: Alice, Age: 30; Name: Charlie, Age: 35. The KQL query uses a `where` clause to filter rows where Age is greater than 25, which selects Alice (Age 30) and Charlie (Age 35) and excludes Bob (Age 25). The `project` operator then selects only the Name and Age columns, so the output consists of those two rows with those columns.

Answer

Id: 1, Name: Alice, Age: 30; Id: 3, Name: Charlie, Age: 35

Answer

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Answer

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Question 12

Refer to the exhibit. The pipeline fails because the source and sink datasets do not match. The source file is a CSV with columns: CustomerID, Name, City. The sink table dbo.Customer has columns: CustomerID, Name, City, CreatedDate (default GETDATE()). The pipeline uses auto-mapping. Why does the pipeline fail?

Accepted Answer

The pre-copy script is not allowed for SQL sink.. The pipeline fails because a pre-copy script is specified for the SQL sink, but pre-copy scripts are not allowed for SQL sinks in Azure Data Factory. This causes an error during the copy activity, even though auto-mapping could handle the extra column with a default value. The other options are incorrect: auto-mapping can handle extra sink columns with default values (so A is wrong), recursive setting does not affect this scenario, and writeBatchSize is not relevant to the failure.

Answer

Auto-mapping fails because the source and sink have different column counts.

Answer

The recursive setting should be false for CSV files.

Answer

The writeBatchSize is too large for the sink.

Question 13

You are monitoring a production data pipeline in Azure Data Factory that runs hourly. You notice that the pipeline has been failing for the last 3 hours due to a timeout error when writing to the sink Azure SQL Database. The database is a General Purpose tier with 100 DTUs. The pipeline writes approximately 1 million rows per run. What is the most effective way to prevent the timeout?

Accepted Answer

Increase the writeBatchSize in the sink settings to reduce the number of batch inserts.. Increasing the writeBatchSize reduces the number of batch inserts, which decreases round trips and can prevent timeouts. Option A is wrong because reducing copy parallelism increases the time taken per run, worsening timeout. Option B is wrong because using a staged copy with Azure Blob Storage adds additional I/O and does not directly address the timeout issue on the sink side. Option C is wrong because scaling up the DTU tier is more costly and may not be the most effective solution if the issue can be resolved by tuning batch size.

Answer

Reduce the degree of copy parallelism in the copy activity.

Answer

Enable staged copy using Azure Blob Storage as an interim store.

Answer

Scale up the Azure SQL Database to a higher DTU tier.

Question 14

Refer to the exhibit. You deploy this ARM template to create a managed integration runtime in Azure Synapse Analytics. You notice that the integration runtime shows as 'Running' but copy activities using it are slow. The data volume is 500 GB per run. What is the most likely cause of the poor performance?

Accepted Answer

The node size (Standard_D3_v2) is too small for the data volume.. The node size Standard_D3_v2 (4 vCPUs, 14 GB memory) is too small for processing 500 GB of data, leading to slow copy performance. Increasing node size (e.g., to Standard_D8_v3) would provide more resources. Option A is incorrect because changing from a managed integration runtime to a self-hosted runtime would not necessarily improve performance; managed runtimes are optimized for Azure-based data movement. Option C is incorrect because two nodes is typically sufficient for large data volumes; the bottleneck is node size, not node count. Option D is incorrect because 'AutoResolve' for location selects the optimal region and usually does not cause significant cross-region data movement; the location setting is not the primary cause of slow performance.

Answer

The integration runtime type should be 'Self-hosted' for better performance.

Answer

The number of nodes (2) is insufficient for 500 GB of data.

Answer

The location is set to 'AutoResolve' which may cause data movement across regions.

Question 15

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. You have Parquet files in Azure Data Lake Storage Gen2 partitioned by date and hour. You need to query the data for the last 7 days efficiently, minimizing the amount of data scanned. Which approach should you use?

Accepted Answer

Use OPENROWSET with a file path that limits to the last 7 days' partitions and use a WHERE clause on the date column.. Using OPENROWSET with a file path that restricts to the last 7 days' partitions (e.g., '/date=2025-03-*/hour=*/*.parquet') minimizes data scanned by only reading the relevant Parquet files. Applying a WHERE clause on the date column further filters rows within those files, leveraging partition pruning at the file system level. This approach avoids scanning all partitions, which is critical for cost and performance in serverless SQL pool.

Answer

Use OPENROWSET with a wildcard path that includes all partitions and then apply a WHERE clause.

Answer

Create an external table with partition definition and query with a WHERE clause on the partition column.

Answer

Create a view using PolyBase external table and query with a date filter.

Question 16

You are optimizing an Azure Synapse Pipeline that processes large volumes of data using a Mapping Data Flow. The pipeline is taking too long due to data skew. Which THREE techniques can help mitigate data skew?

Accepted Answer

Use a broadcast hash join to distribute the smaller table to all nodes.. A broadcast hash join in Azure Synapse Mapping Data Flow sends the smaller table to all nodes, eliminating the need for shuffling large datasets across the cluster. This reduces network I/O and mitigates data skew by ensuring each node processes a local copy of the small table, which is especially effective when one side of the join is significantly smaller than the other.

Answer

Increase the Data Integration Units (DIU) in the data flow.

Answer

Repartition the data on a different column using a random distribution.

Question 17

You are building a streaming pipeline in Azure Synapse Analytics to ingest real-time sensor data from IoT devices. The data must be processed with a 2-second latency and stored in a dedicated SQL pool for reporting. The source emits JSON messages with a nested structure. Which approach should you use to ingest and transform the data?

Accepted Answer

Use Azure Stream Analytics to ingest data from Azure Event Hubs, apply a query to flatten the JSON, and output directly to the dedicated SQL pool.. Azure Stream Analytics can ingest data from Azure Event Hubs, apply a query to flatten nested JSON in real-time (using functions like GetArrayElements and CROSS APPLY), and output directly to an Azure Synapse dedicated SQL pool via the built-in output adapter, achieving sub-2-second latency. Option A (Azure Synapse Spark with structured streaming) is possible but more complex, requiring custom code for JSON flattening and managing Spark clusters, and may not guarantee 2-second latency due to checkpointing overhead. Option C (Azure Data Factory with tumbling window trigger) is a batch approach with a minimum 2-second window but introduces orchestration delays and is not truly real-time. Option D (Azure Databricks with Auto Loader) is designed for incremental batch processing from cloud storage, not for direct Event Hubs ingestion, and Auto Loader does not handle nested JSON flattening natively.

Answer

Use Azure Synapse Spark with structured streaming to read from Event Hubs, flatten the JSON using Spark SQL, and write to the dedicated SQL pool.

Answer

Use Azure Data Factory to run a tumbling window trigger that reads from Event Hubs every 2 seconds and copies data to the dedicated SQL pool.

Answer

Use Azure Databricks with Auto Loader to ingest data from Event Hubs and write to the dedicated SQL pool.

Question 18

You have a mission-critical pipeline that processes financial transactions in Azure Synapse Analytics. The pipeline uses Azure Data Factory with a mapping data flow to transform data. You need to ensure high availability and minimal data loss in case of a regional failure. What should you implement?

Accepted Answer

Store the source data in a GRS storage account and use Azure Data Factory to copy from the secondary endpoint.. Using a GRS storage account ensures geo-redundancy, and Azure Data Factory can copy data from the secondary endpoint in case of a regional failure. This provides high availability and minimal data loss for the source data, while the mapping data flow transformation can proceed after the copy. Option D is incorrect because Azure Synapse Link for Cosmos DB changes the architecture (using Cosmos DB as source) and does not directly provide high availability for the existing ADF pipeline with mapping data flows; it introduces a different technology stack that may not meet the requirement of pipeline resilience.

Answer

Configure the pipeline to retry on failure and manually restore from backup.

Answer

Use Azure SQL Database active geo-replication as the source.

Answer

Use Azure Synapse Link for Cosmos DB to enable near real-time analytics with multi-region writes.

Question 19

You need to transform semi-structured JSON data into a tabular format for analysis in Azure Synapse Analytics. The data is stored in ADLS Gen2. Which feature should you use to query the JSON data directly without loading it into a table?

Accepted Answer

Use the OPENROWSET function in a serverless SQL pool.. OPENROWSET in Synapse serverless SQL can query JSON files directly. Option B (Azure Data Factory) flattens JSON but is an orchestration tool, not a direct query method. Option C (PolyBase) requires external tables. Option D (COPY INTO) loads data into a table, not direct query.

Answer

Use Azure Data Factory to flatten the JSON and store as Parquet.

Answer

Create an external table using PolyBase.

Answer

Use the COPY INTO command in a dedicated SQL pool.

Question 20

You need to process a large number of CSV files stored in Azure Data Lake Storage Gen2 using Azure Databricks. The files are nested in multiple folders, and the schema varies slightly between files. You want to automatically infer the schema and handle schema evolution. Which read option should you use?

Accepted Answer

spark.read.option("inferSchema","true").csv(path). The best choice because `inferSchema` allows Spark to automatically detect the schema from the CSV files. However, it does not handle schema evolution across files with varying schemas; each file's schema is inferred independently, and if schemas differ, you may encounter errors or missing columns. For true schema evolution, you would need to use a custom schema or union DataFrames. Options A and B are invalid: A uses `mergeSchema`, which is not a standard CSV read option—it is used for Delta Lake or Parquet—and B reads Delta format, not CSV. Option D reads without any schema inference or evolution handling.

Answer

spark.read.option("mergeSchema","true").csv(path)

Answer

spark.read.format("delta").load(path)

Answer

spark.read.csv(path)