Airtable

Airtable API

API Documentation Link

Array Fields

When an Airtable table has any fields that are of Linked record, Lookup, or Multiple select type, the field values in the API response will be in an array, such as:

{
    "records": [
        {
            "id": "exampleid",
            "createdTime": "2024-01-01T00:00:00.000Z",
            "fields": {
                "Example Array": [
                    "value1",
                    "value2"
                ]
            }
        }
    ]
}

When only one array is present in a response, Panoply allows for the ability to flatten this value if desired, which will be explained further below. However, when multiple arrays are present in the response, then each field will result in a new sub-table, such as the following:

{
    "records": [
        {
            "id": "exampleid",
            "createdTime": "2024-01-01T00:00:00.000Z",
            "fields": {
                "Example Array 1": [
                    "value1",
                    "value2",
                ]
                "Example Array 2": [
                    "value3",
                    "value4",
                ]
            }
        }
    ]
}

where the result would be tables fields_example_array_1 and fields_example_array_2, with each table have two values that are linked to the exampleid. This can work just fine, however when querying the data and joining multiple sub-tables that were created from an array field, this can lead to duplicates in the resulting query, and should be done with caution of such possibilities.

All of this leads to multiple approaches to configuring an Airtable Flex Connector in Panoply, all of which will be outlined below. If there are any questions regarding a given configuration, or which may best fit your end goals with the data resulting from this configuration, please reach out to our Support Team and we would be happy to assist as needed.

1. If none of the fields in the table being ingested are of Linked record, Lookup, or Multiple select type, please follow the Standard Configuration options below, which will essentially ingest the data as if it was a normal table with no special considerations.
2. If one of the fields in the table being ingested is of Linked record, Lookup, or Multiple select type and you would like to flatten the array, please follow the Flatten Array options below. Please note, when flattening an array on a table, this will result in a number of duplicate records equal to the number of values in the array. For example, using the same single-array example from above, the resulting table would be:

3. If multiple fields in the table being ingested is of Linked record, Lookup, or Multiple select type, there are two options available for ingestion:

• 3a: Only Available for BigQuery Panoply Accounts - If you would like the data within the fields object to be kept as JSON when ingested, please follow the JSON options below. This will create a column called fields that preserves the JSON formatting from the API response, which can then be queried. Please note that this querying will be slightly more complex than a normal SQL query, so if you are aiming for a solution that doesn't require much SQL experience, this may not be the best option.
• 3b: If you would like the data within the fields object to be treated as strings instead of arrays, please follow the Strings options below. This is a native option to Airtable and converts the array fields into strings. Using the second example from above with multiple arrays, the API response would instead be:

  {
      "records": [
          {
              "id": "exampleid",
              "createdTime": "2024-01-01T00:00:00.000Z",
              "fields": {
                  "Example Array 1": "value1, value2"
                  "Example Array 2": "value3, "value4"
              }
          }
      ]
  }
  

  thus resulting in just a single fields table and that contains a single column for each of the fields. If you are looking to simplify your SQL querying requirements and don't care about preserving the array structure of the API response, this would be the ideal option.
  • Please note that as part of this option, fields that have multiple parts to them resulting in a new JSON level in the API response, such as the Button Field, will result in a change to how much information is contained in the API response, which may or may not be meaningful to your business case. As you can see from the below examples, the label value of Test is not present in the Strings response, and instead only the url value of Done appears:
    Non-Strings Response
    
    
    Strings Response
    

Connector configuration

• Base url: https://api.airtable.com/v0/$base_id / $table_id
• Method: GET

• URL Parameters: pageSize=100

Authorization

• Authorization: Bearer Token
• Token: $personal_access_token

Pagination

• Enable:
• Get next page value: Dynamic (next page value included in the response)
• Next page key in response: offset
• Use next page value: As a URL parameter value
• URL/body key: offset
• Stop iterating when: No next page in response

Advanced Settings

• Data key: records