DDL (Data Definition Language)

DDL statements manage the structure of your data platform — catalogs, warehouses, schemas, tables, views, and more. Gnok's DDL is PostgreSQL-flavoured and exposes full Iceberg capabilities underneath.

Catalogs

Catalogs are the top-level namespace in Gnok. Each catalog maps to an Iceberg catalog that manages schemas, tables, and metadata. You can create managed catalogs (backed by Gnok's built-in catalog service) or external catalogs (federated to a remote REST catalog like Polaris or Unity).

CREATE CATALOG

-- Managed catalog (default — stored on AWS S3 per the catalog's primary warehouse)
CREATE CATALOG analytics;

-- Managed catalog on a specific cloud (Gnok mints short-lived credentials at query time)
CREATE CATALOG marketing ON GCS;
CREATE CATALOG logs      ON AZURE;
CREATE CATALOG events    ON S3;

-- Managed catalog with an explicit warehouse URI (bring-your-own bucket)
CREATE CATALOG sandbox ON GCS WAREHOUSE 'gs://my-bucket/iceberg/';
CREATE CATALOG archive ON AZURE
    WAREHOUSE 'abfss://warehouse@myacct.dfs.core.windows.net/iceberg/';

-- External (federated REST) catalog
CREATE EXTERNAL CATALOG lakehouse
    TYPE rest
    URI 'https://iceberg-catalog.example.com'
    WAREHOUSE 'warehouse-name'
    WITH ('oauth2.token' = '...');

Clause	Effect
(none)	Defaults to `ON S3` — managed catalog using the catalog server's primary S3 warehouse
`ON GCS` / `ON AZURE` / `ON S3`	Picks the matching cloud's warehouse (see Credential Vending)
`WAREHOUSE '<uri>'`	Override the warehouse base URI (must match the `ON` cloud scheme)
`WITH (...)`	Catalog-specific properties (OAuth credentials, REST prefix, etc.)

note

ON {cloud} requires the catalog server to be configured with that cloud's authorized storage integration, managed by Gnok for the account. At read/write time the catalog vends short-lived credentials scoped to the table prefix — no long-lived cloud keys ever land on workers.

USE CATALOG

USE CATALOG analytics;

DROP CATALOG

-- Drop an empty catalog (fails if schemas or tables exist)
DROP CATALOG analytics;

-- No error if the catalog doesn't exist
DROP CATALOG IF EXISTS analytics;

-- Drop catalog and all child schemas, tables, and views
DROP CATALOG analytics CASCADE;

-- Drop catalog, all metadata, AND delete underlying S3 data files
DROP CATALOG analytics CASCADE PURGE;

-- Combined with IF EXISTS
DROP CATALOG IF EXISTS analytics CASCADE PURGE;

Modifier	Description
(none)	RESTRICT (default) — fails if the catalog contains any schemas or tables
`CASCADE`	Drops the catalog and all child objects (schemas, tables, views)
`CASCADE PURGE`	Same as CASCADE, plus deletes the underlying Iceberg data files from S3
`IF EXISTS`	No error if the catalog does not exist

caution

PURGE permanently deletes data files from object storage and cannot be undone. Use with care. PURGE requires CASCADE — you cannot purge data without also dropping child objects.

SHOW CATALOGS

SHOW CATALOGS;

Warehouses

Virtual warehouses are isolated compute pools that execute queries. Each warehouse has a dedicated set of workers, concurrency limits, and auto-suspend behavior.

CREATE WAREHOUSE

-- Basic warehouse with default settings (size=small, max_concurrent=8)
CREATE WAREHOUSE analytics_wh;

-- Warehouse with specific size and concurrency
CREATE WAREHOUSE etl_wh WITH SIZE = 'large', MAX_CONCURRENT = 16;

-- Full options
CREATE WAREHOUSE reporting_wh
  WITH SIZE = 'medium', MAX_CONCURRENT = 8, AUTO_SUSPEND_SECS = 600;

-- Pin the warehouse to a specific region
CREATE WAREHOUSE eu_wh
  WITH SIZE = 'large', MAX_CONCURRENT = 16, AUTO_SUSPEND_SECS = 300, REGION = 'eu-west-1';

-- Idempotent creation
CREATE WAREHOUSE IF NOT EXISTS analytics_wh;

Option	Type	Default	Description
`SIZE`	String	`small`	Warehouse size (see table below)
`MAX_CONCURRENT`	Integer	`8`	Maximum concurrent queries before queuing
`AUTO_SUSPEND_SECS`	Integer	`300`	Seconds of idle time before auto-suspend
`REGION`	String	account default	Region to place the warehouse in. Must be an active region — see SHOW REGIONS. The region is fixed at creation time and cannot be changed by `ALTER WAREHOUSE`; recreate the warehouse to move it.

Warehouse Sizes:

Size	Workers	CU/hour
`xsmall`	1	1
`small`	2	2
`medium`	4	4
`large`	8	8
`xlarge`	16	16
`x2large`	32	32
`x3large`	64	64
`x4large`	128	128

ALTER WAREHOUSE

-- Resize
ALTER WAREHOUSE analytics_wh SET SIZE = 'xlarge';

-- Change concurrency limit
ALTER WAREHOUSE analytics_wh SET MAX_CONCURRENT = 32;

-- Multiple options
ALTER WAREHOUSE analytics_wh SET SIZE = 'large', MAX_CONCURRENT = 16, AUTO_SUSPEND_SECS = 120;

-- Suspend (stop workers, no cost)
ALTER WAREHOUSE analytics_wh SUSPEND;

-- Resume
ALTER WAREHOUSE analytics_wh RESUME;

-- Assign a resource monitor
ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = monthly_budget;

A warehouse's REGION is immutable — ALTER WAREHOUSE rejects attempts to change it. To move a warehouse to a different region, drop and recreate it there.

USE WAREHOUSE

-- Set the active warehouse for the current session
USE WAREHOUSE analytics_wh;

All subsequent queries in the session execute on this warehouse's worker pool.

DROP WAREHOUSE

DROP WAREHOUSE analytics_wh;

-- No error if the warehouse doesn't exist
DROP WAREHOUSE IF EXISTS analytics_wh;

SHOW WAREHOUSES

SHOW WAREHOUSES;

Returns: name, size, status, region, max_concurrent, active_queries, created_at.

The region column reports the region each warehouse runs in. Warehouses created without an explicit REGION (including single-region and local deployments) report local-dev.

Warehouse states: running, suspended, starting, resizing, stopping.

SHOW WAREHOUSES is scoped to the current tenant — it lists only the warehouses your account can use.

See Resource Governance for admission control, memory management, and resource groups within warehouses. See Billing for compute unit pricing by warehouse size.

Regions

Regions are the geographic locations where warehouses run. A warehouse is pinned to one region at creation time (see CREATE WAREHOUSE). Regions are managed at the account level — there is no CREATE REGION / DROP REGION DDL; they are provisioned by the platform and surfaced to SQL through SHOW REGIONS.

SHOW REGIONS

SHOW REGIONS;

Lists the regions currently available for placing warehouses. Returns: name, display_name, status, last_seen_at.

Column	Description
`name`	Region identifier used in `CREATE WAREHOUSE ... REGION = '...'` (e.g. `us-east-1`, `eu-west-1`).
`display_name`	Human-readable region name.
`status`	Region availability. Only `active` regions are returned.
`last_seen_at`	Timestamp of the region's most recent heartbeat, or empty if it has not yet reported.

SHOW REGIONS lists only regions that are currently active and accepting warehouses, so its output is the set of valid values for the REGION option of CREATE WAREHOUSE.

Schemas

Schemas organize tables and views within a catalog. The fully-qualified name for any table is catalog.schema.table.

CREATE SCHEMA

CREATE SCHEMA analytics.web_events;
CREATE SCHEMA IF NOT EXISTS analytics.web_events;

DROP SCHEMA

DROP SCHEMA analytics.web_events;
DROP SCHEMA IF EXISTS analytics.web_events CASCADE;
DROP SCHEMA IF EXISTS analytics.web_events CASCADE PURGE;

CASCADE drops every table in the schema before dropping the namespace itself. Without CASCADE, dropping a non-empty schema is an error.
PURGE (only valid together with CASCADE) applies DROP TABLE ... PURGE to each member table, so the data files are physically deleted from object storage in addition to being removed from the catalog. Without PURGE, catalog metadata is removed but the underlying data files remain in S3/GCS/ADLS until cleaned up out-of-band.
PURGE without CASCADE is rejected — PURGE only acts on member tables, so it is meaningless on its own.

SHOW SCHEMAS

SHOW SCHEMAS;
SHOW SCHEMAS IN analytics;

Tables

Tables are Apache Iceberg tables stored in object storage (S3, GCS, Azure Blob). Every table supports schema evolution, hidden partitioning, time travel, and row-level operations.

CREATE TABLE

CREATE TABLE catalog.schema.orders (
    order_id    BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    amount      DECIMAL(10, 2),
    status      VARCHAR DEFAULT 'pending',
    order_date  DATE,
    region      VARCHAR COMMENT 'Geographic region'
)
COMMENT = 'Customer order history'
PARTITIONED BY (region, order_date)
TBLPROPERTIES (
    'write.format.default' = 'parquet',
    'write.parquet.compression-codec' = 'zstd'
);

CREATE OR REPLACE TABLE

Atomically replaces an existing table with a fresh definition. The old table is dropped (purge=false, so the underlying data files stay on object storage and remain recoverable via Iceberg time-travel until the snapshot retention window expires) and the new one is created in the same statement. Mutually exclusive with IF NOT EXISTS — combining them is a bind-time error.

CREATE OR REPLACE TABLE bb.hr.demo (
    id   INT,
    name VARCHAR
);

Also works as CREATE OR REPLACE TABLE … AS SELECT … — drops then runs the CTAS in one statement.

Column Options

Option	Description
`NOT NULL`	Column does not accept NULL values
`NULL`	Column accepts NULL values (default)
`DEFAULT expr`	Default value expression (parsed but not yet persisted to Iceberg metadata)
`COMMENT 'text'`	Column description (populates `information_schema.columns.comment`)
`PRIMARY KEY`	Column participates in the primary key (populates `information_schema.columns.is_primary_key`)

Table Options

Clause	Description
`PARTITIONED BY (...)`	Iceberg partition specification
`TBLPROPERTIES (...)`	Iceberg table properties (key-value pairs)
`WITH (...)`	Shorthand for table properties (e.g., `WITH ('format-version' = '3')`)
`LOCATION 'path'`	Custom table storage location
`COMMENT 'text'`	Table description (also accepts `COMMENT = 'text'`. Populates `information_schema.tables.comment`)
`IF NOT EXISTS`	No error if table already exists
`OR REPLACE` (modifier on CREATE)	Drop the existing table atomically before recreating

Format Version

Specify the Iceberg format version at table creation time:

-- v2 (default) — enables row-level deletes, sequence numbers
CREATE TABLE orders (...) WITH ('format-version' = '2');

-- v3 — enables VARIANT, GEOMETRY/GEOGRAPHY types, deletion vectors, row lineage
CREATE TABLE events (
    id BIGINT,
    payload VARIANT,
    location GEOMETRY
) WITH ('format-version' = '3');

See Iceberg Compatibility for details on what each version enables.

Partitioning

Gnok supports Iceberg's hidden partitioning transforms:

PARTITIONED BY (
    region,                    -- identity
    year(order_date),          -- year transform
    month(order_date),         -- month transform
    day(order_date),           -- day transform
    hour(event_time),          -- hour transform
    bucket(16, customer_id),   -- hash bucket
    truncate(10, zip_code)     -- truncate
)

Multi-spec partition lists that combine bucket(N, col) with other transforms are parsed and propagated correctly end-to-end — the bucket width N is preserved by the binder rather than being silently dropped on the way to the catalog. Earlier builds mishandled the comma-separated combination of a bucket spec with a calendar transform.

-- bucket(8, id) AND year(ts) — both transforms reach the catalog
CREATE TABLE events (id BIGINT, ts TIMESTAMP, payload VARCHAR)
PARTITIONED BY (bucket(8, id), year(ts));

Known follow-up

SHOW CREATE TABLE does not yet echo the PARTITIONED BY clause back from the catalog metadata. The partition spec is correctly stored and used for pruning at scan time; the round-trip rendering in SHOW CREATE TABLE is tracked as a separate follow-up.

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE catalog.schema.summary AS
SELECT region, COUNT(*) AS order_count, SUM(amount) AS total
FROM catalog.schema.orders
GROUP BY region;

CREATE TABLE IF NOT EXISTS catalog.schema.summary AS
SELECT ...;

ALTER TABLE

Add / Drop / Rename Columns

-- Add a column
ALTER TABLE orders ADD COLUMN status VARCHAR;

-- Add with position
ALTER TABLE orders ADD COLUMN priority INT AFTER status;
ALTER TABLE orders ADD COLUMN id BIGINT FIRST;

-- Drop a column
ALTER TABLE orders DROP COLUMN status;

-- Reject: column is referenced by the table's partition spec
-- (defense-in-depth — Iceberg requires partition evolution first)
ALTER TABLE events DROP COLUMN ts;
-- Error: column 'ts' is referenced by the table's partition spec;
-- drop the partition field first via ALTER TABLE ... DROP PARTITION FIELD.

-- Rename a column
ALTER TABLE orders RENAME COLUMN amount TO order_amount;

Alter Column Properties

-- Change column type (widening only: e.g., INT → BIGINT)
ALTER TABLE orders ALTER COLUMN amount SET DATA TYPE DECIMAL(12, 2);

-- Set NOT NULL
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;

-- Drop NOT NULL
ALTER TABLE orders ALTER COLUMN customer_id DROP NOT NULL;

-- Set column comment
ALTER TABLE orders ALTER COLUMN region SET COMMENT 'Geographic region code';
-- or
COMMENT ON COLUMN orders.region IS 'Geographic region code';

Table Properties

-- Set properties
ALTER TABLE orders SET TBLPROPERTIES (
    'write.parquet.compression-codec' = 'zstd',
    'write.metadata.compression-codec' = 'gzip'
);

-- Remove properties
ALTER TABLE orders UNSET TBLPROPERTIES ('write.metadata.compression-codec');

Row-Level Security

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

Tag Assignments

Object tags drive Tag-Based Access Control (ABAC). A tag may be set on a table, on an individual column, or on a schema / catalog (used for inheritance during the masking-policy walk).

-- Table-level tag
ALTER TABLE orders SET TAG sensitivity = 'restricted';
ALTER TABLE orders UNSET TAG sensitivity;

-- Column-level tag
ALTER TABLE orders ALTER COLUMN ssn SET TAG pii = 'high';
ALTER TABLE orders ALTER COLUMN ssn UNSET TAG pii;

SET TAG is upsert-shaped — repeating it with a new value overwrites the existing assignment. Tag values are validated against ALLOWED_VALUES declared at CREATE TAG time.

Primary Keys

Gnok is one of the few Iceberg-native engines with real PRIMARY KEY enforcement. Most Iceberg engines treat PRIMARY KEY as documentation metadata; Gnok validates uniqueness at INSERT time and can reject duplicates atomically.

Declaring a primary key

Both column-level and table-level constraints are accepted, including composite keys:

-- Column-level (single-column PK)
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    amount DECIMAL(10, 2)
);

-- Table-level (composite)
CREATE TABLE order_lines (
    order_id  BIGINT,
    line_id   INT,
    sku       VARCHAR,
    quantity  INT,
    PRIMARY KEY (order_id, line_id)
);

PK columns are implicitly NOT NULL — you don't need to write NOT NULL separately, and an explicit NULL insert into a PK column is rejected with a constraint violation. The implicit NOT NULL applies whether the PK was declared at column level or table level.

At CREATE time, Gnok validates that every named PK column exists in the column list and sets three pieces of state:

Iceberg identifier fields — the PK columns are registered with the catalog as the table's identifier fields (used by downstream CDC consumers)
Table property gnok.pk.enforced = true — tells the DML path to invoke PK checks on INSERT
Table property gnok.pk.columns = col1,col2,... — the ordered list of PK column names

Enforcement semantics

PK enforcement fires through the INSERT buffer (the staging path) and has two tiers:

Intra-batch dedup (always active in optimistic mode): a single INSERT statement with duplicate PK values in its VALUES list is rejected atomically — no rows land even if some in the batch were unique. The error message mentions the duplicate rows.
Cross-statement dedup (backend-dependent): two separate INSERTs that try to use the same PK value are reconciled through the staging backend's PK index. On RocksDB this is per-coordinator; on Postgres staging it is cluster-wide via a centralized PrimaryKeyService.

When the RocksDB staging backend is in use, Gnok maintains a per-table pk_index column family that stores one entry per (table, PK value) pair. Entries persist across commits so cross-statement and cross-commit dedup both work. The pk_index is cleaned up only on DROP TABLE or ALTER TABLE ... DROP PRIMARY KEY.

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR
);

INSERT INTO users VALUES (1, 'Alice');
INSERT INTO users VALUES (1, 'Bob');
-- ERROR: primary key violation — row with id=1 already exists

-- Intra-batch duplicate: rejected atomically, no rows inserted
INSERT INTO users VALUES (2, 'Carol'), (2, 'Dave');
-- ERROR: Duplicate primary key value in INSERT batch at rows 0 and 1

Enforcement modes

The gnok.pk.mode table property selects the enforcement style:

Mode	Behavior
`optimistic` (default)	INSERTs ack after the hot-tier check and commit in the background. Intra-batch dedup is synchronous; cross-statement violations may surface asynchronously.
`strict`	INSERTs bypass the staging buffer and block on the synchronous DML path. Useful when the client must not receive a false ACK.

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    amount DECIMAL(10, 2)
) TBLPROPERTIES ('gnok.pk.mode' = 'strict');

Adding a primary key to an existing table

ALTER TABLE ... ADD PRIMARY KEY runs in four steps:

Column validation — every named column must exist in the table schema. Nullable columns generate a warning (existing NULLs will later cause violations on the first conflicting INSERT).
Uniqueness validation — Gnok runs SELECT pk_cols, COUNT(*) FROM t GROUP BY pk_cols HAVING COUNT(*) > 1 LIMIT 10. If duplicates exist, the ALTER is rejected with sample offending values. Empty tables and unreadable tables fall through (the ALTER still succeeds, with a warning).
Metadata update — gnok.pk.enforced, gnok.pk.columns, and Iceberg identifier fields are set.
PK index backfill — the existing rows are scanned (SELECT the PK columns only), their PK values are extracted into PkEntry records, and the staging backend's PK index is populated. This ensures future INSERTs that duplicate pre-ALTER data are correctly rejected. On RocksDB the backfill is idempotent and chunked into 4K-key WriteBatches.

-- Add single-column PK
ALTER TABLE orders ADD PRIMARY KEY (order_id);

-- Add composite PK
ALTER TABLE order_lines ADD PRIMARY KEY (order_id, line_id);

After a successful ADD PRIMARY KEY, both the catalog metadata cache and the INSERT buffer's per-table cache are invalidated so the next INSERT re-fetches the updated metadata and takes the PK-enforcing path.

Dropping a primary key

ALTER TABLE orders DROP PRIMARY KEY;

DROP PRIMARY KEY clears the identifier fields, unsets the gnok.pk.enforced and gnok.pk.columns properties, invalidates metadata caches, and — critically — drops the pk_index entries for the table from the staging backend. Pending but unflushed staging data is preserved (unlike DROP TABLE, which wipes everything). After the drop, previously-enforced PK values can be re-inserted.

SHOW PRIMARY KEY

Inspect a table's primary key via SHOW PRIMARY KEY. Both FROM and IN keywords are accepted, and the plural SHOW PRIMARY KEYS form works too:

SHOW PRIMARY KEY FROM orders;
SHOW PRIMARY KEY IN orders;
SHOW PRIMARY KEYS FROM orders;

Returns one row per PK column:

Column	Type	Description
`table_name`	VARCHAR	The table name
`column_name`	VARCHAR	A PK column
`key_sequence`	INT	Position in the composite key (1-based)
`pk_enforced`	BOOLEAN	`true` if `gnok.pk.enforced = 'true'`

A table with no primary key returns zero rows (not an error).

Upsert via INSERT ... ON CONFLICT

See INSERT ... ON CONFLICT DO UPDATE in the DML reference. Gnok rewrites the upsert into an equivalent MERGE statement at bind time.

Write mode

The gnok.write.mode table property selects where a table's writes commit. Most tables need nothing here; the default is what every table already does.

Mode	Behavior
`direct` (default)	Ordinary Iceberg DML. Each statement commits to the catalog, so a small write costs a catalog round trip.
`command`	Writes commit through the engine's replicated log and are published into Iceberg by a background daemon. A small write acknowledges in single-digit milliseconds instead of hundreds. Ordinary DML is still permitted, which is what a migration needs while both writers exist.
`command_only`	As `command`, and out-of-band DML is refused (`GK007`). Use this once every writer has moved, so nothing can modify the table behind the engine's back.

-- Put an existing table on the low-latency path. Takes effect immediately;
-- no restart, and nothing else to configure.
ALTER TABLE orders SET TBLPROPERTIES ('gnok.write.mode' = 'command');

-- Once every writer has moved:
ALTER TABLE orders SET TBLPROPERTIES ('gnok.write.mode' = 'command_only');

An unrecognized value reads as direct, so a typo never silently changes how a table commits — the next attempted command write is refused with GK013, naming the table and the property.

What a `command` writer must do

The low-latency path acknowledges a write before it reaches Iceberg, so it has to be able to replay it exactly once and know which row it belongs to. That makes three requirements of the statement:

An explicit transaction carrying an idempotency key. The key is the opt-in — without it the transaction takes the ordinary path.
A full-primary-key MERGE with a complete target projection. Every column is assigned, and the ON clause names the whole primary key, so the row's identity and its post-image are both known without reading the table.
A target in command or command_only mode. A transaction that touches any table outside the write path is refused whole, before anything is admitted (GK013).

BEGIN;
SET LOCAL gnok.idempotency_key = '01a06867-04e0-7f34-a545-d82c7e40e656';  -- UUIDv7, one per command
SET LOCAL gnok.command_type    = 'order.edit';                            -- optional label
SET LOCAL gnok.expected_version = 4;                                      -- optional compare-and-swap

MERGE INTO orders x
USING (SELECT CAST(1001 AS BIGINT) AS order_id,
              CAST(99.50 AS DECIMAL(10,2)) AS amount) s
   ON x.order_id = s.order_id
 WHEN MATCHED THEN UPDATE SET amount = s.amount
 WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (s.order_id, s.amount);

COMMIT RETURNING;   -- returns a commit token: shard/term/index/version

Reads see their own writes: a query against a command table merges the rows that are ordered but not yet published, so a write is visible to the next statement without waiting for publication.

Statements that cannot be replayed exactly once are refused rather than silently taking a slower path — a MERGE that leaves columns unassigned, one whose ON clause is not the full primary key, an UPDATE ... WHERE, or a second write to the same table in one transaction. Each refusal names what it could not prove.

Foreign Keys

Unlike PRIMARY KEY (which Gnok enforces), FOREIGN KEY constraints are informational and unenforced — the model analytical and lakehouse engines typically use, where referential integrity isn't checked at write time. Gnok records the constraint, validates that the reference is well-formed, and surfaces it to BI tools (so Metabase, Tableau, DBeaver, etc. auto-draw the relationship graph and suggest joins), but it does not check referential integrity on INSERT/UPDATE/DELETE, and referential actions (CASCADE, SET NULL, …) are stored and reported but never executed.

This is a deliberate, honest design: declaring an FK that is reported as NOT ENFORCED does not pretend to enforce something it doesn't. (For the same reason, UNIQUE and CHECK constraints — which carry strong enforcement expectations Gnok can't yet meet in distributed mode — are still rejected rather than silently dropped.)

Declaring a foreign key

Both column-level (REFERENCES) and table-level (FOREIGN KEY (...) REFERENCES) forms are accepted, including composite keys and referential actions:

CREATE TABLE customers (
    id   BIGINT PRIMARY KEY,
    name VARCHAR
);

-- Column-level
CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    customer_id BIGINT REFERENCES customers (id),
    amount      DECIMAL(10, 2)
);

-- Table-level, named, with a referential action + omitted referenced columns
-- (defaults to the parent's PRIMARY KEY)
CREATE TABLE order_lines (
    line_id  BIGINT PRIMARY KEY,
    order_id BIGINT,
    CONSTRAINT order_lines_order_fk
        FOREIGN KEY (order_id) REFERENCES orders ON DELETE CASCADE
);

If the referenced columns are omitted, they default to the parent table's primary key. An unqualified parent table resolves to the child table's schema.

Validation

At declaration time (CREATE or ALTER) Gnok validates the shape of the reference and rejects it with a clear error — never persisting invalid metadata — when:

the parent table does not exist;
the referenced columns don't exist on the parent (or are omitted and the parent has no primary key);
a child column doesn't exist on the table;
the child and referenced column counts differ;
the constraint name collides with another FK on the same table.

No row-level validation is performed (FKs are unenforced), so adding one to a table with violating data succeeds.

Adding and dropping foreign keys

-- Add (named or unnamed; unnamed gets <table>_<cols>_fkey)
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

-- Drop by name
ALTER TABLE orders DROP CONSTRAINT orders_customer_fk;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS maybe_missing_fk;

ADD CONSTRAINT validates the reference (same rules as above) and appends it; DROP CONSTRAINT removes it by name (IF EXISTS makes a missing constraint a no-op rather than an error). Neither scans table data.

Storage and surfacing

FK constraints are stored in the gnok.fk.constraints Iceberg table property (a JSON array). They surface to clients through:

pg_catalog.pg_constraint — one contype = 'f' row per FK, with conkey/confkey as real int2[] column-position arrays and conrelid/confrelid resolved to the child/parent table OIDs.
JDBC DatabaseMetaData.getImportedKeys / getExportedKeys — the FK relationships a BI tool reads to draw its schema graph.

A fresh Metabase sync, for example, records orders.customer_id as a foreign key targeting customers.id. See PostgreSQL wire protocol for the BI-tool connectivity details.

Optimizer note: because unenforced FKs can be violated by the data, Gnok does not use them for join elimination or other rewrites today. A future RELY-style trust flag will gate that.

Partition Evolution

-- Add a partition field
ALTER TABLE events ADD PARTITION FIELD year(ts);

-- Drop a partition field
ALTER TABLE events DROP PARTITION FIELD year(ts);

-- Replace (evolve) a partition field
ALTER TABLE events REPLACE PARTITION FIELD year(ts) WITH day(ts) AS ts_day_v2;

Column Defaults

ALTER TABLE orders ALTER COLUMN score SET DEFAULT 100;
ALTER TABLE orders ALTER COLUMN score DROP DEFAULT;

Write Ordering

ALTER TABLE orders WRITE ORDERED BY id ASC, ts DESC;

Table Location

ALTER TABLE orders SET LOCATION 's3://new-bucket/warehouse/';

Identifier Fields

Mark columns as Iceberg identifier fields (used by downstream CDC consumers):

ALTER TABLE orders SET IDENTIFIER FIELDS (order_id, customer_id);

DROP TABLE

Dropping a table removes its metadata from the catalog. By default, the underlying data files in object storage are retained (for time travel recovery). Use PURGE to also delete the data files.

DROP TABLE catalog.schema.orders;
DROP TABLE IF EXISTS catalog.schema.orders;
DROP TABLE catalog.schema.orders PURGE;

DROP TABLE on a view raises a directional error. Earlier builds silently swallowed the request — DROP TABLE IF EXISTS my_view reported "table does not exist" and the view stayed live. Now the engine inspects the catalog entry's kind and emits a message pointing at the correct DDL:

DROP TABLE on 'my_view' which is a VIEW — use DROP VIEW instead.

DROP TABLE IF EXISTS of a missing table (with no view of the same name) still returns silently — IF EXISTS continues to suppress the "not found" error, but it does not mask the wrong-kind error.

Views

Views are named queries stored in the catalog. They do not store data — the query is re-evaluated each time the view is referenced. For pre-computed results, see Materialized Views.

CREATE VIEW

CREATE VIEW catalog.schema.active_orders AS
SELECT * FROM catalog.schema.orders WHERE status = 'active';

CREATE OR REPLACE VIEW catalog.schema.active_orders AS
SELECT * FROM catalog.schema.orders WHERE status = 'active';

CREATE VIEW IF NOT EXISTS catalog.schema.active_orders AS
SELECT * FROM catalog.schema.orders WHERE status = 'active';

Column-list alias

A CREATE VIEW may carry an explicit column-list alias — the body's columns are renamed positionally to the listed names:

CREATE VIEW catalog.schema.short_orders (a, b) AS
SELECT customer_id, amount FROM catalog.schema.orders;

-- 'a' and 'b' are the projected column names
SELECT a, b FROM catalog.schema.short_orders;

Earlier the column list was silently dropped, so SELECT a FROM … errored with Column 'a' not found in view. The renamed shape is also what SHOW CREATE VIEW round-trips.

DROP VIEW

DROP VIEW catalog.schema.active_orders;
DROP VIEW IF EXISTS catalog.schema.active_orders;

Materialized Views

Materialized views pre-compute and store query results as Iceberg tables for fast repeated access. Gnok supports three refresh strategies, incremental maintenance, and event-driven auto-refresh.

CREATE MATERIALIZED VIEW

-- On-demand refresh (default)
CREATE MATERIALIZED VIEW catalog.schema.sales_summary AS
SELECT region, DATE_TRUNC('day', order_date) AS day, SUM(amount) AS total
FROM orders
GROUP BY region, DATE_TRUNC('day', order_date);

-- Scheduled refresh
CREATE MATERIALIZED VIEW catalog.schema.hourly_metrics
REFRESH EVERY '10 minutes'
AS SELECT region, COUNT(*) AS cnt FROM orders GROUP BY region;

-- Event-driven refresh (auto-refreshes when base tables change)
CREATE MATERIALIZED VIEW catalog.schema.live_summary
REFRESH ON COMMIT
AS SELECT COUNT(*) AS cnt, SUM(amount) AS total FROM orders;

-- Idempotent creation
CREATE MATERIALIZED VIEW IF NOT EXISTS catalog.schema.sales_summary
AS SELECT ...;

Data is stored in a backing Iceberg table named __mv_<view_name> in the same schema. The initial data is materialized at creation time.

REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW catalog.schema.sales_summary;

Gnok automatically classifies each refresh into the optimal strategy based on the view definition and source table changes:

Strategy	When Selected	Description
AppendOnly	`SELECT *` / `SELECT col` without aggregation	Reads only new data files since last refresh snapshot
AggregateMerge	Aggregate queries (`SUM`, `COUNT`, etc.)	Merges new deltas into existing aggregate results
FullRefresh	`DISTINCT`, complex joins, or delta exceeds threshold	Recomputes the entire view from scratch

Each refresh bookmarks the source table snapshot IDs. Subsequent refreshes scan only the delta (new data files added since the bookmark), enabling efficient incremental maintenance.

SHOW CREATE MATERIALIZED VIEW

SHOW CREATE MATERIALIZED VIEW catalog.schema.sales_summary;

Returns the original view definition, refresh strategy, and source table dependencies.

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW catalog.schema.sales_summary;
DROP MATERIALIZED VIEW IF EXISTS catalog.schema.sales_summary;

Refresh Strategies

Strategy	Description
On-demand	Refresh only when explicitly requested via `REFRESH MATERIALIZED VIEW` (default)
Scheduled	Refresh on a configurable interval (e.g., `REFRESH EVERY '5 minutes'`)
On-commit	Refresh automatically when underlying tables are modified (via SSE event pipeline)

Staleness Detection

Each materialized view can be assigned a lag budget that defines freshness SLOs:

Tier	Target Lag	Max Lag	Use Case
Real-time	30s	60s	Dashboards, monitoring
Near-real-time	1 min	5 min	Operational reports
Standard	5 min	30 min	Analytics, BI (default)
Batch	30 min	1 hour	Nightly aggregations

Views transition through staleness states: Fresh → Approaching → Stale → Critical → Expired. A background monitor can automatically trigger refreshes and send alerts based on these states.

Persistence

Materialized view metadata (definition, refresh strategy, source dependencies, snapshot bookmarks) is persisted in the catalog service. Both data and metadata survive coordinator restarts.

See Advanced Features: Materialized Views for architecture details.

Sequences

Sequences generate unique, monotonically increasing (or decreasing) integer values.

-- Basic sequence (starts at 1, increments by 1)
CREATE SEQUENCE order_id_seq;

-- With options
CREATE SEQUENCE invoice_seq
    START WITH 1000
    INCREMENT BY 1;

-- Bounded sequence that wraps around
CREATE SEQUENCE round_robin_seq
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 10
    CYCLE;

-- Descending sequence
CREATE SEQUENCE countdown_seq
    START WITH 100
    INCREMENT BY -1
    MINVALUE 0
    NO CYCLE;

-- Idempotent creation
CREATE SEQUENCE IF NOT EXISTS order_id_seq START WITH 1;

Sequence Options

Option	Default	Description
`START WITH`	`1`	Initial value
`INCREMENT BY`	`1`	Step per `NEXTVAL` call (can be negative)
`MINVALUE` / `NO MINVALUE`	No minimum	Lower bound
`MAXVALUE` / `NO MAXVALUE`	No maximum	Upper bound
`CYCLE` / `NO CYCLE`	`NO CYCLE`	Wrap around when bounds are reached, or error

Using Sequences

Use NEXTVAL and CURRVAL as scalar functions:

-- Advance and return the next value
SELECT NEXTVAL('order_id_seq');

-- Return the last value generated in this session (does not advance)
SELECT CURRVAL('order_id_seq');

-- Use in INSERT
INSERT INTO orders (order_id, customer_id, amount)
VALUES (NEXTVAL('order_id_seq'), 100, 99.99);

CURRVAL is session-scoped — it returns the last NEXTVAL result for the current session. Calling CURRVAL before any NEXTVAL in the session returns an error.

Drop a Sequence

DROP SEQUENCE order_id_seq;
DROP SEQUENCE IF EXISTS order_id_seq;

SHOW Commands

-- Catalogs
SHOW CATALOGS;

-- Schemas
SHOW SCHEMAS;
SHOW SCHEMAS IN catalog_name;

-- Tables
SHOW TABLES;
SHOW TABLES IN schema_name;
SHOW TABLES IN catalog_name.schema_name;
SHOW TABLES LIKE '%orders%';

-- Views
SHOW VIEWS;
SHOW VIEWS schema_name;                      -- bare qualifier
SHOW VIEWS IN schema_name;                   -- IN form
SHOW VIEWS FROM schema_name;                 -- FROM synonym
SHOW VIEWS IN catalog_name.schema_name;      -- two-level
SHOW VIEWS IN catalog_name.ns1.ns2;          -- nested namespace (Iceberg)

-- Materialized Views
SHOW MATERIALIZED VIEWS;
SHOW MATERIALIZED VIEWS schema_name;         -- bare qualifier
SHOW MATERIALIZED VIEWS IN schema_name;      -- IN form
SHOW MATERIALIZED VIEWS FROM schema_name;    -- FROM synonym
SHOW MATERIALIZED VIEWS IN catalog.schema;   -- two-level
SHOW MATERIALIZED VIEWS IN cat.ns1.ns2;      -- nested namespace

-- Sequences
SHOW SEQUENCES;

-- Table details — DESCRIBE / DESC, optional TABLE keyword,
-- bare / 2-part / fully-qualified 3-part names all produce identical output
DESCRIBE orders;
DESCRIBE TABLE orders;
DESC TABLE bb.hr.orders;
DESCRIBE TABLE analytics.sales.orders;
SHOW CREATE TABLE orders;
SHOW CREATE VIEW active_orders;
SHOW CREATE MATERIALIZED VIEW daily_revenue;

-- Iceberg snapshots
SHOW SNAPSHOTS FROM orders;

-- Functions
SHOW FUNCTIONS;

-- Masking Policies (all policies, or filter by attached table)
SHOW MASKING POLICIES;
SHOW MASKING POLICIES ON orders;

Qualifier forms

SHOW VIEWS and SHOW MATERIALIZED VIEWS accept three interchangeable qualifier forms:

Form	Example
Bare qualifier	`SHOW VIEWS analytics.sales`
`IN <qualifier>`	`SHOW VIEWS IN analytics.sales`
`FROM <qualifier>` (synonym)	`SHOW VIEWS FROM analytics.sales`

All three produce identical results. The qualifier itself may be:

schema only (sales) — resolves against the session catalog.
catalog.schema (analytics.sales) — two-level.
catalog.ns1.ns2… (analytics.sales.us_west) — nested namespaces, which Iceberg supports natively. The first part is the catalog; the remaining parts are the dotted namespace path.

Identifier case

Unquoted identifiers are normalized to lowercase following Postgres convention. SHOW VIEWS IN Analytics.Sales and SHOW VIEWS IN analytics.sales return the same rows. To preserve case, double-quote the identifier: SHOW VIEWS IN "Analytics"."Sales".

Missing namespaces

Both statements return an empty result (not an error) when the qualifier points at a namespace that doesn't exist. This matches Iceberg's REST list_views / list_tables semantics and lets scripts iterate over schemas without special-casing newly-created empty ones.

-- Returns 0 rows, does not raise an error
SHOW VIEWS IN analytics.brand_new_schema;

SHOW MATERIALIZED VIEWS without a qualifier also returns 0 rows (instead of the catalog-service Identifier cannot be empty cryptic error) when no session-default catalog / schema is set — useful as a probe shape from BI tools that issue bare metadata queries during connection bootstrap. Supply a qualifier to actually list MVs.

Pagination

SHOW SCHEMAS, SHOW TABLES, and SHOW VIEWS (and the underlying information_schema.schemata / tables / views) drive paginated catalog listings under the hood — pageSize=1000, looped on next-page-token until exhausted. Catalogs (including Gnok Catalog) that cap a single un-paginated response at 100 rows still return the full list. There's no caller-visible truncation up to a 10,000-page guard limit (10M rows).

Format Version Upgrade

Upgrade a table's Iceberg format version (one-way, no downgrade):

-- Upgrade from v1 to v2 (enables row-level deletes)
ALTER TABLE orders SET TBLPROPERTIES ('format-version' = '2');

-- Upgrade from v2 to v3 (enables VARIANT, spatial types, deletion vectors, row lineage)
ALTER TABLE orders SET TBLPROPERTIES ('format-version' = '3');

caution

Ensure all consumers of the table support the target format version before upgrading.

Iceberg Table Operations

Inspecting snapshots

SHOW SNAPSHOTS FROM orders;

Returns one row per snapshot in the table's history:

Column	Type	Description
`snapshot_id`	`BIGINT`	Iceberg snapshot id (millisecond timestamp + entropy)
`is_current`	`BOOLEAN`	TRUE for the snapshot the `main` ref points at — i.e. what `SELECT` reads. After a rollback this may not be the newest by timestamp
`parent_snapshot_id`	`BIGINT`	Snapshot this one was committed on top of (NULL for the very first)
`sequence_number`	`BIGINT`	Monotonically-increasing per commit. Use this to order even when timestamps collide on the same millisecond
`timestamp`	`BIGINT`	Commit time, ms since epoch
`operation`	`VARCHAR`	`append`, `overwrite`, `delete`, `replace`
`manifest_list`	`VARCHAR`	S3 path to the snapshot's manifest list

Querying snapshots as a table — `<table>$snapshots`

SHOW SNAPSHOTS is a DDL command, so you can't subquery it. The matching table-valued metadata view is available via the Iceberg- standard <table>$snapshots syntax. WHERE / ORDER BY / LIMIT / projection all work:

-- The active head, by name
SELECT snapshot_id, sequence_number, operation
FROM bb.hr.orders$snapshots
WHERE is_current;

-- Two newest non-current snapshots, oldest first
SELECT sequence_number, operation, snapshot_id
FROM bb.hr.orders$snapshots
WHERE NOT is_current
ORDER BY sequence_number DESC
LIMIT 2;

-- Every append since a given timestamp (ms)
SELECT snapshot_id, sequence_number
FROM bb.hr.orders$snapshots
WHERE operation = 'append' AND timestamp > 1777700000000
ORDER BY sequence_number;

Same evaluator runs on every <table>$<view> system table — $history, $files, $manifests, $partitions, $refs, $entries, $all_data_files, $all_delete_files, $all_manifests — so WHERE record_count > 1000 on $files etc. all benefit. Supported predicates: bare boolean column refs, NOT, AND, OR, IS TRUE, IS FALSE, =, != against Boolean / VARCHAR / BIGINT columns. Anything fancier (joins, subqueries, GROUP BY, complex predicates) falls back to the unfiltered set rather than erroring — preserves the historical "give me everything" behaviour for shapes the post-fetch evaluator can't represent.

Metadata-table reads use your own identity's access, the same way ordinary SELECTs do, so $files / $manifests / $partitions return only what you're allowed to read.

Rolling back

Roll a table's main branch pointer back to a prior snapshot. This is an atomic metadata-only operation — no new data files are written, and the rolled-over snapshots remain in history (you can roll forward again, or read them via time-travel, until expire-snapshots prunes them).

Two equivalent surface syntaxes — pick whichever you prefer:

-- Iceberg-flavoured ROLLBACK form (mind: NOT a transactional
-- ROLLBACK; the parser disambiguates by lookahead — bare ROLLBACK is
-- a transaction abort, ROLLBACK TABLE is the snapshot operation)
ROLLBACK TABLE orders TO SNAPSHOT 1777747402203;
ROLLBACK TABLE orders TO PARENT;
ROLLBACK TABLE orders TO TIMESTAMP 1777740000000;

-- DDL-style alias (recommended — no cognitive collision with the
-- transactional verb; matches Spark/Iceberg's CALL form and
-- Delta's RESTORE family)
ALTER TABLE orders SET SNAPSHOT 1777747402203;
ALTER TABLE orders SET CURRENT SNAPSHOT 1777747402203;
ALTER TABLE orders SET VERSION AS OF 1777747402203;
ALTER TABLE orders SET TIMESTAMP AS OF 1777740000000;

Both forms map to the same handler — same atomicity, same effects.

Because ALTER TABLE ... SET SNAPSHOT <id> is a pure alias for ROLLBACK TABLE, it pins the table's current state to the named snapshot and shares the ROLLBACK error path. Running it against a table that has no snapshots yet (created but never written) errors under the ROLLBACK name:

CREATE TABLE bb.hr.empty_snap (id INT);
-- No INSERT yet → zero snapshots in history

ALTER TABLE bb.hr.empty_snap SET SNAPSHOT 1777747402203;
-- ERROR: ROLLBACK TABLE bb.hr.empty_snap: no snapshots found

Target	Resolves to
`SNAPSHOT <id>` / `SET SNAPSHOT <id>`	The named snapshot (must exist in history)
`PARENT`	Second-to-last snapshot in history (one step back from current)
`TIMESTAMP <ms>` / `SET TIMESTAMP AS OF <ms>`	Newest snapshot whose `timestamp` ≤ given value
`VERSION AS OF <id>`	Synonym for `SNAPSHOT <id>` (Delta-flavour)

End-to-end example:

CREATE TABLE bb.hr.demo_snap (id INT, name VARCHAR, salary INT);
INSERT INTO bb.hr.demo_snap VALUES (1, 'Alice', 100), (2, 'Bob', 80);

UPDATE bb.hr.demo_snap SET salary = 150 WHERE id = 1;
DELETE FROM bb.hr.demo_snap WHERE id = 2;
-- 3 snapshots: append (S1), overwrite (S2), delete (S3, currently active)

-- Find the snapshot id of the original INSERT
SELECT snapshot_id FROM bb.hr.demo_snap$snapshots
WHERE sequence_number = 1;

-- Roll back to it
ALTER TABLE bb.hr.demo_snap SET SNAPSHOT <S1_id>;

-- SELECT now sees the pre-UPDATE / pre-DELETE state
SELECT * FROM bb.hr.demo_snap ORDER BY id;
-- → (1, 'Alice', 100), (2, 'Bob', 80)

-- All 3 snapshots are still in history; is_current has moved
SELECT sequence_number, operation, is_current
FROM bb.hr.demo_snap$snapshots
ORDER BY sequence_number;
-- → 1, append,    True
--   2, overwrite, False
--   3, delete,    False

For the bigger picture — when to use rollback vs time-travel reads, how snapshots interact with materialised views and streaming ingest, and the cleanup story — see Snapshots, Time Travel, and Rollback.

REFRESH TABLE

Invalidate cached table metadata and reload from the catalog:

REFRESH TABLE orders;

READ CHANGES (CDC)

Read incremental changes between two snapshots (Iceberg v2+):

READ CHANGES FROM orders FROM 123456 TO 789012;

Returns rows annotated with change type indicators for change data capture pipelines.

See also: Table Maintenance for OPTIMIZE, VACUUM, and COMPACT operations. See also: Iceberg Compatibility for the full v1/v2/v3 feature matrix.

Dynamic Tables

Dynamic tables are automatically maintained query results — similar to materialized views but with a simpler contract. You define a query and a target lag, and Gnok ensures the table stays fresh within that lag. Use dynamic tables for ETL pipelines where you want declarative freshness guarantees without manually scheduling refreshes.

CREATE DYNAMIC TABLE

CREATE DYNAMIC TABLE hourly_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = analytics_wh
AS
  SELECT region, DATE_TRUNC('hour', order_date) AS hour, SUM(amount) AS total
  FROM orders
  GROUP BY region, DATE_TRUNC('hour', order_date);

-- Idempotent creation
CREATE DYNAMIC TABLE IF NOT EXISTS hourly_sales
  TARGET_LAG = '10 minutes'
AS SELECT ...;

-- Replace with new definition
CREATE OR REPLACE DYNAMIC TABLE hourly_sales
  TARGET_LAG = '30 minutes'
AS SELECT ...;

ALTER DYNAMIC TABLE

ALTER DYNAMIC TABLE hourly_sales SET TARGET_LAG = '5 minutes';
ALTER DYNAMIC TABLE hourly_sales SUSPEND;
ALTER DYNAMIC TABLE hourly_sales RESUME;

DROP / SHOW DYNAMIC TABLE

DROP DYNAMIC TABLE hourly_sales;
DROP DYNAMIC TABLE IF EXISTS hourly_sales;
SHOW DYNAMIC TABLES;

DROP DYNAMIC TABLE also drops the backing Iceberg table. SHOW DYNAMIC TABLES returns one row per dynamic table:

Column	Description
`name`, `catalog`, `schema`	Fully-qualified identity
`target_lag`	The freshness SLO from `TARGET_LAG`
`current_lag`	Time since the last successful refresh (`NEVER` if not yet refreshed)
`status`	`ACTIVE`, `SUSPENDED`, or `FAILED`
`warehouse`	Warehouse recorded at creation (informational)
`rows`, `bytes`	Size after the last refresh
`refresh_mode`	`AUTO` (default), `FULL`, or `INCREMENTAL`
`owner`	Principal that issued the `CREATE`
`created_on`	Creation timestamp

Refresh behavior

A dynamic table owns a backing Iceberg table (its own qualified name), materialized at CREATE time and then kept fresh by the engine — you never issue an explicit REFRESH. Each coordinator periodically checks which dynamic tables have exceeded their TARGET_LAG; a per-table catalog lease ensures exactly one coordinator refreshes per lag window across the cluster, so the effective refresh cadence is the target lag.

Each refresh picks the cheapest correct strategy:

Mode	When	What runs
Incremental	A simple, single-source projection/filter view with an append-only change since the last refresh	`INSERT INTO` of only the new source rows (snapshot-range delta)
Full	Aggregates (`GROUP BY`, `SUM`/`COUNT`/…), `DISTINCT`, multiple sources, or a change range containing deletes	`INSERT OVERWRITE` recomputing the whole result

The source watermark is bookmarked in the catalog (shared across coordinators), so an incremental refresh never re-processes rows that already landed — even after the refresh lease moves to a different coordinator.

ALTER DYNAMIC TABLE … SUSPEND stops refreshes (the data stays at its last state); RESUME re-enables them; a FAILED status means the last refresh errored and will be retried on the next tick.

AI-aware incremental refresh

Because an incremental refresh re-scans only the rows added since the last bookmark, a per-row projection in the defining query is re-evaluated only on those new rows — existing rows' values are already committed in the backing table. This is especially valuable for AI/LLM enrichment:

CREATE DYNAMIC TABLE enriched_reviews
  TARGET_LAG = '5 minutes'
AS
  SELECT id, body, AI_SENTIMENT(body) AS mood
  FROM reviews;            -- simple, single-source

AI_SENTIMENT is invoked only on newly-inserted reviews on each refresh, never re-run on rows whose source bytes haven't changed. This holds even for non-deterministic models (temperature > 0), where a result cache cannot help — the backing table itself is the durable per-row AI result store. See AI Scalar Functions.

note

Single-pass AI evaluation follows the incremental path, which applies to simple single-source views. An AI call inside an aggregate (GROUP BY) currently triggers a full recompute (every row re-evaluated).

Data Quality Tests

Data quality tests are boolean assertions that run automatically on every commit to a target table. They are record-and-alert: a failing assertion is logged and counted but never blocks the write, so they are safe to attach to production tables. Definitions live in the catalog and fire cluster-wide — a per-table lease ensures exactly one coordinator evaluates each commit.

CREATE TEST

CREATE TEST no_null_emails
  AS ASSERT (SELECT COUNT(*) FROM catalog.schema.users WHERE email IS NULL) = 0
  ON EVERY COMMIT TO catalog.schema.users;

-- Replace an existing test
CREATE OR REPLACE TEST no_null_emails
  AS ASSERT (SELECT COUNT(*) FROM catalog.schema.users WHERE email IS NULL) = 0
  ON EVERY COMMIT TO catalog.schema.users;

The assertion is any boolean SQL expression — typically a (SELECT …) <comparison> over the target table. Reference the target table by its fully-qualified name. On each commit to the ON EVERY COMMIT TO table, the engine evaluates SELECT (<assertion>); a false, NULL, or error result is recorded and alerted (metric + log) while the commit itself proceeds untouched.

DROP / SHOW TESTS

DROP TEST no_null_emails;
DROP TEST IF EXISTS no_null_emails;
SHOW TESTS;

SHOW TESTS returns one row per test:

Column	Description
`name`	Test name
`target`	The `catalog.schema.table` it fires on
`assertion`	The boolean assertion SQL
`last_outcome`	`pass`, `fail`, `error`, or `not_run`
`runs`	Total evaluations
`failures`	Evaluations that returned false
`last_detail`	Detail for the last run (the failing value or error text)

note

Outcome counters (runs / failures / last_outcome) can be partial while the service is scaling. Use them to spot failing tests, and confirm a failure by running the assertion query yourself.

Streams

Streams capture change data (CDC) on Iceberg tables. They expose inserts, updates, and deletes since a managed snapshot watermark.

CREATE STREAM

-- Standard stream (tracks all DML changes)
CREATE STREAM orders_changes ON TABLE orders;

-- Append-only stream (tracks inserts only, more efficient)
CREATE STREAM orders_inserts ON TABLE orders APPEND_ONLY = TRUE;

-- Show initial rows on first consume
CREATE STREAM orders_stream ON TABLE orders SHOW_INITIAL_ROWS = TRUE;

-- Idempotent creation
CREATE STREAM IF NOT EXISTS orders_changes ON TABLE orders;

The stream and source table must be in the same catalog and schema. Without SHOW_INITIAL_ROWS = TRUE, the source table's current snapshot becomes the baseline and existing rows are not returned.

Using Streams

-- Preview pending changes without advancing the stream offset
SELECT * FROM orders_changes;

-- Consume pending changes (advances the stream offset)
CONSUME STREAM orders_changes;

-- Check if a stream has unconsumed data
SELECT SYSTEM$STREAM_HAS_DATA('orders_changes');

-- Manually advance the stream offset without consuming
ALTER STREAM orders_changes ADVANCE;

An ordinary SELECT from a stream is repeatable and non-consuming. CONSUME STREAM evaluates a bounded change range, returns its rows, and then advances the watermark with compare-and-swap. ALTER STREAM ... ADVANCE advances without returning the pending rows and should be treated as an explicit discard operation.

Stream rows include METADATA$ACTION, METADATA$ISUPDATE, and METADATA$ROW_ID. When the source has valid Iceberg identifier fields, an update is returned as a related DELETE/INSERT pair with METADATA$ISUPDATE = true and the same row ID.

caution

The current release does not atomically combine target DML with stream watermark advancement. INSERT ... SELECT FROM <stream> and MERGE ... USING <stream> must not be treated as transactional stream consumption. See Streams & Tasks for delivery semantics and operational limits.

SHOW / DROP STREAM

SHOW STREAMS;
SHOW STREAMS ON TABLE orders;
DROP STREAM orders_changes;
DROP STREAM IF EXISTS orders_changes;

SHOW ML STREAMS

Lists the ML-inference streams visible to the current tenant.

SHOW ML STREAMS;

Returns one row per ML stream:

Column	Description
`tenant_id`	Tenant that owns the stream
`stream_name`	Name of the ML-inference stream

SYSTEM$STREAM_HAS_DATA

The canonical check for whether a stream has unconsumed change data. This is a SYSTEM$ scalar function (not a SHOW statement), so it is called from the projection of a SELECT and is commonly used as a CREATE TASK ... WHEN predicate.

-- Returns a single boolean column `has_data`
SELECT SYSTEM$STREAM_HAS_DATA('orders_changes');

Column	Description
`has_data`	`true` if the stream has unconsumed data, otherwise `false`

For a standard stream, the function returns true when the source table has a snapshot after the stream watermark. For an append-only stream, the intervening range must contain an append snapshot. The function does not advance the watermark.

Tasks

Tasks execute SQL statements on a schedule or in response to stream data.

CREATE TASK

-- Interval-based schedule
CREATE TASK daily_aggregate
  SCHEDULE = '60 MINUTES'
  WAREHOUSE = etl_wh
AS
  INSERT INTO hourly_summary
  SELECT region, COUNT(*) FROM orders GROUP BY region;

-- CRON schedule
CREATE TASK nightly_cleanup
  SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
  DELETE FROM staging WHERE created_at < DATEADD(day, -7, CURRENT_DATE);

-- Task dependency (runs after parent completes)
CREATE TASK child_task AFTER parent_task AS SELECT 1;

-- Idempotent creation
CREATE TASK IF NOT EXISTS my_task SCHEDULE = '5 MINUTES' AS SELECT 1;

ALTER / EXECUTE TASK

-- Tasks are created in SUSPENDED state — resume to activate
ALTER TASK daily_aggregate RESUME;
ALTER TASK daily_aggregate SUSPEND;
ALTER TASK daily_aggregate SET SCHEDULE = '30 MINUTES';

-- Manually trigger a task execution
EXECUTE TASK daily_aggregate;

SHOW / DROP TASK

SHOW TASKS;
DROP TASK daily_aggregate;
DROP TASK IF EXISTS daily_aggregate;

Network Policies

Network policies restrict access by IP address at the account or user level.

CREATE NETWORK POLICY

CREATE NETWORK POLICY office_only
  ALLOWED_IP_LIST = ('10.0.0.0/8', '192.168.1.0/24')
  BLOCKED_IP_LIST = ('10.0.0.99')
  COMMENT = 'Office network access only';

CREATE NETWORK POLICY IF NOT EXISTS vpn_policy
  ALLOWED_IP_LIST = ('172.16.0.0/12');

ALTER NETWORK POLICY

ALTER NETWORK POLICY office_only SET ALLOWED_IP_LIST = ('10.0.0.0/8');
ALTER NETWORK POLICY office_only SET BLOCKED_IP_LIST = ('10.0.0.50', '10.0.0.99');
ALTER NETWORK POLICY office_only SET COMMENT = 'Updated policy';

Assign to Account or User

ALTER ACCOUNT SET NETWORK_POLICY = office_only;
ALTER ACCOUNT UNSET NETWORK_POLICY;
ALTER USER alice SET NETWORK_POLICY = vpn_policy;

SHOW / DROP NETWORK POLICY

SHOW NETWORK POLICIES;
DROP NETWORK POLICY office_only;
DROP NETWORK POLICY IF EXISTS office_only;

Replication & Failover

Database replication copies catalog metadata and table data to secondary accounts for disaster recovery and read scaling. Failover groups automate promotion of replicas when the primary becomes unavailable.

Database Replication

-- Enable replication on the primary database
ALTER DATABASE analytics ENABLE REPLICATION TO ACCOUNTS org.account2, org.account3;

-- Create a replica on the secondary account
CREATE DATABASE analytics_replica AS REPLICA OF org.account1.analytics;

-- Sync the replica
ALTER DATABASE analytics_replica REFRESH;

-- Promote replica to primary (failover)
ALTER DATABASE analytics_replica PRIMARY;

-- Disable replication
ALTER DATABASE analytics DISABLE REPLICATION;

-- Show replication status
SHOW REPLICATION DATABASES;

Failover Groups

CREATE FAILOVER GROUP prod_failover
  OBJECT_TYPES = DATABASES
  ALLOWED_DATABASES = analytics, warehouse
  ALLOWED_ACCOUNTS = org.account2, org.account3
  REPLICATION_SCHEDULE = '10 MINUTE';

SHOW FAILOVER GROUPS;
DROP FAILOVER GROUP prod_failover;
DROP FAILOVER GROUP IF EXISTS prod_failover;

User-Defined Functions

Gnok supports two UDF runtimes. Choose based on your performance needs and team expertise:

	WASM UDFs	Python UDFs
Performance	Near-native (compiled, no interpreter overhead)	Interpreted (embedded Python runtime)
Languages	Rust, Go, C/C++, or any language targeting `wasm32-wasip2`	Python only
Best for	Hot-path transforms, high-throughput ETL, latency-sensitive queries	Data science, prototyping, leveraging Python libraries
Deployment	Compile to `.wasm`, base64-encode, register via SQL	Inline Python source in the `CREATE FUNCTION` statement
Sandbox	Wasmtime sandbox with memory/CPU limits	In-process Python interpreter
Distributed	Module bytes sent to workers, compiled and cached on first use	Source sent to workers, interpreted per invocation

Use WASM when the UDF is called on every row of a large table and performance matters. Use Python when you need fast iteration, access to Python's ecosystem, or the function runs on small result sets.

LANGUAGE accepts wasm, python, and native — the last is reserved for built-in UDFs shipped with the engine (not user-authored). A SQL-bodied CREATE FUNCTION (LANGUAGE sql) is not supported; use a view or the trained-in-engine CREATE MODEL surface for SQL-defined logic.

WASM UDFs

Write UDFs in any language that compiles to WebAssembly (Rust, Go, C/C++). Register the compiled .wasm binary as base64:

CREATE FUNCTION normalize_email(TEXT) RETURNS TEXT
LANGUAGE wasm
VOLATILITY immutable
AS 'AGFzbQEAAAA...base64-encoded-wasm-bytes...';

-- With OR REPLACE
CREATE OR REPLACE FUNCTION normalize_email(TEXT) RETURNS TEXT
LANGUAGE wasm
VOLATILITY immutable
AS 'base64-wasm-bytes';

Option	Values	Description
`VOLATILITY`	`immutable`, `stable`, `volatile` (default)	Controls optimizer behavior — `immutable` enables constant folding and pushdown

WASM modules are compiled once and cached (SHA-256 content-addressed). On distributed clusters, modules are sent inline with fragment execution requests and cached on workers.

See WASM UDFs for the full authoring guide, sandbox limits, and ONNX model integration.

Python UDFs

CREATE FUNCTION py_double(x FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
AS $$
def py_double(x):
    return x * 2.0
$$;

CREATE FUNCTION py_greet(name VARCHAR)
RETURNS VARCHAR
LANGUAGE PYTHON
AS $$
def py_greet(name):
    return 'Hello, ' + name + '!'
$$;

-- Use in queries
SELECT py_double(amount), py_greet(customer_name) FROM orders;

Python UDFs are persisted to the catalog and survive coordinator restarts. In HA clusters, a function created on one coordinator is automatically available on all coordinators via lazy-loading at query time.

Stored Procedures

For a guided introduction and a runnable Studio workflow, see Procedural SQL and the order-processing tutorial.

Stored procedures run a Snowflake-Scripting-style SQL body server-side via CALL. They are durable (persisted in the catalog), hydrated on restart, and visible across coordinators.

Without OR REPLACE, creating a procedure whose name and signature already exist is an error (use CREATE OR REPLACE PROCEDURE to overwrite); a different signature creates a new overload (see Overloading). Parameters may declare an IN / OUT / INOUT mode and a trailing DEFAULT; OUT/INOUT values are returned as result columns. STRICT (a.k.a. RETURNS NULL ON NULL INPUT) short-circuits to NULL when any input argument is NULL. COMMENT='...' is stored and shown in SHOW PROCEDURES / SHOW CREATE PROCEDURE.

CREATE [OR REPLACE] PROCEDURE <name>
  ( [ [IN|OUT|INOUT] <param> <type> [DEFAULT <expr>] [, ...] ] )
  RETURNS { <type> | TABLE ( <col> <type> [, ...] ) }
  LANGUAGE SQL
  [ STRICT | { CALLED | RETURNS NULL } ON NULL INPUT ]
  [ EXECUTE AS { CALLER | OWNER } ]
  [ COMMENT='<text>' ]
  AS $$
    [DECLARE <var> <type> [DEFAULT <expr>]; ...]
    BEGIN
      <statements>
    [EXCEPTION WHEN <name> | OTHER THEN <statements>;]
    END;
  $$;

CALL <name>([<args>]);
DROP PROCEDURE [IF EXISTS] <name> [ ( <argtype> [, ...] ) ];

-- Anonymous (inline) procedure — define and run in one statement, no CREATE
-- privilege and no persistence:
WITH <name> AS PROCEDURE (<params>) RETURNS <type> [LANGUAGE SQL]
  AS $$ ... $$
CALL <name>([<args>]);

Body language

Construct	Form
Declare locals	`DECLARE v INT DEFAULT 0;` / `DECLARE e EXCEPTION;`
Assign	`v := <expr>;` (or `SET v := <expr>;`)
Conditional	`IF <cond> THEN ... [ELSEIF <cond> THEN ...] [ELSE ...] END IF;`
Multi-way branch	`CASE WHEN <cond> THEN ... [WHEN ...]* [ELSE ...] END CASE;` (searched) or `CASE <expr> WHEN <val> THEN ... END CASE;` (simple). Raises `CASE_NOT_FOUND` if no arm matches and there is no `ELSE`.
While loop	`WHILE <cond> DO ... END WHILE;`
For loop	`FOR i IN [REVERSE] <a> TO <b> DO ... END FOR;`
General loops	`LOOP ... END LOOP;` (exit via `BREAK` / `RETURN`) or `REPEAT ... UNTIL <cond> END REPEAT;`
Loop control	`BREAK [<label>];` / `CONTINUE [<label>];` (`EXIT` / `ITERATE` aliases). Label a loop with `<<lbl>>` to break or continue an enclosing loop.
Nested block	`[DECLARE ...] BEGIN ... [EXCEPTION WHEN ... THEN ...] END;` — its own variable scope and exception handlers.
Return	`RETURN <expr>;`
Capture a row	`SELECT ... INTO :v1, :v2 ...;` (errors unless exactly one row)
Dynamic SQL	`EXECUTE IMMEDIATE <string-expr> [INTO :v ...];`. Use `IDENTIFIER(<expr>)` in embedded SQL for a dynamic object name — the value is validated and spliced unquoted.
Embedded SQL	any `INSERT` / `UPDATE` / `DELETE` / DDL / `SELECT`
Call a procedure	`CALL <proc>(<args>) [INTO :var];` from within a body (captures a scalar return); args may be positional or named (`name => value`).
Raise / catch	`RAISE <name> [<msg>];` ... `EXCEPTION WHEN <name> / OTHER THEN ...`. Declare numbered exceptions with `DECLARE e EXCEPTION (-20001, 'msg');`; `SQLCODE` / `SQLERRM` / `SQLSTATE` are readable inside a handler.
Cursor	`DECLARE c CURSOR FOR <query>;` then `OPEN c [USING (...)]` / `FETCH [n] [FROM] c INTO ...` / `CLOSE c`
Cursor loop	`FOR rec IN c DO ... END FOR;` or `FOR rec IN (<query>) DO ... END FOR;` (columns exposed as `rec_<col>`)
Result set	`DECLARE rs RESULTSET; rs := (<query>); RETURN TABLE(rs);`

Variable binding

In Scripting expressions (IF/WHILE conditions, FOR bounds, assignment right-hand sides, RETURN) reference a variable by its bare name. In embedded SQL a bare name is a column — bind a variable into SQL with :name. :name is value-position only and is rendered as a safely-escaped literal (no SQL injection); an unbound :name is an error.

Examples

-- Control flow + RETURN
CREATE PROCEDURE classify(n INT) RETURNS VARCHAR LANGUAGE SQL AS $$
BEGIN
  IF n > 0 THEN RETURN 'pos';
  ELSEIF n < 0 THEN RETURN 'neg';
  ELSE RETURN 'zero';
  END IF;
END $$;
CALL classify(-5);          -- 'neg'

-- WHILE accumulator
CREATE PROCEDURE sum_to(n INT) RETURNS INT LANGUAGE SQL AS $$
DECLARE total INT DEFAULT 0; i INT DEFAULT 1;
BEGIN
  WHILE i <= n DO total := total + i; i := i + 1; END WHILE;
  RETURN total;
END $$;
CALL sum_to(100);           -- 5050

-- SELECT ... INTO a variable
CREATE PROCEDURE row_count() RETURNS BIGINT LANGUAGE SQL AS $$
DECLARE c BIGINT;
BEGIN SELECT COUNT(*) INTO :c FROM orders; RETURN c; END $$;

-- Exception handling
CREATE PROCEDURE safe_lookup(k INT) RETURNS VARCHAR LANGUAGE SQL AS $$
DECLARE v VARCHAR;
BEGIN
  SELECT name INTO :v FROM users WHERE id = :k;
  RETURN v;
EXCEPTION WHEN OTHER THEN RETURN 'lookup failed';
END $$;

-- RETURNS TABLE
CREATE PROCEDURE recent(n INT) RETURNS TABLE (id BIGINT, amount DOUBLE)
LANGUAGE SQL AS $$
BEGIN
  SELECT id, amount FROM orders ORDER BY id DESC LIMIT :n;
END $$;
CALL recent(10);            -- returns up to 10 rows

A scalar RETURNS procedure hands back a one-row, one-column result named after the procedure; a RETURNS TABLE(...) procedure returns the rows of its final top-level SELECT; a procedure with no RETURNS (or RETURNS VOID) returns a status row.

Cursors and result sets

A cursor iterates a query's rows. Cursors stream — they hold a bounded amount of memory (one batch plus a small prefetch buffer) regardless of result size, so a procedure can walk a billion-row table without materializing it. The cursor FOR loop is the common form (implicit OPEN/FETCH/CLOSE, exits at exhaustion); explicit OPEN/FETCH/CLOSE give finer control.

-- Cursor FOR loop over an inline query (streams; columns exposed as rec_<col>)
CREATE PROCEDURE count_big_orders(threshold DOUBLE) RETURNS INT LANGUAGE SQL AS $$
DECLARE n INT DEFAULT 0;
BEGIN
  FOR rec IN (SELECT id, amount FROM orders) DO
    IF rec_amount > threshold THEN n := n + 1; END IF;
  END FOR;
  RETURN n;
END $$;

-- Explicit cursor with OPEN ... USING / FETCH / CLOSE
CREATE PROCEDURE first_in_region(r VARCHAR) RETURNS BIGINT LANGUAGE SQL AS $$
DECLARE c CURSOR FOR SELECT id FROM orders WHERE region = :1 ORDER BY id;
        first_id BIGINT;
BEGIN
  OPEN c USING (r);          -- USING args bind the cursor query's :1, :2, ...
  FETCH c INTO :first_id;    -- NOT-FOUND leaves :first_id unchanged
  CLOSE c;
  RETURN first_id;
END $$;

-- RESULTSET handle returned as a table
CREATE PROCEDURE top_orders(n INT) RETURNS TABLE (id BIGINT, amount DOUBLE)
LANGUAGE SQL AS $$
DECLARE rs RESULTSET;
BEGIN
  rs := (SELECT id, amount FROM orders ORDER BY amount DESC LIMIT :n);
  RETURN TABLE(rs);
END $$;

Bounded memory — OPEN/FETCH/FOR pull batches from the distributed workers on demand; the coordinator never holds the whole result. Closing (or the loop finishing / the procedure returning) cancels the rest and frees the workers. The cursor's snapshot is pinned at OPEN, so it reads a stable point-in-time even if the table changes mid-iteration.
FETCH n advances n rows and binds the last into the INTO targets; on exhaustion the targets are left unchanged. The FOR loop is the easiest way to iterate to exhaustion.
RETURN TABLE(rs) / RETURN TABLE(c) hands the result set back as the procedure's table result (this form materializes, since the whole table is returned).
A query that needs a coordinator-side global merge (a multi-worker ORDER BY / TopK / LIMIT) is materialized transparently and still iterates correctly — only the streamable shapes stream.

Execution model

Caller's rights — the body runs as the caller: current_user, row-level security, and data access all use the caller's identity and token. The CALL itself requires EXECUTE on the procedure (see Privileges below).
Persistence / HA — CREATE PROCEDURE persists to the catalog; procedures survive a coordinator restart and are lazy-loaded across coordinators.
Autocommit by default; opt-in transactions — each embedded statement commits independently unless the body opens an explicit transaction with BEGIN TRANSACTION (see Transactions below), in which case the statements up to the matching COMMIT commit atomically.
Bounded-memory cursors — cursor iteration streams; coordinator memory is bounded by the prefetch buffer, independent of result cardinality (validated iterating tens of millions of rows with flat memory).
Guards — WHILE, integer-range FOR, LOOP, and REPEAT loops are capped at 1,000,000 iterations and nested CALL depth at 50 (a clear error, not a crash). Cursor FOR loops are not capped — they are data-bounded and terminate at exhaustion.

Transactions

A procedure body may group writes into an explicit transaction with BEGIN TRANSACTION … COMMIT / ROLLBACK. Everything between BEGIN TRANSACTION and the matching COMMIT is staged and committed atomically; a ROLLBACK discards it.

CREATE OR REPLACE PROCEDURE finance.public.transfer(from_id INT, to_id INT, amt NUMERIC)
RETURNS STRING LANGUAGE SQL AS
$$
BEGIN
  BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - :amt WHERE id = :from_id;
    UPDATE accounts SET balance = balance + :amt WHERE id = :to_id;
  COMMIT;
  RETURN 'ok';
EXCEPTION WHEN OTHER THEN
  ROLLBACK;                       -- both UPDATEs are undone atomically
  RAISE;
END
$$;

COMMIT / ROLLBACK also accept the optional WORK / TRANSACTION keyword; START TRANSACTION is a synonym for BEGIN TRANSACTION. Bare BEGIN … END (no TRANSACTION keyword) remains a block, not a transaction.

Semantics:

Leftover auto-rollback — if the procedure returns (or an unhandled error propagates out) while a transaction it opened is still open, that transaction is rolled back. A procedure never leaks an open transaction.
EXCEPTION handlers — an open transaction survives into the handler, so a handler can ROLLBACK (or COMMIT) it, as in the example above.
Autonomous at the session boundary — a procedure manages its own transaction; it does not join the calling session's transaction. A procedure's COMMIT is durable even if the caller later rolls back its own session transaction (consistent with autocommit-by-default above).
Within a call tree — a nested CALL participates in the caller procedure's open transaction (its writes commit or roll back atomically with it); a nested procedure may not open its own transaction (doing so is a catchable error).

Overloading

A procedure name may have multiple overloads that differ by parameter signature. CREATE PROCEDURE with a new signature adds an overload; CREATE OR REPLACE replaces only the matching-signature overload (the others are untouched).

CREATE PROCEDURE app.public.area(r FLOAT)          RETURNS FLOAT ... ;  -- circle
CREATE PROCEDURE app.public.area(w FLOAT, h FLOAT) RETURNS FLOAT ... ;  -- rectangle

CALL app.public.area(2.0);        -- resolves to area(FLOAT)
CALL app.public.area(2.0, 3.0);   -- resolves to area(FLOAT, FLOAT)

CALL resolves the overload by the number of arguments first (accounting for DEFAULTs and OUT params), then — among same-arity candidates — by argument type (literals, CAST(...) / x::T, and typed variables are matched against the declared parameter types, with numeric widening). If no overload matches the argument count, or the choice is ambiguous, the CALL errors and lists the candidate signatures.

SHOW PROCEDURES lists one row per overload (with its arg_types). DROP PROCEDURE name(argtypes) drops a specific overload; bare DROP PROCEDURE name drops the sole overload, or errors asking for the signature when the name is overloaded.

Privileges

A procedure has an owner (the principal that ran CREATE PROCEDURE, recorded as created_by), and CALL is gated by an EXECUTE privilege check. A caller may invoke a procedure when any of the following holds:

they are the owner;
they hold an admin role (platform_admin / tenant_admin, or gnok_admin / superuser / ACCOUNTADMIN);
they have been granted EXECUTE (or USAGE, which aliases to EXECUTE) on the procedure.

Otherwise the CALL is denied with SQLSTATE 42501 (permission denied).

-- Grant the right to CALL a procedure to a role (USAGE is an alias for EXECUTE)
GRANT EXECUTE ON PROCEDURE sales.public.apply_discount(INT) TO ROLE analyst;
GRANT USAGE   ON PROCEDURE sales.public.apply_discount      TO ROLE reader;

-- Revoke it
REVOKE EXECUTE ON PROCEDURE sales.public.apply_discount(INT) FROM ROLE analyst;

The grant is keyed on the procedure name (the argument signature is optional in the grant). Procedures created before this gate shipped have no recorded owner and are grandfathered — they remain callable by any authenticated user until they are recreated with an owner.

Caller's vs owner's rights

A procedure declares EXECUTE AS CALLER (the default) or EXECUTE AS OWNER. The mode is parsed, persisted, and shown by SHOW CREATE PROCEDURE.

EXECUTE AS CALLER — the body runs with the caller's identity and privileges (current_user, row-level security, data access, masking all fold to the caller). This is the default and the only mode the engine honors out of the box.
EXECUTE AS OWNER — owner's-rights execution is gated and off by default (a service-managed capability). When disabled, an EXECUTE AS OWNER procedure still runs with the caller's rights (which is never more privilege than the caller already has). When enabled, the engine re-scopes the body to the owner's identity and an owner token obtained via an operator-configured RFC 8693 token exchange (managed by Gnok). It is fail-closed: if no owner-identity provider is configured (or the exchange fails), the CALL errors rather than silently running with the caller's rights — owner-rights is never a bare identity flip.

Not yet supported

JavaScript / Python procedures (LANGUAGE JAVASCRIPT / PYTHON). Owner's-rights execution (EXECUTE AS OWNER) is parsed and persisted, but enforcing it requires an operator-provisioned identity provider and is off by default (see Caller's vs owner's rights). A literal :name inside a string literal in embedded SQL is currently still substituted — avoid that form.

Function Management

SHOW FUNCTIONS

List all registered functions with their metadata:

SHOW FUNCTIONS;

Returns: function_name, language, return_type, volatility, arg_types.

SHOW CREATE FUNCTION

Retrieve the full DDL statement for a function, including source code:

SHOW CREATE FUNCTION price_tier;

Returns a single ddl column containing the complete CREATE OR REPLACE FUNCTION statement. Use this to review, edit, and re-execute function definitions.

Editing a Function

To edit an existing function, use the read-modify-replace workflow:

-- 1. Retrieve current definition
SHOW CREATE FUNCTION price_tier;

-- 2. Copy the DDL output, modify the source, and execute:
CREATE OR REPLACE FUNCTION price_tier(price DOUBLE) RETURNS VARCHAR
LANGUAGE python AS $$
def price_tier(price):
    if price is None:
        return None
    if price < 75:       # changed from 50
        return 'budget'
    elif price < 250:    # changed from 200
        return 'mid-range'
    else:
        return 'premium'
$$;

CREATE OR REPLACE updates both the in-memory registry and the persisted catalog entry.

DROP FUNCTION

DROP FUNCTION py_double;
DROP FUNCTION IF EXISTS py_double;

DROP FUNCTION removes the function from the in-memory registry and deletes the catalog entry across all namespaces. The function will not be re-loaded on restart or by other coordinators.

AI/ML Objects

Gnok exposes ML primitives as first-class catalog objects: models, vector indexes, feature groups, and streaming inference / anomaly streams. Every object is durable (stored in the catalog) and scoped to your organization. The AI/ML overview page covers the runtime side; this section is the SQL reference.

CREATE MODEL

Register a model so it can be invoked from SQL like any scalar function. Three source forms, listed in order of preference:

-- 1. AS FROM STAGE — production. Pairs with the studio Upload
--    artifact button.
CREATE OR REPLACE MODEL fraud_detector(FLOAT, FLOAT, INT) RETURNS FLOAT
    USING FRAMEWORK 'onnx'
    AS FROM STAGE @stage/models/run123/fraud_v1.onnx;

-- 2. AS FROM FILE — for an object-storage URI the service can
--    read.
CREATE OR REPLACE MODEL fraud_detector(FLOAT, FLOAT, INT) RETURNS FLOAT
    USING FRAMEWORK 'onnx'
    AS FROM FILE 's3://my-bucket/models/fraud_v1.onnx';

-- 3. AS FROM '<base64>' — inline. Fine for tiny models + self-
--    contained tutorials. Painful past a few KB.
CREATE OR REPLACE MODEL fraud_detector(FLOAT, FLOAT, INT) RETURNS FLOAT
    AS FROM '<base64-encoded-model-bytes>';

See the ONNX inference page for the full studio-UI workflow.

Catalog WITH-clause form (legacy)

A WITH (artifact_uri = …, framework = …, …) form is also accepted for back-compatibility, but new code should prefer the AS FROM forms above.

CREATE MODEL fraud_detector
  WITH (
    framework = 'onnx',
    artifact_uri = 's3://models/fraud/fraud_v1.onnx',
    input_schema = '[{"name":"amount","type":"float64"},
                     {"name":"user_avg","type":"float64"}]',
    output_schema = '[{"name":"score","type":"float64"}]'
  );

USING FRAMEWORK clause

Default framework is onnx. Other frameworks need the matching image variant deployed:

-- PyTorch (TorchScript) — needs the gnok-pytorch image
CREATE MODEL fraud_pt(FLOAT, FLOAT, INT) RETURNS FLOAT
USING FRAMEWORK 'pytorch'
AS FROM '<base64-torchscript-bytes>';

-- sklearn (skops format) — needs the gnok-with-python image
CREATE MODEL fraud_sk(FLOAT, FLOAT, FLOAT) RETURNS FLOAT
USING FRAMEWORK 'sklearn'
AS FROM '<base64-skops-bytes>';

QUANTISATION clause

Declare quantisation at creation time. Recognised kinds: none (default), int8, int4, fp16. The kind propagates through the catalog row, the worker fragment payload, every inference's audit row, and per-kind metrics:

CREATE MODEL fraud_q(FLOAT, FLOAT, INT) RETURNS FLOAT
QUANTISATION 'int8'
AS FROM '<base64-onnx-bytes>';

-- Both clauses combine; FRAMEWORK comes after QUANTISATION:
CREATE MODEL m(FLOAT) RETURNS FLOAT
QUANTISATION 'int8' USING FRAMEWORK 'pytorch'
AS FROM '<base64-bytes>';

Clause	Description
`(arg1, arg2, …)`	Input types — must match the model's input tensor types.
`RETURNS <type>`	Output type. Float32 today; future versions may add Int64 / Bool.
`QUANTISATION '<kind>'`	none / int8 / int4 / fp16.
`USING FRAMEWORK '<name>'`	onnx (default) / pytorch / sklearn / gnok_native.
`framework` (WITH-clause form)	Same as USING FRAMEWORK.
`artifact_uri` (WITH-clause form)	S3 / HTTP URI of the model artifact.
`input_schema` / `output_schema` (WITH-clause form)	JSON-encoded Arrow schema.
`requires_gpu` (WITH-clause form)	`TRUE` to dispatch to GPU-eligible workers only.
`min_gpu_memory_mb` (WITH-clause form)	Minimum GPU memory the worker must advertise.

Remote models (external inference endpoints)

Register a model backed by an HTTP inference endpoint instead of a local artifact. The engine POSTs feature rows to the endpoint and maps the response back to the declared RETURNS type, so a remote model is callable as a scalar UDF exactly like a local one — handy for hosted model servers / LLM endpoints you don't want to co-locate with the workers.

CREATE REMOTE MODEL sentiment(review_text VARCHAR) RETURNS DOUBLE
    ENDPOINT 'https://models.example.com/score/sentiment';

CREATE OR REPLACE REMOTE MODEL churn(FLOAT, FLOAT, INT) RETURNS DOUBLE
    ENDPOINT 'https://model.example.com/v1/predict';

-- Invoke like any scalar model UDF:
SELECT sentiment(review_text) FROM reviews;

DROP MODEL IF EXISTS sentiment;

RETURNS must be a numeric type — FLOAT, DOUBLE, or INT (a VARCHAR return is rejected with "return type Utf8 not supported"). Input argument types may be any SQL type the endpoint accepts.

Trained-in-engine models

Beyond importing pre-trained ONNX / PyTorch / sklearn artifacts, Gnok trains models from a SQL query. Algorithm dispatch is via the TYPE '<algo>' clause; hyperparameters ride in OPTIONS (...).

-- KMeans clustering
CREATE MODEL clusters(FLOAT, FLOAT) RETURNS INT
  TYPE 'kmeans'
  OPTIONS (k = 5, max_iters = 100, tolerance = 0.001)
  AS SELECT
       CAST(longitude AS DOUBLE) AS x,
       CAST(latitude AS DOUBLE) AS y
     FROM stores;

-- Logistic regression — first column is the binary label (0.0 / 1.0)
CREATE MODEL fraud_classifier(DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
  TYPE 'logistic'
  OPTIONS (learning_rate = 0.5, max_iters = 200, tolerance = 1e-7)
  AS SELECT
       is_fraud::DOUBLE AS y,
       amount::DOUBLE AS x0,
       merchant_score::DOUBLE AS x1,
       hour_of_day::DOUBLE AS x2
     FROM transactions;

-- Linear regression — first column is the target
CREATE MODEL revenue_pred(DOUBLE, DOUBLE) RETURNS DOUBLE
  TYPE 'linear'
  OPTIONS (learning_rate = 0.05, max_iters = 2000, tolerance = 1e-9, l2_lambda = 0.1)
  AS SELECT
       revenue::DOUBLE AS y,
       ad_spend::DOUBLE AS x0,
       seasonality::DOUBLE AS x1
     FROM marketing_data;

-- AR(p) time-series forecasting — single Float64 column in chronological order
CREATE MODEL traffic_forecast(DOUBLE) RETURNS DOUBLE
  TYPE 'ar'
  OPTIONS (p = 7, forecast_horizon = 24)
  AS SELECT visits::DOUBLE
     FROM hourly_traffic
     ORDER BY ts;

-- After training, ML_PREDICT works as a scalar UDF:
SELECT clusters(longitude, latitude) AS cluster_id
FROM stores;

Algorithm	`TYPE` value	Required `OPTIONS`	Optional `OPTIONS`	Predict returns
K-Means	`'kmeans'`	`k = N`	`max_iters` (100), `tolerance` (1e-4), `init` ('kmeans++' / 'forgy', default kmeans++), `seed` (u64), `partitions` (1)	`INT32` cluster id
Logistic Regression	`'logistic'`	none	`learning_rate` (0.1), `l2_lambda` (0.0), `max_iters` (100), `tolerance` (1e-4), `partitions` (1)	`FLOAT64` probability ∈ [0, 1]
Multinomial Logistic	`'multinomial'`	`k = K` (classes)	`learning_rate` (0.1), `l2_lambda` (0.0), `max_iters` (100), `tolerance` (1e-4), `partitions` (1)	`INT32` argmax class id
Linear Regression	`'linear'`	none	`learning_rate` (0.01), `l2_lambda` (0.0), `max_iters` (100), `tolerance` (1e-4), `partitions` (1)	`FLOAT64` prediction
SARIMA(p, d, q)(P, D, Q)_s	`'ar'` / `'arima'`	`p = N` (AR order ≥ 1)	`d` (0), `q` (0), `seasonal_period` (0), `seasonal_d` (0), `seasonal_p` (0), `seasonal_q` (0), `forecast_horizon` (1)	`FLOAT64` h-step forecast (recursive, integrated back through differencing + seasonal differencing)
PCA	`'pca'`	none	`num_components` / `k` (min(D, 8)), `max_jacobi_sweeps` (30), `jacobi_tolerance` (1e-10), `partitions` (1)	`FLOAT64` (k=1) or `LIST<FLOAT64>` (k>1) projection per row
Decision Tree (CART classification)	`'decision_tree'` / `'tree'` / `'stump'`	none	`max_depth` (6, ≤ 32), `num_bins` (64), `min_samples_split` (2), `partitions` (1)	`INT32` class id in `[0, num_classes)`
Random Forest (classification)	`'random_forest'` / `'rf'` / `'forest'`	none	`num_trees` (100), `max_depth` (6), `num_bins` (64), `min_samples_split` (2), `feature_subsample_size` (sqrt(D)), `bootstrap_fraction` (1.0), `random_seed` (0), `partitions` (1)	`INT32` majority-vote class id
Gradient Boosting (classification)	`'gradient_boosting'` / `'gbm'` / `'gboost'`	none	`num_trees` (100), `learning_rate` (0.1, ∈ (0, 1]), `max_depth` (3 — small-tree default), `num_bins` (64), `min_samples_split` (2), `partitions` (1)	`INT32` class id in `[0, num_classes)`
MLP / DNN (regression, binary, multi-class)	`'dnn'` / `'mlp'` / `'neural_network'` / `'neural-network'`	none (`num_classes` required for `task='multiclass'`)	`task` (`regression` / `binary` / `multiclass`, default `regression`), `num_classes` (multiclass only, ≥ 2), `hidden_layers` (comma-separated unit counts, default `"16"`), `activation` (`relu` / `tanh` / `sigmoid`, default `relu`), `learning_rate` (0.05), `l2_lambda` (0.0), `max_iters` (100), `tolerance` (1e-4), `seed` (u64), `partitions` (1)	`FLOAT64` (regression) / `FLOAT64` probability (binary) / `INT32` argmax (multiclass)

The partitions = N option (default 1) splits materialized training batches across coordinator-local threads. Worker RPC dispatch requires service enablement and sufficient capacity; failures can fall back to local training. The full input still resides on the coordinator. Floating-point reduction order can change results, so neither proportional speedup nor bit equality across partition counts is guaranteed. See distributed training.

ARIMA is intentionally excluded from partitions — single-series time series can't be split at batch granularity without disrupting the lag structure. Multi-series sharding via per-series reducers is the right path; not yet wired.

Example:

-- Train an 8-cluster KMeans with 8-way coordinator parallelism
CREATE MODEL fast_kmeans(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS INT
  TYPE 'kmeans'
  OPTIONS (k = 8, max_iters = 100, partitions = 8)
  AS SELECT f0, f1, f2, f3 FROM features;

The model signature lists features only; exclude the training label from its argument count. Supported numeric and Boolean training columns are normalized to DOUBLE; encode categorical strings explicitly.

For the supervised algorithms (Logistic, Multinomial, Linear), the SELECT projects [label, x0, x1, …, xN] — column 0 is the target / label (Float64; integer for Multinomial in [0, K)), columns 1..N are the features. ARIMA / SARIMA projects a single Float64 column in chronological order — ordering is the caller's responsibility (use ORDER BY <ts>). At inference, supply an explicit horizon: traffic_forecast(1) predicts the next step and traffic_forecast(7) predicts seven steps ahead. Set seasonal_period (e.g. 7 for weekly, 12 for monthly) plus any of seasonal_d / seasonal_p / seasonal_q (the (P, D, Q) seasonal triplet from Box–Jenkins) to handle seasonal patterns. The full SARIMA(p, d, q)(P, D, Q)_s design matrix in the Hannan–Rissanen Stage 2 OLS adds P seasonal AR-lag columns at multiples of seasonal_period and Q seasonal MA-residual columns; forecast applies all four streams (regular AR + seasonal AR + regular MA

seasonal MA). PCA is unsupervised — projects [x0, x1, …, xN] only.

-- Weekly seasonality with mixed seasonal AR + MA
CREATE MODEL weekly_traffic_forecast(DOUBLE) RETURNS DOUBLE
  TYPE 'arima'
  OPTIONS (
    p = 1, d = 0, q = 1,
    seasonal_period = 7,
    seasonal_p = 1, seasonal_d = 1, seasonal_q = 1,
    forecast_horizon = 14
  )
  AS SELECT visits FROM daily_traffic ORDER BY day;

The tree-based classifiers (Decision Tree, Random Forest, Gradient Boosting) all share the supervised [label, x0, x1, ..., xN] training-query shape — column 0 is an integer class label in [0, num_classes), normalized to Float64; num_classes defaults to 2. Columns 1..N are numeric features, also normalized to Float64. Random Forest's feature_subsample_size defaults to floor(sqrt(D)) (Breiman's recipe); on small D set it explicitly to avoid the ensemble being limited to one feature per tree. Gradient Boosting uses max_depth = 3 by default — the canonical small-tree gradient-boosting setting; raise for high-dimensional features.

The MLP / DNN trainer accepts the same [label_or_target, x0, x1, …, xN] shape across all three tasks. task='regression' predicts a scalar continuous target. task='binary' expects a 0.0 / 1.0 label and returns a sigmoid-activated FLOAT64 probability (threshold yourself if you want a hard 0/1). task='multiclass' expects a non-negative integer class id in [0, num_classes) and returns the INT32 argmax over the softmax output. Architecture is controlled by hidden_layers (one comma-separated number per hidden layer — "16,8" for a 2-hidden-layer network), with per-row input dimensionality inferred from the SELECT projection. The full algorithm description, training-loop semantics, and worked examples for each task live on the dedicated DNN / MLP page.

-- Gradient-boosted classifier on a 4-feature problem
CREATE MODEL fraud_classifier(DOUBLE, DOUBLE, DOUBLE, DOUBLE) RETURNS INT
  TYPE 'gradient_boosting'
  OPTIONS (num_trees = 100, learning_rate = 0.1, max_depth = 3)
  AS SELECT label, amount, account_age, txn_count, hour FROM tx_features;

When PCA is created with k > 1, the UDF returns a List<FLOAT64> whose elements are the projections onto each principal component in descending-variance order. SQL callers index the list with [N] (1-based) to access individual components:

-- All k components per row
SELECT pca_model(x0, x1, x2) AS components FROM features;

-- First two components individually
SELECT pca_model(x0, x1, x2)[1] AS pc1,
       pca_model(x0, x1, x2)[2] AS pc2
FROM features;

The training query is bound + analysed + executed through the same SQL pipeline as a regular SELECT. The model trains over the result batches, the trained parameters serialise to S3 via the catalog's artifact storage, the catalog row lands with framework = GNOK_NATIVE and model_type = '<algo>', the training run records lineage in ml_model_lineage (training query text, hyperparameters, evaluation metrics), and the resulting UDF registers locally + propagates to peer coordinators (via the metadata-changes notification) and workers (via the fragment payload's native_models field).

The training-row cap (default 1M rows) bounds materialized training rows, including for partitioned or worker-dispatched training. The cap is service-managed; capacity changes require coordination with Gnok. Reduce the training set or pre-aggregate when appropriate.

ML.TUNE — hyperparameter search

Grid-search hyperparameters for a trained-in-engine algorithm. Same name(arg_types) RETURNS <type> TYPE '<algo>' shape as a trained-in-engine CREATE MODEL, plus a GRID (...) of value lists and a required AS <SELECT> training query. The engine trains one variant per grid combination and returns them ranked by validation metric — it does not register a model, so feed the winning combo into a CREATE MODEL.

ML.TUNE churn (DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
  TYPE 'gradient_boosting'
  GRID (max_depth = [3, 5, 7], learning_rate = [0.1, 0.3])
  AS SELECT label, amount, account_age, txn_count FROM tx_features;

Returns one row per grid combination:

Column	Description
`rank`	1 = best by validation metric
`variant_name`	`<name>__variant_<i>`
`combo`	the grid combination, e.g. `max_depth=3, learning_rate=0.1`
`iterations` / `converged` / `total_samples` / `wall_ms`	per-variant training stats
`val_metric_name` / `val_metric`	validation metric name + value
`error`	non-NULL if that variant failed to train

CREATE MODEL VERSION

Add a new version of an existing model — the prior version stays ACTIVE until you flip it. The same DDL surface works for both imported artifact models (ONNX, PyTorch, sklearn) and trained-in-engine native algorithms (KMeans, Logistic, Linear, Multinomial, ARIMA, PCA, Decision Tree, Random Forest, Gradient Boosting, DNN).

ONNX / artifact form — supply a new artifact URI:

CREATE MODEL VERSION 'v2' OF fraud_detector
  WITH artifact_uri = 's3://models/fraud/fraud_v2.onnx';

Trained-in-engine form — re-train against fresh data with the same algorithm, hyperparameters that may have changed, and the same input/output signature as the base version:

CREATE MODEL fraud_detector(FLOAT, FLOAT, FLOAT) VERSION 'v2'
  RETURNS FLOAT TYPE 'logistic'
  OPTIONS (learning_rate = 0.5, max_iters = 200, tolerance = 1e-7)
  AS SELECT is_fraud::DOUBLE,
            amount::DOUBLE,
            merchant_score::DOUBLE,
            hour_of_day::DOUBLE
     FROM transactions
     WHERE created_at >= now() - INTERVAL '90 days';

In both cases:

The base version (the one created via CREATE MODEL …) must exist; otherwise the call returns model '<name>' has no base version.
The new version is uploaded as a fresh artifact, registered in the catalog with framework = GnokNative (for trained-in-engine) or the original framework (for imported), and stamped with metadata.requested_version = '<v>' for traceability.
A lineage row is written to ml_model_lineage with the training query text, hyperparameters, and evaluation metrics.
The active version pointer is not changed — live SQL traffic keeps hitting the prior version. Operators promote via ALTER MODEL <name> ACTIVATE VERSION 'vN'.

OR REPLACE and VERSION are mutually exclusive — the engine rejects CREATE OR REPLACE MODEL … VERSION 'vN' at parse time because the two operations have opposite intent (replace = swap in place; version = additive staging).

ALTER MODEL ACTIVATE VERSION

ALTER MODEL fraud_detector ACTIVATE VERSION 'v2';
ALTER MODEL fraud_detector SET REQUIRES_GPU = TRUE MEMORY 4096;
ALTER MODEL fraud_detector ENABLE ONLINE LEARNING FROM feedback_table;
ALTER MODEL fraud_detector DISABLE ONLINE LEARNING;
ALTER MODEL fraud_detector CANCEL TRAINING;   -- abort an in-flight training run

ALTER MODEL SET QUANTISATION

Switch a model's declared quantisation kind after creation. The catalog row's metadata.quantisation field is shallow-merged via PATCH /v1/models/{name}/metadata, so the change propagates to peer coordinators through the existing ml_metadata_changes event stream:

ALTER MODEL fraud_detector SET QUANTISATION 'int8';
ALTER MODEL fraud_detector SET QUANTISATION 'fp16';
ALTER MODEL fraud_detector SET QUANTISATION 'none';  -- back to full precision

ALTER MODEL SET STATUS

Transition a model's lifecycle state. Status values follow the catalog state machine — illegal transitions return 409:

ALTER MODEL clusters SET STATUS 'STAGED';      -- pre-promotion
ALTER MODEL clusters SET STATUS 'ACTIVE';      -- production-serving
ALTER MODEL clusters SET STATUS 'ARCHIVED';    -- retired (kill-switch)
ALTER MODEL clusters SET STATUS 'DEPRECATED';  -- still callable, warn

ARCHIVED and DEPRECATED immediately deregister the local UDF on the originating coordinator and propagate to peer coordinators via the ml_metadata_changes notification — subsequent ML_PREDICT calls fail cleanly with "function not found" rather than executing against a model the operator has retired.

DELETED is reached only via DROP MODEL (terminal).

ALTER MODEL ENABLE SLICE

Register a slice predicate against the model's SlicedRegressionDetector — a slice can rollback independently of the aggregate signal:

ALTER MODEL fraud_detector ENABLE SLICE 'high_value'
  WHERE amount > 1000;
ALTER MODEL fraud_detector DISABLE SLICE 'high_value';

EXPLAIN MODEL

EXPLAIN MODEL fraud_detector;
SHOW MODEL VERSIONS fraud_detector;
SHOW MODELS;

EVALUATE MODEL

Run a registered model against a held-out dataset, return per-metric rows, and persist them to ml_model_evaluations for cross-coordinator audit. The dataset query must produce the same input columns the model expects, with CAST(... AS DOUBLE) on any non-double columns for trained-in-engine models. The optional VERSION n clause scopes the evaluation to a specific version (defaults to the active one).

EVALUATE MODEL fraud_detector
ON SELECT
    CAST(is_fraud AS DOUBLE) AS label,
    amount, merchant_risk, hour_of_day
FROM transactions_validation;

EVALUATE MODEL fraud_detector VERSION 2
ON SELECT label, amount, merchant_risk FROM transactions_validation;

The result is one row per metric. Operational metrics (row_count, latency_ms, output_mean, output_stddev) are always emitted. Supervised metrics (rmse, mae, accuracy, precision, recall, f1, macro_precision, macro_recall, macro_f1, num_classes) are emitted when the dataset query labels its label/prediction columns by the conventional names.

Each evaluation persists to the catalog under the JWT-scoped tenant. NaN-valued metrics are normalized to NULL so downstream AVG() / MIN() / MAX() aggregations skip them.

SHOW MODEL EVALUATIONS

Surface persisted evaluation rows for audit:

SHOW MODEL EVALUATIONS;                       -- recent rows for the tenant
SHOW MODEL EVALUATIONS FOR fraud_detector;    -- filter to one model

Returns model_name, version, metric, value, evaluated_at, and the truncated dataset_ref (the literal SQL the user wrote, capped at 80 chars in the display surface; the full text is in the catalog row). Most-recent rows first; defaults to 200 rows, capped at 1000.

Model introspection

SHOW MODEL LINEAGE fraud_detector;            -- training query + hyperparams + metrics per run
SHOW MODEL LINEAGE fraud_detector VERSION 2;  -- scope to one version
SHOW MODEL SLICES fraud_detector;             -- registered slice predicates + per-slice health
SHOW FEATURE IMPORTANCE FOR MODEL fraud_detector;
SHOW AB EXPERIMENTS;                          -- champion/challenger routing config

SHOW MODEL LINEAGE — one row per recorded training run from ml_model_lineage (training query text, hyperparameters, evaluation metrics). Requires the model to have an active version.
SHOW MODEL SLICES — model_name, slice_id, predicate_sql, state, sample_count, ml_disabled, rollback_count, recent_ml_error_ratio, recent_ml_win_rate (one row per slice registered via ALTER MODEL … ENABLE SLICE).
SHOW FEATURE IMPORTANCE FOR MODEL — feature_name, importance for algorithms that expose it (e.g. the tree ensembles).
SHOW AB EXPERIMENTS — model_name, experiment_name, champion_label, variants, min_samples_per_variant, win_rate_margin.

DROP MODEL

DROP MODEL fraud_detector;
DROP MODEL IF EXISTS fraud_detector;

CREATE VECTOR INDEX

Build an HNSW or IVF index over a vector column for sub-linear ANN search:

CREATE VECTOR INDEX docs_embedding_idx ON documents(embedding)
  USING HNSW WITH (
    m = 16,
    ef_construction = 200,
    ef_search = 100,
    metric = 'cosine'
  );

-- Idempotent rebuild — drops the existing index (catalog row + S3
-- artifact) before re-creating with the new spec. Mutually
-- exclusive with IF NOT EXISTS.
CREATE OR REPLACE VECTOR INDEX docs_embedding_idx
  ON documents(embedding)
  USING HNSW WITH (m = 32, ef_construction = 400);

Parameter	Description
`m`, `ef_construction`, `ef_search`	HNSW recall/latency knobs
`metric`	`l2` (default), `cosine`, `inner_product`, `hamming`
`nlist`, `nprobe`	IVF parameters (when `USING IVF`)

The index is sharded across workers automatically; coordinator fan-out probes shards in parallel and merges top-k results.

Declare which columns participate in inline filtering during HNSW traversal. When a query's predicate references only declared attribute columns, the planner picks IndexFilter strategy — strictly faster than PreFilter or PostFilter because non-matching candidates are dropped during graph traversal:

CREATE VECTOR INDEX product_embeds
ON products(embedding)
USING HNSW
WITH (
  m = 16,
  ef_construction = 200,
  attribute_filters = ('category', 'in_stock', 'tier')
);

-- Picks IndexFilter (all predicate columns are indexed)
SELECT * FROM products
WHERE category = 'electronics' AND in_stock = TRUE
ORDER BY l2_distance(embedding, $query) LIMIT 10;

-- Picks PreFilter (predicate column 'sku_prefix' is not in attribute_filters)
SELECT * FROM products
WHERE sku_prefix LIKE 'X%'
ORDER BY l2_distance(embedding, $query) LIMIT 10;

ALTER VECTOR INDEX SET ef_search

Persist a chosen ef_search value durably in the catalog. Set this from the recall-sweep page's "Set as default" button or manually:

ALTER VECTOR INDEX product_embeds SET ef_search = 128;

The value is shallow-merged into the catalog's parameters JSONB via PATCH /v1/indexes/vector/{id}/parameters, so it survives restart and propagates to peers via the metadata subscriber.

WARMUP VECTOR INDEX

Pre-populate worker shard caches before traffic hits a fresh index. Useful right after a rebuild when ef-warmup costs are otherwise paid by the first probe:

WARMUP VECTOR INDEX product_embeds;

Each worker reports back per-shard outcomes (warmed / already cached / errored). The same operation is available via POST /api/vector-index/{id}/warmup — the Studio Vector Indexes page's per-row Warm button uses the HTTP form.

Both paths emit:

gnok_vector_warmup_calls_total{trigger=ddl|http_api}
gnok_vector_warmup_shards_total{outcome=warmed|cached|errored}
gnok_vector_warmup_latency_ms histogram

Managing indexes

SHOW VECTOR INDEXES;
DROP VECTOR INDEX docs_embedding_idx;

See Vector Operations for the strategy selector (IndexOnly / PostFilter / PreFilter) the planner uses when a query combines ANN with WHERE clauses.

Text Indexes

Build a BM25 full-text index over a text column for ranked keyword search. The full guide — tokenizers, MATCH / bm25_score() queries, scoring internals — lives in Text Search; the SQL surface is:

-- Build (or idempotently rebuild) a BM25 index over a text column
CREATE TEXT INDEX docs_body_idx ON documents(body);
CREATE OR REPLACE TEXT INDEX docs_body_idx ON documents(body);

REFRESH TEXT INDEX docs_body_idx;   -- force a rebuild (commits auto-refresh by default)
DROP TEXT INDEX docs_body_idx;
SHOW TEXT INDEXES;                  -- one row per index for the tenant

SHOW TEXT INDEXES returns:

Column	Description
`name`	Index name
`table`	Indexed table
`column`	Indexed text column
`doc_count`	Number of indexed documents
`avgdl`	Average document length (BM25 length normalization)
`tokenizer`	Tokenizer used to build the index
`last_built_at`	Timestamp of the last build / refresh

See Text Search for query syntax and scoring details.

CREATE FEATURE GROUP

Define a feature group materialized from a SQL query, optionally backed by an Iceberg table for point-in-time training joins:

CREATE FEATURE GROUP user_features (
    user_id BIGINT PRIMARY KEY
)
MATERIALIZED FROM (
    SELECT user_id,
           AVG(amount) AS avg_amount,
           COUNT(*)    AS tx_count
    FROM transactions
    GROUP BY user_id
)
REFRESH EVERY 300
OFFLINE TABLE warehouse.feature_store.user_features;

Clause	Description
`PRIMARY KEY`	One or more key columns; hash-indexed for O(1) point lookup
`MATERIALIZED FROM`	Source SQL — must return key columns plus feature columns
`REFRESH EVERY <secs>`	Materialization cadence
`OFFLINE TABLE <qualified_name>`	(optional) Persist each refresh to an Iceberg table for time-travel reads

ALTER FEATURE GROUP

ALTER FEATURE GROUP user_features ADD COLUMN session_count BIGINT;
REFRESH FEATURE GROUP user_features;
SHOW FEATURE GROUPS;
DROP FEATURE GROUP user_features;

See Feature Store for the FEATURE_LOOKUP and FEATURE_LOOKUP_AS_OF UDFs that consume these objects.

BACKFILL FEATURE GROUP

Materialise the feature group's source SQL over the historical windowed range — used to seed a freshly-created group from existing data rather than waiting for the next refresh tick:

BACKFILL FEATURE GROUP user_features;

The backfill orchestrator walks the source query in chunks (default 1M rows per chunk, configurable). A per-group catalog lease (backfill_<group>) ensures two coordinators receiving concurrent backfill requests for the same group serialise — the second sees status = 'already_running' rather than producing duplicate rows.

Per-chunk retry policy (default 3 retries, 100ms→5s exponential backoff) survives transient catalog / S3 hiccups; the cancel flag is checked at the top of every retry attempt.

Studio's /admin/feature-groups page exposes a per-row Backfill button that runs this DDL.

SHOW BACKFILL PROGRESS

Track in-flight and completed backfills:

SHOW BACKFILL PROGRESS;                 -- all groups
SHOW BACKFILL PROGRESS user_features;   -- one group

Returns group, total_chunks, completed_chunks, failed_chunks, and status — one row per group with a recorded backfill run.

SHOW FEATURE DRIFT

Surface drift reports for any group whose materializer detects distribution shift between successive refreshes:

SHOW FEATURE DRIFT;
SHOW FEATURE DRIFT WHERE feature_group = 'user_features';

Returns one row per detected drift event with the affected group, column, drift score, and timestamp. The drift detector is fed by the feature-drift loop (lease-elected; spawns once per cluster).

CREATE STREAM (ML inference + anomaly detection)

Streaming inference applies a model to every batch ingested into a source stream:

CREATE STREAM fraud_alerts AS
  SELECT user_id,
         amount,
         ML_PREDICT('fraud_detector', amount, user_avg_amount) AS score
  FROM   live_transactions
  WHERE  ML_PREDICT('fraud_detector', amount, user_avg_amount) > 0.85;

Shorthand: CREATE STREAM ... WITH MODEL

For the common case — apply one model to a list of input columns and emit the score alongside — there is a brevity-oriented form that desugars to the same CREATE STREAM <name> AS SELECT ML_PREDICT(...) FROM <source> AST:

CREATE STREAM <stream_name> [IF NOT EXISTS]
  WITH MODEL <model_name>
  USING (col1, col2, ...)
  [AS <output_column>]
  FROM <source_stream>;

Clause	Description
`WITH MODEL <model_name>`	Bare identifier of a registered model.
`USING (col1, col2, …)`	At least one feature column from the source stream. Order matches the model's positional inputs.
`AS <output_column>`	Optional alias for the score column. Defaults to `prediction`.
`FROM <source_stream>`	Source stream to consume from.

-- Equivalent of the long form above
CREATE STREAM fraud_alerts
  WITH MODEL fraud_detector
  USING (amount, user_avg_amount)
  AS score
  FROM live_transactions;

-- Idempotent variant
CREATE STREAM IF NOT EXISTS fraud_alerts
  WITH MODEL fraud_detector
  USING (amount, user_avg_amount)
  FROM live_transactions;

The shorthand desugars at parse time to the same CreateMlStream AST variant the long form produces, so behaviour, observability, and audit-recorder integration are identical. Use the long form when you need a WHERE filter or extra projected columns alongside the score.

Streaming anomaly detection runs a per-stream detector (zscore, iqr, isolation_forest) on the configured column:

CREATE STREAM temperature_anomalies AS
  DETECT ANOMALIES ON sensor_readings(reading_celsius)
  USING METHOD = 'zscore'
  WINDOW '5 minutes';

Ensemble detector

Combine multiple detector types with a configurable consensus rule. Reduces false positives because each base type has a different failure mode (ZScore is fooled by skewed distributions, IQR is fooled by multi-modal, IsolationForest needs more data). A row is flagged anomalous only when the rule's threshold is met:

-- Canonical 3-base ensemble (zscore + iqr + isolation_forest)
-- Consensus options: majority | unanimous | any
CREATE STREAM alerts AS
  DETECT ANOMALIES ON clicks(velocity)
  USING METHOD 'ensemble:majority'
  WINDOW '1 minute';

-- Custom base list (must be at least one)
CREATE STREAM alerts2 AS
  DETECT ANOMALIES ON clicks(velocity)
  USING METHOD 'ensemble:unanimous:zscore,iqr'
  WINDOW '1 minute';

Consensus	Behaviour
`majority`	Strictly more than half of the base detectors must flag. With N=3, that's ≥2.
`unanimous`	Every base detector must flag. Lowest false-positive rate.
`any`	At least one base detector flags. Use for safety-critical signals.

Recognised base detectors: zscore (alias ema), iqr, isolation_forest. Empty base lists are rejected at parse time.

SHOW STREAMS;
DROP STREAM fraud_alerts;

Autonomous AutoML

Your organization's autonomous AutoML mode is managed by Gnok (see the AutoML Advisors page). The modes are disabled, recommend_only, auto_low_risk, and full_auto; to change your organization's mode, email support@gnok.io. The autonomous loop's recommendations appear in the automl_runs catalog table and the Studio AutoML Control page.

-- List runs, optionally filtered by advisor
SHOW AUTOML RUNS;
SHOW AUTOML RUNS WHERE advisor_kind = 'mv_creation' LIMIT 50;
SHOW AUTOML RUNS WHERE advisor_kind = 'compaction_cadence' LIMIT 50;

-- Surface the join-order advisor's pinned recommendations
SHOW JOIN ORDER RECOMMENDATIONS;

-- Surface your organization's fast-path selector state
SHOW FAST PATH SELECTOR;

Ask the file-layout advisor whether a table would benefit from compaction, given its current small-file count, delete ratio, and manifest fan-out. Read-only — it never rewrites data; it emits a ready-to-run OPTIMIZE hint.

-- Single-table compaction advice
RECOMMEND COMPACTION FOR analytics.crm.events;

Column	Description
`table`	Fully-qualified table the advice applies to
`recommendation`	Verdict: `compact` or `skip`
`reason`	Human-readable justification (small files, delete ratio, manifest fan-out)
`confidence`	Advisor confidence in `[0,1]`
`priority_score`	Relative urgency vs other tables (higher = sooner)
`sql_hint`	Ready-to-run `OPTIMIZE` / `REWRITE` statement

SHOW LEARNED OPTIMIZER

Inspect the self-tuning optimizer's per-shard learned state — the cardinality estimator, cost tuner, MLP, and regression guard that adapt plans from observed execution feedback. One row per shard.

SHOW LEARNED OPTIMIZER;

23 columns; the key ones:

Column	Description
`shard`	Optimizer shard identifier
`enabled` / `ml_active`	Whether learning is on and currently driving plans
`query_count`	Queries observed by this shard
`cardinality_samples` / `cardinality_patterns` / `cardinality_predictions` / `cardinality_avg_confidence`	Cardinality-estimator learning state and live confidence
`cost_tuner_samples` / `cpu_multiplier` / `io_multiplier`	Cost-model tuner samples and current CPU/IO calibration factors
`mlp_enabled` / `mlp_predictions` / `mlp_version`	Neural cardinality MLP status, prediction count, version handle
`regression_state` / `rollbacks` / `consecutive_errors`	Regression-guard health, auto-rollback count, error streak
`kill_switch_enabled`	Hard disable flag for the learned path
`ml_win_rate` / `ml_error_ratio`	Plan-quality win rate vs error ratio
`last_retrained_at`	Timestamp of the last MLP retrain
`plan_selector_patterns` / `plan_selector_executions`	Plan-selector pattern cache size and execution count

SHOW MV RECOMMENDATIONS

Surface materialized-view candidates mined from observed query patterns. The advisor clusters recurring SELECT shapes by their referenced tables and filter columns and scores each by how much repeated work a precomputed view would eliminate.

SHOW MV RECOMMENDATIONS;

Column	Description
`fingerprint`	Stable hash of the recurring query shape
`tables`	Tables referenced by the candidate
`filter_columns`	Filter/group columns that define the candidate
`repeated_count`	Times this shape was observed
`savings_score`	Estimated work eliminated by materializing (higher = better)
`last_seen`	Timestamp of the most recent matching query
`sql_hint`	Ready-to-run `CREATE MATERIALIZED VIEW` statement

SUGGEST QUERIES

Ask the LLM to propose example SELECT queries for the active schema. Useful for onboarding new users to a dataset and for populating sample-query panels in Studio.

SUGGEST QUERIES
  [ABOUT '<topic>']
  [LIMIT N]
  [WITH SCOPE <catalog>.<schema>];

Returns one row per accepted suggestion with columns (rank INT4, title VARCHAR, description VARCHAR, sql VARCHAR). Default LIMIT is 5; the hard cap is 20. Each sql value is validated through the same SELECT-only single-statement check as ASK — invalid suggestions are dropped silently.

-- Use the session catalog/schema
USE CATALOG analytics; USE SCHEMA crm;
SUGGEST QUERIES ABOUT 'customer churn';

-- Override the scope inline
SUGGEST QUERIES LIMIT 10 WITH SCOPE analytics.crm;

SUGGEST QUERIES reuses the NLI provider/model and the service-managed schema-size limit documented on the Natural Language Queries page.

Natural Language (ASK)

Run a plain-English question against the active catalog/schema. ASK performs grounded NL2SQL — the LLM is given the relevant table/column metadata, emits a single SELECT, and Gnok executes it and returns a normal result set. Works over /api/query and pgwire. See the Natural Language Queries guide for the full workflow (grounding, RAG threshold, provider config).

ASK

-- One-shot question against the session catalog/schema
ASK 'how many tables are there?';
-- table_count
-- -----------
--          12

The result columns/types are whatever the generated SELECT produces — ASK is just a SELECT with the SQL written for you, so its output is a regular row set you can read like any query.

BEGIN CONVERSATION

Open a multi-turn session so follow-up questions thread prior context.

-- Optionally pin the grounding scope for the whole conversation
BEGIN CONVERSATION WITH SCOPE analytics.crm;

-- Or use the session's current catalog/schema
BEGIN CONVERSATION;

Returns one row:

Column	Description
`session_id`	Conversation handle to pass to `ASK ... IN` and `END CONVERSATION`
`persisted`	`TRUE` if the conversation is catalog-backed (survives reconnect)

ASK ... IN

Ask a follow-up inside an open conversation; prior turns are threaded into the grounding so pronouns / references resolve.

BEGIN CONVERSATION WITH SCOPE analytics.crm;       -- -> session_id 'a1b2c3...'

ASK 'how many customers signed up last month?' IN 'a1b2c3...';
ASK 'and how many of those churned?'            IN 'a1b2c3...';  -- "those" = prior turn

END CONVERSATION

Close a conversation and release its state.

END CONVERSATION 'a1b2c3...';
-- ended
-- -----
--  true

Returns one row:

Column	Description
`ended`	`TRUE` once the conversation handle is closed

Stages

A stage is an upload area for artifacts — data files for COPY INTO, ONNX models for CREATE MODEL ... AS FROM STAGE, and similar. Stage references look like @stage/<name>/<run_id>/<filename>.

Uploads happen via presigned URLs (see HTTP API → stages). Once a file is in the stage, you reference it from SQL.

LIST

List files under a stage prefix. Recursive.

LIST @stage/models/;
-- name                          | size | last_modified
-- e2e-test/fraud_demo.onnx      |  235 | 2026-05-12 21:32:18+00
-- vend-test/fraud_demo.onnx     |  235 | 2026-05-12 22:24:22+00

LIST @stage/models/vend-test/;
-- narrows to one specific run

LIST @stage/uploads/;
-- everything you've staged for COPY INTO

Output schema

Column	Type	Notes
`name`	`VARCHAR`	Stage-relative path (no tenant-id leakage)
`size`	`BIGINT`	Object size in bytes
`last_modified`	`TIMESTAMP WITH TIME ZONE`	UTC, microsecond precision

Known limitations

The bare LIST @stage/ (no path) is rejected by the catalog — list a known subprefix (e.g. @stage/models/, @stage/uploads/).
LIST is currently a top-level statement, not a table function — you can't SELECT … FROM LIST @stage/… or join against its output. The output is a regular row set, just emitted by a dedicated statement.
No PATTERN='.*\.onnx' regex filtering yet (planned).

Row-Level Security Policies

Row-level security (RLS) policies restrict which rows a role can see. Policies are attached to tables and automatically filter query results based on the current user's role.

-- Create a permissive RLS policy
CREATE POLICY dept_filter ON salaries
  AS PERMISSIVE
  FOR SELECT
  TO analyst
  USING (dept = current_role());

-- Drop a policy
DROP POLICY dept_filter ON salaries;
DROP POLICY IF EXISTS dept_filter ON salaries;

See also: Security for RBAC and data masking.

Data Masking

Column-level data masking replaces sensitive values with masked versions at query time. Policy definition and column attachment are separate steps, which lets one policy be reused across many columns.

CREATE [OR REPLACE] MASKING POLICY

CREATE [OR REPLACE] MASKING POLICY <name>
    AS (<input_type>) RETURNS <return_type>
    -> <expression>;

Inside the expression, $value is the placeholder for the column being masked — the rewriter substitutes the actual column name at query time.

CREATE MASKING POLICY policy_email AS (STRING) RETURNS STRING
    -> concat(left($value, 2), '***@', split_part($value, '@', 2));

CREATE MASKING POLICY policy_cc AS (STRING) RETURNS STRING
    -> concat('****-****-****-', right($value, 4));

CREATE MASKING POLICY policy_redact AS (STRING) RETURNS STRING -> '[REDACTED]';
CREATE MASKING POLICY policy_null   AS (STRING) RETURNS STRING -> NULL;

CREATE OR REPLACE updates the policy body in place — every existing column attachment is preserved and the next query picks up the new expression. Without OR REPLACE, a second CREATE with the same name errors with Policy already exists: <name>.

Policy names follow Postgres identifier rules: unquoted → lowercase, double-quoted → preserve case. CREATE MASKING POLICY p_x and CREATE MASKING POLICY P_X both create the same object (the second errors as a duplicate); CREATE MASKING POLICY "P_x" creates a distinct, case-preserved object.

ALTER TABLE … SET / UNSET MASKING POLICY

ALTER TABLE <table>
    ALTER COLUMN <column>
    SET MASKING POLICY <policy_name>
    [ EXEMPT <role1>, <role2>, ... ];

ALTER TABLE <table>
    ALTER COLUMN <column>
    UNSET MASKING POLICY;

The EXEMPT clause names roles that bypass the policy — members of those roles see the raw column value. Role names are preserved as-written.

Policy-name lookup in SET MASKING POLICY is case-insensitive.

DROP MASKING POLICY

DROP MASKING POLICY [IF EXISTS] <name>;

Refuses when the policy is still attached to any column (Cannot drop policy '<n>': still in use by columns: ...). Detach with ALTER TABLE ... UNSET MASKING POLICY first, or use CREATE OR REPLACE to swap the body without detaching.

Supported expression kinds

The policy body is parsed at query-rewrite time into a logical expression tree. Supported node kinds:

Literals (string / int / float / bool / NULL)
Column references ($value or another bare column name)
Function calls (nullary, unary, n-ary; standard scalar functions)
Binary operators (comparison / logical / arithmetic / || concat)
Unary operators (NOT, unary - / +)
CASE WHEN ... THEN ... ELSE ... END (searched and simple)
CAST / TRY_CAST
IS NULL / IS NOT NULL
IN (<literal list>)
POSITION(x IN y)
Parenthesized expressions

Not supported: EXISTS / scalar subqueries, ANY / ALL / SOME, LIKE / ILIKE / SIMILAR TO / RLIKE, BETWEEN, window functions, array / map / struct literals. Use the function-call equivalents where available (e.g. regexp_replace instead of SIMILAR TO).

See Data Masking for the full semantics ($value substitution, query-time behavior, EXEMPT roles, leading-comment handling, and a complete end-to-end example).

Users, service accounts, and PATs

User and service-account lifecycle is managed with native DDL:

CREATE USER alice EMAIL = 'alice@ex.com' PASSWORD = 'Initial_1!';
ALTER USER alice SET MUST_CHANGE_PASSWORD = TRUE;
DROP USER IF EXISTS alice CASCADE;

CREATE SERVICE ACCOUNT etl_svc DEFAULT_ROLE = ingestion;
ALTER SERVICE ACCOUNT etl_svc ADD KEYPAIR
    KEY_ID 'kp-1' PUBLIC_KEY '<PEM>' ALGORITHM RS256;
ALTER SERVICE ACCOUNT etl_svc DROP KEYPAIR KEY_ID 'kp-1';

CREATE PERSONAL ACCESS TOKEN laptop TTL '30 days' SCOPE 'read write';
SHOW PERSONAL ACCESS TOKENS;

GRANT MANAGE USERS TO USER alice;

ALTER USER SELF

Self-service statements act on the caller's own principal (resolved from the session token), so no target username is named. They mutate the live auth account; treat the forms below as syntax.

-- Rotate the caller's own password (verifies the old hash before writing).
ALTER USER SELF SET PASSWORD '<new_password>' OLD PASSWORD '<old_password>';

-- TOTP MFA self-service.
ALTER USER SELF ENROLL MFA TOTP;                  -- returns (secret, otpauth_uri)
ALTER USER SELF VERIFY MFA TOTP '<6-digit-code>'; -- flips totp_enabled = true
ALTER USER SELF DISABLE MFA WITH TOTP CODE '<6-digit-code>';

ENROLL returns one row to seed the authenticator app:

Column	Description
`secret`	Base32 TOTP shared secret
`otpauth_uri`	`otpauth://` provisioning URI (encode as a QR code)

VERIFY and DISABLE both require a live 6-digit code — there is no unauthenticated path. See User Lifecycle for the full pairing flow.

The full surface — CREATE/ALTER/DROP USER, service accounts + keypairs, PATs, MFA, invitations, legal hold, EXPUNGE, webhooks, and the delegable MANAGE privileges — is documented in User Lifecycle.

Roles

Roles group privileges for access control. Gnok implements a two-tier model: tenant roles (granted to users) and catalog roles (scoped to one catalog, only inheritable by tenant roles or other catalog roles in the same catalog). See RBAC for the full model, effective-role resolution, ownership, and propagation semantics.

CREATE ROLE

-- Tenant role (account-wide; users hold these directly)
CREATE ROLE analyst;
CREATE ROLE IF NOT EXISTS senior_analyst;

-- Catalog role (scoped to one catalog; users never hold these directly)
CREATE ROLE billing_reader IN CATALOG bb;
CREATE ROLE pci_auditor    IN CATALOG bb;

DROP ROLE

DROP ROLE analyst;                           -- RESTRICT by default
DROP ROLE analyst CASCADE;                   -- removes grants + hierarchy edges in one transaction
DROP ROLE IF EXISTS analyst CASCADE;
DROP ROLE billing_reader IN CATALOG bb;

RESTRICT refuses the drop when dependents exist (grants to users, inbound or outbound hierarchy edges). CASCADE writes a single audit rollup row that enumerates every dependency removed.

The built-in tenant_admin role (your organization's administrator role) cannot be dropped; Gnok rejects the delete.

ALTER ROLE OWNER TO ROLE / GRANT OWNERSHIP ON ROLE

-- Two equivalent forms
ALTER ROLE analyst OWNER TO ROLE tenant_admin;
GRANT OWNERSHIP ON ROLE senior_analyst TO ROLE tenant_admin;

Only the current owner (directly, transitively via their tenant-role closure, or via tenant_admin) can transfer ownership.

GRANT ROLE / REVOKE ROLE

Hierarchical role grants follow the dispatch matrix (role_id = holder, granted_role_id = target) — the holder transitively picks up everything the target holds.

-- Tenant → Tenant
GRANT ROLE analyst TO ROLE senior_analyst;

-- Tenant → Catalog
GRANT ROLE pci_auditor IN CATALOG bb TO ROLE analyst;

-- Catalog → Catalog (must stay in the same catalog)
GRANT ROLE billing_reader IN CATALOG bb TO ROLE pci_auditor IN CATALOG bb;

-- User grants (tenant roles only; optional WITH ADMIN OPTION lets grantee re-grant)
GRANT ROLE analyst TO USER alice;
GRANT ROLE analyst TO USER bob WITH ADMIN OPTION;

-- Revoke
REVOKE ROLE analyst FROM ROLE senior_analyst;
REVOKE ROLE analyst FROM USER alice;
REVOKE ADMIN OPTION FOR ROLE analyst FROM USER bob;   -- strip only the delegation flag

Forbidden grant shapes (rejected with actionable errors):

GRANT ROLE <cat_role> IN CATALOG c TO USER … — catalog roles aren't grantable to users; grant to a tenant role first.
GRANT ROLE <cat_role> IN CATALOG c1 TO ROLE <cat_role> IN CATALOG c2 — catalog role inheritance must stay within one catalog.
Any grant that would close a cycle — rejected after a recursive-CTE cycle check (service-managed depth cap, default 16).

USE ROLE / USE SECONDARY ROLES

Session-scoped role activation — narrows the set of effective roles that participate in RLS/masking matching for the current session, without changing persistent grants.

USE ROLE analyst;                             -- set primary
USE ROLE NONE;                                -- clear primary (also: USE ROLE DEFAULT)

USE SECONDARY ROLES ALL;                      -- default: every effective role active
USE SECONDARY ROLES NONE;                     -- only primary active
USE SECONDARY ROLES (analyst, pci_auditor);   -- primary + named secondaries

Secondary-role selections belong to your sign-in session and apply to every query in it until you change them or the session ends. The session lifetime is service-managed.

GRANT / REVOKE ON FUTURE TABLES

Privileges declared before an object exists, auto-materialized on every CREATE TABLE in the target scope.

-- Schema scope — fires for every new table in bb.bb
GRANT SELECT ON FUTURE TABLES IN SCHEMA bb.bb
  TO ROLE pci_auditor IN CATALOG bb;

-- Catalog scope — fires for every schema in the catalog
GRANT SELECT, INSERT ON FUTURE TABLES IN CATALOG bb
  TO ROLE pci_auditor IN CATALOG bb
  WITH GRANT OPTION;

REVOKE SELECT ON FUTURE TABLES IN SCHEMA bb.bb
  FROM ROLE pci_auditor IN CATALOG bb;

Privileges in v1: SELECT, INSERT, UPDATE, DELETE, MODIFY, USAGE. Grantees are catalog roles (tenant-role grantees are a documented follow-up). See RBAC § Future grants for the privilege-type mapping and materialization semantics.

SHOW

SHOW ROLES;                                   -- tenant roles
SHOW ROLES IN CATALOG bb;                     -- catalog roles in `bb`

SHOW GRANTS TO USER alice;
SHOW GRANTS TO ROLE senior_analyst;           -- what the role inherits
SHOW GRANTS TO ROLE pci_auditor IN CATALOG bb;
SHOW GRANTS OF ROLE analyst;                  -- who holds the role directly

Resource Monitors

Resource monitors set credit spending limits on warehouses with automatic enforcement. When accumulated compute units reach a configured threshold, the monitor triggers an action — either a notification or warehouse suspension.

Each time usage is recorded (per query, or by periodic warehouse metering), Gnok adds the credits to the monitor's total and checks its thresholds, without adding work to your queries.

CREATE RESOURCE MONITOR

CREATE RESOURCE MONITOR monthly_budget
  WITH CREDIT_QUOTA = 1000
  FREQUENCY = MONTHLY
  TRIGGERS
    ON 50 PERCENT DO NOTIFY
    ON 80 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

Returns:

message
Resource monitor 'monthly_budget' created (quota=1000 credits, frequency=MONTHLY,
  triggers=[50% -> NOTIFY, 80% -> NOTIFY, 100% -> SUSPEND])

Option	Type	Description
`CREDIT_QUOTA`	Integer	Maximum CUs allowed per period
`FREQUENCY`	String	Reset period: `DAILY`, `WEEKLY`, or `MONTHLY`
`TRIGGERS`	List	One or more threshold/action pairs

Trigger actions:

Action	Behavior
`NOTIFY`	Records an alert in `resource_monitor_alerts`
`SUSPEND`	Same as NOTIFY, plus sets the warehouse status to `suspended`

Each trigger fires once per period. When the period resets, all triggers are re-armed.

ALTER WAREHOUSE SET RESOURCE_MONITOR

ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = monthly_budget;

Returns:

message
Warehouse 'analytics_wh' resource monitor set to 'monthly_budget'

This links the warehouse to the monitor via warehouses.resource_monitor_id. When the monitor is dropped, the link is automatically cleared (ON DELETE SET NULL).

SHOW RESOURCE MONITORS

SHOW RESOURCE MONITORS;

Returns:

monitor_name   | credit_quota | frequency | credits_used | triggers
monthly_budget | 1000         | MONTHLY   | 42.5         | 50% -> NOTIFY, 80% -> NOTIFY, 100% -> SUSPEND

credits_used is updated in real-time by the database trigger as usage records are inserted.

DROP RESOURCE MONITOR

DROP RESOURCE MONITOR monthly_budget;
DROP RESOURCE MONITOR IF EXISTS monthly_budget;

Dropping a monitor automatically unlinks it from any warehouses that reference it.

Session Settings

Session settings control per-connection behavior such as timezone, query timeouts, and memory limits. Settings persist for the duration of the session.

ALTER SESSION SET TIMEZONE = 'America/New_York';
ALTER SESSION SET QUERY_TIMEOUT = '120s';
ALTER SESSION SET MAX_MEMORY_PER_QUERY = '8GB';

Additional SHOW Commands

SHOW commands list metadata objects. Most accept optional IN clauses to scope the listing to a specific catalog or schema.

-- Table properties (returns rows of (key, value), sorted by key)
SHOW TBLPROPERTIES orders;

-- Primary keys (accepts FROM or IN; plural KEYS also works)
SHOW PRIMARY KEY FROM orders;
SHOW PRIMARY KEY IN orders;

-- Dynamic tables
SHOW DYNAMIC TABLES;

-- Streams and tasks
SHOW STREAMS;
SHOW STREAMS ON TABLE orders;
SHOW TASKS;

-- Tags
SHOW TAGS;
SHOW TAG REFERENCES TAG_NAME = 'pii_level';

-- Network policies
SHOW NETWORK POLICIES;

-- Replication
SHOW REPLICATION DATABASES;
SHOW FAILOVER GROUPS;

-- Warehouses and regions
SHOW WAREHOUSES;
SHOW REGIONS;

-- Resource monitors and billing
SHOW RESOURCE MONITORS;
SHOW WAREHOUSE USAGE;
SHOW METERING HISTORY;

Catalogs​

CREATE CATALOG​

USE CATALOG​

DROP CATALOG​

SHOW CATALOGS​

Warehouses​

CREATE WAREHOUSE​

ALTER WAREHOUSE​

USE WAREHOUSE​

DROP WAREHOUSE​

SHOW WAREHOUSES​

Regions​

SHOW REGIONS​

Schemas​

CREATE SCHEMA​

DROP SCHEMA​

SHOW SCHEMAS​

Tables​

CREATE TABLE​

CREATE OR REPLACE TABLE​

Column Options​

Table Options​

Format Version​

Partitioning​

CREATE TABLE AS SELECT (CTAS)​

ALTER TABLE​

Add / Drop / Rename Columns​

Alter Column Properties​

Table Properties​

Row-Level Security​

Tag Assignments​

Primary Keys​

Declaring a primary key​

Enforcement semantics​

Enforcement modes​

Adding a primary key to an existing table​

Dropping a primary key​

SHOW PRIMARY KEY​

Upsert via INSERT ... ON CONFLICT​

Write mode​

What a command writer must do​

Foreign Keys​

Declaring a foreign key​

Validation​

Adding and dropping foreign keys​

Storage and surfacing​

Partition Evolution​

Column Defaults​

Write Ordering​

Table Location​

Identifier Fields​

DROP TABLE​

Views​

CREATE VIEW​

Column-list alias​

DROP VIEW​

Materialized Views​

CREATE MATERIALIZED VIEW​

REFRESH MATERIALIZED VIEW​

SHOW CREATE MATERIALIZED VIEW​

DROP MATERIALIZED VIEW​

Refresh Strategies​

Staleness Detection​

Persistence​

Sequences​

Sequence Options​

Using Sequences​

Drop a Sequence​

SHOW Commands​

Qualifier forms​

Identifier case​

Missing namespaces​

Pagination​

Format Version Upgrade​

Iceberg Table Operations​

Inspecting snapshots​

Querying snapshots as a table — <table>$snapshots​

Rolling back​

REFRESH TABLE​

READ CHANGES (CDC)​

Catalogs

CREATE CATALOG

USE CATALOG

DROP CATALOG

SHOW CATALOGS

Warehouses

CREATE WAREHOUSE

ALTER WAREHOUSE

USE WAREHOUSE

DROP WAREHOUSE

SHOW WAREHOUSES

Regions

SHOW REGIONS

Schemas

CREATE SCHEMA

DROP SCHEMA

SHOW SCHEMAS

Tables

CREATE TABLE

CREATE OR REPLACE TABLE

Column Options

Table Options

Format Version

Partitioning

CREATE TABLE AS SELECT (CTAS)

ALTER TABLE

Add / Drop / Rename Columns

Alter Column Properties

Table Properties

Row-Level Security

Tag Assignments

Primary Keys

Declaring a primary key

Enforcement semantics

Enforcement modes

Adding a primary key to an existing table

Dropping a primary key

SHOW PRIMARY KEY

Upsert via INSERT ... ON CONFLICT

Write mode

What a `command` writer must do

Foreign Keys

Declaring a foreign key

Validation

Adding and dropping foreign keys

Storage and surfacing

Partition Evolution

Column Defaults

Write Ordering

Table Location

Identifier Fields

DROP TABLE

Views

CREATE VIEW

Column-list alias

DROP VIEW

Materialized Views

CREATE MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW

SHOW CREATE MATERIALIZED VIEW

DROP MATERIALIZED VIEW

Refresh Strategies

Staleness Detection

Persistence

Sequences

Sequence Options

Using Sequences

Drop a Sequence

SHOW Commands

Qualifier forms

Identifier case

Missing namespaces

Pagination

Format Version Upgrade

Iceberg Table Operations

Inspecting snapshots

Querying snapshots as a table — `<table>$snapshots`

Rolling back

REFRESH TABLE

READ CHANGES (CDC)