How many Troubleshooting Scenario Questions questions are on this page?

This page has 11 Troubleshooting Scenario Questions scenario questions for the DP-203 exam, each with detailed explanations and wrong-answer analysis.

How should I approach DP-203 scenario questions?

Read the full scenario before looking at the answer options. Identify the constraint or requirement in the scenario, then eliminate options that are generally true but wrong for this specific case. Scenario questions reward careful reading over pattern matching.

← Back to Microsoft Azure Data Engineer Associate DP-203 questions

Scenario-based practice

Troubleshooting Scenario Questions

Practise Microsoft Azure Data Engineer Associate DP-203 practice questions — original exam-style scenarios covering every exam domain, with detailed explanations, wrong-answer analysis, and common exam traps.

Start full practice test Read exam guide

scenario questions

DP-203

exam code

Microsoft

vendor

Scenario guide

How to approach troubleshooting scenario questions

These questions describe a network symptom and ask you to identify the root cause or the correct fix. They appear across all certification exams and reward systematic thinking over memorisation. The best candidates follow a consistent troubleshooting framework even under time pressure.

Quick answer

Troubleshooting Scenario Questions questions test whether you can apply the concept in context, not just recognise a definition.

How the topic appears in realistic exam-style scenarios.

Which detail in the question changes the correct answer.

How to eliminate plausible but wrong options.

How to connect the question back to the wider exam objective.

Practice scenarios

Question 1hardmultiple choice

Full question →

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

A
The dimension table is not actually replicated due to its size exceeding the replication threshold
If the dimension table is too large to be replicated, it will be distributed and cause shuffle.
B
The query is running with a low resource class
Why wrong: Resource class affects concurrency, not shuffle.
C
Result set caching is enabled
Why wrong: Caching would improve performance, not cause shuffle.
D
Statistics are outdated on the dimension table
Why wrong: Outdated statistics may cause suboptimal plans but not necessarily shuffle.

Full breakdown with real-world context →

Question 2hardmultiple choice

Full question →

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?

A
Rebuild the columnstore indexes on the table
Rebuilding reduces fragmentation and improves partition pruning.
B
Convert the table to a rowstore heap
Why wrong: Rowstore is slower for large analytical queries.
C
Create statistics on the date column
Why wrong: Statistics help but fragmentation is the likely root cause.
D
Increase the number of partitions for the table
Why wrong: More partitions can increase overhead and not fix fragmentation.

Full breakdown with real-world context →

Question 3hardmultiple choice

Full question →

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

A
Change the output format to Parquet to reduce file size.
Why wrong: Output format is unlikely to cause script execution failure.
B
Review the job logs in Azure Data Lake Analytics to identify the specific script error.
Job logs provide detailed error messages that pinpoint the issue.
C
Migrate the U-SQL script to Azure Synapse Spark pool.
Why wrong: This is a major change; troubleshooting should start with logs.
D
Increase the degree of parallelism for the U-SQL job.
Why wrong: This may help with performance but not with script errors.

Full breakdown with real-world context →

Question 4mediummultiple choice

Full question →

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Exhibit

Refer to the exhibit.

{
  "encryption": {
    "keySource": "Microsoft.Keyvault",
    "keyvaultproperties": {
      "keyvaulturi": "https://mykeyvault.vault.azure.net/",
      "keyname": "mykey",
      "keyversion": "1"
    }
  }
}

A
The key vault does not have soft-delete enabled.
Why wrong: Soft-delete is not required for encryption key access.
B
The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.
The storage account's identity must have these permissions to use the key.
C
The key name or version is incorrect.
Why wrong: The key name and version are specified.
D
The key vault URI is incorrectly formatted.
Why wrong: The URI appears correctly formatted.

Full breakdown with real-world context →

Question 5hardmultiple choice

Full question →

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

A
Parquet files require special permissions that are not granted by RBAC roles
Why wrong: RBAC roles cover data operations for Parquet files.
B
The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'
Why wrong: 'Storage Blob Data Contributor' includes read permissions, so this is not the issue.
C
The storage account firewall does not have a 'Resource instances' exception for the managed identity
Firewall rules require explicit addition of the managed identity as a resource instance to allow access when the firewall is enabled.
D
The notebook is using an incorrect connection string with account key
Why wrong: The notebook uses managed identity authentication, not account key.

Full breakdown with real-world context →

Question 6mediummultiple choice

Full question →

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

A
Change the distribution type to round-robin.
Why wrong: Changing to round-robin places rows randomly across distributions, which would increase data movement during joins because no collocation exists.
B
Update statistics on all columns used in joins.
Why wrong: Updating statistics improves query optimization but does not structurally reduce data movement; the distribution scheme remains unchanged.
C
Increase the number of distributions to 120.
Why wrong: Increasing the number of distributions does not align the distribution key with the join column, so data movement remains high.
D
Redistribute the fact table on the join column using hash distribution.
Redistributing the fact table on the join column using hash distribution ensures collocation of matching rows, directly reducing data movement.

Full breakdown with real-world context →

Question 7hardmultiple choice

Full question →

You are troubleshooting a slow-running pipeline in Azure Data Factory. The pipeline copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. The copy activity is using the 'Auto' copy method. You notice that network bandwidth is limited. Which configuration change would most likely improve performance?

A
Enable staging using Azure Blob Storage and use PolyBase to load into Synapse
Staging improves performance by using parallel uploads to Blob Storage.
B
Increase the Data Integration Units (DIU) for the copy activity
Why wrong: DIU applies to Azure IR, not self-hosted IR.
C
Change the copy method to 'Bulk insert'
Why wrong: Bulk insert is not available for on-premises sources.
D
Set the Fault Tolerance option to skip incompatible rows
Why wrong: This handles errors, not performance.

Full breakdown with real-world context →

Question 8hardmultiple choice

Full question →

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

Exhibit

Azure Stream Analytics job diagnostics log:

{
  "time": "2023-08-01T12:00:00Z",
  "properties": {
    "jobId": "job-123",
    "jobName": "IoTStreamJob",
    "events": [
      {
        "time": "2023-08-01T11:59:00Z",
        "type": "WatermarkDelay",
        "properties": {
          "watermarkDelaySeconds": 120,
          "maxWatermarkDelaySeconds": 300
        }
      },
      {
        "time": "2023-08-01T11:59:30Z",
        "type": "InputDeserializationError",
        "properties": {
          "source": "iothub",
          "count": 15
        }
      }
    ],
    "jobOutputWatermark": "2023-08-01T11:57:00Z"
  }
}

A
Check the input data schema and ensure it matches the query
Deserialization errors are often due to schema mismatch; fixing the data or query resolves the root cause.
B
Change the output to a different sink
Why wrong: Output sink is unrelated to input deserialization errors.
C
Increase the number of Streaming Units (SUs)
Why wrong: Scaling may help throughput but does not fix deserialization issues.
D
Set the watermark delay threshold higher
Why wrong: That only masks the symptom; the underlying issue remains.

Full breakdown with real-world context →

Question 9hardmultiple choice

Full question →

You are troubleshooting a slow-running Azure Synapse Pipeline that loads data from Azure Blob Storage into a dedicated SQL pool using a Copy activity. The source is a set of CSV files totaling 500 GB. The sink is a staging table with a clustered columnstore index. The pipeline takes 4 hours to complete. You need to reduce the execution time to under 1 hour. What should you do?

A
Enable PolyBase in the Copy activity sink settings.
PolyBase provides the fastest way to load data into dedicated SQL pool by leveraging its parallel architecture.
B
Increase the Data Integration Units (DIU) in the Copy activity to the maximum.
Why wrong: Increasing DIU improves parallelism but the bottleneck is often the sink; PolyBase is more effective.
C
Increase the dedicated SQL pool's DWU setting to the highest tier.
Why wrong: Scaling up improves query performance but does not change the copy method; the load may still be slow.
D
Partition the staging table on a date column.
Why wrong: Partitioning helps with query performance and partition switching, not with the initial load speed.

Full breakdown with real-world context →

Question 10mediummultiple choice

Full question →

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

A
The copy activity is not using staging and PolyBase
PolyBase dramatically improves load performance.
B
The source and sink are in different Azure regions
Why wrong: They are in the same region.
C
The Data Integration Unit (DIU) setting is too low
Why wrong: DIU affects throughput but PolyBase is more impactful.
D
The source files are compressed
Why wrong: Compression can actually improve performance.

Full breakdown with real-world context →

Question 11hardmultiple choice

Full question →

You are troubleshooting slow COPY INTO performance in Azure Synapse Analytics dedicated SQL pool when loading Parquet files from Azure Data Lake Storage Gen2. The files are 1 GB each. What should you do to improve performance?

A
Reduce the file size to 100 MB to increase parallelism
Why wrong: Reducing file size to 100 MB does not directly address the parallelism issue; the problem is too few files to match the 60 distributions, not the file size.
B
Use PolyBase instead of COPY INTO
Why wrong: PolyBase is an older technology; COPY INTO is the recommended and more performant approach for loading Parquet files in Synapse dedicated SQL pool.
C
Increase the number of files to match the number of distributions
Correct. With 1 GB files, there are too few files to fully utilize all 60 distributions. Increasing the number of files to at least 60 ensures each distribution receives data, maximizing parallelism and improving performance.
D
Disable parallel processing in the COPY command
Why wrong: Disabling parallel processing would reduce performance by forcing sequential execution, which is counterproductive for large data loads.

Full breakdown with real-world context →

These DP-203 practice questions are part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style DP-203 questions with detailed explanations, topic-based practice, mock exams, readiness tracking, and study analytics.

Troubleshooting Scenario Questions

How to approach troubleshooting scenario questions

Quick answer

Related DP-203 topic practice pages

Secure, monitor, and optimize data storage and data processing practice questions

Design and implement data storage practice questions

Develop data processing practice questions

DP-203 fundamentals practice questions

DP-203 scenario practice questions

DP-203 troubleshooting practice questions

Practice scenarios

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Exhibit

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

Exhibit

You are troubleshooting slow COPY INTO performance in Azure Synapse Analytics dedicated SQL pool when loading Parquet files from Azure Data Lake Storage Gen2. The files are 1 GB each. What should you do to improve performance?