What problem does Census solve that a normal ETL pipeline does not?
answer
- the pipeline runs the other way
- warehouse is the source of truth
- modeled tables pushed into Salesforce or HubSpot
- field mappings plus a unique identifier
- only changed rows go over the wire
basics
~20 sCensus is a reverse-ETL tool: it reads modeled tables out of your warehouse and writes them into operational SaaS apps like Salesforce or HubSpot. ETL fills the warehouse; reverse ETL pushes its answers back into the tools people work in.
solid answer
~40 sETL and ELT pipelines move data **into** the warehouse. Census moves it back **out**: you point it at a modeled table or a dbt model, map columns to fields on a destination object (a Salesforce Contact, a HubSpot Company, an ad-platform audience), tell it which column is the unique identifier, and it keeps the destination in step with the model on a schedule or after a dbt run. The value is that business logic — a churn score, a product-qualified-lead flag, a lifetime-value bucket — stays defined once, in version-controlled SQL, instead of being re-implemented as a CRM formula field or a cron script. Census diffs the model against what it sent last time and pushes only changed rows, because destinations bill and throttle you per API call.
code
sql · 12 lines-- the warehouse model a reverse-ETL sync reads from
select
u.email as email, -- unique identifier
u.salesforce_contact_id as sfdc_id,
s.churn_risk_score as churn_score,
case when s.churn_risk_score > 0.7
then 'At Risk' else 'Healthy' end as health_status,
b.mrr_usd as mrr
from analytics.dim_users u
join analytics.fct_churn_scores s on s.user_id = u.user_id
join analytics.fct_billing b on b.user_id = u.user_id
where u.is_activego deeper
Be ready to state the direction plainly: data leaves the warehouse and lands in a SaaS tool. Name one concrete destination and one concrete use case, such as pushing a churn score onto a Salesforce contact.
Explain the moving parts — model, unique identifier, field mappings, sync behavior, schedule — and why only changed rows are sent. Interviewers expect you to connect diff-based sending to destination API rate limits.
Show you have run one: talk about per-record rejections, throttling, tying the sync trigger to the upstream transformation job, and monitoring rejection counts rather than just sync success.
Own the argument for centralizing business definitions in the warehouse instead of scattering them across SaaS tools, and be candid about what that centralization costs in ownership disputes and vendor lock-in.
## The direction of travel A conventional pipeline is *inbound*: connectors, CDC streams and file loads fill a warehouse so analysts can query it. Reverse ETL is *outbound*: it takes a table that already lives in the warehouse and writes its rows into the operational systems where humans and machines act — the CRM a salesperson lives in, the support desk, the marketing-automation tool, the ad platform's custom-audience API. Census is one of the two best-known products in that category. ## Why the warehouse is the right source By the time a "churn risk" score or a "product-qualified lead" flag exists, it usually exists because someone joined product events, billing rows and CRM records in the warehouse and modeled the result, very often in dbt. That model is version-controlled, tested and reproducible. If you re-implement the same logic as a Salesforce formula field, a Zapier rule and a marketing-tool filter, you now have four definitions that will drift, and no one can tell you which is right. Reverse ETL keeps the definition in one place and treats the SaaS tool as a *cache* of the warehouse's answer rather than as a second brain. ## What a Census sync is made of - **A source** — the warehouse itself (Snowflake, BigQuery, Redshift, Databricks, Postgres and similar). - **A model** — a SQL query, a table or view, or a dbt model. Rows are records; columns are fields. - **A destination object** — a Salesforce Contact, a HubSpot Company, a customer list on an ad platform, a user profile in a messaging tool. - **A unique identifier mapping** — the column that identifies the same record on both sides (an email, an external id, a CRM record id). Without it there is no way to decide whether a row is an update or an insert, and no way to be idempotent across runs. - **Field mappings** — model column to destination field, declared one at a time. A field you do not map is a field Census never writes. - **A sync behavior** — upsert-shaped ("Update or Create"), update-only, create-only, mirror (which also removes or archives destination records that have left the model), and append/send shapes for event-like destinations. - **A schedule or trigger** — on a cadence, after an upstream dbt job finishes, or fired through the API so the sync runs only once the model is actually fresh. ## Change detection Census does not push the whole model every run. It compares the current model output against the state it recorded for the previous run and sends only the rows that are new or changed. That is not a performance nicety: destination APIs are rate-limited and often metered, so a naive "send everything hourly" integration exhausts an org's daily API allowance and gets throttled. Diffing is what makes a frequent schedule affordable. ## What it is not It is not an ingestion tool — nothing about Census fills the warehouse. It is not an iPaaS or an event bus: it is a batch, warehouse-first, one-direction sync, so the freshest a destination can be is the freshness of the model plus the sync cadence. And it is not bidirectional. If you also need CRM edits reflected in the warehouse, that is a separate inbound pipeline, and the two together form a loop you have to reason about deliberately. ## Failure modes worth naming Four come up constantly. **Rate limits** — the destination throttles you, and the sync stretches or fails. **Rejected records** — a picklist value that does not exist in the CRM, a required field left unmapped, a malformed email; these fail per record, not per sync, and you need to be watching the rejection count, not just the green checkmark. **Latency floor** — stakeholders ask for "real time" and get model-refresh plus schedule. **Ownership conflict** — a human edits a field in the destination that the warehouse also writes, and the next sync overwrites them. ## How to answer in an interview Lead with the direction of travel and the one-definition argument, then show you know the mechanics: model, unique identifier, field mappings, sync behavior, diff-based change detection, schedule tied to upstream freshness. Finish with the operational reality — rate limits, per-record rejections, and the fact that the warehouse becoming the writer means someone must decide, field by field, who is allowed to own each value.
- Why does a reverse-ETL sync insist on a unique identifier column in the model?It is the join key between the warehouse row and the destination record. Without it the tool cannot tell an update from an insert, cannot be idempotent across reruns, and would create duplicate contacts every time the sync fires. It is usually an email, an external id, or the destination's own record id carried back into the warehouse by an inbound pipeline.
- How should a reverse-ETL sync be scheduled relative to the dbt job that builds its model?Trigger it after the model is rebuilt rather than on an independent clock. A sync on a fixed hourly schedule will sometimes read a half-built or stale table and push yesterday's answer with today's timestamp. Chaining sync to job completion also means a failed transformation blocks the push instead of propagating bad values into the CRM.
- What is the practical latency floor for data arriving in a destination via reverse ETL?Model freshness plus sync cadence plus the destination's own ingestion lag. If the underlying table rebuilds hourly and the sync runs after it, sub-hour freshness is not achievable no matter how often you schedule the sync. Genuinely real-time activation needs event streaming, not a warehouse-batch tool.
Think of the warehouse as the system of record and the CRM as a cache of it: reverse ETL is the cache-fill job, and anything written into the cache by hand is at risk of being refreshed away.
saying these in an interview costs you the question
- Calling it just another ETL tool that loads the warehouse
- Assuming the sync is bidirectional and merges destination edits
- Claiming it delivers real-time data regardless of model freshness
- Thinking every row is pushed on every run
- Forgetting a unique identifier is required to avoid duplicate records