--- title: validate_cube_sql description: Run candidate cube SQL once, read-only, and report columns, row count, advisories and a size recommendation. Parameters and errors. updated: 2026-09-20 tools: [validate_cube_sql] --- Runs a candidate cube SQL once, read-only, against the connection's surface, and returns the column shape, the row count, a capped sample of rows, advisories, and a size recommendation. The row count is exact except in one SQL Server case, described under Caps below, where it is a floor. 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`, and **the shape depends on the connection**. Against a warehouse or the uploaded-file source, return one row per underlying record and let Dashies work each number out, because it holds that data and aggregates it when a viewer opens the page: a statement that has already aggregated is aggregated a second time, and a count comes back as the number of groups you made rather than the number of records. Against `self`, group by your dimension keys and select one aggregate per measure. Never a hardcoded date, since it re-runs unattended, and anchor a relative window to the newest complete period the data itself holds rather than to the wall clock, except on `self`, whose dates are computed against today. | | `connection` | string | no | `self` (the default), a warehouse connection id, or the uploaded-file source id (which also needs `upload`). | | `upload` | string | no | Which uploaded file to read, as an upload id from [`create_file_upload`](/reference/mcp-tools/create-file-upload). **Required when `connection` is the workspace's uploaded-file source, and refused on any other connection** - a warehouse has no uploads. There is no default and the newest upload is not assumed: a later upload would otherwise silently change what this call answers and what a published dashboard reads, so omitting it is refused with the newest ready upload named for you to pass back. | | `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. | | `entitlement_key` | string | no | The column that decides who sees which rows, for a dataset you intend to give row-level security. Advisory only: it reports when that column already holds more than 100 distinct values in the rows this check read. | ## 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 on the echo bind independently, whichever comes first. Neither one changes `row_count`, and `rows_truncated` is `true` whenever the echoed sample is shorter than the rows the check read. **`row_count` is a floor rather than a total on a SQL Server connection whose data Dashies holds, when a statement fills this check's sample.** The check reads a bounded sample there and does not count past it, so `Rows` reads `at least ` and `row_count` is that ``. The text says so, reports the size as not measured (`size_band` and `recommended_mode` then describe only the rows read), and gives a `select count(*)` wrapper you can validate to learn the true total. A statement returning that many rows is expected on such a connection and is not a problem: the data stays outside the page and every refresh reads all of it. :::warning{title="rows is a sample, not the cube"} Never treat `rows` as the whole result. When `rows_truncated` is true you have a prefix, and baking that into a data island ships a truncated dashboard. Publish a small placeholder island and let the first refresh fill it, move a large row-level dataset to Parquet, or coarsen the grain until the whole cube inlines. **That last remedy is the in-file one.** A dataset whose rows stay with Dashies ships nothing inside the file, so there is nothing there to make inline, and coarsening its grain hands Dashies a summary to summarize: it holds those rows and works each number out when a reader opens the page, so a statement that has already aggregated is aggregated a second time. ::: :::tip{title="Pass echo_rows: 0 when publishing a spec"} On the spec publish path the rows are dead weight: the server seeds the data island from this same SQL, so you never paste rows anywhere. `echo_rows: 0` drops the sample while keeping the column shape, `row_count`, every advisory and the size recommendation. It can only make the result smaller: it is clamped to the server's row limit, and the byte budget still applies regardless. ::: ## Returns ```text 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} {"month":"2026-07-01","plan":"team","revenue_usd":18720} {"month":"2026-07-01","plan":"enterprise","revenue_usd":96400} {"month":"2026-06-01","plan":"pro","revenue_usd":39815} {"month":"2026-06-01","plan":"team","revenue_usd":17960} The 96 result rows are in the JSON block below, and the columns above are what your dimensions and measures can be built from. You do not need to copy the rows anywhere: the server re-runs this SQL itself when it publishes. Size: this result is small enough to travel inside the dashboard (size_band: small, this connection's cap is 100000 rows / 8000000 bytes) - nothing needs to change (recommended_mode: inline). BEGIN_JSON {"connection":"self","ok":true,"row_count":96,"rows_truncated":false,"columns":[...],"rows":[...],"warnings":[],"size_band":"small","recommended_mode":"inline"} END_JSON ``` The `columns` and `rows` arrays are shortened here. The real block carries every column and every echoed row. When `rows_truncated` is true and `echo_rows` was not 0, that guidance line reads instead: ```text The JSON block below carries the first of result rows - the full set is too large to return here, which is expected and fine. The server re-runs this SQL itself when it publishes, so the sample is only for you to read; use the columns above to see what your dimensions and measures can be built from. ``` On a SQL Server connection whose `row_count` is a floor (see Caps), the guidance line and the size line read instead: ```text The JSON block below carries of the row(s) this check read, and the statement returns at least . You do not need to copy any of them anywhere: the server re-runs this SQL itself when it publishes. Size: not measured. This check read row(s) of a result that has at least that many, so it cannot say how large the whole result is; the size_band and recommended_mode in the JSON block describe only the rows it read. ``` ### 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: ```text 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: ```text 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() AS __g_` 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. **`inline` and `parquet` are the in-file vocabulary, so read `recommended_mode` against your connection.** A dataset whose rows Dashies holds carries no island and declares no offload, so neither describes it. `reject` does reach it, and the remedy there is the narrower window rather than the coarser grain. See [Datasets](/reference/spec#datasets). ## Errors The shared [connection rules](/reference/mcp-tools/conventions#connections), 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 on `self` or Postgres | `Cube SQL rejected []: ` | **`check your SELECT and GROUP BY` is the server's own wording and it assumes the in-file shape.** Against a warehouse or the uploaded-file source the statement need not group at all: it returns one row per underlying record and each measure is declared rather than computed in the SQL. Where it does group, it groups back to one row per record rather than to the grain the dashboard reports at, so there is no reporting-grain `GROUP BY` for that sentence to be about, and what the refusal means there is that the statement returned no columns at all. On `self` and on a Postgres connection, a timeout is classified and names both budgets, so you can tell which one you hit: ```text 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. ``` **Whether a refusal carries your warehouse's own words depends on the engine.** - **BigQuery, Snowflake, Redshift and Databricks.** When the engine refuses the statement itself, you get the engine's own message with nothing added, cut at 2,000 characters. If that answer carries no message, the text is `BigQuery rejected the cube SQL`, `Snowflake rejected the cube SQL`, `Redshift rejected the cube SQL` or `Databricks rejected the cube SQL`. A failure that is not about the statement, such as a credential or reachability problem, gets a short Dashies sentence naming the kind of failure instead. - **SQL Server.** When SQL Server refuses the statement, you get its own message with nothing added, for example `Invalid column name 'amont'.` A refusal Dashies makes before the statement reaches SQL Server, such as a column type it cannot read, is a Dashies sentence. When the error number says the statement is not the problem (a lock timeout, a deadlock, a throttle, a database failing over), you get this instead, where `` is a short Dashies phrase such as `warehouse query timed out`: ```text SQL Server did not answer for a reason that is not in the SQL (, SQL Server error ). Nothing in the SQL needs to change; run it again. ``` When running it again will not help, the second sentence reads `Nothing in the SQL needs to change; re-test the connection in the Dashies web app (Data sources).` - **Postgres.** You get `Cube SQL rejected []: `, where `` is Postgres's own message with your connection's host, database name, user name and password (each one three characters or longer) replaced by `[redacted]` wherever they appear, for example `Cube SQL rejected [42703]: column "amont" does not exist`. When the check cannot use its own connection to your database and runs the statement through the connection's confined executor instead, Postgres's words do not come back: that executor passes on only the SQLSTATE and a class, so the text reads `Cube SQL rejected []: warehouse_execute_failed:`. A refusal the check recognises, such as a timeout, gets its own sentence instead. **One Databricks sentence gets a note appended.** When Databricks refuses with `The request could not be processed by the warehouse.` and nothing else, that sentence is still surfaced verbatim and a note follows it: the refusal is not necessarily about your SQL; if even `select 1` is refused the same way, the SQL warehouse may be unable to start, and an account that has run out of credits is one cause known to produce exactly this refusal; check in your Databricks workspace that the warehouse can start. Every other Databricks message is surfaced exactly as before. 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 any connection belonging to a workspace you are a current member of, plus the built-in `self` and the shared sample. A warehouse connection requires a paid plan; `self` does not. ## Check it worked :::danger{title="A green validate is not a correct number"} This tool proves the SQL runs, returns a shape, and fits the budget. It says nothing about whether the numbers are right. Before you trust a published figure, sum each additive measure over the cube and compare it against an independent aggregate over the un-joined base table. If those disagree, the cube is wrong and every refresh will keep it wrong. ::: Also confirm `rows_truncated` is `false` before pasting rows anywhere, and that `row_count` matches what you expect from the grain you designed.