DP-203 Practice Test 18 — 15 Questions

Question 1

You are troubleshooting a Synapse Pipeline that runs a Copy activity from an on-premises SQL Server to Azure Synapse Dedicated SQL Pool. The pipeline fails with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed.' The on-premises SQL Server has no firewall restrictions. What is the most likely cause?

Accepted Answer

The self-hosted integration runtime is not configured properly.. The error indicates a failure on the source side. Since the on-premises SQL Server has no firewall restrictions, the most likely cause is that the self-hosted integration runtime is not installed or configured correctly, preventing connectivity from the cloud to the on-premises network. Option A is plausible but less likely given the error message. Option C is not a typical cause for a source-side error. Option D is about staging, which is not mentioned.

Answer

Staging is not enabled for the Copy activity.

Answer

The destination table in Synapse has a different schema.

Answer

The SQL Server credentials in the linked service are incorrect.

Question 2

You are designing a data processing pipeline in Azure Data Factory that uses a Mapping Data Flow. You need to handle errors gracefully, such as when a row fails to convert a column value. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure the sink transformation to allow errors and log error rows to a separate file.. Options A and D are correct. Option A: Using a conditional split to route error rows is a common pattern. Option D: Configuring the sink to allow errors and logging them ensures fault tolerance. Option B is wrong because try-catch is not available in Mapping Data Flows. Option C is wrong because aborting the activity is not graceful. Option E is wrong because schema drift does not handle conversion errors.

Answer

Wrap the data flow in a Try-Catch activity in the pipeline.

Answer

Set the data flow's error handling to 'Abort on error' to stop processing on first failure.

Answer

Enable schema drift on the source to automatically handle data type mismatches.

Question 3

You are developing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion into a dedicated SQL pool. You need to ensure that data from streaming sources is available for queries within 5 seconds. Which approach should you use?

Accepted Answer

Use Azure Stream Analytics with a custom SQL function that writes directly to the dedicated SQL pool. Using Azure Stream Analytics with a custom function to write to the dedicated SQL pool via a stored procedure can achieve low latency. Option A is wrong because Azure Data Factory is batch-only. Option B is wrong because Event Hubs Capture writes in batches (minutes). Option D is wrong because Spark Structured Streaming to Data Lake and then PolyBase introduces additional latency.

Answer

Use Azure Databricks with Structured Streaming, write to Data Lake Storage, and then use PolyBase to load into SQL pool

Answer

Use Azure Data Factory with tumbling window triggers to load data from Event Hubs every 5 seconds

Answer

Use Event Hubs Capture to write to Data Lake Storage, then use PolyBase to load into the SQL pool every 5 seconds

Question 4

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.. Option B (use skewed data optimization), Option D (broadcast hash join), and Option E (salting the key) are techniques to handle skew in data flows. Option A (increasing DIU) adds resources but doesn't address skew. Option C (repartition) can help but is not a direct skew mitigation; broadcast join is more effective.

Answer

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

Answer

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

Question 5

A financial services firm uses Azure Synapse Analytics to process daily trade data. The data is stored in a dedicated SQL pool as partitioned tables by date. Recently, queries that filter on a specific date range have become slow. You suspect that partition pruning is not working effectively. What should you do to improve query performance?

Accepted Answer

Rebuild the columnstore indexes on the table. Option D (Rebuild the columnstore indexes) is correct because fragmented columnstore indexes can cause poor partition pruning and overall slow query performance. Option A (Increase the number of partitions) may worsen performance due to partition overhead. Option B (Switch to rowstore) would degrade performance for large tables. Option C (Create statistics on the date column) helps but is not the primary issue here; the problem is likely index fragmentation.

Answer

Convert the table to a rowstore heap

Answer

Create statistics on the date column

Answer

Increase the number of partitions for the table

Question 6

Which TWO techniques can you use to handle schema drift in Azure Data Factory mapping data flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation. Options A and D are correct. 'Allow schema drift' allows flexible column mapping based on incoming schema. Column pattern matching can map dynamic columns to a target. Option B is wrong because using a fixed schema mapping is the opposite of handling drift. Option C is wrong because deriving columns is for static transformations. Option E is wrong because assertion rules are for validation, not adaptation.

Answer

Use derived column transformation to handle each new column manually

Answer

Use assertion rules to reject rows with unknown columns

Answer

Use a fixed schema mapping to ignore unknown columns

Question 7

You are troubleshooting a failed Azure Synapse Pipeline execution. The pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The error indicates a 'Connection timeout' to the on-premises source. The Integration Runtime is Self-Hosted and has been running successfully for months. What is the most likely cause?

Accepted Answer

The on-premises network configuration has changed.. Option B is correct because a change in the on-premises network configuration (e.g., firewall rules, DNS) could block the Integration Runtime from reaching the SQL Server. Option A is wrong because the Integration Runtime was running, so installation is not the issue. Option C is wrong because the storage account is the destination, not the source. Option D is wrong because the Integration Runtime was successful before, so credentials are likely valid.

Answer

The SQL Server authentication credentials have expired.

Answer

The Self-Hosted Integration Runtime is not installed.

Answer

The Azure Storage account firewall is blocking access.

Question 8

Refer to the exhibit. The pipeline executes a Spark job definition that reads from the input path and writes to the output path. The job fails with an error indicating that the output path already exists. The job is configured with default behavior. What configuration change should you make to allow the job to overwrite the existing output?

Accepted Answer

Add a Delete activity before the Spark job to delete the output folder if it exists.. Spark jobs by default fail if the output directory exists. To overwrite, you need to set the Spark configuration 'spark.hadoop.parquet.enable.summary-metadata' is not relevant. The correct approach is to set the Spark configuration 'spark.sql.sources.partitionOverwriteMode' to 'STATIC' or use 'overwrite' save mode in the code. However, in the exhibit, the job is a Spark job definition that calls a file; the simplest way is to set the argument to use overwrite mode. Option B (Delete activity) is a separate activity. Option C changes the job definition. Option D (output mode) is not a pipeline property.

Answer

Modify the Spark job definition to include 'overwrite' save mode.

Answer

Change the output path in the arguments to a non-existing folder.

Answer

Set the pipeline property 'outputMode' to 'overwrite' on the SynapseSparkJob activity.

Question 9

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Accepted Answer

Amazon S3. Option A is correct because Azure Blob Storage is a supported source. Option C is correct because Amazon S3 is a supported source via a connector. Option B is wrong because Power BI is not a data source for Copy activity. Option D is wrong because Azure Analysis Services is not a supported source. Option E is wrong because Azure DevOps is not a data store.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 10

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

Auto-mapping fails because the source and sink have different column counts.. Option C is correct because the source has 3 columns and the sink has 4 columns. With auto-mapping, the copy activity tries to map all source columns to sink columns by name but fails because the sink has an extra column (CreatedDate) that is not nullable and has no default value in the mapping context (the default is a SQL Server default constraint, but auto-mapping expects an explicit mapping or a NULL). The pre-copy script truncates the table, but that does not resolve the column count mismatch. Option A is wrong because the pre-copy script is valid. Option B is wrong because recursive is fine for a single file. Option D is wrong because writeBatchSize is acceptable.

Answer

The recursive setting should be false for CSV files.

Answer

The writeBatchSize is too large for the sink.

Answer

The pre-copy script is not allowed for SQL sink.

Question 11

Refer to the exhibit. You are reviewing an Azure Stream Analytics job query. The job has a stream input and a reference data input. The job is failing with the error 'Reference data input must be of type Reference, not Stream'. What is the cause of the error?

Accepted Answer

The output type is set to ReferenceData; it should be a different type.. Option B is correct because the output type is incorrectly set to ReferenceData; it should be set to a sink type like SQL or Blob. Option A is wrong because the alias is not the issue. Option C is wrong because the query syntax is correct. Option D is wrong because the input type is correct.

Answer

The input alias is incorrect.

Answer

The JOIN syntax is incorrect for reference data.

Answer

The input type is Stream; it should be Reference.

Question 12

Your team is developing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs. The output must be written to Azure SQL Database. You need to ensure that the processing guarantees exactly-once semantics for the output. Which output configuration should you use?

Accepted Answer

Set output mode to 'Exactly once'. Option C is correct because Azure Stream Analytics supports exactly-once delivery to Azure SQL Database when using the 'Exactly once' output mode. Option A is wrong because 'At least once' is the default and can cause duplicates. Option B is wrong because 'When possible' is not a valid output mode. Option D is wrong because 'Best effort' is not a valid mode.

Answer

Set output mode to 'When possible'

Answer

Set output mode to 'At least once'

Answer

Set output mode to 'Best effort'

Question 13

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

Enable result-set caching on the dedicated SQL pool.. Option C is correct because result-set caching stores query results in SSD storage, reducing compute time for repeated queries. Option A is wrong because increasing the service level (DWU) would increase cost. Option B is wrong because materialized views require storage but also incur compute for refresh. Option D is wrong because workload management separates resources but does not automatically reduce compute time without additional cost.

Answer

Create materialized views on frequently queried tables.

Answer

Increase the service level objective (DWU) of the dedicated SQL pool.

Answer

Implement workload management to prioritize the job.

Question 14

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

Accepted Answer

The serverless SQL pool infers schema from the first file and expects all files to have the same schema.. Serverless SQL pools infer the schema from the first file encountered. If files have different schemas, the inference may be inconsistent. Option A is correct because the schema inference is done on the first file, and mismatches cause errors. Option B is wrong because recursive reads subfolders but not different schemas. Option C is wrong because .parquet extension is fine. Option D is wrong because derivedModel is false, which means no model is used, but that doesn't cause schema mismatch.

Answer

The Parquet files are not using the .parquet extension.

Answer

The derivedModel option is set to false, which disables schema inference.

Answer

The recursive option is causing the table to include files from subfolders that have different schemas.

Question 15

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.. Options A, B, and E are correct. Option A: Increasing the number of partitions can improve parallelism. Option B: Using Delta Lake and optimizing the file layout reduces I/O. Option E: Caching intermediate results reduces repeated reads. Option C is wrong because reducing shuffle partitions can cause OOM errors. Option D is wrong because autoscaling may not help if the bottleneck is I/O.

Answer

Reduce the number of shuffle partitions to minimize data movement.

Answer

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