Create a Snowflake Connection in Airflow
Snowflake is a cloud data warehouse where you can store and analyze your data. Integrating Snowflake with Airflow allows you to do all of the following and more from a DAG:
- Run SQL
- Monitor the status of SQL queries
- Run a SnowPark python function
- Load and export data to/from Snowflake
This guide provides the basic setup for creating a Snowflake connection. For a complete integration tutorial, see Orchestrate Snowflake Queries with Airflow. To run Snowpark queries in Airflow, see Run Snowpark queries with the ExternalPythonOperator in Apache Airflow.
Prerequisites
- The Astro CLI.
- A locally running Astro project.
- A Snowflake account.
Get Connection details
A connection from Airflow to Snowflake requires the following information:
- Host
- Account
- Region
- Role
- Database
- Warehouse
- Username
- Password
Complete the following steps to retrieve these values:
Open the Snowflake classic console and locate the URL for the page. The URL should be in the format
https://<account-identifier>.<region>.snowflakecomputing.com/
. If you're using the new Snowsight console (https://app.snowflake.com
), follow the Snowflake documentation to retrieve your URL.Copy
<account-identifier>
and<region>
from the URL.infoWhen you copy your
region
, you might have to additionally copy the cloud provider identifier after the region name for some GCP and some AWS regions. For example, if your account URL ishttps://ZS86751.europe-west4.gcp.snowflakecomputing.com
, then yourregion
will beeurope-west4.gcp
. See Account identifiers to learn more about Snowflake's account types and their identifiers.Follow the Snowflake documentation to copy your Role.
Copy the names for your Warehouse, Database, and Schema.
Create a new user that Airflow can use to access Snowflake. Copy the username and password.
Create your connection
Open your Astro project and add the following line to your
requirements.txt
file:apache-airflow-providers-microsoft-snowflake
This will install the Snowflake provider package, which makes the Snowflake connection type available in Airflow.
Run
astro dev restart
to restart your local Airflow environment and apply your changes inrequirements.txt
.In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection, then select the connection type as Snowflake.
Fill out the following connection fields using the information you retrieved from Get connection details:
- Connection Id: Enter a name for the connection.
- Schema: Enter your Schema.
- Login: Enter your username.
- Password: Enter your password.
- Account: Enter your
account-identifier
. - Warehouse: Enter your Warehouse.
- Database: Enter your Database.
- Region: Enter your
region
. - Role: Enter your Role.
Click Test. After the connection test succeeds, click Save.
How it works
Airflow uses the Snowflake connector python package to connect to Snowflake through the SnowflakeHook.