Airfold makes it easy to connect with data that you have stored in Snowflake. It also enables you to automate data updates using scheduled cron jobs.

This guide walks through an example of configuring a Snowflake connector in your workspace.

Creation

Create Source

Navigate to Sources and click on Create new Source. Alternatively, click on the + next to Sources in the left-hand tool bar.

In the popup window, click on Snowflake

Connection Details

The next window in the pop up will ask you for some information from your Snowflake account.

Account

The Account field requires the following format:

<your_account_id>.<your-snowflake-region>.<your_cloud_provider>

(e.g., lh10221.us-central1.gcp)

Run the following query in a Snowflake SQL worksheet to generate the correct account string:

SELECT
  LOWER(CURRENT_ACCOUNT()) || '.' ||
    CASE 
      WHEN SPLIT_PART(CURRENT_REGION(), '_', 1) = 'AWS' THEN 
        LOWER(SPLIT_PART(CURRENT_REGION(), '_', 2) || '-' || SPLIT_PART(CURRENT_REGION(), '_', 3) || '-' || SPLIT_PART(CURRENT_REGION(), '_', 4) || '.aws')

      WHEN SPLIT_PART(CURRENT_REGION(), '_', 1) = 'AZURE' THEN 
        LOWER(REPLACE(SPLIT_PART(CURRENT_REGION(), '_', 2), ' ', '') || '.azure')

      WHEN SPLIT_PART(CURRENT_REGION(), '_', 1) = 'GCP' THEN 
        LOWER(SPLIT_PART(CURRENT_REGION(), '_', 2) || '-' || SPLIT_PART(CURRENT_REGION(), '_', 3) || '.gcp')

      ELSE LOWER(CURRENT_REGION()) || '.unknown'
    END AS airfold_connector_account_field

Copy the result of this query and paste into the Airfold UI where needed. If this query does not return the expected value, you can manually construct the string using the following:

SELECT
    CURRENT_REGION(),
    CURRENT_ACCOUNT()

For example, if the above query returned:

Then the correct value for Account would be `gu81828.us-east-2.aws’

Username

This is the username that you use to log in to your Snowflake account. You can obtain this by running the following query:

SELECT CURRENT_USER();

Password

The password that you use to log in to your Snowflake Account. If unknown, contact your Snowflake administrator.

Database

Name of the database that you wish to access.

SELECT CURRENT_DATABASE();

Optional Settings:

Warehouse

Name of your warehouse, defaults to COMPUTE_WH

SELECT CURRENT_WAREHOUSE();

Schema

The schema name of the data you wish to ingest into Airfold, defaults to PUBLIC

SELECT CURRENT_SCHEMA();

Role

Name of your role

SELECT CURRENT_ROLE();

Click on “Test Connection”, and you should see this:

Select Table

Select the table you wish to add from your database. A Snowflake connection can only have one table. If you wish to join the data from multiple tables together, you will need to create a Source for each table and then join the data using a downstream Pipe.

User and Role

This section will require you to run administrative queries in your Snowflake account that will create a Role and a User and then grant permissions for those new resources to the database that you are syncing data from.

Run these queries one by one in your Snowflake account. Once each query has successfully run, click the Mark as Done checkbox and then Next.

Set Up Cron job

Set up automated data updates with a cron expression:

Confirm Schema

Airfold will automatically infer a schema from Snowflake, but you have the opportunity to make any changes. Remember - once a source is created, the table settings cannot be altered. If you wish to alter the table at a later stage, all the data will have to be dropped which could cause issues in your downstream, production APIs. Source settings, such as the Engine type and the Primary Key, are critical for optimal query performance - Take care at this stage to ensure your Source is optimally configured.

Click Confirm Schema to finalize the connection. Your Snowflake table will now sync into Airfold based on your cron schedule, and the Source will appear in your workspace.

Airfold makes it easy to connect with data that you have stored in Snowflake. It also enables you to automate data updates using scheduled cron jobs.

This guide walks through an example of configuring a Snowflake connector in your workspace.

Creation

Create Source

Navigate to Sources and click on Create new Source. Alternatively, click on the + next to Sources in the left-hand tool bar.

In the popup window, click on Snowflake

Connection Details

The next window in the pop up will ask you for some information from your Snowflake account.

Account

The Account field requires the following format:

<your_account_id>.<your-snowflake-region>.<your_cloud_provider>

(e.g., lh10221.us-central1.gcp)

Run the following query in a Snowflake SQL worksheet to generate the correct account string:

SELECT
  LOWER(CURRENT_ACCOUNT()) || '.' ||
    CASE 
      WHEN SPLIT_PART(CURRENT_REGION(), '_', 1) = 'AWS' THEN 
        LOWER(SPLIT_PART(CURRENT_REGION(), '_', 2) || '-' || SPLIT_PART(CURRENT_REGION(), '_', 3) || '-' || SPLIT_PART(CURRENT_REGION(), '_', 4) || '.aws')

      WHEN SPLIT_PART(CURRENT_REGION(), '_', 1) = 'AZURE' THEN 
        LOWER(REPLACE(SPLIT_PART(CURRENT_REGION(), '_', 2), ' ', '') || '.azure')

      WHEN SPLIT_PART(CURRENT_REGION(), '_', 1) = 'GCP' THEN 
        LOWER(SPLIT_PART(CURRENT_REGION(), '_', 2) || '-' || SPLIT_PART(CURRENT_REGION(), '_', 3) || '.gcp')

      ELSE LOWER(CURRENT_REGION()) || '.unknown'
    END AS airfold_connector_account_field

Copy the result of this query and paste into the Airfold UI where needed. If this query does not return the expected value, you can manually construct the string using the following:

SELECT
    CURRENT_REGION(),
    CURRENT_ACCOUNT()

For example, if the above query returned:

Then the correct value for Account would be `gu81828.us-east-2.aws’

Username

This is the username that you use to log in to your Snowflake account. You can obtain this by running the following query:

SELECT CURRENT_USER();

Password

The password that you use to log in to your Snowflake Account. If unknown, contact your Snowflake administrator.

Database

Name of the database that you wish to access.

SELECT CURRENT_DATABASE();

Optional Settings:

Warehouse

Name of your warehouse, defaults to COMPUTE_WH

SELECT CURRENT_WAREHOUSE();

Schema

The schema name of the data you wish to ingest into Airfold, defaults to PUBLIC

SELECT CURRENT_SCHEMA();

Role

Name of your role

SELECT CURRENT_ROLE();

Click on “Test Connection”, and you should see this:

Select Table

Select the table you wish to add from your database. A Snowflake connection can only have one table. If you wish to join the data from multiple tables together, you will need to create a Source for each table and then join the data using a downstream Pipe.

User and Role

This section will require you to run administrative queries in your Snowflake account that will create a Role and a User and then grant permissions for those new resources to the database that you are syncing data from.

Run these queries one by one in your Snowflake account. Once each query has successfully run, click the Mark as Done checkbox and then Next.

Set Up Cron job

Set up automated data updates with a cron expression:

Confirm Schema

Airfold will automatically infer a schema from Snowflake, but you have the opportunity to make any changes. Remember - once a source is created, the table settings cannot be altered. If you wish to alter the table at a later stage, all the data will have to be dropped which could cause issues in your downstream, production APIs. Source settings, such as the Engine type and the Primary Key, are critical for optimal query performance - Take care at this stage to ensure your Source is optimally configured.

Click Confirm Schema to finalize the connection. Your Snowflake table will now sync into Airfold based on your cron schedule, and the Source will appear in your workspace.