Designing the cube

The query behind a dataset is not a query you run. It is a query that runs itself, on a schedule, with nobody watching, for as long as the dashboard exists. That changes what a good one looks like.

Measure correctness covers whether the numbers mean what you think. This page covers the shape around them.

Grain

The grain is what one row of the result represents. It is set by your GROUP BY, and it decides everything the dashboard can do.

The rule with no exceptions: anything you want to filter on, chart on, or break down by has to be a grouped dimension in the SQL. The published dashboard does not go back to the warehouse, so a column that was aggregated away is gone. You cannot filter by plan on data that was grouped only by month.

The inverse is also true and easier to get wrong: every column the query outputs ships. On a cube, lattice or hybrid dataset an undeclared column is refused at publish rather than quietly included, because on a public dashboard it would be world-readable bytes that nothing renders.

On a row-level slice this is a warning, not a refusal, and the publish proceeds. That is the case to watch: the bytes it lets through are row-level and world-readable, which is the more exposed half of the rule, so a rows or hybrid dataset needs you to actually read the warnings rather than trusting that a clean publish means no stray columns shipped.

A dataset needs at least one dimension. For a single all-time KPI, bucket by date anyway: it keeps the total exact and gives you a trend for free.

Keep dimensions low-cardinality

Every filter renders as a menu of a dimension's distinct values. A dimension with 5,000 values gives you an unusable dropdown and a bloated page, and on a lattice it multiplies the cell count until the dataset is refused outright.

Aim for a few hundred to a few thousand rows in a dataset, not tens of thousands.

For a genuinely high-cardinality category, the standard move is top-N plus other: keep the values that carry the volume, fold the rest into a single Other bucket in SQL, and drop the long tail from the grain. That is a design decision to make before you write the query, not an edit afterwards, because the remedy for a dataset that turns out too wide is usually to split it across several narrow datasets rather than to trim one.

Bucket dates in the business timezone

Bucket timestamps to the period the dashboard reports on: day, week, or month. Never ship a raw timestamp as a dimension.

Do the bucketing in the SQL, in the business timezone. A refresh runs with no session timezone, so a bare date_trunc('month', ts) buckets in UTC, which shifts every month and quarter boundary, and shifts by an hour across a daylight-saving change.

In PostgreSQL there is an operand trap worth knowing, because the wrong form is silently wrong rather than an error:

-- A `timestamp with time zone` column: the single form is correct.
select date_trunc('month', created_at at time zone 'America/Los_Angeles')::date as month,
       count(*) as signups
from users
group by 1
order by 1

-- A naive `timestamp` column storing UTC: label it UTC first, then convert.
select date_trunc('month', created_at at time zone 'UTC' at time zone 'America/Los_Angeles')::date as month,
       count(*) as signups
from users
group by 1
order by 1

The single form applied to a naive timestamp mis-buckets without complaining. Check the column's type before choosing; introspection reports it.

Other engines have their own spellings: BigQuery takes the zone as a third argument to timestamp_trunc, Redshift and Snowflake use convert_timezone, Databricks uses from_utc_timestamp, and SQL Server needs the double AT TIME ZONE cast back to datetime2.

Dashies checks what it can see and warns without blocking: a single AT TIME ZONE is reported as ambiguous, since it is right for one column type and wrong for the other, and a dataset that declares a timezone its SQL never names is reported as bucketing in some other zone. A column that is already a DATE needs no conversion at all.

ORDER BY on every dataset

Write an explicit ORDER BY on every dataset, in every mode.

This one deserves emphasis because nothing warns you. There is no publish error for a missing ORDER BY, so a wrong order looks deliberate.

Most tiles draw a dimension's members in the order the rows arrive. Without an explicit ordering, the axis order, a pie's slice order, a filter menu's order, and which members survive a limit on a matrix or heatmap are all whatever the engine happened to produce, and they can move between refreshes with nothing in the spec changing.

Two assumptions that would let you skip it are both wrong. A date dimension is not always sorted for you; some tiles treat it exactly like a category, a month filter menu among them. And changing the dataset mode does not excuse you either: on a lattice or hybrid, only the filter menu moves off the SQL order onto the declared value list.

There is one case where it is enforced rather than advised. A windowed row slice must end in a top-level ORDER BY, and it is rejected at publish without one, because the window means "which rows" and there is no such thing without an order. Give it a unique tiebreak, so two rows with the same timestamp cannot swap between refreshes:

select created_at, account_id, amount
from transactions
where created_at >= current_date - interval '90 days'
order by created_at desc, id desc

The ordering has to be the result order. An ORDER BY inside a subquery, a CTE body, or a window function does not count.

Write it to survive a year

• One read-only SELECT per dataset. Not multiple statements, no DML, no DDL. This is enforced by the executor, not merely requested.
• Relative time windows, never hardcoded dates. current_date - interval '12 months', not '2026-01-01'. A hardcoded window is correct on the day it is published and progressively more wrong afterwards, and nothing will tell you.
• Bound the result. The executor refuses an oversized result rather than truncating it, so a query that grows past its ceiling starts failing rather than starting to lie. That is the right behaviour, and it is still a broken dashboard, so leave headroom.
• Aggregate away anything sensitive. A published dashboard is world-readable and its data is embedded verbatim in the file. No personal data, no raw rows you would not publish, and no cells small enough to re-identify someone. This is stricter rather than looser on a row-level dataset, where every column you select is shipped as-is.

Designing a lattice specifically

A lattice adds two shape rules on top of everything above:

• The CUBE arguments must be plain columns, named exactly as the dimension keys. Bucket or derive a dimension in an inner query first, then group by the resulting column. This keeps the powerset portable across engines and sidesteps their disagreements about grouping by an alias.
• Every dimension must declare its bound: a value list for a category, a bucket count for a date. That declaration is what makes the size predictable before any SQL runs.

Keep the grand-total cell. If a HAVING clause or a filtered source strips the row where everything is rolled up, the unfiltered dashboard boots blank, and the publish will tell you so.

Next

Connections and scope is where the query runs.