SQL reference

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.

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.

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

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

<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.

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 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.

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.

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 page. These are the limits of the SQL itself.

Error messages

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

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.

Find your way around

select table_schema, table_name, table_type
from information_schema.tables
where table_schema in ('raw', 'derived', 'mapping')
order by table_schema, table_name;

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;

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;

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

select * from raw.csv__sales limit 20;

select count(*) as row_count from raw.csv__sales;

select max(_becca_loaded_at) as last_loaded from raw.csv__sales;

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

select date_trunc('month', sale_date)::date as month,
       sum(revenue) as revenue
from raw.csv__sales
group by 1
order by 1;

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;

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;

explain
select region, sum(revenue)
from raw.csv__sales
group by region;

Write in derived (read-write mode)

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;

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. Its definition must satisfy the strict rules above: one select, no semicolon, no comments.

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