Airtable
Airtable is an online collaboration platform for creating and sharing relational databases.
Features
| Feature Name | Supported | Notes |
|---|---|---|
| Capture deletes | check | |
| History mode | check | |
| Custom data | check | |
| Data blocking | check | |
| Column hashing | check | |
| Re-sync | check | |
| API configurable | check | API configuration |
| Priority-first sync | ||
| Fivetran data models | ||
| Private networking | ||
| Authorization via API | check |
Supported deployment models
We support the SaaS Deployment model for the connector.
Setup guide
Follow our step-by-step Airtable setup guide to connect Airtable with your destination using Fivetran connectors.
Schema information
We use the Airtable base details to name the schema. We add the prefix _base_name_base_id to the original schema name you choose during the connection setup. For example, if you entered airtable as the destination schema name in the setup form, and the name of your Airtable base is marketing, your schema name will be airtable_marketing_id, where id is the 16-digit base ID.
The destination table name will be the same as the table name in your Airtable base.
If the Airtable base name changes, the schema name is not updated in the destination by default. Instead, Fivetran creates a new schema in your destination with the updated base name. Fivetran then syncs any new data from your account to the new schema in your destination. This schema does not contain the historical data.
To sync your historical data and current data in a single schema, do the following:
Trigger a re-sync for all your tables under the renamed base.
Delete the old schema from your destination.
Authentication
The Airtable connector supports the OAuth 2.0 authentication mechanism.
User permissions for OAuth 2.0 authentication
We can seamlessly sync the Airtable bases for which the user authenticating the connector has Creator or Owner permissions. These permissions enable us to create and delete the webhooks for these bases.
We re-import the tables of the bases for which the authenticating user does not have Creator or Owner permissions.
By default, Airtable users have Creator and Owner permissions for the bases that they create. If the bases are from a shared workspace, the owner of the shared workspace can grant the necessary permissions to the collaborators.
Limitations
Listed limitations are from the source side.
OAuth 2.0 authentication
For each third-party integration, Airtable limits the maximum number of authorizations to 20 for a single user. Because of this limitation, a single Airtable user can create a maximum of 20 Airtable connectors using OAuth-based authentication. For more information about this limitation, see Airtable's documentation.
Payloads
In Airtable's server, Airtable stores its incremental data as payload objects. Airtable deletes these payloads from its server after 7 days of generating them, regardless of whether our connector has synced these changes. For more information, see Airtable's documentation.
If you pause the connector or if the syncs fail continuously for more than 7 days, the payloads may not be available in Airtable's server to perform an incremental sync. To avoid data integrity issues in such scenarios, if Airtable deletes your payloads from its server, we re-import all the selected tables of the associated base. These re-imports count towards paid MAR.
Webhooks
Airtable allows a maximum of 10 webhooks for each base. If we are unable to create a webhook due to this limitation, we re-import the tables of the base until we can create a webhook. These re-imports contribute towards your MAR. See our troubleshooting documentation to learn how to delete existing webhooks in Airtable.
Webhooks created using OAuth authentication have an expiration time of 7 days.
Fivetran will refresh the OAuth-based webhooks associated with the connector during the sync of any table under the base which the webhook is associated with.
If an OAuth webhook expires, it becomes disabled, so a new OAuth webhook is created, and all tables in the base are re-imported, which is counted towards paid MAR.
Attributes observed only in webhook response
User type fields like collaborator and createdBy have attributes such as profilePicUrl and permissionLevel which are observed only in webhook responses during incremental syncs. So, after a re-sync, these attributes are unavailable in the destination until the fields are updated at the source.
Sync notes
Airtable attachment URLs shared using the API automatically expire after a couple of hours. You can use an external storage service such as Dropbox or Google Drive to store the attachments and link the URLs.
We skip formula fields containing error codes from the sync to prevent column data type changes in your destination. If all the values in a column contain error codes, we do not create the column in the corresponding destination table.
Syncing empty tables and columns
The Airtable connector does not support the creation of empty tables and columns in your destination.
Primary key
Airtable does not enforce constraints such as NOT NULL or UNIQUE on the user-configured primary field.
To ensure data integrity, we're utilizing Airtable's automatically generated id field as the primary key in your destination table. This field is unique for each record, guaranteeing a reliable identifier.
Renaming data
If you rename a column in Airtable, we treat it as two separate columns:
- the old column containing data added before the rename
- the new column containing data added after the rename
After you re-sync the table, we backfill the new column with the data from the old column, leaving the old column empty.
Deleting data
When you delete a row from a table in your Airtable base, we hard delete that row from the destination table.
History Mode
Airtable does not automatically include the Last Modified Time field in the table, which is essential for our history mode functionality. Without this field, re-syncing tables may result in data loss.
To prevent data loss, we only allow syncing tables that have the Last Modified Time field configured for All editable fields.
How to add the Last modified time field to a table
- Click the + icon (Add field).
- Select the Last modified time field type.
- Enter a Field name.
- In the Fields section, select All editable fields.
- Click Create field.
If a table doesn't have the Last Modified Time field, we skip this table during sync. Once you add the field to your table, Fivetran triggers a re-import to address data integrity issues and adjusts the Monthly Active Rows (MAR).
If a record does not have a Last Modified Time value, we use the record’s creation time; if that time is not available, we use the payload’s creation time instead. As a result, while the history of these records may be deleted, we preserve the latest state of the record.
In some instances, Airtable generates events for single character changes in a column, which results in multiple updates to the same record in your destination. To avoid phantom updates while in history mode, we assign the record_timestamp value with minute-level granularity as the _fivetran_start value.
For example, if the record_timestamp value is 2021-06-23T15:19:12.610Z, we assign _fivetran_start to be 2021-06-23T15:19:00.000Z. This way, at any given minute you have single record updates in your destination.