The problem
Every ecommerce question started with the same join across eight systems and 19 source tables, about 200 lines of SQL, re-derived slightly differently each time. The same metric was defined differently from dashboard to dashboard. The tiebreaker was a 40-tab workbook, 112 columns wide, that one person updated by hand every day. Meetings opened with an argument about whose number was right and rarely got past it.
The build
- One staging model per source, typed and deduplicated, joined to the fiscal calendar so a missing day shows up as a gap instead of a silent zero.
- One mart. One row per day, 136 metrics across sales, traffic, spend, funnel, forecast, contact center, and stores. Definitions documented in the warehouse where the table lives.
- Scheduled every morning. Rewrites a trailing 7-day window on every run, so late-arriving data corrects itself without anyone noticing there was a problem.
- The definition fights, settled by reconciliation. What counts as an ecommerce sale: a grab-bag channel bucket was overstating it by about $6M a quarter until an explicit store list replaced it. How vendor income behaves on a return: contribution dollars reconciled to the finance cube exactly. On completed weeks, ecommerce sales now match finance to the dollar.
| Before | After | |
|---|---|---|
| Sources joined per report | 8 systems, 19 tables, across warehouse projects | 1 table |
| SQL to start a new dashboard | 200+ lines, re-derived each time | select from the mart |
| Definition of a metric | Varied by dashboard | One, documented in the warehouse |
| Referee when numbers disagreed | A 40-tab workbook updated by hand daily | Reconciliation to finance, checked weekly |
| Ecommerce sales vs. finance | Overstated by about $6M a quarter | To the dollar on completed weeks |
| Late-arriving data | Manual re-pulls | 7-day self-healing window, every morning |
| Lineage documentation | Hand-drawn, drifted for 4 months unnoticed | Generated from the models, build fails on drift |
| Prior-year comparisons | A 364-day offset, wrong in 53-week years | A mapped prior-year date, 114 prior-year columns |
What it showed
The payoff was not consistency. It was detection. With every source side by side in one table, a broken feed cannot hide. Three catches in one summer:
- A media-spend feed zeroed its newest day. A week that was really down 15% would have read down 26%. The cross-source spend columns flagged it before the readout.
- A web-analytics feed started half-loading. Sessions "fell" 90% while the parallel behavioral source showed the normal seasonal dip of about 18%. Feed failure, not traffic collapse. A staleness alert shipped that week.
- Units per transaction was inflated about 75% by free promotional items nobody had filtered out.
The documentation needed a gate too. The hand-drawn lineage diagram drifted for four months, three sources and ten columns behind the models, before anyone noticed. It is generated from the models now, and the build fails if they diverge.
Not everything reconciles, and the mart says so. Two instruments for store intent disagree by roughly a quarter and are published as two independent instruments, not rescaled to agree.
What changed
- 40 downstream projects read the mart: 10 dashboards, 10 weekly-deck modules, all three forecast engines, most new analysis.
- The deck's forecast switched from a planning file that read down 24% when reality was down 14% to the mart's daily columns.
- Hard-coded stopgaps in the deck deleted once the mart reproduced them within basis points.
- The 40-tab workbook retired as referee. New dashboards start with select from the mart, not 200 lines of SQL.
- A prior-year view with 114 mapped columns, so BI tools inherit the fiscal-calendar shift instead of re-implementing it.
Technical notes, for the people who will ask
Modeling. dbt over BigQuery: 19 source tables from 8 systems, one staging model per source (typed, deduplicated, calendar-joined), one wide daily mart of 136 documented columns plus a prior-year view. LEFT JOINs from the calendar spine so no source can drop a row.
Operations. Warehouse-scheduled queries every morning, staging then mart, DELETE+INSERT over a trailing 7-day window, so runs are idempotent and late data self-heals. API ingestion for search ads runs outside dbt on a scheduler with retries. Metric documentation is pushed into the warehouse schema so definitions travel with the table.
Quality. A check script and an HTML quality report per run, staleness alerts on feeds, and a lineage generator with a drift gate: undocumented model, orphan source, mart column outside a documented section, or a hard-coded table name fails the build.
Fiscal-calendar correctness. Year-over-year comparisons use a mapped prior-year date rather than a naive 364-day offset; the retail calendar's 53-week years and moving holidays break the naive version in ways that surface at the worst time.
Resilience. Overlapping sources are kept side by side (two web-analytics feeds, spend from platform and API) with blended columns that prefer the freshest complete source. That redundancy is what makes the feed-failure catches possible.
Next: the dashboard & reporting suite that reads from this layer, the organic decline, or back to all work.