Snowflake Connector for Azure Data Factory (ADF)

Author: Jeremiah Hansen

How to Use Snowflake, Snowflake Technology

Last year, I wrote two blog posts on building enterprise Azure data and analytics solutions around Snowflake. In Part 1, I wrote specifically about how to connect Azure Data Factory (ADF) to Snowflake, which has generated a lot of interest.

Since writing that blog post, I found a better way to connect ADF to Snowflake that I will share in this blog post. But more importantly, this time around I will also provide the code so you can begin using this connector immediately.

Below, I will introduce the new connector, but feel free to skip directly to the GitHub repository for the Snowflake ADF connector.

Connector Goals

This connector is an Azure Function that allows ADF to connect to Snowflake in a flexible way. It provides SQL-based stored-procedure-like functionality with dynamic parameters and return values. By using it with ADF, you can build a complete end-to-end data warehouse solution in Snowflake while following Microsoft and Azure best practices around portability and security.

The goals for the connector are:

  • To provide the ability to connect ADF to Snowflake in a flexible, performant, and cost-efficient manner
  • To provide SQL-based, stored-procedure-like functionality with Snowflake from ADF
  • To enable a low-friction migration once ADF supports native Snowflake connectivity and once Snowflake supports native SQL-based stored procedures

Connector Overview

Azure Functions have proven to be a better fit for this use case than the approach I outlined previously in Part 1, which leveraged Azure Batch via ADF’s Custom Activity. Here is an architectural overview of the connector:

High level architectural overview of the Snowflake Connector for Azure Data Factory (ADF).

Azure Functions have proven to be a better fit for this use case than the approach I outlined previously in Part 1, which leveraged Azure Batch via ADF’s Custom Activity. Here is an architectural overview of the connector:

The connector documentation contains a more detailed sequence diagram to explain the interaction between each component, but here is a description of the high-level process illustrated above:

  1. ADF looks up the Azure Function host key securely from Key Vault.
  2. ADF calls the Azure Function, passing the details about the stored procedure (database, schema, and name) as well as any parameters.
  3. The Azure Function looks up the Snowflake connection string and blob storage account connection string securely from Key Vault.
  4. The Azure Function reads the contents of the script from the Azure blob storage account following the naming convention: /<Database Name>/<Schema Name>/<Object Name>.sql.
  5. The Azure Function creates Snowflake variables for each parameter, executes each SQL query in the script, and returns any return values to ADF.

Here is a screenshot from ADF showing how the connector can be called two times in a row (here, against the same stored procedure) with values being passed between the two activities:

Azure Data Factory (ADF) pipeline showing the Snowflake Connector in action.

Getting Started

To get started with the Snowflake Connector for ADF, please visit the GitHub repository for the Snowflake ADF connector. The README.md file provides a detailed description of how the connector works as well as detailed deployment steps and legal notices. Please note that this connector is not an official Snowflake product and is licensed under the Apache license.

In addition to the code for the connector itself, I have created an advanced ARM template that allows you to deploy all the Azure components necessary to try out the connector, including the sample ADF Pipeline shown above. The steps for deploying the ARM template are included in the main connector deployment steps.

My hope is that this connector will enable you to build a complete end-to-end data warehouse solution in Snowflake with ADF now, before native ADF drivers and Snowflake SQL-based stored procedures are available.