Create a Snowflake Connection in Airflow

Tip

The key information from this and other Snowflake guides is available as an Astronomer Cheat Sheet.

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.

Prerequisites

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

Key-pair authentication

A key-pair 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. For the Airflow Snowflake provider version 6.3.0+ the key needs to be base64 encoded.
• 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":"LS0..<key>..C0="
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 -v2 des3 -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. Format the private key. Version 6.3.0+ of the Airflow Snowflake provider requires the private key to be base64 encoded. You can create a base64 encoded key with the following script:

   1
   import base64
   2
   3
   with open("path/to/rsa_key.p8", "rb") as key_file:
   4
       private_key_content = base64.b64encode(key_file.read()).decode("utf-8")
   5
       print(private_key_content)
   6
   ```note

If you on version 6.2.2 or older of the Airflow Snowflake provider, you need to provide the private key without any coding conversions but with newlines encoded as \n. You can use the script below to format the key correctly:

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)

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

9. 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>';

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.

Info

Astro users can also create connections using the Astro Environment Manager, which stores connections in an Astro-managed secrets backend. These connections can be shared across multiple deployed and local Airflow environments. See Create Airflow connections in the Astro UI.

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

   apache-airflow-providers-snowflake>=6.4.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": "LS0..<key>..C0="
     8
     }

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 package documentation
• Orchestrate Snowflake Queries with Airflow tutorial
• Common SQL Airflow provider package documentation
• Import and export Airflow connections using Astro CLI
• See how Snowflake pipelines can run on Astro