The SQL console runs DuckDB over the data files of the datasets you can read. It does not reach Postgres. Terms are defined in Concepts.
Tools · SQL (open to everyone who is signed in). Every dataset in your projects, and every dataset shared into them,
is a table named ds_<id>. It also has an alias, the dataset's name in lowercase with each run of other characters
turned into _ (t_ first when it starts with a digit), when that alias is unique among your datasets. The table
datasets lists them: view, alias, dataset_id, name, project, borrowed and rows. GET /api/sql/tables
returns the same list with each dataset's columns and types.
SELECT * FROM ds_12 LIMIT 100;
FROM (SUMMARIZE ds_12);
SELECT a.*, b.label FROM ds_12 AS a JOIN ds_14 AS b USING (customer_id);- The console runs one query:
SELECT,WITH … SELECT,VALUESorFROM …. DuckDB's own parser checks it, so a second statement is refused however it is written. - It sees only your datasets. Another project's
ds_<id>, a file path (read_parquet('/data/…'),FROM '…'),COPY … TO,ATTACH,INSTALL,LOADandSETare all errors, and the catalogs (duckdb_views(),information_schema.tables,SHOW TABLES) list only your tables. Each query runs in its own in-memory DuckDB connection with external access off and its configuration locked. PIVOTis not available (DuckDB runs it as several statements). Usecount(*) FILTER (WHERE …)orCASEinside an aggregate.- A query stops after
SQL_TIMEOUT_MS, may useSQL_MEMORY_MBof memory, and returns at mostSQL_MAX_ROWSrows.SQL_POOL_MAXqueries run at once on one API worker; another is told to try again. :datasetin an example is replaced with the current dataset's id.- Field profiles, decisions, correlations, jobs and run metrics are not tables here. They are on the pages, through
the API, and through
rfin a notebook (rf.dataset(id).fields(),rf.jobs()).
-- share of missing values per column
SELECT column_name, column_type, null_percentage FROM (SUMMARIZE ds_1) ORDER BY null_percentage DESC;
-- value counts of a column
SELECT some_column, count(*) AS n FROM ds_1 GROUP BY some_column ORDER BY n DESC LIMIT 50;Postgres holds the application's metadata (meta) and the raw tables (raw.ds_<id>) behind row-level security for the
app's role. A client connected to localhost:5432 (DB_PORT) as the database user from .env owns those tables. It
sees everything; keep it to administrators. make psql opens psql as that user.
Schema changes go in api/app/migrations/NNN_name.sql. The API applies new migrations once on startup, under an
advisory lock, and records them in meta.schema_migrations.
Views live in api/app/views.sql. The API recreates them on every start.