Shopify to ClickHouse: How to Move Your Data

Moving Shopify into ClickHouse gives a large store analytical speed over years of orders. Shopify reports adequately on recent trading and becomes slow and rigid once somebody asks about basket composition across three years or cohort behaviour by acquisition month.

This guide covers the managed path with Airbyte. Two things shape the build: some streams are served by a bulk API that stalls on large stores, and the grain you choose for your tables is the decision that determines whether queries are fast or merely possible.

Shopify to ClickHouse at a glance:

Why move data from Shopify to ClickHouse?

Two situations account for most of these pipelines.

The first is interactive analysis at scale. Cohort retention, basket composition and repeat purchase behaviour across several years are heavy aggregations, and the difference between a query returning in a second and in a minute changes how often anybody asks. A column store is built for exactly that.

The second is powering a customer-facing analytics product where latency is a feature. If instead you need governed access to customer records with policies over who reads what, Shopify to Snowflake offers masking and row access controls that a column store does not match.

What do you need before you start?

Four things, and the last one is a modelling decision rather than a credential:

A custom app with the scopes your streams need. Shopify scopes access per resource, and an ungranted scope removes the stream rather than raising an error. The Shopify source documentation lists which scope each stream requires.

Your shop name, which is the subdomain. The portion before myshopify.com rather than the domain customers type, which stores with a long-established custom domain often have to look up.

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

A decision about the grain you analyse at. Orders carry line items inside them, and whether your working tables are one row per order or one row per line decides which questions are cheap and which are awkward.

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 Shopify to ClickHouse pipeline in Airbyte?

Step 1: Decide the grain before you create any tables

Work through the questions this dataset is meant to answer and notice which grain each one needs. Revenue by month and repeat purchase rate are order-level questions. Basket composition, product affinity and margin by product are line-level ones. Most stores need both, which means two tables rather than an argument, and knowing that now shapes how you sort and organise them.

Step 2: Configure the Shopify source

Click Sources in the left navigation, then New Source, and select Shopify, following adding a source. Supply the shop subdomain, credentials and start date. Check the discovered streams against the ones you expected, because a missing scope produces a missing stream rather than a complaint.

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 land in typed columns over the native protocol, which is what makes aggregating years of orders quick, and the types settled at table creation are the ones you keep.

Step 4: Create the connection and expect the bulk streams to complain

Click Connections, then New connection, select your streams and a sync mode. Rate limit warnings in the logs are normal and not a sign of trouble. A bulk stream that appears to hang on a large store is a different matter, and the bulk job checkpoint setting exists precisely for it.

Apply FINAL in views for any figure somebody reports, since orders change status and deduplication happens during background merges rather than on write.

Why do some streams stall on a large store?

Because the connector uses three different Shopify APIs and they behave differently under load. Streams served by REST and ordinary GraphQL are predictable. Streams served by GraphQL Bulk submit a job to Shopify and wait for it to complete, which is efficient for large extractions and occasionally looks like nothing happening at all.

On a store with years of orders that wait can be long enough that people assume something has broken and restart the sync, which makes matters worse by discarding progress. The bulk job checkpoint setting is the intended remedy and knowing it exists is most of the battle, since the alternative is an afternoon spent investigating a pipeline that was working.

Separate the rate limit noise from the real signal while you are at it. Warnings about rate limits appear routinely on this connector and mean nothing; a bulk stream making no progress for an extended period means something. Judge the sync by whether it completes and whether the row counts look right, rather than by the tone of the logs.

Which grain should your tables be?

Both, deliberately, rather than one by accident. Shopify sends orders with their line items nested inside, which is faithful to how an order works and awkward for a column store, where the performance comes from scanning flat typed columns rather than reaching into a structure on every row.

The productive arrangement is two working tables. One at order grain carrying totals, customer reference, status and dates, which answers revenue, cohort and repeat purchase questions. One at line grain with a row per product per order, which answers basket composition and product performance. They are derived from the same landing data and they make different questions cheap.

Sort each one for how it will be read, since that is what decides whether ClickHouse skips data or scans it. An order table is usually sorted by date because almost every query bounds a period; a line table often benefits from product being early in the sorting key. And remember that order status changes over an order's life, so figures reported to anybody should use FINAL rather than trusting a count taken before a merge ran.

Frequently asked questions

A stream seems to hang. What now?

It is probably served by GraphQL Bulk, which submits a job and waits. Adjust the bulk job checkpoint setting rather than restarting and losing progress.

Why is a stream missing entirely?

A missing scope. Shopify grants access per resource and an ungranted one removes the stream silently, so compare discovery against what you expected.

Should I model at order or line grain?

Usually both, as two tables derived from the same landing data. Revenue and cohort questions want order grain; basket and product questions want line grain.

Are rate limit warnings a problem?

No, they are normal for this connector. Judge the sync by completion and row counts rather than by warnings in the logs.

Can I do this without writing code?

The pipeline, yes. The two working tables and the views applying FINAL are SQL, and they are what makes this fast rather than merely populated.

Get your Shopify data into ClickHouse

Decide your grain before creating anything, because order level and line level answer different questions and most stores need both. Check discovery against your expected streams, since a missing scope is silent. Expect bulk streams to look stalled on a large store and reach for the checkpoint setting rather than restarting. Then sort each table for how it will actually be read, and apply FINAL wherever a figure leaves the building.

Airbyte's connector catalog includes 600+ pre-built connectors, so store data can be analysed at whatever speed the questions demand. For the same source into a lakehouse, see Shopify to Databricks, and for another storefront into the same destination, Woocommerce to ClickHouse.

Start syncing now →