Syntax reference

A Program is GoogleSQL with Pipe syntax. This page is the one place that lists every form of that text: how a Program is put together, the Pipe operators (GoogleSQL's own and the ones ActQL adds), the named calls for vectors, graphs and events, INFER, NEAREST, SERIALIZE and the Effects. Ordinary SQL (SELECT, joins, the scalar functions) is on SQL compatibility and Types and functions. Which forms run today in each environment is on Implementation status.

Each entry gives the form ([ ] optional, … repetition, keywords in upper case), its parts, its meaning, its defaults, one minimal example that passes check, the errors check reports, and the spec section that defines it. The examples assume the connections of the Quickstart project and name their parameters explicitly; each one is checked against a fixture as part of the build.

1. Program shapeCopy Anchor Link

A Program textCopy Anchor Link

Form

[WITH name AS (…), …] SELECT … [|> step …] [|> EFFECT …]
[WITH name AS (…), …] FROM source [JOIN …] |> step … [|> EFFECT …]
CREATE [OR REPLACE] TABLE conn.table AS (query)

Parts

Meaning One text is one query, optionally ending in one data-changing Effect, and optionally followed by Command postludes on its named Results (see Effects). A trailing ; is allowed; ; separates the query from its postludes and does not chain statements or make the text a script. The Program model a text lowers to carries several Stages, written as JSON or YAML, when a workflow needs ordered steps. A FROM-headed query has no bare WHERE, ORDER BY or LIMIT: after the first |> every operation is a step. A Pipe is logical composition only; it does not create a stage, a Checkpoint or an execution boundary.

Example

FROM support.cases AS c
|> WHERE c.status = 'open'
|> AGGREGATE COUNT(*) AS open_cases GROUP BY c.account_id
|> ORDER BY open_cases DESC
|> LIMIT 10;

Errors query_syntax_invalid (second statement, Effect not last, bare clause after FROM).

Spec SQL and explicit Pipe boundaries

WITHCopy Anchor Link

Form

WITH name AS ( query | GRAPH (…) | time_series(…) | calendar_series(…) | mean_graphs(…) ), …

Parts

Meaning A binding names a value — rows, a Graph, Vectors or Events — so later steps can refer to it. Naming neither reads nor publishes: the declaration itself performs no read, and its name never appears in the Results. At run time the Host may share one observation between references or optimise the computation, as long as the meaning is kept. Bodies are pure: INFER, RESULT and EFFECT are not accepted inside them.

Example

WITH open_cases AS (FROM support.cases |> WHERE status = 'open')
SELECT account_id, COUNT(*) AS n FROM open_cases GROUP BY account_id;

Errors query_syntax_invalid (INFER, RESULT or EFFECT in a body), query_invalid (name defined twice, unresolved name).

Spec WITH producers and pure Graph construction

Naming: WITH, AS and RESULTCopy Anchor Link

A Program that only reads publishes its final rows as the Result main when it declares no RESULT; a Program that ends in an Effect publishes no Result unless it declares one. RESULT is accepted at statement level only, not inside WITH, derived tables or set operands. A Result is fixed when it is declared: a later SELECT or LIMIT does not change it.

Spec Named intermediate Results

ParametersCopy Anchor Link

Form @name

Meaning A parameter is declared by the Program with a type and supplied at run time (--params, parameters). It is a value: it is never interpolated into the text, never selects an operation and never names a resource. It is accepted wherever an expression is, as LIMIT/OFFSET, and as an author guard.

Example

SELECT id, name FROM crm.accounts WHERE owner = @owner;

Errors parameter_undeclared, parameter_unused, supplied_value_type_mismatch.

Spec Parameters

UNNESTCopy Anchor Link

Form

FROM UNNEST(array_expr) [AS] element [WITH OFFSET [AS] position]
… JOIN UNNEST(array_expr) [AS] element [ON cond]
… CROSS JOIN UNNEST(array_expr) [AS] element

Meaning Expands an array into one row per element. element names the whole element value; an object element also exposes its fields as element.field. The array may be a parameter (UNNEST(@items)), an ARRAY[…] constructor or a column of the current row (correlated expansion). NULL and empty arrays produce no rows.

Example

SELECT element.id, position
FROM UNNEST(@items) AS element WITH OFFSET AS position;

Errors query_expression_invalid (not an array), query_invalid (duplicate alias).

Spec Declared UNNEST value tables

Source-free SELECTCopy Anchor Link

Form SELECT expr [AS name], … (no FROM)

Meaning One logical row built from expressions and parameters. A WHERE that is false yields no rows. It may feed a set operation, a WITH body or a JOIN like any other query.

Example

SELECT @account_id AS id, 'high' AS level;

Spec Source-free SELECT

ARRAY constructorsCopy Anchor Link

Form

[expr, …]              -- bare, type inferred from the elements
ARRAY[expr, …]         -- the same, spelled out
ARRAY<T>[]             -- an empty array needs its element type

T is one of BOOL, BOOLEAN, STRING, FLOAT64, DOUBLE, TIMESTAMP, JSON.

Meaning A finite array. Elements must share one type; an empty array needs the explicit ARRAY<T> annotation (ARRAY<STRING>[]). Arrays are accepted in IN UNNEST, as UNNEST sources and as values.

Example

SELECT id FROM crm.accounts WHERE tier IN UNNEST(['gold', 'platinum']);

Errors expression_type_mismatch (mixed element types, missing annotation on []).

Spec ARRAY constructors

LIST_FILESCopy Anchor Link

Form FROM LIST_FILES(connection.listing | connection [, paths => [string, …] | glob => string]) [AS alias]

Parts

Meaning A source whose rows are the files the listing exposes: resource, version, path, media_type, size and the Connector's other metadata. paths and glob are exclusive. Neither selector means the whole listing; the Connector must allow an unconditional listing and keep it within the Program's row limit, and a listing cut short by a limit is reported as incomplete, never as the whole set — LIMIT on a listing is a request for fewer files, not permission to treat them as all of them. Listing reads no content. A file's content is a separate source, connection.contents (resource, version, content as a content_ref, with the media type of the captured bytes), joined on resource and version, so the Program names the exact version it reads.

Example

FROM LIST_FILES(gcs.objects, glob => r'reports/*.csv') AS f
|> JOIN gcs.contents AS c ON f.resource = c.resource AND f.version = c.version
|> SELECT f.path, f.media_type, c.content;

Errors query_syntax_invalid (both selectors), source_selector_unregistered (no such listing; a bare connection with several listing sources).

Spec File listing Sources

2. Pipe operatorsCopy Anchor Link

Pipe syntax is GoogleSQL's: every step after the first FROM (or after a full SELECT) starts with |> and consumes the rows of the step before it. The first table lists GoogleSQL's pipe operators as ActQL accepts them, with GoogleSQL's meaning; the second lists the operators ActQL adds.

GoogleSQL operators

GoogleSQL operators not in this table (CALL, WINDOW as a standalone step, TABLESAMPLE) are not accepted in 0.1; check refuses them with a named reason. The WINDOW clause inside SELECT and EXTEND is accepted.

Operators ActQL adds

Clause arguments stay inside their operator: AGGREGATE's GROUP BY, JOIN's ON, INFER's INPUT, LIMIT's OFFSET and SERIALIZE's OPTIONS never take their own |>.

Spec SQL and explicit Pipe boundaries, Scope and lowering

SET, EXTEND and ASCopy Anchor Link

SET binds the replacement to a new value slot: an INFER key completed from the old value is not retargeted. SELECT is the step that both chooses columns and ends the range scope.

Spec Scope and lowering

3. Arguments of named callsCopy Anchor Link

A named call is |> name(key: value, …). Keys are closed per operation and case-insensitive; backticks quote a reserved key. The role of each value is fixed by the operation's signature, not by how the value is spelled:

So interval_join(with: spans, …) joins the WITH producer spans, while with: 'spans' is a string and is refused. A numeric literal may carry one leading + or -; a sign on a parameter or a calculation is not accepted. Unknown keys, duplicate keys and a value in the wrong role are refused before anything runs.

Spec Named logical SGA calls

4. Guards and assertionsCopy Anchor Link

Both forms are ActQL's own. GoogleSQL has no guard on a join, and ASSERT UNIQUE BY is ActQL's spelling of its assert_rows operation, not the |> ASSERT condition operator some GoogleSQL implementations offer; that operator is not part of the accepted set.

MAX_OUTPUT_ROWSCopy Anchor Link

Form … JOIN source ON cond MAX_OUTPUT_ROWS n

Meaning An author guard on one join: if the join would produce more than n rows, the Run fails with the limit named. It never truncates. n is a positive integer literal or a parameter. Each JOIN carries its own guard, including joins inside WITH.

Example

FROM salesforce.accounts AS a
JOIN snowflake.usage_30d AS u ON a.id = u.account_id MAX_OUTPUT_ROWS 100000
|> SELECT a.id, u.active_users;

Errors operation_parameter_invalid (zero, negative, above the admitted ceiling).

Spec Composition assertions

ASSERT UNIQUE BYCopy Anchor Link

Form |> ASSERT UNIQUE BY (col, …)

Meaning Proves that the listed columns are present, non-null and unique across the current rows, then passes every row through unchanged and in order. A duplicate fails the Run; nothing is deduplicated. The proof is what a later INFER or EFFECT … ID can rely on when the key is not already proved by the source.

Example

FROM sheets.leads
|> ASSERT UNIQUE BY (email)
|> SELECT email, company;

Errors query_invalid (empty key list, unresolved column); at run time, the assertion failure with the first duplicate key.

Spec Composition assertions, assert_rows

5. Models and vectorsCopy Anchor Link

INFER sends each row to a model task. The vector forms work on vectors that already exist: score_vectors scores a Vectors set against one query vector, and NEAREST returns the top K candidates for each row's query. Producing an embedding from text is a separate model task; neither form does it implicitly.

INFERCopy Anchor Link

Form

|> INFER `model-task:<name>.v<n>`
     [ITEM KEY (col, …)]
     INPUT (col | expr AS field | field = expr, …)
     [KEEP (col [AS name] | * | alias.*, …)]
     AS name
     [MAX ITEMS n]
     [ON FAILURE FAIL]

Parts

Meaning Sends each row's INPUT to the task's model and adds one object column name with name.outcome (ok or failed:<reason>), name.value (the task's typed output on ok, otherwise NULL) and name.input_key. Rows the model could not answer come back with the others; filter on name.outcome before reading name.value. The input columns stay in the row and remain usable for joins and filters.

Defaults

Example

FROM salesforce.accounts AS a
JOIN snowflake.usage_30d AS u ON a.id = u.account_id
|> INFER `model-task:churn-risk.v1`
     INPUT (notes = a.description, active_users = u.active_users, trend = u.trend) AS risk
|> WHERE risk.outcome = 'ok' AND risk.value.level = 'high';

Errors query_invalid (unknown task, missing or extra input field, no provable key, duplicate KEEP name, name collides with AS), expression_type_mismatch (input type), operation_parameter_invalid (MAX ITEMS).

Spec INFER KEEP source projection, infer

NEARESTCopy Anchor Link

Form

|> NEAREST candidates [AS c]
     [USING expr | QUERY KEY (col, …) VECTOR expr]
     [CANDIDATE KEY (col, …) VECTOR expr]
     [METRIC cosine | dot | l2]
     K n
     [KEEP (col [AS name] | * | alias.*, …)]
     AS hit

Parts

Meaning For each current row, finds the K candidates with the best score under the metric and emits one row per match. The search is exact under the metric; ActQL never substitutes an approximate index. hit holds hit.score, hit.rank, hit.query_key, hit.candidate_key and, when the candidate has a body column, hit.body; other candidate columns are reached through KEEP (c.col). A row with no match produces no rows. l2 scores are negative distances, so "best" is always the largest score.

Defaults

Example

SELECT c.id, c.description AS question, c.embedding AS query_vec
FROM salesforce.cases AS c
WHERE c.priority = 'urgent'
|> NEAREST kb.articles USING query_vec K 3 AS hit
|> AGGREGATE ARRAY_AGG(hit.body ORDER BY hit.rank) AS refs GROUP BY id, question;

Errors query_syntax_invalid (KEY without VECTOR, USING together with QUERY), query_invalid (no or ambiguous vector column or key, duplicate KEEP name), operation_parameter_invalid (K).

Spec NEAREST KEEP source projection, nearest

Vector callsCopy Anchor Link

Example

WITH v AS (FROM kb.articles |> to_vectors(id: id, vector: embedding) |> normalize_vectors())
FROM v
|> score_vectors(query: @query_vec, metric: 'cosine')
|> vectors()
|> ORDER BY score DESC |> LIMIT 5;

Errors query_invalid (wrong carrier: a Relation step such as ORDER BY on Vectors without vectors()), operation_parameter_invalid (query of another dimension or with NULLs).

Spec Vector-set-preserving operations and bridges, pool_vectors

Vector functionsCopy Anchor Link

vector_score and its aliases require two vectors of the same dimension; NULL propagates through every function.

Spec Row-local vector expressions, Decoding and expression rules

6. GraphsCopy Anchor Link

GRAPH constructorCopy Anchor Link

Form

WITH g AS (GRAPH (
  NODE TABLES (rows [AS N] KEY (col, …) [LABEL Name | DEFAULT LABEL] [PROPERTIES (col [AS name], …) | PROPERTIES ARE ALL COLUMNS [EXCEPT (…)] | NO PROPERTIES], …)
  [EDGE TABLES (rows [AS E] KEY (col, …)
                SOURCE KEY (col, …) REFERENCES N [(col, …)]
                DESTINATION KEY (col, …) REFERENCES N [(col, …)]
                [LABEL Name] [PROPERTIES (…)], …)]
))

Meaning Builds a Graph value from Relations: each node table contributes nodes keyed by KEY, each edge table contributes edges whose endpoints REFERENCES a node definition. Labels and properties belong to the definition. The Graph exists only inside the Program; no catalog object is created. This is the GoogleSQL/SQL-PGQ table mapping, not ISO GQL CREATE GRAPH.

Example

WITH accounts AS (FROM crm.accounts |> SELECT id, name),
     refs AS (FROM crm.referrals |> SELECT id, from_id, to_id),
     g AS (GRAPH (
       NODE TABLES (accounts AS Account KEY (id) LABEL Account PROPERTIES (name))
       EDGE TABLES (refs AS Referred KEY (id)
                    SOURCE KEY (from_id) REFERENCES Account
                    DESTINATION KEY (to_id) REFERENCES Account
                    LABEL Referred NO PROPERTIES)
     ))
FROM g |> MATCH (a:Account)-[:Referred]->(b:Account) |> SELECT a.name, b.name AS referred;

Errors graph_operation_invalid (unknown definition in REFERENCES, duplicate label, key type mismatch).

Spec WITH producers and pure Graph construction, graph

MATCHCopy Anchor Link

Form

FROM [GRAPH] g
|> MATCH [p =] [WALK | TRAIL | ACYCLIC] node (edge node)…

node  ::= ( [var] [:Label] [{prop: expr, …} | WHERE cond] )
edge  ::= -[ [var] [:Label] [{…} | WHERE cond] ]->  |  <-[ … ]-  |  -[ … ]-  |  ->  |  <-  |  -
          [ {n} | {m,n} ]

Parts

Meaning Yields one row per match with the pattern's variables bound. One MATCH per Graph source; a second pattern needs its own source and an ordinary join. SELECT after MATCH ends the pattern scope and exports only its columns. This is the GoogleSQL element-pattern profile; it is not full GQL.

Defaults Path mode WALK.

Example

FROM g
|> MATCH ACYCLIC (a:Account WHERE a.tier = 'gold')-[:Referred]->{1,3}(b:Account)
|> SELECT a.name AS source, b.name AS reached;

Errors query_syntax_invalid (unbounded quantifier, second MATCH), graph_operation_invalid (unknown label, property not on every candidate), query_invalid (row alias on a Graph).

Spec Graph pattern source, Graph operations

Graph callsCopy Anchor Link

All take a Graph as the current input unless noted. Enum arguments are strings; direction is 'out', 'in' or 'both'.

Examples

WITH seeds AS (FROM crm.accounts |> WHERE tier = 'gold' |> SELECT id)
FROM g
|> reach(start: {from: seeds, id: id}, direction: 'out', max_depth: 2)
|> SELECT start_id, node_id, depth;

FROM g
|> shortest_path(source: 'acc_1', target: 'acc_9', direction: 'out', max_hops: 5)
|> SELECT total_cost, hop_count, node_ids;

WITH stats AS (FROM g |> degree(direction: 'out'))
FROM stats |> WHERE degree > 10 |> SELECT node_id, degree |> RESULT hubs;

Errors graph_operation_invalid (missing required key, wrong carrier, a string where a producer or column is expected), operation_parameter_invalid.

Spec Named logical SGA calls, Graph operations

7. Events and timeCopy Anchor Link

Event callsCopy Anchor Link

to_events turns rows into an Events carrier; events() turns it back. The calls in between keep the carrier they are given. A clock argument (time, left_time, right_time, start, end) is a timestamp column or an integer column of milliseconds since the epoch; the column itself is kept as it is. Write occurred_at, not a hand-made occurred_ms.

GeneratorsCopy Anchor Link

A generator reads no input, so it is a WITH body (or the head of a Pipe), never a |> step.

calendar_bucketCopy Anchor Link

Form |> calendar_bucket(time: col, into: name, unit: '…', timezone: '…' [, week_start: '…'])

Meaning Adds the column name holding the calendar bucket that contains time in the named time zone.

Examples

WITH records AS (FROM app.events |> SELECT id, account_id, occurred_at, kind)
FROM records AS e
|> sequence(by: [e.account_id], id: e.id, time: e.occurred_at,
     pattern: [{as: first, where: e.kind = 1}, {as: second, where: e.kind = 2}],
     within: 20, max_output_rows: 100)
|> SELECT first_id, second_id;

WITH ticks AS (FROM market.ticks |> SELECT id, at),
     spans AS (FROM market.spans |> SELECT id, begin, finish)
FROM ticks AS t
|> interval_join(with: spans, kind: 'left', mode: 'point_in_interval',
     on: [], left: {time: t.at}, right: {start: spans.begin, end: spans.finish},
     select: {id: left.id, value: right.id}, max_output_rows: 1000)
|> SELECT id, value;

WITH days AS (calendar_series(start: '2026-01-01T00:00:00.000Z',
                              end: '2026-03-01T00:00:00.000Z',
                              unit: 'day', timezone: 'Asia/Tokyo'))
FROM days AS d
|> calendar_bucket(time: d.start, into: month, unit: 'month', timezone: 'Asia/Tokyo')
|> AGGREGATE COUNT(*) AS days GROUP BY month;

Errors query_invalid (wrong carrier, missing required key), operation_parameter_invalid (negative size, week_start with a non-week unit, a parameter where a literal is required).

Spec Event operations, Relational temporal operations, sequence, as-of join, interval join, merge_rankings, Calendar profile, Named finite event sequences

8. Serialization and other ActQL functionsCopy Anchor Link

SERIALIZECopy Anchor Link

Form

|> SERIALIZE FORMAT CSV | JSONL
     ORDER BY col [ASC | DESC], … | PRESERVE ORDER
     OPTIONS (name = '…', header = TRUE | FALSE, null_marker = '' | r'\N',
              field_delimiter = '…', encoding = '…', line_ending = 'LF' | 'CRLF',
              max_rows = n, max_bytes = n)

Parts

Meaning Encodes the current rows as one piece of content. The output is a single row with exactly name, content (a content_ref), media_type, size_bytes and row_count; the input columns do not survive. This is the explicit boundary at which content is created. It uploads nothing: an EFFECT STORE after it is what writes the content anywhere.

Example

FROM support.cases
|> WHERE status = 'open'
|> SELECT id, subject, opened_at
|> SERIALIZE FORMAT CSV ORDER BY opened_at
     OPTIONS (name = 'open-cases.csv', header = TRUE, null_marker = '')
|> EFFECT STORE s3.objects SET (key = 'exports/' || name, content = content);

Errors query_syntax_invalid (missing order clause or OPTIONS), operation_parameter_invalid (unknown or duplicate option, non-literal value, missing header/null_marker for CSV).

Spec Content serialization, serialize

Functions that differ from GoogleSQL or are ActQL-onlyCopy Anchor Link

The complete function list with types and NULL rules is on Types and functions.

Spec Decoding and expression rules, Common expression contract

9. EffectsCopy Anchor Link

An Effect is the only way a Program changes an external system. A text has room for Effects in two places: the Pipe ends in at most one data-changing Effect (UPDATE, INSERT, UPSERT, DELETE, STORE, CREATE TABLE), and after the terminating ; any number of Command postludes may follow, each reading one of the query's named Results (the long form at the end of this section). A second data-changing Effect in the Pipe, or an Effect inside WITH, is refused. The Effects declared in one text belong to one Stage and form one Effect Set: they are checked together and approved at one Checkpoint. Work that must happen in order — write, then notify about what was written — is expressed as Stages in the Program model, not by chaining statements. Values in an Effect are pure expressions over the current row — columns, literals, functions of them. They are evaluated before anything is sent, and the evaluated values are what the Preview fixes and the approval covers. Every Effect goes through policy and, when policy asks for it, a Checkpoint before anything is sent; the approval covers the whole Effect Set, which for one Effect is that Effect (Effects and approvals).

Targets and Mappings. conn.resource names a connection and one of the resources its Connector publishes (actql catalog show conn). The Host resolves the write Mapping — which fields may be set, their types, the key — from the Connector's published write schema for that resource; an author never names a Mapping. A resource that publishes more than one Mapping or Command contract is addressed with the long form shown at the end of this section.

EFFECT UPDATECopy Anchor Link

Form |> EFFECT UPDATE conn.resource ID key SET (field = value, …)

Parts

Meaning Updates one record per row, by key. The Preview lists every record and the field changes; after the write, each item is applied, failed or unknown — or accepted on a Connector that confirms the write later and is reconciled by key.

Example

FROM salesforce.accounts AS a
|> WHERE a.id = @account_id
|> EFFECT UPDATE salesforce.Account ID id SET (Churn_Risk__c = 'high');

Errors query_invalid (unknown resource, field not in the write schema, duplicate field, key column missing), expression_type_mismatch.

EFFECT INSERTCopy Anchor Link

Form |> EFFECT INSERT conn.resource (col, …) [VALUES (value, …)]

Meaning Creates one record per row. Without VALUES, the listed columns of each row are written to the fields of the same names; with VALUES, each listed column receives the corresponding expression.

Example

FROM webshop.signups
|> WHERE created_at > @since
|> EFFECT INSERT hubspot.contacts (email, company) VALUES (email, company_name);

EFFECT UPSERTCopy Anchor Link

Form |> EFFECT UPSERT conn.resource ID key SET (field = value, …)

Meaning Updates the record with that key, or creates it when none exists. Same Preview and outcomes as UPDATE; an applied item additionally records which it was, action: created or action: updated.

EFFECT DELETECopy Anchor Link

Form |> EFFECT DELETE conn.resource ID key

Meaning Deletes one record per row, by key. The Preview lists the records.

EFFECT STORECopy Anchor Link

Form |> EFFECT STORE conn.resource [SET (field = value, …)]

Meaning Stores content or rows. Pointed at an object or file resource (s3.objects, gcs.objects, drive.files), it writes the content of each row — a content_ref produced by SERIALIZE or read from a contents source — as one object. The SET fields are the inputs the destination takes, each an expression over the row: where the object goes (key, name, folder, …), the content itself, and whether an existing object may be replaced (overwrite). They are the data of the Effect, not settings of it. Rows without a content are refused: a Relation becomes content only through SERIALIZE. Pointed at a table resource a Connector declares (local.<table>, tableau.datasources), it stores the rows themselves, in the shape the resource declares. The Preview fixes the destination, the exact content (by its content identity) or the row count, and the overwrite condition. Each Connector page lists the fields its resources take.

Example

FROM support.cases
|> WHERE status = 'open'
|> SELECT id, subject, opened_at
|> SERIALIZE FORMAT CSV ORDER BY opened_at
     OPTIONS (name = 'open-cases.csv', header = TRUE, null_marker = '')
|> EFFECT STORE s3.objects SET (key = 'exports/' || name, content = content);

Errors query_invalid (no content column for an object resource, unknown field), expression_type_mismatch (content is not a content_ref).

EFFECT COMMANDCopy Anchor Link

Form |> EFFECT COMMAND conn.operation [TARGET expr] ID key [INPUT (field = value, …)]

Parts

Meaning Runs a named operation once per row: send a message, refresh an extract, start a job. The contract fixes the input types, the target kind, the permission, what the Preview shows and how an outcome is confirmed; check verifies all of it. The outcome per item is accepted (the system took the request), applied (it confirmed the result), failed or unknown; an unknown outcome is confirmed through the Connector's own evidence, never resent. Resources that publish more than one Command contract are addressed with the long form at the end of this section.

Example

FROM support.replies AS r
|> WHERE r.status = 'ready'
|> EFFECT COMMAND gmail.send
     ID r.id
     INPUT (to = r.recipient, subject = r.subject, body = r.body);

Errors query_invalid (unknown operation, missing or extra input field, TARGET required or not accepted), expression_type_mismatch (input or target type).

CREATE TABLECopy Anchor Link

Form

CREATE [OR REPLACE] TABLE conn.table AS (query)
… |> [EFFECT [name]] CREATE [OR REPLACE] TABLE conn.table

Meaning Writes the rows as a whole table: CREATE requires that the table does not exist; CREATE OR REPLACE replaces the whole table with the same schema. A create conflict stays a conflict under OR REPLACE. It is a store Effect and is previewed and approved like one.

Example

FROM snowflake.usage_30d
|> WHERE active_users > 0
|> CREATE OR REPLACE TABLE duckdb.active_usage;

Spec Table create and whole-table replace

SQL statement formsCopy Anchor Link

Form

UPDATE conn.resource SET field = value, … WHERE cond;
INSERT INTO conn.resource (col, …) VALUES (value, …) | SELECT …;
DELETE FROM conn.resource WHERE cond;

Meaning The same Effects as EFFECT UPDATE, EFFECT INSERT and EFFECT DELETE. A WHERE that is an equality on the key column writes by key. Any other WHERE selects the targets at Preview time: the Host reads the matching records and fixes their identities and versions, the Preview lists exactly those, and the approval covers them and nothing else — the WHERE is not evaluated again at apply time, so a record that matches by then is not added. At apply time each target's condition (its version or current values) is checked; a changed record ends failed with conflict. A Connector that cannot fix the target set and its conditions this way refuses the form at check. Writing by key is the simplest to reconcile; a write selected by WHERE is reconciled when the Connector has an operation id, an idempotency key or an audit record for it.

Example

UPDATE salesforce.Account
SET Churn_Risk__c = 'high'
WHERE Id = @account_id;

The long formsCopy Anchor Link

When a resource publishes more than one Mapping or Command contract, name the one you mean:

|> EFFECT mark UPDATE
     TARGET 'connector://salesforce/Account'
     MAPPING `mapping:salesforce.account-risk.v1`
     ID id SET (Churn_Risk__c = risk.value.level);

FROM support.escalations |> SELECT channel, message |> RESULT escalations;
EFFECT notify COMMAND
  FROM RESULT escalations AS e
  TARGET e.channel
  CONTRACT `command:slack.post.v1`
  INPUT (text = e.message);

TARGET is the resource URI, MAPPING or CONTRACT the published contract, and the optional Effect name (mark, notify) is how the Run's status and ledger refer to it. The COMMAND long form follows a ;-terminated query and refers to one of its RESULTs. A short form is the long form with the Effect name, the Mapping and the Result left to the Host.

Errors query_invalid (unknown Mapping or contract, Result not retained, a second data-changing Effect in the Pipe).

Spec Terminal updates, COMMAND postludes, Effect declarations

10. Planned syntaxCopy Anchor Link

RESULT(name) — a reference to a Result already published earlier in the same Program, usable as a source (FROM RESULT(updates)), so that one Program can publish rows and then continue from exactly what it published. Planned after 0.1.

BRANCH — a body that groups steps which publish their own Results and run under one Program, so that one text can fan out into several Results without repeating the shared prefix. Planned after 0.1.

11. Program operations without query syntaxCopy Anchor Link

The JSON/YAML Program document has operations that SQL and Pipe do not spell directly. They are listed here so that nobody looks for a keyword that does not exist; they are not written as |> name(…).

Spec top_k, nest, Reshaping and nested data, search_candidates, text_rank, Effect declarations