If you work with multi-location data long enough, you’ve run into this problem: two reports that should agree… don’t. The finance dash says one thing, marketing says another. Stakeholders who have already shared a revenue figure externally lose confidence and start questioning the integrity of the data in general.

In many cases, the culprit here is not an error in the sources themselves, it’s missing rows of data. One source has gaps where another doesn’t, so your joins quietly drop days, and your “by location, by day” rollups stop lining up. The fix is boring and powerful: build a spine that lists every date for the period you care about and every location you operate, then attach all of your facts to that spine. Once you do that, your numbers stop arguing.

The idea in plain English

For the sake of this example, it doesn't much matter which data warehouse you actually use - the flow is straightforward:

  • First, seed or generate a date spine for the date range you care about (leave yourself plenty of room in the past and future) and enrich it with the simple helpers your team uses every day (for example, company holidays, payroll processing days, etc.)
  • Next, load or seed a locations dimension with the attributes you need to slice by (location name, MSA, region, etc.)
  • Then we build the date_location_harness by joining those two and keeping only rows on or after each location’s open date
  • From there, your reporting models aggregate each fact to date + location and left-join onto the harness. This guarantees that “every location × every day” exists exactly once. From here on out, every reporting model hangs off this harness, not off whichever fact table you happen to be touching that day.

Article content

A quick sample table showing how this harness output looks

Two benefits appear immediately. First, you can add useful context once and reuse it everywhere: “weeks since opening,” “is mature vs new,” “major metro vs midsize.” Second, ad-hoc work gets easier. When you need a quick “location by day” query, the harness gives you a stable base that won’t collapse because one source is sparse.

Why this matters

Imagine you join “revenue by day” to “reviews by day.” If a store recorded no revenue on Tuesday but did get three reviews, a naïve join will often drop Tuesday entirely for that store. Now your review counts are wrong whenever there’s no matching revenue. The spine prevents that. The day exists whether or not a fact showed up, so the join works and your denominators are consistent.

Common ways this goes sideways

A few details make or break the result, so keep these tips in mind as you build your harness:

  1. Make sure your dates are in the same time zone across sources, or at least mapped to a single reporting day
  2. Respect a location’s open and closed periods, or you’ll create fake zeros before a store existed or after it shut down
  3. If you use a fiscal calendar, generate it up front so everyone is truncating to the same week and month boundaries
  4. Ensure all day that you join to the harness is aggregated at the day / location level of granularity (in other words, those are your "group by" fields!). Otherwise, you'll start duplicating results very quickly.

Bottom line

When reports don’t tie, it’s usually not (just) a dashboard issue. Much more often, it’s an issue with grain or join related gaps. A simple date + location spine gives you one grid to hang every measurement on, which means totals line up, trends look right, and reconciliation stops being a weekly ritual.

If you want to see this built end-to-end, we recorded a short walkthrough that shows the dbt setup and the harness in action.

Thanks for reading! Want more? Check out our blog and our YouTube channel for deeper dives and walkthroughs. We'll be back each week with more content - subscribe to stay in the loop.

#dbt #DimensionalModeling #Analytics