- 15 Jul 2024
- 11 Minutes à lire
- Impression
- SombreLumière
- PDF
Airtable Sync integration: Snowflake
- Mis à jour le 15 Jul 2024
- 11 Minutes à lire
- Impression
- SombreLumière
- PDF
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 |
|
Learn how to use the Airtable Sync integration feature to bring information from an external application into Airtable as a new table in your base. Sync integrations are always a one way sync from the external application into an Airtable base, so changes made in Airtable cannot be synced back to the external application that you are syncing with.
The Snowflake Sync allows you to sync the contents of a Snowflake view (saved query) or table as an Airtable table.
Understanding the Snowflake sync
The sync can be configured to occur automatically every 6 hours or only synced when manually triggered by a base user with editor or higher permissions.
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
Navigate to the Airtable admin panel → Settings → Integrations & development
Under the “Block integrations in automations and external source sync” section, click All integrations and allow Snowflake.
Under “Configure integrations,” go to Snowflake and click Add configuration.
Enter a name for your configuration.
Next, enter the Snowflake account identifier, Snowflake account locator, Snowflake client id, Snowflake client secret obtained from steps 1 and 2 above.
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
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:
Click the Select account dropdown.
Click the Connect new Snowflake account option.
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.
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.
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
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.
You can also configure which fields to bring into the sync by clicking the Edit synced fields option.
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. 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.
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. 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. 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.
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.