Airtable upsert: stop creating duplicate records

Short answer: an append-mode sync creates new records every run, so a daily job gives you seven copies of every row by the end of the week. Upsert mode merges on one to three key fields instead: matching records are updated in place, and only genuinely new rows are created.

Why the duplicates appear

Most “sync to Airtable” setups default to creating records, because creating is the simple case — no matching, no schema questions, no ambiguity. Run it once and it looks perfect.

Run it on a schedule and the base doubles, then triples. By the time anyone notices, the linked records and views built on top are pointing at a mess, and deduplicating after the fact means deciding which of six near-identical rows is the real one.

How upsert works

Set the destination write mode to upsert and give it key_columns — the fields that identify a record. For every incoming row:

• A match on the key fields → that record is updated with the new values.
• No match → a new record is created.

Two constraints from Airtable’s own API, worth knowing before you design around it:

• At most three merge fields. More than that is rejected.
• Writes go 10 records per request. At the default 4 requests per second (Airtable’s own ceiling is 5), that is roughly 40 records a second — about 2,400 a minute. Fine for thousands of rows, worth planning around for hundreds of thousands.

Choosing a merge key

This is the decision that determines whether the sync is stable, and it is worth more than five minutes.

Use a stable ID from the source

The best key is an identifier the source system already guarantees is unique and never reuses:

Do not use email addresses

Tempting, and it fails in a specific way: people change them. When they do, upsert cannot match, so it creates a second record — and now you have the duplicate you were trying to prevent plus a stale one.

Email is fine as a display field. It is a poor key.

Compound keys, when a row is a fact per period

If you are keeping a daily history — spend per campaign per day, say — one row is not one entity, it is one entity per day. The key is both fields: campaign_id and date.

Get this wrong in either direction and it shows immediately: key on campaign_id alone and every day overwrites yesterday, leaving one row per campaign and no history at all. Key on nothing and you get the duplicates.

The key field must be written

An obvious trap once you have hit it: if you restrict which columns get written and the key field is not among them, there is nothing to match on.

Field types matter more here than in a spreadsheet

Airtable is typed. A number that lands in a single-line-text field will not sum, and a date that lands as text will not sort.

typecast is on by default, which lets Airtable coerce strings into select, number and date fields rather than rejecting the write. It is a convenience, not a substitute for setting the field types up correctly.

For columns your table does not have at all, unknown_fields decides the behaviour: error (the default — fail loudly, so a renamed source column does not silently drop data) or skip.

If you are unsure what a column should become, paste a sample into the Airtable field type mapper — it reads the values and suggests a type per column, plus a sensible primary field.

Setting it up

1. Add your source — Stripe, Postgres, a REST API, or a CSV at a URL.
2. Choose Airtable as the destination; pick the base and table.
3. Set the write mode to upsert.
4. Set key_columns to your stable ID — or the compound key, if a row is a fact per period.
5. Map the columns, checking the field types as you go.
6. Schedule it. Daily is right for most things; the cron generator will read your schedule back in plain English.

Testing it before you trust it

Run the job twice by hand and look at the record count. If it doubles, the mode or the key is wrong — and it is far easier to fix that on run two than on run thirty.