Calculated follows detail
Materialized has its own refresh
Say whether the filter still applies
The same name needs a version
Stale results must be visible
A shared name does not hide two implementations
“Sales this month” can be calculated from detail at query time, or read from a table built overnight. A refund at 10:00 is in detail and not in the monthly table. If both are called sales this month, the dashboard and the query answer will disagree. Model them as two implementations of one metric, each with a watermark: detail’s last complete time, and the last successful refresh. Pick a default in writing. Do not pick whichever returns first.
dbt separates a metric’s definition from how it is landed in the warehouse. A shared name means the business meaning was intended to match. Different landings can still differ in filters and freshness.
Materializing a metric can drop filters and grain
If the monthly table stores only month and channel, a filter on loyalty tier or on one coupon cannot use that implementation. The query must calculate from detail, or refuse. Returning the monthly number while mentioning loyalty in the sentence is wrong. Calculation is slower, and the filter still exists. The materialized path is faster, and its filters are only the dimensions that were grouped in advance.
Grain is frozen too. A monthly table cannot answer “month to date through this morning” unless a daily table exists. Using the full-month materialization as month-to-date mixes implementations. Kimball’s grain has to be stated on each implementation, not only on the display name.
A failed refresh must not look current
After a failed job, the table still holds the old number. If the query layer only checks that the table exists, yesterday or last week is served as today’s metric. Each materialized implementation needs a success time, the business time it covers, and a failure state. On failure, calculate from detail, or return the old number and mark it stale. A silent old number is an incident.
Detail can be stale too, if it stopped last night. There are two watermarks. Show the one this answer actually used. A star schema does not make a refresh succeed. It only shapes the tables.
Two default strategies
Path one calculates for ad hoc questions and uses the materialized table for the morning dashboard, with both watermarks visible. It suits teams that want a fast board and occasional extra filters. Path two prefers the materialized table when it is fresh and the filter can be pushed down, and otherwise calculates, saying “answered from detail.” It suits teams that want one name. The rewrite has to be real. A plan in the log with the old number still on screen does not count.
Do not cache by metric name and month alone. A different loyalty filter, identity, or watermark cannot share one materialized result.
Counterexamples and acceptance
Counterexamples: a refund is in detail and the answer still returns the overnight month table; a loyalty filter does not change the number; a failed refresh has no stale mark; the month table is used as month-to-date. Insert a known refund into detail and confirm that calculation changes while the materialized figure does not, until a successful refresh. Add a filter the materialized grain lacks, and confirm a rewrite or a refusal. Fail a refresh and confirm the answer is not silently old.
BuildTable.ai can be a candidate semantic layer for registering calculated and materialized implementations. This article does not show that the current release already compares watermarks or rewrites when a filter cannot be pushed down. Verify it on one real refresh.
Public references
Build an AI-ready data foundation
Contact us to discuss your data modeling scenario and deployment support.
Contact us