Bind keyset

@BIND_KEYSET() binds a batch population of entity keys from a persisted KEYSET(), an ordered KEYSET_GROUP(), or a direct EXTERNAL_COLUMNS() / INLINE_COLUMNS() source — so analytics queries evaluate features for a business-defined cohort instead of hand-listed IDs.

Serving vs. batch evaluation patterns

Real-time serving:

• Precise key specification: "Calculate revenue for user_id = 1"
• Typically uses BIND_VALUE() or small BIND_VALUES()

Batch analytics:

• Population selection: "Calculate revenue for all orders from yesterday"
• Uses @BIND_KEYSET() (or BIND_COLUMNS()) to drive the entity key space

How it works

@BIND_KEYSET() is a metaprogramming binder (the @ prefix). It resolves at compile time into the internal bind IR that drives the FOR clause.

1. Define a keyset with KEYSET(entity, query). The persisted feature name is the keyset identity — there is no separate label argument.
2. Optionally group alternatives with KEYSET_GROUP(ARRAY(K1, K2, …)). Member order is meaningful.
3. Bind with @BIND_KEYSET(feature [, ROW(...)]) in FOR. Pin one keyset, a group, or a direct external/inline source feature.

Source lineage for each keyset query is derived from its SQL AST. Table-name hints are not authored.

:::caution Breaking change Older forms are rejected: KEYSET(label, entity, query, tables), @BIND_KEYSET(label, entity, …), and $1 / $2 placeholders. Recreate persisted keysets with the new signatures. Named SQL placeholders use :name and are supplied via a named ROW(...). :::

Syntax

Defining a keyset (in CREATE FEATURES):

<keyset_name> := KEYSET(<entity>, '<SQL query with optional :params>')

<keyset_name> := KEYSET(<entity>, '<SQL query with optional :params>')

Grouping alternatives:

<group_name> := KEYSET_GROUP(ARRAY(<keyset1>, <keyset2>, ...))

<group_name> := KEYSET_GROUP(ARRAY(<keyset1>, <keyset2>, ...))

Using a keyset (in evaluation queries):

FOR
    <input> := @BIND_KEYSET(<feature> [, ROW(<name> := <expr>, ...)])

FOR
    <input> := @BIND_KEYSET(<feature> [, ROW(<name> := <expr>, ...)])

Basic usage: pin one keyset

Create an entity and a keyset. The feature name (ORDER_KEYS) is what you bind later:

CREATE OR REPLACE FEATURES IN FM.TUTORIALS.KEYSETS1 AS
SELECT
    orders := ENTITY(),
    order_keys := KEYSET(
        orders,
        'SELECT order_id FROM (VALUES (10), (11), (12)) AS t(order_id)'
    )
;

Evaluate features for every key returned by that keyset:

WITH
    orders := fm.tutorials.keysets1.orders,
    order_id := INPUT(BIGINT#orders),
    order_keys := fm.tutorials.keysets1.order_keys
SELECT
    order_id
FOR
    order_id := @BIND_KEYSET(order_keys)
;

@BIND_KEYSET(ORDER_KEYS) pins that feature. Selection reason in EXPLAIN (FORMAT JSON) is PINNED.

Keyset groups and source alignment

When several keysets describe the same entity against different sources, put them in a KEYSET_GROUP. Member order is the deterministic tie-breaker.

/* SQL */
CREATE SCHEMA IF NOT exists keysets;
--
DROP TABLE IF EXISTS keysets.table1;
--
CREATE TABLE keysets.table1 AS
SELECT key_id, 'table1_' || CAST(key_id AS VARCHAR) AS value
FROM (VALUES (1), (2), (3)) AS t(key_id);
--
DROP TABLE IF EXISTS keysets.table2;
--
CREATE TABLE keysets.table2 AS
SELECT key_id, 'table2_' || CAST(key_id AS VARCHAR) AS value
FROM (VALUES (1), (2), (3)) AS t(key_id);
--
SELECT CAST(COUNT(*) AS INTEGER) AS Count FROM keysets.table2;

CREATE OR REPLACE FEATURES IN FM.TUTORIALS.KEYSETS2 AS
SELECT
    keys := ENTITY(),
    keys_from_t1 := KEYSET(
        keys,
        'SELECT DISTINCT 1 AS key_id FROM keysets.table1'
    ),
    keys_from_t2 := KEYSET(
        keys,
        'SELECT DISTINCT 2 AS key_id FROM keysets.table2'
    ),
    keys_group := KEYSET_GROUP(ARRAY(keys_from_t1, keys_from_t2))
;

@BIND_KEYSET(KG) compares every group member against the set of all active external sources in the query (not declaration order, dependency order, or join order). It selects the first member, in group order, whose derived source lineage intersects that set.

WITH
    key_id := INPUT(BIGINT),
    keys_group := fm.tutorials.keysets2.keys_group
SELECT
    value := EXTERNAL_COLUMNS(
        key_id BIGINT BIND TO key_id,
        value VARCHAR
        FROM TABLE(keysets.table1)
    )[value]
FOR
    key_id := @BIND_KEYSET(keys_group)
;

WITH
    key_id := INPUT(BIGINT),
    keys_group := fm.tutorials.keysets2.keys_group
SELECT
    value := EXTERNAL_COLUMNS(
        key_id BIGINT BIND TO key_id,
        value VARCHAR
        FROM TABLE(keysets.table2)
    )[value]
FOR
    key_id := @BIND_KEYSET(keys_group)
;

Only one member drives a group binding. If several members align with different active sources, group order wins.

If no member aligns, member zero is selected and warning BIND-KEYSET-GROUP-NO-SOURCE-ALIGNMENT names the group, active sources, fallback member, and the remedy: pin the intended member with @BIND_KEYSET(K1).

WITH
    key_id := INPUT(BIGINT),
    keys_group := fm.tutorials.keysets2.keys_group
SELECT
    key_id
FOR
    key_id := @BIND_KEYSET(keys_group)
;

FeatureQL keeps the selected keyset join in generated SQL; the target SQL optimizer decides whether it can eliminate a redundant scan. There is no special ALL-label collapse path.

Named parameters (:name + ROW)

Keyset SQL uses named placeholders such as :num or :ds. Pass values with a named parameter row:

/* SQL */
CREATE SCHEMA IF NOT exists keysets;
--
DROP TABLE IF EXISTS keysets.tablec1;
--
CREATE TABLE keysets.tablec1 AS
SELECT key_id, 'tablec1_' || CAST(key_id AS VARCHAR) AS value
FROM (
  VALUES
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
) AS t(key_id);
--
DROP TABLE IF EXISTS keysets.tablec2;
--
CREATE TABLE keysets.tablec2 AS
SELECT key_id, 'tablec2_' || CAST(key_id AS VARCHAR) AS value
FROM (
  VALUES
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
    (21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
) AS t(key_id);
--
SELECT CAST(COUNT(*) AS INTEGER) AS Count FROM keysets.tablec2;

CREATE OR REPLACE FEATURES IN FM.TUTORIALS.KEYSETS2C AS
SELECT
    keysc := ENTITY(),
    keys_c1 := KEYSET(
        keysc,
        'SELECT DISTINCT 1 + :num AS key_id FROM keysets.tablec1'
    ),
    keys_c2 := KEYSET(
        keysc,
        'SELECT DISTINCT 2 + :num AS key_id FROM keysets.tablec2'
    ),
    keys_c_group := KEYSET_GROUP(ARRAY(keys_c1, keys_c2))
;

CONST
    BASE := 5,
    NUM := base * 2
WITH
    key_id := INPUT(BIGINT),
    keys_c_group := fm.tutorials.keysets2c.keys_c_group
SELECT
    value := EXTERNAL_COLUMNS(
        key_id BIGINT BIND TO key_id,
        value VARCHAR
        FROM TABLE(keysets.tablec1)
    )[value],
    param_num := VALUE(@eval_as_literal(num))
FOR
    key_id := @BIND_KEYSET(keys_c_group, ROW(num AS num))
;

Group members must share the same entity and the same placeholder-name set; parameter field order in ROW is irrelevant. Exact name match is required — missing, extra, or duplicate names raise a UE. Non-constant field expressions raise a UE.

A common batch pattern is a date-filtered population:

/* SQL */
CREATE SCHEMA IF NOT EXISTS keysets;
--
DROP TABLE IF EXISTS keysets.customers_by_date;
--
CREATE TABLE keysets.customers_by_date AS
SELECT * FROM (VALUES
    (1, DATE '2024-01-15', 100.0),
    (2, DATE '2024-01-15', 200.0),
    (3, DATE '2024-01-15', 150.0),
    (1, DATE '2024-01-16', 110.0),
    (2, DATE '2024-01-16', 220.0),
    (3, DATE '2024-01-16', 165.0),
    (4, DATE '2024-01-16', 300.0),
    (5, DATE '2024-01-16', 250.0)
) AS t(customer_id, ds, revenue);
--
SELECT CAST(COUNT(*) AS INTEGER) AS Count FROM keysets.customers_by_date;

CREATE OR REPLACE FEATURES IN FM.TUTORIALS.KEYSETS3 AS
SELECT
    customers := ENTITY(),
    customers_by_date := KEYSET(
        customers,
        'SELECT DISTINCT customer_id FROM keysets.customers_by_date WHERE ds = :ds'
    )
;

CONST TARGET_DATE := DATE '2024-01-15'
WITH
    customer_id := INPUT(BIGINT),
    customers_by_date := fm.tutorials.keysets3.customers_by_date
SELECT
    customer_id,
    revenue := EXTERNAL_COLUMNS(
        customer_id BIGINT BIND TO customer_id,
        ds DATE BIND TO @eval_as_literal(target_date),
        revenue DOUBLE
        FROM TABLE(keysets.customers_by_date)
    )[revenue]
FOR
    customer_id := @BIND_KEYSET(customers_by_date, ROW(target_date AS ds))
;

The keyset selects which entity keys to evaluate; BIND TO @eval_as_literal(...) on EXTERNAL_COLUMNS still filters the fact partition when needed.

Direct source binding

@BIND_KEYSET(EXTERNAL_OR_INLINE_FEATURE) means “evaluate every key represented by this active source.” It is valid only when:

• the referenced feature is an active EXTERNAL_COLUMNS() or INLINE_COLUMNS() in the query, and
• that source has BIND TO keys whose entity matches the FOR-bound input.

When both hold, the binder reuses the source’s mapped key columns and drops only that redundant first bind join. Otherwise it raises a UE.

WITH
    key_id := INPUT(BIGINT),
    src := EXTERNAL_COLUMNS(
        key_id BIGINT BIND TO key_id,
        value VARCHAR
        FROM TABLE(keysets.table1)
    )
SELECT
    value := src[value]
FOR
    key_id := @BIND_KEYSET(src)
;

Resolution readout — EXPLAIN (FORMAT JSON)

EXPLAIN (FORMAT JSON) includes a top-level keyset_resolutions array with one object per @BIND_KEYSET binder:

Pin path → selection_reason: PINNED. Group with intersecting sources → SOURCE_ALIGNMENT. No intersection → NO_ALIGNMENT_FALLBACK plus the warning above. Direct external/inline bind → DIRECT_SOURCE. Annotate a focused EXPLAIN SLT expected string only after Phase 2 emits a stable JSON shape (avoid brittle full-VARCHAR pins before then).

Best practices

• Name keysets by intent: the persisted feature name is the identity (ORDER_KEYS, CUSTOMERS_BY_DATE)
• Prefer groups over duplicate labels: put alternatives in KEYSET_GROUP(ARRAY(...)) and let source alignment pick; pin with @BIND_KEYSET(K1) when you need certainty
• Keep keyset SQL to key columns: filtering for the population belongs in the keyset query; feature logic stays in features
• Use :name placeholders: pass compile-time values via ROW(name := expr) / ROW(expr AS name), often with CONST
• Inspect resolution: use EXPLAIN (FORMAT JSON) → keyset_resolutions when a group picks an unexpected member
• Direct-source bind when the driving table already carries the entity keys you want — avoid a redundant keyset for that case