The data warehouse question for a mid-market merchant

Three things, and it is worth being precise because the sales pitch usually promises a fourth that does not exist.

Joins across systems. Shopify knows what you sold and for how much. It does not know landed cost, which is in your ERP or a spreadsheet. It does not know what you paid to acquire the customer, which is in three ad platforms with three attribution models. It does not know that the same customer opened four support tickets about the same product. Every genuinely interesting commerce question is a join, and a warehouse is the only place where the join is cheap to write.

History that outlives its tools. This is the one people notice late. Your email platform holds three years of engagement data; the day you switch platforms, most of it does not come with you. Same for a subscription platform, a reviews app, a helpdesk. A warehouse that has been copying those facts nightly still has them after the contract ends. If you have ever migrated an app and lost the before-and-after comparison that would have told you whether the migration was worth it, that is the cost.

One definition per metric. Finance, marketing and the merchandising team all report revenue, and all three numbers differ. Somebody has to write down whether revenue is gross or net of returns, whether it includes shipping and tax, whether a cancelled order that was refunded the following month comes out of the month it was placed or the month it was refunded. A warehouse is where that decision gets encoded once. It does not make the decision for you — and if nobody is willing to make it, the warehouse will host three definitions instead of settling on one.

What it does not buy is insight. A warehouse is plumbing. Plumbing has never once told anybody which product to reorder.

Two mechanisms, and most teams asking for a warehouse have used neither.

ShopifyQL is Shopify’s own analytics query language — SQL-shaped, over commerce data including sales, orders, customers, marketing, inventory and payments. It runs in an editor inside the admin and is also exposed as shopifyqlQuery on the Admin GraphQL API, so a scheduled job can run the same query your analyst wrote by hand. For "what happened, sliced by something Shopify already knows about", this is the shortest path there is, and it involves no infrastructure at all.

Bulk operations are how you get everything out. Instead of paging a connection thousands of times, you submit a query and Shopify runs it asynchronously, producing a JSONL file — one JSON object per line, streamable rather than loaded whole. Shopify’s documentation sets out the constraints: as of API version 2026-01 an app can run up to five bulk query operations per shop simultaneously, where earlier versions allowed one of each type; a query may include at most five connections, nested no more than two levels deep; the operation must complete within ten days or it is marked failed; and the signed result URL expires after one week. The initiating mutation counts against your rate limit, the execution does not.

That last point is the one worth internalising. A nightly extract of every order, product and customer costs you almost nothing in rate limit terms, which is why a perfectly serviceable export pipeline is a few hundred lines of code and a scheduler — not a platform, not a vendor, not a per-row bill. If someone is quoting you a data integration project to get Shopify data out of Shopify, this is what they are building.

Warehouse pricing is conspicuous and, for a mid-market catalogue, rarely the problem. Commerce data is small — a store doing tens of thousands of orders a year produces less data in a year than one team’s video calls produce in a week. The three costs that decide whether the project works are all human.

Modelling. Raw Shopify orders are not a revenue table. Somebody has to decide how refunds attach to the original order, how discounts are allocated across line items, what happens to an exchange, which currency the reporting is in and at which exchange rate, and whether a draft order that was never paid exists. Each of those is a half-day conversation and a permanent decision. Skip it and every dashboard disagrees with the admin, at which point people stop trusting the warehouse and go back to the admin — which is the actual failure mode, not an outage.

Ownership. Pipelines break quietly. An API version is deprecated, a field is renamed, a job silently returns zero rows for a fortnight, and nobody notices because the chart still renders — it just bends. A warehouse needs someone whose job includes noticing, and "the agency that built it" is not an answer unless that is written into a retainer.

The second tool. A warehouse has no interface. You will also buy or build the BI layer on top, and that tool has its own modelling layer, its own definitions and its own seat cost. Count it in the decision, because a warehouse without a way to look at it is a bill with no benefit.

There is a version of this that is neither a spreadsheet nor a platform, and it is where we would put most stores in the $2M–$50M range.

Run a scheduled bulk export of the handful of Shopify objects you actually report on — orders with line items, products with variants, customers, inventory levels. Land each one as its own table, keyed by Shopify’s ID, and pull incrementally on updatedAt rather than re-extracting the world every night. Add exactly one other source: whichever system holds the fact you most often cannot join, usually cost price. Point whatever BI tool your finance team already has at the result.

That is a warehouse in every sense that matters, built in a week or two, and it has the property that matters most at this size: you can stop. Nothing about it commits you to a modelling framework, a transformation tool, or a contract. When a third and fourth source arrive and the SQL starts repeating itself, that is the moment to introduce a transformation layer — and by then you will know exactly which models you need, because you will have written them badly once already.

The alternative pattern we see often enough to warn about: a full modern data stack bought up front, with orchestration, transformation, reverse ETL and a semantic layer, serving four dashboards that two people open. Every component is defensible; the total is a part-time job nobody was hired for.

We build data integrations, and this is one of the engagements we most often talk clients down from. The pattern is consistent: the request arrives as "we need a data warehouse", and four conversations later it turns out there are three questions that matter, two of them are answerable from Shopify alone, and the third needs one file a month from the ERP.

That is not a reason to never build one. It is a reason to build it when you can name what it is for, because a warehouse scoped from real questions is a small, boring, durable thing, and a warehouse scoped from ambition is a platform migration that never quite lands. If you want help working out which one you are looking at, that conversation is cheaper than either.

• Shopify integrations and data
• The Admin GraphQL API: pagination, cost and bulk operations
• CDPs, ESPs and the customer record nobody owns