Zoho CRM to ClickHouse: How to Move Your Data

Moving Zoho CRM into ClickHouse turns sales records into something you can aggregate quickly and repeatedly. Zoho reports adequately on its own data, but pipeline analysis across years, joined to product usage or billing, is not what a CRM is built to do and not what its reporting will do quickly.

This guide covers the managed path with Airbyte. Two things shape the build: the stream list is discovered from Zoho rather than fixed, so it differs between environments, and ClickHouse deduplicates during background merges rather than on write, which changes what a count means.

Zoho CRM to ClickHouse at a glance:

Why move data from Zoho CRM to ClickHouse?

Two situations account for most of these pipelines.

The first is analysis the CRM cannot do. Conversion rates by cohort, pipeline velocity over eight quarters, the relationship between activity volume and closed revenue: these are aggregation problems across a lot of rows, and ClickHouse answers them in the time it takes a CRM report to decide it is still loading.

The second is joining sales records to everything else, since a deal is more interesting beside the product usage and support history of the account that signed it. The pairing is less suitable if your team would rather not think about table engines and merge behaviour at all, because ClickHouse rewards attention and quietly misleads the inattentive. A managed warehouse asks less, and Zoho CRM to BigQuery covers that route.

What do you need before you start?

Four things, and the second one decides whether your testing means anything:

Zoho OAuth credentials and your data centre. A client identifier, secret and refresh token, generated in the Zoho API console, together with the region your account belongs to. The Zoho CRM source documentation covers generating them and the scopes involved.

Access to production, not just the developer environment. A developer environment holds roughly half the modules production does, so a stream list discovered there is missing about as much as it shows. Build and verify against production, using the developer environment to learn the mechanics rather than to plan the catalogue.

A ClickHouse database and a view on table engines. Records arrive into typed columns over the native protocol, and deduplication depends on ReplacingMergeTree behaving the way you expect rather than the way people assume. Know which tables matter for counts before anybody builds a report on them.

A sensible cursor for each stream. A timestamp or numeric field, not a string. String cursors compare awkwardly and behave badly once the table is large, and modified time is usually the right answer on CRM records.

If your database restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a Zoho CRM to ClickHouse pipeline in Airbyte?

Step 1: Discover against production before planning anything

Run discovery against the production account and keep the list. Because streams are built dynamically from Zoho's own metadata, this is the only accurate description of what you can sync, and a developer environment will show you roughly half of it. Planning a warehouse schema from a partial list means redesigning it once the real modules appear, which is a tedious way to spend a fortnight.

Step 2: Configure the Zoho CRM source

Click Sources in the left navigation, then New Source, and select Zoho CRM, following adding a source. Supply the credentials, data centre and edition. Custom modules appear in the catalogue automatically, so if one you expected is absent the cause is almost always an edition limitation or a missing scope in Zoho rather than anything in the pipeline.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply the host, port, database and credentials. Data arrives over the native protocol into typed columns, which is what makes aggregation quick, and it also means a type decided at table creation is a decision you live with.

Step 4: Create the connection and choose cursors carefully

Click Connections, then New connection, select your streams and a sync mode. Use a modified timestamp as the cursor wherever one exists, and avoid string cursors entirely. Incremental deduplication relies on the destination resolving duplicates later, which is the subject of the second half of this guide and worth reading before anyone counts anything.

Re-run discovery occasionally. Custom modules added in Zoho will appear on their own, and a stream that exists but was never selected is invisible until somebody asks why last quarter's new process is missing.

Why does your developer environment show fewer modules?

Because it holds roughly half of what production does, and the connector reports what it finds rather than what ought to be there. Streams are assembled from Zoho's metadata APIs at discovery time, so the catalogue is a description of one account at one moment rather than a fixed list the connector carries around.

That dynamic discovery is mostly a gift. Custom modules your sales operations team created appear without anybody configuring them, and a connector with a hard-coded stream list would simply never see them. The cost is that you cannot know what a pipeline will produce without pointing it at the account it will actually run against.

So when a module is missing, look in Zoho rather than at the pipeline. The usual causes are an edition that does not include that module or a scope the OAuth credentials were not granted, both of which are fixed in Zoho's own settings. Debugging a connector for an hour over something that is an entitlement question is a common and entirely avoidable way to lose a morning.

Why do your ClickHouse counts change between queries?

Because ReplacingMergeTree deduplicates during background merges rather than at write time. Two versions of the same record can sit in the table simultaneously, perfectly legitimately, until a merge happens to run and collapses them. Nothing is broken and nothing is lost; the table is simply eventually deduplicated rather than immediately so.

The consequence is that a plain count is not reliably a count of distinct records. Run it twice with a merge in between and the number falls, which looks like data disappearing and is actually the table doing exactly what it promised. Anybody reconciling a deal count against Zoho at an unlucky moment will find a discrepancy that resolves itself before they can demonstrate it.

Add FINAL wherever the count matters, which forces resolution at query time at some cost to speed, or build views that do it so nobody has to remember. Reserve it for queries where correctness genuinely depends on it, since a dashboard scanning trends over quarters is unaffected by a handful of unmerged duplicates while a figure somebody reports to the board is not.

Frequently asked questions

A module is missing from my streams. Is the connector broken?

Almost certainly not. Streams come from Zoho's metadata, so an absent module usually reflects your edition or the scopes granted to the credentials. Check Zoho's settings first.

Can I plan the pipeline using our developer environment?

Only to learn the mechanics. It contains around half the modules production has, so any schema planned from it will be substantially incomplete.

Why did my record count drop without anybody deleting anything?

A background merge deduplicated rows that were waiting to be resolved. Use FINAL where the count has to be exact rather than treating the earlier, higher number as the truth.

Does a custom module appear on its own?

In discovery, yes, because the catalogue is built dynamically. You still have to select it on the connection, so re-run discovery periodically to catch new ones.

Can I do this without writing code?

The pipeline, yes. You will want views applying FINAL for the figures that must be exact, which is a small amount of SQL that saves a recurring argument about whose number is right.

Get your Zoho CRM data into ClickHouse

Discover against production, because a developer environment shows about half the picture and a schema planned from it will need rebuilding. When a module is missing, check Zoho's edition and scopes before suspecting the pipeline. Use timestamp cursors rather than strings, and decide early which figures have to be exact, because ReplacingMergeTree deduplicates on its own schedule and a count without FINAL is an estimate however confident it looks.

Airbyte's connector catalog includes 600+ pre-built connectors, so CRM records can sit beside the product and billing data that explain them. For the same source into a lakehouse, see Zoho CRM to Databricks, and for another CRM into the same destination, Salesforce to ClickHouse.

Start syncing now →