SQL reference

Name tables, write queries in the warehouse SQL dialect, and know what each surface allows.

Ember, SQL in Embrasure, the MCP query tool, the CLI, and dbt all run SQL against the same warehouse. This page covers the rules they share.

Dialect

Warehouse SQL uses the Trino dialect: ANSI SQL with Trino's functions, types, and casts. Embrasure may run a query on a faster engine behind the scenes (see how queries are routed), but you always write Trino SQL. PostgreSQL-only syntax, such as :: casts, ILIKE, or DATE_PART('epoch', …), doesn't work.

PostgreSQL habit	Warehouse SQL
`created_at::date`	`CAST(created_at AS date)`
`name ILIKE '%acme%'`	`lower(name) LIKE '%acme%'`
`now() - interval '7 days'`	`current_timestamp - INTERVAL '7' DAY`
`date_trunc('week', created_at)`	`date_trunc('week', created_at)`
`count(DISTINCT user_id)` over large tables	`approx_distinct(user_id)` for a fast estimate
`amount::numeric / 100`	`CAST(amount AS decimal(18, 2)) / 100`
A cast that might fail	`try_cast(value AS integer)` returns `NULL` instead of an error

Send one statement per request. A trailing semicolon is fine; a second statement is rejected.

Name tables

Use three-part names: database.schema.table.

SELECT count(*) AS customer_count
FROM ember.stripe.customers;

Database. Sources connected during setup sync into the ember database. A source added later can use a different destination database; check its name on the source's page in Sources or with Copy full name in the catalog.
Schema. Synced tables keep their source schema. A PostgreSQL table in public lands in public; application sources use their own schema, such as stripe. Schema and table names are lowercased, characters other than letters, numbers, and underscores become _, and a name that doesn't start with a letter gets an s_ prefix.
Table. If two source tables would get the same name, later ones get a _2, _3, … suffix.

Columns that start with _embrasure_ are bookkeeping columns Embrasure adds during syncs. They don't appear in the catalog, but some are useful in queries; for example, application syncs such as Stripe keep deleted records with _embrasure_deleted = true.

To explore what exists:

SHOW TABLES IN ember.stripe;
SHOW COLUMNS FROM ember.stripe.customers;
DESCRIBE ember.stripe.customers;

What each surface can run

Surface	Allowed statements
Ember in Slack	`SELECT` only. Comments and `%` placeholders are rejected.
MCP `query` tool and CLI	`SELECT`, `SHOW`, and `DESCRIBE`.
SQL in Embrasure	The above, plus writes for editors and above (see below).
dbt	Model builds into `<database>.default.<model>`.

Writes

Editors can create and change their own tables from SQL or dbt. Writes are limited to tables in a database's default schema, such as analytics.default.weekly_revenue.

Allowed: CREATE DATABASE, CREATE TABLE, CREATE TABLE AS SELECT, INSERT, MERGE, and DROP TABLE.

Not allowed:

Changing synced tables. Tables managed by a source sync are read-only; build a new table from them instead.
CREATE VIEW. Use CREATE TABLE AS SELECT.
CREATE OR REPLACE TABLE, INSERT OVERWRITE, and partition writes.
UPDATE, DELETE, and ALTER.

A database named default is reserved. Database and table names must start with a lowercase letter and use only lowercase letters, numbers, and underscores.

Results

Limit	Value
Query timeout	120 seconds by default
Data scanned per query	25 GiB
Rows kept per result	10,000 by default. SQL also offers 100,000 or unlimited.
Result retention	7 days

Use LIMIT when you only need a sample, and filter on date columns to scan less data. See limits for every query limit.

In API and MCP results, decimals, integers larger than 2⁵³, timestamps, and binary values are returned as strings so no precision is lost.

Common errors

Message	Fix
`Warehouse table '…' was not found. Use database.schema.table.`	Check the name with Copy full name in the catalog or `SHOW TABLES IN <database>.<schema>`.
`Table reference '…' is ambiguous …`	Use the full three-part name.
`Warehouse database '…' does not exist or is not ready.`	Wait for the first sync to finish, or check the destination database name.
`This endpoint only supports read-only warehouse statements.`	Run writes from SQL in Embrasure or through dbt.
`This is a managed ingestion table. Use its database.schema.table identity for reads.`	Query the table by its catalog name, not its storage name.
`Query cancelled after 120 seconds.`	Filter on a date column, select fewer columns, or aggregate before joining.
`Query stopped after scanning more data than this warehouse allows.`	Narrow the date range or select fewer columns.

See troubleshooting for more.