Airtable is where a lot of operations teams do their actual work, and it’s good at that. Reporting is a different job. Once finance wants Airtable jobs next to invoices from the accounting system, or the operations lead asks how long requests sat untouched last quarter, people start exporting CSVs into spreadsheets and the numbers stop agreeing.
If the company already runs Amazon Redshift, the fix is to put Airtable data there with everything else. This article is the design we’d start from: no code, because most of the decisions that matter come before anyone writes any. We use the same fictional facilities company from our Airtable to Supabase sync article, whose operations team runs clients, sites and jobs in Airtable.
What Redshift gives you that Airtable can’t
Three kinds of question come up again and again, and Airtable can’t answer any of them well.
- Questions across systems. Quoted versus invoiced per client needs Airtable jobs joined to the accounting system. In Airtable you’d have to copy invoices in, and keep them up to date.
- Questions about change. How long did urgent jobs wait before someone scheduled them? Airtable stores the current status, not when it changed. Revision history is there for people to read, not for reports to count.
- Heavy questions on lots of rows. A few years of jobs, readings or orders, grouped and filtered for a dashboard half the company opens every morning.
If none of these apply yet, you probably don’t need a warehouse. Airtable’s own interfaces or a small PostgreSQL reporting copy will do. If you already use BigQuery or Snowflake, everything below works the same way there; only the load commands change.
Buy or build
There are three realistic ways to get Airtable into Redshift. We’d always look at the first one before building anything.
| Option | Good when | Watch out for |
|---|---|---|
| Managed connector (Fivetran and similar) | You want tables in Redshift this week and the base is a reasonable size | Pricing by rows changed each month; how linked records, renamed fields and deletions are handled is their decision, not yours |
| Airbyte, self-hosted | You want a connector without per-row pricing and someone can run it | You own upgrades, monitoring and the server it runs on |
| Your own pipeline | You need daily history, field-id keys, custom checks, or connector costs have grown | Someone has to own it; the design below is what makes it cheap to run |
Most teams can start with a connector and switch later. The tables you design for reporting don’t have to change when the thing feeding them does, as long as reports read from your own tables rather than straight from the connector’s.
The pipeline we’d build
A small scheduled job, usually an AWS Lambda function on an EventBridge schedule, asks the Airtable API for records changed since its last run. Airtable can filter on LAST_MODIFIED_TIME(), so an hourly run on a quiet base is a handful of requests. The job writes the responses exactly as they came back to S3, in a folder per run.
That raw copy is the most useful part of the design. When a load fails, or you find a mapping mistake three weeks later, you fix it and replay the files. Nobody has to ask Airtable for the data again, and nothing depends on what the base looks like today.
From S3, a COPY command loads each run into a staging table, and a MERGE updates the reporting tables by Airtable record id. Redshift is built for loading files in bulk; sending it one INSERT per record is slow and puts load on the cluster that your dashboards are using. Load each run in one transaction, so a dashboard never sees half a run.
Mapping Airtable fields to Redshift columns
This is where connectors and hand-built pipelines differ most, and where reports go wrong later if nobody decides on purpose.
| Airtable field | In Redshift | Why |
|---|---|---|
| Record id | airtable_id varchar, the merge key | Names change, ids don’t |
| Linked record | A bridge table with one row per link | Links are lists, even when people only ever pick one |
| Single select | varchar, not a fixed list | Someone will add a new option one day; the load shouldn’t fail because of it |
| Multiple select | A bridge table, or SUPER if you only filter on it | Counting by tag needs rows |
| Currency, number, percent | decimal with fixed scale | Floats drift when you add up thousands of quotes |
| Date | date | No time zone to get wrong |
| Date with time, last modified | timestamptz, stored in UTC | The API returns UTC; convert in the report, not the load |
| Attachments | One row per file: name, size, type, your own copy’s S3 key | Airtable’s attachment links expire after a few hours |
| Lookup, rollup, formula | Usually skip, and rebuild in SQL | They’re computed from data you already have, and they change when the base changes |
Key the pipeline on field ids as well as record ids. The API can return fields by id, so when someone renames Scheduled for to Visit date, which will happen, the pipeline keeps working and only the label in your mapping needs updating.
Deletions and history
The Airtable REST API has no list of deleted records. The pipeline has to ask for every record id in a table, which is cheap because you can request a single field, and compare that list with Redshift. Rows that are gone get a deleted_at date instead of being removed, so last month’s report still adds up.
Put a limit on it. If a run finds that a large share of a table has disappeared, it’s far more likely that a token lost access or someone pointed the job at the wrong base than that half the jobs were deleted overnight. The run should stop and alert someone instead.
History is the other half. Once a day, copy the current state of each table into a snapshot table with the date added. It’s one INSERT … SELECT, and Redshift compresses repeated values well, so a year of daily snapshots of a few thousand jobs is small. Time from request to scheduled, jobs open at the end of each week, quotes that changed after approval: all of these become simple queries on the snapshots.
What we’d check before calling it done
- Row counts per table in Airtable and in Redshift match after every run, and the run fails loudly if they don’t.
- A total that matters to the business, such as the sum of quotes, matches too.
- Replaying an old run from S3 gives the same result as the first time.
- A renamed field, a new select option and a deleted record all go through a test run without breaking it.
- Someone gets an alert when a run fails, or when no run has happened for a few hours.
These checks are what separate a pipeline people trust from one they quietly re-check in a spreadsheet. We use the same reconciliation approach in our Airtable to PostgreSQL migration article, where the load only commits when counts and totals agree.
Is Redshift the right warehouse?
| If you… | We’d usually suggest |
|---|---|
| Already run Redshift for other data | Redshift, with the design above |
| Are on Google Cloud or use Looker Studio | BigQuery |
| Have one base and a few reports | A PostgreSQL or Supabase copy, kept up to date with a sync |
| Need an app or portal on the data as well as reports | Supabase for the app, and a warehouse later if reporting grows |
Picking a warehouse only for Airtable data is rarely worth it. The value comes from putting Airtable next to the other systems the business already reports on.
Sources
Questions we often hear
Can Airtable connect to Redshift directly?
No. Airtable has no built-in export to Redshift. You need a connector such as Fivetran or Airbyte, or a small pipeline of your own that reads the Airtable API and loads Redshift through S3.
How often can the data refresh?
As often as every few minutes, but hourly is enough for most reporting. Each run only reads records changed since the last one, so frequent runs stay cheap.
What happens to linked records and attachments?
Linked records become bridge tables keyed on Airtable record ids. Attachments should be copied to your own S3 bucket during the run, because Airtable’s attachment links expire after a few hours.
Should we move off Airtable instead?
Not necessarily. If the team works well in Airtable and only reporting is the problem, a pipeline to the warehouse fixes that without changing how anyone works. If permissions, limits or speed are the problem, a move is the better answer.
