Shopify to ClickHouse: build a fast ecommerce analytics warehouse

Quick answer: ClickHouse is a strong home for a Shopify analytics warehouse when you need fast, repeatable answers over orders, line items, customers, products, inventory, and events. Load the Shopify data with the ingestion method that fits your team, keep a source-shaped raw layer, then publish cleaned models and reporting tables. ingestr is a simple, fast CLI option for the first step. To make this concrete, we will use Bruin's out-of-the-box ecommerce template as a running example to explain how to ingest, transform, and model Shopify data in ClickHouse.

The first Shopify sync is rarely the hard part. The hard part is what happens afterwards.

An order is edited. A partial refund lands three weeks later. A product is archived. Someone asks why the revenue chart no longer matches Shopify. The answer is often trapped in a connector setting, a dashboard calculation, or a very brave CASE WHEN that nobody wants to touch.

This is why a Shopify to ClickHouse setup needs more than a connector. It needs a warehouse shape, explicit business rules, and a way to change both without turning every new metric into a mini migration project.

Why ClickHouse works well for Shopify analytics

Shopify has a familiar ecommerce data shape: lots of detailed records, a few very common reporting paths, and regular changes to existing records. People ask for revenue by day, product performance, returning-customer rate, discount usage, refunds, inventory risk, and attribution. Then they ask for the same answers by country, channel, currency, collection, customer cohort, or marketing campaign.

ClickHouse is built for this kind of analytical workload. Its MergeTree table family stores data in sorted parts, supports high ingest rates, and uses a sparse primary index to skip data during reads. The physical order of a table matters, which is useful when the queries are predictable enough to model around them.

For a Shopify warehouse, that usually means:

• writing orders and events in batches instead of row by row
• sorting tables around the fields that appear in filters and joins
• using monthly partitions for date-based data when they help operations
• keeping money as Decimal and timestamps as UTC DateTime64
• treating source updates and refunds as normal data behaviour, not exceptions

There is one ClickHouse detail worth getting right early: ORDER BY is not a uniqueness constraint. It defines the sorting key and, in a MergeTree table, the sparse index. Choose it for the way people read the data, not because it looks like a familiar OLTP primary key. ClickHouse's own guidance also cautions against overly granular partitions. A monthly partition is often enough; partitioning by store or customer ID is usually a bad trade. Read the MergeTree partitioning guidance.

Start with the Shopify data that answers real questions

You do not need every Shopify object on day one. Start with the data that supports the decisions the business already makes.

Shopify's source reference lists orders, customers, products, inventory_items, discounts, transactions, and events as available data sets. That is plenty for a proper first version.

The useful question is not "what can the API give us?" It is "what has to be true for this report to be trusted?" If a finance report needs payment status, get transactions. If the merchandising team needs stockout analysis, take an inventory snapshot. If neither question is in scope, do not build it just because the endpoint exists.

Choose an ingestion method that fits your team

There is no single best way to ingest Shopify data into ClickHouse. The right choice depends on how much connector maintenance you want to own, how often data needs to refresh, how much control you need over the process, and whether ingestion is one isolated job or part of a broader pipeline.

ingestr is a good option when the goal is simple: move data reliably, run it from a terminal or CI, and keep the configuration close to the project. Its interface is deliberately small: provide a source and destination, choose an incremental strategy when the source supports one, then let the tool move the records. It can also run inside a Bruin project as an ingestr asset.

For Shopify, incremental loading matters more than a clever first backfill. Orders, customers, products, and discounts all change after their first appearance. Use the source updated_at field and a merge-style write where it is supported. Shopify's source documentation identifies updated_at and merge as the natural incremental pattern for the main commerce resources. See the Shopify ingestion reference.

Build a Shopify medallion architecture in ClickHouse

Medallion architecture is just a practical way to stop source data, cleaning logic, and business metrics from becoming one big table. The labels vary. You might call the layers bronze, silver, and gold, or raw, staging, and marts. The separation is what matters.

Shopify Admin API
       |
       v
bronze: source-shaped records and ingestion metadata
       |
       v
silver: clean orders, line items, customers, products, inventory
       |
       v
gold: daily revenue, cohorts, product performance, reconciliation

Bronze: make raw data recoverable, not mysterious

The bronze layer is a recovery point. It should keep Shopify close enough to the source that you can inspect an odd order, replay a downstream model, or adapt to a source field that appears later.

Each raw table should have the source identifier, the Shopify update timestamp, the ingestion timestamp, and the source fields or raw payload that downstream models need. For example, bronze.shopify_orders may keep the order header and nested line-item structure, while bronze.shopify_transactions keeps payment and refund events separately.

ReplacingMergeTree can be useful when an ingestion process writes a newer version of the same Shopify record. But it is not a magic upsert guarantee. De-duplication happens during background merges, so duplicate versions may remain visible for a while. ClickHouse documents this as eventual correctness and recommends query-time de-duplication where an exact result requires it. Read the ReplacingMergeTree behaviour.

That means a revenue dashboard should not depend on background de-duplication happening at the right moment. Treat the bronze table as source history or the latest available source state, then make the silver and gold refresh rules explicit.

Silver: define orders before defining revenue

Raw Shopify orders are not analytics tables. They contain nested line items, shipping addresses, fulfilments, discounts, refunds, and several status fields that look similar until they do not. The silver layer gives those records stable names and a single grain.

A practical Shopify silver layer usually includes:

• silver.orders with one row per Shopify order
• silver.order_line_items with one row per order line item
• silver.customers with customer lifecycle fields and only the personal data you genuinely need
• silver.products and silver.product_variants with catalogue context
• silver.inventory_snapshots when inventory history matters
• silver.transactions and silver.refunds when finance needs reconciliation

This is where you flatten line items, cast timestamps, normalize status values, and state the business rules. Keep gross_sales, discount_amount, refund_amount, and net_revenue separate. Preserve the source currency. Exclude test or cancelled orders through named flags instead of copying the same conditions into every downstream query.

One boring but important rule: never sum different currencies in the same metric. If leadership needs a single reporting currency, add an explicit FX model with a documented rate source and date policy. A chart that silently adds USD, EUR, and CAD is worse than no chart at all.

Gold: make reporting tables small and obvious

Gold tables should answer a business question directly. They are not a place to dump every cleaned field in the warehouse.

Here is a deliberately small daily_revenue shape:

CREATE TABLE gold.daily_revenue
(
  metric_date Date,
  currency LowCardinality(String),
  completed_orders UInt64,
  gross_sales Decimal(18, 2),
  discounts Decimal(18, 2),
  refunds Decimal(18, 2),
  net_revenue Decimal(18, 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(metric_date)
ORDER BY (currency, metric_date);

The table is compact because the complicated work already happened in silver. When an order changes, recompute only the affected date range or partition. That is generally safer than trying to patch every aggregate in place.

Use ClickHouse features deliberately

ClickHouse gives you useful choices, but each one has a cost. A Shopify analytics warehouse does not need every optimisation on the first day.

The right model is tied to the query pattern. A product-performance query filtering by date and product should not use the same sort order as a customer-cohort table filtering by first-order month. ClickHouse's query optimisation guide is a useful reference once you have production query logs rather than guesses.

Make Shopify revenue rules executable

The dangerous warehouse bugs are often reasonable-looking numbers. A pipeline needs checks close to the SQL that produces the metric.

For a Shopify to ClickHouse pipeline, start with rules like these:

• every raw resource has a non-null, unique Shopify source ID at its intended grain
• one line item belongs to one order, and line totals reconcile to the order header within a documented tolerance
• test, cancelled, and incomplete orders do not contribute to recognized revenue
• a refund does not make an order look like a new order
• every revenue table retains its source currency
• cohort activity cannot occur before the customer's first order
• an order's financial status maps to one reconciliation bucket

These rules make the warehouse easier for people to maintain. They also make it much easier for an AI analyst or data agent to answer a question without guessing what revenue means. The definition, dependencies, and validation rule all live next to the implementation.

Where Bruin fits into the Shopify to ClickHouse workflow

You do not need Bruin to use ClickHouse, and you do not need to replace a working ingestion tool just to adopt a medallion architecture. Bruin is useful when the tooling around the warehouse starts to sprawl: one job for extraction, another repository for SQL, a third place for checks, and no clear way to see what an edited order affects.

Bruin is an open-source CLI that runs SQL and Python assets, supports data-quality checks, and includes ingestr as a native ingestion asset. A small Shopify orders asset can look like this:

name: bronze.shopify_orders
type: ingestr
connection: clickhouse-default

materialization:
  type: table
  strategy: merge
  incremental_key: updated_at

parameters:
  source_connection: shopify-default
  source_table: orders
  destination: clickhouse

The asset describes the source, destination, incremental behaviour, and ownership in one reviewable file. From there, SQL assets can build the silver and gold layers, while checks make the metric contracts executable. Bruin can also show the dependencies between those assets, so a change to the Shopify order model has an obvious downstream blast radius.

If you are starting fresh, the Bruin ecommerce template scaffolds Shopify ingestion, cleaned models, and reporting tables for ClickHouse, BigQuery, or Snowflake. The workflow is deliberately straightforward:

bruin init ecommerce
bruin validate ./ecommerce
bruin run ./ecommerce

The template is not the architecture. It is a fast, readable example of one. Keep the pieces that match the questions your business needs to answer, delete the ones that do not, then make the data rules more specific as the team learns what it trusts.

ClickHouse gives Shopify analytics a fast analytical engine. A medallion structure keeps that engine understandable. And a tool such as Bruin can keep the ingestion, SQL, checks, and lineage together so the team spends less time nursing data tooling and more time deciding what the data should change.