---
title: SQL dialect notes
description: 'What changes about your cube SQL per warehouse engine: identifiers, date bucketing, percentiles, and the traps that mis-bucket silently.'
updated: 2026-08-05
---

Dashies runs your SQL on your warehouse, unchanged. It never rewrites your query
into a portable subset, so the dialect you write is the warehouse's own.

This page is the cross-engine view. Where an idiom has a runnable example, it
lives on that engine's own page under
[Connect a warehouse](/guides/connect-warehouse) and is not copied here: one
statement of each fact, in one place. Coverage is not uniform, and the
[percentiles section](#percentiles-and-medians) says where it runs out.

## What Dashies requires of every engine

Whatever the dialect, a dataset's SQL has to satisfy the same rules:

1. **One statement, and it reads.** A single `SELECT` (or `WITH ... SELECT`).
   Anything that writes is refused.
2. **Every output column is declared**, as a dimension or a measure, and the keys
   match the output names.
3. **A bounded result**, inside the row and byte caps for that engine. See
   [Limits](/reference/limits#per-engine-cube-caps).
4. **A top-level `ORDER BY` when a `rows` or `hybrid` dataset declares
   `rows_window`.** That is a hard error: without an ordering, the window the
   dashboard presents as a defined slice is an arbitrary subset that changes
   between refreshes. Ordering every dataset is the authoring rule regardless,
   because deterministic ordering is what lets a refresh that changed nothing
   produce identical bytes.

## The matrix

Each column links to that engine's page.

| | [`postgres`](/guides/connect-warehouse/postgres) | [`bigquery`](/guides/connect-warehouse/bigquery) | [`snowflake`](/guides/connect-warehouse/snowflake) | [`redshift`](/guides/connect-warehouse/redshift) | [`databricks`](/guides/connect-warehouse/databricks) | [`mssql`](/guides/connect-warehouse/sqlserver) |
|---|---|---|---|---|---|---|
| Dialect | PostgreSQL | GoogleSQL | Snowflake SQL | a PostgreSQL dialect | Spark SQL, not a PostgreSQL dialect | T-SQL |
| Table reference | `schema.table` | `` `project.dataset.table` `` | `DATABASE.SCHEMA.TABLE` | `schema.table` | `` `catalog`.`schema`.`table` `` | `schema.table`, `[brackets]` where quoting is needed |
| Unquoted output alias | folded to lower case | preserved | folded to upper case | depends on cluster settings | preserved | preserved |
| Parquet offload past the inline ceiling | yes | yes | yes | yes | yes | no |

The built-in `self` connection is our own PostgreSQL, restricted to a no-PII
metrics view, and it has **no Parquet offload**: an over-ceiling `self` cube is
refused rather than offloaded.

**Redshift's alias case depends on two cluster parameters** rather than on how you
write the query. The
[Redshift page](/guides/connect-warehouse/redshift#dialect-notes) carries a
one-line probe that answers both at once.

**Letter-case is reconciled for you on every engine.** An output column whose
name differs from your declared key only by case is renamed to the declared form
before it is stored, so `sum(amount) as revenue` is correct against a measure
named `revenue` whichever way the warehouse folds it. What is refused, loudly and
naming both, is **two** output columns that differ only by case landing on one
declared key.

## Bucket dates in your business time zone, in the SQL

**Never rely on a session time zone.** A refresh runs unattended, on our
connection rather than yours, and **nothing on the execution path ever sets a
session zone**. A bare `date_trunc('month', ts)` therefore buckets in whatever
zone that session happens to carry, which is whatever your warehouse or its
account is configured for. We neither set it nor read it, so it is not something
a dashboard should depend on, and a change to it moves your month boundaries with
no other symptom. **Name the zone in the query.**

The `timezone` field on the spec's `source` anchors the *schedule*, not the
query. Nothing propagates it into your SQL.

The conversion idiom differs per engine and each is shown, runnable, on that
engine's page. Two are worth stating here because they mislead rather than fail:

:::danger{title="The Postgres `AT TIME ZONE` operand trap"}
On a `timestamp with time zone`, the single form converts:

```sql
date_trunc('month', ts AT TIME ZONE 'America/Los_Angeles')::date
```

On a naive `timestamp` that happens to store UTC, you need the double form,
labelling UTC first and converting second:

```sql
date_trunc('month', ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles')::date
```

**The single form on a naive timestamp silently mis-buckets.** Check the column
type first; introspection reports it. Publish and validation flag a single
`AT TIME ZONE` as ambiguous, but only as a warning: the server cannot read the
operand's type, so it cannot tell which form you needed.
:::

:::warning{title="SQL Server takes Windows zone names"}
`mssql` uses names such as `Pacific Standard Time`, not IANA names such as
`America/Los_Angeles`. It also needs the double `AT TIME ZONE` and a cast back to
`datetime2`, because `AT TIME ZONE` returns a `datetimeoffset` the island reader
does not accept. A Linux-hosted instance may accept IANA names; check
`select name from sys.time_zone_info` for what your server takes.
:::

## Percentiles and medians

A declared `median` or `percentile_cont` measure has to be **exact**: a lattice
cell promises an exact value for its filter state, and a `hybrid` or `rows`
dataset recomputes from the underlying rows.

:::danger{title="BigQuery has no aggregate percentile function, and the obvious substitute is wrong twice"}
Never back a declared median or percentile with `APPROX_QUANTILES`.

1. **It is approximate**, which already breaks the exactness a lattice cell
   promises.
2. **Its answer changes with the number of dimensions in the cube.** Measured on
   a live 300,000-row table, the same population returned 22518 from a
   two-dimension lattice and 22164 from a three-dimension one, against a true
   median of 22785. Two cells of one lattice can disagree about the same rows.

The second point is what makes it unusable rather than merely imprecise: a reader
who filters down and back up gets two different medians for the same population.
:::

Three engines carry a worked exact-median example: PostgreSQL, Snowflake and
BigQuery, the last of which is the refusal above rather than an idiom.

:::note{title="Redshift, Databricks and SQL Server have no median example here, and that is a real gap"}
There is no runnable exact-median idiom on those three engine pages, and this
page does not invent one. Candidate forms do exist in an internal authoring
reference, and **every one of them is labelled "Not verified - confirm before
relying on it"** - so publishing them here would turn an unverified note into a
contract. Until each is confirmed against a live warehouse, the honest answer is
that we have not established it.

If you need an exact median on one of those three, the route below works
regardless of dialect.
:::

Where an engine has no exact aggregate percentile, or where you cannot confirm
one, the way out is a `rows` or `hybrid` dataset, which recomputes the percentile
from the underlying rows under whatever filter is on screen. See
[Datasets and the four modes](/concepts/dataset-modes).

## Nested and repeated columns multiply rows

Three engines carry column types that hold more than one value, and each has an
operator that expands one into many rows:

| Engine | Types | Expanding operator |
|---|---|---|
| `bigquery` | `ARRAY`, `STRUCT` | `cross join unnest(...)` |
| `snowflake` | `VARIANT`, `OBJECT`, `ARRAY` | `lateral flatten` |
| `databricks` | `ARRAY`, `MAP`, `STRUCT` | `explode` |

**Nothing rejects this.** The cube runs, publishes, refreshes, and every measure
is silently multiplied by the fan-out. Aggregate back to the grain you meant, and
cross-check against an independent aggregate over the un-joined base table:
[Verify your numbers](/guides/verify-your-numbers).

## Types that need care in the island

| Engine | What to watch |
|---|---|
| `bigquery` | A `TIMESTAMP` arrives as an ISO-8601 UTC string rounded to milliseconds. `DATE`, `DATETIME` and `TIME` arrive verbatim, the last two keeping microseconds. |
| `databricks` | A `TIMESTAMP` arrives as an ISO-8601 UTC string with a `T` and a trailing `Z`. Big integers keep full precision, as strings. |
| `mssql` | A `tinyint` above 127 fails the island read: write `cast(<measure> as int)`, and cast a lattice's grouping flags the same way. `decimal`, `numeric` and `money` pass through a floating-point hop good for about 15 to 16 significant digits, so cast money to integer units if you need exactness. `datetime` and `datetime2` truncate to whole seconds. |

Format and bucket in SQL rather than parsing the raw text on the page.

## Check it worked

Ask your AI tool to validate the cube against your connection before publishing.
The reply carries the exact row count, the column names as the warehouse returned
them, and a sample. **That is the cheapest place to catch a fan-out**: a row count
several times what you expected is the whole signal.