Pipelines & Vector Search

Pipelines

Named, reusable transformation chains that can be saved to disk and restored across sessions.

-- Define
DEFINE PIPELINE clean AS WHERE active = 1 THEN ORDER BY score DESC THEN LIMIT 10

-- Inspect
SHOW PIPELINES
DESCRIBE PIPELINE clean

-- Apply
APPLY PIPELINE clean ON products INTO top_products
APPLY PIPELINE clean ON products          -- in-place (overwrites source)

-- Persist
SAVE PIPELINE clean                        -- <data_dir>/<db>/pipelines/clean.json
SAVE PIPELINE clean TO '/backups/clean.json'

-- Restore
LOAD PIPELINE clean
LOAD PIPELINE clean FROM '/backups/clean.json'

-- Remove
DROP PIPELINE clean

Steps chain with THEN:

Step	Syntax	Description
Projection	SELECT col [AS alias], ...	Keep/rename columns
Filter	WHERE expr / FILTER expr	Row predicate
Sort	ORDER BY col [ASC|DESC] [, ...]	Row ordering
Limit	LIMIT n	Cap row count
Normalize	NORMALIZE col	L2-normalize a vector column

Pipelines are stored as human-readable JSON containing the original DSL source:

{ "name": "clean", "source": "DEFINE PIPELINE clean AS WHERE active = 1 THEN LIMIT 10", "version": "0.1.46" }

The version field records the engine version that saved the pipeline (informational only, not a compatibility gate). On load, the source is re-parsed to reconstruct the pipeline exactly, and the file is editable: any valid DEFINE PIPELINE DSL can replace the source field.

Vector search & indexing

CREATE INDEX

CREATE INDEX ON docs(category)
CREATE VECTOR INDEX ON docs(embedding)

• CREATE INDEX [<name>] ON <dataset>(<column>): a standard lookup index on a scalar column.
• CREATE VECTOR INDEX [<name>] ON <dataset>(<column>): an index-accelerated structure over a Vector column, enabling SEARCH and index-aware COSINE_SIM filtering in WHERE.
• List existing indexes with SHOW INDEXES [<dataset>].
• Persists with the dataset: SAVE DATASET writes which columns are indexed (and with what type), including the actual k-means clustering state; LOAD DATASET restores it directly instead of recomputing it from scratch, and reports which indexes were restored. A content hash travels with the snapshot: a mismatch against the freshly loaded column falls back to a full rebuild rather than trusting stale clustering.
• Clustering happens automatically: CREATE VECTOR INDEX clusters the column’s vectors (IVF-style k-means, cosine metric) once it has at least ~64 rows. No extra syntax is needed. Below that, it falls back to brute-force scan. An approximate top-k query (SEARCH, ORDER BY COSINE_SIM(...)) only probes the nearest few clusters; an exact predicate (WHERE COSINE_SIM(...) > threshold) uses a provable per-cluster similarity bound to skip clusters that can’t contain a match, so it never drops a qualifying row.

SEARCH

SEARCH docs ON embedding QUERY [0.9, 0.1, 0.0] LIMIT 10
SEARCH docs ON embedding QUERY my_query_tensor LIMIT 10 INTO results
SEARCH docs ON embedding QUERY [0.9, 0.1, 0.0] LIMIT 10 FILTER category = "electronics"

SEARCH <dataset> ON <column> QUERY <[vector literal]|tensor_name> LIMIT <k> [FILTER <predicate>] [INTO <target>] returns the top-k nearest rows by cosine similarity. INTO <target> materializes the results as a new dataset.

FILTER <predicate> (modern syntax only; the two alternate forms below don’t have it) applies <predicate> to the k nearest-neighbor results as a post-filter, using the same predicate vocabulary as WHERE/FILTER in SELECT: not a pre-filtered/ expanded search, so a highly selective predicate can return fewer than k rows. A separate keyword from WHERE, which is already claimed by the alternate query-vector syntax below.

A plain SELECT ... WHERE COSINE_SIM(...) > threshold AND <other predicate> gets the same index acceleration automatically: the planner decomposes a top-level AND to find the COSINE_SIM(...) > threshold conjunct anywhere in it, index-accelerates that conjunct via the vector index, and applies the remaining conjuncts as a post-filter, so WHERE COSINE_SIM(...) > t AND category = 'x' isn’t forced into a full scan+filter just because the predicate isn’t exactly the cosine comparison alone. EXPLAIN on such a query shows whether this fired (CosineFilterExec in the physical plan).

Two alternate forms parse to the exact same statement:

-- WHERE-style shorthand (approx-equals operator ~=)
SEARCH docs WHERE embedding ~= [0.9, 0.1, 0.0] LIMIT 10

-- Legacy explicit-target form
SEARCH results FROM docs QUERY [0.9, 0.1, 0.0] ON embedding K=10

All three forms require a CREATE VECTOR INDEX on <column> first: SEARCH always runs as an index-accelerated lookup and errors if no index exists. For ad hoc similarity scoring without a prebuilt index, use COSINE_SIM directly in SELECT/WHERE/ORDER BY instead (see Querying). That’s the more common pattern for one-off queries.

TRANSFORM

TRANSFORM docs SELECT id, UPPER(name) AS name_upper WHERE active = 1 INTO clean_docs
TRANSFORM docs SELECT id, UPPER(name) AS name_upper WHERE active = 1   -- overwrites docs in place

TRANSFORM <source> SELECT <columns> [WHERE <expr>] [INTO <target>] is a single-shot projection/filter, equivalent to SELECT ... FROM <source> [WHERE ...] under the hood. With INTO <target>, writes to <target> (creating it if needed). Without INTO, it overwrites <source> in place: unlike a plain SELECT, it does not return results inline.