Airtable Analytics & Reporting - Knowi Integration
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
Connect to Airtable using a Personal Access Token (PAT) for authentication.
Select from pre-built collections (List Bases, List Tables, List Records) or use the Custom Endpoint for any Airtable API call.
Join Airtable data with MongoDB, PostgreSQL, REST APIs, and 30+ other sources without ETL.
Visualize and automate your reporting instantly.
Connecting
Step 1: Create a Personal Access Token (PAT)
Log in to your Airtable account.
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.
Click Create new token.
Give your token a name (e.g., "Knowi Analytics").
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.
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.
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
Log in to Knowi and navigate to Queries from the left sidebar.
Click New Datasource (the + icon), then search for Airtable in the datasource selector.
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
Click Test Connection to verify. You should see "Connection Successful".
Click Save to store the datasource.
Step 3: Create a Query
Click Start Querying (or create a new query and select your Airtable datasource).
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
Fill in the required parameters (Base ID, Table Name, etc.).
Click Preview to see results.
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:
```sql
select bases;
select expand(bases);
select id, name, permissionLevel;
```
List Tables
Returns all tables in a specific base, including field definitions.
Parameters:
| Parameter | Required | Description |
|-----------|----------|-------------|
| Base ID | Yes | The Airtable base ID (starts with app) |
Default Cloud9QL:
```sql
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:
| Parameter | Required | Description |
|-----------|----------|-------------|
| Base ID | Yes | The Airtable base ID (starts with app) |
| Table Name or ID | Yes | Table name or ID (starts with tbl) |
| Page Size | No | Records per page, 1-100 (default: 100) |
| View | No | View name or ID to apply view filters |
| Filter Formula | No | Airtable formula to filter records |
| Sort Field | No | Field name to sort by |
| Sort Direction | No | asc or desc |
| Fields | No | Comma-separated field names to return |
Default Cloud9QL:
```sql
select records;
select expand(records);
select id, createdTime, fields;
select id, createdTime, expand(fields);
```
Custom Endpoint
Query any Airtable API endpoint directly.
Parameters:
| Parameter | Required | Description |
|-----------|----------|-------------|
| API Path | Yes | Path after /v0/ (e.g., appXXX/TableName) |
| Query Parameters | No | Additional URL query parameters |
Cloud9QL Examples
Flatten nested fields from records
```sql
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
```sql
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
```sql
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
```sql
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:
- After creating a query, click the Schedule tab.
- Set the frequency (e.g., every 15 minutes, hourly, daily).
- 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:
- Create a query on Airtable to pull project records.
- Create a second query on PostgreSQL for budget data.
- Use Cloud9QL to join:
```sql
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:
- Pull contacts from Airtable.
- Pull company data from a REST API.
- Join on company name or domain.
Airtable + MongoDB
Combine Airtable task tracking with MongoDB event logs:
- Pull tasks from Airtable.
- Pull activity logs from MongoDB.
- 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
Datasources
Knowledge Center
Video Tutorials
Security
About Knowi