Airtable Sync integration: Snowflake
  • 31 Jan 2024
  • 10 Minuten zu lesen
  • Dunkel
    Hell
  • pdf

Airtable Sync integration: Snowflake

  • Dunkel
    Hell
  • pdf

The content is currently unavailable in German. You are viewing the default English version.
Article Summary

Plan availability

Enterprise Scale access only

Permissions

Owners/Creators - To create or update synced tables, you'll need to have creator permissions in the base where you are setting up the sync.

Platform(s)

Web/Browser, Mac app, and Windows app

Related reading

  • Sync - Basic setup - If you haven’t set up a sync before, we recommend first reading this article to become familiarized with how the Airtable Sync feature generally works.

  • Airtable Sync FAQs - Answers to common questions that might help unblock you if you are encountering issues.

Understanding the Snowflake sync

The Snowflake Sync allows you to sync the contents of a Snowflake view (saved query) or table as an Airtable table. The sync can be configured to be manual or to occur automatically every 6 hours.

Snowflake sync uses OAuth authentication to access data in Snowflake. Configuring this for a Snowflake account/instance requires a one-time setup with account admin access to Snowflake and organization admin access in Airtable. After that, all users within your organization will be able to use the configuration to connect their Snowflake account to Airtable.

Admin setup in Snowflake and Airtable

Step 1: Create OAuth security integration in Snowflake

This step must be done by someone with ACCOUNTADMIN role in Snowflake.

Run the following SQL command to create an OAuth security integration, choosing a desired name for it (users will see the name when connecting to Snowflake from Airtable):

CREATE OR REPLACE SECURITY INTEGRATION INTEGRATION_NAME_HERE
  TYPE = oauth
  OAUTH_CLIENT = custom
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = "https://airtable.com/integration/authorize/eatY89uUyfSoAncQS/callback"
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
  OAUTH_USE_SECONDARY_ROLES = IMPLICIT
  ENABLED = true;

After that command, run this command to retrieve the necessary information about the security integration, adding the chosen integration name between the quote marks (must be in all capital letters):

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('INTEGRATION_NAME_HERE');

The output will look like the following:

{"OAUTH_CLIENT_SECRET_2":".......","OAUTH_CLIENT_SECRET":".......","OAUTH_CLIENT_ID":"........"}

Copy and save the text in between the quotes after “OAUTH_CLIENT_SECRET” and after “OAUTH_CLIENT_ID” - do not include the quotes. These are the “Snowflake client secret” and “Snowflake client id” that will be required in step 3 below.

Step 2: Gather other required information in Snowflake

The Snowflake account identifier and the Snowflake account locator are also required. Note that we only support the “Account Name in Your Organization” format for the account identifier, which is preferred by Snowflake.

Follow the instructions here to obtain the organization and account name. If copying from the UI, replace the “.” in the middle with a “-” (to update it to the format used by the API).

The account identifier can also be found in the previous UI under “Locator”, or by running the SQL command SELECT CURRENT_ACCOUNT().

Step 3: Add configuration in the Airtable admin panel

  1. Navigate to the Airtable admin panel → Settings → Integrations & development

  2. Under the “Block integrations in automations and external source sync” section, click All integrations and allow Snowflake.

  3. Under “Configure integrations,” go to Snowflake and click Add configuration.

  4. Enter a name for your configuration.

  5. Next, enter the Snowflake account identifier, Snowflake account locator, Snowflake client id, Snowflake client secret obtained from steps 1 and 2 above.

  6. Lastly, click Save. Note that it may take a few minutes to save.

After completing the setup, Airtable users will be able to use this configuration to connect to Snowflake. If you have multiple Snowflake accounts, repeat the Admin setup steps for each of them, choosing a different integration name in step 1 and configuration name in step 3 for each. Airtable users will be able to choose between the different configurations when adding a Snowflake connection.

Adding a new Snowflake synced table

Step 1: Add a new synced table

From a new or existing base, find the + Add or import button next to the table furthest to the right. If you have 4 or more tables in your base, then this button will simply appear as a + plus sign. Next, under “Add from other sources,” click the ## more sources option. Then, you’ll see a pop-up of other available syncs to choose from. Finally, click the Snowflake option.

Step 2: Connecting a Snowflake account

Under “Snowflake Account,” you’ll need to select a previously connected account or connect a new one. To connect a new account:

  1. Click the Select account dropdown.

  2. Click the Connect new Snowflake account option.

  3. A pop-up window will appear where you will select a Snowflake account under the “Choose a Snowflake account” dropdown menu. If there are no options present or if you don’t see the Snowflake account that you want to sync into Airtable, then you will need to have someone with Admin privileges in Airtable and Snowflake complete the setup process outlined above.

  4. After choosing a Snowflake account, click Continue to be redirected to Snowflake to sign in and grant Airtable access to your Snowflake account. Enter your “Username” and “Password” to authenticate.

  5. You’ll then need to click Allow so that Airtable and Snowflake can connect.

Note

Snowflake only allows each user to be connected via OAuth once. For example, there is a shared user sync_user and Alice uses it to connect to Snowflake from Airtable, and then later on Bob also uses it to connect to Snowflake from Airtable, Alice’s connection will stop working. Each user in Airtable who wants to configure Snowflake syncs needs a separate user account in Snowflake.

Step 3: Setting up the Snowflake sync configuration

After you have connected a Snowflake account, you can begin setting up the Snowflake sync configuration. The first step is to select the following:

  • Snowflake Account - the Snowflake user connected in the previous step

  • Warehouse - the Snowflake virtual warehouse (compute resources) used for the SQL queries executed when configuring and performing the sync.

    • Note: Snowflake charges for usage based on the warehouse size (larger warehouses consume more credits). A XS (smallest warehouse size) warehouse is usually sufficient even for syncing large tables. A larger warehouse may be needed if syncing a very expensive/complicated view.

    • The size of each warehouse is shown in the dropdown.

  • Database, Schema, Table/View, Source - The specific model in Snowflake to sync. Both regular and materialized views are supported.

  • Unique Column - A unique column in the table. Select the table/view’s ID column. This is how Airtable identifies the original row in Snowflake.

Note

The options above show everything available to the connected user in Snowflake. If the warehouse/database/schema/table/view you want to use is not visible, see the FAQs.

Step 4: Configure sync settings and create

  1. Before creating your synced table, there are options in the Settings section that allow you to choose whether or not you want to sync manually or automatically, and how to handle records deleted or hidden in the Snowflake source.

  2. You can also configure which fields to bring into the sync by clicking the Edit synced fields option.

  3. Once you’ve selected your desired sync settings, click the Create table button. Depending on how large the dataset being synced in from Snowflake is it may take a few moments for the synced table to be created.

Snowflake sync behavior & limitations

Sync frequency

The sync can be configured to either only occur manually (when triggered) or automatically roughly every six hours.

Record limits & behavior when over limits

Snowflake sync can sync up to 100,000 rows into Airtable. By default, we will allow you to sync tables/views that are over the limit, but we will only sync the first 100,000 rows into Airtable, dropping the rest.

Account expiry & how to reconnect an account

By default, Snowflake only allows OAuth authorizations to last for 90 days, after which they expire. After this happens, your syncs will stop working. To get them to work again, you will need to reconnect your Snowflake connection.

This can be done by clicking your profile picture in the top-right-hand corner of airtable.com, selecting Integrations, and clicking the three-dot menu next to your Snowflake connection:

After clicking Reconnect, it will bring you through the same steps as Connecting a Snowflake account again - it’s recommended to log in with the same Snowflake user you used previously, but you can also reconnect with a different user if needed.

Increasing the time to expiry

A Snowflake account administrator can request for Snowflake Support to increase the expiry time from 90 days up to a maximum of 1 year by first following the steps here under “Could the Refresh Tokens' validity be increased beyond the Maximum limit in special circumstances?”.

After Snowflake support increases the limit for your account, run the following command to update the OAuth security integration to use the higher limit:

ALTER SECURITY INTEGRATION INTEGRATION_NAME_HERE
  SET OAUTH_REFRESH_TOKEN_VALIDITY = 31622400

Existing connections will still expire after 90 days, but new and re-connected connections after the above has been run will then last for 1 year instead of 90 days.

Understanding Snowflake (SQL) types to Airtable field types

Other Snowflake data types not listed below are not supported. Columns of unsupported data types will not be synced into Airtable.

Snowflake data type

Airtable field type

Usage notes

NUMBER

Number

The column will default to scale decimal places, up to a maximum of 8.

FLOAT

Number

The column will default to 8 decimal places. Any additional decimal places will not be displayed - some precision may be lost.

TEXT

Long text

Rich text formatting is not supported

BOOLEAN

Checkbox

Both false and null values will be “unchecked” when synced into Airtable

DATE

Date

TIMESTAMP_NTZ

Date (include time)

The wall clock time will be interpreted with UTC/GMT time zone

TIMESTAMP_LTZ

Date (include time)

TIMESTAMP_TZ

Date (include time)

The time zone offset will not be considered, to 

ARRAY

Long text

OBJECT

Long text

Understanding common Snowflake sync errors

When connecting a Snowflake account:

  • Invalid consent request error when logging in to Snowflake - the user’s default role is not allowed to authorize Snowflake via OAuth (e.g. ACCOUNTADMIN). Try using a different user.

  • Incoming request with IP/Token ... is not allowed to access Snowflake when logging in to Snowflake - the user, a role of the user, or the account has a network policy that restricts the IP address being used to log in to Snowflake. Ask an administrator of your Snowflake account to update the network policy.

  • Login pop-up disappears when trying to log in as a user - Similar to the above, the user, a role of the user, or the account has a network policy that restricts the IP addresses of our servers. Ask an administrator of your Snowflake account to update the network policy to allow the following IP addresses:

    • 52.44.24.118

    • 3.215.151.50

    • 34.204.30.184

    • 3.225.92.43

    • 3.231.96.120

    • 34.236.75.94

  • 403 Forbidden error after selecting a configuration, clicking “Connect”, and clicking “Continue” in Airtable - there is an issue with some of the information provided during the admin setup. Ask your admin to try adding a new configuration again, taking care to ensure that all of the information is entered correctly.

During sync setup:

SQL compilation error: Object 'table/viewName' does not exist or not authorized after selecting a table/view - The user has permission to see the selected table/view but not to read the contents of the table/view.

After a sync has been set up:

“The selected account is not valid or no longer works” - there are many possible reasons for this error, including:

  • The user no longer exists in Snowflake

  • The user’s authorization has been revoked from the Snowflake side

  • Security integration has been removed from the Snowflake side

  • Network policy is blocking Airtable’s API requests

  • The user’s authorization has expired

To remedy this, follow the prompts to re-connect the account.

FAQs

Why does my Snowflake account say it is expiring in 90 days?

Snowflake enforces a maximum validity of 90 days on OAuth connections. After this, you can re-connect your Snowflake account, after which it will be valid for another 90 days, etc. See this section for more details.

Why can’t I see the table/view/database/schema/warehouse I want to use during sync setup?

Airtable is only able to see the models that your user’s default role has access to in Snowflake and the models that your secondary roles have access to if your user has DEFAULT_SECONDARY_ROLES set to ('ALL'). Ensure that the user has a role that grants access to the models you want to use and that it is either the user’s primary role or that DEFAULT_SECONDARY_ROLES is set to ('ALL').

Another option is to create a new user to use for sync purposes with a default role that has access to all tables you want to sync. For more information on Snowflake roles, refer to their documentation here.

What can Airtable access in the connected Snowflake account?

Airtable can access the models that your user’s default role has access to in Snowflake, as well as the models that your secondary role has access to if your user has DEFAULT_SECONDARY_ROLES set to ('ALL').

We recommend setting up a separate user in Snowflake to use for sync purposes with a primary role that only has access to the models that you want to sync into Airtable. For more information on Snowflake roles, refer to their documentation here.


War dieser Artikel hilfreich?