A nightly Hightouch sync into a CRM hits API rate limits and rejects rows — how do you diagnose it?

## Why reverse ETL is rate-limit bound A warehouse can produce ten million rows in seconds; a SaaS API will accept some bounded number of records per unit time, often shared across every integration your company has pointed at that tenant. Reverse ETL is therefore never a throughput problem on the read side and almost always one on the write side. Every design decision — diffing, batching, field mapping, scheduling — exists to keep the number of destination API calls proportional to *what actually changed*, not to how big your model is. ## Step 1: read the run, not the dashboard A sync run gives you three numbers that answer most questions: rows queried, rows the diff considered changed, and rows the destination rejected. Alongside that, Hightouch surfaces per-row errors carrying the destination's own message. Sort those messages by frequency, because they split into two very different diagnoses. **Validation rejections** name a field: an invalid email format, a missing required field, a picklist value the CRM does not recognise, a string longer than the field allows. These are a data-quality problem in the model, and the fix belongs upstream — filter the offending rows out, or clean them in the warehouse so the destination never sees them. **Quota rejections** name the org or the API, not a field. They mean the CRM stopped accepting calls. No amount of retrying fixes that; you have to send less or send it at a different time. ## Step 2: find out why the run was so large If a model of ten million rows reports nine million changed on a nightly run, the diff is not lying — something in the row genuinely differs from last night. The most common cause is a **volatile column**: `current_timestamp as synced_at`, a score recomputed with a random tiebreak, a `days_since_signup` integer that increments daily, a timestamp with sub-second precision. Every such column makes the row's value differ, so every row is "changed" and every row becomes an API call. The fix is to make the model's output stable between runs: drop columns the destination does not use, round timestamps to the day if the destination only needs a date, and compute derived values at query time in the destination rather than shipping a daily-changing number. A second cause is an unstable primary key — if the key is derived from something that changes, every row looks removed and re-added. ## Step 3: reduce the payload honestly Three levers, in order of leverage: 1. **Filter the model.** Sales does not need every user who ever visited; sync the records the CRM actually acts on. A `where` clause is the cheapest optimisation in the pipeline. 2. **Map fewer fields.** Some destination APIs charge a call per record regardless of width, but narrower payloads reduce validation-rejection surface and make change detection less twitchy. 3. **Split the sync.** One sync for the high-churn attributes on a frequent schedule, another for slow-moving attributes daily. That way the expensive object is only touched when the fast-moving fields actually move. ## Step 4: respect the destination's budget The API quota belongs to the destination tenant, not to your sync. Other integrations, an ETL tool pulling data back out, and human users in the UI all draw from it. Practical measures: move the sync off the hour when everything else runs; stagger multiple syncs against the same tenant rather than firing them together; use the destination's bulk or batch endpoint where the connector supports it, since a batched call costs one unit for many records; and avoid running a full resync during business hours — a full resync ignores the stored diff state and re-sends every row, which is exactly the spike you are trying to avoid. ## Step 5: make rejections visible Rejected rows are silent data loss with a friendly UI. Treat them like a dead-letter queue: alert when the rejection rate for a run crosses a threshold, route the rejected records and their error messages somewhere reviewable, and fix the top error class rather than retrying blindly. A sync that has quietly rejected two percent of rows for a month has left a CRM full of stale records that everyone believes is fresh. ## Prevention Before enabling any new sync, estimate its steady-state daily change volume from the warehouse — count how many rows differ day over day — and compare it with the destination's documented limits. Then watch the first week of runs. A sync whose changed-row count is a stable small fraction of the model is healthy; one whose changed-row count equals the model size will fail the moment the model grows.