A faster total is not always a valid substitute

An order fact with a hundred million rows can collapse to a few hundred thousand store-day rows. “Sales by store last month” can use the summary. “Orders that used this coupon” cannot, if the summary has no coupon dimension. The substitution either drops the filter or returns a larger number. Navigate to a summary only when its grain, dimensions, and refresh time cover the query. Otherwise use detail and say why.

Kimball’s grain rule says to declare what one fact row is. A summary is another fact table: one row is a store-day, not an order. A star schema does not promise that two fact tables match under every filter.

Write three mismatches into the contract

Grain: the summary is store-day and the question wants orders. Filter: the question uses a coupon, a loyalty tier, or a return reason that the summary does not store. Time: detail already includes this morning’s refund and the summary stopped yesterday. All three forbid a silent swap. If a summary is still returned for speed, label it “coupon filter not applied” or “through yesterday’s summary.” Do not reuse the detail metric’s name without that label.

Also agree how the tables reconcile. With no extra filter, last month’s store-day sums should match detail, within a stated rounding tolerance. Past that tolerance, stop navigating. A dbt metric with one name still needs the query shapes that are allowed to use each implementation.

The source of truth depends on the question

For a closed month, the finance-locked summary can be official, and detail only explains the gap. For an open operating question, detail or the later event stream wins, and the summary is only an acceleration layer. Both rules can exist. One metric version picks one rule and the time range where it applies. An operating question compared with a locked finance summary should be explained as refunds after lock, not as a reason to edit the locked table.

Unknown members break navigation too. A summary may fold a blank store into Other, while detail still has null. Filtering Other on the summary is not the same set as filtering null stores on detail. Check that unknown-member policy before navigating.

Two implementation paths

Path one decides at compile time. If every dimension and filter exists on the summary, and the summary is at least as fresh as the requested watermark, rewrite to the summary. It suits fixed dashboards. Path two always uses detail. The summary is for people to read, not for the query engine. It suits ad hoc filters. It is slower, and it does not answer the wrong filter.

The wrong rule is “if the tables join, use the summary.” A shared metric name means someone hoped the numbers would match. It does not mean they do.

Counterexamples and acceptance

Counterexamples: a coupon filter still hits a daily table with no coupon; the summary stopped yesterday but the answer claims to include today; unknown stores are classified differently; navigation continues after the tolerance breaks. For one month, check conservation with no extra filter, check that a filter the summary lacks forces detail, and check that an older summary refresh is disclosed or refused.

BuildTable.ai can be a candidate semantic layer for declaring when a query may use a summary. This article does not show that the current release already makes that choice or compares watermarks. Verify it on a real store-day summary and its order detail.

When a summary table and the detail disagree, which one should the semantic layer use?

A faster total is not always a valid substitute

Write three mismatches into the contract

The source of truth depends on the question

Two implementation paths

Counterexamples and acceptance

Public references

Build an AI-ready data foundation