Using Azure Function in Azure Data Factory to call Snowflake Stored Procedure

In a typical ETL process, data is extracted from various sources, moved to a stage area, transformed and loaded to Data warehouse/Datamart. Most data solutions consist of repeated data processing operations, encapsulated in workflows.

Azure Data Factory is a cloud-based ETL and data integration service that allows you to create data-driven workflows for orchestrating data movement and transforming data at scale. Using Azure Data Factory, you can create and schedule data-driven workflows (called pipelines) that can ingest data from disparate data stores. Azure Functions is a serverless compute service that enables you to run code on-demand without having to explicitly provision or manage infrastructure. Using Azure Functions, you can run a script or piece of code in response to a variety of events.

Snowflake is a data warehouse built on top of the Amazon Web Services or Microsoft Azure cloud infrastructure. The Snowflake architecture allows storage and compute to scale independently, so customers can use and pay for storage and computation separately.

In Azure Data Factory, there is no built in activity to directly call the Snowflake procedure. In this article, we are going talk about a solution that is going to use Azure Data Factory, Azure Functions and how to use them to call a stored procedure in Snowflake.

Prerequisites:

1. Create a Data factory : https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal
2. Create a key vault (For storing Snowflake Credentials) : https://docs.microsoft.com/en-us/azure/key-vault/general/quick-create-portal
3. Download and Install Visual Source Code — https://code.visualstudio.com/download
4. Download and run the Core Tools installer : https://go.microsoft.com/fwlink/?linkid=2135274
5. Snowflake FREE TRIAL Account : https://signup.snowflake.com/
6. How to Create a function in Azure with Python using Visual Studio : https://docs.microsoft.com/en-us/azure/azure-functions/create-first-function-vs-code-python
7. Must read about Azure Functions Python developer guide : https://docs.microsoft.com/en-us/azure/azure-functions/functions-reference-python?tabs=azurecli-linux%2Capplication-level
8. Must Read Use Key Vault references for App Service and Azure Functions :
   https://docs.microsoft.com/en-us/azure/app-service/app-service-key-vault-references

Now let’s jump into the action.

1. Login to Snowflake Account

All Snowflake accounts has access to shared database SNOWFLAKE_SAMPLE_DATA. For this exercise, we are going to create a stored procedure to get a count of records from one of the table in SNOWFLAKE_SAMPLE_DATA. This stored procedure takes table name as parameter and returns the count of records for the table.

2. Test the stored procedure

CALL DEMO_DB.PUBLIC.get_record_count(‘orders’)

3. Create Azure Function by the name ‘HttpTrigger’. This is the code for the function. Create Snowflake access parameters in local.settings.json file. Also, add ‘snowflake-connector-python’ to requirements.txt file

4. Test the function locally by passing “ORDERS” (or any table name in SNOWFLAKE_SAMPLE_DATA database) to the name parameter.

5. Function Output (Local)

6. Deploy the function to Azure account.

7. Add Snowflake account access parameters to the Key vault and also get secret Identifier for each one of these entries. Create Managed Identity for the Azure Function App and add this identity to Key Vault access policy. Also, copy secret identifier for each one of the access parameters.

8. Add configuration entries for the Function App. Make sure that the names in Key Vault match the names with the local.setting file.

9. Get the function URL for the Azure function and test it in web browser. Also, copy the Function Key for the function. We are going to be using this Function Key for creating the linked service in Azure Data Factory

10.Create Linked Service for Azure Function in Data Factory

11. Create a pipeline in Azure Data Factory and add Azure Function and Web task to the pipeline

URL : https://XXXXXXXXXXXXX.azurewebsites.net/api/HttpTrigger?code=xxxxxxxxxxxxxxx274WhqHG9eBAptz8fZGjkGRLsEWvEVbQ==

12. Run the data factory pipeline and check the task output for both Web Activity and Azure Function

If you would like us to evaluate and review your current data use-cases, please email us at info@datasurge.com or complete the form on our contact us page.