LLM reference — FeatureQL syntax

CONST                          -- Optional: compile-time constants
    MY_CONST := 42,
WITH                           -- Local features (only evaluated if referenced in SELECT)
    FEATURE1 := INPUT(BIGINT),
    FEATURE2 := FEATURE1 * 2,
SELECT                         -- Features to return in output
    FEATURE1,
    FEATURE2,
FROM fm.namespace              -- Optional: pull persisted features from registry
FOR                            -- REQUIRED: bind values to INPUT() features
    FEATURE1 := BIND_VALUES(ARRAY[1, 2, 3]),
WHERE FEATURE2 > 2             -- Optional: filter result rows
ORDER BY FEATURE2 DESC         -- Optional: sort
LIMIT 10 OFFSET 5              -- Optional: pagination
;

NUMBER1 := 1           -- FeatureQL style (preferred)
1 AS NUMBER1           -- SQL style

BIGINT#CUSTOMERS           -- This value is a CUSTOMERS entity key
ARRAY(BIGINT#ORDERS)       -- Array of ORDER entity keys

CAST('123' AS BIGINT)      -- Standard form
'123'::BIGINT              -- Shorthand (higher precedence than operators)
ID::BIGINT#ORDERS          -- Add entity annotation
ID::BIGINT                 -- Remove entity annotation

-- OK: DECIMAL widening in arrays and ROWs (same family, different precision)
arr := ARRAY[1.25, 1.1]                            -- ARRAY(DECIMAL(3,2))
aor := ARRAY[ROW(1.1 AS val), ROW(1.25 AS val)]    -- ARRAY(ROW(val DECIMAL(3,2)))

-- OK: integer widening in arrays (all integers, different sizes)
arr := ARRAY[ROW(2::BIGINT), ROW(INT '2')]          -- ARRAY(ROW(field_1 BIGINT))

-- WRONG: BIGINT + DECIMAL in array (no promotion in constructors)
arr := ARRAY[1.25, 1]     -- Error: DECIMAL + BIGINT are different families

-- WRONG: 1.25 is DECIMAL(3,2), price is DOUBLE → type mismatch
revenue := price * 1.25

-- RIGHT: Use scientific notation for DOUBLE literals
revenue := price * 1.25e0

-- RIGHT: Or cast explicitly
revenue := price * CAST(1.25 AS DOUBLE)

' HELLO '.TRIM().LOWER()              -- Equivalent to LOWER(TRIM(' HELLO '))
ARRAY[3,1,2].ARRAY_SORT().ARRAY_SUM() -- Chain array operations
(1 + 2).MULTIPLY(3)                   -- Equivalent to MULTIPLY((1 + 2), 3)

-- ROW field access
row_feature[field_name]              -- Extract one field
row_feature[field1, field2]          -- Extract multiple fields (returns ROW)

-- ARRAY element access (1-indexed)
array_feature[1]                     -- First element
array_feature[2:4]                   -- Slice (elements 2, 3, 4)
array_feature[2,4]                   -- Elements at positions 2 and 4

-- ARRAY(ROW) field extraction (returns ARRAY of that field's values)
array_of_rows[field_name]            -- Extract field as array: [{id:1,name:'A'},{id:2,name:'B'}][name] → ['A','B']

-- Ternary
IF(condition, then_value, else_value)

-- Multi-branch
CASE WHEN(
    ARRAY[condition1, condition2],    -- Boolean conditions
    ARRAY[value1, value2],            -- Corresponding results
    default_value                     -- Fallback
)

-- Value matching
CASE WHEN VALUE(
    expression,                       -- Value to match
    ARRAY[match1, match2],            -- Match values
    ARRAY[result1, result2],          -- Corresponding results
    default_value                     -- Fallback
)

-- First non-null
COALESCE(value1, value2, value3)

CONST MY_VALUE := 42,                          -- Compile-time constant

-- Inject computed literal (preserves type, evaluated once at compile time)
THRESHOLD := @literal(POWER(2, 10)),

-- Non-deterministic functions MUST be wrapped in @literal
TODAY := @literal(CURRENT_TIMESTAMP()),         -- Evaluated once, not per row
RAND := @literal(RANDOM()),                     -- Evaluated once, not per row

-- Inject computed string into query text
DYNAMIC := @echo('SELECT * FROM ' || table_name),

-- Deterministic per-row randomness (NOT @literal — this is per-row by design)
BUCKET := HASH01(USER_ID, 'experiment_name'),   -- Returns 0.0 to 1.0

CREATE FEATURES IN fm.namespace AS SELECT ...;           -- Persist feature definitions
CREATE OR REPLACE FEATURES IN fm.namespace AS SELECT ...; -- Upsert
CREATE TEMPORARY FEATURES AS SELECT ...;                  -- Session-scoped
DROP FEATURES fm.namespace.feature_name;                  -- Remove
ALTER FEATURE fm.namespace.feature SET COMMENT = 'desc';  -- Add metadata

-- Features use fm.namespace.feature_name pattern
FROM fm.ecomm                         -- Import namespace, use short names
FROM fm.ns1, fm.ns2 AS _ns2           -- Multiple namespaces (alias must start with _)
fm.ecomm.CUSTOMER_NAME                -- Fully qualified reference