Skip to content

Transform time versus query time metrics for a portfolio reporting stack

Data platformUpdated 2026-08-238 min read

In short

A transform time metric is computed during the warehouse build and stored as rows. A query time metric is generated from a definition each time a consumer asks. Precomputing is cheap to read and slow to change. Computing on request is flexible and pays compute on every dashboard load.

The portfolio dashboard takes 14 seconds to load, and the person who built it wants to precompute everything into a summary table. The person who owns the metric definitions wants to leave it alone, because last time somebody precomputed a measure it took three weeks to add a breakdown by hall.

Transform time versus query time metrics is the choice underneath that argument. Compute the number when the pipeline runs and store it, or store only the definition and compute the number when somebody asks. Both are defensible. For an event portfolio, the numbers involved are small enough that the honest answer is usually to do both, deliberately, with a rule for which measures go where.

Two places a number can be computed

At transform time, the pipeline runs a query during the build, aggregates the atomic fact rows, and writes the result. The consumer reads rows. A dashboard hitting a table of a few hundred rows returns instantly, and it returns the same thing at 9am and 5pm.

At query time, the warehouse stores only the atomic facts and the definition. When a consumer requests verified attendance by show and year, the semantic layer generates SQL from the definition and the warehouse computes it. Nothing is stale, because nothing was stored.

Cube's documentation, current in 2026, describes the middle position it takes on this, where the precomputed form is treated as a cache of the definition rather than a separate object: "a pre-aggregation is a condensed version of the source data", built from the same model, and stored in "Cube Store, a dedicated pre-aggregation storage layer." Freshness is handled mechanically, since "Cube uses a refresh key to check the freshness of the data; if a change in the refresh key is detected, the pre-aggregations are rebuilt." The documentation claims the approach "can reduce the size of the dataset by several orders of magnitude", which for an annual event portfolio is an understatement.

The 360 row aggregate, worked through

Count what a full precomputation of the headline layer actually costs in rows.

Eight shows, five years, so 40 editions. Nine headline measures: registrations, verified attendance, exhibitors, net square metres sold, rebooking rate, sessions, session attendance, leads captured, meetings held. Precomputing every measure for every edition is 40 times 9, which is 360 values, and stored long with one row per edition and measure that is 360 rows.

Three hundred and sixty rows is nothing. Refreshing it means one pass over the atomic facts, and on a scan fact of 40 editions averaging 38,900 rows, that pass reads 1,556,000 rows. At roughly 148 bytes a row the build reads about 230 megabytes, once, and writes a table you could email.

Add one dimension and the arithmetic changes character. By show day, three days per edition, the table becomes 1,080 rows. By hall as well, four halls, 4,320 rows. By exhibitor and day, at 580 exhibitors and three days, one edition comes to 15,660 rows, and across 40 editions that is 626,400 rows, or about 40 per cent of the size of the atomic fact table you were summarising. That is the moment precomputation stops being free, and it arrives faster than people expect because each dimension multiplies.

What does resolving at query time actually cost?

Take the same headline dashboard and serve it from definitions instead.

One load resolves nine measures against one edition partition of 38,900 scan rows, roughly 5.8 megabytes scanned. Forty users opening it fifteen times a day is 600 loads, so about 3.5 gigabytes a day, every day, for a set of numbers that changed once when the edition closed and will never change again.

Against that, the precomputed path reads 230 megabytes for the daily rebuild plus a few kilobytes per dashboard load, call it 260 megabytes a day in total. Thirteen times less, for identical output.

Google Cloud's BigQuery documentation, current in 2026, makes the underlying point about where scan cost comes from: pruned partitions "are not included when calculating the bytes scanned by the query" and "In general, partition pruning helps reduce query cost." A query time metric that filters to one edition prunes well and stays cheap. A query time metric that spans all 40 editions to compute a portfolio trend does not, and that is the query people run in a board meeting while everyone waits.

The rigidity tax on a precomputed aggregate

Everything above argues for precomputing, so here is the other side, which is the reason the argument at the top of this post happens at all.

A precomputed aggregate answers exactly the questions its grain anticipated. Ask for verified attendance by hall, when the aggregate holds it by edition, and there is no query that gets you there. You need a schema change, a rebuild, and a backfill of 40 editions before anyone can see the answer. Three weeks is a realistic estimate for that in an organisation where the pipeline is owned by one person who is also running two shows.

The same request against a query time definition is a filter. The layer generates different SQL, the warehouse does more work for a few seconds, and the answer appears in the meeting where it was asked for.

This is a real trade and it does not resolve to a rule. What it resolves to is a question about the measure: has anyone asked to slice this differently in the past two years? Registrations by edition, no. Leads by exhibitor by day, constantly. The first belongs in an aggregate and the second belongs in a definition, and the atomic rows underneath both stay exactly where they are either way.

Why is an event portfolio small enough to do both?

Because 360 rows is not a decision that needs defending, and because the two paths can share one definition.

The pattern that works is a narrow precomputed layer and a wide query time layer. Precompute the nine headline measures by edition, which is the 360 row table, and refresh it once after each load rather than nightly, because an event warehouse changes when an edition closes and stays still the rest of the year. Serve everything else at query time from the same definitions.

The critical constraint is that the aggregate has to be generated from the definition, and never hand written. If someone writes a summary query to populate the aggregate table, you now have two statements of what verified attendance means, they will diverge within a year, and the divergence will show up as a portfolio total that disagrees with the sum of its editions. Keeping one definition under review is what makes the dual path safe.

There is also a refresh trigger worth getting right. Time based refreshes make sense for daily businesses. An annual show has a natural trigger, which is the completion of the load for an edition, and hooking the aggregate rebuild to that event means the precomputed layer is never stale and never rebuilt for nothing.

That opens a third position worth knowing about, which is materialising per edition instead of for the whole portfolio at once. An edition is closed and immutable once its late data has settled, so its nine measures can be computed once and never touched again, while the edition currently in market stays on the query time path until it closes. Forty editions then hold 39 stored rows per measure, 351 in total, plus one live computation, and the full rebuild disappears entirely. That fits an annual cadence better than any schedule, because the trigger is the thing that actually changes.

Where the aggregate must never be the source of truth

One rule that is worth more than the rest of this post combined: no consumer gets to write the precomputed table by hand, and no consumer gets to treat it as authoritative.

Aggregates get patched. Somebody notices a wrong number in the summary before the show close and corrects it directly, and the atomic layer never learns. From then on the two disagree, the summary is the one on the dashboard, and the discrepancy is discovered by a person reconciling a board figure eight months later.

The precomputed layer should be droppable and rebuildable at any moment without a conversation. If you cannot drop it, something is being stored there that exists nowhere else, and that thing needs to move into the semantic layer or the fact tables before it goes missing.

Where this stops

The comparison above assumes the measures are additive over the dimensions you are aggregating, and several event measures are not.

Verified attendance is a distinct count of people. Precompute it by day and you cannot sum the days to get the edition, because a person who came on all three days would be counted three times. The same trap catches unique companies, repeat visitors and anything else counted distinctly. Aggregates of distinct counts have to be built at every grain you intend to serve, or served with a sketch structure that supports merging, and both of those are more work than the 360 row arithmetic suggests.

The second limit is that neither approach helps with a measure whose definition is unsettled. If three teams still disagree about which badges count, precomputing it fixes their disagreement in a table and computing it at query time fixes it in a definition. The disagreement survives both, and the sequence has to be definition first, delivery second, in the data platform and in the conversation.

Start by counting rows. Take the aggregate table your reporting currently reads from, count its rows and count the atomic rows behind it. If the ratio is small, you are paying rigidity for a saving you do not need, and moving that measure to a query time definition this week will cost you nothing and buy back a slice request you have been refusing.

Questions people ask about transform time versus query time metrics

What is the difference between a transform time and a query time metric?
A transform time metric is calculated when the pipeline runs and written into an aggregate table, so reading it costs almost nothing. A query time metric exists only as a definition, and the SQL to compute it is generated when a consumer requests it, so every request pays the compute.
Is a precomputed aggregate table still worth building?
Yes, for the small set of figures that many people read often and that almost never change shape: the portfolio roll-up, the headline measures per edition, the numbers on the front page of a post-show pack. The case weakens fast as the number of dimension combinations grows, because the table grows with it.
How do you keep a precomputed aggregate consistent with the definition?
Generate the aggregate from the same definition the query time path uses, so there is one statement of what the measure means and two ways of delivering it. An aggregate built by a separate hand written query is a second definition, and it will drift from the first one within a year.

Related reading

All data platform articles