Airtable

Knowi connects directly to Airtable via the REST API, enabling analytics, visualization, and reporting on your Airtable bases alongside 30+ other structured and unstructured data sources. Build dashboards, join Airtable data with SQL databases and APIs, and automate reporting in minutes.

Overview

1. Connect to Airtable using a Personal Access Token (PAT) for authentication.
2. Select from pre-built collections (List Bases, List Tables, List Records) or use the Custom Endpoint for any Airtable API call.
3. Join Airtable data with MongoDB, PostgreSQL, REST APIs, and 30+ other sources without ETL.
4. Visualize and automate your reporting instantly.

Connecting

Step 1: Create a Personal Access Token (PAT)

1. Log in to your Airtable account.
2. Go to Account Overview (click your profile icon in the top right), then click Developer Hub in the left sidebar, or navigate directly to airtable.com/create/tokens.
3. Click Create new token.
4. Give your token a name (e.g., "Knowi Analytics").

5. Add the following scopes (click "Add a scope" for each):

   • data.records:read — Required to read records from tables (used by the List Records collection)
   • schema.bases:read — Required to list bases and tables (used by the List Bases and List Tables collections)

   Important: If you skip schema.bases:read, the List Bases and List Tables collections will return a 403 Permission Denied error. If you only need to read records and already know your Base ID and table names, data.records:read alone is sufficient.

6. Set Base Access: Under Access, select the specific bases you want Knowi to access, or choose All current and future bases in all current and future workspaces for full access.
7. Click Create token and copy the token immediately. Airtable only shows the full token once. It starts with pat (e.g., patXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX).

Step 2: Connect in Knowi

1. Log in to Knowi and navigate to Queries from the left sidebar.
2. Click New Datasource (the + icon), then search for Airtable in the datasource selector.
3. Fill in the form:
   • Datasource Name: A label for this connection (e.g., "Airtable Production")
   • Personal Access Token: Paste the PAT you copied in Step 1
4. Click Test Connection to verify. You should see "Connection Successful".
5. Click Save to store the datasource.

Step 3: Create a Query

1. Click Start Querying (or create a new query and select your Airtable datasource).
2. Select a Collection from the "Airtable Options" dropdown:
   • List Bases — Discover all bases you have access to (requires schema.bases:read scope)
   • List Tables — See all tables in a specific base (requires schema.bases:read scope)
   • List Records — Pull records from a specific table (requires data.records:read scope)
   • Custom Endpoint — Query any Airtable API endpoint
3. Fill in the required parameters (Base ID, Table Name, etc.).
4. Click Preview to see results.
5. Name your dataset and click Create & Run to save.

Available Collections

List Bases

Returns all Airtable bases accessible with your token.

Parameters: None required.

Default Cloud9QL: 

select bases;

select expand(bases);

select id, name, permissionLevel;

List Tables

Returns all tables in a specific base, including field definitions.

Parameters: 

Default Cloud9QL:

select tables;

select expand(tables);

select id, name, primaryFieldId, description;

List Records

Returns records from a specific table with optional filtering, sorting, and field selection.

Parameters:

Default Cloud9QL:

select records;

select expand(records);

select id, createdTime, fields;

select id, createdTime, expand(fields); 

Custom Endpoint

Query any Airtable API endpoint directly.

Parameters: 

Cloud9QL Examples

Flatten nested fields from records

select records;

select expand(records);

select id, createdTime, fields;

select id, createdTime, expand(fields);

select id, createdTime, Name, Status, Priority, Assignee, Due Date
order by Due Date desc;

Count Records by Status

select records;

select expand(records);

select id, createdTime, expand(fields);

select Status, count(*) as count group by Status
order by count desc;

Filter and format dates

select records;

select expand(records);

select id, createdTime, expand(fields);

select Name, Status, Priority, dateformat(createdTime, 'yyyy-MM-dd') as createddate
where Status = 'In Progress'
order by createdTime desc;

Get table schema with field types

select tables;

select expand(tables);

select name, expand(fields);

select name, fields.name as fieldname, fields.type as fieldtype;

Scheduling

Set up automated data pulls from Airtable:

1. After creating a query, click the Schedule tab.
2. Set the frequency (e.g., every 15 minutes, hourly, daily).
3. Choose a data storage strategy:
   • Replace All — Replace all data each run
   • Append — Add new records alongside existing data
   • Upsert by Key — Update existing records by a key field (e.g., record ID)

Note: Airtable's API has a rate limit of 5 requests per second per base. For large bases with many records, schedule queries at reasonable intervals to stay within limits.

Cross-Source Join Examples

Airtable + PostgreSQL

Join Airtable project data with PostgreSQL financial data:

1. Create a query on Airtable to pull project records.
2. Create a second query on PostgreSQL for budget data.
3. Use Cloud9QL to join:

select a.Name as project,
  a.Status,
  b.budget,
  b.spend 
from AirtableProjects a
join PostgresBudgets b
  on a.Project ID = b.project_id
where a.Status = 'Active';

Airtable + REST API

Enrich Airtable CRM data with external API data:

1. Pull contacts from Airtable.
2. Pull company data from a REST API.
3. Join on company name or domain.

Airtable + MongoDB

Combine Airtable task tracking with MongoDB event logs:

1. Pull tasks from Airtable.
2. Pull activity logs from MongoDB.
3. Join on user ID or task reference.

Troubleshooting

Authentication Errors (401)

• Verify your Personal Access Token is valid and not expired.
• Check that the token has the required scopes (data.records:read, schema.bases:read).
• Ensure the token has access to the specific base you are querying.

404 Not Found

• Verify the Base ID starts with app and is correct.
• Verify the table name matches exactly (case-sensitive).
• For table names with spaces, they are URL-encoded automatically.

Rate Limiting (429)

• Airtable allows 5 requests per second per base.
• Reduce query frequency or increase the schedule interval.
• Use the view parameter to filter server-side instead of pulling all records.

Empty Results

• Check that the table has records.
• Verify the filterByFormula syntax is correct.
• Try querying without filters first, then add them back.
• Ensure the fields parameter uses exact field names.

Pagination

• Airtable returns a maximum of 100 records per request.
• Knowi handles pagination automatically using the offset parameter.
• For very large tables (10,000+ records), consider using filters or views to reduce the result set.

Additional Resources

• Airtable API Documentation
• Airtable Formula Reference
• Cloud9QL Reference
• Cross-Source Joins
• Embedded Analytics & SSO