Admin dashboards slow across multiple sources: what to fix first

It is Monday morning. Your ad platform, Shopify and your fulfilment system each report a different number of orders for last week. The report that was meant to settle it has been spinning for a minute, because it is reading the same database that serves your checkout. Nobody can say which number is right, so the meeting moves on without a decision.

That is two problems wearing one coat. One is a slow query. The other is that no single system holds the answer. They have different fixes, and only one of them needs a second database.

This is for the operator whose three systems disagree, not the analyst choosing a vendor

A data warehouse is a separate database built for reading history. Your store database is built the other way round: it takes small writes fast, and keeps what the storefront needs to serve the next request.

Those two jobs have names. OLTP is the transaction side: checkout, admin, the app. OLAP is the analysis side: totals and joins across long time windows. Google calls its own warehouse product a “fully managed serverless data warehouse” (BigQuery introduction, fetched 21 September 2026).

You are the reader here if orders get re-keyed into an ERP by hand. An ERP is the system holding your stock, purchasing and accounting records. The same applies if stock counts drift between two systems, or if three tools each claim a different revenue number for the same week.

Two readers should stop. If every question you ask can be answered inside one system, you do not have this problem yet. And if you are comparing warehouse vendors on features, this is not that comparison: it is the decision that comes before it.

Working out which questions actually need a second database is the first hour of any ecommerce analytics engagement. It is usually a shorter list than the founder expects.

A slow dashboard is usually one query reading years of history on your checkout database

Here is the shape of the query that does it. This is valid PostgreSQL, and it is the kind of thing a dashboard runs on every page load.

SELECT customer_id, COUNT(*)
FROM orders
WHERE created_at > NOW() - INTERVAL '2 years'
GROUP BY 1;

Two things make that slow, and neither is “the server is too small”.

First, if created_at carries no index, the database reads the whole orders table. Second, the grouping needs memory. On a default PostgreSQL install work_mem is 4MB. A sort or hash table uses that much “before writing to temporary disk files” (PostgreSQL, Resource Consumption, read 21 September 2026). The same page warns that one complex query may run several such operations at once.

So the query does not fail. It spills to disk and gets slow, and it does that while your checkout is competing for the same disk.

One thing you will read everywhere is wrong. A read-only dashboard query does not lock your writers out. In PostgreSQL a plain SELECT takes an ACCESS SHARE lock, which “Conflicts with the ACCESS EXCLUSIVE lock mode only” (PostgreSQL, Explicit Locking, read 21 September 2026). Orders keep saving. What you lose is memory, disk throughput and cache, all shared with the store.

Getting two years of orders out of Shopify is rate limited, so pull it on a schedule

If your reporting calls the Shopify Admin API live, you are spending a budget you share with everything else that app does.

The GraphQL Admin API charges each query in cost points against a bucket that refills at a fixed rate. Standard refills at 100 points per second, Advanced at 200, Shopify Plus at 1000 and Enterprise at 2000 (Shopify, GraphQL Admin API rate limits, fetched 21 September 2026).

Shopify documents the answer for large pulls. A bulk operation runs in the background and hands back a file. You skip “manually paginating results and managing a client-side throttle”. From API version 2026-01, each app can run five bulk query operations per shop at once. Results come back as JSONL, one JSON object per line. The download URL expires after one week. An operation that has not finished within 10 days is stopped and marked failed (Shopify, Bulk operations, fetched 21 September 2026).

That last pair of numbers is the real argument for a warehouse. The export is a rental. If you want last year’s orders next March, you keep your own copy.

Count the systems that must appear on one row, not the rows you have

Volume is rarely the trigger. A single Shopify store’s order table stays small enough for an indexed query long after the reports have stopped agreeing with each other.

The trigger is joining. The moment the question is “revenue by campaign, after returns, for items we actually shipped”, you are joining an ad platform, a store and a fulfilment system. Doing that by hand in a spreadsheet each month is what produces three numbers and no answer.

A warehouse is overkill when one system already holds the answer

If your question is last month’s sales by product, Shopify answers it. If your question is which SKUs went out of stock during a promotion, your store and your fulfilment feed answer it between them. Adding a second database there buys you a nightly job that can fail and nothing else.

Keep the metrics layer honest about which is which. A metrics layer is the one place a term like “net revenue” gets defined, so every chart means the same thing.

What breaks after it exists, and who owns each break

A warehouse is not finished when the first dashboard loads. It becomes a system somebody maintains, and the maintenance is mostly about corrections.

Transformation logic is where it shows. dbt is the common tool for writing those transformations as version-controlled models. Its own documentation says an incremental model “limits the amount of data that needs to be transformed, vastly reducing the runtime of your transformations”. The trade is stated just as plainly. Change the logic and you run a full refresh. Add a column and no setting fills it in for you: “None of the on_schema_change behaviors backfill values in old records for newly added columns” (dbt, Incremental models, fetched 21 September 2026). Adding a column is easy. Filling it in for three years of history is a separate job.

We have paid this bill on our own build. Between August and December 2025 we replaced a monthly spreadsheet routine with a scheduled pipeline. It loads survey exports from Google Drive into BigQuery, partitioned by month and clustered. It retries every morning through the first week of the month, until that month’s file appears. It works. It also carries a defect we wrote down rather than hid.

There is a second one in the same pipeline. The deduplication skips files whose checksum it has seen before. Google Drive exposes no checksum for native Sheets. So those files are re-downloaded and re-processed on every run. Both are ordinary. Both are the sort of thing that only surfaces months later, when someone asks why a corrected figure did not change the chart.

The pattern is not unique to clients. Memox is our own conversational AI product, built and run by the team described in the embedded product team story. Its usage data sits in three places: Stripe for billing, the telephony console for calls, and a product analytics tool for conversations. Our own platform notes say outright that no aggregate usage number exists in any repository, and instruct nobody to invent one. That is the normal state of a business with three systems, not a failure of discipline.

Ownership splits cleanly once you write it down. You own the schedule, the backfills and the definition of every metric. The warehouse vendor owns uptime and query execution. Shopify owns the rate limit and the one-week expiry on export URLs, and it will not move either for you.

Most of what this costs you is not the build. It is the monthly reconciliation nobody scheduled, and the meeting where three numbers arrive and no decision does. Those are the hours worth getting back. They are also the hours a Shopify or Shopify Plus operator hands over when the order sync, the ERP handoff and the reporting on top are owned by someone, rather than merely installed.