Warehouse & reverse-ETL

A guide that stops at “sync your warehouse to Salesforce” is a 2022 guide. That part is solved plumbing now. The interesting work moved up the stack: resolving identity into a golden record before you activate, and deciding what the warehouse should decide versus what a downstream tool should. Reverse-ETL is the pipe underneath that, and it matters most when you stop treating it like a dumb one.

The proof that this is where the money is: Hightouch raised a $150M Series D at a $2.75B valuation in April 2026 building warehouse-native identity resolution and AI Decisioning on top of the reverse-ETL pipe, and Gartner named the composable CDP a Leader in its January 2026 Magic Quadrant. Meanwhile Census stopped existing as a standalone product, folded into Fivetran as “Activations” in the May 2025 acquisition. The category consolidated around one idea: the warehouse is the source of truth, and everything else rents activation muscle. This guide ships the build order that idea implies, the dbt layering that keeps it from rotting, and the SQL and sync config you deploy.

Warehouse as the base, composable CDP as the shape

Point-to-point was the old failure: every tool synced to every other tool, each holding a slightly different version of the truth, and “product-qualified lead” meant three different things in Salesforce, the sequencer, and a spreadsheet. With N tools you own N-squared integrations and N-squared arguments about whose number is right. Warehouse-first inverts it. Land everything in Snowflake or BigQuery or Databricks, model it once, and push the modeled tables back out. Your PQL definition becomes one dbt model, tested and version-controlled, and every tool that needs the flag reads the same value.

The composable CDP is this pattern with a name: the activation layer sits on top of your warehouse and never stores a second copy of your data. That is the whole point. Packaged CDPs failed by becoming a second source of truth that took six-plus months to implement and only understood behavioral events. The composable version keeps the warehouse as the base and rents only the activation muscle. You already paid for the warehouse; the CDP is a thin layer that reads it and writes to your tools, not a new database you have to keep in sync with the one you already trust.

Sources

ELT (Fivetran)

Warehouse

Transform (dbt)

Identity resolution

Reverse-ETL

CRM / ad tools

Here is the same shape as a blueprint, so the one-writer discipline is visible: data fans in, gets modeled and resolved in the middle, and fans back out to tools that only ever read.

Identity resolution comes before activation

This is the step a dumb-pipe setup skips, and it is where the center of gravity moved. Before you write anything back, you resolve identity: stitch the same human or account across product logins, CRM records, and marketing events into one golden record, then apply survivorship rules to pick the winning value per field. Deterministic matching keys on shared identifiers like email or account ID; probabilistic matching fills the gaps by scoring likely matches on fuzzy signals like company name plus domain. Activate before you resolve and you sync the same person three times under three IDs, and every downstream tool inherits the split.

The cost of skipping it is not abstract. Without matching, 15 to 25% of records misroute, and B2B data decays around 30% a year (ZoomInfo), so the split compounds. A golden record built on a stable key is the thing that stops one customer with three product workspaces from becoming three accounts in Salesforce, three owners, and three renewal forecasts. Survivorship is the tiebreak logic: when billing says the ARR is $80K and the CRM says $60K, which wins, and why. Write that rule down as SQL, not as tribal knowledge, or every rep who touches the account will overwrite it with their own guess. If you are modeling the objects those records land in, CRM data modeling covers the Lead-versus-Contact architecture decision that sits next to this one.

Here is the shape of a deterministic-plus-probabilistic resolve, written as one intermediate model:

-- int_account_identity: collapse many source rows into one golden account
WITH src AS (
  SELECT domain, company_name, account_id, arr, updated_at, 'crm'     AS source FROM stg_crm_accounts
  UNION ALL
  SELECT domain, company_name, NULL,       mrr*12, updated_at, 'billing' AS source FROM stg_billing_accounts
),
keyed AS (
  SELECT *,
    -- deterministic key first, fall back to normalized domain
    COALESCE(account_id, LOWER(REGEXP_REPLACE(domain, '^www\\.', ''))) AS match_key
  FROM src
)
SELECT
  match_key,
  -- survivorship: CRM name wins, billing ARR wins, latest updated_at wins
  MAX(CASE WHEN source = 'crm'     THEN company_name END) AS company_name,
  MAX(CASE WHEN source = 'billing' THEN arr          END) AS arr,
  MAX(updated_at)                                         AS last_seen
FROM keyed
GROUP BY match_key

The survivorship rules live in the MAX(CASE WHEN ...) lines, in plain sight, version-controlled, and testable. That is the artifact. Everything downstream reads match_key and inherits one truth per account.

dbt layering keeps the models from rotting

The transformation layer sprawls into unmaintainable spaghetti if you let every model reference every other one. dbt’s convention exists to prevent that: stg_ staging models clean one source each and rename fields, intermediate models handle the joins and heavy logic, and marts are the final business-grade tables named as plural nouns. Reverse-ETL reads from marts, never from a raw source or a staging model. When a synced score is wrong, that layering is what lets you trace it back to one model instead of untangling a web.

	Point-to-point (2022)	Warehouse-first (2026)
Integrations to own	N-squared (every tool to every tool)	N (each tool to the warehouse)
PQL definition	Three versions, three tools, no winner	One dbt model, tested, version-controlled
Copies of the truth	One per tool, all drifting	One in the warehouse; tools read it
Tracing a wrong number	A day of untangling	One model, one commit, one test
Identity	Split across tools, never reconciled	Resolved once into a golden record

The incremental math is the part that saves your API budget. Writing 800 changed rows instead of 12,000 every run is not a nicety; on Salesforce, daily API calls are capped at 100,000 plus 1,000 per license, and a full-table overwrite on a few large marts will eat that before lunch. If your sync writes 100% of rows every run, your change-detection is misconfigured, and you are paying API budget to rewrite values that did not move.

Rows written per runAPI calls (batched /200)

View as table

Stage	Value
Full-table overwrite	12,000
Incremental (changed)	800

Which tool for which job

	Reverse-ETL	iPaaS / webhooks
Handles	State: batch sync of modeled attributes	Events: real-time, record-by-record reactions
Use when	Logic needs data the CRM lacks and you want one governed definition	"When X happens, do Y now"
Latency	Standard 15-min syncs; Enterprise down to 1-min	Seconds
Native CRM formula	Right when the inputs already live in the CRM	not applicable

The two-vendor reality after the Census acquisition is Hightouch plus Fivetran Activations. Both bill on monthly active rows (MAR), the count of distinct records that changed and synced in a month, which is the metric that rewards incremental sync and punishes full overwrites. The pricing shape matters when you plan:

Tier	Sync frequency	Rough price	MAR included
Fivetran Activations free	Standard batch	$0	3,500 MAR
Fivetran Activations typical GTM	15-min	~$200/mo	scales with MAR
Hightouch standard	15-min	usage-based	scales with MAR
Enterprise (either)	down to 1-min	custom	high-volume MAR

Source: vendor pricing pages, 2026; MAR bands move, the model does not. Sub-minute reverse-ETL is rarely worth the cost or the API strain; if you genuinely need per-second reaction, that is an event job for a webhook, not a sync. The rule of thumb: reverse-ETL for state (the health tier, the account score), webhooks for events (the pricing-page revisit, the trial signup).

Here’s how I’d build it

1. 1

   1. Land raw data

   ELT tool (Fivetran, Airbyte), one schema per source. Do not transform on the way in; land it raw so you can re-model without re-extracting.

2. 2

   2. Model in dbt layers

   stg_ cleans one source each, intermediate does the joins, marts are the business tables. Define each concept exactly once. Reverse-ETL reads marts only.

3. 3

   3. Resolve identity

   Deterministic key first, probabilistic fill second, survivorship rules in SQL. One golden record per account before anything gets synced. This is the step you cannot skip.

4. 4

   4. Carry a stable join key

   The CRM record ID (sfdc_account_id) must live in the mart, or the sync has nothing to match on and creates duplicates.

5. 5

   5. Configure incremental sync

   Change-detection on a reliable updated_at or a hash of the synced columns. Changed rows only, never full overwrites on slowly-changing data.

6. 6

   6. Handle destination limits

   Batch to the API page size (Salesforce composite /200), back off on HTTP 429, and surface rejected rows to a table someone reads.

7. 7

   7. Monitor writeback

   A sync that suddenly writes 0 or all rows is a bug. Writeback has no undo, so alert on row-count anomalies before they hit production.

Order is load-bearing. Skip step 3 and every later step spreads the ambiguity instead of the truth. Skip step 5 and step 6 becomes a fire drill the first time a mart grows past your API budget. Build it in this order and each step is testable before the next depends on it.

Where I would start

Start by writing the identity-resolution model, not the sync config. If you cannot produce one golden record per account today, activating anything spreads the ambiguity into every tool that reads it, and you will spend the next quarter reconciling duplicates you created at machine speed. Write the survivorship rules as SQL, test the mart for join-key uniqueness, then wire the incremental sync on top. The plumbing is the easy part now; the model and the resolution are where the payoff lives. Once one golden mart syncs incrementally to one CRM object, the pattern repeats for every attribute the business asks for next, and none of them need a new integration.