Retail Data Warehouse Consolidation

The group had grown by acquisition and each brand kept its own reporting, so a question like how many customers do we have had four answers and a long argument attached. Finance and marketing were reconciling numbers manually before every board meeting, which took days and still produced disagreements in the room. The immediate ask was one set of definitions the whole group used, and the underlying ask was that people stop distrusting the reports enough to keep shadow spreadsheets. It was as much a definitions problem as a technical one.

I was the lead data engineer on a team of three, working alongside an analytics lead who owned the semantics. My job was the ingestion and transformation layers and the operational reliability of the whole thing. I ended up chairing the sessions where brand analysts argued about what counted as an active customer, which was not in my job description and was the most useful thing I did.

I built the ingestion for eleven sources — four point-of-sale systems, two e-commerce platforms, the loyalty database, and some genuinely awful supplier CSV drops — and owned the transformation project that models them into shared dimensions and facts. I wrote the test suite that runs on every build, including the reconciliation tests that compare our totals against each brand's own system. I owned the orchestration, the alerting, and the on-call runbook. I did not own the reporting layer or the dashboards; those belonged to the analytics team and I was happy about that.

The decision with the longest tail was insisting on ELT with transformations in the warehouse rather than transforming during ingestion, which the incumbent contractor had been doing. It meant landing raw data we couldn't immediately use and cost us about a month, but it made every transformation reviewable, testable and rerunnable — and when a supplier changed their file format three months in, we reprocessed from raw instead of losing the history. I also refused to build a single customer dimension until the definitions argument was settled, and shipped brand-scoped models in the meantime. Building it first would have encoded whichever definition I happened to pick.

Two of the point-of-sale systems could only be extracted from overnight, and one of those exports on a schedule we couldn't influence, so the whole daily build is shaped around a window that isn't ours. The group had a strict rule about customer data leaving its region, which constrained which cloud regions and which vendors were available. There was also no appetite to change any source system, so every quirk had to be absorbed downstream — including one platform that reuses order IDs after a year.

Board reporting preparation went from several days of manual reconciliation to a review of the exception report, roughly half a day. The definitions work produced a documented set of about thirty metrics the group agreed on, which is the artifact people actually reference. Our data tests catch source problems before analysts do — in the first six months they flagged the reused-order-ID issue and a point-of-sale system that had been silently dropping refunds from its export for over two years, which had been quietly overstating revenue for one brand.

The analytics lead owned the definitions and did the political work of getting four brands to agree; I supplied evidence about what the data could actually support. A junior engineer on my team built most of the supplier CSV ingestion and handled it better than I would have, since I'd have over-engineered it. Each brand nominated an analyst who reviewed their own models, and their corrections were more valuable than anything our tests found.