SQL dialect notes

What changes about your cube SQL per warehouse engine: identifiers, date bucketing, percentiles, and the traps that mis-bucket silently.

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 and is not copied here: one statement of each fact, in one place. Coverage is not uniform, and the percentiles section says where it runs out.

What Dashies requires of every engine

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

One statement, and it reads. A single SELECT (or WITH ... SELECT). Anything that writes is refused.
Every output column is declared, as a dimension or a measure, and the keys match the output names.
A bounded result, inside the row and byte caps for that engine. See Limits.
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`	`bigquery`	`snowflake`	`redshift`	`databricks`	`mssql`
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 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:

The Postgres `AT TIME ZONE` operand trap

On a timestamp with time zone, the single form converts:

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:

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.

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.

BigQuery has no aggregate percentile function, and the obvious substitute is wrong twice

Never back a declared median or percentile with APPROX_QUANTILES.

It is approximate, which already breaks the exactness a lattice cell promises.
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.

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.

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.

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.