Snowflake - Load data - Apache Airflow Load Control - 1.7

Before loading the data from your Source(s) System(s) to your Snowflake Target System, please:

You are now ready to load the data with Apache Airflow.

Prerequisites

To load data to Snowflake with Apache Airflow, please configure a key-pair authentication for the user you will use to load the data.

Official Snowflake guidelines are available here.

Please generate an encrypted private key.

In Airflow, you will need the following information:

  • The password used to encrypt the private key
  • The private key itself
-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFJDBWBgkqhkiG9w0
....
....
....
rnG/kpSV4Fl/Klf1Xygk/qJDY+efGi6E
-----END ENCRYPTED PRIVATE KEY-----

Load the data with Apache Airflow

To load the data with Apache Airflow:

    • Navigate to the Airflow homepage http://localhost:8080
    • Enter your credentials
    • You should have the following result:
    • Configure a connection to your target solution environment by following the steps:
      • Open the menu Admin > Connections
      • Click on the plus icon
      • Enter the connection details:

        • Connection id: Must be snowflake_default
        • Connection Type: select Snowflake
        • Schema: leave it empty
        • Login: username to access your target database
        • Password: password used to encrypt your private key
        • Account: account where your target database is stored
          • If you access Snowflake with the URL https://zz11111.eu-west-1.snowflakecomputing.com, the account is zz11111
        • Warehouse: name of the Snowflake Compute Unit to use
        • Database: target database name
        • Region: region where your target database is stored
          • If you access Snowflake with the URL https://zz11111.eu-west-1.snowflakecomputing.com, the region is eu-west-1
        • Role: role to access the target database

    If you are using Key Pair Authentication in Snowflake, add the Private Key (text).

    More information on the official Snowflake documentation is here.

     
      • Check the connection by clicking on the Test button:
      • You should have the following message:
      • Save the connection by clicking on the Save button:
    • Go back to the DAGs list by clicking on the DAGs menu:
    • To launch a data load, click on the Trigger DAG button for the concerned DAG:
    • The data load started:
    • You can follow the data load by clicking on the DAG, then on the current execution (green bar), and finally on the Graph tab:
    • If you want to check the logs of the execution, click on the Audit Log tab:
    • You can now check that your data were correctly loaded with the following script in Snowflake:
    USE DATABASE IDENTIFIER('[Here your target database name]');

    SELECT table_schema AS table_schema
    , table_name AS schema_table
    , table_type AS table_type
    , row_count AS rows_count
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
    AND table_schema IN ('SA','CA','COR','DM')
    ORDER BY row_count;