Azure Data Factory Monitoring and Observability
Monitoring and diagnosing data pipelines in Azure Data Factory (ADF) can be challenging as data workflows grow in complexity. Effective monitoring and observability are crucial to ensure smooth operation. In this post, we’ll explore how User Properties and Kusto Query Language (KQL) can enhance diagnostics, allowing you to gain deeper insights, identify issues early, and troubleshoot with precision.
Understanding User Properties and annotations in Azure Data Factory
Annotations in ADF are static tags that help you organise and group objects for clarity. In contrast, User Properties are dynamic and can be updated during pipeline execution. For those familiar with BizTalk, think of User Properties as an evolved form of tracked properties in ADF.
By assigning User Properties to specific pipeline activities, you can capture more granular data, which aids debugging and monitoring. These properties are also tightly integrated with the Azure ecosystem, working seamlessly with log analytics workspace and other diagnostic tools.
When combined with logging features, they provide detailed insights into pipeline activities. This data becomes accessible for querying in Azure Monitor using KQL (Kusto Query Language) or other tools, helping teams with better activity monitoring and troubleshooting.
Enabling Diagnostics and Logging in ADF
To start leveraging User Properties for diagnostics, ensure that Diagnostic Settings are enabled in ADF. This allows logs to flow into Azure Log Analytics Workspace, where KQL can be used for advanced diagnostics.
Example: If you need to track an Order ID or User ID through different pipeline activities, you can configure User Properties to capture these values. This helps in tracing specific transactions, making troubleshooting more effective.
Useful KQL Queries for Diagnosing Data Pipelines
Once ADF diagnostics are set up, use KQL to query key logs. The most important log tables for monitoring include:
- ADFTriggerRun: Logs trigger activities
- ADFPipelineRun: Logs pipeline runs
- ADFActivityRun: Logs detailed activity steps
Using KQL (Kusto Query Language) for diagnostics in Azure Data Factory (ADF) provides deep insights into the inner workings of your data pipelines. You can monitor triggers, pipelines, and activities, while also accessing detailed information like tags, status, user properties, and annotations. This helps you track the entire workflow, including predecessors and triggers.
Want to improve visibility and monitor your Azure Data Factory pipelines with KQL? Below are some essential KQL queries for Azure Data Factory that will boost your diagnostic capabilities and provide better control over your data processes.
Reporting for multiple ADF instances
When working with multiple Azure Data Factory instances, pipelines across different instances can share the same name. This could lead to confusion and mix up the statistics, especially when generating reports.
Also, when troubleshooting, it’s important to know which Data Factory instance a particular pipeline belongs to.
A useful approach is to include the ResourceId to further differentiate pipelines. For example, you can concatenate the Data Factory name with the pipeline name to ensure each pipeline is unique and easily identifiable. Here’s a snippet to achieve this:
split(ResourceId, "/")[-1],'/',PipelineName)Identify Failed ADF Pipelines & Activities
Description: This KQL query retrieves all failed Azure data factory pipeline runes. It focuses on completed runs with errors to give you a quick overview of workflow failures.
Insights: Use this query to check your Azure data factory for general health. It helps you identify failed runs that may require manual investigation and troubleshooting, ensuring your workflows run smoothly and efficiently.
ADFPipelineRun
| where Status == "Failed"
| order by TimeGenerated
| join kind=inner (
ADFActivityRun
| where isnotempty(parse_json(Error).message)
) on $left.RunId == $right.CorrelationId
| project TimeGenerated, Pipeline=strcat(split(ResourceId, "/")[-1],'/',PipelineName), RunId, OperationName1, ErrorCode1, ErrorMessage1
Track ADF pipeline runs by Specific User Property
Description: This KQL query lists all completed Pipeline runs that contain a specific tracked property, such as an OrderId. This query works across different pipelines, making it easier to identify and monitor specific processes.
Insights: Use this query to verify workflow executions and trace back executions using business keys like OrderId. This helps in understanding the flow and status of each workflow.
ADFActivityRun
| project TimeGenerated,PipelineName,CorrelationId, OrderId=parse_json(UserProperties).orderId
| where OrderId <> ""
| order by TimeGenerated as
Analysing ADF pipeline execution totals
Description: This KQL query helps you visualize the total executions for all pipelines, presenting the data in an easy-to-understand pie chart.
Insights: By using this query, you can quickly identify which workflows are executed the most, allowing you to optimize resources and improve efficiency.
ADFPipelineRun
| where Status in("Failed","Succeeded")
| summarize PipelineNameCount=count() by PipelineName
| sort by PipelineNameCount
| render piechart with(title="Total Executions ADF pipelines")
Analyse Total Executions per Workflow Over the Last 7 Days
Description: This KQL query provides a time chart visualization of completed azure data factory pipeline executions for all workflows over the past week. By analyzing these metrics, you can identify patterns and understand peak load times for your system or specific workflows.
Insights: Use this data to pinpoint peak usage periods and optimize performance. This information is important for identifying bottlenecks & planning scaling strategies for your azure data factory.
ADFActivityRun
| where Status in ("Completed","Failed")
| summarize Count=count() by PipelineName, bin(TimeGenerated, 1h)
| where TimeGenerated >= ago(7d)
| render columnchart with(title="Total Executions per Pipeline Over the Last 7 Days",kind=unstacked)
Execution Time Analysis: 95th Percentile
Description: The 95th percentile execution time shows the upper bound of workflow completion times. This metric indicates that 95% of workflows are finished within this time or less.
Insights: By analyzing the 95th percentile, you can identify pipelines that take longer than expected. This helps pinpoint areas for performance improvement in your pipelines.
ADFPipelineRun
| where Status in ("Completed","Failed")
| project PipelineName, Start, End, Duration = End - Start
| join kind=inner (
ADFPipelineRun
| where Status in ("Completed","Failed")
| project PipelineName, Duration = End - Start
| summarize P95_Duration = percentiles(Duration, 95) by PipelineName
) on PipelineName
| where Duration > P95_Duration
| project PipelineName, Start, End, Duration, P95_ExpectedExecutionTime = P95_Duration
| summarize by PipelineName, P95_ExpectedExecutionTime
| order by PipelineName, P95_ExpectedExecutionTime descNote: pipelines without sufficient data points or not enough variety to extract 95% percentile data won’t be shown in this query.
Identify the slowest 5%
Description: This KQL query identifies the slowest 5% of executions for each workflow. It helps to understand which workflows are underperforming.
Insights: Analyzing the slowest 5% of workflow executions provides insights into performance bottlenecks. This helps you address inefficiencies in your ADF pipelines.
ADFPipelineRun
| where Status in ("Completed","Failed")
| project PipelineName, Start, End, Duration = End - Start
| join kind=inner (
ADFPipelineRun
| where Status in ("Completed","Failed")
| project PipelineName, Duration = End - Start
| summarize P5_Duration = percentiles(Duration, 95),P95_Duration = percentiles(Duration, 5) by PipelineName
) on PipelineName
| where Duration > P5_Duration
| project PipelineName, Start, End, Duration, P95_ExpectedExecutionTime = P95_Duration
| summarize Count=count() by PipelineName
| order by PipelineNameActivity runs – Top 5 latest failures
Description: Returns top five activities failing with system errors.
Insights: By analyzing the highest failures, you can identify activities that cause the biggest amount of exceptions in your system. This helps to pinpoint areas of optimizations, such as error handling, or external dependencies.
ADFActivityRun
| where TimeGenerated >= ago(7d)
| where Status != 'InProgress' and Status != 'Queued' and Status != 'Succeeded'
| summarize failureCount = countif(Status != 'Succeeded') by bin(TimeGenerated, 1h), ActivityName
| top 5 by failureCount desc nulls last
| order by TimeGenerated asc