Salesforce to ClickHouse: How to Move Your Data
Move Salesforce into ClickHouse with Airbyte. Why a successful sync can load fewer records, ReplacingMergeTree duplicates, and using FINAL for pipeline totals.
Moving Salesforce into ClickHouse is what teams do when CRM reporting has outgrown Salesforce's own dashboards and a general-purpose warehouse feels like more machinery than the problem needs. ClickHouse is fast, cheap to run and very good at the aggregations pipeline analysis invites.
This guide covers the managed path with Airbyte. Nearly all the constraints are on Salesforce's side, and one behaviour matters more than any setting: when the connector reaches your org's daily API limit it does not fail, it stops early and reports success.
Salesforce to ClickHouse at a glance:
Why move data from Salesforce to ClickHouse?
Two situations account for most of these pipelines.
The first is joining the commercial picture together without provisioning a warehouse for it. Salesforce knows about deals, your product database knows whether those customers use anything, and your billing system knows whether they paid. ClickHouse handles that join at a fraction of the operational overhead.
The second is powering something fast. If you already run ClickHouse behind an internal tool or a customer-facing dashboard, putting CRM data next to the event data already there means one engine serving both. If your only need is standard CRM reporting, Salesforce does that and this pipeline earns you nothing.
What do you need before you start?
Two of these determine what actually reaches your tables:
A dedicated Salesforce integration user. The connector sees exactly what the authenticating user sees, so field-level security and sharing rules on that account decide what lands in ClickHouse. A pipeline built on somebody's personal login also changes contents when their permissions change.
A shortlist of objects and fields. Select what your analysts query rather than everything available. Salesforce orgs accumulate custom objects, and syncing them all wastes API budget you have already agreed to share.
An agreed share of the daily API budget. Every connected application draws from the same org-wide pool, so exhausting it affects other people's integrations rather than only yours. Find out what else calls Salesforce before choosing a schedule.
A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes through the native protocol into typed columns rather than storing everything as JSON in a raw table.
If your ClickHouse instance restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.
How do you build a Salesforce to ClickHouse pipeline in Airbyte?
Step 1: Scope the integration user
Create an account for the pipeline with deliberately chosen visibility, and write down what it can see. That becomes part of your data model rather than an administrative detail, because a field the user cannot see is a field your analysts will never know was missing.
Step 2: Configure the Salesforce source
Click Sources in the left navigation, then New Source, and select Salesforce, following adding a source. Authenticate as your integration user and set a start date. Set the lookback window deliberately too, because Salesforce's API is eventually consistent and a record modified shortly before a sync may not be visible. The default is ten minutes.
Step 3: Configure the ClickHouse destination
Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. Deduplication uses the ReplacingMergeTree table engine, and for it to order duplicates correctly your cursor should be a timestamp rather than a string, which on Salesforce records it naturally is.
Step 4: Use incremental append and deduped
Click Connections, then New connection, select your objects and choose incremental append and deduped as the sync mode. On any org of size this is not really a choice, for the reason in the next section, and deduplication also makes the lookback window free since the overlap collapses on the way in.
That collapsing happens during background merges rather than on write, which is worth knowing before somebody reports duplicate opportunities as a bug. The section below covers what to do about it.
Why does a successful sync sometimes load fewer records?
When the connector reaches your org's daily API limit it does not fail. It stops where it is, reports the run as successful, and picks up from that position on the next run. That is sensible, because failing the whole sync would discard work already done.
The self-correction only works on incremental sync. Configure full refresh against a large org and every run truncates somewhere arbitrary, then starts from the beginning again next time, never reaching the end. That is why the sync mode above is stated as a requirement rather than a recommendation.
On an engine people query interactively the consequence is specific. A partially loaded table does not look broken, it looks like a number that is slightly off, and an analyst comparing it against Salesforce will assume the pipeline is wrong rather than incomplete. Monitor row counts as well as sync status.
Why do duplicates appear and then disappear?
ReplacingMergeTree collapses duplicate rows during background merges, which happen when ClickHouse decides they should rather than when your sync finishes. So a query run shortly after a sync can legitimately return duplicates that the same query returns cleanly an hour later.
This is expected behaviour rather than a fault, and it catches out analysts who have not been told. To guarantee deduplicated results at query time, add the FINAL operator. It costs performance, so the usual pattern is a view using FINAL for correctness-critical queries and direct table access for exploratory work.
On CRM data specifically, deal counts and pipeline totals are exactly the numbers people notice being wrong, so put those behind a view that uses FINAL. Combined with the partial sync behaviour above, that means two separate reasons a Salesforce number in ClickHouse can look off, and it is worth knowing which you are looking at.
Frequently asked questions
Why did my sync succeed but load fewer records?
It hit your org's daily API limit. The connector stops there, reports success, and resumes next run. On incremental sync this is self-correcting, but monitor row counts because a partial table looks like a wrong number.
Why does my ClickHouse table contain duplicate records?
ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication before those merges run.
Why are some Salesforce fields missing?
The connector sees what the authenticating user sees, so field-level security and sharing rules on your integration user decide what reaches ClickHouse.
I changed a formula field and nothing updated. Why?
Editing a formula does not change any record's modification timestamp, so incremental sync never re-reads them. Reset the stream and backfill.
Can I do this without writing code?
Yes. The setup above is entirely UI-driven. You will want ClickHouse views using FINAL for your pipeline totals, which is a short piece of SQL.
Get your Salesforce data into ClickHouse
Scope the integration user and document what it can see, agree your share of the daily API budget, run incremental append and deduped so partial syncs resume, and put your pipeline totals behind a view that uses FINAL. Then monitor row counts, because two different things can make a number look wrong here.
Airbyte's connector catalog includes 600+ pre-built connectors, so CRM data can sit beside product and billing sources in one engine. For the warehouse version, see Salesforce to Snowflake, and for another CRM landing in ClickHouse, HubSpot to ClickHouse.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.