ADF Lookup activity not returning correct datetime value

Hari 26

A pipeline uses a lookup activity and runs a script against Snowflake, returns first row of data and we are using it in a update statement.

Running the lookup to get MAX(load_date) from a snowflake table. In Snowflake it is "2024-04-25 17:26:01.548 -0700" but we are getting "2024-04-26 00:26:01.548" with lookup.

This started happening after we switched to new Snowflake linked service, with Snowflake legacy it was working fine.

Any idea why it is happening and how to resolve this.

AnnuKumari-MSFT 33,151 Reputation points Microsoft Employee

2024-04-26T06:02:17.4166667+00:00

Hi Hari ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I understand that after switching to new snowflake connector, lookup is not returning the correct datetime value from a Snowflake table.

There are lots of other observations as well when it comes to the difference between snowflake legacy and new connectors which is expected. However, here it seems the timestamp value coming from the table is showing up converted to UTC timezone by the connector.

Is there any difference in data of other datatypes which you observed, or only in datetime data?

I am taking it forward to the internal team and get back to you with their response once I receive. Thankyou
Hari 26 Reputation points

2024-04-26T12:56:31.3533333+00:00

Thanks, noticed another issue with null values. We used case statement to replace null vaule (CASE WHEN LEN(EH_COLUMNNAME) = 0 THEN ''XXX'' ELSE EH_COLUMNNAME END), it worked with legacy connection, but now it is not working.

Also is there any easy way to update all the DataSets and Pipelines that are using the legacy connection.

Thanks for the support.
AnnuKumari-MSFT 33,151 Reputation points Microsoft Employee

2024-05-07T11:22:19.3833333+00:00
@Hari ,

Is this related to the MAX(load) method or related with datetime type? Coud you share move details about how to reproduce this issue.

Could you please confirm that query is natively supported in snowflake side by executing the Query in snowflake portal? If you could reproduce this issue in new snowflake connector, could you please the sample query and reproduce steps?
Hari 26 Reputation points

2024-05-07T15:47:15.7966667+00:00

Here is the query, we are using ForEach activity to get the date.

select MAX(@{item().LOOKUPCOLUMN}) as LOAD_DTTM FROM @{item().TARGETSCHEMA}.@{item().TARGETTABLENAME}
Hari 26 Reputation points

2024-05-07T15:53:44.8866667+00:00

select MAX(LOAD_DTTM) as LOAD_DTTM FROM BIO_RET_DB.E_RET_CORE.IM_STATUS

In the screenshot you can see that the job ran at 4/25/2024 10:12 PM. And in the output it shows as "2024-04-26T00:26:01", but the actual max date was "2024-04-25 17:26:01.548 -0700" .
Hari 26 Reputation points

2024-05-21T15:07:55.75+00:00

@AnnuKumari-MSFT any update on this issue.

Thanks, Hari
Hari 26 Reputation points

2024-06-27T21:30:31.6466667+00:00

Any update on this issue?

Thanks, Hari

1 answer

Q&A Assist

2024-04-26T04:24:18.3133333+00:00

AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more
The issue you are experiencing with the Lookup activity in Azure Data Factory may be related to the differences between the new Snowflake connector and the Snowflake (legacy) connector. According to the documentation, the Snowflake connector supports BigDecimal in the Lookup activity, while the Snowflake (legacy) connector does not. It's possible that the new connector is returning the value as a BigDecimal, while the legacy connector returned it as a string. You can try converting the returned value to the desired format using a dynamic expression in the pipeline.

References:

Lookup activity properties

Differences between Snowflake and Snowflake (legacy)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

ADF Lookup activity not returning correct datetime value

1 answer

Your answer