DP-203 Practice Test 24 — 15 Questions

Question 1

You are developing a data pipeline in Azure Data Factory that ingests data from multiple on-premises SQL Server databases to Azure Data Lake Storage Gen2. The data volume is about 1 TB per day. You need to ensure the pipeline can handle the volume and provide monitoring and alerting. Which THREE components should you include?

Accepted Answer

Self-hosted integration runtime. Correct answers: A, C, and E. Self-hosted integration runtime is required for on-premises connectivity. Azure Monitor provides monitoring and alerting. Data flow can be used for data transformation at scale. Option B is wrong because Azure Synapse Analytics is not used in this pipeline. Option D is wrong because Power BI is for visualization, not data ingestion.

Answer

Azure Synapse Analytics pipeline

Answer

Power BI

Question 2

Your team uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is inconsistent, and some queries take a long time to execute. You need to improve query performance. What should you do?

Accepted Answer

Create statistics on the columns used in joins and filters. Option B (Create statistics on the columns used in joins and filters) is correct because serverless SQL pool relies on statistics for optimal query plans. Option A (Increase the maximum degree of parallelism) is not directly applicable. Option C (Convert to CSV) would degrade performance. Option D (Use a dedicated SQL pool) may be an option but not the best immediate step.

Answer

Increase the MAXDOP setting in the query

Answer

Move the data to a dedicated SQL pool

Answer

Convert the Parquet files to CSV format

Question 3

Your team is developing a data processing solution that uses Azure Databricks to transform streaming data from Azure Event Hubs. The transformation includes joining the stream with a static reference table stored in Azure Data Lake Storage Gen2. You need to implement the join efficiently. Which approach should you use?

Accepted Answer

Use a broadcast join with the static DataFrame loaded from Delta Lake. Option A is correct because streaming-static joins in Spark Structured Streaming are optimized when the static data is broadcast to all nodes, avoiding shuffles. Option B is wrong because the reference table is static; streaming-static join is appropriate. Option C is wrong because joining two streams is not needed. Option D is wrong because foreachBatch with a batch join adds complexity.

Answer

Use a watermark on both sides and perform a stream-stream join

Answer

Use foreachBatch to micro-batch the stream and perform a batch join

Answer

Use a stream-stream join by converting the static table to a stream

Question 4

You are building a data processing solution using Azure Databricks. The solution must process streaming data from Azure Event Hubs, join it with a static reference table stored in Azure Data Lake Storage Gen2 (Parquet format), and write the output to Azure Synapse Analytics. The reference table is updated daily. Which approach minimizes latency and ensures data consistency?

Accepted Answer

Use Spark Structured Streaming with a streaming join and cache the reference table as a static DataFrame.. Option D is correct because using Spark Structured Streaming with a streaming join and a static DataFrame for the reference table minimizes latency and ensures consistency by reading the reference table once. Option A is wrong because batch processing every hour adds latency. Option B is wrong because loading the reference table per batch increases overhead. Option C is wrong because foreachBatch is useful for batch writes but does not improve join performance.

Answer

Use Spark Structured Streaming with foreachBatch to write to Synapse.

Answer

Use Spark Structured Streaming with a streaming join and load the reference table in each micro-batch.

Answer

Use a batch job that runs every hour to process the data.

Question 5

Refer to the exhibit. You have an Azure Data Factory pipeline that copies trade data from Azure Blob Storage to Azure SQL Database. The pipeline runs every hour and truncates the destination table before each copy. However, users report that data is missing during the copy window. What is the most likely cause?

Accepted Answer

The preCopyScript truncates the table before the copy completes, causing a period with no data. Option B is correct. The preCopyScript truncates the table before copying, so if the copy fails or takes time, the table is empty. Option A is wrong because writeBatchSize does not cause data loss. Option C is wrong because recursive does not affect data loss. Option D is wrong because outputs and inputs are correct.

Answer

The output dataset is not correctly configured

Answer

The writeBatchSize is too low, causing timeouts

Answer

The source dataset is set to recursive, which includes unwanted files

Question 6

Refer to the exhibit. You have a mapping data flow in Azure Data Factory that aggregates sales data. The data flow runs successfully but the sink table contains only the total sum per run instead of per product. What is missing?

Accepted Answer

The aggregate transformation does not have a groupBy column. Option C is correct. The aggregate transformation does not have a groupBy clause, so it sums all rows. Adding a groupBy on ProductID will produce per-product totals. Option A is wrong because the source is correct. Option B is wrong because the sink is fine. Option D is wrong because there is no filter.

Answer

The source dataset is not filtering by date

Answer

The data flow is missing a filter transformation

Answer

The sink dataset is not configured to append

Question 7

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. Option B is correct because 'skipLineCount': 1 skips the first line after the header line; since header is already parsed, the first data row is skipped. Option A is wrong because 'recursive': true only affects subdirectories, not line skipping. Option C is wrong because 'writeBehavior': 'insert' is default and does not skip rows. Option D is wrong because 'enableStaging': false is fine for direct copy.

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 8

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slower than expected. The files are large (500 MB each) and not partitioned. You need to improve query performance without moving data. What should you do?

Accepted Answer

Create external tables with explicit file format and partition elimination hints.. Creating external tables with file format options allows the serverless SQL pool to use metadata for optimization. Changing the file format to CSV or using OPENROWSET without external tables does not improve performance as much. Converting to Delta format requires data movement. Partitioning the files would be best, but that requires reorganizing files.

Answer

Convert the Parquet files to Delta format using Azure Databricks.

Answer

Change the files to CSV format with a header row.

Answer

Use OPENROWSET with BULK and specify ROWSET_OPTIONS for better performance.

Question 9

You are tuning a Spark job in Azure Synapse Analytics that processes large Parquet files. The job currently takes too long due to data skew. Which three actions can improve performance? (Choose three.)

Accepted Answer

Use bucketing on the join key when writing intermediate data.. Options A, B, and E are correct. Repartitioning redistributes data. Salting adds a random key to break skew. Bucketing pre-partitions data. Option C is wrong because coalesce reduces partitions but does not address skew. Option D is wrong because increasing executor memory may help but does not directly solve skew.

Answer

Use coalesce to reduce the number of partitions.

Answer

Increase executor memory to handle larger partitions.

Question 10

Your company uses Azure Synapse Analytics to run a data warehouse. You have a dedicated SQL pool with a hash-distributed fact table named Sales. The distribution column is ProductID. You notice that queries against the Sales table are slow due to data skew. After analysis, you find that a few products (e.g., ProductID 100, 200) account for 80% of the rows. You need to optimize query performance without redesigning the entire table. You also need to minimize data movement during queries. Which action should you take?

Accepted Answer

Change the distribution to round-robin.. Option B is correct. Round-robin distribution distributes data evenly across distributions, eliminating skew. However, it may increase data movement for joins. Given the severe skew, round-robin is a reasonable trade-off. Option A is wrong because adding more distributions does not fix skew. Option C is wrong because changing to replicate distribution is not suitable for large fact tables. Option D is wrong because creating non-clustered indexes does not address distribution skew.

Answer

Increase the number of distributions to 120.

Answer

Change the distribution to replicate for the Sales table.

Answer

Create non-clustered indexes on the ProductID column.

Question 11

You are running a batch processing job using Azure Data Factory. The job reads from Azure Blob Storage, transforms data with a Data Flow, and writes to Azure Synapse Analytics. The job fails intermittently with the error: 'Operation on target WriteToSynapse failed: Cannot bulk load because the file could not be opened.' You need to resolve the issue with minimal downtime. What should you do?

Accepted Answer

Enable staging blob file deletion in the copy activity settings.. The error indicates a transient lock on the staging blob file. Enabling staging blob file deletion in the copy activity settings allows the pipeline to clean up and retry. Increasing the degree of copy parallelism does not address file locking. Using PolyBase with external tables requires schema changes. Switching to staging via SQL authentication does not solve the file lock issue.

Answer

Increase the degree of copy parallelism in the Data Flow.

Answer

Use PolyBase to load data directly from Blob Storage without staging.

Answer

Switch to staging via SQL authentication instead of Managed Identity.

Question 12

A company is ingesting streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time and stored in Azure Synapse Analytics for reporting. The solution must handle late-arriving data and ensure exactly-once semantics. Which Azure service should you use for stream processing?

Accepted Answer

Azure Stream Analytics. Option D is correct because Azure Stream Analytics supports exactly-once semantics to Synapse, handles late arrivals, and is optimized for real-time. Option A is incorrect because Spark Structured Streaming in Synapse does not provide exactly-once to Synapse out of the box. Option B is incorrect because Event Hubs itself does not process data. Option C is incorrect because Data Factory is for batch, not streaming.

Answer

Azure Data Factory with Event Hubs source

Answer

Azure Synapse Spark Structured Streaming

Answer

Azure Event Hubs with Capture

Question 13

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 is used to copy data from sources. Stored procedure activity is used to execute the aggregation procedure. Option A is wrong because Data Flow is for transformations, but the requirement is to load and then run a stored procedure. Option C is wrong because Execute Pipeline is for running another pipeline, not for executing a stored procedure. Option E is wrong because Azure Function activity is for custom code, not needed here.

Answer

Execute Pipeline activity

Answer

Azure Function activity

Answer

Data Flow activity

Question 14

You are designing a data processing solution in Azure Data Factory that uses mapping data flows. You need to perform type conversions on incoming data. Which three transformations can be used to change data types? (Choose three.)

Accepted Answer

Derived Column. Options A, B, and C are correct. Derived Column can change types via expressions. Select can cast columns during projection. Assert can check and convert types. Option D is wrong because Sort only orders data. Option E is wrong because Conditional Split routes rows based on conditions, not type conversion.

Answer

Conditional Split

Answer

Sort

Question 15

Which TWO actions should you take to optimize a Spark job in Azure Synapse Analytics that is experiencing excessive shuffling and long execution times?

Accepted Answer

Broadcast one of the tables if it is small enough to fit in memory.. Option A is correct because broadcasting a small lookup table can avoid shuffles in joins. Option C is correct because increasing the number of partitions can spread data more evenly, reducing shuffle size. Option B is wrong because coalescing reduces partitions, which may worsen shuffling. Option D is wrong because disabling dynamic allocation may lead to resource contention. Option E is wrong because enabling checkpointing adds overhead and does not directly reduce shuffling.

Answer

Coalesce the number of partitions to reduce overhead.

Answer

Enable checkpointing to persist intermediate results.

Answer

Disable dynamic resource allocation.