Reverse ETL with Census by Fivetran: Syncing Warehouse Models Back to Salesforce and HubSpot

Most data teams finish the hard part and then stop one step short. The pipelines land raw data, dbt turns it into a clean dim_account with a churn score, a product-qualified-lead flag and a lifetime value number — and then that model sits in Snowflake where exactly four people can query it. The account executive who needs to know that Acme Corp's usage dropped 60% last month is looking at Salesforce, and Salesforce knows nothing.

Reverse ETL closes that loop: it reads a table in your warehouse and writes it into the operational tools people actually work in. Fivetran acquired Census in 2024, so if you already run Fivetran the tooling is on the same platform and, increasingly, the same billing relationship. This tutorial walks through a first working sync end to end, and spends most of its length on the parts that go wrong in production rather than on the happy path.

When reverse ETL is the right answer

It is worth being honest about the boundaries first, because reverse ETL is over-sold.

Good fits:

• Enriching CRM records with warehouse-computed attributes — health scores, usage tiers, expansion signals, support ticket counts.
• Building audiences in ad platforms or marketing tools from warehouse segments, so the definition of "enterprise trial user" lives in one place.
• Pushing account-level entitlements or plan data into support tooling so agents see context.

Bad fits:

• Anything that needs to happen in under a minute. Reverse ETL is batch, typically 15 minutes to hourly. Use an event stream for real-time.
• Two-way state where both systems edit the same field. Reverse ETL is one-directional by design; simulating bidirectional sync with it produces flapping records.
• Replacing an application backend. If the destination system is the source of truth for a field, do not overwrite it from the warehouse.

If your use case is in the second list, stop here and save yourself a quarter.

Step 1: Model the entity before you sync it

The single biggest predictor of a smooth reverse ETL project is that the model you sync is purpose-built for syncing. Do not point Census at fct_events and try to reshape it in the mapping UI.

Build a dedicated model — one row per destination object, columns named close to what the destination expects:

-- models/activation/crm_account_sync.sql
{{ config(materialized='table') }}

select
    a.salesforce_account_id            as sfdc_id,
    a.account_id                       as warehouse_account_id,
    a.account_name,
    coalesce(u.active_users_28d, 0)    as active_users_28d,
    coalesce(u.usage_trend_pct, 0)     as usage_trend_pct,
    h.health_score,
    h.health_band,                       -- 'green' | 'amber' | 'red'
    r.arr_usd,
    r.renewal_date,
    case when h.health_band = 'red' and r.renewal_date <= current_date + 90
         then true else false end      as churn_risk_flag,
    current_timestamp()                as synced_computed_at
from {{ ref('dim_account') }} a
left join {{ ref('fct_account_usage') }} u using (account_id)
left join {{ ref('fct_account_health') }} h using (account_id)
left join {{ ref('fct_account_revenue') }} r using (account_id)
where a.is_active
  and a.salesforce_account_id is not null

Three deliberate choices in there:

1. One row per Salesforce account, guaranteed. Add a dbt uniqueness test on sfdc_id. A duplicate identifier in a reverse ETL source is not a warning — it is two writes racing each other on the same record, and the last one wins nondeterministically.
2. coalesce on numeric fields. A null arriving where the destination previously had a value will, depending on your sync behaviour, blank out a field a human was reading. Decide null semantics explicitly rather than discovering them.
3. Only rows we intend to manage. The where clause is the sync's scope. Rows that fall out of the model stop being updated; they are not deleted from the destination. That is usually what you want, but know it.

Step 2: Pick the identifier, carefully

Every sync needs a way to match a warehouse row to a destination record. In rough order of reliability:

1. Destination primary key (sfdc_id, HubSpot object ID). Unambiguous. Use it whenever you have it — which you do, if Fivetran is already replicating Salesforce into the warehouse.
2. A dedicated external ID field you created on the destination object (Warehouse_Account_Id__c). Excellent: lets you upsert records that do not exist yet.
3. Email or domain. Fragile. Emails change, people share aliases, and info@ addresses match dozens of accounts.

If you are creating records and not only updating them, option 2 is worth the twenty minutes it takes to add a custom field. It makes the sync idempotent: re-running it never produces duplicates, no matter how the destination has been edited in between.

Step 3: Choose the sync behaviour

Census offers several behaviours per sync; the choice has real consequences.

Behaviour	What it does	Use when
Update Only	Writes to matched records; never creates	The destination owns record creation. Start here.
Upsert	Updates matches, creates the rest	The warehouse is the source of truth for the object
Create Only	Only inserts unmatched rows	Append-only objects, e.g. logging events
Mirror	Makes destination match source, including removals	Audiences and lists — rarely for core CRM objects

Start every new sync in Update Only. It is the behaviour with the smallest blast radius: at worst you write a wrong value to an existing record, which is recoverable. Upsert on a bad identifier can generate thousands of duplicate accounts in a CRM, and cleaning that up is a week of somebody's life.

Step 4: Map fields, and protect the ones humans own

Field mapping is mostly mechanical, but three rules save pain:

• Never map to a field a human edits. If reps type into Description, do not sync into Description. Create warehouse-owned custom fields with an obvious prefix — WH_Health_Score__c, WH_Usage_Trend__c — so it is visually clear who owns what. This one convention prevents most reverse ETL political incidents.
• Match types deliberately. Picklists reject values that are not in the list. If health_band can be green/amber/red, those exact strings must exist as picklist values, lowercase and all.
• Sync a timestamp. Include synced_computed_at mapped to a WH_Last_Synced__c field. When someone asks whether a number is stale, the answer is on the record instead of in a support thread.

Step 5: Schedule it against reality

The temptation is to sync every 15 minutes. Resist it. The right schedule is derived from two facts: how often the underlying data actually changes, and destination API limits.

Salesforce orgs have a daily API request allocation. Census batches — typically 200 records per Bulk API call — but a sync of 200,000 accounts still costs real requests, and it competes with every integration in the org. A sync that runs hourly against a model that updates once a day after your dbt run burns twenty-three runs' worth of quota for nothing.

Better: trigger the sync when the data changes. If your dbt job runs at 06:00, chain the sync to the end of that job rather than putting it on a clock. Census supports dbt Cloud triggers and API-based triggering, and since the Fivetran/dbt merger the orchestration story on the platform is converging — see our post on what the merger changes for your pipelines.

A simple API trigger at the end of a dbt run:

curl -s -X POST \
  -H "Authorization: Bearer $CENSUS_API_TOKEN" \
  "https://app.getcensus.com/api/v1/syncs/${SYNC_ID}/trigger"

Step 6: Expect rejected records, and route them somewhere

This is the step teams skip, and then the sync quietly half-works for months.

Destination systems reject rows for reasons that have nothing to do with your data quality: a validation rule requires a field you did not populate, a required picklist value is missing, a record is locked by an approval process, a trigger throws, the record was deleted last Tuesday. Census reports these as rejected records with the destination's error message attached.

Do two things:

1. Enable sync failure alerting to a channel someone reads — Slack or PagerDuty, not email that goes to a distribution list. Alert on partial failures too, not only total failures. A sync that succeeds with 4% rejections looks green on a dashboard and is silently wrong for 8,000 accounts.
2. Write rejections back to the warehouse. Census can log sync results to a table. Once they are in the warehouse you can build a rejection-rate-by-reason view alongside the pipeline health views described in our Platform Connector observability tutorial, and treat activation failures as a first-class data quality metric.

A useful triage query once results are landing:

select
    sync_id,
    error_message,
    count(*) as records,
    max(completed_at) as last_seen
from census_results.sync_records
where status = 'rejected'
  and completed_at >= current_date - 7
group by 1, 2
order by records desc
limit 20

Almost always, three error messages account for 90% of rejections, and each is a ten-minute fix.

Step 7: Watch the cost of the round trip

Reverse ETL charges on the records it syncs. Two habits keep this sane.

Sync only changed rows. Census detects changes by diffing against its own tracking state, but you can help it: filter the model to rows whose payload actually moved.

where hash(active_users_28d, usage_trend_pct, health_band, arr_usd) 
      is distinct from previous_payload_hash

Do not sync columns nobody uses. Every extra mapped field is more payload, more API weight, and one more thing to break on a validation rule. Ship five fields people asked for, not forty you had lying around.

And remember the other half of the round trip: the data you are pushing into Salesforce will come back into your warehouse on the next Fivetran Salesforce sync, where those updates count towards monthly active rows. Writing a health score to 200,000 accounts daily makes 200,000 Salesforce rows active every day. Deselect warehouse-owned fields from the inbound connector's column selection where you can, and read our connection-level MAR guide before scaling a sync up.

Step 8: Roll out to humans, not just to the API

A technically perfect sync that nobody trusts is a failed project. What works:

• Pilot on one team and one field. Ship the health score to one sales pod. Ask them in two weeks whether it matched reality.
• Publish the definition. Every warehouse-owned field needs a one-paragraph description in the CRM field help text, saying what it means and how often it updates. Reps who do not know what a number means will ignore it.
• Put the sync in a dashboard the business can see. Last run, rows updated, rows rejected. Trust comes from visibility, not from accuracy alone.

A first-week checklist

1. Build a dedicated *_sync model, one row per destination record, with a uniqueness test on the identifier.
2. Add an external ID field on the destination object.
3. Create the sync in Update Only, mapped to prefixed warehouse-owned fields.
4. Dry run against a sandbox or a 50-row filtered model.
5. Turn on failure and partial-failure alerting to a real channel.
6. Trigger the sync from the end of your dbt run, not on a clock.
7. Land sync results back in the warehouse and review rejections after a week.
8. Only then widen the scope, add fields, or switch to Upsert.

Reverse ETL is not technically difficult. It is an exercise in blast-radius management: you are writing into systems where people work, and mistakes are visible to the whole company within an hour. Do it narrowly and observably and it becomes the most valued thing the data team ships all year.

If you want help designing the activation layer — models, identity resolution, sync governance and the cost implications of the round trip — that is exactly what our reverse ETL and data activation and Fivetran + dbt transformations practices do. Get in touch with the destinations you are trying to light up.