20 billion rows of price history, integrated without breaking the budget

Context

A clothing retailer operating more than 150 physical stores alongside a substantial e-commerce channel had accumulated years of product pricing history. The data existed, but it sat outside the warehouse in a form nobody could query, which meant pricing decisions were being made without reference to what had actually happened before.

The problem

Roughly twenty billion rows — around eight terabytes — of nested JSON pricing data. Loading it naively into the warehouse would have been slow, expensive to store and ruinous to query. The volume also made data integrity a genuine risk: a silent truncation or type coercion at that scale is very hard to detect after the fact and very costly to discover late.

What we did

• 01Preprocessed the raw JSON on a distributed processing cluster, doing the heavy reshaping against distributed storage before anything reached the warehouse.
• 02Designed a medallion architecture in the warehouse so raw, cleaned and business-ready layers stayed separated and independently reprocessable.
• 03Tuned partitioning and clustering against real query patterns rather than defaults, so the common access paths scan a fraction of the table.
• 04Built integrity checks into the processing stage — row counts, type assertions and reconciliation against source — so any loss surfaced during the run, not months later.
• 05Introduced a managed transformation framework to control movement and dependencies between datasets as the graph grew.