Execute Snowflake Stored Procedure with Azure Data Factory

1
votes

I have a linked service is up and running from which I was able to run simple queries.

My intention is to encapsulate the majority of the logic of the ETL in a stored procedure, scheduled and executed from Azure Data Factory "Lookup" component. However, it seems that I'm unable to execute it with the regular CALL test_snowflake_sp() that works so well from the workbench.

My stored procedure is comprised of a simple query followed by a "failure"/"success" message, I get the following error message:

Activity Get Value failed: Failure happened on 'Source' side. ErrorCode=UserErrorOdbcInvalidQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The following ODBC Query is not valid: 'CALL test_snowflake_sp()'.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,'

Unfortunately I don't have a Python access to run it in code.

Has anyone figured it out?

Thanks in advance

1
azureazure-data-factoryazure-data-factory-2snowflake-cloud-data-platform
Hi, If you are trying to create and run stored procedure then unfortunately snowflake doesnt allow stored procedures. I also ran in same situation then finally i have to write my query logic in python script. – Krishna
Snowflake Stored Procedures are now generally available docs.snowflake.net/manuals/sql-reference/stored-procedures.html - hope this gives you an extra option for your query logic @marc_s – Graham Mossman

1 Answers

1
votes

You can call Snowflake stored procs fine from a Lookup using exactly the syntax from your example. Be sure you grant the Data Factory user "usage" permissions on the proc, and re-grant any time you "create or replace" the proc ("grant usage on procedure test_snowflake_sp() to role datafactory" assuming you have created a role for the ADF user).