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

Question 1

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?

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

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 2

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 3

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 4

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 5

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)

Question 6

Which TWO are valid output sinks for an Azure Stream Analytics job? (Choose two.)

Accepted Answer

Azure SQL Data Warehouse (dedicated SQL pool). Azure Stream Analytics supports output to Azure SQL Data Warehouse (dedicated SQL pool) and Azure Blob Storage. Azure Blob Storage can store streaming data in formats such as Avro, CSV, JSON, and Parquet. Azure SQL Data Warehouse (now part of Azure Synapse Analytics) allows real-time streaming data to be stored in a relational data warehouse for analytics. Azure Cosmos DB (SQL API) and Azure Table Storage are not valid output sinks. Note that Azure SQL Data Warehouse and Azure Synapse Analytics are essentially the same service; the dedicated SQL pool in Synapse is the former SQL Data Warehouse.

Answer

Azure Cosmos DB (SQL API)

Answer

Azure Table Storage

Answer

Azure Synapse Analytics

Question 7

You are designing a data processing solution for a retail company. The solution must ingest clickstream data from a website, process it in near real-time to update user session information, and store the results in Azure Cosmos DB for low-latency queries. Which combination of Azure services should you use?

Accepted Answer

Azure Event Hubs -> Azure Functions -> Azure Cosmos DB. Azure Event Hubs ingests streaming clickstream data, Azure Functions processes each event to update session information (e.g., by maintaining session state), and Azure Cosmos DB provides low-latency key-value storage for querying user sessions. Option A is wrong because Azure SQL Database is not optimized for low-latency key-value lookups at Cosmos DB's scale. Option B is wrong because Azure IoT Hub is designed for IoT devices, not web clickstream data, and Blob Storage is not suitable for real-time querying. Option C is wrong because Azure Stream Analytics is intended for analytical processing over time windows, not for per-event sessionization logic that requires custom code; Azure Functions is a better fit for that requirement.

Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure SQL Database

Answer

Azure IoT Hub -> Azure Data Factory -> Azure Blob Storage

Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure Cosmos DB

Question 8

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 9

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. Setting 'skipLineCount' to 1 causes the copy activity to skip the first line after the header; since the header is already parsed, the first data row is skipped. Option A is incorrect because 'writeBehavior' set to 'insert' does not skip rows; it simply inserts new rows and does not overwrite existing rows. Option C is incorrect because setting 'recursive' to true only affects whether files in subdirectories are included, not row skipping. Option D is incorrect because 'enableStaging' being false is acceptable for direct copy and does not cause row skipping.

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 10

Your company uses Azure Synapse Analytics to run a large-scale batch processing job every night. The job currently runs on a dedicated SQL pool and takes 4 hours. Management wants to reduce the runtime to under 2 hours without increasing cost. The job involves heavy compute operations with no data movement limitations. What should you do?

Accepted Answer

Increase the service level objective (DWU) of the dedicated SQL pool.. Increasing the service level objective (DWU) of the dedicated SQL pool provides more compute resources, directly reducing the runtime of compute-heavy batch jobs. Since the pool runs for fewer hours, the total cost (DWU × hours) may remain the same or even decrease, meeting the requirement to reduce runtime without increasing cost. Materialized views (A) and result-set caching (D) benefit repeated queries, not a unique nightly job. Workload management (C) only prioritizes resources, without increasing overall compute power.

Answer

Create materialized views on frequently queried tables.

Answer

Implement workload management to prioritize the job.

Answer

Enable result-set caching on the dedicated SQL pool.

Question 11

You are designing a batch processing solution in Azure Synapse Analytics using pipelines. The solution must load data from multiple sources (Azure Blob Storage, Azure SQL Database, and REST API) into a dedicated SQL pool. After loading, you need to run a stored procedure to aggregate the data. Which two activities should you include in the pipeline? (Choose two.)

Accepted Answer

Copy activity. Options B and D are correct. Copy Activity (B) is used to copy data from the various sources into the dedicated SQL pool. Stored Procedure Activity (D) is used to execute the aggregation stored procedure after data loading. Option A (Execute Pipeline Activity) is incorrect because it runs another pipeline, not relevant here. Option C (Azure Function Activity) is incorrect because it executes custom code, which is not needed. Option E (Data Flow Activity) is incorrect because while it can perform transformations, the requirement is to load and then run a stored procedure; Data Flow is not required for this simple load-and-aggregate scenario.

Answer

Execute Pipeline activity

Answer

Azure Function activity

Answer

Data Flow activity

Question 12

Refer to the exhibit. You have a Mapping Data Flow in Azure Data Factory that reads JSON files from a folder partitioned by year/month/day. The source setting includes a row limit of 10,000. The sink writes Parquet files with a file pattern and partition columns. You notice that the job processes only the first 10,000 rows from the entire dataset instead of 10,000 rows per partition. How should you modify the data flow to achieve row limit per partition?

Accepted Answer

Add a 'RowNumber' transformation after the source, then filter rows where row number <= 10000 per partition.. The row limit in source settings applies globally, not per partition. The correct approach is to use a Window transformation to assign a row number per partition (partitioned by the same columns as the folder structure, e.g., year, month) and then filter rows where the row number is <= 10000. Option B describes this method (though 'RowNumber' is not an actual transformation name; it refers to the Window transformation with a row number function). Option A is incorrect because there is no 'Top N' transformation in Azure Data Factory mapping data flows. Option C is incorrect because the row limit setting does not support dynamic expressions based on partition variables. Option D is incorrect because Distinct removes duplicates but does not limit row count.

Answer

Remove the rowLimit from source settings. In the Optimize tab of the source, set 'Partition option' to 'Set partitioning' with 'Value' as 'year,month'. Then add a 'Top N' transformation after the source to limit rows per partition.

Answer

In the source settings, change rowLimit to a dynamic expression that uses partition variables to limit per partition.

Answer

Use a 'Distinct' transformation with 'All columns' to remove duplicate rows, which inherently limits rows.

Question 13

You are designing a data pipeline in Azure Data Factory to load incremental changes from an on-premises SQL Server database to Azure Synapse Analytics. The source table has over 1 billion rows and a datetime column 'LastUpdated' that is indexed but not always increasing. The requirement is to capture all changes with minimal latency and no missed rows. Which approach should you recommend?

Accepted Answer

Use SQL Server change tracking with a watermark table.. SQL Server change tracking reliably captures all changes including deletes and out-of-order updates, and the watermark table mechanism ensures no rows are missed. Option A is incorrect because CDC can miss rows when timestamps are out of order. Option C is incorrect because it requires schema changes (triggers) and may impact source performance. Option D is incorrect because the 'LastUpdated' column is not always increasing, so queries based on 'last run time' can miss rows.

Answer

Enable Change Data Capture (CDC) on the source table.

Answer

Use a custom staging table with triggers to capture changes.

Answer

Use a watermark column (LastUpdated) and query rows where LastUpdated > last run time.

Question 14

Refer to the exhibit. A data engineer is reviewing a Mapping Data Flow in Azure Data Factory. The source reads 1000 rows from a CSV file. The sink writes to a Delta dataset with optimizeWrite enabled. The data flow currently has no transformations. The engineer runs the data flow and notices that the sink writes only 500 rows. What is the most likely cause?

Accepted Answer

The sink's allowSchemaDrift setting prevents writing rows with additional columns.. The sink's allowSchemaDrift setting, when set to false, prevents writing rows that have additional columns beyond the target schema. Since the source CSV may contain rows with extra columns (e.g., inconsistent formatting or varying column counts), those rows are silently dropped during the sink operation. This accounts for the reduction from 1000 rows read to 500 rows written.

Answer

The source's rowLimit setting limits the number of rows read, but the sink writes only unique rows.

Answer

The sink's partitionBy('hash', 1) setting causes some rows to be dropped due to partitioning logic.

Answer

The data flow has no transformations, so the sink cannot write data.

Question 15

A data engineering team is building a batch processing solution for a financial services company. Data is ingested daily from multiple sources into Azure Data Lake Storage Gen2 in CSV format. The data must be transformed (filtered, aggregated, joined) and loaded into Azure Synapse Analytics dedicated SQL pool. The team must optimize for cost and performance. The total data volume is 2 TB per day. The team has the following options:

Option A: Use Azure Data Factory pipelines with copy activity to load raw CSV files into Synapse staging tables, then use T-SQL stored procedures in Synapse to perform transformations.

Option B: Use Azure Databricks with Auto Loader to incrementally ingest CSV files, perform transformations in Spark, and write the results to Synapse using the Spark Synapse connector.

Option C: Use Azure Data Factory with mapping data flows to transform the data in a serverless environment and then write to Synapse.

Option D: Use Azure Synapse Pipelines (built on ADF) with a notebook activity that runs a PySpark notebook in Synapse Spark pool to transform and load data.

Which option should the team choose to minimize cost and management overhead while meeting performance requirements?

Accepted Answer

Option C. Which involves using Azure Data Factory mapping data flows, is correct. Mapping data flows run on a serverless ADF integration runtime, which scales automatically and incurs cost only per execution, minimizing management overhead. In contrast, Option A (use of Azure Data Factory pipelines with copy activity and T-SQL stored procedures) requires managing staging tables and stored procedures. Option C (use of Azure Databricks) requires managing Spark clusters. Option D (use of Azure Synapse Pipelines with notebook activity) requires managing Synapse Spark pools. Mapping data flows are cost-effective for batch transformations of this size.

Answer

Option B

Answer

Option A

Answer

Option D