Azure Data Factory: If Condition activity takes a long time to trigger inner activity

Question

Azure Data Factory: If Condition activity takes a long time to trigger inner activity

Brian Toan 45

Hi guys,

From yesterday (2024-08-20), the If Condition activity starts to take a long time to run. As you can see in the picture below, it is triggered at 6.00 AM, but doesn't trigger the inner activity until 6.05AM. The inner activities (Set Variable) also takes an unusual amount of time to run (over 1 minute)

User's image

This doesn't happen with all runs, but when this happens, it takes a very long time for the pipeline to run.

Can you help me on why this may happen?

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-08-20T18:03:03.4033333+00:00
Hello Brian Toan,

Welcome to the Microsoft Q&A forum.

Regarding the delay in the If Condition activity and the inner activities (Set Variable), there could be several reasons why this is happening. One possibility is that there has been an increase in the amount of data processed by the If Condition activity, which might be causing a delay.

To troubleshoot this issue, you can try the following steps:

Check the data sources used in the If Condition activity and the inner activities. Make sure that the data sources are available and that there are no issues with the data, such as SQL deadlocks or blocking.

Check if your Azure Data Factory's Integration Runtime is overloaded, especially during peak times. This could lead to delays in activity execution due to limited resources (CPU, memory) on the virtual machines backing the IR.

I hope this helps.

Brian Toan 45

Hi @Bhargava-MSFT ,

Thank you for your answer.

My If Condition activity is very simple. It doesn't use any data source at all. All it does is to check if an input parameter is true or not. In this case, it was False, so it need to set 2 variables. This usually runs in 5 seconds. Here is how it looks on ADF UI:

enter image description here

Here is the activity JSON:

{
                "name": "check_full_refresh",
                "type": "IfCondition",
                "dependsOn": [
                    {
                        "activity": "set_latest_state_cursor_value",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    },
                    {
                        "activity": "create_table_if_not_exist",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "expression": {
                        "value": "@pipeline().parameters.is_full_refresh",
                        "type": "Expression"
                    },
                    "ifFalseActivities": [
                        {
                            "name": "set_cursor_query_params",
                            "description": "",
                            "type": "SetVariable",
                            "dependsOn": [
                                {
                                    "activity": "set_state_cursor_value",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "cursor_query_params",
                                "value": {
                                    "value": "@if(\n    or(\n        empty(pipeline().parameters.cursor_value),\n        empty(pipeline().parameters.cursor_field)\n    ),\n    json('[]'),\n    createArray(\n        concat(\n            '$filter=',\n            pipeline().parameters.cursor_field,\n            ' ge datetime''',\n            variables('state_cursor_value'),\n            ''''\n        )\n    )\n)",
                                    "type": "Expression"
                                }
                            }
                        },
                        {
                            "name": "set_state_cursor_value",
                            "description": "Apply previous refresh range to the cursor value.",
                            "type": "SetVariable",
                            "dependsOn": [],
                            "policy": {
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "state_cursor_value",
                                "value": {
                                    "value": "@if(\n    or(\n        empty(pipeline().parameters.cursor_value),\n        empty(pipeline().parameters.cursor_field)\n    ),\n    null,\n    subtractFromTime(\n        pipeline().parameters.cursor_value,\n        pipeline().parameters.refresh_range_days,\n        'Day',\n        'yyyy-MM-dd'\n    )\n)",
                                    "type": "Expression"
                                }
                            }
                        }
                    ],
                    "ifTrueActivities": [
                        {
                            "name": "truncate_table",
                            "type": "Script",
                            "dependsOn": [],
                            "policy": {
                                "timeout": "0.00:10:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "linkedServiceName": {
                                "referenceName": "topdrill_snowflake",
                                "type": "LinkedServiceReference",
                                "parameters": {
                                    "database_name": {
                                        "value": "@variables('snowflake_database_name')",
                                        "type": "Expression"
                                    },
                                    "env_type": {
                                        "value": "@pipeline().globalParameters.env_type",
                                        "type": "Expression"
                                    }
                                }
                            },
                            "typeProperties": {
                                "scripts": [
                                    {
                                        "type": "NonQuery",
                                        "text": {
                                            "value": "TRUNCATE TABLE @{variables('snowflake_schema_name')}.@{variables('snowflake_table_name')}",
                                            "type": "Expression"
                                        }
                                    }
                                ],
                                "scriptBlockExecutionTimeout": "02:00:00"
                            }
                        }
                    ]
                }
            }

2.Thank you for this, it is probably this one. I'm using the default Auto Resolved Integration Runtime. Could you tell me how can I check if it is overloaded? Should I create a separate IR?

2 answers

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-08-20T18:03:03.4033333+00:00

Hello Brian Toan,

Welcome to the Microsoft Q&A forum.

Regarding the delay in the If Condition activity and the inner activities (Set Variable), there could be several reasons why this is happening. One possibility is that there has been an increase in the amount of data processed by the If Condition activity, which might be causing a delay.

To troubleshoot this issue, you can try the following steps:

Check the data sources used in the If Condition activity and the inner activities. Make sure that the data sources are available and that there are no issues with the data, such as SQL deadlocks or blocking.

Check if your Azure Data Factory's Integration Runtime is overloaded, especially during peak times. This could lead to delays in activity execution due to limited resources (CPU, memory) on the virtual machines backing the IR.

I hope this helps.

Answer 1

@Brian Toan

Based on the JSON, it seems that the If Condition activity is not the cause of the slowness.

The IfCondition activity depends on the successful completion of two previous activities: set_latest_state_cursor_value and create_table_if_not_exist.

If either of these activities takes longer than expected the start of the IfCondition will be delayed. Even though the IfCondition itself is simple, any delay in the preceding activities will impact its execution time.

and In the ifTrueActivities branch, there's a Script activity that runs a TRUNCATE TABLE command on a Snowflake database. If this branch is triggered, the script execution could introduce delays, especially if there's any transient connectivity issue or the Snowflake environment itself is under load.

Since you're using the AutoResolveIntegrationRuntime, this IR might be under heavy load at times, which could slow down the execution of both the IfCondition activity and the subsequent activities. This is true if multiple pipelines or activities are running concurrently on the same IR..

To troubleshoot the performance issue, you can use Azure Monitor to check the detailed performance metrics of the activities for any spikes in CPU, memory usage.

Additionally, you can check the Snowflake performance to ensure that it is not the bottleneck.

Answer 2

Brian Toan 45

After a few days, this delay is gone. I guess it was due to something from Azure itself, or maybe due to sudden high load from AutoResolvedIntegrationRuntime as Bhargava mentioned.Anyway, thank you @Bhargava-MSFT for your information.

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-08-26T20:22:07.2466667+00:00

Hi Brian Toan,

I'm glad to hear that the issue has been resolved. Yes, the delay could have been due to the load on the integration runtime. If you need a detailed root cause analysis, I recommend opening a support case. A support engineer can then review the backend logs and provide you with the root cause.

Thank you again for using the Microsoft Q&A forum.

Share via

Azure Data Factory: If Condition activity takes a long time to trigger inner activity

2 answers

Your answer