FeatureQL for the Impatient

You know SQL. You've written hundreds of CTEs, maintained dbt models, debugged metric discrepancies across dashboards. FeatureQL takes the parts of SQL you actually care about — the business logic — and makes them reusable, composable, and executable anywhere.

This page walks through a complete example in under 5 minutes.

Build a customer segment, step by step

The goal: define IS_LONG_TENURED — a feature that takes a customer ID and returns whether the customer signed up before 2024.

Step 1: Declare the input

Every FeatureQL query starts with an input — the key you're computing features for. Here, it's a customer ID:

WITH
    CUSTOMER_ID := INPUT(BIGINT)  -- This is the definition of the input parameter
SELECT
    CUSTOMER_ID  -- The feature you want to return
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101])  -- Binds actual values to the input parameter

No tables, no FROM clause. You declared a parameter (CUSTOMER_ID), selected it, and bound two values to evaluate. That's the core pattern: define features, bind inputs, get results.

Step 2: Connect to real data

Create some mockup customer data (this is just SQL — FeatureQL can embed raw SQL when needed):

/* SQL */
CREATE SCHEMA IF NOT exists impatient;
--
CREATE OR REPLACE TABLE impatient.dim_customers (
    customer_id BIGINT,
    preferred_city_id VARCHAR,
    name VARCHAR,
    time_create TIMESTAMP,
);
--
INSERT INTO impatient.dim_customers SELECT * FROM (
    VALUES
        (100, 'BCN', 'John Doe', '2023-09-18 14:30:00'),
        (101, 'BCN', 'Jane Doe', '2024-12-08 09:45:00'),
        (102, 'BCN', 'Jack Doe', '2025-02-03 12:00:02')
);

Now map table columns to features with EXTERNAL_COLUMNS. The BIND TO clause tells FeatureQL which column is the lookup key:

WITH
    CUSTOMER_ID := INPUT(BIGINT),
    DATA.CUSTOMERS := EXTERNAL_COLUMNS(
        customer_id BIGINT BIND TO CUSTOMER_ID,
        name VARCHAR,
        time_create TIMESTAMP
        FROM TABLE(impatient.dim_customers)
    ),
    CUSTOMER_NAME := DATA.CUSTOMERS[name],
    CUSTOMER_TIME_CREATE := DATA.CUSTOMERS[time_create],
SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    CUSTOMER_TIME_CREATE,
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101])  -- We need to choose the values we want to bind to the input
;

Notice: you didn't write a JOIN. You declared which columns you need and which key to look them up by. FeatureQL generates the join for you.

Step 3: Add business logic

One line of logic on top of the data mapping. A customer is long-tenured if they signed up before 2024:

WITH
    CUSTOMER_ID := INPUT(BIGINT),
    DATA.CUSTOMERS := EXTERNAL_COLUMNS(
        customer_id BIGINT BIND TO CUSTOMER_ID,
        name VARCHAR,
        time_create TIMESTAMP
        FROM TABLE(impatient.dim_customers)
    ),
    CUSTOMER_NAME := DATA.CUSTOMERS[name],
    CUSTOMER_TIME_CREATE := DATA.CUSTOMERS[time_create],
    IS_LONG_TENURED := CUSTOMER_TIME_CREATE < '2024-01-01'::TIMESTAMP,
SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    CUSTOMER_TIME_CREATE,
    IS_LONG_TENURED,
FOR
    CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(impatient.dim_customers))  -- The entire column is BIND TO the input
;

IS_LONG_TENURED is a pure function: it depends on CUSTOMER_TIME_CREATE, which depends on DATA.CUSTOMERS, which depends on CUSTOMER_ID. FeatureQL resolves this dependency chain automatically — you never specify evaluation order.

Also notice: the FOR clause switched from BIND_VALUES (explicit list) to BIND_COLUMNS (entire table column). Same features, different binding strategy.

Persist and reuse

The features above were local — defined in the query. In practice, core features like CUSTOMER_ID and CUSTOMER_NAME would be persisted in the registry so the entire organization can reuse them:

CREATE OR REPLACE FEATURES IN FM.TUTORIALS.IMPATIENT AS
SELECT
    CUSTOMER_ID := INPUT(BIGINT),
    DATA.CUSTOMERS := EXTERNAL_COLUMNS(
        customer_id BIGINT BIND TO CUSTOMER_ID,
        name VARCHAR,
        time_create DATE
        FROM TABLE(impatient.dim_customers)
    ),
    CUSTOMER_NAME := DATA.CUSTOMERS[name],
    CUSTOMER_TIME_CREATE := DATA.CUSTOMERS[time_create],
;

Now anyone can build on top without redefining the data mapping. An analyst just writes their business logic:

WITH
    IS_LONG_TENURED := CUSTOMER_TIME_CREATE < '2024-01-01'::DATE,
SELECT
    CUSTOMER_ID,
    IS_LONG_TENURED,
FROM FM.TUTORIALS.IMPATIENT
FOR
    CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(impatient.dim_customers))
;

Three lines of FeatureQL. The FROM FM.TUTORIALS.IMPATIENT pulls the persisted features, the WITH clause adds a local feature on top, and the FOR clause binds the input. Everything else — the data source connection, the join, the dependency resolution — is handled automatically.

From features to dashboards

Features produce fine-grained, per-entity results. For dashboards and reports, you typically need aggregations. FeatureQL handles this with GROUP BY as part of the feature definition:

WITH
    IS_LONG_TENURED := DATA.CUSTOMERS[time_create] < '2024-01-01'::DATE,
SELECT
    IS_LONG_TENURED,
    NUM_CUSTOMERS := COUNT(1) GROUP BY IS_LONG_TENURED, -- Note that group by is part of the feature definition
FROM FM.TUTORIALS.IMPATIENT
FOR
    CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(impatient.dim_customers))
;

For existing BI tools that generate their own SQL, you can embed FeatureQL inside SQL. The BI tool writes the outer GROUP BY and WHERE clauses; FeatureQL handles the feature computation:

/* SQL */
WITH
    CUSTOMERS_DATA AS FEATUREQL(
        WITH
            IS_LONG_TENURED := CUSTOMER_TIME_CREATE < '2024-01-01'::DATE,
        SELECT
            CUSTOMER_ID,
            IS_LONG_TENURED,
        FROM FM.TUTORIALS.IMPATIENT
        FOR
            CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(impatient.dim_customers))
        ;
    )
SELECT
    IS_LONG_TENURED,
    COUNT(1) AS NUM_CUSTOMERS
FROM CUSTOMERS_DATA
GROUP BY IS_LONG_TENURED;

This means you can adopt FeatureQL incrementally — your existing SQL tools keep working, they just get better data.

Joins without JOIN

All cross-entity operations in FeatureQL use RELATED(). No JOIN keyword, no ON clause, no ambiguous column names. You describe what you want from another entity, and FeatureQL figures out the join path from the entity annotations.

Here's a single query that demonstrates four join patterns across customers, orders, and items — based on the e-commerce tutorial :

WITH
    -- Join PK on PK
    CUSTOMER_NAME := TABLES.DIM_CUSTOMERS[name],
    CUSTOMER_ORDERS := TABLES.AGG_CUSTOMERS_OBT[orders],
    LAST_ORDER_ID := TABLES.AGG_CUSTOMERS_OBT[last_order_id],
    -- Join FK on PK
    LAST_ORDER_CITY_NAME := LAST_ORDER_ID.RELATED(TABLES.FCT_ORDERS[order_city_name]),
    -- Join PK on FK
    NUM_ORDERS := CUSTOMER_ID.RELATED(SUM(1) GROUP BY TABLES.FCT_ORDERS[order_customer_id]),
    -- Join FK on FK
    LAST_ORDER_PRICE := LAST_ORDER_ID.RELATED(
        SUM(TABLES.FCT_ITEMS[price] * TABLES.FCT_ITEMS[quantity]::DECIMAL)
        GROUP BY TABLES.FCT_ITEMS[item_order_id]
    )
SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    CUSTOMER_ORDERS,
    LAST_ORDER_ID,
    LAST_ORDER_CITY_NAME,
    NUM_ORDERS,
    LAST_ORDER_PRICE
FROM FM.ECOMM
FOR CROSS
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
    ORDER_ID := BIND_VALUES(SEQUENCE(200,203)),  -- Join PK on FK
    ITEM_ID := BIND_VALUES(SEQUENCE(300,306)),  -- Join FK on FK
;

Look at what's happening: a customer's name (direct lookup), their last order's city (FK→PK join), their total number of orders (PK→FK aggregation), and their last order's total price (FK→FK aggregation across items). In SQL, this would be three or four CTEs with explicit joins. In FeatureQL, each is one line using RELATED().

Arrays of rows: the nested data model

FeatureQL is built around Entity-Centric Modeling — instead of joining flat tables, each entity carries its related facts as nested arrays. A customer row contains an array of their orders. Each order contains an array of its items.

This means you can filter, aggregate, sort, and transform nested data without flattening it first. EXTEND() enriches arrays with data from other entities. TRANSFORM() runs mini-queries inside each array. Together, they replace most of what you'd use window functions and self-joins for in SQL.

See the full reference: Array of rows .

Compile-time computation

FeatureQL features are pure functions — but sometimes you need values that depend on "now" or on external parameters. @literal() evaluates an expression at compile time and injects the result as a constant.

In our customer example, instead of hardcoding 2024-01-01, we can compute "2 years ago from today" dynamically:

WITH
    IS_LONG_TENURED := FM.TUTORIALS.IMPATIENT.CUSTOMER_TIME_CREATE > @literal(DATE_ADD(NOW(), 'YEAR', -2)::DATE),
SELECT
    FM.TUTORIALS.IMPATIENT.CUSTOMER_ID,
    IS_LONG_TENURED,
FOR
    FM.TUTORIALS.IMPATIENT.CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(impatient.dim_customers))
;

No Jinja templates, no external parameter injection, no string interpolation. The date computation happens inside FeatureQL itself, at compile time, with full type safety.

See also: @echo() for injecting computed strings into query text.

Testing and experimentation built in

FeatureQL treats testing and experimentation as first-class concerns, not afterthoughts:

VARIANT() — Create alternative versions of any feature. Swap a data source, change a threshold, test a different formula. Variants share the same dependency graph, so you can compare implementations side by side without duplicating code. Perfect for A/B testing and model comparison.

MACRO() — Define reusable parameterized functions in FeatureQL. Like SQL functions, but with FeatureQL's type system and dependency tracking.

HASH01() and GRADUAL_ROLLOUT() — Deterministic experiment assignment and progressive rollouts, built into the language. No external experimentation platform needed for basic A/B tests.

Business functions you'd otherwise build yourself

FeatureQL ships with specialized functions that would take weeks to implement correctly:

Historical data — SCD_AT_TIME() queries slowly changing dimensions at any point in time. ACCUMULATION_AT_TIME() computes running totals as of a given date. Time-travel queries without complex window functions.

Growth accounting — TO_ACTIVITY_BITS() compresses 60 days of activity dates into a single bitwise representation. Then IS_ACTIVE(), COUNT_ACTIVE(), RECENCY(), and ACTIVITY_STATUS() let you classify users into lifecycle stages (new, active, dormant, resurrected) with one function call each.

Simulations — SAMPLE_UNIFORM(), SAMPLE_GAUSSIAN(), SAMPLE_VALUE(), and SAMPLE_BOOL() let you build digital twins of your business. Test pricing strategies, model behavior, and risk scenarios before going live.

Self-documenting and discoverable

Every feature in the registry is queryable metadata. You can explore what exists, understand dependencies, and find documentation without leaving FeatureQL:

• SHOW FEATURES — Browse the catalog, filter by namespace, function type, or dependency
• SHOW CREATE FEATURES <name> — See the exact definition of any feature
• SHOW FUNCTION SIGNATURES — Get signatures and documentation for any built-in function
• SHOW DOCS — Browse the full documentation from inside a query

Where to go next

• Hello World — The language fundamentals, one concept at a time
• E-commerce tutorial — A complete data model with customers, orders, and items
• Mapping data sources — How to connect FeatureQL to your tables
• Batch analytics — Using FeatureMesh as a semantic layer for your data warehouse
• Real-time serving — Serving features from Redis, JDBC, and HTTP in production