Introduction

basalt is a SQL data movement engine in a single static binary. A script describes a columnar data pipeline: read from a source, transform with a query, write to a sink. A script is plan-time static — parsed, type-checked, and planned once, then executed as a streaming pull pipeline.

CREATE CONNECTION erp TYPE sqlserver OPTIONS (host = 'sql.internal', database = 'totvs');

LOAD INTO 'az://lakeacct/bronze/orders.parquet' AS
SELECT id, customer, amount, placed_at
FROM erp.orders
WHERE status = 'paid';

The sources and sinks are files (CSV, Parquet, Arrow IPC, Excel), object storage, SFTP servers, Windows shares, SQL databases and HTTP APIs; the same script runs once from the command line, or as an HTTP endpoint under basalt serve.

This book is the reference for the dialect, derived from the parser (src/lang/sql_parser.zig); it describes what the engine actually accepts.

• Getting started runs a first query and a first load.
• The language covers scripts, parameters, connections, queries and the statements around them.
• Reference lists the expressions, types and functions.
• Connectors has what is particular to each kind of database, API and file server.
• Tools describes the command line, the REPL and the kernel a notebook drives.

Getting started

Download the binary for Linux x86-64 from the latest release, or build it from source with Zig 0.15.2 (see the README):

$ curl -fsSL -o basalt https://github.com/leonardomb1/basalt/releases/latest/download/basalt-x86_64-linux
$ chmod +x basalt

A first query

Save this as orders.csv:

id,region,amount,placed_at
1,north,120.50,2026-03-01
2,south,80.00,2026-03-01
3,north,42.25,2026-03-02
4,east,310.00,2026-03-02
5,south,15.75,2026-03-03

A SELECT on its own prints its rows. -c runs a script given on the command line:

$ basalt run -c "SELECT * FROM 'orders.csv' LIMIT 3;"
id  region  amount  placed_at
--  ------  ------  ----------
1   north   120.5   2026-03-01
2   south   80      2026-03-01
3   north   42.25   2026-03-02
(3 rows)

The file’s extension picks the reader, and the column types are read from the data: amount is a number and placed_at a date. Queries are SQL:

$ basalt run -c "SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
                 FROM 'orders.csv' GROUP BY region ORDER BY revenue DESC;"
region  orders  revenue
------  ------  -------
east    1       310
north   2       162.75
south   2       95.75
(3 rows)

A first load

A script in a file can take parameters and write its result instead of printing it. Save this as daily.sql:

PARAM since DATE DEFAULT '2026-03-02';

LOAD INTO 'daily.parquet' AS
SELECT placed_at AS day, region, SUM(amount) AS revenue
FROM 'orders.csv'
WHERE placed_at >= $since
GROUP BY placed_at, region
ORDER BY day, region;

check validates a script without running it — every name, type and parameter — and run executes it. -p binds a parameter:

$ basalt check daily.sql
ok: daily.sql checks out
$ basalt run daily.sql
Read 5 rows, loaded 3 into daily.parquet in 86ms (58 rows/s, 12 lanes)
$ basalt run daily.sql -p since=2026-03-01
Loaded 5 rows into daily.parquet in 90ms (55 rows/s, 12 lanes)

A mistake is reported where it is, before anything runs:

$ basalt check -c "SELECT regoin FROM 'orders.csv';"
<command>:1:8: error: unknown field `regoin`

Seeing the plan

EXPLAIN prints how a query will run without running it:

$ basalt run -c "EXPLAIN SELECT region, SUM(amount) AS revenue FROM 'orders.csv'
                 WHERE amount > 50 GROUP BY region;"
plan
  write  stdout  (default)
    aggregate  1 agg(s), 1 group(s)
      schema: region:string?  revenue:float?
      filter
        schema: id:int?  region:string?  amount:float?  placed_at:date?
        scan  csv  orders.csv
          schema: id:int?  region:string?  amount:float?  placed_at:date?
  physical: morsel-parallel candidate (per-lane partials, combined)

Next

• Scripts describes what a script may hold.
• Connections reaches databases, APIs and file servers.
• Sources and LOAD INTO list what can be read and written.
• REPL is the interactive way to explore data.

Scripts

A script is a sequence of ;-terminated statements:

@include 'lib.sql';               -- top of file only; C-style, relative to this file
CREATE ENDPOINT '/x' DOC '...';   -- only for HTTP mode; absent = batch
PARAM ...;                        -- request/CLI inputs
LET name = <expr>;                -- sealed plan-time constant
THROW 'msg' WHEN <condition>;     -- fail the plan on the script's own invariant
CREATE CONNECTION ...;            -- named data endpoints
CREATE FUNCTION f(a) AS <expr>;   -- scalar functions (inlined at plan time)
CREATE FUNCTION p(a) AS ... END;  -- statement functions, invoked with CALL
CREATE FUNCTION t(a) RETURNS TABLE AS SELECT ...;  -- table functions, read with FROM t('x')
LOAD INTO ... AS <query>;         -- output pipeline(s)
<query>;                          -- terminal SELECT = print to stdout
CALL p('x');                      -- run a statement function
FOR EACH ROW OF (...) ... END FOR;
CASE ... END CASE;                -- plan-time dispatch
PRINT <expr>;                     -- progress line on stderr, via the run log

@include 'file.sql'; splices another script’s declarations ahead of this one at plan time. It stands on a line of its own — nothing may follow it there but a -- comment. Each included file is parsed separately (errors report the included file’s own path and line), includes may nest (depth 16, cycles rejected), and paths resolve relative to the including file. A file is spliced in once, at its first include, like C’s #pragma once: when outliers.sql and dispersion.sql both include stats.sql and a script includes both, stats.sql is in the program one time — its CREATE FUNCTIONs are not defined twice — and each library still sees what it declares. That holds for every statement in it, so an included file’s LOAD INTO runs once however many paths reach it.

THROW <message> [WHEN <condition>]; asserts what the engine cannot infer. Both operands are ordinary expressions over $params and $lets (Expressions), so they are decided at plan time: an absent or true condition aborts the script before a row is read, with message as the error text verbatim; a false condition is a no-op. basalt check rejects a script whose guard fires, so a bad invocation is caught without connecting to anything. A fired guard is permanent, never transient — exit 1, never 75 (exit codes), so a scheduler will not retry it.

THROW 'tbl is required (e.g. -p tbl=SC5)' WHEN $tbl IS EMPTY;
THROW 'since must be an ISO date' WHEN $since <> '' AND length($since) < 10;
THROW 'unreachable branch';       -- unconditional, e.g. in a CASE arm

• Batch is the silent default. A script with no CREATE ENDPOINT runs once to completion (exit codes).
• Keywords are case-insensitive; identifiers keep their case.
• Comments: -- to end of line, /* ... */ blocks.
• Strings are '...' (double '' for a literal quote), and only '...'.
• Quoted names are "..." (ANSI): "Exchange rate" is the column of that name, not the text. It is the only way to name a column containing a space or spelling a keyword — SELECT "select", "Valor Total" FROM ... — and a quoted name is never read as a keyword. Double "" for a literal quote inside one. A misspelled quoted name fails as unknown field, at plan time.
• Raw SQL literals use Postgres dollar-quoting: $$...$$, or $tag$...$tag$ when the body contains $$. No escaping inside; ${...} interpolation of loop vars still applies within them (FOR EACH).
• Dynamic names (per-row table/sink names, keys) use $var + IDENTIFIER() + ||, not raw string interpolation — see dynamic names.

PRINT <expr>; emits one progress line where it stands — the way a long FOR EACH or CALL says what it is doing. The argument is an ordinary expression (literals, ||, $params, $lets, and inside a FOR EACH or statement-function body the loop variables, bound per row); non-strings render as they would in a sink. It writes to stderr through the run log, never stdout — stdout is the data contract (--format json NDJSON rows or the summary object), and a progress line there would corrupt it. A PRINT is shown whatever --log-level says, since the script asked for it; only -q silences it. Under --log-format json it is an NDJSON line with "level":"print" and the text as its msg. PRINT is not an output pipeline — a script still needs a LOAD INTO or a terminal query.

Parameters and LET

PARAM dias   INT DEFAULT 7;              -- batch: -p dias=3 | http: query string
PARAM desde  TIMESTAMP;                  -- no default = required
PARAM job    JSON FROM BODY;             -- whole JSON body as a document
PARAM tenant STRING FROM HEADER('X-Tenant');

• Reference with $: $dias, $desde. JSON documents navigate by dotted path — $job.tables, $job.source.host — resolved to literals at plan time. A JSON param takes its document from -p job='{"a":1}' (or a kernel’s params), else the request body in HTTP mode, else a string DEFAULT; a path to a key the document lacks is an error naming the key. check with no value bound reads every path as null.
• Safe navigation: $job.filtro?.uf — a missing intermediate resolves the whole path to null instead of erroring.
• Types: BOOL INT FLOAT STRING BYTES DATE TIME TIMESTAMP DECIMAL(p,s) JSON (common synonyms accepted: INTEGER BIGINT DOUBLE TEXT VARCHAR(n) DATETIME NUMERIC ...). A value bound from text — -p, a kernel’s params, a default — is read as a CAST reads it and keeps the declared type: -p d=2026-02-01 is a DATE (date_add('day', 1, $d) works), a date alone bound to a TIMESTAMP is its midnight, TIME takes HH:MM[:SS[.ffffff]], and a DECIMAL(10,2) rounds 12.345 to 12.35. Text that is not one fails the run, and check, naming the param and the value. DECIMAL alone is DECIMAL(38,0); DECIMAL(p) without a scale is refused.
• Source defaults: scalars bind from the query string, JSON from the body. A query-string value is decoded as a browser or a form encodes it: %C3%A3 is ã, + is a space and %2B a plus. FROM QUERY, FROM BODY and FROM HEADER('X-Name') say so explicitly; a bare FROM HEADER reads the header named like the param.

LET name = <expr>; is PARAM’s sealed sibling: a script-scoped constant folded once at plan time (in declaration order; it may reference $params and earlier $lets) and referenced as $name. It can never be bound externally — -p name=... is an error, HTTP binding ignores it, and it is not part of an endpoint’s parameter surface. A LET and a PARAM may not share a name. LET run_ts = now(); gives one consistent timestamp across every pipeline of a run.

LET name = (SELECT ...); binds the single cell of a query instead, run when the statement is reached (so it may read anything the script can) — see Subqueries. An expression LET cannot reference a query LET: expression LETs fold before any query runs.

Connections

CREATE CONNECTION erp TYPE sqlserver OPTIONS (
  host     = 'sql.internal',
  database = 'totvs',
  tls      = 'require'
);

The connection types are postgres, mysql, sqlserver, starrocks, doris, http, sftp, smb and ftp; any other TYPE is a plan-time error.

• postgres, mysql, sqlserver: host port database user password tls (off, require or insecure). sqlserver adds auth (sql, the default; aad with client_id resource token; ntlm; kerberos) and, for Windows authentication, domain realm kdc spn (below).
• starrocks and doris: fe_host/host, fe_port/port, user password, a required database, and for loading be_url (also load_url) buckets replication_num auto_create label_prefix.
• http, sftp, smb and ftp have pages of their own: HTTP APIs, SFTP, SMB file shares and FTP; the databases’ particulars are under SQL databases.

Credentials

Credentials by convention: connection erp resolves ERP_USER / ERP_PASS from the environment at connect time — the common case costs zero characters. Explicit user = ... / password = ... options override the convention. An http connection reads them only for auth = 'basic' (and oauth2 without client_id/client_secret), so a public API needs none. Azure Blob paths (az://..., see Sources) resolve AZURE_STORAGE_KEY, and AZURE_BLOB_ENDPOINT points them at an emulator. S3 paths (s3://...) resolve AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY (+ optional AWS_SESSION_TOKEN, AWS_REGION), and AWS_ENDPOINT_URL points them at an emulator such as MinIO. Secrets are never literals in the script; always environment indirection.

A second CREATE CONNECTION of a name replaces the first from that statement on; CREATE OR REPLACE CONNECTION says the same thing explicitly.

Queries

A query is a SELECT, with WITH bindings ahead of it if it needs them. On its own it prints its rows; after LOAD INTO … AS it writes them (LOAD INTO).

WITH pedidos AS (                          -- CTE = named binding
  SELECT filial, num, valor, obra
  FROM erp.dbo.SC5010
    PUSHDOWN($$D_E_L_E_T_ <> '*'$$)        -- raw predicate, verbatim to the source
  WHERE valor > 0                          -- translated predicate
), obras AS (                              -- a join's right side is a CTE
  SELECT codigo_obra, nome_obra FROM erp.dbo.obras
)
SELECT p.filial, p.num, o.nome_obra
FROM pedidos p
LEFT JOIN obras o ON p.obra = o.codigo_obra
ORDER BY p.num DESC
LIMIT 100 OFFSET 20;

Operators

Row order without ORDER BY is not defined in SQL, and GROUP BY returns groups in hash-partition order. A pipeline that only filters, projects or joins keeps the source’s order at any -j — to the terminal, a CSV, a parquet or an Arrow file — so the same run writes the same rows in the same order; lanes still read in parallel, and their output is put back in order before it is written. A parquet file’s row groups follow those lanes’ units (each lane encodes its own), so its layout, not its rows, can differ from a -j 1 run. Loading into a database table does not order its rows (a table has none). DISTINCT keeps the first row per key in input order at any -j. Add ORDER BY whenever the order is part of the answer.

An ORDER BY whose input passes --op-memory sorts it in pieces: each piece is written as a sorted run to the scratch directory and the runs are merged as rows are read out. The result is the same, ties included, and the disk it takes is bounded by --spill-cap (Spilling to disk).

A GROUP BY or DISTINCT whose keys outgrow --op-memory spills to disk the same way. The rows are the same — DISTINCT ON still keeps the first row per key — but their order without ORDER BY is not that of an in-memory run.

Naming, GROUP BY and ORDER BY

A computed SELECT item does not need AS. Without one it is named after the text of its expression, lowercased and stripped of spaces — COUNT(*) becomes count(*), ClientIP - 1 becomes clientip-1. An explicit alias always wins.

The same naming is what lets GROUP BY and ORDER BY repeat an expression instead of its alias: both sides render the expression the same way, so they bind to the one column the projection already produced.

SELECT AdvEngineID, COUNT(*) FROM hits
GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;      -- binds to count(*)

SELECT DATE_TRUNC('minute', EventTime) AS m, COUNT(*) AS c FROM hits
GROUP BY DATE_TRUNC('minute', EventTime);          -- binds to m

• A subquery in FROM is a derived table — FROM (SELECT ...) x — and works in a join’s right side too: JOIN (SELECT ...) y ON .... It lowers to exactly what WITH x AS (...) produces, so it costs nothing extra to run; the alias is optional.
• Column order is the SELECT list’s, not the aggregate’s. SELECT k, SUM(x), k2 writes k, sum, k2; the engine folds grouping keys before aggregates internally and reorders the projection back on the way out.
• GROUP BY <n> is positional — it names the n-th SELECT item.
• GROUP BY <expr> accepts a computed key (GROUP BY ClientIP - 1).
• ORDER BY may name a column the SELECT list does not project. It is carried through the projection as a hidden column and dropped after the LIMIT, so sorting by an unselected column costs nothing in the output.

Sources

A query reads from one source, named after FROM, and joins may read more (Joins).

Every source takes an alias, with or without AS: FROM 'x.xlsx' AS xl, FROM sr.db.t t.

Folders

A folder read lists the folder — subfolders included, names starting _ or . skipped (_SUCCESS, _temporary/, .crc) — and reads the files sorted by path. Parquet files and no CSVs read as Parquet; CSVs as CSV, other files ignored; a folder of both is refused unless WITH (format = 'parquet') or 'csv' picks one. Every Parquet file must have the first one’s columns, in its order and of its types, among those the query reads: a file with one missing, moved or retyped fails the read naming it, rather than putting values under the wrong name. Each file still skips the columns and row groups the query does not need. CSV files must repeat the first one’s header.

SQL sources

A raw QUERY($$…$$) is sent as it is and may hold several statements — a DELETE and an INSERT before its SELECT, a statement after it. The batch runs to its end: every statement executes, and an error in any of them fails the read with the server’s message. It may return one result set; a second is an error (the query returned more than one result set) rather than dropped or, if it has the same columns, appended as if it were more rows. A read that stops early — a LIMIT, a row cap — cancels whatever of the batch had not run yet. MySQL and StarRocks refuse a multi-statement QUERY outright.

A SQL Server session is opened as SSMS and the ODBC, JDBC and .NET drivers open one: ANSI_WARNINGS, ANSI_NULLS, ANSI_PADDING, ANSI_NULL_DFLT_ON, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL and ARITHABORT on. So a value too long for its column is refused (String or binary data would be truncated) rather than cut, a division by zero is an error rather than NULL, a varchar keeps its trailing spaces, a column created without NULL / NOT NULL allows nulls, and a table with a filtered index or an indexed view can be written.

Source clauses

These follow the source, in any order. PUSHDOWN and the rules for what a SQL source is asked to do are on Pushdown.

• PAGINATE BY page|offset|cursor (param = 'page', size = 100, total = 'count', field = 'next', start = 2, max = 50) — REST pagination. Friendly keys map to the engine hints (param→page_param/cursor_param, size→page_size, total→total_field, field→cursor_field, start→start_page, max→max_pages); unknown keys pass through.
• RETRY n [ON (429, 503)] — retries + retryable statuses.
• WITH (delimiter = ';', encoding = 'latin1') — the CSV dialect. The delimiter is one character, or the word tab; the encodings are utf8 (default), latin1 / iso-8859-1, and cp1252 / windows-1252. Non-UTF-8 input is decoded to UTF-8 as it is read, so everything downstream — comparisons, length(), a parquet sink — sees proper text. A local file read with a dialect still fans out over byte-range chunks under -j, each lane decoding its chunk in that dialect. Multi-byte encodings are not supported: they would break that chunking. A file whose bytes are not what you claimed does not fail, it just yields mojibake, so prefer the publisher’s stated encoding over guessing. The delimiter is also accepted on a LOAD INTO file target; encoding is not — a CSV sink always writes UTF-8, and being told otherwise is an error rather than ignored.
• WITH (format = 'csv' | 'parquet' | 'arrow' | 'xlsx') — read or write a path as this format whatever its extension says (xlsx reads only). Needed for a file named .dat or .txt, and for a URL that serves CSV from an extensionless path. Without it, an extension basalt does not know is refused at plan time rather than parsed as CSV, which would read a binary file’s bytes as rows.
• WITH (k = v, flag, ...) — every other option of a read, listed in full below.

WITH options

A read’s WITH (...) takes key = value pairs and bare flags. PAGINATE BY and RETRY (above) are shorthands that set some of the REST ones.

Files

REST sources — HTTP(...), conn.GET(...), conn.POST(...) and resources

Joins — on the JOIN clause: max_build = '16GB' sets the right side’s memory ceiling, past which the join spills to disk (Joins); join_order = 'written' (default 'auto') keeps an inner join’s right side in memory and its place in a chain, whatever the size estimates say.

WHERE on a REST source runs in basalt after the fetch; on a SQL table it is pushdown. Same word, different plan — EXPLAIN shows which.

Reading a REST API

A complete REST read, for orientation:

CREATE CONNECTION crates TYPE http OPTIONS (base_url = 'https://crates.io/api/v1')
  RETRY 2 ON (429, 503);

SELECT id AS crate, downloads, json_get(links, 'owners') AS owners
FROM crates.GET('/crates', sort = 'downloads', per_page = 100)
  PAGINATE BY page (param = 'page', size = 100, total = 'meta.total', max = 5)
  WITH (items = 'crates')
WHERE downloads > 0;

The rows are the array at items (or the response itself); a single object — a detail endpoint’s answer — is one row. Columns are typed from the first object: numbers, booleans and strings as themselves, nested objects and arrays as JSON text, which json_get and JSON_EACH (JSON functions, and UNNEST in Queries) take apart.

File formats

A path’s extension picks its reader and writer — .csv, .parquet, Arrow IPC and Excel — unless WITH (format = ...) names one (Sources).

CSV

A header name in double quotes loses its quotes ("" inside is one quote) and may hold the delimiter: "Valor Total" is the column Valor Total.

A CSV column’s type is sniffed from the first 1024 rows: int ⊂ float ⊂ string, and a column whose every non-empty, unquoted cell is an ISO YYYY-MM-DD reads as a DATE — so WHERE day >= '2026-01-01', date_add, date_diff and EXTRACT work on it directly, and a parquet sink stores it as a date. A string function on it (substr(day, 1, 7), day LIKE '2026%') still sees the ISO text. A cell past the sample that does not parse as the inferred type is an error, never a silent coercion. Timestamps are not sniffed; CAST(ts AS TIMESTAMP) as before.

Parquet

Parquet reads use column projection, row-group skipping from statistics, and ranged reads — only the footer and the chunks a query needs are fetched. A remote .parquet (https://..., az://..., s3://...) is read the same way, by HTTP range request, so a projected query transfers only the chunks it decodes; a server that ignores Range falls back to one whole-object fetch. Parquet writes store DECIMAL in the narrowest physical type its precision allows — INT32 to 9 digits, INT64 to 18, FIXED_LEN_BYTE_ARRAY up to 38; past 38 digits (the engine’s own ceiling) a value is refused rather than silently truncated.

Excel

An Excel workbook — .xlsx or .xlsm, local, by URL or in object storage — reads its first worksheet; WITH (sheet = 'Vendas') names another (case does not matter, and a name that is not there is an error listing the ones that are). The first non-empty row is the header — an empty header cell is named by its column letter, a repeated one numbered (total_2) — or, with header = false, every row is data and the columns are A, B, …. range = 'B3:F200' reads that block, its first row the header, for a sheet with a title above its table; B3:F reads to the last row. Excel is read, never written.

A workbook’s cells carry their own types, so a column’s type is decided from every row, not a sample (a CSV’s first 1024): numbers are INT when each is an integer below 2^53 and FLOAT otherwise; numbers in a date or time format are DATE, TIMESTAMP or TIME (to the millisecond, as Excel keeps them; a 1904-based workbook is read as such, and serials before 1 March 1900 are a day late, as in Excel); booleans are BOOL; and any text in a column makes it STRING, a number there keeping the digits the file stored — so a “Total” row of text at the bottom turns its column to text instead of failing the load. Empty cells and error values (#N/A, #DIV/0!) are null; a formula reads as the value Excel last saved, and a merged cell only in its first cell. Rows stream; what stays in memory is the workbook’s shared-string table (up to 512 MB). The old binary .xls, .xlsb and .ods are not read.

Arrow IPC

Arrow IPC — .arrow, .feather, .ipc (the file format, Feather v2) or .arrows (the stream format) — is how a dataframe reaches basalt fastest: the file is memory-mapped and its columns copied out, so polars’ write_ipc or pyarrow’s write_feather hands a frame over with no encode or decode step. Both formats read, uncompressed or with LZ4-frame or ZSTD buffers, and only the columns a query uses are converted. Every integer width reads as int (a UInt64 above 2^63−1 is an error, never a wraparound), floats as float, all three string layouts (utf8, large_utf8, polars’ default utf8_view) as string and the binary ones as bytes, decimal128 as decimal, and dates, times, timestamps and durations in any unit as basalt’s own (nanoseconds floor to the microsecond; a zoned timestamp is its UTC wall-clock). Dictionary-encoded columns — a polars Categorical — read as their values. Lists, structs and maps read as JSON text, so json_get and CROSS JOIN UNNEST(JSON_EACH(col)) reach inside them. Unions and run-end or list-view encodings are refused with the column named. Only the first stream of a .arrows holding several is read. An Arrow read is one lane; -j does not split it yet.

Parquet types

A parquet column’s type comes from its LogicalType annotation when the writer set one, else from the legacy ConvertedType. That matters for files from polars, DuckDB, Spark or pyarrow: they omit the legacy annotation on a naive timestamp and on every nanosecond one, so without the logical type those columns would read as bare int. TIMESTAMP and TIME in milliseconds, microseconds or nanoseconds all read as basalt’s microsecond timestamp/time; nanoseconds floor to the microsecond. A UTC-adjusted timestamp reads as its UTC wall-clock time: basalt has no zoned timestamp, so isAdjustedToUTC is not carried. DATE, DECIMAL, STRING/ENUM/JSON and INTEGER map as their converted twins do; a UUID stays bytes.

A struct’s fields read as flat dotted columns (addr.city). Everything repeated — a LIST of scalars or of structs, a MAP, and any nesting of them — reads as one string column named for it, holding each row’s value as JSON: lists as arrays ([1,2], [] when empty), structs as objects, maps as objects keyed by each key’s text ({"k1":3}), NULL for a null column value and null for a null element or field, dates and timestamps as quoted text. CROSS JOIN UNNEST(JSON_EACH(tags)) AS tag gives a row per element and json_get reaches into one. No column of the file is left out, so LOAD INTO 'copy.parquet' AS SELECT * FROM 'x.parquet' carries every one — the nested ones as JSON text, since that is how basalt holds them. A filter on a nested column is never used to skip row groups: the file’s statistics describe its leaves, not the JSON.

Compressed files and archives

A compression suffix is read through: FROM 'orders.csv.gz' and FROM 'orders.csv.zst' decompress as they stream, and it is the inner name that picks the reader. Both work over HTTP too. A .gz of several gzip members — what pigz, bgzip and an append write — reads whole. .xz is not supported (std’s decoder has the wrong shape for this reader), nor is bzip2.

LOAD INTO 'orders.csv.gz' writes gzip, at about gzip -6’s size, and APPEND adds a member to an existing one. Other codecs are refused at plan time: zstd output is not supported, and Parquet and Arrow compress their own pages.

A file inside a zip is addressed with ::, the separator ClickHouse uses for the same idea:

SELECT COUNT(*) FROM 'inf_diario_fi_202607.zip :: inf_diario_fi_202607.csv'
  WITH (delimiter = ';');

SELECT * FROM 'cnpj.zip';        -- an archive holding one file needs no `::`

An archive holding more than one file and no :: is a plan-time error naming the candidates, rather than a silent pick of the first — the wrong file read successfully is worse than no read at all. Stored and deflated members are supported, which is every zip in practice.

Members stream: nothing is expanded to memory or to a temp file, so a 59 MB CSV in a 12 MB zip costs the same memory as reading it loose.

Two consequences worth knowing:

• Neither is parallel. There is no mapping from a byte offset in a compressed stream to a row, so -j cannot cut one up — the same reason gzip is not splittable under Hadoop. EXPLAIN reports physical: serial for both, and a loose CSV is the faster shape if you have the disk for it.
• Parquet cannot be read through either. It needs to seek — footer first, then the chunks the query wants — and neither a codec nor an archive member offers that. Both are refused at plan time rather than quietly expanded somewhere.

Reading an archive over HTTP is not supported yet: a zip’s index sits at the end of the file, so it needs a ranged fetch before anything else can happen.

Pushdown

A read from a SQL source asks the source to do as much of the query as it can answer exactly — the filter, the columns, a whole aggregate, a top-N — so less crosses the wire. The answer never changes, only where the work happens; EXPLAIN shows what descended on the scan’s pushdown: line.

• PUSHDOWN(<expr>) — a raw predicate sent verbatim into the generated source query’s WHERE. The argument is a string expression: a $$...$$ literal (PUSHDOWN($$D_E_L_E_T_ <> '*'$$)), a loop-var value (PUSHDOWN($where)), or one built with ||. ANDed with whatever the translated WHERE pushes down. Empty ⇒ no clause. Syntax errors surface at the source at runtime (permanent, exit 1).

• Projection pushdown — a table read asks the source only for the columns the pipeline provably needs (SELECT a, b FROM erp.t is sent as SELECT [a], [b] FROM t), so a narrow read of a 300-column table moves only the columns it reads. SELECT * EXCEPT (...) — after a table read or a union of table reads — first asks the source for the table’s shape and no rows, then names every column but the excepted ones, so a column nobody wants never leaves the server. A plain SELECT * still fetches everything; a split-parallel read adds its key column to the list.

• Implicit pushdown — the contiguous WHERE (filter) prefix directly after a SQL table/query read is translated into that source query’s WHERE automatically, across a join too: a filter naming only columns the probe side already had is moved below the join first, and a filter on a join key also gains a twin on the other side’s key — so FROM fact JOIN dim ON fact.k = dim.k WHERE dim.k = '…' prunes the fact table at the source. Both moves are refused where they would change the answer: never below a RIGHT/FULL join (there the probe side is the one that gets null-extended), never when a referenced column could have come from the right side, and the key twin only under an inner join. EXPLAIN shows where the filter ended up. Translatable: comparisons, AND/OR/NOT, IS [NOT] NULL/EMPTY, IN, LIKE, CASE/IF, CAST (but not one converting text to a number — the sources disagree about what CAST('abc' AS INT) means, NULL in StarRocks and MySQL against an error in Postgres and here, so descending it would make the answer depend on whether it descended; put the expression in a raw QUERY(...) to ask for the source’s own coercion), and the portable string functions (lower upper trim substr replace concat coalesce starts_with ends_with contains). A $param, LET or loop variable descends as its value — WHERE D2_EMISSAO >= $since sends >= '20240105', with a query LET’s value decided first. Untranslatable pieces (arithmetic, now()/today(), user funcs) stay in the engine — the filter is always kept, so results never change, only how much crosses the wire. EXPLAIN prints the descended predicate on a pushdown: line. A CTE, derived table or table function the query starts from counts as that read, and so does a chain of them, each reading the one before: every WHERE along it descends as if written inline ((via binding b, a) in EXPLAIN), except in a binding holding a window function, which stays apart so a WHERE rn = 1 over it can run as a top-N. A filter written after a SELECT list — the query’s own WHERE over a CTE, derived table or table function — moves in front of it when every column it names is one that list passes through or renames, rewritten in the source’s names: SELECT num FROM paid_orders($d) WHERE num > 5, with the body selecting C5_NUM AS num, sends "C5_NUM" > 5. A conjunct naming a computed column (x * 2 AS y) stays above the list, the others still move. Rows a moved filter removes are no longer evaluated by the list, so a computed column that would have failed on one of them no longer fails the query. A CTE, derived table or table function read as a JOIN’s right side is readied the same way — its own WHERE descends into its read and only the columns it uses are asked for — so JOIN itens($filial) i reads that branch’s rows, not the table. A filter written after an inner join that names only the right side’s columns, each by its alias (WHERE i.valor > 0), joins that read too — and descends with it. After a LEFT, RIGHT or FULL join it stays above the join, since there it also decides the unmatched rows; so does a condition naming both sides, or a bare column name, which could be either side’s.

• Text comparisons follow the column’s collation, so the source never keeps fewer rows than basalt would. basalt compares text byte by byte; a source compares by collation — case-insensitive by default on SQL Server and MySQL, and SQL Server ignores trailing spaces ('ab ' = 'ab') under every collation, binary ones too. Sent as written, code >= 'B' lost 'a…' rows on a case-insensitive server, and D2_DOC > '000123' lost the padded '000123 ' on a Protheus char(n). So, asking the source’s catalog once per run and table, and only when a text comparison is in play:

  • =, IN, LIKE and the prefix/suffix/contains tests descend under any collation — folding and padding only let the source match more, and the engine re-applies the filter.
  • <, <=, >, >= on text descend only where the column compares bytes (a _BIN2 collation on SQL Server, or _BIN on a char/varchar; _bin on MySQL; on Postgres C, POSIX, the builtin provider, or any libc collation on a musl build — as the server reports it, never an ICU one; StarRocks always) and the literal is printable ASCII; where the collation also pads, > is sent as >= 'x' OR col LIKE 'x%' and < as <=, which keeps every row basalt keeps. Elsewhere they stay in the engine.
  • <>, NOT (… = …) and IS NOT EMPTY on text negate an equality the collation widens, so they descend only where it compares bytes — on SQL Server with an exact form, NOT (col = 'x' AND DATALENGTH(col) = 1).
  • length() and strpos() never descend (MySQL’s LENGTH counts bytes, SQL Server’s LEN drops trailing spaces), nor a LIKE pattern holding _ (a byte in a SQL Server varchar under a UTF-8 collation); SQL Server’s [ is escaped, and on SQL Server a literal outside printable ASCII stays in the engine.

  EXPLAIN does not connect, so it says text comparisons decided by the collation at run time where the catalog will decide.

• Whole-aggregate pushdown — read <sql> | filters | GROUP BY descends as one grouped query when every filter translates, the group keys are bare columns, and the aggregates are COUNT[(DISTINCT)] SUM MIN MAX with types the engine can pin via explicit casts (AVG, summed floats/decimals, and collation-dependent string extremes deliberately stay engine-side — the result must be bit-identical, not merely close). HAVING/sort/limit still run in the engine on the tiny grouped result. Nothing re-applies the WHERE over a grouped result, so each filter must keep exactly basalt’s rows: a text comparison descends only where the column’s collation compares bytes — on a Protheus binary collation as D2_FILIAL = '01' AND DATALENGTH(D2_FILIAL) = 2, since SQL Server would also count '01 '. The top-N descent below holds its filters to the same rule. When an aggregate over a SQL source does not descend, every matching row is streamed to the engine to be grouped — the run log says so in a warn line that names the rule that refused it (the WHERE predicate does not translate exactly to mysql SQL (…), group key `x` is renamed by the aggregate, …).

• LIMIT and top-N pushdown — read <sql> | filters | [SELECT] | [ORDER BY] | LIMIT n [OFFSET m] asks the source for n + m rows: LIMIT on postgres/mysql/StarRocks, TOP on sqlserver. Without ORDER BY any n + m rows are an answer, so it always descends. With one, the source orders them first — nulls last in both directions, as the engine does (NULLS LAST; a leading k IS NULL key on mysql/StarRocks; a CASE key on sqlserver) — and the engine still sorts, offsets and cuts what arrives, so the final order is its own. It descends only when the answer cannot change:

  • every WHERE before the limit translates (the rules above) — the source counts rows after its own filter, so one left here would thin the capped set;
  • each ORDER BY key is a source column, as-is or renamed by the SELECT (SELECT id AS k … ORDER BY k), not a computed one;
  • each key is a number, date, time or timestamp. A string key stays engine-side: its order is the source’s collation — case-folded by default on mysql and sqlserver, space-padded for char(n) — where the engine compares bytes, the same reason string MIN/MAX do not descend.

  Rows tied on every key at the cut are interchangeable either way: which of them the engine keeps already depends on the order rows arrive in, which a SQL source never promises. A DISTINCT, a join or an aggregate before the limit is not this shape (the aggregate has its own descent above). The capped read runs as one statement, so it is not split into key ranges under -j. A top-N that does not descend streams every matching row here to be sorted, and the run log says so in a warn line naming the rule. EXPLAIN shows it on the scan’s pushdown: line — order by id desc limit 1000 (if the keys are numeric or temporal), since analysis does not connect to learn the key’s type — and physical: serial (top-N pushed, sorts at most 1000 rows) in place of materializes.

Joins

A join with an = key is a hash join: one side is materialized and indexed once, the other streams through — the right side is indexed unless the left is estimated much smaller (Which side is held in memory). One whose ON has no = between a left and a right value is a nested-loop or range join (Joins without a key), which holds the right side in memory. The right side is a CTE, a (SELECT ...), a table function, or any source a FROM reads — a path (JOIN 'smb://fs/x.xlsx' x, its WITH (...) after the alias) or a connection’s table (JOIN sr.db.t AS t), read as (SELECT * FROM it) would be. A key is an = between a value of each side, AND-combined for composite keys, written in either order; a null key never matches. USING (k, d) joins on the columns of those names on both sides and lists each once, first, as SQL does; under a right or full join that column is the first non-null of the two. NATURAL JOIN is refused — name the shared columns with USING. A key may be computed — trim(t.code) = CAST(x.code AS string), each value naming one side’s columns — and each side then computes it before the join, out of sight of SELECT *. Columns need no table when the names say which side they are: trim(code) = CAST(cr AS varchar) finds code and cr in the two sides’ columns, in either order, and is refused as ambiguous only when both values’ columns exist on both sides. Keys of two types (a spreadsheet’s number against a table’s text) do not join until one is cast. The rest of an ON is a condition. In an inner or left join, one naming only the right side, or no column (1 = 1), narrows the right side before the join, pushed down to a SQL source as that side’s WHERE — so JOIN sr.t AS t ON t.D_E_L_E_T_ <> '*' AND t.k = x.k reads only live rows. In an inner join any other condition (the left side alone, the two sides compared otherwise than by =) filters the joined rows. In an outer join it decides which key matches count instead, and a row none of whose matches pass still comes out, unmatched — the way to look up the value valid on a date:

SELECT s.id, p.price
FROM 'sales.csv' s
LEFT JOIN 'prices.csv' p
  ON p.product = s.product
 AND CAST(s.sold_on AS DATE) BETWEEN CAST(p.valid_from AS DATE) AND CAST(p.valid_to AS DATE);

A right or full join keeps every right row the same way, so there a condition on the right side alone decides matches too rather than narrowing it. CROSS JOIN <cte> takes no ON; an ON with no condition across the sides (only b.del <> '*') pairs every left row with every right row it leaves. Right-side columns that collide with a left name come back suffixed _r, and _r2, _r3, … if that name is taken too — that is the name SELECT * shows. A qualified reference needs no suffix: with FROM t a JOIN r b, b.amt is the right side’s amt everywhere in the query (SELECT, WHERE, GROUP BY, ORDER BY, a later join’s ON), and SELECT b.amt calls its output amt unless a.amt is already in the list. A pipeline shaped read | filters | join | filters | write probes in parallel under -j — over local CSV/Parquet morsels, and over key-range splits for a splittable SQL source. A chain of joins followed by GROUP BY fans out the same way (read | filters | join+ | filters | aggregate | sort/limit | write). Right and full joins probe in parallel in the first shape: every lane marks the build rows it matched in one shared set, and once all lanes are done the build rows nothing matched are written once, in build order, through the filters after the join, after every other row, as a serial run writes them. Under a GROUP BY they stay serial.

Joins without a key

An ON with no = between a left and a right value — a.x < b.y, a.d BETWEEN b.from AND b.to, a.k = b.k OR a.alt = b.k, any condition — is a nested-loop join, of any kind: inner, left, right, full. The right side is read into memory, then each left row is paired with the right rows and the whole ON decides which pairs join, evaluated over up to 65,536 pairs at a time; a comparison with a null is no match, and the unmatched rows of an outer join come out as with a hash join. When the ON holds, AND-ed, a comparison of a left value with a right one (L BETWEEN R1 AND R2, L >= R, L < R, …) of one orderable type — numbers, or text, dates, times or timestamps on both sides — it is a range join: the right rows are sorted by that bound and each left row is paired only with those a binary search leaves, which makes looking up the period a date falls in cheap:

SELECT s.id, p.name AS period
FROM 'sales.csv' s
LEFT JOIN 'periods.csv' p
  ON CAST(s.sold_on AS DATE) BETWEEN CAST(p.starts AS DATE) AND CAST(p.ends AS DATE);

With both bounds the search keeps the rows that start on or before the date whose running latest end reaches it, so periods that do not overlap cost one row each. An OR of equalities has no key and no range: it runs as a plain nested loop, comparing every pair — correct, but its cost grows with the product of the sides; split it into two keyed joins when the sides are large. EXPLAIN names the join (range) or (nested-loop), or — when a side’s columns are only known once it is read, as for a SQL table or a remote file — (no key: range or nested-loop, decided at run time); EXPLAIN ANALYZE then says which ran under the join. Such a join never spills, never runs in parallel lanes and takes no key pushdown: its right side must fit in memory, up to the join’s WITH (max_build = '…') or 4 GiB, past which it fails with the build-too-large error.

Which side is held in memory

An inner join holds its right side in memory and streams the left side through it, unless the left side is estimated at least four times smaller: then the left side is indexed and the right side streams. A chain of inner joins that all key on the columns of the table they start from (a fact table and its dimensions, f JOIN a ON f.x = a.x JOIN b ON f.y = b.y) runs its smallest side first. The result is the written query’s — same columns in the same order, same names (_r suffixes included), same rows — and only the order of the rows can differ. So neither choice is made unless the rows are reordered anyway: a GROUP BY, an aggregate or an ORDER BY must follow the joins, with only WHERE conditions, projections and other joins between. A pipeline that only filters, projects and joins keeps its source’s order, as Queries promises.

The estimates are cheap and taken when the query is planned: a Parquet file’s row count from its footer, a CSV, TSV or JSON-lines file’s from its size and the lines in its first 64 KiB, another local file’s size, a SQL table’s catalog statistics (PostgreSQL’s pg_class.reltuples, MySQL, StarRocks and Doris’s information_schema.TABLES.TABLE_ROWS, SQL Server’s sys.partitions), asked once per run. A side’s WHERE keeps its table’s estimate. Rows are compared with rows, else bytes with bytes; a side that cannot be estimated — a remote file, a folder, a SQL query, a REST read, a side that aggregates or joins before this join, a table never analyzed — keeps the join as written. A left side whose estimated bytes pass the join’s spill threshold is not moved into memory.

Only inner joins are turned around; left, right, full, semi, anti, cross and NOT IN joins always index their right side. The choice applies where a join runs serially; a pipeline whose joins run in parallel lanes (see above) probes with its head read in every lane and keeps the written order — a right side too big for the lanes sends the pipeline back to the serial plan, where the choice is made. Key pushdown follows the sides: a SQL right side streamed through an indexed left side takes the left side’s keys once that side is indexed, with no read-ahead. EXPLAIN ANALYZE (and run --explain) prints the choice under the join, e.g. build: left (est. 1.2k rows vs 3.4M rows); plain EXPLAIN only says when it will be decided. WITH (join_order = 'written') on a join keeps it as written, its right side in memory and its place in the chain:

SELECT o.id, c.name
FROM 'customers_eu.csv' c
JOIN 'orders.parquet' o ON o.cust = c.id WITH (join_order = 'written')
ORDER BY o.id;

Spilling

The build side is held in memory up to --op-memory (2 GiB by default), or the join’s own WITH (max_build = '16GB'). Past that the join spills: both sides are split by key into 16 partitions, a file per side each, in the run’s scratch directory, and joined one partition at a time, so memory holds one partition’s build rows. A partition whose build rows still pass the limit is split again into 16, and so on up to 4 levels (65,536 partitions); each file is deleted as soon as it is split or joined, so the disk holds about one copy of the data. A spilled join gives the same rows, but not in the same order — add ORDER BY if order matters. Splitting cannot separate rows of one key: a key whose rows alone pass 4 GiB (or --op-memory, if larger), or max_build when given, fails at once with “a single join key holds N rows, more than –op-memory allows” — raise --op-memory or max_build, or filter that key out; one under it is held whole. A partition still past that after 4 levels fails too. A NOT IN build side never spills, since one null on it changes every row’s answer, and fails with the build-too-large error past the limit. Under -j a build side past the limit sends the pipeline back to a serial run, which reads that side again — a second query for a SQL source. --spill-cap bounds the disk a run’s spills hold at once (8 GiB by default; a partition’s files count until it is joined) and --spill-dir puts them elsewhere; the files are removed when the run ends.

Key pushdown

When one side of a join is a SQL table, the other side’s key values narrow its read, so joining a small sheet to a large table reads only the table’s matching rows:

CREATE CONNECTION sr TYPE starrocks OPTIONS (host = 'fe', database = 'erp');
SELECT s.cr, c.name
FROM 'centers.xlsx' s
JOIN sr.erp.cost_centers c ON trim(c.code) = CAST(s.cr AS varchar);
-- the table is read as … WHERE TRIM(`code`) IN ('1005', '1048')

A SQL right side takes the left side’s keys: the left side is read first, into memory, and replayed into the join. Past 100,000 rows or 64 MB the read-ahead stops and the right side is read in full, so a large left side costs nothing extra; a join turned around (above) sends the keys once the left side is indexed instead. A local file of up to 64 MB joined to a SQL table runs serially even under -j, so the table can take its keys; a bigger one keeps its lanes. Otherwise a SQL left read right before the first join takes the right side’s keys once that side is indexed, under -j too, where every split reads with them. A side takes keys only where the join never outputs its unmatched rows — the right side of an inner, left, semi or anti join, the left side of an inner, right or semi one — and not under NOT IN. A side with anything but filters and selects between its read and the join, or a key that is no function of the read’s columns, takes nothing.

Up to 1,000 distinct values go as IN (…); past that a number, decimal, date or timestamp key goes as its >= min AND <= max range, and a text key as nothing, since the database’s collation may order text otherwise. A value the dialect cannot spell exactly drops that key’s predicate, never just the value: the read may return more rows than match, which the join discards, never fewer. An empty side reads nothing (WHERE 1 = 0). EXPLAIN names the side that takes the keys; WITH (key_pushdown = false) on the join turns it off.

The SQL side also asks only for the columns the join and what follows it use: above, the table is read as SELECT code, name FROM …, since the key reads code and the output name. A query that keeps every column (SELECT *, or a join with nothing after it) reads them all.

A local Parquet file or folder takes keys on the same terms, when no SQL side does: on the right of the join from the left side’s read-ahead, and on the left once the right side is indexed. A Parquet left side skips under -j too, in every lane; a Parquet right side only on a serial run. It reads no less of a row group it reads, but skips each row group whose min/max statistics hold none of the key values (or, past 1,000 of them, no part of their range). Only a number, decimal, date or timestamp key that is one of the file’s columns as it is counts: a key computed from them, a text key (whose statistics’ byte order the file does not pin down), or a row group without statistics never skips.

SELECT o.id, o.amount, c.name
FROM 'today.csv' c
JOIN 'orders/' o ON o.customer_id = c.id;
-- reads only the row groups of orders/ whose customer_id range holds a c.id

Aggregates

GROUP BY folds rows into one per group; the aggregate functions are listed under Queries. End to end:

SELECT region,
       COUNT(*)                AS orders,
       COUNT(DISTINCT customer) AS customers,
       SUM(amount)             AS revenue
FROM 'orders.parquet'
WHERE placed_at >= '2026-01-01'
GROUP BY region
HAVING COUNT(*) > 100
ORDER BY revenue DESC
LIMIT 10;

A plan-time constant may sit in the list alongside the aggregates, since it is one value for the whole query — a literal, arithmetic or || over literals, a call like now(), or a $param / $let. That is how an aggregate result carries a run id or a tenant tag:

PARAM tag STRING DEFAULT 'acme';
LET run_ts = now();
SELECT $tag AS tenant, $run_ts AS loaded_at, region, COUNT(*) AS orders
FROM 'orders.parquet' GROUP BY region;

A plain column still may not: it has no single value per group, so it must be wrapped in an aggregate or named in GROUP BY.

FILTER

Any aggregate takes FILTER (WHERE <condition>), folding only the rows where the condition holds — several slices of a group in one pass, where a WHERE would need a query each:

SELECT region,
       COUNT(*)                                       AS orders,
       COUNT(*)    FILTER (WHERE status = 'returned') AS returns,
       SUM(amount) FILTER (WHERE amount > 0)          AS revenue,
       COUNT(DISTINCT customer) FILTER (WHERE placed_at >= '2026-01-01') AS new_year_customers
FROM 'orders.parquet'
GROUP BY region
HAVING SUM(amount) FILTER (WHERE amount > 0) > 1000;

A null condition counts as false. Every aggregate skips nulls, so agg(x) FILTER (WHERE c) is read as agg(if(c, x, null)) and COUNT(*) FILTER (WHERE c) as count_if(c): the result is the same, and a filtered aggregate runs wherever a plain one does — on the parallel lanes, spilling, and over a window. An aggregate with no matching row is null (COUNT 0).

A float SUM is reproducible for a given -j, not across values of it. The lanes each total their own slice and the slices are added in a fixed order, so rerunning the same command writes the same bytes; but -j decides how the input is cut, and float addition is not associative, so -j 4 and -j 8 can differ in the last bits (as can either from -j 1). SUM over an INT or a DECIMAL is exact and identical everywhere — SUM(CAST(amount AS DECIMAL(18,2))) is the way to total money you intend to compare or checksum. COUNT, MIN and MAX are exact too, as are count_if, bool_and/bool_or and the bit_* aggregates. The variances and deviations combine their lanes the same fixed way, so they share SUM’s float caveat: ROUND them before comparing runs at different -j.

Past --op-memory, a GROUP BY spills to disk (see Spilling to disk): groups it already holds keep folding in memory, and rows of new groups go to disk by a hash of their key, to be aggregated once the input ends. Every result is exact — a float SUM included, since each group still sees its rows in input order — but the groups come out in another order, so add ORDER BY if the order matters. The disk it may use is bounded by --spill-cap. Spilling happens on a -j 1 run and on any aggregate the parallel lanes do not take; the lanes’ own per-lane tables do not spill.

Window functions

A window function computes a value for each row from the rows around it — its partition (PARTITION BY), in an order (ORDER BY), within a frame — without folding them into one, as GROUP BY would:

SELECT conta, data, valor,
       ROW_NUMBER() OVER (PARTITION BY conta ORDER BY data DESC)          AS recencia,
       SUM(valor)   OVER (PARTITION BY conta ORDER BY data)               AS saldo,
       AVG(valor)   OVER (PARTITION BY conta ORDER BY data
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)       AS media_7,
       valor - LAG(valor) OVER (PARTITION BY conta ORDER BY data)         AS variacao
FROM 'movimentos.csv';

Functions

• The ranking and distribution functions, NTILE, LAG and LEAD need an ORDER BY inside OVER (...) — it is what they number by — and take no frame; writing one is an error rather than being ignored.
• NTILE’s bucket count and NTH_VALUE’s position are positive whole numbers; LAG/LEAD’s offset a non-negative one. Their default is a constant — a literal, a $param or an expression over them — cast to the column’s type (LAG(amount, 1, 0)).
• DISTINCT is accepted inside COUNT only.
• The names are not reserved: a column called rank still reads as a column.

The window: PARTITION BY, ORDER BY

PARTITION BY is optional; without it the whole input is one partition, and its nulls group together as in GROUP BY. ORDER BY sorts within the partition, DESC per key; nulls sort last either way (NULLS FIRST / NULLS LAST are not accepted). Keys and arguments may be any expression — PARTITION BY substr(conta, 1, 3), ORDER BY qtd * preco, SUM(qtd * preco) — computed into hidden columns before the window runs.

A window used more than once can be named after HAVING:

SELECT conta, data, valor,
       SUM(valor) OVER w                                 AS saldo,
       AVG(valor) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_3,
       RANK()     OVER w                                 AS ordem
FROM 'movimentos.csv'
WINDOW w AS (PARTITION BY conta ORDER BY data);

OVER w takes the named window whole; OVER (w ...) may add an ORDER BY it lacks and a frame, never a PARTITION BY, and cannot extend a window that has a frame. One named window may build on another (WINDOW a AS (PARTITION BY conta), b AS (a ORDER BY data)).

Frames

The frame is the slice of the partition an aggregate or a value function reads:

{ ROWS | RANGE } <start>                       -- ends at CURRENT ROW
{ ROWS | RANGE } BETWEEN <start> AND <end>

• The default frame: without ORDER BY, the whole partition (a share of the total); with it, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — everything up to the current row and its peers (a running total in which ties share a value). So LAST_VALUE(x) OVER (ORDER BY t) is the last of the current row’s peers, not of the partition; write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for that.

• ROWS counts positions; RANGE compares values:

  -- ties do NOT share: 10, 30, 50 over v = 10, 20, 20
  SUM(v) OVER (ORDER BY v ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  -- ties DO share (the default): 10, 50, 50
  SUM(v) OVER (ORDER BY v)
  

• A frame is clipped at the partition’s edges, and may be empty (ROWS BETWEEN 3 FOLLOWING AND 5 FOLLOWING on the last row): an aggregate over no rows is null (COUNT is 0), a value function null.

• A start after its end is refused where it is written (ROWS BETWEEN CURRENT ROW AND 1 PRECEDING, ROWS 2 FOLLOWING, ROWS BETWEEN 1 PRECEDING AND 3 PRECEDING), as are UNBOUNDED FOLLOWING as a start and UNBOUNDED PRECEDING as an end. A ROWS offset is a whole number of rows. GROUPS frames and frame exclusion (EXCLUDE …) are not supported.

• A RANGE offset (<n> PRECEDING / FOLLOWING) needs exactly one ORDER BY key, a number, date or timestamp — check refuses any other. Over a number the offset is in its units (RANGE BETWEEN 0.5 PRECEDING AND CURRENT ROW over a price). Over a date or timestamp it is a number of days, or an INTERVAL '<n>' DAY | HOUR | MINUTE | SECOND (also written INTERVAL '<n> days'); a TIME key takes only the interval. With DESC “preceding” means larger keys, as the order runs. A row whose key is null has its peers, the other nulls, as its offset frame, and a non-null row’s offset never reaches the nulls (UNBOUNDED FOLLOWING does):

  SELECT data, valor,
         SUM(valor) OVER (ORDER BY data RANGE BETWEEN 6 PRECEDING AND CURRENT ROW)                 AS semana,
         COUNT(*)   OVER (ORDER BY data RANGE BETWEEN INTERVAL '36' HOUR PRECEDING AND CURRENT ROW) AS recentes
  FROM 'movimentos.csv';
  

• Without ORDER BY a ROWS frame counts in input order, as the rows arrive.

IGNORE NULLS

LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE take IGNORE NULLS — after the call, as in the standard, or inside it — to look through null values. RESPECT NULLS, the default, is accepted too. Carrying the last known value forward:

SELECT conta, data,
       LAST_VALUE(saldo) IGNORE NULLS OVER (PARTITION BY conta ORDER BY data) AS ultimo_saldo,
       LAG(saldo IGNORE NULLS)        OVER (PARTITION BY conta ORDER BY data) AS saldo_anterior
FROM 'movimentos.csv';

LAG(x, n) IGNORE NULLS is the n-th non-null value before the row (offset 0 the row’s own value), NTH_VALUE(x, n) IGNORE NULLS the frame’s n-th non-null one.

FILTER

An aggregate takes FILTER (WHERE <condition>) — over a window and under GROUP BY alike — and folds only the rows where the condition holds (a null condition counts as false):

SELECT conta, data, valor,
       SUM(valor) FILTER (WHERE valor > 0) OVER (PARTITION BY conta ORDER BY data) AS entradas_ate_aqui,
       COUNT(*)   FILTER (WHERE valor < 0) OVER (PARTITION BY conta)              AS saidas_na_conta
FROM 'movimentos.csv';

See Aggregates for how it runs.

Windows inside expressions

A window call can sit anywhere in a SELECT item: it is computed first under a hidden name, then the expression around it — ROW_NUMBER() OVER (...) + 1, valor - LAG(valor) OVER (...), ROUND(AVG(valor) OVER (...), 2), CASE WHEN RANK() OVER (...) = 1 THEN 'top' END. ORDER BY may repeat a window item of the SELECT list (or name its alias). Columns a window reads — keys, arguments, columns of the expression around it — need not be projected.

A window function cannot be nested in another, nor fed to an aggregate, and is refused in WHERE, GROUP BY, HAVING and ON, as in standard SQL: a row’s window is known only once the rows it filters are. Filter on one through a derived table or a CTE:

WITH ranked AS (
  SELECT conta, data, valor,
         ROW_NUMBER() OVER (PARTITION BY conta ORDER BY data DESC) AS rn
  FROM 'movimentos.csv')
SELECT conta, data, valor FROM ranked WHERE rn <= 3;

That shape is recognized: a lone ROW_NUMBER filtered with rn <= k (or <, =) keeps only each partition’s best k rows as they stream, rather than sorting them all.

Several windows

Functions with the same PARTITION BY and ORDER BY share one window stage, each with its own frame. A different one is a stage of its own, chained after the first and sorting its own input; the columns still come out in the SELECT list’s order. Without an outer ORDER BY the rows come out in the last stage’s order — add one when it matters.

Over a GROUP BY

With GROUP BY the windows run after the aggregate and HAVING, over one row per group, and may read the aggregates — the share of the total, a ranking of groups:

SELECT conta,
       SUM(valor)                                        AS total,
       ROUND(100.0 * SUM(valor) / SUM(SUM(valor)) OVER (), 1) AS pct,
       RANK() OVER (ORDER BY SUM(valor) DESC)            AS posicao
FROM 'movimentos.csv'
GROUP BY conta;

A column a window reads there must be a grouping key or inside an aggregate.

Types

All but the never-null ones are nullable: an empty frame, or one of nothing but nulls, has no value.

How it runs

A window is a breaker: a row’s value is not known until its whole partition has arrived, so memory is bounded by the input, as for ORDER BY, DISTINCT and GROUP BY (a window does not spill). Each stage sorts once, then resolves every row’s frame by two forward-moving edges, so a frame of any size costs the same: an aggregate adds the rows entering and takes back the rows leaving — MIN/MAX through a monotonic queue, the variances by Welford’s update and its inverse (exact over integers), COUNT(DISTINCT) by per-value counts. MEDIAN, bit_and and bit_or cannot take a row back: a frame that only grows adds to them, any other is recomputed. A float SUM or AVG over a sliding frame can differ from a fresh total in the last bits.

DISTINCT ON (...) remains the cheaper way to keep one row per key — without an ORDER BY it streams, where ROW_NUMBER() ... = 1 would not.

Subqueries

IN (SELECT ...) and the scalar subquery are supported as sugar over machinery that already existed — each is rewritten at parse time into the join or constant it always denoted, so nothing here adds a new execution path:

-- Planned as a SEMI JOIN against the (anonymous) subquery binding.
SELECT * FROM 'facts.csv' WHERE x IN (SELECT k FROM 't.csv');

-- NOT IN plans as an ANTI JOIN with SQL's NULL rule — see below.
SELECT * FROM 'facts.csv' WHERE x NOT IN (SELECT k FROM 't.csv');

-- A scalar subquery runs ONCE, before the outer query, and its single cell is
-- spliced in as a constant — so the comparison pushes down to the source like
-- any literal. This is the incremental-extraction idiom:
SELECT * FROM conn.orders WHERE ts > (SELECT max(ts) FROM 'lake/orders.parquet');

-- The same thing, named — useful when several statements share the value:
LET hi = (SELECT max(ts) FROM 'lake/orders.parquet');
SELECT * FROM conn.orders WHERE ts > $hi;

The subquery of IN must produce exactly one named column, and the test must sit as a top-level AND condition of WHERE — under an OR, a NOT or a CASE it cannot be lifted into a join without changing what the predicate means, so those shapes are parse errors (spell them as an explicit join). The left side must be a plain column. The literal-list form IN (1, 2, 3) is unchanged.

A scalar query — inline or as LET x = (SELECT ...); — must produce exactly one column; one row is the value, zero rows read as NULL (standard SQL), and more than one row is an error. The inline form is evaluated per statement, not per row: it may not reference columns of the outer query.

NOT IN is standard three-valued NOT IN. A single NULL from the subquery makes x NOT IN (...) unknown for every row, so no row survives; a NULL x survives only an empty subquery. To keep the rows that match no non-null key — what an extraction script usually means — say so in the subquery: WHERE k IS NOT NULL.

EXISTS has no spelling: the uncorrelated form is degenerate (all rows or none) and the useful form is correlated. A correlated subquery — one referencing a column of the outer query — has no equivalent here and is not planned: it needs either decorrelation in an optimiser or per-row execution of the inner query, and the second is fatal for a streaming engine. Rewrite it as a join — the equality correlation EXISTS usually carries is exactly IN on that key.

UNION, INTERSECT and EXCEPT

UNION ALL is SQL’s: branches line up by position, under the first branch’s column names, and must have the same number of columns. Types widen per column (an int meeting a float is a float); a pair with no common type is an error. UNION without ALL also removes duplicate rows — from everything to its left, so a UNION b UNION ALL c deduplicates a ∪ b and then appends c.

SELECT id, amount FROM 'eu.csv'
UNION ALL
SELECT id, total FROM 'us.csv'      -- `total` lands under `amount`
ORDER BY id;

INTERSECT keeps the rows found in both branches, EXCEPT those of the first found nowhere in the second; both line columns up by position and remove duplicates. Two NULLs count as the same value there, unlike in a join’s ON. INTERSECT binds tighter than UNION and EXCEPT (a UNION b INTERSECT c is a UNION (b INTERSECT c)). INTERSECT ALL and EXCEPT ALL are not supported.

BY NAME lines branches up by column name instead, and is what reconciling N similar tables needs. It applies to UNION only, and a chain is one or the other.

A BY NAME branch may be any query — a file, a filter, a projection, an aggregate — not only SELECT ['tag' AS c,] t.* FROM <conn>.<table>. That shape is the reconciliation case the feature was built for (N similar tables aligned by name) and it still gets the tag column and table discovery; a general branch is built like any other pipeline and reconciled the same way.

Alignment is by column name: NULL-fill missing, drop extra, and widen types where one holds the other (an int meeting a float is a float), as DuckDB’s UNION ALL BY NAME does. Unlike DuckDB, types with no common one (a date against a string) are an error rather than cast to text: CAST one side, or EXCEPT the column. UNION BY NAME also deduplicates.

-- explicit branches: the tag is just a literal column
SELECT '01' AS CT2_EMPRESA, t.* FROM erp.dbo.CT2010 t
UNION ALL BY NAME
SELECT '02' AS CT2_EMPRESA, t.* FROM erp.dbo.CT2020 t
ANCHOR SCHEMA erp.dbo.CT2010;          -- schema authority (optional)

-- discovered: one branch per row of a raw 2-column query (table, tag)
SELECT *
FROM EACH TABLE OF (erp.QUERY($$SELECT name, SUBSTRING(name,4,2) FROM sys.tables WHERE name LIKE 'CT2%'$$))
  AS (table_name, CT2_EMPRESA)         -- 2nd name = output tag column
  PUSHDOWN($$D_E_L_E_T_ <> '*'$$)      -- raw predicate on EVERY branch
  ANCHOR SCHEMA erp.dbo.CT2010;

The discovery source may also be a full basalt SELECT, executed in-engine at plan time (its translatable WHERE prefix still descends to the source as usual). The connection the discovered tables live in is inferred from the query’s leading read when it names a connection; IN <conn> overrides it:

SELECT *
FROM EACH TABLE OF (SELECT name, substr(name, 4, 2) FROM erp.sys.tables WHERE name LIKE 'CT2%')
  AS (table_name, CT2_EMPRESA)
  ANCHOR SCHEMA erp.dbo.CT2010;

JSON form (array of {table, tag} objects, e.g. from a request body): FROM EACH TABLE OF ($job.tables) IN erp AS (table_name, tag) — element keys remappable via WITH (table_field = ..., tag_field = ..., tag_substr = '4,2').

LOAD INTO

LOAD INTO sr.silver.pedidos            -- conn[.schema].table, or a quoted path
  USING stream_load                    -- load path; optional, starrocks/doris only
  UPSERT ON (empresa, num_pedido)      -- disposition (below)
  SPLIT BY (num_pedido) JOBS 4         -- key-range parallel load
  WITH (label_prefix = 'noturno')      -- overrides the connection's label_prefix
AS
<query>;

• File target by quoted path — the extension picks the writer: LOAD INTO '/out/x.csv', LOAD INTO '/out/x.parquet', LOAD INTO '/out/x.arrow' (Arrow IPC file; also .feather, .ipc, and .arrows for the stream format, local paths only), or an object-store path LOAD INTO 'az://account/container/bronze/x.parquet' or LOAD INTO 's3://bucket/bronze/x.parquet'.
• USING stream_load names the one load path StarRocks and Doris have, and may be left out; on any other target, or with another name, it is an error.
• A per-row dynamic target uses IDENTIFIER(<string-expr>) over loop vars (FOR EACH): LOAD INTO sr.IDENTIFIER('crm_' || lower($name)) .... A file target built this way must end in a literal extension, which picks the writer.
• .xlsx is read but never written: a workbook target is a plan-time error.
• UPSERT needs a table to merge into; on a file target it is a plan-time error.
• WITH (...) on a LOAD INTO takes delimiter and format (a file) and label_prefix (StarRocks, Doris); any other key is a plan-time error rather than ignored.
• Dispositions on a table target: APPEND (default, omissible) · REPLACE (overwrite) · UPSERT ON (k1, k2) · UPSERT ON (id) PARTIAL COLS (a, b) · bare UPSERT (infer the PK from the source table’s metadata at plan time — needs a table read on a SQL source that exposes it). An empty/unresolved upsert key is an error, never a silent no-op.
• Dispositions on a file target — the omissible default is not APPEND: a bare LOAD INTO 'x.csv', like REPLACE, creates or truncates the file, so a rerun replaces it. Explicit APPEND accumulates for CSV only: the file is opened without truncating and the header row is written only when it was absent or empty. Explicit APPEND is a plan-time error for .parquet and Arrow IPC (the footer indexes every row group or batch and is written last, so appending means rewriting the file), for az:// / s3:// (an object is replaced on write, never extended), and for sftp:// / smb:// (the file is written beside the target and renamed over it) — use REPLACE, a per-run path, or INTO BUFFER (HTTP mode).
• SPLIT BY (col) parallelizes the load by key ranges; JOBS n fixes the lane count (otherwise the CLI -j applies).

stdout is not syntax: a terminal SELECT ...; statement prints the result as an aligned table — basalt run -c "SELECT * FROM 'x.csv'" works as a mini-DuckDB.

FOR EACH and the CASE statement

Plan-time fan-out — one pipeline (or dispatch) per row of a discovery source. A catalog of tables, each read and loaded under a per-row name:

FOR EACH ROW OF ($tables) AS (name, where)
  PARALLEL ON ERROR CONTINUE           -- or SEQUENTIAL / ON ERROR STOP
  LOAD INTO sr.IDENTIFIER('fluig_' || lower($name))
    USING stream_load UPSERT AS        -- bare UPSERT: PK inferred from source
  SELECT *, now() AS extraction_timestamp
  FROM fluig.dbo.IDENTIFIER($name)     -- a per-row TABLE read
  PUSHDOWN($where);                    -- raw predicate value ("" ⇒ no WHERE)
END FOR;

• Sources: a raw discovery query (conn.QUERY($$...$$), first N columns → N loop vars positionally), an in-engine SELECT query (any basalt query, run once at plan time; first N columns → N loop vars positionally), or a JSON param path ($tables, $job.tables, …; object fields bound to the loop vars by name, a missing field ⇒ "").
• The body holds queries (LOAD INTO / SELECT, each with its own WITH), nested FOR EACH ROW OF, the CASE statement, CALL, PRINT, EXPLAIN and THROW. Declarations — PARAM, LET, CREATE CONNECTION, CREATE FUNCTION — belong at the top level; check and run refuse one in a body alike. A top-level CASE arm may declare a connection, so a script can pick its endpoint per environment; a PARAM, LET or function there is refused.
• Loops nest. The inner loop’s discovery source is rendered with the outer row (FOR EACH ROW OF (erp.QUERY($$SELECT ... WHERE t = '${name}'$$))), and the inner body sees both rows’ variables, the innermost winning a shared name. Each PARALLEL loop fans out over its own rows.
• A WITH inside a body is rendered per row like the query that reads it, and is scoped to the body: after the loop the name means whatever it meant before. At the top level a WITH is visible from its statement onwards, so two statements may reuse a CTE name and each reads its own.
• Loop variables may be typed: AS (name, port:INT).
• A loop variable is also an ordinary expression value: SELECT $name AS empresa, WHERE $port > 1000 (typed vars compare as their declared type). Only $name is the loop variable: a bare name in the body is still the source column of that name, as it is beside a PARAM.
• The CASE statement (... THEN <statements> ... END CASE) dispatches whole pipelines per row — subject form (CASE $env WHEN 'prod', 'staging' THEN ... END CASE) and the guard form. END CASE distinguishes it from the CASE expression (Expressions). Use it when the branches are different pipelines (different sources/sinks); for choosing a value, put the conditional in the expression (IDENTIFIER(if($pk = '', $name || 'id', $pk))).

Dynamic names — $var, IDENTIFIER(), ||

Loop variables (and params) are referenced with $ — $name, $where — resolved by name per row. A name is computed from them by an ordinary string expression, and IDENTIFIER(<string-expr>) turns that string into a table or object reference (the precedent is Snowflake / Databricks IDENTIFIER). || is string concat; lower(), if(), concat() compose as usual.

In an expression or a name position (SELECT list, WHERE, GROUP BY, ORDER BY, DISTINCT ON) IDENTIFIER(<string-expr>) is a column whose name is computed per row — or once, from a PARAM, outside any loop. The column is only known when the row renders it, so check validates the stages before the first dynamic name and leaves the rest to run, which reports an unknown column per row (``for-each row c=nope: unknown field `nope```).

IDENTIFIER($name) resolves to a table read, so bare UPSERT still infers the PK from source metadata — a raw QUERY(...) read cannot. This is why the catalog holds only {name, where}, never a PK.

Raw ${...} interpolation (raw SQL bodies only)

Inside a raw QUERY($$...$$) or PUSHDOWN($$...$$) literal, ${var} / ${ <expr> } still splices loop values into the SQL text (C#-style: nested string literals in the hole need no escaping) — QUERY($$SELECT ${cols} FROM ${name}$$). Prefer $var + IDENTIFIER() everywhere a name is meant; reach for ${...} only when you are literally building a raw SQL string.

User functions

CREATE FUNCTION names an expression, a block of statements or a query, so a script — or a library it @includes — can reuse it.

Scalar and statement functions

CREATE [OR REPLACE] FUNCTION nome(a [TYPE] [DEFAULT <expr>], ...) — two body forms. AS <expr>; is a scalar function, inlined at plan time; recursion and arity mismatches are compile errors, declared types are checked against literal arguments at the call site, defaults fill omitted trailing arguments. A body starting with LOAD/FOR/CALL/SELECT/WITH/PRINT/THROW, or with the statement CASE (the one closed by END CASE), is a statement function terminated by END; and invoked with CALL nome(args); — its params bind like loop variables ($name, IDENTIFIER($name), PUSHDOWN($f), ${name} in strings), rendered per call through the same machinery as a FOR EACH ROW OF body. CALL nesting is depth-guarded (16); a statement function is not atomic — a mid-body failure leaves earlier loads committed, exactly as if the statements were inline. Plain re-declaration of a name is an error; OR REPLACE is the sanctioned overwrite.

Table functions

CREATE [OR REPLACE] FUNCTION nome(a [TYPE] [DEFAULT <expr>], ...) RETURNS TABLE AS <query>; is a table function: a query with parameters, read wherever a CTE could be — FROM nome(args) [alias], or the right side of a JOIN. Each call is the body with every $a replaced by its argument, lowered to a derived table at plan time, so it behaves exactly as if the query were written inline; two calls in one query (even of one function) are independent, WITH clauses inside the body included. Without an alias, the function’s name qualifies its columns (paid.id). Arguments are plan-time constants — literals, $params, loop variables, and expressions over them — except under JOIN LATERAL (below). Arity, defaults and literal-argument types are checked as for a scalar function, the body is checked where it is declared, and an @included table function is called like a local one. A body may call table functions declared before it; a call that reaches its own function (possible only through OR REPLACE) stops at 16 levels. A discovery query — FOR EACH ROW OF (...), EACH TABLE OF (SELECT ...) — may call one too, as it may read a derived table or open with WITH: their bindings run ahead of the loop.

CREATE FUNCTION paid_orders(since DATE, branch STRING DEFAULT '01') RETURNS TABLE AS
  SELECT C5_NUM AS num, C5_CLIENTE AS cliente, C5_EMISSAO AS emissao
  FROM erp.dbo.SC5010 PUSHDOWN($$D_E_L_E_T_ <> '*'$$)
  WHERE C5_FILIAL = $branch AND C5_EMISSAO >= $since;

SELECT num, cliente FROM paid_orders($desde) WHERE cliente <> '000001';

JOIN LATERAL

CROSS JOIN LATERAL f(o.col) x (or JOIN LATERAL, or LEFT JOIN LATERAL … ON TRUE to keep rows with no match) passes each row’s column. It is not a query per row: where the body says column = $param, that conjunct leaves the body and the call becomes an ordinary hash join ON o.col = x.column — the table is read once, its other conditions still reach the source, and lanes apply as to any join. So the body has to be one the join can stand for: a read, joins, filters and a SELECT list, with the parameter only in column = $param conjuncts of its WHERE (or on its own as a SELECT item, which is then that column). A LIMIT, DISTINCT, GROUP BY, window or set operation, a parameter compared any other way, or one never compared at all is refused at plan time, naming it: a join could not apply them per row. A body that does not select the column joins on it under the parameter’s name, which x.* then shows. Constant arguments in the same call stay constants.

CREATE FUNCTION itens(pedido) RETURNS TABLE AS
  SELECT C6_ITEM AS item, C6_PRODUTO AS produto, C6_QTDVEN AS qtd
  FROM erp.dbo.SC6010 PUSHDOWN($$D_E_L_E_T_ <> '*'$$)
  WHERE C6_NUM = $pedido;

-- one read of SC6010, joined on C6_NUM = p.num
SELECT p.num, p.cliente, i.produto, i.qtd
FROM paid_orders($desde) p CROSS JOIN LATERAL itens(p.num) i;

DESCRIBE, SHOW TABLES and EXPLAIN

DESCRIBE and SHOW TABLES

DESCRIBE <source>; prints one row per column — column, type, nullable — for a file, a conn.schema.table, a conn.QUERY($$...$$) or a whole query (DESCRIBE SELECT ..., CTEs included). The types are the engine’s, i.e. what a sink would receive; a table or query is asked for no rows, so it is cheap on a large one. SHOW TABLES FROM <conn>[.<schema>] [LIKE 'pattern']; lists a SQL source’s tables and views from its information_schema (table_schema, table_name, table_type), system schemas left out; on an http connection it lists the declared resources (HTTP APIs). Both are ordinary result statements: basalt run --format json -c "DESCRIBE erp.dbo.SC5010;" gives a program the schema, and the REPL’s \d and \dt are the same statements.

EXPLAIN

EXPLAIN <statement> prints the plan instead of running it. EXPLAIN ANALYZE <statement> runs it and prints the operator tree with the time and row count each stage actually cost — time is exclusive, so a stage’s figure excludes its inputs.

Both print the same tree: root first, the scan deepest, which is the nesting a pull pipeline has. EXPLAIN annotates each operator with its detail and output schema, EXPLAIN ANALYZE with what it measured.

$ basalt run -c "EXPLAIN SELECT g, COUNT(*) AS c FROM 'x.parquet' GROUP BY g;"
plan
  write  stdout  (default)
    aggregate  1 agg(s), 1 group(s)
      schema: g:string?  c:int
      scan  parquet  x.parquet
        schema: g:string?  v:int?
  physical: morsel-parallel candidate (per-lane partials, combined)

$ basalt run -c "EXPLAIN ANALYZE SELECT g, COUNT(*) AS c FROM 'x.parquet' GROUP BY g;"
plan (actuals, exclusive time)
  aggregate      13.1ms          601 rows        2 batches
    scan          6.2ms       500000 rows        6 batches

The physical: line names the fan-out the plan is eligible for: split-parallel for a SQL read divided into key ranges, morsel-parallel for a local CSV divided into byte-range chunks or a parquet divided into row groups, serial for everything else (a CSV over HTTP is fetched whole, so it parses serially). Both stay “candidate” because what settles it is inside the source — a table’s key and size, a CSV that quotes a newline, a parquet with one row group — and neither EXPLAIN nor check reads that far.

A source whose schema only the source itself can describe — a database table, a remote object — reads schema: unresolved, said once at the scan rather than repeated down the tree. Neither form connects.

A SQL read prints the WHERE it will send on a pushdown: line under its scan (Pushdown). A join prints its right side’s read on a right line under it, with that read’s own pushdown: — so a CTE or table function joined in shows that it filters at the source:

$ basalt run -c "... EXPLAIN SELECT o.id, c.nm FROM pg.orders o JOIN active(1) c ON o.cid = c.cid;"
plan
  write  stdout  (default)
    select  id, nm
      join  inner __tvf1_active
        right  scan  postgres  table customers (via binding __tvf1_active)
          pushdown: ("active" = 1)
        scan  postgres  table orders
          schema: unresolved
  physical: split-parallel candidate

EXPLAIN is an ordinary statement: it goes anywhere a terminal SELECT or a LOAD INTO goes, and explains that one query against whatever the statements above it declared — connections, CTEs, PARAMs, LETs, functions. Everything before and after it runs normally, at full parallelism, and a script may hold several. A script that opens with EXPLAIN still explains the whole script, offline: no params are bound and nothing runs, which is the form to reach for when the point is to inspect a job rather than run one.

A plan printed by an EXPLAIN statement goes to stderr, like the EXPLAIN ANALYZE tree and PRINT: the statements around it may be writing rows to stdout, and stdout is the data contract (Command line). The whole-script EXPLAIN prefix prints to stdout instead — there the plan is the invocation’s only output, so basalt run -c "EXPLAIN ..." > plan.txt still captures it.

$ basalt run -c "CREATE CONNECTION pg TYPE postgres OPTIONS (host = 'db', database = 'erp');
                 EXPLAIN SELECT SUM(v) AS v FROM pg.public.t;"

EXPLAIN ANALYZE moves no data. The pipeline runs — that is where the numbers come from — but every sink is discarded: a terminal SELECT prints its plan instead of its rows, and a LOAD writes nothing, creates no table and commits no blob. A database can wrap an explained INSERT in BEGIN ... ROLLBACK; a load into a remote lake has no undo, so it is never performed.

It also runs serially, whatever -j says, so the tree is one operator tree rather than a different report per parallel path. Read the timings as a serial profile: the row counts and the shape are exact, the durations are not what the same query costs at full parallelism.

EXPLAIN COSTS is rejected at parse time — there is no cost model to report.

HTTP mode

CREATE ENDPOINT '/eventos' DOC 'Recebe telemetria';

LOAD INTO sr.bronze.eventos USING stream_load AS
SELECT device_id, CAST(ts AS TIMESTAMP) AS ts, tipo, now() AS recebido_em
FROM BODY (
  device_id STRING NOT NULL,
  ts        STRING,
  tipo      STRING,
  payload   JSON
)
WHERE tipo IN ('leitura', 'alarme');

• basalt serve <dir> hosts every *.sql endpoint script in the folder, routed by the declared path, on port 8080 unless --port (-p here) says otherwise, listening on every interface unless --host 127.0.0.1 (or another address) narrows it; DOC feeds the startup banner. SIGHUP, or --watch on a change, reloads the scripts. GET /healthz and /readyz answer 200 for a load balancer. basalt run script.sql --port N serves a single script the same way.
• FROM BODY (schema) declares the request contract. The body (JSON array or single object) is validated row by row: a missing/null NOT NULL column or an unreadable value rejects the request with a message naming the row — served as 422. Extra keys are dropped. JSON columns ride as text.
• FROM HEADER('X-Tenant') on a PARAM binds it from that request header (case-insensitive); bare FROM HEADER matches the param’s own name.
• Status contract: success → 200 + summary JSON; per-item failures → 207; permanent error → 422; transient → 503 + Retry-After.

Durable buffer (WAL)

ACCEPT ... INTO BUFFER turns the endpoint into a queue: 200 means “accepted durably” (fsynced), and the load happens asynchronously.

CREATE ENDPOINT '/eventos'
  DOC 'Recebe telemetria; ack após persistir em disco'
  ACCEPT BODY (
    device_id STRING NOT NULL,
    ts        STRING,
    payload   JSON
  )
  INTO BUFFER 'eventos'
    AT '/var/lib/basalt/wal'
    SEGMENT 16 MB
    RETAIN UNTIL LOADED;          -- or: RETAIN 24 HOURS (allows reprocessing)

LOAD INTO sr.bronze.eventos USING stream_load AS
SELECT device_id, CAST(ts AS TIMESTAMP) AS ts, payload, now() AS recebido_em
FROM BUFFER 'eventos'
  FLUSH EVERY 5 SECONDS OR 50000 ROWS;

• Requests are validated against the ACCEPT BODY schema (422 naming the row), appended to append-only JSONL segments, and acked after one fsync (group commit: N rows, one sync).
• A flusher thread drains completed segments through the pipeline, one run per segment. The StarRocks label is derived from the segment name (eventos-000042), so a crash between “loaded” and “marked” replays the same label and the sink dedups — effectively exactly-once, no 2PC.
• Backpressure: buffer disk usage over the limit — MAX 2 GB in the clause, 1 GiB by default — ⇒ 503 + Retry-After; the client is the queue. Sizes are written in KB, MB or GB.
• Without AT, segments go to wal/ under the working directory.
• Batch replay: FROM BUFFER 'eventos' AT '<dir>' in a plain batch script reads every retained segment — the queue is just another source.
• Honest cost: serve becomes stateful (the WAL directory needs a persistent volume) and durability is the node’s disk, not replicated.

Expressions and types

SQL-ish, Pratt-parsed. Precedence (high→low): unary - NOT ~ → * / % → + - || → << >> → & → ^ → | → comparisons = == != <> < <= > >= LIKE IN IS → ?? → AND → OR.

Scope rule: $name is script/environment scope — a PARAM, a LET, or (in a FOR EACH ROW OF / statement-function body) a loop variable, resolved at plan time. Bare names are row/local scope — columns, LET … IN bindings, aliases — and a PARAM, LET or loop variable never stands in for one: with PARAM region, WHERE region = 'West' filters on the column and WHERE region = $region on the parameter. Among $ names the innermost binding wins: loop var > LET/PARAM. A $name that nothing binds is a plan-time error naming it — never the column it happens to spell — and check reports it even over a table whose columns it has not seen.

• $name — see the scope rule above. $job.a?.b navigates a JSON param.

• Bitwise (INT only, engine-side — never pushed down): & | ^ << >>, unary ~. ^ is xor. >> is arithmetic; shift counts < 0 or >= 64 yield 0 (-1 for >> of a negative). Companions: bit_count() to_hex() from_hex().

• a || b — string concat (ANSI), sugar for concat(a, b).

• IDENTIFIER(<string-expr>) — treat a computed string as a name (dynamic names): a table or path in FROM/LOAD INTO, an upsert key, or a column wherever a column may be named.

• CASE expression, both forms: CASE status WHEN 'paid', 'ok' THEN 'done' ELSE 'open' END · CASE WHEN amount >= 1000 THEN 'gold' WHEN amount >= 100 THEN 'silver' ELSE 'std' END

• IF(c, a, b) kept as sugar.

• x IS [NOT] NULL · x IS [NOT] EMPTY (true when null or ''; string operands only — handy for loop values).

• a ?? b — null-coalesce (sugar for COALESCE).

• CAST(x AS INT) / CAST(x AS DECIMAL(18,2)) / CAST(x AS DATE) / CAST(x AS TIMESTAMP) — implicit widening is int→float/decimal only. Text parses as YYYY-MM-DD[ HH:MM:SS]. Text to a number is strict: an optional sign, digits and at most one ., and anything else fails rather than being guessed at. That matters wherever money is written the Brazilian or European way — '1000,00' is not a number basalt will read, rather than a guess that might be 100000.00. Strip the separator first: CAST(replace(v, ',', '.') AS DECIMAL(18,2)), or reach for TRY_CAST to turn unreadable values into nulls.

• A decimal that loses digits rounds half away from zero, as PostgreSQL and SQL Server round: CAST('12.345' AS DECIMAL(10,2)) is 12.35 and -12.345 is -12.35. The rule is the same for a cast, for a value written to a file column of smaller scale (Parquet, Arrow), and for an aggregate’s result — one answer whichever path a value takes. A database sink is sent the value whole and its column does its own rounding. A float converts through its 15 significant digits, as PostgreSQL converts float8 to numeric: the double nearest 2.675 is 2.67499…, and it still becomes 2.68. A float too large for 38 digits fails the cast.

• DECIMAL arithmetic is exact. +, - and % over two decimals (or a decimal and an int) answer a decimal at the wider operand’s scale, * one at the summed scale — CAST(1.1 AS DECIMAL(18,2)) + CAST(0.3 AS DECIMAL(18,2)) is 1.40, not 1.4000000000000001, so a SUM cast to DECIMAL stays exact when this month’s total is subtracted from last month’s. / has no finite scale and stays float, as does anything with a float operand (a literal like 0.3 is a float). round(x, n) on a decimal is exact too, and with a literal n answers DECIMAL(p, n); -x stays a decimal, and CAST(x AS INT) rounds half away from zero. % takes the dividend’s sign for every numeric kind: -5.5 % 2 is -1.5.

• A DATE/TIMESTAMP column compares directly against an ISO string literal (WHERE d >= '2013-07-01'). The literal is coerced to the column’s type, never the reverse, and it is validated at plan time — so '2013-13-01' and '01/07/2013' are errors from check, not silent text comparisons.

• "Valor Total" — a quoted column name (Scripts), valid anywhere a bare name is: SELECT, WHERE, GROUP BY, ORDER BY, an alias (AS "Total Geral"), and after a qualifier (t."Valor Total").

• x LIKE 'a%', x IN (1, 2, 3) (expands to an OR-chain), x [NOT] BETWEEN a AND b (inclusive; expands to x >= a AND x <= b, so it pushes down like any other pair of comparisons).

• LET x = <val> IN <body> — local binding, inlined at plan time.

• TRY_CAST(x AS T) — CAST that yields null instead of failing on a bad value; the workhorse for dirty inputs. Never pushed down.

• CAST(x AS TIME) takes 'HH:MM:SS[.ffffff]' or 'HH:MM' text, or a timestamp (its time of day).

• Scalar functions are listed on Scalar functions, and the JSON ones on JSON functions.

Scalar functions

Function names are case-insensitive. A function given a null argument answers null unless its row says otherwise. Aggregates (COUNT, SUM, MEDIAN, …) are listed under Queries, window functions under Window functions, the JSON functions on their own page, and CAST, TRY_CAST, IF, CASE and the operators under Expressions and types.

Strings

length(), substr(), upper()/lower(), the functions in this table and LIKE’s _ count characters, as Postgres and DuckDB do; strlen() counts bytes. A byte that is not UTF-8 counts as one character, so mis-encoded text never fails a load.

Searching rows

search(cols, text[, unaccent]) is true when a row holds text in the given columns: * for every column, t.* for one table of a join, * EXCEPT (a, b) or t.* EXCEPT (…) to leave some out, (a, b) or a single column to name them. The text is a small query: words must all appear, each in any of the columns; -word keeps the rows without it; col:word looks in that column only (a name that is none of them leaves the whole token as text); "two words" is one phrase. All of it ignores case — accented, Greek and Cyrillic letters too, as upper() and lower() fold them, so crédito finds CRÉDITO (an accent still counts: credito does not, unless the third argument is true, which drops accents on both sides as unaccent() does — credito then finds CRÉDITO and strasse finds Straße; it may be a bool $param) — and a value is matched as basalt prints it — a date as 2026-03-01, a decimal at its scale — so 2026-03 finds March and 10.50 that amount; a NULL holds nothing. The REPL’s \view find (f) is the same search over the rows it shows.

SELECT * FROM 'orders.csv' WHERE search(*, 'north -cancelled');
SELECT o.id, c.name FROM 'orders.csv' o JOIN 'customers.csv' c ON c.id = o.cust
WHERE search(c.*, '"são paulo"');
SELECT COUNT_IF(search((note, memo), 'urgent')) AS urgent FROM 'orders.csv';
SELECT * FROM 'payments.csv' WHERE search(kind, 'credito', true);

It reads whole rows, so it is never pushed to a source: a SQL read sends every column and filters here, and a WHERE with search stays after the selects and joins it follows. In a join’s ON it applies to the joined pair — filtering an inner join, deciding the matches of an outer one. The columns are resolved when the query is planned, so an unknown name fails check; the text may be a literal or a $param. Every searched value is turned into text for each row, so naming the columns (search((a, b), …)) is faster than * on a wide table.

Regular expressions

The pattern syntax is on Regular expressions. A literal pattern is compiled at plan time, so a malformed one fails check.

Hashes and encodings

The hashes are over the value’s text. For a row’s change key, write md5(concat_ws('|', a, b, c)): concat would make it null whenever a column is.

Numbers

Nulls and comparison

Dates and times

The units are year, month, week, day, hour, minute and second. week is the ISO week: it starts on Monday, extract numbers it 1–53 with week 1 holding the year’s first Thursday, and date_diff counts the Mondays crossed. check rejects an unknown unit even over a SQL table whose columns it has not seen.

The format codes are %Y %m %d %H %M %S %y %%. strptime takes a number shorter than its width (3/1/2026) and pivots %y at 69. Text that does not fit the format, or a day that does not exist, is an error that stops the statement, as a failed CAST does — one bad date in a file loads nothing; try_strptime makes it null instead and the load goes on.

Bits

The bitwise operators & | ^ << >> ~ are under Expressions and types.

JSON functions

JSON documents travel as text: a JSON column, a nested Parquet or Arrow column, a REST response’s nested object. These functions build and take them apart; CROSS JOIN UNNEST(JSON_EACH(col)) turns an array into rows (see Queries).

Building a document

JSON_OBJECT(k1, v1, …), JSON_ARRAY(v1, …) — build a JSON document, for a request body or a nested column: numbers and booleans as themselves, text as a JSON string, a null as null (the result itself is never null), and a NaN or infinity as null too, since JSON has neither. Text that is a JSON object or array — another json_object, or what json_get returned — goes in as that object or array, so json_object('id', id, 'tags', json_array(a, b)) nests. A null key fails the statement.

Reading a value

JSON_GET(doc, path) — one value out of a JSON document, as text: path is a.b, a[0].b or a.0.b (a leading $. is allowed). Strings come back unquoted, objects and arrays as JSON text; a missing key, an index past the end or a JSON null is null. A cell that is not JSON is an error, not a null. CAST(json_get(doc, 'n') AS INT) for a typed value.

Lambdas over an array

JSON_FILTER(arr, x -> cond), JSON_TRANSFORM(arr, x -> value), JSON_ANY(arr, x -> cond), JSON_ALL(arr, x -> cond) — work on a JSON array in place, without UNNEST and re-aggregating: the lambda’s body is evaluated once per element with its parameter bound to it. json_filter keeps the elements whose condition is true, as written; json_transform returns the array of the body’s values; json_any / json_all whether the condition is true for some / every element (an empty array: false / true). A nested Parquet list reads as exactly such an array.

SELECT id,
       json_filter(tags, t -> t LIKE 'vip%')                    AS vip_tags,
       json_transform(items, i -> json_get(i, 'sku'))           AS skus,
       json_any(items, i -> CAST(json_get(i, 'qty') AS INT) = 0) AS has_empty_line
FROM 'orders.parquet'
WHERE json_any(tags, t -> t = 'new');

An element is the parameter as itself — a number is a number (x > 5), a string a string, true/false a BOOL — and an object or array is its JSON text, which json_get and these functions take apart (lambdas nest). The body may name the row’s columns too (t -> t = region); the parameter shadows a column of its name. In json_transform’s result, text that is a JSON object or array (what json_get returns for one) goes in as that object or array, any other text as a string. An element the body cannot compare — a number against text, in an array that mixes kinds — counts as null (the condition is not true; json_transform puts null); a failing CAST still fails the run. A null cell is null; a cell that is JSON but not an array is an error. A lambda is an argument of these functions and json_reduce only, and never descends to a SQL source — the row is filtered here, while the rest of its WHERE still descends. Written (x, i) -> …, the lambda also gets the element’s position, from 0 as in a json_get path.

Folding an array

JSON_REDUCE(arr, initial, (acc, x) -> value) — a fold: acc starts at initial and becomes the body’s value at each element in turn; the result is the last one, initial for an empty array and null for a null cell. (acc, x, i) -> … adds the position. The accumulator keeps initial’s type, widened to what the body returns from it (a DECIMAL total stays a DECIMAL, a date a date); a FLOAT element arriving in an INT or DECIMAL total stops the statement rather than being cut short — start from 0.0 to sum floats. An element the body cannot compare stops it too, where json_transform would put null: a null mid-fold would quietly undo everything folded before it.

Arrays

CHARS(s) is a string’s characters as a JSON array, JSON_RANGE([start,] stop) the integers start (default 0) up to but not including stop, JSON_LENGTH(arr) an array’s element count, JSON_SLICE(arr, start[, stop]) its elements from start to before stop (from 0; a negative bound counts from the end, as in Python) and JSON_CONCAT(a, b, …) the arrays joined (null when any is). Together with json_reduce they make a per-character algorithm a function — a CNPJ check digit, letters included (the July 2026 alphanumeric CNPJ counts a character as its code point minus 48):

CREATE FUNCTION cnpj_dv(base, weights) AS
  LET s = json_reduce(chars(base), 0, (acc, c, i) ->
            acc + (ascii(c) - 48) * CAST(json_get(weights, CAST(i AS STRING)) AS INT))
  IN CASE WHEN s % 11 < 2 THEN 0 ELSE 11 - s % 11 END;

CREATE FUNCTION cnpj_valid(raw) AS
  LET d = translate(upper(raw), './-', '')
  IN length(d) = 14
     AND cnpj_dv(left(d, 12), '[5,4,3,2,9,8,7,6,5,4,3,2]') = ascii(substr(d, 13, 1)) - 48
     AND cnpj_dv(left(d, 13), '[6,5,4,3,2,9,8,7,6,5,4,3,2]') = ascii(substr(d, 14, 1)) - 48;
-- cnpj_valid('11.222.333/0001-81') and cnpj_valid('12.ABC.345/01DE-35') are true

Regular expressions

The regex engine behind regexp_matches, regexp_extract and regexp_replace is a subset, sized for the patterns SQL actually carries rather than for a dependency. It supports ^ $ . |, character classes with ranges and negation, capturing and non-capturing groups, the escapes \d \w \s (and their negations), the quantifiers * + ? and counted {n} {n,} {n,m} — each greedy, or lazy with a ? suffix (*?, {2,4}?), and \n \t \r and an escaped punctuation character (\., \$). Lookaround, backreferences (\1) and every other letter or digit escape (\b, \A) are rejected at compile time rather than read as literal characters, so an unsupported pattern is an error and never a silently wrong match.

SQL databases

Postgres, MySQL, SQL Server, StarRocks and Doris are read with a SQL query and written by bulk load or insert; their options are listed under Connections. What is particular to each follows.

StarRocks

A starrocks connection is both ends: LOAD INTO sr.t writes by stream load (be_url), and FROM sr.db.t / sr.QUERY($$...$$) reads through the FE’s MySQL protocol (fe_host/host, fe_port/port, default 9030) with the same user/password — no second mysql connection pointed at the FE is needed. Reads get everything a SQL source gets: WHERE and whole-aggregate pushdown in the StarRocks dialect, key-range splits under -j, and FOR EACH ROW OF (sr.QUERY(...)) discovery.

Before a load, the target’s database and table are created only if information_schema does not list them (auto_create = false skips this altogether). A role allowed to load into an existing table therefore needs no CREATE privilege; one that does lack a privilege it needs gets StarRocks’ own message in the error (starrocks refused 'CREATE TABLE …': Access denied; you need …), not only in the log.

Doris

A doris connection is the same in every respect — Doris is the project StarRocks forked from, read through its FE and loaded by stream load — except for the tables it creates. Doris takes no FLOAT, DOUBLE or STRING column as a key, so an APPEND or REPLACE target is a duplicate table with no sort key (DISTRIBUTED BY RANDOM), where StarRocks keys on the first column; an UPSERT ON (k) target is a UNIQUE KEY table with merge-on-write, its text keys VARCHAR(65533), and PARTIAL COLS loads as Doris’ partial_columns. Timestamps are DATETIME(6), to keep microseconds. Loads run in Doris’ strict mode, so a value that does not convert fails the load instead of landing as NULL.

SQL Server

Named SQL Server instances: write host = 'sql01.corp.local\SALES'. When a host carries a \INSTANCE and no explicit port is given, basalt resolves the instance’s TCP port via the SQL Server Browser (UDP 1434) before connecting. Give an explicit port to skip the lookup — the robust choice where UDP 1434 is firewalled but the TDS port is open. (*.dynamics.com / Azure SQL are default-instance cloud endpoints, so this never applies there.)

Windows authentication (auth = 'ntlm' or 'kerberos'): authenticates to an on-prem SQL Server with a domain account. Give the domain either inline — user = 'CORP\myuser' — or as its own option, domain = 'CORP'; when both appear the domain option wins and the CORP\ prefix is stripped off the user name. Either way it is a login with an explicit password, not single sign-on from the host’s logged-in identity: basalt runs on Linux, holds no system ticket, reads no keytab, and always needs password.

auth = 'kerberos' is for a server or domain that refuses NTLM. It needs the realm — realm = 'CORP.LOCAL', the domain’s DNS name, or a user written myuser@CORP.LOCAL — and asks the KDC for a ticket to MSSQLSvc/<host>:<port> with the resolved port (a named instance’s included), so host is the server’s DNS name as registered in AD; when it is not (an IP, an alias), spn names the service: spn = 'MSSQLSvc/sql01.corp.local:1433'. The KDC is kdc = 'host[:port]', else the one DNS lists for _kerberos._tcp.<realm>, else the realm’s own name; AES keys only, RC4 refused, tickets reused until they expire. The server’s reply must prove it holds the service’s key, or the login is refused. The channel is always TLS (tls = 'off' is taken as 'require'). Extended Protection set to Required on the server is not supported.

Encryption is mandatory for auth = 'ntlm': tls = 'off' is a plan-time error, refused before any socket opens, because an unencrypted NTLM exchange hands the challenge/response to any passive observer for offline cracking. tls = 'require' verifies the server certificate and is the right setting; tls = 'insecure' encrypts without verifying and is accepted, since on-prem instances usually present a self-signed certificate — but an unverified channel still leaves an active man-in-the-middle able to relay the handshake. Point BASALT_CA_BUNDLE at the PEM of your internal CA and use tls = 'require' to close that gap. Note that NTLMv2 never puts the password on the wire — only a challenge-response derived from it — whereas a SQL login sends it under LOGIN7’s trivially reversible scrambling, so on an unverified channel NTLM is the stronger of the two, not the weaker.

HTTP APIs

HTTP connections hold what every read of an API shares. After OPTIONS, an http connection takes the REST source clauses (Sources) — PAGINATE, RETRY, WITH (...) — as defaults each read starts from; a read’s own clauses win:

CREATE CONNECTION gh TYPE http OPTIONS (base_url = 'https://api.github.com',
    auth = 'bearer', token = env('GH_TOKEN'))
  RETRY 3 ON (429, 503)
  WITH (header = 'Accept: application/vnd.github+json');

auth is bearer (token), basic (user/password, which default to the NAME_USER/NAME_PASS convention), header (header_name/header_value, for API keys), login_json (login_url, the body’s fields as body_<name>, and token_path, token_header, token_prefix for where the token goes) or oauth2 (login_url — token_url is accepted too — client_id, client_secret, scope). With no auth, no credentials are read at all.

CREATE RESOURCE conn.name AS GET(...) | POST(...) [PAGINATE ...] [RETRY ...] [WITH (...)]; names one endpoint, so it reads like a table:

CREATE RESOURCE gh.repos AS GET('/orgs/ziglang/repos', type = 'public')
  PAGINATE BY page (param = 'page', size = 100);

SELECT name, stargazers_count FROM gh.repos WHERE NOT archived;

SHOW TABLES FROM gh lists a connection’s resources (resource, method, path) and DESCRIBE gh.repos fetches it to print its columns. Reading gh.name that no CREATE RESOURCE declared is a plan-time error that says so.

SFTP

An sftp connection names a file server, and paths reach it as sftp://name/path — absolute, or sftp://name/~/path under the login’s home:

CREATE CONNECTION bank TYPE sftp OPTIONS (
  host = 'sftp.banco.com.br', user = 'empresa',
  key_file = '/etc/basalt/bank_ed25519'          -- or a password (BANK_PASS)
);
LOAD INTO IDENTIFIER('retornos/' || $dia || '.parquet') AS
SELECT * FROM IDENTIFIER('sftp://bank/retorno/' || $dia || '.csv');
LOAD INTO 'sftp://bank/remessa/pagamentos.csv' AS SELECT * FROM pagamentos;

Options are host port user password key_file key_passphrase known_hosts host_key; user/password follow the NAME_USER/NAME_PASS convention, and the password is optional when a key_file logs in. The key is an OpenSSH Ed25519 key, passphrase-protected or not; an RSA key file is refused with that advice. Password and keyboard-interactive logins both work. Without a connection, sftp://user@host/path logs in with ~/.ssh/id_ed25519 or SFTP_PASSWORD. A connection name is process-wide: under basalt serve every script sees the latest CREATE CONNECTION of it, so give different servers different names.

The server’s host key is checked before anything is sent, against known_hosts (~/.ssh/known_hosts by default, hashed entries and [host]:port included) or a pinned host_key = 'SHA256:…'. An unknown key is refused with its fingerprint and the option that pins it, a changed or @revoked one is refused outright — never trusted on first use, as a transfer to the wrong host is a leak. Ciphers are chacha20-poly1305, AES-GCM and AES-CTR with HMAC-SHA2, with OpenSSH’s strict key exchange.

Reads are by offset, so a Parquet file or an Excel workbook on the server is read without fetching what the query does not need, and a parallel read gets a session per lane; sessions are pooled per server and user. A write goes to name.part and is renamed over name once complete, so a partner polling the folder never picks up half a file; a failed load removes the .part. APPEND is not supported, and a missing folder is an error rather than created on someone else’s server.

FTP

Plain FTP is read by URL, ftp://[user[:password]@]host[:port]/path. Public data is still published this way, and anonymous login is the default:

SELECT * FROM 'ftp://ftp.example.org/dados/2026/arquivo.csv' WITH (delimiter = ';', encoding = 'latin1');
SELECT * FROM 'ftp://ftp.example.org/dados/pacote.zip :: tabela.csv';
SELECT * FROM 'ftp://ftp.example.org/dados/mensal/';       -- every file under the folder

A server that needs a login can be named once with a connection, whose paths are then ftp://name/path:

CREATE CONNECTION parceiro TYPE ftp OPTIONS (host = 'ftp.example.org');  -- PARCEIRO_USER / PARCEIRO_PASS
SELECT * FROM 'ftp://parceiro/saida/2026-10.csv';

Options are host port user password; user/password follow the NAME_USER/NAME_PASS convention, and without them the login is anonymous. Without a connection the login is the URL’s, else FTP_USER / FTP_PASSWORD from the environment, else anonymous. A name with spaces or accents is percent-encoded in the URL (arquivo%20um.csv). A connection name is process-wide, as an SFTP one is.

FTP has no cheap random access, so each file is downloaded whole to a temporary folder the first time a run reads it and read from there. Every format works, zip members, Parquet and Excel included, and a CSV still splits across -j lanes. A second read of the same URL in the run uses the copy, and the copies are removed when the run ends; error messages name the URL, not the copy.

A trailing / reads a folder: every file under it, subfolders included, with names starting _ or . skipped, as for any folder read. Folders are listed by MLSD, or by NLST and a CWD to each name on servers without it. A folder of more than 10,000 files is refused rather than copied, so a URL one level too high fails fast: read a subfolder or a file instead.

Transfers are binary over a passive data connection (EPSV, or PASV where the server refuses it). The address a PASV reply advertises is ignored in favour of the server’s own host, as a server behind NAT often advertises a private one. Plain FTP sends the password in clear text, and FTPS (FTP over TLS) is not supported; use SFTP for anything private.

A busy server (a 4xx reply) or a dropped connection fails the run as transient (exit 75), worth retrying; a missing file (550) or a refused login fails it for good, with the server’s reply in the message. FTP is read only: LOAD INTO 'ftp://…' is refused by check.

SMB file shares

An smb connection names a Windows file share or a Samba server, and paths reach it as smb://name/share/path — or smb://name/path when the connection fixes the share:

CREATE CONNECTION fs TYPE smb OPTIONS (
  host = 'fileserver.corp.local', domain = 'CORP', user = 'svc_basalt',
  share = 'Financeiro'                           -- optional; password from FS_PASS
);
SELECT * FROM 'smb://fs/fechamento/2026-10.xlsx';
LOAD INTO 'smb://fs/exportacao/vendas.parquet' AS SELECT * FROM vendas;

Options are host port user password domain share realm kdc spn auth; user/password follow the NAME_USER/NAME_PASS convention, domain is the account’s (empty for one local to the server), and port is 445. Without a connection, smb://[domain;]user@host/share/path logs in as that user with SMB_PASSWORD (SMB_USER and SMB_DOMAIN filling what the URL leaves out). Paths use /; names are matched as the server matches them, without regard to case on Windows.

The login is NTLMv2, or Kerberos when the connection names a realm — the domain’s DNS name in capitals (CORP.LOCAL), not its NetBIOS name. Kerberos is what an Active Directory account on Azure Files needs (NTLM there serves only the storage account key), and what a domain that disables NTLM leaves:

CREATE CONNECTION az TYPE smb OPTIONS (
  host = 'mystorage.privatelink.file.core.windows.net', share = 'dados',
  user = 'svc_basalt', realm = 'CORP.LOCAL'      -- password from AZ_PASS
);

basalt asks the KDC for a ticket with the password itself — it holds no system ticket and reads no keytab — with pre-authentication and AES keys only (RC4 is refused). The KDC is kdc = 'host[:port]' when given, else the one DNS lists for _kerberos._tcp.<realm>, else the realm’s own name; the service is cifs/<host>, or spn when the server is known by another name (reach a private endpoint by IP, say, but name it by its DNS name). Tickets are reused until they expire. The user may be written me@CORP.LOCAL or CORP\me; it logs in as me, and with auth = 'kerberos' the part after @ serves as the realm when no realm is given. auth = 'ntlm' keeps NTLM despite a realm; SMB_REALM and SMB_KDC fill in for a plain smb:// URL. The machine’s clock must be within five minutes of the KDC’s.

Every request is signed and every signed reply checked — SMB 2.1 to 3.1.1, the latter with pre-authentication integrity — which Windows 11 24H2 and Server 2025 require by default and older servers accept. A guest login is refused rather than taken as success. A share or session that requires encryption gets it: AES-128-GCM on SMB 3.1.1, AES-128-CCM on 3.0.

Reads are by offset, as on SFTP: a Parquet file or an Excel workbook is read without fetching what the query does not need, several reads in flight within the credits the server grants, and a parallel read gets a session per lane; sessions are pooled per server and login. A write goes to name.part and is renamed over name once complete; a failed load deletes the .part and leaves name as it was. APPEND is not supported, and a missing folder is an error.

Command line

basalt run      <script>|-|-c "<inline>" [-p key=value ...] [-j threads] [--format table|json|csv|tsv|arrow] [--max-rows N] [--op-memory SIZE] [--spill-dir DIR] [--spill-cap SIZE] [--port N] [--host IP]
basalt serve    <dir> [--port N] [--host IP] [--watch]
basalt check    <script>|-|-c "<inline>" [-p key=value ...] [--format json] [--known t1,t2]
basalt complete <script>|-|-c "<inline>" [--pos N] [--connect] [--utf16]
basalt repl
basalt kernel   [--format table|json|csv|tsv|arrow] [-j threads] [--max-rows N]
basalt version

run and serve take the log options --log-level error|warn|info|debug, --log-format text|json|auto and -q; kernel takes the first two.

Options

Options may come before or after the script path, and - (the script on stdin) may sit anywhere among them: basalt run --format json job.sql and basalt run job.sql --format json are the same run.

Output formats

--format json makes stdout machine-readable: a terminal SELECT emits one JSON object per row (NDJSON, streamed — decimals as strings, temporals as ISO text, bytes as base64), and a LOAD run emits one summary object instead. --format csv and --format tsv emit a header line and the rows — quoted exactly as a .csv sink quotes them, a null as an empty field — and nothing else: no rule, no (N rows) footer, so | wc -l, | cut and | column -t see only data. The default table is for reading and does close with (N rows), on stdout, the way psql does; reach for csv, tsv or json when a program is the reader. --format arrow emits the SELECT as an Arrow IPC stream — one record batch per engine batch, typed (DECIMAL as decimal128, dates as date32, times and timestamps in microseconds), readable with pyarrow.ipc.open_stream, polars.read_ipc_stream or Arrow JS tableFromIPC. An empty result is still a valid stream: the schema followed by the end-of-stream marker.

A script with several results writes one stream per result, back to back, and each describes itself. Its schema carries custom_metadata: basalt.statement (the result’s ordinal in the run, from 0), basalt.kind (select, show, describe or explain), and basalt.line / basalt.col where the statement starts. Just before its end-of-stream marker comes a zero-row record batch whose message metadata holds basalt.rows, basalt.elapsed_ms and basalt.truncated — plain Arrow, so a reader that ignores metadata sees the same table. pyarrow reads the streams in turn from one file object (ipc.open_stream(f) until f is exhausted; the trailer through read_next_batch_with_custom_metadata). A statement EXPLAIN under arrow is a result of its own — one string column, plan, a row per line — rather than text on stderr, and so is a whole-program EXPLAIN.

Logs are stderr-only, plain text, level warn by default (--log-level, --log-format json, -q).

Capping rows

--max-rows N keeps the first N rows of each result printed to stdout and stops the query there: once a result has more rows than it keeps, every source reports end of input at its next read, so SELECT * FROM 'huge.parquet' returns its first rows after decoding one row group, not the file. A blocking operator is unaffected — an aggregate, a sort or a join’s build side has read its whole input before the first row reaches stdout, so what is kept is exactly the first N rows of the full answer. A cut result logs result cut at --max-rows N (level warn), carries basalt.truncated = true in its Arrow trailer, and in a kernel status. A LOAD is never capped.

Spilling to disk

A blocking operator — a join’s build side, a sort, an aggregate — holds its input in memory up to --op-memory (default 2GB) and spills the rest to disk. Spill files go in a directory of the run’s own under --spill-dir (default $TMPDIR, else /tmp), made only when something first spills and removed with everything in it when the run ends, however it ends. --spill-cap (default 8GB) bounds the disk a run’s spills hold at once — a file counts until it is deleted, so a sort’s merge passes do not add up; a run that needs more fails cleanly rather than filling the disk. Sizes take 512MB, 8GB or 2GiB (units are binary: 1GB is 1024 MiB).

Errors and editors

Errors point at what is wrong, not at the statement it sits in: an unknown column or function is reported at the name itself, on its own line of a multi-line query. Under --log-format json an error is one NDJSON line in the log’s shape, with the range an editor underlines — end_line/end_col are just past the offending text, and absent when the error is about a whole stage — and whether a retry could help (class, the exit-75 distinction below):

{"ts":1790592941244,"level":"error","event":"script_error","msg":"unknown field `nope`",
 "file":"orders.sql","line":2,"col":14,"end_line":2,"end_col":18,"class":"permanent"}

event is parse_error, script_error or aborted; file is the script, or the @included file the fault is in.

For an editor: basalt check --format json prints its diagnostics as a JSON array on stdout — [] when the script checks out, else one object per error in the shape above without ts/event — and still exits 1 on an error. check does not stop at the first problem: every statement is checked on its own and each that fails is listed, in script order. A statement that does not parse is skipped to its ; and parsing resumes after it — except inside a FOR, CASE or CREATE FUNCTION body, whose own ;s make the statement’s end unknowable, so the problems after a broken block go unreported until it is fixed. --known enrich,daily names tables the script reads but does not declare — a notebook’s other cells — so FROM enrich is checked as a table whose columns are unknown rather than failing as an unknown source. basalt complete --pos N prints what Tab would offer at byte offset N (the end, without --pos; --utf16 counts N and the answer in UTF-16 units): {"start":S,"end":N,"items":[{"text":…,"kind":…,"detail":…}]}, the items replacing script[S..N], with kind one of keyword, function, param, cte, connection, table, column, path. S is where the word under the cursor begins even when nothing matches. detail, present only when there is one, is a built-in function’s signature (date_add(unit, n, ts)) or a column’s type — the engine’s for a file, the database’s data_type for a table. A word comes back once: a column named name is not offered again as the keyword name, and a column shadows a function of its name. It completes against the script’s own declarations, so a script half typed still has its names; local files named in it give their columns; connections are asked for their tables and columns only under --connect, since that is a round trip to each.

Progress and summaries

While a LOAD runs, a terminal gets a live line on stderr — what is moving where, rows so far, the rate and the clock, with [3/12] in front inside a FOR EACH:

⠹ [3/12] erp.dbo.SC5010 → sr.bronze.sc5010  1,204,112 rows  48.3k rows/s  0:24

A run that writes closes with one sentence, and one that has several LOADs (more than one statement, or a FOR EACH / CASE / CALL) reports each as it finishes — + loaded, x failed, with the reason:

 + sr.bronze.sc5010                      1,204,112 rows    24.1s
 x sr.bronze.sb1010  connection reset by peer
Loaded 11 of 12 targets, 9,482,004 rows in 3m 12s (49.4k rows/s, 12 lanes)
1 failed

A single LOAD prints only the sentence — Loaded 20,000,000 rows into sr.bronze.orders in 8.2s (2.4M rows/s, 12 lanes), or Read 4,000,000 rows, loaded 7 into agg.csv in 1.9s when the query reduces. The text carries no run id; under --log-format json the same facts are load_complete / load_failed events (level info) and a run_complete line, each with run_id, and --format json prints the summary object with loads and loads_failed. Parse those, never the sentence. A run of SELECTs writes no sentence — the printed rows are its feedback — but under --log-format json it too closes with a run_complete line on stderr (never on stdout, which holds the rows). Every run_complete carries a pushdown object when a source was spared anything: parquet row_groups considered and row_groups_skipped on statistics, columns_read of columns_total (parquet or Arrow), and sql_filtered_reads, the SQL reads that carried a pushed filter.

The progress line is drawn only when stderr is a TTY, never under -q or --log-format json, not for a terminal SELECT (whose rows go to the same screen), and not for the first 400 ms, so short runs stay silent. Under --log-format json the line becomes an event instead, once a second after those 400 ms, terminal or not — what a UI reading the log shows in its place (--no-progress and -q still turn it off):

{"ts":…,"level":"info","run_id":…,"event":"progress","target":"erp.dbo.SC5010 → sr.bronze.sc5010","rows":1204112,"rows_per_sec":48300,"elapsed_ms":24930}

loop_done/loop_total join it inside a FOR EACH. A log or PRINT line erases it rather than colliding with it, and the run summary replaces it at the end. Piped or redirected, stderr carries no control characters at all. --no-progress turns it off.

Printed results

basalt run prints a terminal SELECT whole — every row and every column, in a plain left-aligned table — whether stdout is a terminal, a pipe or a file. What -c returns never depends on who is watching.

Exit codes

Transient means the network or the peer failed, not the script: a refused, reset or timed-out connection, a failed name lookup, an HTTP 429/5xx, and a database that closed the connection or failed on its socket — during login (a server turning connections away under a burst of parallel logins) or mid-query (ServerClosedConnection, ConnectionIoFailed). Past an HTTP source’s in-place backoff and a SQL sink’s one reconnect mid-write, basalt does not retry: the exit code hands the decision to whatever scheduled the run. A FOR EACH whose failed items are all transient exits 75 too. The same end-of-file or write failure on a local file stays permanent.

REPL

basalt repl is an interactive session: type a statement, see its result, and keep the session’s connections and functions from one entry to the next.

Results

The REPL is where a person looks at data, so there the table is fitted to the terminal: a row of types under the names, numbers right-aligned, NULL spelled out, cells cut at 40 columns, and … for what does not fit — the middle columns, in the manner of pandas, and the middle rows past 40. The footer always gives the true size:

 id  customer_name       amount  …  country  last_col
int  string               float  …  string   string
---  ------------------  ------  …  -------  --------
  0  customer number 0        0  …  BR       tail-0
  …  …                        …  …  …        …
 99  customer number 99   148.5  …  BR       tail-99
(100 rows × 10 columns, 5 shown — \view scrolls it)

A REPL result keeps its first 10,000 rows (and the last 20) rather than all of it. Values are coloured by their column’s type — numbers cyan and a negative one red, dates and times magenta, true green and false red, arrays and structs yellow, NULL dim — with the names bold and the types dim; only foreground colours, so a light terminal reads it as well as a dark one. Set NO_COLOR to drop all of it.

Viewing a result

In the REPL, \view (or \v) opens the last result full-screen, the names and types pinned. ←/→ move a column cursor (its name lit; the view follows it), ↑/↓ scroll by row, Ctrl-arrows and PgUp/PgDn move a screenful, Home/End jump to the first and last columns, g/G to the first and last rows, q leaves. s sorts by the cursor’s column — ascending, again descending, again off; by value for a number, as text otherwise (which orders ISO dates rightly), NULL last either way. / filters it as you type: text keeps the rows containing it in any case, !text those that do not, and >= 100, < 2026-01-01, = SP, != 0 compare — by value on a number column. Filters on several columns all apply; the header marks a filtered column ≈ and the sorted one ↑/↓. Enter keeps a filter, Esc while typing puts it back, Esc after clears the filters and then the sort. f finds text across every column: type the search and press Enter, and the rows narrow to those holding it, each match highlighted where it shows. Words are AND-ed and may be in any column, -word keeps the rows without it, col:word looks in that column only, and "two words" is one phrase — all in any case, the rules of the language’s search, which runs the same search over a whole result in a query. a makes the find and the filters ignore accents as well as case (the status line says so), as search(…, true) does. f searches the rows kept; F searches the whole result instead: it runs the query that made it again — that SELECT alone, not the rest of its entry — narrowed by the search, and opens the view on what it finds. Esc clears a find before the filters and the sort. Sorting, filtering and f rearrange the rows kept and never run the query again — so on a result larger than 10,000 rows they see only the first 10,000, and the status line says so; F is the way past them.

Commands

basalt repl executes on a top-level ; and carries CREATE CONNECTION / CREATE FUNCTION / PARAM declarations across entries (re-declaring a name replaces it). Meta commands: \connections list the session’s declarations · \reset drop them · \clear (or clear, cls, ^L) clear the screen · \format table|json|csv|tsv switch result output · \view scroll, sort and filter the last result · \help · \q. Short forms: \c for \connections, \e for \edit, \f for \format, \v for \view, \source for \i; \h, help or ? for help, and \quit, :q, quit or exit to leave.

Editing

The entry is a small text editor rather than a single line, with an editor’s habits. Enter runs the entry when it ends in a top-level ; and the cursor is at its end; anywhere else it opens a line — and after ( it steps in and puts the ) on a line of its own. Ctrl+J runs the entry as it stands, ; or not (Ctrl+Enter too, where the terminal delivers it). Brackets and quotes close themselves, typing the closer steps over it, and over a selection they wrap it; the matching bracket is underlined. Alt+Up/Down move the line or selected lines, Shift+Alt+Up/Down duplicate them, Tab and Shift+Tab indent and dedent a selection, Ctrl+/ comments lines out with -- and back in, Esc drops the selection. The entry is coloured as you type (keywords, strings, numbers, comments, $params; NO_COLOR turns it off), a multi-line entry gets line numbers in its gutter, and a parse error is shown with a caret under the column it names.

Making a connection

\connect [type] makes a connection with a form: the type picked from a list (↑/↓, its number, or the first letters of its name), then the fields its connector needs on one screen — host with the type’s usual port, database, user, password (blank means the env(NAME_USER) / env(NAME_PASS) convention; env:VAR names another variable). ↑/↓ and Tab move between fields, ←/→ turn a choice such as tls or auth, a field edits with the entry’s own keys, Enter goes on and submits from the last field, Esc cancels with nothing made. A field only some answers need, such as the Kerberos realm, shows only then. It shows the CREATE CONNECTION it built (a typed password masked), registers it, and offers to reach it and to save it to the startup file. ^R searches the history incrementally (type to narrow, ^R for an older match, Enter keeps it). A session starts by running ~/.config/basalt/repl.sql (or $XDG_CONFIG_HOME/basalt/repl.sql) when it exists — the place for the connections you always want, with env() for the secrets — and \save writes the session’s declarations there (or to a named file); \i <file> runs any file so its declarations join the session; \connections shows them as a table with host, database and whether the session has reached them (\c test reaches each now); \edit opens the last entry in $EDITOR and runs what comes back.

Completion

Tab completes the word under the cursor: keywords (in the case you are typing), the session’s connections, functions and $params, the entry’s CTEs, a path inside an unclosed quote, conn. followed by that connection’s tables, the columns of every table and file the entry names, and the built-in functions — scalar, aggregate and window. Tables and columns are asked of the source once per session, on first use, through the same information_schema queries SHOW TABLES and DESCRIBE run. A lone match is taken; several fill in what they share and come up as a row of choices that Tab cycles through. Arrows travel the whole entry — Ctrl+arrows by word, Home/End the line, Ctrl+Home/End the entry — and Up or Down past its edge recall history, where an entry comes back whole however many lines it had (~/.basalt_history). Shift with any of those selects; typing, Backspace and Delete replace the selection. ^A selects all, ^C copies a selection (and drops the entry when there is none), ^X cuts, ^V pastes, ^Z/^Y undo and redo. A paste is inserted as text, never run line by line, and a copy reaches the system clipboard where the terminal supports OSC 52. Two things a terminal cannot deliver: the mouse, and Ctrl+Enter, which arrives as plain Enter.

Kernel protocol

basalt kernel keeps one session alive for a program to drive, the way repl keeps one for a person. Each script it runs sees every CREATE CONNECTION, CREATE FUNCTION, CREATE RESOURCE, PARAM and LET an earlier script declared: they are kept as text and replayed ahead of the next script, so a script behaves exactly as it would in one file below them all, and re-declaring a name replaces it. A WITH belongs to its query and is not kept — two scripts may reuse a CTE name. A LET keeps the value it was given in the script that declares it (even one that only declares): LET t = now() is one instant for every later script, and LET n = (SELECT count(*) ...) is not queried again, however its source changes. Declare it again to recompute it. The REPL keeps LETs the same way.

Requests

Requests are NDJSON on stdin, one object per line:

{"op":"run","id":"c1","script":"SELECT ...;","params":{"days":7},"format":"arrow"}
{"op":"cancel","id":"c1"}     stop the running script; the session survives
{"op":"complete","id":"c2","script":"SELECT erp.","pos":11}
{"op":"check","id":"c3","script":"SELECT ...;","tables":["enrich"]}
{"op":"reset"}                forget every declaration
{"op":"close"}                exit 0 (as does EOF)

complete answers what basalt complete would, against the session’s declarations as well as the script’s own; its status carries complete: {start, end, items}. Connections are asked for their tables and columns once per session and remembered, as the REPL does ("connect": false keeps it offline); reset forgets that too. pos is a byte offset, or — with "utf16": true — a UTF-16 offset, the unit a JavaScript editor counts in, and the answer’s start/end then count the same way.

check lists every problem in a script without running it, against the session — its connections, params, LETs and functions — as basalt check --format json does; its status carries "diagnostics": [...], [] when the script checks out, each placed in the script as sent (file is session when the fault is in a declaration an earlier script made). tables names what the script may read that no script declared, such as other cells’ results: a name alone ("enrich") is checked as a table with unknown columns; {"name": "enrich", "columns": [{"name": "id", "type": "int"}, …]} types everything read from it (any type a PARAM takes: int, decimal(10,2), varchar(20), timestamp, …, each column nullable). A script’s own WITH of the same name wins. Nothing runs and nothing connects, and the session is left as it was: a checked script’s declarations are checked, not kept. params binds the script’s PARAMs for that script only, as -p does for a run; format overrides --format (default arrow) and max_rows overrides --max-rows for that script. A cancel acts at once, even mid-script; one naming a different id than the running script’s is ignored, and one without an id stops whatever runs. SIGINT cancels the running script too — however many times it is sent, it never ends the process.

Replies

Replies are frames on stdout, each a JSON header line. A data header is followed by exactly len raw bytes: what the script wrote to stdout — its results in format, in order, possibly over several frames. A result frame follows the last byte of each result, so the data between two result frames is exactly one result in any format — NDJSON rows of two SELECTs never run together, and an Arrow reader that cannot read streams in turn gets one stream at a time. Exactly one status closes each request, listing the results again, and nothing for that request follows it:

{"type":"data","id":"c1","len":1184}
<1184 bytes>
{"type":"result","id":"c1","statement":0,"kind":"select","line":2,"col":1,"rows":12,"elapsed_ms":40,"truncated":false}
{"type":"status","id":"c1","ok":true,"cancelled":false,"truncated":false,"elapsed_ms":42,"declared":[{"kind":"param","name":"days"}],"results":[...]}
{"type":"status","id":"c2","ok":false,"cancelled":false,"truncated":false,"elapsed_ms":3,"declared":[],
 "error":{"msg":"unknown field `nope`","file":"script","line":3,"col":8,"transient":false}}

Progress and loads

A statement that runs past 400 ms also sends a progress frame a second — {"type":"progress","id":…,"target":"…","rows":…,"rows_per_sec":…,"elapsed_ms":…} — for a SELECT as well as a LOAD, since a notebook shows either filling. Inside a FOR EACH it adds loop_done and loop_total: rows of the outermost loop finished, and rows in all — the terminal’s [3/12].

Each LOAD sends a load frame as it finishes, written or failed, in the order they finish — what the CLI’s + target and x target lines say:

{"type":"load","id":"c3","load":0,"target":"/tmp/a.parquet","line":1,"col":1,"rows_read":30000000,
 "rows_written":30000000,"elapsed_ms":3270,"lanes":12,"ok":true}
{"type":"load","id":"c3","load":1,"target":"sr.bronze.x","line":2,"col":1,"rows_read":0,"rows_written":0,
 "elapsed_ms":4,"lanes":1,"ok":false,"reason":"sqlserver connect failed: ServerClosedConnection","transient":true}

load numbers the loads in the order they finished; line/col are where the LOAD stands, as for a result. target is the path or conn.table as the script spelled it, IDENTIFIER() and ${...} rendered. rows_read is the load’s own, loads running side by side in a parallel FOR EACH included. A load that fails — before writing a row, too — still reports, with reason and transient. Inside a FOR EACH, each row’s load carries loop_row and loop_rows (its row, 1-based, of its own loop) and the outermost loop’s loop_done/loop_total; a row that fails under ON ERROR CONTINUE reports and the loop goes on.

The status of a run repeats them, so a reader that skips frames still has them — "loads":[…] (the frames’ fields, without type/id) — with the run’s totals: loads_ok, loads_failed, rows_read, rows_loaded and lanes, the facts of the CLI’s closing sentence (Loaded 11 of 12 targets, 9,482,004 rows in 3m 12s (49.3k rows/s, 12 lanes)). A failed or cancelled run carries them too, for the loads that ran before it stopped.

A result’s line/col count in the script as sent, as its Arrow metadata does; truncated says a row cap cut it, and the status’s truncated says any result was. declared lists what the script added to the session — a script’s declarations join the session once it parses, whether or not it then runs cleanly. An error’s line/col count in the script as sent; file is script, an @included file’s path, or session when the fault lies in a declaration an earlier script made. end_line/end_col, when present, end the offending name, as under --log-format json. transient is the exit-75 class above.

Results written before a failing statement are still delivered. Logs and PRINT stay on stderr (--log-level, --log-format); the item lines and the per-run summary are left out — the load frames and the status carry them.

Not yet implemented

Accepted design not yet in the engine:

• Cross-source pushdown, the full Trino model. Filters move below a join and across an equijoin key, and a chain of CTEs over one read sends all its filters as one WHERE (Pushdown); what is missing is the rest. An aggregate descends only in the whole-aggregate shape and a join never does (Trino’s applyAggregation/applyJoin are more general), and there is no runtime/dynamic filter — the build side’s key values are not sent back to the probe scan, so a selective predicate on a non-key dimension column still reads the whole fact table. A filter on a join’s right side by a bare name (WHERE valor > 0 rather than i.valor) stays above the join, as does one after an outer join.