To sync Amazon product data into Airtable, look up each ASIN on SellerMagnet's /amazon-product-lookup endpoint for title, price and Best Sellers Rank, then batch-upsert the results into an Airtable table with performUpsert keyed on the ASIN - up to 10 records per call, spaced to stay under Airtable's 5-requests-per-second-per-base rate limit. Keying on the ASIN means re-running the same sync updates existing rows instead of creating duplicates.
Key Takeaways
- Airtable's Web API allows 5 requests per second per base (50/s per personal access token); exceeding it returns 429 and a 30-second wait before the next request succeeds.
- Batch writes up to 10 records per create/update call - at 5 requests/second that's up to 50 records written per second, not one API call per row.
- performUpsert with fieldsToMergeOn finds-or-creates in one call, keyed on a field you pick (the ASIN); no separate read-then-decide step is needed.
- A SellerMagnet 404 is a billed, real answer - report it, don't write an empty or placeholder Airtable row for it.
- Free and Team Airtable plans also cap total monthly calls (1,000 and 100,000); Business and Enterprise have no monthly cap but keep the same per-second limit.
What are Airtable's actual rate and batch limits?
Airtable's own documentation (support.airtable.com, checked 2026-10-17) states a flat 5 requests per second per base across every pricing tier, plus a separate 50 requests per second ceiling per personal access token across all the bases it can reach. A 429 means the limit was hit and the next request will not succeed for 30 seconds - Airtable's own troubleshooting guide recommends exponential backoff with jitter, not a fixed short retry. Monthly call caps are separate and plan-specific: the Free plan allows 1,000 calls a month with a 30-day grace period after that, the Team plan allows 100,000 before throttling to 2 requests/second, and Business/Enterprise have no monthly cap at all.
| Limit | Value | Behaviour when hit |
|---|---|---|
| Per base | 5 requests/second | 429, wait 30 s before the next request succeeds |
| Per personal access token | 50 requests/second | across every base that token can reach |
| Free plan | 1,000 calls/month | 30-day grace period, then blocked until the month resets |
| Team plan | 100,000 calls/month | throttled to 2 requests/second after the limit, not blocked |
| Business / Enterprise | no monthly call cap | still subject to the 5 req/s per-base limit |
How do I batch the sync without tripping the rate limit?
The script below looks up every ASIN on SellerMagnet first (via get_with_retry from the Python quickstart, so a 502/503 from SellerMagnet doesn't abort the batch), then slices the successful rows into groups of 10 and sends each group as one performUpsert call, spaced just over 200 ms apart to stay under 5 requests/second with margin. An Airtable 429 triggers a roughly 30-second wait and a retry of that same batch, matching Airtable's documented recovery time.
airtable_sync.py - look up on SellerMagnet, batch-upsert into Airtable
"""airtable_sync.py - pull title/BSR/price for a list of ASINs from SellerMagnet
and upsert them into an Airtable table, one row per ASIN.
Respects Airtable's documented limits (support.airtable.com/articles/7735693959
and 2111090641, retrieved 2026-10-17): 5 requests/second per base (50/s per
personal access token), batched create/update up to 10 records per request, and
a 429 means "wait 30 seconds before the next request succeeds" - exponential
backoff with jitter on 429, not a fixed sleep. performUpsert lets one PATCH
both create and update by a key field (here: ASIN) in a single batched call.
"""
from __future__ import annotations
import os
import random
import time
import requests
from retry import get_with_retry
from sellermagnet import new_session
AIRTABLE_API = "https://api.airtable.com/v0"
AIRTABLE_BATCH = 10 # Airtable's max records per create/update request
AIRTABLE_MIN_INTERVAL = 0.21 # keep requests under the 5 req/s per-base cap with margin
MARKETPLACE = "ATVPDKIKX0DER" # amazon.com
def _airtable_session() -> requests.Session:
s = requests.Session()
s.headers["Authorization"] = f"Bearer {os.environ['AIRTABLE_TOKEN']}"
s.headers["Content-Type"] = "application/json"
return s
def _airtable_upsert_batch(session: requests.Session, base_id: str, table: str,
records: list[dict], attempts: int = 4) -> dict:
"""performUpsert on one batch of <=10 records, keyed by the Asin field.
429 means wait ~30s and retry with backoff; other 4xx are not retried."""
url = f"{AIRTABLE_API}/{base_id}/{table}"
body = {"performUpsert": {"fieldsToMergeOn": ["Asin"]},
"records": [{"fields": r} for r in records]}
for attempt in range(1, attempts + 1):
resp = session.patch(url, json=body, timeout=30)
if resp.status_code == 200:
return resp.json()
if resp.status_code == 429 and attempt < attempts:
time.sleep(30 + random.uniform(0, 2)) # Airtable: wait 30s after a 429
continue
resp.raise_for_status()
raise RuntimeError(f"Airtable upsert failed after {attempts} attempts")
def sync_asins_to_airtable(sm_session, airtable_session, base_id: str, table: str,
asins: list[str]) -> dict:
"""Look up every ASIN on SellerMagnet, then upsert rows into Airtable in
batches of 10, spaced to stay under 5 requests/second per base."""
rows, failed = [], []
for asin in asins:
try:
data = sm_lookup(sm_session, asin)
rows.append({"Asin": data["asin"], "Title": data["title"], "Price": data["price"],
"Currency": data["currency"], "Bsr": data["rank"]})
except Exception as exc: # noqa: BLE001 - report per-row, never stop the batch
failed.append({"asin": asin, "error": str(exc)})
written = 0
last_call = 0.0
for i in range(0, len(rows), AIRTABLE_BATCH):
wait = AIRTABLE_MIN_INTERVAL - (time.time() - last_call)
if wait > 0:
time.sleep(wait)
batch = rows[i:i + AIRTABLE_BATCH]
_airtable_upsert_batch(airtable_session, base_id, table, batch)
last_call = time.time()
written += len(batch)
return {"looked_up": len(rows), "written": written, "failed": failed}
def sm_lookup(session, asin: str) -> dict:
"""Same fields as sellermagnet.lookup(), but via get_with_retry so a
502/503 from SellerMagnet doesn't abort the whole Airtable sync."""
p = get_with_retry(session, "amazon-product-lookup", asin=asin, marketplaceId=MARKETPLACE)["productInfo"]
price = p["buyBoxInfo"].get("price")
return {
"asin": p["asin"], "title": p["title"],
"price": price if isinstance(price, (int, float)) else None,
"currency": p["buyBoxInfo"].get("currencyCode"),
"rank": ((p.get("bestsellerRanks") or {}).get("main_category") or {}).get("rank") or None,
}
if __name__ == "__main__":
sm = new_session()
at = _airtable_session()
result = sync_asins_to_airtable(sm, at, os.environ["AIRTABLE_BASE_ID"],
os.environ.get("AIRTABLE_TABLE", "Products"),
["B0CL61F39H", "B0CLTBHXWQ", "B000000000"])
print(result)Demo run (2 known ASINs, 1 unknown - billed 404, never written)
{'looked_up': 2, 'written': 2, 'failed': [{'asin': 'B000000000', 'error': '404: Product not found'}]}What one Airtable performUpsert batch request looks like
{
"performUpsert": {"fieldsToMergeOn": ["Asin"]},
"records": [
{"fields": {"Asin": "B0CL61F39H", "Title": "PlayStation\u00ae5 console (slim)",
"Price": 444.99, "Currency": "USD", "Bsr": 31}}
]
}
Why key the upsert on the ASIN instead of overwriting by row position?
fieldsToMergeOn: ["Asin"] tells Airtable to match each incoming record to an existing row by its Asin field value, update that row if found, and create a new row otherwise - all inside the same batched call. Re-running the sync script daily then refreshes price and BSR on the existing rows instead of appending duplicate rows for ASINs you already track, and the field must actually be unique in your table for the match to behave as expected.
Frequently Asked Questions
How many Airtable records can one API call write?
Up to 10 per batched create or update request. Airtable's own guidance is to batch instead of writing one record per call, which is also how you stay under 5 requests per second per base with a few hundred rows.
What should my script do on an Airtable 429?
Wait - Airtable's documented behaviour is that a 429 means the next request will not succeed for 30 seconds, so a short retry loop just wastes calls. Back off for roughly 30 seconds with a little jitter, then retry the same batch.
Does performUpsert avoid a separate 'does this row exist' check?
Yes. performUpsert with fieldsToMergeOn finds, creates or updates records in the same batched call keyed on a field you choose (the ASIN here), so you never need a prior read to decide create versus update.
What happens to an ASIN SellerMagnet can't find?
It is reported in the sync result and simply never sent to Airtable - a billed 404 is a real answer, not a reason to write an empty or placeholder row.
Can I sync more than 10,000 rows this way?
The batched create/update endpoints used here top out effectively around 50 records/second (10 per request at 5 requests/second); Airtable's separate Sync API accepts up to 10,000 CSV rows per request for large one-off loads, which is a different endpoint not covered here.
What if fieldsToMergeOn matches more than one existing row?
The upsert fails for that record - fieldsToMergeOn (1 to 3 fields) must uniquely identify a row, so keep the ASIN field unique in your table, or the sync will report an error instead of silently picking one match.
Bottom line: look up each ASIN on SellerMagnet, keep only the successful rows, and batch-upsert them into Airtable up to 10 at a time, keyed on the ASIN, spaced to stay under Airtable's 5-requests-per-second-per-base limit. See the rate limits and retries guide for the SellerMagnet side of this same pattern, API Documentation for every field /amazon-product-lookup returns, and Code Examples for more runnable snippets.
