Most agencies and in-house marketing teams do not have a data warehouse. They have a Shopify admin, a GA4 property, a Meta Ads Manager, a Google Ads UI, a Klaviyo dashboard, and a reporting spreadsheet that tries to tie them together and usually fails.
That works until the question stops being "what did paid search do last week" and starts being "what share of revenue across fifteen brands, eight markets and three currencies was incrementally driven by marketing, and which cohort is actually profitable after refunds." At that point the spreadsheet breaks, the connector subscription starts getting expensive, and somebody who has been in this situation before tells you it is time to build a warehouse.
This is what one actually looks like, built for a multi-brand operator running approximately fifteen DTC stores across Europe.
When you actually need a warehouse
A warehouse is not for everyone. A single-brand business spending under £50k/month on paid, selling in one market in one currency, can run on Shopify reports and a well-built Google Sheet. Building a warehouse for that operation is expensive over-engineering.
The threshold changes when three conditions combine.
Multiple brands or stores. Two or more P&L units under one operator or agency. Each needs its own reporting, plus a portfolio view.
Multiple markets or currencies. A brand selling across UK, EU, and Nordics in three currencies needs local numbers that tie to the store admin, internal numbers for headquarters reporting, and external numbers for client consumption. Three views of every figure.
Questions the platform UIs cannot answer. Refund-adjusted cohort LTV, cross-channel blended ROAS against internal targets, customer overlap between brands, marketing's causal contribution to revenue via MMM. If the answer is "export to Excel and pivot", the warehouse is cheaper.
A useful test: if your reporting answer to any strategic question begins with "let me export a CSV and pivot it", you are below the threshold for needing a warehouse. If it begins with "we cannot answer that without two weeks of manual reconciliation", you are above it. The middle ground is where connectors like Fivetran or Funnel earn their keep, before the brand count and complexity tip the economics to custom.
The shape of the build
The build follows a three-layer pattern per data source, per brand.
Raw ingestion lands in staging tables (one dataset per brand, prefixed stg_). Deduplication and partitioning produce production tables (prd_). Business logic, currency conversion, tax treatment, and attribution produce fact tables (fact_) that feed dashboards and downstream analysis.
Each brand gets its own BigQuery dataset. This gives clean role-based access control (a brand team sees only their brand), clean cost attribution (each brand's spend on BigQuery is traceable), and clean blast radius (a breaking change in one brand's SQL does not affect another).
Two shared datasets hold cross-brand concerns: a global layer for FX rates, monthly revenue targets, ingestion run history, and pipeline health; and an operations layer for portfolio aggregations like blended ROAS across all brands and group-level commission calculations.
Ingestion runs on Cloud Run in Python, one service per data source. Transforms run as BigQuery Scheduled Queries, orchestrated by Cloud Workflows in dependency tiers. The entire pipeline completes daily within a four-hour window, refreshing fifteen brands across approximately 265 SQL transforms.
Everything is version-controlled and deployed via Terraform. Adding a new brand is a configuration change, not a copy-paste exercise.
Why BigQuery and custom Python, not Fivetran and Snowflake
Three choices worth explaining because they are the ones readers reach for first.
BigQuery over Snowflake. BigQuery is the native home of GA4 data, integrates with Looker Studio for zero-cost visualisation, and uses pay-per-query pricing that handles spiky agency workloads well. Snowflake is an excellent product for different problems; for this shape of workload, BigQuery wins on integration and cost.
Custom Python on Cloud Run over Fivetran, Stitch, Supermetrics, or Funnel.io. At fifteen brands across six data sources, connector pricing gets aggressive fast. A Fivetran or Funnel footprint at this scale typically runs £3,000 to £10,000 per month depending on row volumes and sync frequency. The GCP bill for the equivalent custom ingestion is low four figures all in, for a system with more control over the data shape, the tax-handling, and the API quirks per source.
The trade is maintenance. A Fivetran connector updates automatically when Shopify's schema changes. A custom pipeline needs someone to catch that change and ship a fix. The commercial question is whether that engineering time is cheaper than the connector subscription. At this scale, the answer is clearly yes. Below around five brands and six sources, the answer flips.
Looker Studio over Tableau, Looker, or Metabase. Per-seat licensing across fifteen brand teams plus agency stakeholders is not viable. Looker Studio is free, BigQuery-native, and good enough for the operational reporting surface. If a brand later needs a more powerful BI layer, BigQuery feeds anything.
The trust layer
The ingestion and transformation plumbing is the part most engineers focus on. The trust layer is the part that makes the warehouse commercially useful, and it is where most builds cut corners.
Tax-inclusive figures that match the store admin. Shopify's UI shows tax-inclusive revenue by default. If the warehouse reports net figures by default, every conversation with a brand manager starts with "your dashboard does not tie to Shopify" and ends with the warehouse losing credibility. Every fact table should carry the tax-inclusive version as default and expose net alongside as an option.
Currency as a chain, not a single rate. A brand selling in Sweden, reporting internally in GBP, and client-facing in EUR needs local amounts preserved, plus conversions through two rates: local to internal, and internal to external. Treating currency as a single conversion breaks the moment one client's ad accounts spend in a different currency than their reporting.
Defensive joins against shared lookups. FX rates and monthly targets live in a spreadsheet that finance owns. The sheet will eventually gain duplicate rows. Every fact table that joins to it must deduplicate at the join, not rely on the sheet being clean. A Cursor skill enforces this pattern on every SQL edit.
These are patterns each deserving their own writeup. Methods to follow on currency chains, FX defence, and tax reconciliation.
The single biggest category of silent warehouse failure is joins against shared lookup tables that gain duplicate rows. The FX and targets sheet, the platform config lookup, the market-to-currency mapping. Every one of them will eventually duplicate. Every fact table must deduplicate at the join. Enforcement belongs in CI or in the edit-time linting layer, not in the reviewer's memory.
What the warehouse unlocks that platforms cannot
Five classes of analysis become possible once the warehouse is in place.
Blended ROAS across Google, Meta, Microsoft, affiliates, reconciled against Shopify revenue net of refunds, at daily-brand-market grain. The platforms cannot produce this because they cannot see each other.
Refund-aware cohort LTV. New-customer acquisition cost against lifetime contribution, net of the refunds that happened two months later. Impossible in platform UIs, trivial in SQL once the fact tables exist.
Forecasting per brand. ARIMA-style time series against clean historical data, with model parameters tuned per brand because generic training windows fail when a portfolio contains both growth and decline stories. Another pattern worth its own writeup.
Marketing Mix Modelling. The MMM stack described in L/005 runs directly against the warehouse's fact tables, sitting in the oversight layer of the broader two-layer measurement architecture (L/004). The data is already reconciled, currency-normalised, and attributable. Most of the engineering hours in an MMM project are data preparation; the warehouse erases that category of work.
Audience automation. Value-based customer lists pushed to ad platforms, seeded from LTV and cohort segmentation rather than broad-stroke audiences. The same server-side activation surface that L/003 uses for conversion recovery also accepts custom audiences directly from the warehouse. More on this in future pieces.
The operator's warehouse runs approximately 265 daily SQL transforms across fifteen brand datasets and two shared layers. Ingestion and transformation complete within four hours each night. The monthly GCP bill sits in the low thousands of pounds all-in, versus an equivalent Fivetran or Funnel.io footprint that would price in the mid-to-high thousands for comparable source and volume coverage. The commercial case for custom ingestion strengthens with brand count.
What breaks in production
Three classes of failure recur across any warehouse at this scale.
Authentication drift. Platforms change auth models with minimal notice. Shopify's ongoing migration of custom apps onto OAuth client credentials, Meta long-lived tokens expiring at sixty days without a system user swap, Google Ads API version deprecations every six months. The pipeline has to expect this and fail loudly when it happens.
Data shape drift. A campaign-naming convention changes, a spreadsheet gains an extra row, a platform adds a new tax field that defaults to null. Silent drift is worse than loud failure because the numbers look plausible until someone reconciles them against the source and the trust is gone.
Scale limits that surprise you. Cloud Workflows has a 100-branch concurrency limit. BigQuery Data Transfer Service clears destination datasets on multi-statement scripts unless configured via REST. These are not in the documentation you read during selection.
Monitoring alerts, data-staleness views, and convention enforcement at edit time are the difference between a warehouse that stays credible and one that quietly decays.
The MMM connection, and what comes next
The reason we built this warehouse before building the MMM was deliberate. Marketing Mix Modelling is only as credible as the data it runs on. Daily reconciliation, currency normalisation, attributable ad spend, refund-adjusted revenue, all of these have to be solved before a Bayesian model can produce outputs a board will trust.
Most agencies get asked for MMM and spend six weeks preparing data, then discover the preparation itself is unstable and needs to be rebuilt every quarter. The warehouse makes MMM a six-day project, not a six-week one, because the data is already clean.
The next phase of this build extends the warehouse beyond DTC. Wholesale order data from the operator's Brightpearl ERP adds the other half of the commercial picture, letting MMM measure not just DTC revenue but the halo effect of DTC ad spend on wholesale sell-through. That is the measurement question most multi-brand operators want answered and cannot answer without both layers present.
Audience automation is the second extension. Value-based customer lists, LTV-seeded lookalikes, and refund-risk exclusions, all sourced from the warehouse and pushed back to ad platforms. The warehouse becomes an activation layer, not just a reporting one.
Methods and a full Case Study on both will follow.
