# SQL reference

> The SQL dialect, schemas and naming, the columns sanda adds, what sanda sql allows and refuses, limits, and worked example queries.

Your warehouse speaks PostgreSQL, so everything here is ordinary PostgreSQL. This page is the lookup for the parts that are sanda's: which schemas exist and what they are called, the columns sanda adds, exactly what each place you can type SQL accepts and refuses, the limits, and examples that run against a sanda warehouse as it is laid out.

For a walk-through of the shell itself, see [sanda sql](https://docs.sanda-os.com.au/warehouse/sql).

## Dialect

The dialect is PostgreSQL. Common table expressions, window functions, `filter (where ...)`, `distinct on`, lateral joins, arrays, JSON operators and the built-in date and text functions all work as in PostgreSQL, subject to what the role you run as may do.

Identifiers fold to lower case unless you double-quote them. A landed column with capital letters has to be quoted, for example `"CustomerId"`. Names sanda creates are all lower case.

## Where SQL runs

There are five places to run or store SQL, and they are not the same. What differs is who may use them, which login they run as, and how strictly the text is checked.

| Where | Who | Runs as | What it accepts |
|---|---|---|---|
| [sanda sql](https://docs.sanda-os.com.au/warehouse/sql) | Owners and admins | The builder role | One statement of any kind the role may run. Read-only unless you switch to read-write. |
| A view or materialized view definition in [Modelling](https://docs.sanda-os.com.au/modelling/views), and its preview | Owners and admins | The builder role | One `select` under the strict rules below. |
| A procedure body in [Modelling](https://docs.sanda-os.com.au/modelling/procedures) | Owners and admins | The builder role | SQL or `plpgsql`, not scanned. The role's permissions are the boundary. |
| cherry and connected assistants, when they write SQL with `run_sql` | Anyone using cherry, and credentials with the `sql:run` scope | A login that can read only the tables the semantic fluid publishes in `mapping` | One read-only `select`, under the strict rules below and a few more. They reach for it only when a question cannot be composed from the fluid's metrics and dimensions, and they must give a reason. |
| An assistant over MCP using `execute_sql` | A credential with the `sql:write` scope | The builder role | The same as sanda sql, with a reason recorded for every statement. |

Reports, the semantic workbench and the OData feed do not run SQL that you write. sanda composes their queries from the definitions on the fluid.

## Schemas and naming

### Schemas

| Schema | What it holds | You can |
|---|---|---|
| `raw` | Landing tables: every stream a connection syncs, and every CSV upload. | Read, in sanda sql and in view definitions. |
| `derived` | Your views, materialized views and procedures, and tables your own SQL creates. | Read and, in read-write mode, write. |
| `mapping` | Views the semantic fluid publishes for cherry and reports, plus any tables built there (for example the sanda sample dataset copied into a warehouse). | Read the tables. The published views are not readable by the builder role. |
| `search` | sanda search's indexes, one table per service. | Not from the shell. |
| `meta` | sanda's own bookkeeping. | Not from the shell. |

The managed sync also keeps a working area in the same database. It is not listed in the Explorer and you cannot read it.

### Names

| What | Name | Example |
|---|---|---|
| A table landed by a connection | `raw.<connection>__<stream>` | `raw.xero__invoices` |
| A table from a CSV upload | `raw.csv__<table>` | `raw.csv__sales` |
| Your view, materialized view or procedure | `derived.<name>` | `derived.monthly_revenue` |
| A table published on the fluid | `mapping.<name>` | `mapping.invoices` |

**`<connection>`** is the connection's name in lower case, with every run of characters other than `a` to `z` and `0` to `9` replaced by one underscore, leading and trailing underscores removed, and cut to 40 characters. If nothing is left, sanda uses the connector's type. Two underscores separate it from the stream.

**`<table>` for a CSV** is the name you give the upload, in lower case, without a `.csv`, `.tsv` or `.txt` ending, with the same replacement rule, cut to 40 characters, and prefixed `t_` if it starts with a digit. The CSV's columns become lower snake case with no leading or trailing underscore, at most 58 characters, with `c_` added in front of a name that starts with a digit, `_2`, `_3` added to repeats, and `column_<n>` for an empty heading. Types are worked out from the whole file: `bigint` for whole numbers, `numeric` for decimals, `boolean` for `true` and `false`, `date` for `2026-09-30`, `timestamptz` for ISO timestamps, and `text` for anything else. Empty cells are nulls.

**`<name>` in `derived`** is lower case letters, digits and underscores, starts with a letter, is at most 63 characters, does not start with `pg_`, and cannot match a table in `raw` or a view in `mapping`.

Always write the schema, as in `raw.csv__sales`.

## Columns sanda adds

Some columns in your tables are not from your source. Their names start with an underscore, and sanda treats every one as bookkeeping.

| Column | Where | What it holds |
|---|---|---|
| `_becca_loaded_at` | Every `raw.csv__*` table | `timestamptz`, defaulting to the moment the row was loaded. |
| Underscore-prefixed sync columns | Every table a connection lands | Loading details such as a record identifier, the time the row was extracted and a metadata record. Their names start with an underscore and one of two reserved prefixes. |

A CSV heading never keeps a leading underscore, so a heading called `_becca_loaded_at` lands as `becca_loaded_at` and cannot collide.

Because they are bookkeeping, these columns are left out wherever a person or cherry would see business data:

- the [Explorer](https://docs.sanda-os.com.au/warehouse/explorer) counts them but does not list or preview them;
- the views the fluid publishes in `mapping` do not name them, so cherry and reports cannot read them;
- the fluid's map and the context cherry is given do not include them.

They are real columns, so `select *` in sanda sql returns them, and you can use them. The prefixes are reserved and are matched as prefixes, so a column of your own called `_notes` is untouched.

## What sanda sql allows and refuses

### The builder role

Every statement in sanda sql runs as the warehouse's builder role. The role, not a list of banned words, is what limits a statement.

| The role can | The role cannot |
|---|---|
| Read `raw.*`, `derived.*` and the tables in `mapping.*` | Read a view the semantic fluid publishes in `mapping` |
| Read the database catalogue: `information_schema` and `pg_catalog` | Read `search.*` or `meta.*`, or the managed sync's working area |
| Create, change and drop objects in `derived` (read-write mode) | Write anywhere else, or reach another database or role |

Anything outside those permissions fails with a permission error that says what the role may do.

### Statements

- **One statement per press.** sanda reads the text the way PostgreSQL does. A semicolon inside a string, a double-quoted name, dollar-quoted text or a comment is text, and a semicolon between two statements is a boundary. A single trailing semicolon, with comments after it, is fine.
- **Comments and dollar quoting are allowed.** `--` and `/* ... */` comments (including nested block comments), `E'...'` strings and `$tag$...$tag$` quoting all work in sanda sql. They are refused in view definitions.
- **At most 20,000 characters.**
- **Read-only or read-write.** In read-only mode the statement runs in a read-only transaction and PostgreSQL refuses anything that would change data, including a writable `with` and a function with side effects. In read-write mode it runs as written and commits at once.
- **Statements that start with `select`, `with`, `values` or `table`** are read through a cursor, so the row limit is enforced at the source. Anything else runs directly. If it returns rows, as `explain` does, they are shown under the same cap. Otherwise the result is its command tag and the number of rows it touched.

### Rows, values and secrets

- At most **500** rows come back. The **rows** menu chooses 50, 200 or 500.
- A value is cut at **300** characters, with an ellipsis. Dates arrive as ISO strings and JSON as JSON text.
- A result column whose **name** looks like a secret is returned with empty values, and the result lists it as withheld. The test reads the column's name in the result as words, split at underscores, hyphens, digits and changes of case (so `userPassword2` is `user`, `password` and `2`), and ignores case. These words count wherever they appear, even run together with others: `password`, `passwd`, `passphrase`, `passcode`, `passkey`, `token`, `credential`, `apikey`, `privatekey`, `secretkey`, `socialsecurity`, `taxfile` and `cardnumber`. `secret` counts as a word or at the end of one, so `clientsecret` is withheld and `secretary` is not. These count only as a whole word: `pass`, `ssn`, `tfn`, `iban`, `swift`, `cvv`, `pan` and `routing`. Pairs of words count too: `api_key`, `private_key`, `secret_key`, `social_security`, `tax_file` and `card_number`, however they are separated. So `pass_hash` and `auth_token` are withheld, and `passenger_count` and `compass_heading` are not. Giving a column a different name in your `select` shows it, so use that only where the match is a false one.
- The Explorer's preview applies the same test to column names.

### Time

The shell uses your warehouse's statement ceiling, which is set under **Limits** and is at most 60 seconds on Basic, 60 seconds on Standard and 5 minutes on Enterprise. It never waits longer than 55 seconds, so a longer ceiling is lowered to 55 seconds. A statement that runs past it is stopped.

## Rules for view definitions and assistant queries

A view or materialized view definition, its preview, and every query cherry or an assistant writes with `run_sql` must pass one strict check first. sanda does this as a courtesy, so you get a sentence instead of a database error, and the login's permissions still stand behind it.

| Rule | Detail |
|---|---|
| One statement | Begins with `select` or `with`. A trailing semicolon is removed. A semicolon anywhere else is refused, even inside a string. |
| No comments | `--`, `/*` and `*/` are refused anywhere in the text, even inside a string. |
| Ordinary quoting only | Dollar-quoted text, `E'...'` strings and `U&'...'` or `U&"..."` are refused. Write an ordinary string and double the quote to include one. |
| At most 20,000 characters | Longer text is refused. |
| Reads named tables | Use schema-qualified names of `raw`, `derived` or tables in `mapping`. A definition that reads a view the fluid publishes is rejected after it is created, and rolled back. |
| Forbidden words | See below. |

The check reads the statement as tokens. A forbidden word inside a string literal or inside a double-quoted name is fine: `where status = 'CREATED'` and `select "comment"` both pass.

**Refused as bare words:**

- statements and clauses: `insert`, `update`, `delete`, `drop`, `alter`, `create`, `grant`, `revoke`, `truncate`, `copy`, `merge`, `call`, `do`, `vacuum`, `analyze`, `reindex`, `cluster`, `lock`, `listen`, `notify`, `prepare`, `execute`, `comment`, `refresh`, `set`, `reset`, `begin`, `commit`, `rollback`, `savepoint`, `security` and `into`;
- the catalogue: `pg_catalog`, `information_schema`, `pg_class`, `pg_attribute`, `pg_namespace`, `pg_tables`, `pg_views`, `pg_roles`, `pg_user`, `pg_shadow`, `pg_authid`, `pg_settings`, `pg_database`, `pg_proc`, `pg_stat_activity` and `pg_stat_statements`. These are refused even inside double quotes;
- families of server, file, lock and replication functions, matched by prefix: names starting `pg_sleep`, `pg_terminate_backend`, `pg_cancel_backend`, `set_config`, `dblink`, `pg_notify`, `pg_reload_conf`, `pg_rotate_logfile`, `pg_switch_wal`, `pg_backend_pid`, `pg_export_snapshot`, `pg_stat_file`, `pg_advisory_`, `pg_try_advisory_`, `pg_read_`, `pg_ls_`, `lo_`, `pg_logical_`, `pg_replication_`, `pg_create_`, `pg_drop_`, `txid_` and `pg_current_`, and the XML export functions `query_to_xml`, `table_to_xml`, `cursor_to_xml`, `schema_to_xml` and `database_to_xml`.

Because the word list matches whole words, a column named `comment` has to be written `"comment"`, and a bare column called `lo_score` is refused until it is quoted.

Queries written by cherry and assistants face a few more rules. A statement is refused if any column or alias in it looks like a secret, if it uses a whole row as a value, or if it renames columns by position. Those queries read only the fluid's published tables.

## Limits

Plan limits are on the [Limits](https://docs.sanda-os.com.au/reference/limits) page. These are the limits of the SQL itself.

| Limit | Value |
|---|---|
| Statement in sanda sql, or in a view definition, or a procedure body | 20,000 characters |
| Rows returned by sanda sql | 500 (menu: 50, 200 or 500) |
| Longest value shown in a result | 300 characters |
| Longest statement in sanda sql | The warehouse's ceiling, or 55 seconds, whichever is less |
| Rows in a view preview, and in the Explorer's data preview | 50 |
| Columns in the Explorer's data preview | 60 |
| A view's preview | Stops after 20 seconds |
| Build or procedure call started from the page | 55 seconds |
| Build or procedure call in a task | Up to 14 minutes for the whole run |
| Object names | 63 characters |
| Columns in a drawn view | 200, with at most 8 joins |
| Key columns on a materialized view | 8 |
| Procedure arguments | 16, of the types `text`, `integer`, `bigint`, `numeric`, `boolean`, `date`, `timestamptz`, `timestamp`, `interval`, `jsonb` and `uuid` |
| Steps in a task | 20 |
| Concurrent queries in a warehouse | 60 on Basic, 60 on Standard, 300 on Enterprise, at most |
| Warehouse statement ceiling | 60 seconds on Basic, 60 seconds on Standard, 5 minutes on Enterprise, at most |

## Error messages

What you may read in sanda sql, and what to do.

| Message | What it means | What to do |
|---|---|---|
| Type a statement first. | The editor is empty. | Type one. |
| That statement is longer than 20,000 characters. | Over the length limit. | Split the work, or move it to a [procedure](https://docs.sanda-os.com.au/modelling/procedures). |
| One statement at a time... | The text has a second statement after a semicolon. | Run them one after another. |
| The shell is in read-only mode, and that statement would change something. | A change was attempted in read-only mode. | Switch to **Read-write** if you mean it. |
| Permission denied... The builder role reads `raw.*`, `derived.*` and mapping's tables, and writes only `derived.*`. | The statement touched something the role may not. | Read from a table the role can reach, or write in `derived`. |
| Something depends on it... | A `drop` or change would break another object. | Drop or redefine the dependents first. |
| That ran past its time ceiling and was stopped. | The statement outlasted the ceiling. | Narrow it, add a filter, or materialize the result in a view. |
| PostgreSQL's own message, ending "(at character N)" | A syntax or name error. `N` is the position in the text. | Fix the text at that position. |
| This workspace's warehouse is suspended... | The workspace's budget was reached. | See [Usage, budgets and suspension](https://docs.sanda-os.com.au/warehouse/usage-and-suspension). |

## Examples

These examples use a CSV upload called `sales`, with the headings `Sale date`, `Region`, `Product`, `Units` and `Revenue`, and dates written `2026-09-30`. sanda lands it as `raw.csv__sales` with the columns `sale_date` (a `date`), `region`, `product`, `units` and `revenue`. Replace the names with your own from the [Explorer](https://docs.sanda-os.com.au/warehouse/explorer).

### Find your way around

```sql title="Every table and view the builder role can read"
select table_schema, table_name, table_type
from information_schema.tables
where table_schema in ('raw', 'derived', 'mapping')
order by table_schema, table_name;
```

```sql title="The columns of one table"
select ordinal_position, column_name, data_type, is_nullable
from information_schema.columns
where table_schema = 'raw'
  and table_name = 'csv__sales'
order by ordinal_position;
```

```sql title="The bookkeeping columns on a landed table"
select column_name, data_type
from information_schema.columns
where table_schema = 'raw'
  and table_name = 'xero__invoices'
  and left(column_name, 1) = '_'
order by ordinal_position;
```

```sql title="How big are the objects you have built"
select c.relname as name,
       c.relkind,
       pg_size_pretty(pg_total_relation_size(c.oid)) as size
from pg_class c
where c.relnamespace = 'derived'::regnamespace
  and c.relkind in ('r', 'm')
order by pg_total_relation_size(c.oid) desc;
```

### Look at the data

```sql title="A quick look"
select * from raw.csv__sales limit 20;
```

```sql title="An exact row count"
select count(*) as row_count from raw.csv__sales;
```

```sql title="When a CSV table was last loaded"
select max(_becca_loaded_at) as last_loaded from raw.csv__sales;
```

```sql title="Duplicates on what should be a unique key"
select sale_date, region, product, count(*) as copies
from raw.csv__sales
group by 1, 2, 3
having count(*) > 1
order by copies desc;
```

### Answer a question

```sql title="Revenue by month"
select date_trunc('month', sale_date)::date as month,
       sum(revenue) as revenue
from raw.csv__sales
group by 1
order by 1;
```

```sql title="Month-on-month change"
with monthly as (
  select date_trunc('month', sale_date)::date as month,
         sum(revenue) as revenue
  from raw.csv__sales
  group by 1
)
select month,
       revenue,
       revenue - lag(revenue) over (order by month) as change,
       round(
         100.0 * (revenue - lag(revenue) over (order by month))
         / nullif(lag(revenue) over (order by month), 0),
         1
       ) as change_pct
from monthly
order by month;
```

```sql title="Top ten products in the last 90 days"
select product, sum(units) as units, sum(revenue) as revenue
from raw.csv__sales
where sale_date >= current_date - interval '90 days'
group by product
order by revenue desc
limit 10;
```

```sql title="See the plan before you run something heavy"
explain
select region, sum(revenue)
from raw.csv__sales
group by region;
```

### Write in derived (read-write mode)

```sql title="Keep a rollup as a table"
create table derived.monthly_revenue as
select date_trunc('month', sale_date)::date as month,
       region,
       sum(revenue) as revenue
from raw.csv__sales
group by 1, 2;
```

```sql title="Remove it again"
drop table derived.monthly_revenue;
```

A table made this way has no version history and no schedule. For something sanda versions and rebuilds, define a view in [Modelling](https://docs.sanda-os.com.au/modelling/views). Its definition must satisfy the strict rules above: one `select`, no semicolon, no comments.

```sql title="A definition that passes the strict rules"
select date_trunc('month', s.sale_date)::date as month,
       s.region,
       sum(s.revenue) as revenue
from raw.csv__sales s
group by 1, 2
```

:::links
- [sanda sql](https://docs.sanda-os.com.au/warehouse/sql): The shell, step by step.
- [Views and modelled tables](https://docs.sanda-os.com.au/modelling/views): Turn a query into a view sanda keeps.
- [Procedures](https://docs.sanda-os.com.au/modelling/procedures): Multi-step SQL with arguments.
- [Limits](https://docs.sanda-os.com.au/reference/limits): Every plan limit by edition.
:::