validate_cube_sql

Runs a candidate cube SQL once, read-only, against the connection's surface, and returns the column shape, the exact row count, a capped sample of rows, advisories, and a size recommendation.

The SQL runs under exactly the confinement the refresh cron will later enforce: a single read-only statement, a row and byte cap, and a statement timeout. A query that validates here keeps running unattended.

Behaviour hints for clients: read-only, not open-world.

Parameters

Parameter	Type	Required	Notes
sql	string	yes	A single read-only SELECT or WITH ... SELECT. Use relative time windows, never hardcoded dates, since it re-runs unattended.
connection	string	no	self (the default) or a warehouse connection id.
mode	string	no	cube, lattice, rows or hybrid. Selects which advisory rule applies. Omit for a single-mode cube.
stock_columns	array	no	Columns in this SQL that hold a point-in-time stock rather than a per-period flow. Advisory only.
echo_rows	integer	no	How many rows to echo back, minimum 0. Omit for the default sample.

Caps

Cap	Value
Rows requested	100000
Rows echoed	200
Bytes echoed	8192
Statement budget, self	8000 ms
Statement budget, warehouse	15 s

The row cap and the byte cap bind independently, whichever comes first. row_count stays exact either way, and rows_truncated tells you which applied.

Returns

Cube SQL is valid for connection "self".
Rows: 96
Columns:
- month: string
- plan: string
- revenue_usd: number

Sample (first 5 of 96 returned):
  {"month":"2026-07-01","plan":"pro","revenue_usd":41230}

The 96 cube rows are in the JSON block below - paste them into the data island as the initial cube, and use the columns above to map your dimensions/measures.

BEGIN_JSON
{"connection":"self","ok":true,"row_count":96,"rows_truncated":false,"columns":[],"rows":[],"warnings":[],"size_band":"small","recommended_mode":"inline"}
END_JSON

When truncated, that guidance line reads instead: The JSON block below carries the first 200 of <n> cube rows - the full set is too large to inline here.

Advisories

Two channels, and the difference matters if you are scripting:

• Warning: ... lines have a machine-readable counterpart in the warnings array.
• Note: ... lines are text-only. The manifest v3 grain-compiler blessing is a note.

The advisory that fires most often names non-additive aggregates, and there are two of them with different remedies. Which one you get depends on whether you passed mode, so do not go looking for the other one.

With mode: cube, the remedy is a different v4 dataset mode:

For a v4 "cube" dataset this SQL computes non-additive aggregates (avg): a cube dataset re-sums pre-aggregated rows in JS, so these go silently wrong under viewer filters. Use mode "lattice" (exact per cell, no engine to load) when the dimensions are low-cardinality, or mode "rows" (row-level + DuckDB) for row-level detail.

With mode omitted, the advisory stays version-agnostic and the remedy is a manifest version:

This SQL computes non-additive aggregates (avg); it requires manifest v2 (row-level + DuckDB). A manifest v1 (additive cube) publish rejects it: the v1 page re-sums pre-aggregated rows in JS, which silently mis-aggregates non-additive values under viewer filters. For v2, have cube_sql return the underlying rows and declare the metric as a v2 measure (agg: 'count_distinct' / 'median' / 'percentile_cont' / 'avg') so the engine recomputes it with real SQL.

Both are Warning: lines with an entry in warnings. A GROUP BY CUBE shape with per-dimension GROUPING(<dim>) AS __g_<dim> tags is recognised and gets a Note: instead of either, because those same aggregates are exact per lattice cell.

A separate Warning: fires on approximate aggregates under mode: lattice or mode: hybrid, and it is a refusal-in-waiting rather than advice: a lattice REJECTS them at publish, because its guarantee is that every precomputed cell is the exact aggregate for its filter state.

The size recommendation

Field	Values
size_band	small, big, extreme
recommended_mode	inline, parquet, reject

Act on recommended_mode. inline means bake the rows into the island. parquet means publish a row-level dataset with data.mode: parquet so the scheduled refresh offloads the rows. reject means the cube is too large to serve as it stands, so narrow the window or coarsen the grain.

Errors

The shared connection rules, plus:

Condition	Text
Missing SQL	sql is required
Bad mode	mode must be one of cube/rows/lattice/hybrid - the v4 dataset mode to validate this SQL for (omit it for a single-mode v1/v2/v3 cube).
Bad stock columns	stock_columns must be an array of non-empty column names - the columns in this SQL that hold a point-in-time STOCK (a level such as ARR, headcount, a balance) rather than a per-period flow.
Bad echo count	echo_rows must be a non-negative integer - how many result rows to echo back (0 for none; capped at 200 and by the result byte budget either way).
No columns came back	Cube SQL ran but returned no columns. A cube needs the grain dimensions plus at least one measure - check your SELECT and GROUP BY.
An unclassified refusal	Cube SQL rejected [<sqlstate>]: <raw message>

A timeout is classified and names both budgets, so you can tell which one you hit:

Cube SQL timed out against the connection (authoring runs on a 15s budget for a warehouse connection, 8s for self) - it moved more data than the budget allows.

For BigQuery, Snowflake, Redshift and Databricks the engine's own message is surfaced verbatim, falling back to BigQuery cube validation failed, Snowflake cube validation failed, Redshift cube validation failed or Databricks cube validation failed when the engine gives nothing usable.

A cube too large to inline exceeds the row or byte cap and comes back as a rejection whose message carries the same size recommendation, labelled an estimate because validation stopped at the cap.

Rate limited at 120 calls per user per minute.

Scope

Connection-scoped, so there is no workspace argument. It accepts a personal connection or one belonging to a workspace you are a current member of. A warehouse connection requires a paid plan; self does not.

Check it worked

Also confirm rows_truncated is false before pasting rows anywhere, and that row_count matches what you expect from the grain you designed.