Reverse ETL in practice: warehouse audiences in your ESP

A pattern we see often enough to expect it: a data team has built a careful, well-tested customer model in the warehouse. Propensity scores, lifecycle stages, predicted value, all versioned and documented. Meanwhile the CRM team is building segments by hand in the ESP interface, from a different set of fields, and once a week somebody exports a CSV and uploads it.

Both teams know this is wrong. Neither has the other's context. The data team assumes the ESP can accept anything; the CRM team assumes a nightly sync means the numbers will be current when a journey fires at nine in the morning.

Reverse ETL is the piece that connects them, and it is less about the tool than about the agreements that sit around it. The tooling is mature and largely interchangeable. What goes wrong is scope, timing and ownership.

Decide what belongs in the ESP and what stays in the warehouse

The first mistake is syncing everything. If the warehouse holds four hundred attributes per customer, the temptation is to make all four hundred available in the ESP "in case someone needs them". That produces a slow, expensive sync, a profile schema nobody can read, and a set of fields whose freshness nobody can state.

Our default split is simple. A field goes to the ESP only if it is used to decide who gets a message, when they get it, or what is inside it. Everything else stays in the data warehouse, where analysis belongs.

That test removes most of the list. Lifetime value decile: yes, it drives eligibility. The eighteen intermediate features that produced the score: no. Next-best-category: yes, it drives content. The model's confidence interval: no, unless a journey branches on it.

Apply the same test to audience membership. A segment that exists only for reporting does not need to leave the warehouse. A segment that a campaign targets does. It should arrive as a boolean or a small enumerated field rather than as a separate list object, because lists are hard to diff and booleans are not.

Model the audience as one row per person with a stable key

Everything downstream depends on the join key, so decide it before you build anything. It must exist for every person you intend to message, it must never change, and it must be the same key the ESP already uses to identify a profile.

Email address is the wrong choice and the common one. People change it, the same person can hold several, and a change becomes a duplicate profile rather than an update. Use an internal customer identifier and treat email as an attribute of that identifier. If the ESP's primary key is the email address, sync the internal identifier into a dedicated field anyway, so reconciliation has something to work with.

Build one model whose grain is one row per messageable person, with the synced fields as columns. Not one model per audience. When each audience is its own query, the definitions drift, and within a quarter two campaigns will disagree about what "active" means.

This is the same discipline that makes a single customer view work without a CDP. The value is in agreed definitions maintained in one place, not in the product that moves the rows around.

Keep the transformation in your existing modelling layer rather than in the sync tool. If you already run dbt as part of an ingest and modelling stack, the audience model belongs there, with the tests and documentation the rest of the project has. Logic written inside a sync tool's query box has no tests, no version history and no reviewer.

Add tests on the audience model specifically. Not null on the key, unique on the key, accepted values on every enumerated field, and a row-count test with an expected range. That last one catches the worst failure, which is a model that silently returns almost nobody.

Sync cadence is a product decision before it is a technical one

The question is not how often the tool can sync. It is how stale a field is allowed to be at the moment a message is composed.

Work it out per field, not per sync. Lifecycle stage recalculated nightly is fine, because it changes slowly. Cart contents for an abandonment message are useless at nightly cadence and should not travel this route at all. Most reverse ETL disappointments are really someone expecting event-speed behaviour from a batch pipeline.

The split we recommend:

• Slow attributes, recalculated on a schedule: scores, deciles, lifecycle stage, predicted category. Nightly or hourly sync, batch upsert.
• Fast signals, needed within minutes: behavioural triggers, stock and price events, in-session activity. These belong on a streaming path or a direct webhook from the application, not on a warehouse round trip.
• Reference data, changing rarely: store lists, product taxonomies, translation strings. Sync on change, or hold them in the platform's own content tables.

Write the expected freshness next to each field in the documentation and surface it in the campaign brief template. A CRM manager should not have to ask an engineer how old a number is.

Plan for the failure modes before the first sync

Four things go wrong, and all four are predictable.

Rate limits. Every messaging platform limits API writes, and a full refresh of a large base will hit that limit. Sync diffs rather than snapshots: compare against the last successful run and send only changed rows. A full refresh should be a deliberate, scheduled operation, not the normal mode.

Partial failure. A sync that stops halfway leaves the ESP in a state where some people have the new value and some have the old one. Campaigns built on that field then target an incoherent audience. Make the pipeline idempotent, record a run identifier and a completion timestamp per row, and let journeys check that the last run completed before they act on it.

Deletions and suppressions. This is the one that causes real harm. If a person is removed from an audience in the warehouse, nothing tells the ESP unless you send the removal explicitly. Never model membership as "rows present in this export". Model it as a field with an explicit false value, so removal is an update rather than an absence.

Consent moving the wrong way. Consent should flow towards the warehouse for reporting and be enforced at the point of sending. Do not let a reverse ETL job write a consent flag into the ESP from a stale model. The platform's own consent state, and any suppression list attached to it, remains the authority.

Give every synced field a contract and an owner

A synced field is an interface between two teams, and it deserves the same treatment as any other interface.

For each field, record four things in one place both teams can read. The name in the warehouse and the name in the ESP, the owner, the expected freshness, and the campaigns or journeys that currently depend on it. That last column is what makes a change safe, because it tells the data team who breaks when a definition moves.

Then set two rules. Renaming or removing a field requires checking the dependency column first. And no field is added to the sync without a named campaign that will use it, which keeps the schema from growing back into the four hundred attributes you started by avoiding.

This is the part of a composable CDP approach that determines whether it works. The architecture is sound, the tools do what they claim, and the whole thing falls apart on undocumented fields whose owner left the company.

Start by listing every field currently synced into your ESP, and marking the ones no live campaign references. Removing those is a morning's work and it makes everything after it easier to reason about.