Woocommerce to ClickHouse: How to Move Your Data

Moving WooCommerce into ClickHouse gives a store analytical speed its own database was never going to provide. WooCommerce runs on WordPress, which means your orders live in a database designed for content, and any report spanning a few years of trading competes with the shoppers currently browsing.

This guide covers the managed path with Airbyte. Two things shape the build: a WordPress setting decides whether the connector can see anything at all, and orders change status repeatedly, which affects how ClickHouse reports your totals.

Woocommerce to ClickHouse at a glance:

Why move data from Woocommerce to ClickHouse?

Two situations account for most of these pipelines.

The first is getting analysis off the store. Cohort analysis, repeat purchase rates and basket composition across years of orders are heavy queries, and running them against the database serving your storefront is a reliable way to make the site slow at exactly the wrong moment. A column store answers them in seconds and touches nothing customers rely on.

The second is joining orders to marketing spend, support contacts and everything else that determines whether a sale was profitable. A small store does not need any of this, and WooCommerce's own reports will serve perfectly well until the order count grows into the hundreds of thousands, so this is a pipeline worth building when the queries genuinely hurt rather than in anticipation.

What do you need before you start?

Four things, and the first will stop everything if it is wrong:

Pretty permalinks enabled in WordPress. WooCommerce's REST API serves through custom endpoints that depend on this setting, so with plain permalinks there is nothing to connect to. It is found under Settings, then Permalinks. The WooCommerce source documentation covers the requirements.

A consumer key and secret with read permissions. Generated in WooCommerce's advanced settings under the REST API section. Read is all this needs, and granting more than that to a pipeline is an unnecessary risk on a system that handles payments.

Your shop name in the right form. The domain only, with no protocol and no trailing slash. Supplying the full address with a scheme in front is a common early mistake and the resulting error does not point at the cause.

A ClickHouse database and a sensible cursor per stream. Use a modified timestamp rather than anything string-shaped, since string cursors compare awkwardly and degrade as the tables grow.

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

How do you build a Woocommerce to ClickHouse pipeline in Airbyte?

Step 1: Turn on pretty permalinks and prove the API responds

Change the permalink setting in WordPress, then confirm the WooCommerce REST endpoints actually answer before configuring anything in Airbyte. Two minutes with a browser or a command line settles it, and it separates a WordPress configuration problem from a credentials problem, which is worth doing because the error you get otherwise suggests neither. On a live store, make the permalink change carefully, since it affects your URLs.

Step 2: Configure the WooCommerce source

Click Sources in the left navigation, then New Source, and select WooCommerce, following adding a source. Supply the shop name as a bare domain, the consumer key and secret, and a start date. Select the streams your analysis needs rather than everything, since each one is load on a server that has customers to serve.

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. Records arrive in typed columns over the native protocol, which is what makes aggregating years of orders quick, and it also means the types settled at table creation are the ones you live with.

Step 4: Create the connection and schedule around your shoppers

Click Connections, then New connection, select your streams and a sync mode. This is your own server, so every request competes with real traffic. WooCommerce lets you set custom rate limits precisely to protect the store, and running the heavier syncs outside your busiest hours costs you nothing analytically.

Watch the first full sync against your server's own metrics rather than only Airbyte's, because the cost of this pipeline lands somewhere Airbyte cannot see.

Why can the connector see nothing at all?

Because of a WordPress setting that has nothing to do with WooCommerce or with Airbyte. The REST API serves through custom endpoints that require pretty permalinks, and with the plain setting those endpoints simply are not routed. The store works perfectly, the credentials are valid, and there is nothing at the address the connector is calling.

This is the most common reason a first connection attempt fails, and it sends people to check their consumer key repeatedly because that is what an access failure usually means. Checking the endpoint directly before touching the connector is what separates the two causes, and it takes a fraction of the time that regenerating credentials does.

The shop name format is the second candidate. It wants the bare domain, so no scheme in front and nothing after it, and supplying a full address produces a failure that also looks like a credentials problem. Between the permalinks and the shop name, those two account for most stalled setups, and both are settled in a few minutes if you check them deliberately rather than by trial.

Why do your order totals change between queries?

Because orders change and ClickHouse resolves that in its own time. An order moves from pending to processing to completed, and possibly to refunded, so the same order is synced several times across its life. ReplacingMergeTree deduplicates those versions during background merges rather than at write time, which means several versions can legitimately coexist until a merge runs.

For a store this is unusually visible, because order status is what most reporting filters on. Count completed orders twice with a merge in between and the number falls, which looks like sales disappearing and is the table doing exactly what it promised. Worse, an order that has been both processing and completed can be counted in each until the merge resolves it.

Use FINAL wherever a figure leaves the building, and build it into views so nobody has to remember. Revenue reported to anybody, order counts in a board pack and anything reconciled against your payment provider all need it. A dashboard scanning trends across quarters does not, since a handful of unmerged duplicates will not move a trend line, and FINAL costs speed you do not need to spend there.

Frequently asked questions

The connection fails and my credentials are correct.

Check that pretty permalinks are enabled in WordPress, since the REST endpoints do not serve without them, and confirm the shop name is a bare domain with no scheme.

Will syncing slow down my store?

It can, since the store is self-hosted and every request competes with shoppers. WooCommerce allows custom rate limits for this reason, and scheduling heavy syncs off-peak is sensible.

Why did my completed order count drop?

A background merge deduplicated older versions of orders whose status had changed. Use FINAL for any count that has to be exact rather than trusting the higher earlier figure.

What permissions does the API key need?

Read only. The pipeline never writes, and a read key limits the damage if it is ever exposed, which matters on a system processing payments.

Can I do this without writing code?

The pipeline, yes. Views applying FINAL for reported figures are a small amount of SQL that prevent a recurring argument about whose revenue number is right.

Get your Woocommerce data into ClickHouse

Enable pretty permalinks and prove the endpoints respond before blaming your credentials, and supply the shop name as a bare domain. Use a read-only key on a system that handles payments. Schedule around your shoppers, since this is your own server and Airbyte cannot see the cost. Then apply FINAL in views for any figure somebody acts on, because order statuses change and ClickHouse resolves that on its own schedule rather than yours.

Airbyte's connector catalog includes 600+ pre-built connectors, so store data can be analysed without troubling the storefront. For the same source into a search engine, see Woocommerce to Elasticsearch, and for payment data into the same destination, Stripe to ClickHouse.

Start syncing now →