Datasets and the four modes

A dashboard carries up to eight named datasets. Each one is a SQL query plus its declared dimensions and measures, and each independently chooses a mode: how its results are materialized into the file and how the browser answers a question about them.

This is the most consequential decision in a spec, because it is the one that decides whether a number stays correct when a viewer touches a filter.

The four modes

They form a ladder, in ascending power and cost:

cube < lattice < hybrid < rows

	cube	lattice	hybrid	rows
What ships	pre-aggregated rows, one per combination of dimension values	one precomputed cell per possible filter state	a lattice plus a row-level slice	the raw rows
How the browser answers	re-sums the rows in JavaScript	looks up the one matching cell	looks up, or recomputes from the rows when it has to	runs real SQL in DuckDB-WASM
Correct for	additive measures only: sum, count, min, max, and ratios of them	any exact aggregate, under single-select filters	any exact aggregate, including under multi-select and range filters	anything, including ad-hoc slicing you cannot enumerate
Filters	single-select, multi-select and range, all exact	single-select; multi-select and range only for a composable measure	single-select, plus multi-select and range on any measure	multi-select, ranges, anything
Engine in the browser	none	none	DuckDB, only when a filter needs it	DuckDB, always
Size	smallest	small, bounded by a cell ceiling	lattice plus rows	largest; the only mode that can offload
Costs you	nothing, but it refuses non-additive measures	every dimension must declare a bounded value set	the same bound, plus shipping rows as well	row-level bytes are world-readable, and DuckDB loads on every view

A one-line mnemonic that survives most conversations: a cube recomputes, a lattice remembers, rows re-query.

How the choice is actually made

It is mechanical, not a matter of taste. Note that the first question is about your measures, never about your filters:

1. Every measure additive? cube. Smallest, fastest, simplest. Filters do not enter into it: a cube answers multi-select and range filters exactly, because re-summing an additive measure over any subset of rows is still that measure. See below.
2. Any measure non-additive (a distinct count, an average, a median, a percentile), but every dimension has a small enumerable set of values and every filter is single-select? lattice. You get exact answers with no query engine in the browser and the file stays small.
3. Those same bounded dimensions, but a filter has to be multi-select or a range over a measure that cannot be composed from parts? hybrid.
4. A dimension you cannot bound, or a genuine need for row-level detail? rows.

You do not have to declare the mode. Leave it out and the server picks, then reports what it picked and why. But it will only ever pick cube or lattice: rows and hybrid are always an explicit opt-in, because both ship row-level bytes that are world-readable, and nothing should route your raw rows into a public file on your behalf.

If your dataset needs one of those two and you did not ask for it, the publish stops and tells you so, naming which of the two situations you are in and what to change.

Why cube refuses non-additive measures

A cube ships pre-aggregated rows and the browser re-sums whatever survives a filter. That works for sums, counts, minimums and maximums, because those compose: the total over two groups is genuinely derivable from the total of each.

A distinct count does not compose. Ship one row per region with a count(distinct customer_id) on it, select "all regions", and the browser adds them up, double-counting every customer who appears in two regions. A live measurement of exactly this shape reported 2,503 customers against a true 1,200.

So Dashies inspects a cube dataset's SQL and refuses to publish if it computes count(distinct ...), avg, a median, a percentile, stddev, variance, or mode(). The refusal names the construct it found. That refusal is the feature; the other three modes are how you get the measure anyway.

Measure correctness is the page on which measures are which, and on the ones no static check can catch.

A cube answers multi-select and range filters exactly

This surprises people, and getting it wrong is expensive in one specific direction, so it is worth stating on its own.

A multi-select or range filter is not a reason to leave cube. The runtime re-sums the additive cube over whatever subset of rows the filter leaves, and a sum over a subset is still that sum. Every measure in a cube is additive by construction, which is exactly the property that makes it composable, so there is nothing for a multi-value filter to break.

The server agrees, and will tell you so if you let it: leave mode off and the auto-selector checks your measures first. If they are all additive it returns cube immediately, without ever looking at your filter tiles. The multi-select question only arises further down the ladder, when deciding whether a non-additive measure can live in a lattice.

The reason to be careful here is the cost of the wrong answer. Reaching for hybrid or rows to get a multi-select filter you already had means shipping row-level, world-readable bytes and loading a query engine in every viewer's browser, buying nothing. And nothing will tell you: the dashboard works, so the waste is invisible. If you have an additive measure and you are reading this because of a filter, stay on cube.

How a lattice stays exact

Instead of storing one row per region and asking the browser to combine them, a lattice stores one row per possible filter state, with the answer already computed exactly by the warehouse.

The SQL is a GROUP BY CUBE over exactly the declared dimensions, projecting a GROUPING(<dim>) AS __g_<dim> flag column for each one:

select region, plan,
       grouping(region) as __g_region,
       grouping(plan) as __g_plan,
       count(distinct customer_id) as customers
from orders
group by cube(region, plan)

GROUP BY CUBE(a, b) asks the warehouse for every combination of "group by this" and "roll this up": four groupings for two dimensions. A 1 flag means that dimension is rolled up in that row.

region	plan	customers	__g_region	__g_plan
us	pro	120	0	0
us	(all)	500	0	1
(all)	pro	350	1	0
(all)	(all)	1000	1	1

The last row is the point. A cube dataset holding one row per region would have answered "all regions" by adding 500 and 400 and 300 to get 1,200. The lattice holds the warehouse's own answer for that state: 1,000.

When a viewer picks a filter, the browser does not aggregate. It looks up the one precomputed row for that filter state, synchronously, with no query engine and no network call. A median stays a real median; a distinct count stays a real distinct count.

The flag columns are why it keys on GROUPING() and never on a NULL: without them, a rolled-up blank is indistinguishable from a genuine null value in your data.

Two constraints follow from the shape, and both are enforced at publish:

• Multi-select and range filters are limited, and a cube has no such limit. The lattice precomputes "region is us" and "region is anything". It does not precompute "region in (us, eu)", because that is a different population and not a cell. Where the measure is composable the runtime combines the cells it does have; where it is not, the answer is unavailable and you need hybrid. This is the constraint that makes the ladder counter-intuitive: a cube handles those same filters without complaint, because a sum over a subset is still a sum.
• Approximate aggregates are rejected. A lattice promises each cell is exact, which is true of a real median and false of an approximate quantile. On one real BigQuery population, two cells of the same lattice disagreed about the same rows (22,518 and 22,164, against a true 22,785) precisely because the aggregate was approximate. BigQuery has no exact percentile function at all, so an author reaching for a median there lands on the approximate form because it is the only thing that runs. On BigQuery, percentiles need rows.

The lattice cell budget

Cell count is the product of each dimension's number of values plus one: for cardinalities c1 to cN it is (c1 + 1) * (c2 + 1) * ... * (cN + 1), where each plus-one is that dimension's rolled-up state.

Multiply, not add. That is why low cardinality is a hard requirement rather than a style preference:

Dimensions	Cardinalities	Cells
region	4	5
region, plan	4, 3	20
region, plan, month	4, 3, 12	260
region, plan, month, channel	4, 3, 12, 6	1,820
region, plan, month, channel, device	4, 3, 12, 6, 4	9,100
the same plus one 50-value dimension	..., 50	464,100, refused

The ceiling is 50,000 cells per lattice dataset, and it is checked at publish from your declared value sets, before any SQL runs. So you find out while authoring rather than on the first refresh.

The rule of thumb: adding a dimension multiplies; adding one more value to an existing dimension adds. Past roughly 20 values, bucket the dimension (top 10 plus "other") or move to rows.

Sizes and ceilings

Every cube, lattice and hybrid dataset rides inside the file, and they share one budget across the whole dashboard:

Ceiling	Value	Applies to
Data island	8 MiB, and 100,000 rows	all inline datasets, summed
Compiled publish body	5 MiB	the whole file; usually the one that binds first
Lattice cells	50,000	one lattice dataset
Datasets	8	one dashboard
Parquet-backed datasets	2	one dashboard

Two engine-specific facts change these:

• SQL Server is much tighter. Its executor caps a single result at 5,000 rows and 2,000,000 bytes, twenty times fewer rows than every other engine. A dataset that is comfortable on Postgres or Snowflake can simply be refused there, and the remedy is a coarser grain, a narrower window, or a lower-cardinality dimension.
• BigQuery, Snowflake, Redshift and Databricks have no byte cap at execution time. They refuse on row count before fetching. The island and publish ceilings above still bind, and they usually bind first because they measure real bytes. So do not coarsen a BigQuery grain to fit a number that does not apply to it.

Offloading to Parquet

A rows dataset on a warehouse connection can declare that its rows live in a Parquet object instead of inside the file. The runtime range-reads that object, pulling only the parts it needs.

This is the single most misread feature in Dashies, so the precise statement:

Parquet does not raise what you may declare. It raises what the dataset may grow into.

At publish, the SQL you write is still seeded through the same executor, so it must still return no more than 100,000 rows. That is the same row cap as an inline dataset, and declaring Parquet buys you nothing there. What it buys is real, and is usually the reason to reach for it:

• the dataset leaves the shared 8 MiB island budget, so every other dataset gets all of it
• between publishes it may grow to 256 MiB and 50,000,000 rows
• the browser range-reads instead of downloading the whole thing

The trade-offs: refresh for that dashboard becomes asynchronous and completes in minutes rather than immediately, the dataset publishes empty and reads "Updating" until the first refresh lands, and SQL Server has no Parquet path at all, so declaring it there is refused.

The composite model

The shape a large report should take is not one heavy dataset. It is two:

• a lattice carrying the aggregates, exact under every single-select filter, megabyte-scale, with no engine to load
• a Parquet-backed rows dataset carrying the drill-through detail behind it

That is why the limit is two Parquet datasets per dashboard: it is sized for exactly this. If you have used Power BI's composite models, it is the same idea, compiled into a static file.

Next

Measure correctness is what the mode ladder is protecting you from. Designing the cube is how to write SQL that stays inside these budgets.