---
title: Designing the cube
description: Which statement shape to write depends on where the numbers live. Plus grain, cardinality, timezones and ordering, for SQL that runs unattended for a year.
updated: 2026-09-20
---

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](/concepts/measure-correctness) covers whether the numbers
mean what you think. This page covers the shape around them.

:::warning{title="Which shape you write, and what decides it"}
**Against a warehouse connection or the workspace's uploaded-file source**, the
statement returns **one row per underlying record**, carrying the columns each
number is worked out from, and each number is declared in the spec rather than
worked out in the SQL. Dashies holds those rows and works the numbers out when a
reader opens the page, so a statement that has already aggregated gets aggregated
a second time.

**Against the built-in `self` connection**, the statement **groups and
aggregates**: one row per combination of the things you group by, with the
numbers already worked out, and those rows ship inside the published file.

**Nothing refuses the wrong one.** A plain total survives the wrong grain, so a
warehouse dataset written the grouped way reads as correct until somebody adds a
count, and then the count is the number of groups you made rather than the number
of records.

This page calls the first the **held shape** and the second the **in-file
shape**, after where the numbers end up, because that is what actually decides
which one you write. The connection decides it in every case but one: on a
**free** plan a dashboard built on the shared Dashies sample data connection
carries its numbers inside the page, so it takes the in-file shape even though
that connection is a warehouse. See
[Datasets and the four modes](/concepts/dataset-modes).

Every section below names which shape it is about wherever the two differ.
:::

## Grain

The **grain** is what one row of the result represents, 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 column the statement outputs.** A field that is not in the
output does not exist as far as the dashboard is concerned, and nothing later
puts it back. You cannot filter by `plan` on a dataset whose statement never
outputs `plan`.

**How it gets into the output is the half that forks.**

**Against a warehouse connection or the uploaded-file source**, it is a plain
column of the records you return. Return one row per underlying record and
declare each number in the spec rather than working it out in the SQL, because
Dashies holds those rows and works the numbers out per request. Grouping to what
the dashboard **reports at** hands it a summary to summarize.

Grouping to the thing each row is **about** is a different move and is fine: one
row per order, carrying `count(line_items.id) as lines` for that order. Declare
`lines` as a total rather than as a count, because your SQL has already done the
counting and from Dashies' side that column is now just a number to add up.
Measured on six line items across three orders, the same column comes back as 6
declared as a total and as 3 declared as a count.

If you are porting a dashboard that already groups, the test is: **does the
calculation give the same answer when it is done twice?** A total does. A
smallest does. A largest does. **A count does not, and it is the one to watch**,
because it is the number most likely to have been worked out already. Measured on
those same six line items, a total returns 6 at either grain while a count
returns 3 grouped by order and 2 grouped by region.

**Against the built-in `self` connection**, it is set by your `GROUP BY`. The
published page carries its numbers rather than going back for them, so the grain
the statement returns is the grain the dashboard reports at, and a column that was
aggregated away is gone.

The inverse is also true and easier to get wrong: **every column the query
outputs is carried**. On a `cube`, `lattice` or `hybrid` dataset an undeclared
column is refused at publish rather than quietly included, because it would be
bytes every viewer of the dashboard can read and nothing renders. Those three are
in-file materializations; see
[Datasets and the four modes](/concepts/dataset-modes).

Where the statement is **row-shaped this is a warning, not a refusal**, and the
publish proceeds. That is the case to watch, and a held-shape dataset is always
in it, because its statement returns records; on the in-file shape it is a `rows`
slice and a `hybrid`'s row-level half. So a clean publish is not evidence that no
stray column is being carried: read the warnings. On the in-file shape those
bytes are row-level and readable by every viewer, which is the more exposed half
of the rule.

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.

**The two shapes can also disagree for reasons that are not double-aggregation.**
[Connect SQL Server](/guides/connect-warehouse/sqlserver) measures one: under a
case-insensitive collation your server folds `acme` and `ACME` into one group,
and Dashies, which compares text by its bytes, keeps them as two.

## Keep dimensions low-cardinality

Every filter renders as a menu of a dimension's distinct values, on either shape.
A dimension with 5,000 values gives you an unusable dropdown, and on a
[lattice](/concepts/dataset-modes) it multiplies the cell count until the dataset
is refused outright.

**Row count is where the two shapes part.** On the in-file shape, aim for a few
hundred to a few thousand rows in a dataset, not tens of thousands: those rows
ship inside the file and share one budget with every other inline dataset. On the
held shape the records themselves are what you return, so the row count is
whatever the records are, bounded by what the extract may carry between refreshes
rather than by anything at publish. The ceilings are in
[Limits](/reference/limits). Do not coarsen that grain to hit an inline number,
because that is the pre-aggregation this page opens by warning about.

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. It works on either shape, because it
rewrites a column's values at the grain you already have rather than collapsing
rows.

## Bucket dates in the business timezone

This section is the same on either shape. 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:

```sql
-- 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
```

```sql
-- 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
```

Both examples group, because bucketing a timestamp is what they are about. On the
held shape the same conversion goes in the `SELECT` list as a column of the
records, with no `GROUP BY` under it.

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, and publishing does not warn about one: Dashies asks the
warehouse for the type of every value a bucket reads, and stays quiet when each
is a `DATE` taken from a `DATE` column, including through an aggregate or a CTE.
Where it cannot establish that, such as a bucket over a date cast from a timestamp,
the warning stays. A day bucket written as a cast of a timestamp, such as
`date(ts)`, is not checked at all, so convert the timestamp to the business
timezone before you cast it.

## `ORDER BY` on every in-file dataset

**This section is about the in-file shape**, where the order the rows arrive in
is the order tiles draw in, so the `ORDER BY` in your statement is the one that
decides it. The held shape is at the end of the section, and the short version is
that this does not apply there.

Write an explicit `ORDER BY` on every in-file 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:

```sql
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. A windowed row slice is an in-file
shape too: a held-shape dataset is refused a row window rather than bounded by
one.

**On the held shape, your statement's `ORDER BY` is not what a tile draws.**
Dashies queries the rows it holds once per request and orders that answer itself,
so ordering is decided there rather than in your SQL. Bound the row set with a
`WHERE` window instead; that is what carries over.

## 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, on either shape.
- **Relative time windows, never hardcoded dates.** A hardcoded window is correct
  on the day it is published and progressively more wrong afterwards, and nothing
  will tell you. **What the window is relative TO forks.** On the held shape,
  anchor it to the data's own newest complete period rather than to the wall
  clock: take the anchor from `(select max(<date>) from <source>)` and measure the
  window back from there. **Excluding the partial newest period is a second
  predicate**, not something the lower bound does for you. A mart's data ends at
  its last complete period, so a wall-clock window is short by that gap every day,
  and on a source that has stopped being rebuilt it drains to nothing while every
  number left on the page stays plausible. The wall clock is only the right anchor
  where the source is genuinely live, which `self`, Dashies' own metrics view, is:
  `current_date - interval '12 months'`.
- **Bound the result.** On the in-file shape 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. **On the held shape nothing at
  publish bounds it for you**: the seed is deliberately limited there and a short
  read is the intended outcome rather than a truncation, so what binds is the
  extract's own ceilings. Both sets are in [Limits](/reference/limits).
- **Leave anything sensitive out of the statement's output.** That is the rule on
  either shape and only the mechanism differs. On the in-file shape the data is
  embedded verbatim in the file, so every viewer gets all of it whatever the tiles
  show or the filters hide, and that is stricter rather than looser on a row-level
  dataset, where every column you select is shipped as-is. On the held shape the
  rows stay with Dashies, and every column the dataset declares is one the page can
  ask Dashies for, so every reader can still reach it. Its viewers are you, or your
  workspace's members - never the public - but that is an audience, not a filter:
  no personal data, no raw rows you would not hand that audience, and no cells
  small enough to re-identify someone.
- **Row-level security does not change that bullet.** On an Enterprise workspace a
  held-shape dataset can filter its **rows** per viewer by one declared column,
  which never hides a **column** from somebody entitled to any row at all. It
  decides who gets which rows; it is not a way to carry a field nobody should see.
  See [Row-level security](/reference/spec#row-level-security).
- **Aggregating the sensitive thing away is an in-file remedy.** On the held shape
  it is the pre-aggregation this page opens by warning about, so drop the column,
  or coarsen its values at record grain, rather than collapsing the statement to a
  summary.

## Designing a lattice specifically

A [lattice](/concepts/dataset-modes) is an in-file materialization, so a
held-shape dataset cannot carry one and this section is about the in-file shape
alone. It 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](/concepts/connections-and-scope) is where the query runs.