Relational Authoring

Grid deliberately supports several familiar ways to work with structured data. They are different reading styles over shared values, not separate execution languages. Choose the form that makes the operation easiest to recognize.

The source carrier must not choose the spelling. A query should read the same whether Orders is a small record collection, a Frame, a model table, or a supported external relation.

Quick Choice

Your thought	Prefer
Keep, drop, or rename a few fields	PICK, OMIT, RENAME
Apply a short sequence of obvious value transformations	>> or `
Filter/sort an ordinary spreadsheet array using already-computed masks or keys	FILTER, SORT, SORTBY, TAKE
Read one scalar summary from a model table	concise table method chain
Express row predicates, computed columns, grouping, joins, sets, or windows	SELECT
Name intermediate relational stages	WITH/CTEs or named relation assignments
Insert, update, or delete durable records	explicit table compound mutation

These are preferences for shared models and generated code, not artificial restrictions on small expressions that are already clear.

Projection

Use PICK, OMIT, and RENAME when the operation is fundamentally about record keys:

PublicUsers = users OMIT password_hash, reset_token
Identity = users PICK id, display_name
Normalized = source RENAME "Customer Name" AS customer

This form works naturally for one object as well as a collection. It also supports dynamic and glob keys, which are broader than GRSQL-1 projection.

Use SELECT when projection is one clause in a larger row query, when columns are computed, or when aliases establish a result schema:

Invoices = SELECT id, quantity * unit_price AS total
  FROM Lines
  WHERE quantity > 0
  ORDER BY total DESC

Static exact Frame PICK id, amount and SELECT id, amount FROM Frame lower to the same logical projection. The formatter should preserve the author's chosen surface rather than translating one into the other.

Filtering, Ordering, And Limits

Spreadsheet functions are clearest when their operands are already arrays:

Positive = FILTER(values, values > 0)
Top = TAKE(SORT(Positive, -1), 10)

Pipes are clearest for a short left-to-right transformation:

Top = (values
  >> FILTER(value => value > 0)
  >> SORT(_, -1)
  >> TAKE(10))

The lambda is an element predicate. In a pipe this lowers to the ordinary spreadsheet mask form FILTER(values, MAP(values, predicate)); outside a pipe, write FILTER(values, include_mask [, if_empty]) directly. This value pipeline does not become a relational WHERE merely because the source happens to hold records. Treat it as equivalent to WHERE only when the carrier is a row collection, the lambda is a row predicate, and FILTER's empty fallback is not observable.

Use SELECT when predicates and order keys are fields in a row schema, or when clause order matters to understanding the result:

Top = SELECT id, score
  FROM Candidates
  WHERE eligible = TRUE
  ORDER BY score DESC
  LIMIT 10

LIMIT without ORDER BY does not mean “top”; it observes whichever order the input relation carries. Write the order whenever row choice matters.

File materialization in Lite

Lite's file materialization service supports CSV and newline-delimited JSON with row limits and contiguous slices. These selections apply in order: taking five rows and then skipping two leaves at most three rows. Selecting zero rows keeps the source's column names and types.

Filtering, sorting, joins and aggregation in that file service require Pro. Array helpers such as FILTER and SORT retain their documented behavior.

Lite and Pro share CSV/NDJSON import rules. CSV quoted empty fields ("") remain empty strings; unquoted empty fields become null. Duplicate headers, short or extra records, and unterminated quotes are errors. Closed multiline quoted fields remain valid. Header-only CSV produces zero rows with nullable string columns, unless an explicit schema supplies the types. Completely empty CSV or NDJSON requires an explicit schema.

Inference examines the first 1,000 logical data records, excluding CSV headers. A quoted multiline CSV record counts once. The schema then freezes: incompatible values and unexpected NDJSON fields are errors, never silently dropped. Mixed numeric/text CSV samples infer strings and retain their original text. NDJSON numbers and strings do not implicitly convert into one another. Nested JSON retains its values and recursive schema; missing nullable fields become null. Discovered fields keep first-appearance order.

For files whose later records introduce columns or change CSV types, explicitly select full-file discovery. It examines every record for inference before materialization, includes late fields, and supplies null in earlier rows where those fields were absent. It does not relax type rules. Ordinary imports do not run this full discovery pass.

The direct ETL source-operation options are:

Option	Meaning
uri or path	Source file path; Pro also retains its object-store transports.
infer_schema_records	Positive integer; defaults to 1000; cannot accompany an explicit schema.
full_file_discovery	Boolean, default false; mutually exclusive with an explicit schema or an explicit inference-record limit.
schema	A FrameSchema object with ordered columns, each specifying name, dtype, and nullable.
header	CSV only: boolean, default true. Without headers, use explicit schema names or column_1, column_2, and so on.
delimiter	CSV only: one ASCII character, default comma; quote, newline and NUL are invalid.

For example, these are direct ETL source options, not a new worksheet function signature:

{"uri":"orders.jsonl","full_file_discovery":true}

An explicit schema skips inference and validates every imported value. Schemas use serialized FrameSchema type names; the UInt64 variant is "u_int64" (including list element types). This wire spelling is unchanged. Supported scalar types are null, boolean, int64, uint64, float64, and utf8. NDJSON also supports recursive list and struct types. Declared nullability is enforced during import and retained in the logical FrameSchema. IPC carrier fields are conservatively nullable in both editions; those storage flags do not weaken the declared contract. Tags and geometry annotations are not reader schema options. For example:

{"uri":"orders.csv","schema":{"columns":[{"name":"order_id","dtype":"int64","nullable":false}]}}

Numeric inference may widen integers to floating point when a float appears; an integer that cannot be represented exactly as float64 is refused. Mixed signed/unsigned integer types do not automatically widen: NDJSON refuses them, while CSV conservatively infers strings. Use an explicit type when needed. Integer tokens wider than int64/uint64 remain text in CSV and are refused in NDJSON; represent such JSON values as strings explicitly. Date/time-like CSV text is kept as text rather than automatically assigned a temporal type. Unknown options, unsupported types and invalid option values are errors. Reader failures use ETL_READER_OPTION, ETL_READER_SYNTAX, ETL_READER_SCHEMA, or ETL_READER_IO, with record/field context where relevant. These file imports validate every record, including records outside a requested row window. For example, HEAD(10) still refuses an incompatible value in record 1,001. Leading head/slice/collect limits retain only the selected rows in memory; they do not move across filters, sorts or aggregates. Full collection retains the requested output table. Pro constructs native Polars columns directly, without an intermediate IPC round trip. Remote Pro inputs are fetched once into temporary disk storage, which is removed when the import ends; local files are read directly. The 1,000-record inference default and explicit full-file discovery are unchanged. Frozen-schema imports bind fields once and fill typed column batches. Discarded JSON values still receive numeric, Unicode and nested-schema validation. These implementation optimizations do not relax parsing, nullability or refusal rules.

Scalar Table Reads

A concise table method is idiomatic for one obvious aggregate:

late_count = Orders.where(status = "Late").count()
paid_total = Orders.where(amount >= 0).sum("amount")

Use SELECT once the result has multiple outputs, grouping, a computed aggregate argument, HAVING, or ordering:

ByCustomer = SELECT customer, COUNT(*) AS orders, SUM(quantity * price) AS revenue
  FROM Orders
  WHERE status = "Paid"
  GROUP BY customer
  HAVING revenue >= 1000
  ORDER BY revenue DESC

Do not stretch a method chain to imitate SQL clauses. Do not wrap a one-line scalar aggregate in a multi-clause query merely to make it look more formal.

Joins, Sets, Windows, And Subqueries

Use SELECT for relational composition whose established public vocabulary is SQL-shaped:

• schema-aware joins;
• grouped and global aggregates;
• UNION, INTERSECT, and EXCEPT;
• analytic windows;
• derived relations, CTEs, and supported correlated subqueries.

These forms carry cardinality, null, alias, and ordering rules that generic array helpers do not imply. A lookup function is still preferable when the thought really is one keyed lookup rather than a relation join.

Name a window when several projections share its partition, order, or frame. Use QUALIFY when the result should retain only rows selected by an analytic output:

LatestOrder = SELECT customer, id, ROW_NUMBER() OVER newest AS rank
  FROM Orders
  WINDOW newest AS (PARTITION BY customer ORDER BY created_at DESC)
  QUALIFY rank = 1

For total null-safe equality in a row predicate, use IS DISTINCT FROM (or IS NOT DISTINCT FROM) rather than constructing a pair of IS NULL checks.

Naming Intermediate Work

Use a CTE when an intermediate relation exists only to explain one query:

Report = WITH active AS (
    SELECT id, region, amount FROM Orders WHERE status = "Active"
  )
  SELECT region, SUM(amount) AS total
  FROM active
  GROUP BY region

Use a named assignment when the relation is independently meaningful, reused, published, or inspected elsewhere:

ActiveOrders = SELECT id, region, amount FROM Orders WHERE status = "Active"
RegionalReport = SELECT region, SUM(amount) AS total
  FROM ActiveOrders
  GROUP BY region

An untyped named assignment remains a computed Workbook value; a CTE remains local to its query. Use a table-typed assignment when the relation is part of the model's durable, continuously maintained state:

table PaidOrders = SELECT region, amount AS revenue
  FROM Orders
  WHERE status = "paid"

table RevenueByRegion = SELECT region, SUM(revenue) AS revenue
  FROM PaidOrders
  GROUP BY region

TotalRevenue = RevenueByRegion.sum("revenue")
RevenueAlert = TotalRevenue > 100

This is a Live Table declaration. Deploying the model records the declared table's ownership and reconciles it into the existing materialized-table catalog before dependent formulas are primed. A relevant source-row mutation then updates the target table, dependent cells and realtime snapshots through one existing table-change path; it does not recompile the model or invoke a second relational evaluator.

The first admitted shapes are deliberately narrow. A row-preserving Live Table may project and rename direct columns from one declared table and may carry one comparison between a source column and a scalar literal. Its durable source-row identity lets one edit add, retract, or update exactly one target row. A grouped Live Table admits either one declared table source or an inner join of two declared tables on one equality key; one projected grouping column; and one aliased COUNT(*), SUM(column), AVG(column), MIN(column), or MAX(column). Join keys, grouping columns, and aggregate inputs must be explicitly qualified in the joined form. ORDER BY, LIMIT, HAVING, multiple group columns, multiple aggregates, distinct aggregates, compound/join filters, outer or multi-key joins, and computed row projections are not yet admitted as compiler-owned Live Tables. Such queries retain their existing Workbook evaluation rather than silently changing semantics.

Saving or refreshing a materialized projection through the operational API remains available for host-managed lifecycle effects. Live Tables make that ownership part of ordinary model source.

Equivalent And Non-Equivalent Forms

Equivalent surface forms must lower to one logical algebra before physical planning. Optimizers may recognize more equivalences over time, but syntax does not grant a preferred engine.

Some visually similar forms are deliberately not interchangeable:

Form	Why it is distinct
PICK with glob/dynamic keys	output schema may depend on runtime keys; GRSQL-1 projection is statically named
FILTER(data, mask, fallback)	fallback is spreadsheet value behavior, not relational empty-result behavior
positional SORT(array, index)	selects a physical array column; relational ordering names a semantic field/expression
TAKE(data, n)	observes current collection order; ORDER BY ... LIMIT n establishes order first
lookup function	returns one lookup result under function-specific miss rules; a join returns a relation with join cardinality
editing a query result	changes that value only; it is never implicit writeback to source rows

Mixing Styles

One expression may combine forms when each boundary remains obvious:

Preview = (
  SELECT id, amount FROM Orders WHERE status = "Open"
) PICK id, amount

Prefer one dominant style per relational stage. If a pipe accumulates branching, grouping, aliases, or several row-schema operations, rewrite that stage as SELECT. If a query exists only to remove one key, use OMIT.

Formatting

• Put each major SELECT clause on its own line once the query has more than projection and source.
• Start an enclosing ( on the assignment line for a multiline query; continuation newlines are enabled inside (...), [...], and {...}.
• Indent continuation clauses two spaces from the assignment.
• Require explicit AS for computed, aggregate, and window outputs.
• Keep short method chains on one line; name a stage before chains become hard to scan.
• Wrap pipes after about two stages, with the pipe token leading each continuation line.
• Preserve authored syntax during formatting; canonicalize casing, spacing, operators, and indentation within that syntax.

Declaring Columns And Write Rules

A table declaration can describe its columns. A deploy then creates or extends the table to match, and the table store enforces the column rules for every writer: surfaces, rule bodies, imports and the table API alike.

# Chart of accounts: codes never change, and deleting an account archives it.
default table Accounts = {
  schema: "Accounts",
  columns: {
    code: { type: "text", unique: TRUE, immutable: TRUE },
    name: { type: "text" },
    archived_at: { type: "dateTime", softDelete: TRUE }
  }
}

# One row per posting. The rows of one entry must total zero.
default table Postings = {
  schema: "Postings",
  columns: {
    entry_id: { type: "text", immutable: TRUE, balance: "group" },
    account: { type: "text", immutable: TRUE, references: "Accounts.code" },
    amount_cents: { type: "integer", immutable: TRUE, balance: "amount" },
    voided_at: { type: "dateTime", softDelete: TRUE }
  }
}

Each column takes type plus any of these keys:

Key	Meaning
type	Column type, such as "text", "integer", "number", "date", "dateTime" or "singleSelect". Required.
unit	Display unit for a numeric column.
unique	TRUE keeps the values distinct.
options	Choice labels for a select column, such as ["Open", "Closed"].
immutable	TRUE fixes a value once its row exists. Restating the same value is allowed.
softDelete	TRUE on one date or date-time column. Deleting a row sets it instead of removing the row.
references	"Table.column", naming a unique column of another table. A live row must name a live target row.
balance	"group" names the column that groups rows. "debit" and "credit" must total equally within each group, or one "amount" column must total zero.

A mutation that breaks a rule is refused as a whole, and nothing is written. The referenced table refuses to change or hard-delete a key that live rows still name; archive it instead.

Reads through the table API skip soft-deleted rows unless the request sets includeDeleted. Formulas and SELECT read every stored row, so filter on the soft-delete column yourself, as in WHERE voided_at IS NULL.

Schema changes from source are additive. A later deploy may add columns, choices, unique indexes on already-distinct data, and rules that the existing rows satisfy. A deploy that would drop a column or loosen a rule is refused, and the model keeps running on its previous source. Declarations without columns keep their earlier meaning: the columns come from imports, the Table surface or the table API.

Reserved And Rejected SQL Forms

Grid recognizes several familiar SQL spellings that it deliberately refuses to parse. Each refusal is a stable, named diagnostic rather than a generic syntax error: the spelling is understood, and the rejection is the contract. Treat these diagnostics as redirects to a Grid-native form, not as gaps to work around.

Reserved Read Clauses: GRID_RELATION_SQL_RESERVED

ASOF JOIN and LATERAL are recognized where they would appear in a SELECT and rejected:

R1C1 = SELECT * FROM Trades AS t ASOF JOIN Quotes AS q ON t.symbol = q.symbol

error[GRID_RELATION_SQL_RESERVED]: ASOF JOIN is reserved for a later Grid
relational profile and is not part of GRSQL-1

The diagnostic reserves spelling, not semantics. A later profile may admit these clauses once they have carrier-independent semantics and conformance coverage; until then, restructure.

For an ASOF-style nearest-match join, use a supported join with a named window and QUALIFY:

LatestQuote = SELECT t.id AS trade_id, q.price AS price, ROW_NUMBER() OVER newest AS rank
  FROM Trades AS t
  JOIN Quotes AS q ON t.symbol = q.symbol
  WHERE q.quoted_at <= t.executed_at
  WINDOW newest AS (PARTITION BY t.id ORDER BY q.quoted_at DESC)
  QUALIFY rank = 1

For LATERAL UNNEST-style expansion of a nested collection, flatten with array functions before querying:

Orders = [{ id: 1, items: ["a", "b"] }, { id: 2, items: ["c"] }]
AllItems = REDUCE([], Orders, (acc, order) => VSTACK(acc, order.items))

CROSS JOIN in the join position is part of GRSQL-1, not reserved. The other spellings on the profile's not-in-GRSQL-1 list (comma joins, NATURAL JOIN, USING, GROUPING SETS, and so on) are not recognized specially; they fail with ordinary parse or capability diagnostics rather than this named reservation.

SQL Write Statements: GRID_RELATION_SQL_WRITE

SQL DML, DDL, and transaction statement heads are recognized wherever an expression is expected and rejected:

• INSERT INTO, UPDATE <target> SET, DELETE FROM, MERGE INTO;
• CREATE, ALTER, DROP, or TRUNCATE followed by TABLE, VIEW, or INDEX;
• bare BEGIN, COMMIT, and ROLLBACK.

The diagnostic names the recognized statement and states the redirect:

R1C1 = UPDATE Orders SET status = "paid" WHERE id = 1

error[GRID_RELATION_SQL_WRITE]: UPDATE ... SET is not a Grid relational
expression; use an explicit table compound mutation (+=, ^=, *=, or -=) or a
host migration boundary

Each write statement has one Grid-native form. The four mutation operators are type-dispatched on a table-typed target; see the table-mutation section of assignments.md.

SQL thought	Grid-native form
INSERT INTO Orders ...	table Orders += { ... }
UPDATE Orders SET ...	table Orders *= { ... } (patch by id or filter)
DELETE FROM Orders ...	table Orders -= { ... } (by id or filter)
MERGE INTO Orders ...	table Orders ^= { ... } (upsert on row id)
CREATE TABLE Orders	table Orders = { schema: "orders" } declaration, optionally with columns
CREATE VIEW ...	Live Table table Name = SELECT ... (see Naming Intermediate Work)
ALTER / DROP / TRUNCATE	host migration boundary; catalog lifecycle is not model source
BEGIN / COMMIT / ROLLBACK	none; a rule body already commits atomically

Declaration redirects:

table Orders = { schema: "orders" }

table PaidOrders = SELECT region, amount AS revenue
  FROM Orders
  WHERE status = "paid"

Mutation redirects are model source inside WHEN/EVERY/AT rule bodies, where the durable table write runs after the rule's atomic cell-state commit. Mark the table declaration default when rules in the same model mutate it:

default table Orders = { schema: "orders" }
input order_received = FALSE
input shipment_confirmed = FALSE
input price_corrected = FALSE
input order_cancelled = FALSE

WHEN order_received THEN
  table Orders += { name: "Acme", amount: 100 }
END

WHEN shipment_confirmed THEN
  table Orders ^= { id: "rec01HXYZ", status: "Shipped" }
END

WHEN price_corrected THEN
  table Orders *= { id: "rec01HXYZ", amount: 200 }
END

WHEN order_cancelled THEN
  table Orders -= { id: "rec01HXYZ" }
END

A standalone top-level table mutation is an explicit host command that commits once when submitted, rather than becoming a recalculating formula; see assignments.md.

The rejection matches the statement shape, not the bare word. For the multi-keyword heads, a write keyword alone remains an ordinary name:

UPDATE = 1
Total = UPDATE + 1

BEGIN, COMMIT, and ROLLBACK are rejected as bare keywords in expression position, so do not use them as value names in generated code.

See Also

• style-guide.md
• cookbook.md
• infix-operators.md
• reference.md
• features.md

Append-only records and closed accounting dates

A column may declare appendOnly: TRUE to prevent removing any row of its table. Combine it with immutable: TRUE to preserve closure records. A date column may declare closedThrough: "ClosedPeriods.through_date": writes to rows dated on or before the greatest cutoff are refused, including updates, voids, restores and deletes. The target must be a unique, immutable, append-only date column in another table. Dates are canonical YYYY-MM-DD; missing or invalid dates are refused on this selected path. An empty cutoff table leaves all valid dates open. These rules are additive and cannot be silently loosened. See Ledger period closing and the multi-currency example.