Retail
Enterprise data warehouse migration to BigQuery
A national retailer moves off an ageing on-premises appliance, cutting warehouse running costs and turning overnight reporting into same-hour analysis.
- Sector
- National multi-channel retail
- Scale
- Several hundred stores, e-commerce and marketplace channels
- Engagement
- Assessment, migration and enablement
- Duration
- Approximately nine months, delivered in waves
The challenge
Where this started
The reporting estate ran on a fixed-capacity on-premises appliance bought for a business half the size. Peak trading saturated it, so the analytics team queued their own workloads behind nightly batch loads and answered merchandising questions a day late.
Capacity could only be added in large, expensive increments, and a hardware refresh was approaching. Meanwhile a decade of undocumented views and stored procedures had accumulated, with no lineage back to source systems and no agreed definition of margin across channels.
Our approach
What we did, and why
The decisions that mattered, including the ones that were unglamorous.
- 01
Assess before moving anything
We inventoried every table, job and downstream consumer, then measured which were actually used. A substantial share of objects had not been queried in over a year and were retired rather than migrated — the cheapest workload to move is the one you delete.
- 02
Model deliberately, not literally
Rather than lift the existing schema unchanged, we remodelled the core sales, inventory and customer domains for BigQuery — partitioning by event date, clustering on the predicates that dominated real query logs, and collapsing chains of nested views.
- 03
Rebuild transformations as tested code
Stored procedures became version-controlled SQL transformations with automated tests and data-quality assertions on the tables the business depends on, reviewed like application code.
- 04
Run in parallel, then cut over
Both platforms ran side by side while we reconciled outputs domain by domain. Cutover happened only once figures matched and the owning team signed off, which kept the decommission decision uncontroversial.
- 05
Define margin once
A Looker semantic layer gave each metric a single definition across channels, ending the reconciliation meetings that had previously preceded every trading review.
Architecture
How the pieces fit together
01
Sources
- Point of sale
- E-commerce platform
- Inventory and ERP
- Marketplace feeds
02
Ingestion
- Batch loads for financial data
- Pub/Sub and Dataflow for inventory movements
03
Storage
- Cloud Storage landing zone
- Partitioned and clustered BigQuery datasets
04
Transformation
- Version-controlled SQL
- Automated tests
- Quality assertions
05
Consumption
- Looker semantic layer
- Finance and merchandising reporting
- Forecasting inputs
Technologies
- BigQuery
- Cloud Storage
- Dataflow
- Pub/Sub
- Dataplex
- Looker
- Terraform
Outcomes
What changed
Described qualitatively. We publish client-specific figures only where the client has approved them.
Analysis during the trading day
Merchandising questions that previously waited for the next morning's batch are answered while decisions can still act on them.
Capacity stops being a ceiling
Peak trading no longer forces analysts to queue behind batch loads, and the hardware refresh was avoided entirely.
A materially smaller estate
Retiring unused objects before migrating meant a smaller platform to run, document and pay for.
Cost that tracks usage
Partitioning, clustering and a considered slot strategy replaced fixed appliance capacity, so spend follows actual demand and is monitored for drift.
One definition of margin
Channel comparisons became straightforward once the semantic layer removed competing definitions.
This engagement is anonymized and presented as a representative scenario. It reflects the architecture patterns, decisions and trade-offs typical of our work in this area rather than the details of one named client. Identifiable engagement details are published only with written client approval.
Related services
Other engagements
- Predictive maintenance on Vertex AI
Manufacturing & Energy
- Document processing automation with generative AI
Financial Services