BaseBrainers | Airtable API Upserts: Idempotent Imports Without Duplicate Records

If you load data into Airtable from another system on a schedule, sooner or later you hit the duplicate problem: the job runs twice, or a record changes upstream, and your base gains a second copy of the same customer. The usual fix is a lookup-then-write loop, which is slow and fragile. The Airtable API has a better option: upsert. You send records plus the field(s) that identify them, and Airtable updates the match or creates a new record if none exists.

This tutorial shows how to build a repeatable import that is safe to run any number of times.

What an upsert does

An upsert is a normal update request (PATCH or PUT on the table endpoint) with an extra performUpsert object. Inside it, fieldsToMergeOn lists the field names Airtable should use to find an existing record.

No record matches: a new one is created.
Exactly one record matches: it is updated.
More than one record matches: the request is rejected, so you find out about existing duplicates rather than silently making them worse.

Because the result is the same however many times you run it, we call the job idempotent. That is the property you want for any scheduled sync.

Step 1: Pick a merge key that never changes

The merge key decides everything. Good keys are stable identifiers from the source system:

A customer ID, order number, or SKU from your ERP, CRM, or shop.
A single text or number field in Airtable that you populate only from the source.

Poor keys are things people edit or format differently: names, email addresses typed by hand, or anything with trailing spaces and mixed case. If the only identifier you have is messy, normalise it first (trim, lowercase) in your import code and store the clean value in a dedicated External ID field.

Two practical rules:

Merge fields must be ordinary, writable fields. Formula, rollup, and other computed fields cannot be written to, so build the key as a real field.
Check the current API reference for the permitted number and types of merge fields before you design around a composite key. If you need more than one field to be unique, a single concatenated External ID is often simpler.

Step 2: Prepare the table

Create a field called External ID (single line text) as the first column, and put a view filter on a copy of the table, or a simple automation, to flag blanks. Existing rows must be backfilled before the first upsert, otherwise the first run creates a second copy of everything that was already there. Export the table, add the IDs from the source, and paste them back in before you start.

Step 3: Send the request

The body looks like this, with typecast optional:

{
  "performUpsert": { "fieldsToMergeOn": ["External ID"] },
  "typecast": false,
  "records": [
    { "fields": { "External ID": "CUST-1001", "Name": "Acme Ltd", "Status": "Active" } },
    { "fields": { "External ID": "CUST-1002", "Name": "Globex", "Status": "Paused" } }
  ]
}

Send it with a PATCH to https://api.airtable.com/v0/{baseId}/{tableIdOrName} and a personal access token that has the data.records:write scope on that base. Use PATCH when you only want to change the fields you send; PUT clears any field you leave out, so be careful with it on tables that people also edit by hand.

In a script, a minimal Node version is:

const chunk = (arr, n) => Array.from({ length: Math.ceil(arr.length / n) }, (_, i) => arr.slice(i * n, i * n + n));

async function upsertAll(rows) {
  const summary = { created: 0, updated: 0 };
  for (const batch of chunk(rows, 10)) {
    const res = await fetch(`https://api.airtable.com/v0/${BASE}/${TABLE}`, {
      method: 'PATCH',
      headers: { Authorization: `Bearer ${TOKEN}`, 'Content-Type': 'application/json' },
      body: JSON.stringify({
        performUpsert: { fieldsToMergeOn: ['External ID'] },
        records: batch.map(fields => ({ fields }))
      })
    });
    if (!res.ok) throw new Error(`${res.status} ${await res.text()}`);
    const data = await res.json();
    summary.created += (data.createdRecords || []).length;
    summary.updated += (data.updatedRecords || []).length;
  }
  return summary;
}

Step 4: Batch, pace, and retry

Write requests accept at most 10 records each, so chunk your data into tens.
Airtable limits each base to about 5 requests per second. Add a short delay between batches, and on a 429 response wait and retry rather than hammering the API.
A retry after a timeout is safe precisely because the call is an upsert. That is the main payoff over plain creates.

Step 5: Read the response

The response lists the records, and it also tells you which IDs were created and which were updated (createdRecords and updatedRecords). Log those counts for every run. A sudden spike in creates on a job that normally updates is the earliest warning that your merge key has drifted.

Common traps

Blank keys. A record with an empty External ID should never be sent. Filter these out in code and report them.
Duplicates already in the table. If two existing records share a key, the upsert for that key fails. Find them with a grouped view on External ID, merge or delete one, then rerun.
Case and whitespace. cust-1001 and CUST-1001 are different strings. Normalise before sending.
Linked records. When a field links to another table, you pass record IDs or, with typecast on, primary field values. Typecast can create new select options and linked rows you did not intend, so keep it off unless you need it.
Overwriting human edits. If staff edit some columns in Airtable, only send the columns the source system owns.
Deletions. Upsert never deletes. For records removed upstream, send a Status or Last Seen value and let an automation or view handle archiving.

A simple test plan

Duplicate your base, or use a sandbox base, so you can experiment safely.
Run the job with five sample rows and confirm five creates.
Run it again unchanged and confirm zero creates and five updates.
Change one source value, run again, and confirm exactly that field changed.
Insert a deliberate duplicate key and confirm you get an error you can see in your logs.

When to get help

Upserts remove most duplicate headaches, but real integrations also deal with schema changes, large backfills, and two-way edits. If your Airtable sync is producing duplicates you cannot untangle, or you want a reviewed integration design, the BaseBrainers team can help with Airtable API integration and data migration to Airtable.