Troubleshooting practice questions

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?

Trap 1: Increase the Data Integration Units (DIU) for the copy activity

DIU applies to Azure IR, not self-hosted IR.

Trap 2: Change the copy method to 'Bulk insert'

Bulk insert is not available for on-premises sources.

Trap 3: Set the Fault Tolerance option to skip incompatible rows

This handles errors, not 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.

Question 2hardmultiple choice

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?

Trap 1: Parquet files require special permissions that are not granted by…

RBAC roles cover data operations for Parquet files.

Trap 2: The managed identity has not been granted the 'Storage Blob Data…

'Storage Blob Data Contributor' includes read permissions, so this is not the issue.

Trap 3: The notebook is using an incorrect connection string with account…

The notebook uses managed identity authentication, not account key.

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.

Question 3hardmultiple choice

You are troubleshooting a data processing job in Azure Synapse Pipelines that fails intermittently with the error: 'Operation on target Sink failed: The request was aborted: Could not create SSL/TLS secure channel.' The pipeline reads from Azure Blob Storage and writes to an Azure SQL Database. The source and sink are in the same region. What is the most likely cause?

Trap 1: Azure SQL Database firewall rules blocking the IP address of the…

Firewall blocks would give a different error message, like 'Cannot open server...'.

Trap 2: Transient network connectivity issues between the services.

Transient issues usually show timeout or connection reset errors, not SSL specific.

Trap 3: The Azure SQL Database DTU limit has been exceeded, causing…

Throttling returns HTTP 429 or specific Azure SQL error codes.

A
Azure SQL Database firewall rules blocking the IP address of the integration runtime.
Why wrong: Firewall blocks would give a different error message, like 'Cannot open server...'.
B
Transient network connectivity issues between the services.
Why wrong: Transient issues usually show timeout or connection reset errors, not SSL specific.
C
The Azure SQL Database DTU limit has been exceeded, causing throttling.
Why wrong: Throttling returns HTTP 429 or specific Azure SQL error codes.
D
The self-hosted integration runtime is using TLS 1.0, which is not supported by the services.
SSL/TLS handshake failure often stems from TLS version mismatch.

Question 4hardmultiple choice

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?

Trap 1: Staging is not enabled for the Copy activity.

Staging is optional and not required for basic copy operations; its absence would not cause a source-side error.

Trap 2: The destination table in Synapse has a different schema.

A schema mismatch would fail on the sink side, not the source side.

Trap 3: The SQL Server credentials in the linked service are incorrect.

Incorrect credentials would cause a login failure, but the error is generic 'SqlOperationFailed' and could be connectivity-related.

A
Staging is not enabled for the Copy activity.
Why wrong: Staging is optional and not required for basic copy operations; its absence would not cause a source-side error.
B
The destination table in Synapse has a different schema.
Why wrong: A schema mismatch would fail on the sink side, not the source side.
C
The SQL Server credentials in the linked service are incorrect.
Why wrong: Incorrect credentials would cause a login failure, but the error is generic 'SqlOperationFailed' and could be connectivity-related.
D
The self-hosted integration runtime is not configured properly.
A self-hosted integration runtime is required to connect from Azure to on-premises networks; misconfiguration is a common cause of source-side failures.

Question 5mediummultiple choice

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?

Trap 1: The SQL Server authentication credentials have expired.

Unlikely if the pipeline was working recently; network issue is more probable.

Trap 2: The Self-Hosted Integration Runtime is not installed.

It has been running for months, so it is installed.

Trap 3: The Azure Storage account firewall is blocking access.

The error is about the source connection, not destination.

A
The SQL Server authentication credentials have expired.
Why wrong: Unlikely if the pipeline was working recently; network issue is more probable.
B
The Self-Hosted Integration Runtime is not installed.
Why wrong: It has been running for months, so it is installed.
C
The on-premises network configuration has changed.
Network changes can block connectivity to the SQL Server.
D
The Azure Storage account firewall is blocking access.
Why wrong: The error is about the source connection, not destination.

Question 6mediummultiple choice

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?

Trap 1: The source and sink are in different Azure regions

They are in the same region.

Trap 2: The Data Integration Unit (DIU) setting is too low

DIU affects throughput but PolyBase is more impactful.

Trap 3: The source files are compressed

Compression can actually improve performance.

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.

Question 7hardmultiple choice

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?

Trap 1: Increase the Data Integration Units (DIU) in the Copy activity to…

Increasing DIU improves parallelism but the bottleneck is often the sink; PolyBase is more effective.

Trap 2: Increase the dedicated SQL pool's DWU setting to the highest tier.

Scaling up improves query performance but does not change the copy method; the load may still be slow.

Trap 3: Partition the staging table on a date column.

Partitioning helps with query performance and partition switching, not with the initial load speed.

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.

Question 8mediummultiple choice

You are troubleshooting a slow-running Azure Data Factory pipeline that copies data from an Azure SQL Database to ADLS Gen2. The pipeline uses a copy activity with the default settings. The source table has 10 million rows. Which optimization should you apply first?

Trap 1: Set the 'parallel copies' property to 10.

Parallel copies are automatically tuned; manual setting may not yield significant improvement.

Trap 2: Replace the copy activity with a mapping data flow.

Data flows are for transformations, not simple copies; they add overhead.

Trap 3: Increase the data integration unit (DIU) to maximum.

DIU increase costs more and may not address the bottleneck from SQL DB.

A
Set the 'parallel copies' property to 10.
Why wrong: Parallel copies are automatically tuned; manual setting may not yield significant improvement.
B
Replace the copy activity with a mapping data flow.
Why wrong: Data flows are for transformations, not simple copies; they add overhead.
C
Increase the data integration unit (DIU) to maximum.
Why wrong: DIU increase costs more and may not address the bottleneck from SQL DB.
D
Enable staged copy using an Azure Blob Storage staging location.
Staging allows data to be transferred via Blob Storage, which improves throughput for SQL to ADLS copies.

Question 9mediummultiple choice

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?

Trap 1: Change the distribution type to round-robin.

Round-robin distributes rows evenly but does not align with join keys, often increasing data movement.

Trap 2: Update statistics on all columns used in joins.

Statistics improve query plans but do not directly reduce data movement.

Trap 3: Increase the number of distributions to 120.

More distributions do not inherently reduce movement; alignment matters more.

A
Change the distribution type to round-robin.
Why wrong: Round-robin distributes rows evenly but does not align with join keys, often increasing data movement.
B
Update statistics on all columns used in joins.
Why wrong: Statistics improve query plans but do not directly reduce data movement.
C
Increase the number of distributions to 120.
Why wrong: More distributions do not inherently reduce movement; alignment matters more.
D
Redistribute the fact table on the join column using hash distribution.
Hash distribution on the join column keeps related rows together, reducing data shuffling.

Question 10easymultiple choice

You are troubleshooting an Azure Databricks job that writes data to Azure Data Lake Storage Gen2. The job fails with '403 Forbidden' error. The Databricks workspace uses a managed identity (system-assigned) for authentication. What should you verify?

Trap 1: The storage account name is correct

Would give 404, not 403

Trap 2: The storage account firewall is configured to allow Azure services

Would give network-level error, not 403

Trap 3: A private endpoint is configured between Databricks and the storage…

Not required for access

A
The storage account name is correct
Why wrong: Would give 404, not 403
B
The storage account firewall is configured to allow Azure services
Why wrong: Would give network-level error, not 403
C
A private endpoint is configured between Databricks and the storage account
Why wrong: Not required for access
D
The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account
RBAC role is required for write access

Question 11hardmulti select

Which THREE measures should you implement to monitor and optimize the performance of Azure Data Lake Storage Gen2?

Trap 1: Enable Network Security Group flow logs for the storage account…

NSG flow logs are for network traffic analysis, not storage performance.

Trap 2: Configure lifecycle management policies to move cold data to…

Lifecycle management optimizes cost, not performance monitoring.

A
Enable Network Security Group flow logs for the storage account subnet.
Why wrong: NSG flow logs are for network traffic analysis, not storage performance.
B
Enable Storage Insights to monitor capacity and transactions.
Storage Insights provides performance metrics.
C
Configure lifecycle management policies to move cold data to archive tier.
Why wrong: Lifecycle management optimizes cost, not performance monitoring.
D
Use Azure Storage Analytics logs to analyze latency and request rate.
Storage Analytics provides performance data.
E
Enable Azure Monitor diagnostic settings to capture read and write requests.
Diagnostic logs help analyze request patterns.