incremental load from Snowflake to ADLS using ADF

Question

I'm currently working on a project involving incremental data loading from Snowflake to Azure Data Lake Storage (ADLS) using Azure Data Factory (ADF). However, I’ve encountered some challenges that I could use your expertise on.

Approach 1:
I attempted to implement a pipeline with two lookup activities: one to retrieve the last modified time from a watermark table and another to get the table names that have been modified since that timestamp. While this seemed like a viable solution, I ran into issues when trying to update the watermark table. Unfortunately, Snowflake doesn’t support stored procedures for this purpose.

Approach 2:
For change data capture (CDC), I learned that only Snowflake Legacy is supported in our database, which complicates matters further.

I would greatly appreciate any insights or suggestions you may have on how to effectively perform incremental data loading in this context. If you have experience with Snowflake and ADF, your input would be invaluable.

Thank you in advance for your help!

Answer 1

In your Approach 1, you can use Script activity in ADF instead of a stored procedure to UPDATE your Water mark table.

After the first lookup activity, take a string set variable and store the current date @utcnow(). Then, go ahead with your activities involving copying source tables to target. After your activities, take the Script activity with your Snowflake linked service and select NonQuery.

The NonQuery option will be used to give the DML statements like UPDATE, INSERT and DELETE. Give your update query to update the water mark table using the string variable which was created earlier. Storing the current date in a variable prior to copy avoids missing records when the date time values are very close.

Give the query like below.

UPDATE watermark_table SET modified_date = @{variables('curr_date')}