Reverse ETL is the unglamorous half of a warehouse-first stack. You already load CRM, product, and billing data into the warehouse and compute things there: lead scores, lifetime value, product-qualified flags, churn risk. Those numbers are useless to marketing until they show up as properties in HubSpot, fields in Salesforce, or audiences in an ad platform. Vendor platforms sell that last mile. For one or two destinations and a modest number of fields, building it yourself is a reasonable call, as long as you build the parts that vendors spent years getting right: change detection, batching, rate limiting, and failure accounting.

This is the shape of a sync that holds up in production.

Start with a sync model, not a query

The most common mistake is pointing a script at an analytics table and pushing whatever it returns. Create a dedicated model per destination object instead, one row per record you intend to write, with exactly the columns the destination will receive.

A sync model for HubSpot contacts might have:

  • email (the match key, lowercased and trimmed)
  • lead_score, ltv_band, last_product_event_at (the fields you own)
  • row_hash, a hash of every outbound column concatenated in a fixed order

Three rules keep this model safe:

  1. One row per key. Deduplicate in SQL. If two rows share an email, the destination will see two writes in an undefined order, and your sync will flap.
  2. Only fields you own. Do not sync a field that sales reps also edit by hand unless you have agreed on which side wins. Reverse ETL quietly overwriting human edits is how these projects lose trust.
  3. Formatted for the destination. Dates as the destination expects them, enumerations matching the destination’s allowed option values, nulls handled deliberately. A HubSpot dropdown property rejects values that are not defined options, and that rejection lands at write time, not in your warehouse.

Diff against the last successful run

Pushing every row on every run burns rate limit and makes failures hard to reason about. Keep a state table in the warehouse:

create table reverse_etl_state (
  destination   text,
  object_type   text,
  record_key    text,
  row_hash      text,
  destination_id text,
  synced_at     timestamp,
  primary key (destination, object_type, record_key)
);

Each run computes three sets by joining the sync model to the state table:

  • Added: keys in the model with no state row.
  • Changed: keys where row_hash differs.
  • Removed: keys in state that left the model. Decide explicitly what removal means. Usually it means clearing your owned fields, not deleting the CRM record.

Only after the destination confirms a write do you upsert the state row with the new hash and the destination’s record ID. That ordering is the whole design: if the job dies mid-run, the unconfirmed rows are still “changed” next time and get retried. State that is written before confirmation produces silent drift, where the warehouse believes the CRM has a value it never received.

Storing destination_id pays off later. Updates by internal ID are cheaper and less ambiguous than updates by email, and you will need the ID to reconcile.

Batch, throttle, and respect the destination’s limits

Every destination publishes limits, and a reverse ETL job is usually the heaviest API consumer in the account. Read the current docs before sizing the job.

HubSpot. HubSpot’s usage guidelines set a per-app burst limit of 100 requests per 10 seconds on Free and Starter accounts and 190 per 10 seconds on Professional and Enterprise, with daily account caps of 250,000, 625,000, and 1,000,000 calls respectively for privately distributed apps. The CRM batch endpoints accept multiple records per request, including a batch upsert that matches on a unique property such as email, so a few thousand changed contacts become a few dozen calls. Note that the daily cap is per account, so your sync competes with every other integration installed there.

Salesforce. For more than a few thousand rows, use Bulk API 2.0 rather than per-record REST calls. You upload a CSV job, Salesforce processes it asynchronously, and you poll the job status and download per-row success and failure results. Upserts match on an external ID field, which you should create for this purpose rather than relying on email. Salesforce also enforces a rolling 24-hour API request allocation per org, and a naive per-record loop can spend a meaningful share of it.

A practical client wrapper does four things:

  • Groups changed rows into batches at the destination’s documented maximum.
  • Uses a token bucket sized below the burst limit, leaving headroom for other integrations.
  • On a 429, honors the Retry-After header when present and otherwise backs off exponentially with jitter.
  • Treats 5xx responses and timeouts as retryable, and 4xx validation errors as row-level failures that should not be retried as-is.

We covered rate-limit strategies across MarTech APIs in more depth in our rate limiting guide.

Handle partial failure at the row level

Batch endpoints rarely succeed or fail as a unit. A single malformed email or invalid enum value can be rejected while the rest of the batch writes, or, depending on the endpoint, can fail the whole batch. Your job needs to parse the response per record and route failures:

  • Successes update the state table.
  • Validation errors go to a reverse_etl_errors table with the key, the payload, the error message, and the run ID. They stay “changed” in the diff, so fix the data upstream and they retry automatically.
  • Whole-batch failures on a batch that contains a bad row should fall back to splitting the batch, so one bad record does not block a thousand good ones.

Alert on error rate, not error count. A steady handful of rejected rows is normal; a spike after a schema change is not.

Reconcile on a schedule

Diff-based syncs are only as accurate as the assumption that nobody else touched the data. Users edit records, other integrations write the same fields, and merges in the CRM change record IDs. Run a periodic reconciliation, weekly is common, that reads your owned fields back from the destination for a sample or for all synced records, compares them to the warehouse, and marks mismatches as changed so the next run corrects them.

Merges deserve special handling. When two CRM records merge, one ID disappears. If your state table still points at it, updates by ID will fail. Catch those not-found responses, clear the stored ID, and fall back to matching on the key.

Decide where it runs

The orchestration layer you already use for loading data is usually the right home: an Airflow or Dagster task, a dbt post-hook that triggers a job, or a scheduled container. Keep three things outside the code:

  • Credentials in the platform’s secret store, with a scoped private-app token or connected app per destination rather than an admin user’s credentials.
  • Run metadata, meaning start and end time, rows added, changed, removed, failed, and API calls used, in a table you can chart.
  • A kill switch, a config flag that stops writes without a deploy, because the first bad model change will push wrong values to every record before anyone notices.

When to stop building it yourself

The in-house version stays manageable while you have a small number of destinations, each with a handful of objects. It gets expensive when marketing wants a new destination every quarter, when ad-platform audience APIs with their own hashing and matching rules enter the picture, or when nobody owns the job after the engineer who wrote it moves on. At that point a vendor platform is buying you maintenance of API version changes, not just a UI.

Until then, the build is mostly discipline: a sync model per object, a state table written only on confirmation, batching under published limits, and per-row error handling. Get those four right and the pipeline will tell you exactly what it wrote, which is more than many purchased tools do.