Tags: analytics commerce concept

Reverse ETL

Date: 2026-08-17

---

Pushing modelled data out of the warehouse and into the tools that act on it. It closes the loop that warehouse-first analytics leaves open — a beautifully-modelled definition of “at-risk customer” is worth nothing until it reaches the email platform that can do something about it.

---

Reverse ETL is syncing modelled tables from a data warehouse into operational tools — the reverse direction of ETL (extract, transform, load), which moves data from those tools into the warehouse.

The direction

ETL / ELT                            REVERSE ETL

sources ──────▶ warehouse            warehouse ──────▶ operational tools

Shopify, GA4, ads, support           email, ads, CRM, support desk,
  → one place to analyse               on-site personalisation
                                       → one place to ACT

The warehouse is where the definition lives; the tools are where the action happens. Without a path back, every operational system either reimplements the definition or works from a worse one — which is how three systems end up with three different ideas of who a lapsed customer is.

the problem it solves

email platform      "active customer" = opened in 90 days
ad platform         "high value"      = spent over £200 lifetime
support desk        "VIP"             = a manually maintained tag
warehouse           the real model — RFM, margin-adjusted, returns-netted
                    ← correct, and reaching nobody

What it looks like

Define the audience once, in the modelling layer, as a query:

-- models/audiences/at_risk_high_value.sql
select
    c.customer_id,
    c.email,
    c.first_name,
    c.lifetime_margin_pence,
    c.days_since_last_order,
    c.predicted_churn_score
from analytics.customers c
where c.lifetime_margin_pence > 20000          -- £200+ contribution
  and c.days_since_last_order between 90 and 180
  and c.predicted_churn_score > 0.6
  and c.marketing_consent = true               -- ← never optional
  and c.deleted_at is null

A sync tool then maps those columns onto fields in each destination and keeps them current — usually hourly or daily, with a change-detection step so only differences are sent.

The consent column is load-bearing. A reverse ETL pipeline is a machine for copying personal data into third-party systems, and consent state has to be part of the model rather than checked downstream — Consent Management, Legitimate Interest vs Consent.

The problems it creates

• The warehouse becomes production infrastructure. A model that fails at 3am used to mean a stale dashboard; now it means the email campaign doesn’t send. Data quality tests stop being hygiene and become uptime — Data Quality Monitoring
• API limits set your cadence. Most destinations rate-limit writes, so “real-time” is usually hourly at best. Design the audiences to tolerate that latency — a same-session trigger is not a reverse ETL job — Rate Limiting
• Deletes are harder than inserts. Adding someone to a segment is easy; removing them when they no longer qualify requires the sync to track prior state. A pipeline that only adds produces segments that grow forever and audiences full of people who bought last week
• Field mapping drifts. A destination’s custom field gets renamed by whoever administers that tool, and the sync fails quietly or writes to nothing
• Cost scales with rows synced, and a badly-scoped audience is expensive in both warehouse compute and destination billing

Where it earns its place

• Lifecycle and lapsed-customer campaigns driven by the real model rather than the email tool’s simpler one — Lifecycle Stages, Winback Campaigns
• Ad platform audiences — suppression lists, lookalike seeds, value-based bidding fed with actual contribution margin rather than revenue. Suppression alone often pays for the whole pipeline, by stopping spend on people who just bought — Bid Strategies and Budget Allocation
• Support context — lifetime value and order history visible to an agent without leaving their tool
• On-site personalisation driven by warehouse segments — Personalisation Tests
• Sales and account management in a business with a B2B side

When it isn’t the right tool

• Real-time behaviour. A basket-abandonment email needs an event stream, not an hourly sync — Event-Driven Architecture, Webhooks
• You don’t have a warehouse or a modelling layer. Reverse ETL is the last step of a pipeline, not a first move — Warehouse-First Analytics
• A packaged CDP already does it. Considerable overlap; running both means two systems computing segments and disagreeing — Customer Data Platforms
• One destination, one simple rule. A direct integration is less machinery

Running it safely

• Version the audience definitions with the models, reviewed like any other code — Code Review
• Test before syncing. A model that returns 400,000 rows when it usually returns 4,000 should fail rather than sync — a row-count assertion is the cheapest guard available
• Log what was sent, and when. “Why did this customer get that email” is asked regularly and is unanswerable without it
• Propagate deletion. A subject access or erasure request must reach every destination the pipeline pushed to, not just the warehouse — UK GDPR and PECR for Analytics, Data Retention
• Suppress at source. Consent, opt-out and fraud flags belong in the model so no destination can receive someone it shouldn’t

Where it interacts

• Warehouse-First Analytics — the architecture this completes; without reverse ETL a warehouse is a reporting system rather than an operational one
• Customer Data Platforms — the packaged alternative, and the composable version of a CDP is essentially warehouse plus reverse ETL
• RFM Segmentation — the kind of model most often pushed out this way
• Metric Design — the whole value depends on the definition being right and singular