ADF Lookup Activity not returning values from Snowflake

Question

Hi there,

We have a pipeline that uses a lookup activity to run a script against Snowflake, return the first row of data and assign that returned data into a variable.

The lookup code is:

{
    "name": "Last Run",
    "type": "Lookup",
    "dependsOn": [
        {
            "activity": "LastModifiedVariable",
            "dependencyConditions": [
                "Succeeded"
            ]
        }
    ],
    "policy": {
        "timeout": "0.12:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "source": {
            "type": "SnowflakeV2Source",
            "query": "select max(PROCESSINGCOMPLETE) as lastdate from schema.loadcontrol where status = 'Completed' and filedirection = 'Outbound'",
            "exportSettings": {
                "type": "SnowflakeExportCopyCommand"
            }
        },
        "dataset": {
            "referenceName": "DID",
            "type": "DatasetReference"
        }
    }
}

Its set to return 'First row only' but as its choosing a max in the sql query it will only ever return one value anyway. I want to then load this value into a variable, which I do in the next step:

{
    "name": "LastRun",
    "type": "SetVariable",
    "dependsOn": [
        {
            "activity": "Last Run",
            "dependencyConditions": [
                "Succeeded"
            ]d
        }
    ],
    "policy": {
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "variableName": "LastRun",
        "value": {
            "value": "@formatDateTime(activity('Last Run').output.firstRow.lastdate, 'yyyy-MM-dd HH:mm:ss')",
            "type": "Expression"
        }
    }
}

The problem I am getting is, even though the query is being passed to snowflake and running fine (as confirmed by the Query History on snowflake itself) its returning only row and its a datetime, all expected. The issue is that the process will fail with the error message:

Operation on target LastRun failed: The expression 'formatDateTime(activity('Last Run').output.firstRow.lastdate, 'yyyy-MM-dd HH:mm:ss')' cannot be evaluated because property 'firstRow' doesn't exist, available properties are 'effectiveIntegrationRuntime, durationInQueue'.

But it wont fail all the time, only sometimes. It fails on the assign to a variable step, and if I look at the lookup activity before that runs the code, the output looks liek:

{ "effectiveIntegrationRuntime": "adf-MIR (UK South)", "durationInQueue": { "integrationRuntimeQueue": 1 } }

Whereas on times when its run successfully it looks like:

{ "firstRow": { "LASTDATE": "2024-04-10T14:44:14.355Z" }, "effectiveIntegrationRuntime": "adf-MIR (UK South)", "durationInQueue": { "integrationRuntimeQueue": 4 } }

You can see when it runs successfully it is returning the FirstRow value, but theres nothing to indicate why it doesnt manage to return it sometimes.

Does anyone have any ideas why it might not be returning a value from a query against snowflake when snowflake query history confirms it ran successfully?

Answer 1

You need to check the First row only check box in the lookup activity settings.

However, this could potentially happen if the Lookup activity does not return any rows or if there is a delay or issue in the response from Snowflake, causing the activity output to be different than expected.

Try to set a default value when it is NULL :

{  "value": "@if(equals(activity('Last Run').output.firstRow, null), 'DefaultValue', formatDateTime(activity('Last Run').output.firstRow.lastdate, 'yyyy-MM-dd HH:mm:ss'))",
    "type": "Expression"
}

Answer 2

Were you able to resolve this? We are also experiencing exactly the same issue with lookup activity.