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 Orchestrate Snowpark Machine Learning Workflows with Apache Airflow.

Prerequisites

• The Astro CLI.
• A locally running Astro project.
• A Snowflake account.

Connection options

When creating a Snowflake connection in Airflow, you can choose from the following options:

• Private Key (recommended): Use key-pair authentication with Snowflake.
• Password: Use a username and password to authenticate with Snowflake. This method is only recommended for testing purposes.

Private Key authentication

A private key connection from Airflow to Snowflake requires the following information:

Base parameters:

• conn_id: A unique name for the connection.
• conn_type: snowflake. Note that you need to install the apache-airflow-providers-snowflake provider package to use this connection type.
• login: The user you authenticate with. Note that some operators require the user to be properly capitalized.
• password: The passphrase of the private key. If your private key is not encrypted (not recommended), you can leave this field as an empty string.
• schema: The default schema for the connection, this can be overridden in the operator.

Parameters in the extra field:

• account: The account identifier from your Snowflake account URL in the format abc12345.
• warehouse: The default warehouse for this connection, this can be overridden in the operator.
• database: The default database for the connection, this can be overridden in the operator.
• region: The region identifier from your Snowflake account URL in the format us-west-2. Note that for some regions, you might have to include the cloud provider identifier after the region name, see the Snowflake documentation on account identifiers
• role: The role you want Airflow to have in Snowflake. Note that some operators require the user to be properly capitalized.
• private_key_content: The content of your private key file in the format "-----BEGIN ENCRYPTED PRIVATE KEY-----\nABC...ABC\nABC...ABC=\n-----END ENCRYPTED PRIVATE KEY-----\n",.
• private_key_file: alternatively to private_key_content, you can provide the path to your private key file.

Optional:

• authenticator: snowflake (default). To connect using OAuth set this parameter oauth.
• refresh_token: The refresh token for OAuth authentication.
• session_parameters: A dictionary of session parameters to set for the connection.
• insecure_mode: false (default). Set to true to disable OCSP certificate checks.

See the template below for a private key connection in JSON format:

1
AIRFLOW_CONN_SNOWFLAKE_DEFAULT='{
2
    "conn_type":"snowflake",
3
    "login":"<your user, properly capitalized>",
4
    "password":"<your private key passphrase>",
5
    "schema":"DEMO_SCHEMA",
6
    "extra":{
7
        "account":"<your account id in the form of abc12345",
8
        "warehouse":"<your warehouse>",
9
        "database":"DEMO_DB",
10
        "region":"<your region>",
11
        "role":"<your role, properly capitalized>",
12
        "private_key_content":"-----BEGIN ENCRYPTED PRIVATE KEY-----\nABC...ABC\nABC...ABC=\n-----END ENCRYPTED PRIVATE KEY-----\n"
13
    }
14
}'

Get connection details

Complete the following steps to retrieve the needed connection values:

4. Copy the names for your Database and Schema.

5. In your terminal run the following command to generate a private RSA key using OpenSSL. Note that while there are other options to generate a key pair, Snowflake has specific requirements for the key format and may not accept keys generated with other tools. Make sure to write down the key passphrase as you will need it later.

   $
   openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

6. Generate the associated public key using the following command:

   $
   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub 

7. In the Snowflake UI, create a new user that Airflow can use to access Snowflake. Copy the username and password.

8. Add the public key to the user you created in Snowflake. In the Snowflake UI, run the following command. You can paste the public key directly from the rsa_key.pub file without needing to modify it.

   1
   ALTER USER <your user> SET RSA_PUBLIC_KEY='<your public key>';

9. Run the following script to format the private key to replace all newlines with \n.

   1
   def format_private_key(private_key_path):
   2
       with open(private_key_path, 'r') as key_file:
   3
           private_key = key_file.read()
   4
       return private_key.replace('\n', '\\n')
   5
   6
   formatted_key = format_private_key('rsa_key.pem')
   7
   print(formatted_key)

Password authentication

When using password instead of a private key authentication, all connection parameters stay the same with the following changes:

• login: Your Snowflake login username (typically your email address).
• password: Your Snowflake login password.
• private_key_content: Remove this field from the connection JSON.

Note that this method will not work when using 2FA or MFA with Snowflake or with the SnowflakeSqlApiOperator.

Create your connection

Airflow connections can be created using multiple methods, such as environment variables, the Airflow UI or the Airflow CLI. The following example shows how to create a Snowflake connection using the Airflow UI.

1. Open your Astro project and add the following line to your requirements.txt file:

   apache-airflow-providers-snowflake>=5.7.0

   This will install the Snowflake provider package, which makes the Snowflake connection type available in Airflow.

2. Run astro dev restart to restart your local Airflow environment and apply your changes in requirements.txt.

3. In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection.

4. Fill out the following connection fields using the information you retrieved from Get connection details:

   • Connection Id: Enter a name for the connection.

   • Connection Type: Select Snowflake. If you don’t see this option, make sure you’ve added the apache-airflow-providers-snowflake provider package to your requirements.txt file.

   • Description: (Optional) Enter a description for the connection.

   • Schema: Enter your default schema.

   • Login: Enter your user. Make sure it’s properly capitalized.

   • Password: Enter your private key passphrase.

   • Extra: Enter the following JSON in the extra field and replace the values with your Snowflake connection details. Add any optional parameters as needed.

     1
     {
     2
         "account": "<your account id in the form of abc12345>",
     3
         "warehouse": "<your warehouse>",
     4
         "database": "<your database>",
     5
         "region": "<your region>",
     6
         "role": "<your role in capitalized format>",
     7
         "private_key_content": "-----BEGIN ENCRYPTED PRIVATE KEY-----\nABC...ABC\nABC...ABC=\n-----END ENCRYPTED PRIVATE KEY-----\n"
     8
     }

   Your connection should look something like the screenshot below.

   

How it works

Airflow uses the Snowflake connector Python package to connect to Snowflake through the SnowflakeHook.

The SnowflakeSqlApiOperator uses the Snowflake SQL API via the SnowflakeSqlApiHook.

See also

• Snowflake Airflow provider and the Snowflake Airflow provider package documentation
• Orchestrate Snowflake Queries with Airflow tutorial
• ELT with Snowflake and Apache Airflow® for eCommerce reference architecture
• Common SQL Airflow provider and the Common SQL Airflow provider package documentation
• Import and export Airflow connections using Astro CLI
• See how Snowflake pipelines can run on Astro