Skip to content

Reverse ETL and activation

Sending results computed in the data platform, such as customer segments or scores, back into the operational tools where people and systems act on them: CRM, marketing, support and advertising platforms.

Before reading

Data APIs and the backend-for-frontend pattern

Why it matters

The platform is where data from many sources is combined: orders, payments, clicks and support tickets together. The results, such as which customers are likely to churn or which are high value, are only useful if they reach the people who act on them, and those people work in their own tools. A sales rep looks at the CRM, not a dashboard. Activation closes that loop.

How it works

Reverse ETL is the name for the pipeline that does this: it reads a table or query from the platform and writes the rows into an operational tool through that tool's API. The term reflects the direction: ordinary ETL brings data from operational systems into the platform; reverse ETL sends results back.

A sync has four parts. A source model: a table in the platform with one row per record to send, such as one row per customer with their churn score. A destination: an object in the target tool, such as the contact record in the CRM. A mapping: which column goes to which field, and which column identifies the record in the target, such as email or the CRM's own contact ID. A schedule and mode: how often to sync, and whether to insert, update, or upsert.

Most tools sync only changes. They keep the state of the last sync and compare it with the current model output, sending only rows that were added, changed or removed. This matters, because operational APIs have rate limits, and sending a million unchanged rows every hour wastes the limit and can trigger the tool's automations needlessly.

There are two directions of control. In push, the platform side, through a reverse ETL tool or a pipeline, writes into the target on a schedule. In pull, the target fetches data when it needs it, through a data API or a connector the target supports; some marketing and CRM platforms can read directly from warehouses and lakehouses. Pull avoids keeping two copies in sync but puts the target's query load on the platform, which then needs a serving path suited to it.

Dedicated tools, such as Hightouch and Fivetran Activations (formerly Census), provide connectors to hundreds of destinations, change tracking and monitoring. Simple cases can be built as ordinary pipeline steps.

flowchart TB
  M[(Platform model:<br/>customer_churn_score)] --> D[Diff against<br/>last sync]
  D -->|changed rows only| MAP[Map columns<br/>to CRM fields]
  MAP -->|upsert by contact ID,<br/>within rate limits| CRM[CRM contact:<br/>churn_risk field]
  CRM --> A[Rep sees risk,<br/>CRM workflow<br/>opens a task]

Activation creates new responsibilities. The platform now writes into systems that drive real actions: emails sent, calls made, ads targeted. A wrong model output or a broken join can change thousands of customer records within minutes. Personal data leaves the platform into tools with their own access rules, so classification and consent must be respected in what is sent. And the target becomes a consumer with its own contract: field names and meanings the platform must keep stable.

Key terms

  • Reverse ETL: syncing platform results into operational tools.
  • Activation: making data usable where actions happen.
  • Sync: a configured transfer from a source model to a destination object.
  • Identifier mapping: the field used to match a row to a record in the target.
  • Change-based sync: sending only rows that changed since the last sync.
  • Rate limit: the maximum number of API calls a target allows per period.

Example

The shop computes a daily churn score per customer. The model:

-- shop.customer_churn_score, rebuilt daily
SELECT c.crm_contact_id, s.churn_score, s.churn_band, s.scored_at
FROM shop.ml_churn_scores AS s
JOIN shop.clean_customers AS c ON c.customer_id = s.customer_id
WHERE c.is_crm_contact_allowed = true;   -- approved use: account management

The sync, configured in the reverse ETL tool: destination CRM contacts; match on the CRM contact ID stored in clean_customers; map churn_band to the CRM field Churn risk; upsert daily after the scoring job succeeds (normally by 07:00); only changed rows. On a typical day 3,000 of 200,000 customers change band, so 3,000 updates are sent. A CRM workflow creates a call task for the account manager when Churn risk becomes high.

One day the scoring job fails silently and every score is null. A check on the model blocks the sync when any band is null or more than 5% of bands change from the previous day, so 200,000 CRM records are not overwritten.

Pitfalls

  • No guard on volume. A broken model overwrites every record in the target.
  • Syncing everything every time. Rate limits are exhausted and target automations fire for nothing.
  • Ignoring consent and classification. Personal data reaches tools and uses it was not approved for.
  • Weak identifiers. Matching on names or unverified emails updates the wrong records.
  • Hidden consumers. Target fields that other teams' workflows rely on change without notice; treat them as a contract.

Learn more

  • Hightouch, What is reverse ETL: https://hightouch.com/blog/reverse-etl
  • Fivetran Activations documentation (formerly Census): https://fivetran.com/docs/activations

Data contracts · PII and data classification · The ML lifecycle and MLOps · Observability and SLOs