Data Migration to Snowflake with Azure Data Factory

This is the second part in a 2 part series on how the cloud data integration service, Azure Data Factory, can be used to load data from on-premises databases into Snowflake, a cloud data warehouse. In this article, we’ll take you through a step by step guide of how Smartbridge does just that.

Azure Data Factory (ADF) can be used to migrate data directly from an on-premises database into a Snowflake database. This article explains the process used by Smartbridge in setting up our internal demonstration instance.

Data Migration to Snowflake with Azure
Picture Courtesy of Snowflake

1. Add Azure Integration Runtime on Azure

Create an Azure Data Factory. The data factory name should be unique.

Data Migration to Snowflake with Azure

Once created, click on “Author & Monitor”.

Data Migration to Snowflake with Azure

In the data factory, add a new Integration Runtime on the Connections tab.

Data Migration to Snowflake with Azure

This will open Integration Runtime Setup. Click on “Perform data movement and dispatch activities to external computes.” and then “Next”.

Data Migration to Snowflake with Azure

Choose “Self-Hosted” and then “Next”.

Data Migration to Snowflake with Azure

Click on “Download and install integration runtime”. This will launch the Microsoft web portal connection. It is important to make a note of the Authentication Keys as shown. You need these to pair your cloud integration runtime with the local integration runtime.

Data Migration to Snowflake with Azure

2. Download and install Azure Data Factory Integration Runtime on the on-premises server

Download the latest version of Integration Runtime HERE and Install on the local (on-premises) server.

3. Pair Cloud and On-Premises Integration Runtime

Once Integration Runtime is installed on the local server, it will open the Registration page. Past the first authentication key from Azure Integration Runtime in the box.

Data Migration to Snowflake with Azure

There will be a green checkmark indicating the key is valid. Click on “Register” and the on-premises server’s Azure Data Factory Integration Runtime is paired with Azure Integration Runtime. Copy key from Integration Runtime on Azure (step 1).

Data Migration to Snowflake with Azure

Click on “Finish”.

Data Migration to Snowflake with Azure

Once it has been paired, this message confirms it is up and running on local server.

Data Migration to Snowflake with Azure

4. Download ODBC driver from Snowflake Help and install on on-premises server

Data Migration to Snowflake with Azure

Open ODBC Administrator and verify that the Snowflake driver is installed.

Data Migration to Snowflake with Azure

5. Add the Snowflake System DSN through ODBC Administrator

Click on “System DSN” tab and click on “Add”. Smartbridge already added one.

Data Migration to Snowflake with Azure

A new dialog box will open. Add the Snowflake User, Password, Server, Snowflake Database, Snowflake Data Warehouse and User Role.

Data Migration to Snowflake with Azure

The new Snowflake ODBC data source has been added in System DSN.

6. Create Azure Linked Service pointing to On-Premises Source Database

On the “Linked Services” tab (under “Connections”), click on “New” and add a RDB data source pointing to the on-premeiss SQL Server database.

7. Create Azure Linked Service pointing to Snowflake Target Database

On “Linked Services” tab, click on “New” and add an ODBC data source pointing to the target Snowflake database.

Data Migration to Snowflake with Azure

Search for ODBC and Continue.

Data Migration to Snowflake with Azure

Fill out all the information, “Test connection” and “Finish”.

Data Migration to Snowflake with Azure

Linked Service is added successfully.

Data Migration to Snowflake with Azure

Create new Data Pipeline within your Data Factory.

Add “Copy Data” activity.

Source: on-premises source database

Sink: Snowflake target database

Data Migration to Snowflake with Azure

Trigger the pipeline and data will be copied from on-premises database to Snowflake database.

Data Migration to Snowflake with Azure

Snowflake Logo

Learn more about our partnership with Snowflake

There’s more to explore at Smartbridge.com!

Sign up to be notified when we publish articles, news, videos and more!