Airtable Sync integration: 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.

There are two broad methods you can utilize to gather the necessary information before moving onto step 3.

1. SQL-based

   1. For "Snowflake account identifier", you can run the following commands:

      1. SELECT CURRENT_ORGANIZATION_NAME(); (ex. AIRTABLE)

      2. SELECT CURRENT_ACCOUNT_NAME(); (ex. DATA_ENG)

      3. Then, combine them into orgName-accountName (ex. AIRTABLE-DATA_ENG)

   2. For "Snowflake account locator", you can directly use the output of SELECT CURRENT_ACCOUNT();

2. UI-based

   1. Format 1: Use the "Snowflake account identifier" can be retrieved by following Snowflake's instructions. If copying from the UI, replace the “.” in the middle with a “-” to update it to the format used by the API.  

   2. Format 2: Use the "Snowflake account locator" by following Snowflake’s instructions.

   3. When combined the result should look like this: orgName-accountName (ex. AIRTABLE-DATA_ENG)

Note

Regardless of the method that you choose, do not include the < or > characters that Snowflake shows in its documentation.

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.

   1. Note that “All integrations" isn't visible if "Block integrations in automations and external source sync" is disabled. If that's the case, then you won’t need to specifically enable 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, your Airtable users with owner/creator permissions 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.

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.

While the Snowflake admin setup described above must be performed by a user with the ACCOUNTADMIN role, connecting a Snowflake account cannot be done with a user who has ACCOUNTADMIN as their default role and will result in an "Invalid consent request” error from Snowflake. To resolve this error, ensure the user attempting to connect to Snowflake has a different default role or have a different user connect to the 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.

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.

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.

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

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. This will result in unpredictable behavior for tables and views without an ORDER BY clause, which do not have a consistent sort order in Snowflake. For views with an ORDER BY clause, it will sync the first 100,000 rows according to that ordering. 

We recommend using a view with an ORDER BY clause for data sets that exceed the record limit to avoid this issue.

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.

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.

• 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. This means that the user, a role of the user, or the account has a network policy that restricts the IP address being used to log into Snowflake. Keep in mind that there may be multiple network policies in effect, and our integration will not work if any single one of those rejects our IP addresses. To allow Airtable access while still restricting other external traffic from accessing your Snowflake instance, ask an administrator of your Snowflake account to add the static IP addresses listed in this article to the allowlist in your firewall.

• 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. You’ll likely see the message Could not authorize the account when encountering this error. To allow Airtable access while still restricting other external traffic from accessing your Snowflake instance, ask an administrator of your Snowflake account to add the static IP addresses listed in this article to the allowlist in your firewall.

• 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.

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.

“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.

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.

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.

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.