Analytics and serving with FeatureMesh

This page is the interactive companion to the FeatureMesh homepage How it works walkthrough. Same path, runnable end to end: namespace fm.home, a tiny customer/orders dataset, one promo rule (show_promocode), batch on the warehouse, then the same rule from Redis via VARIANT() for serving.

This is the product walkthrough, not the language introduction. If ENTITY(), FOR, bindings, or RELATED() are new, start with the FeatureQL companion , then return here.

Set up the analytics data

Three tables: customers, orders, and a small OBT with each customer's last order and order-id array. Edit the VALUES if you want different demo data.

Customers

FeatureQL

/* SQL */
CREATE OR REPLACE TABLE home.dim_customers AS
SELECT
    customer_id::BIGINT AS customer_id,
    name,
    created_at
FROM (
    VALUES
        (100, 'Alice', DATE '2022-03-15'),
        (101, 'Bob', DATE '2023-06-10'),
        (102, 'Charlie', DATE '2024-01-20'),
        (103, 'Diana', DATE '2024-11-01')
) AS t(customer_id, name, created_at);

Result

Orders

FeatureQL

/* SQL */
CREATE OR REPLACE TABLE home.fct_orders AS
SELECT
    order_id::BIGINT AS order_id,
    order_customer_id::BIGINT AS order_customer_id,
    price::DECIMAL(10, 2) AS price,
    created_at
FROM (
    VALUES
        (1001, 100, 450.00, TIMESTAMP '2025-06-01 08:00:00'),
        (1002, 100, 380.00, TIMESTAMP '2025-07-15 14:45:00'),
        (1003, 101, 600.00, TIMESTAMP '2025-08-20 17:30:00'),
        (1004, 101, 550.00, TIMESTAMP '2025-10-05 16:20:00'),
        (1005, 100, 520.00, TIMESTAMP '2025-11-12 11:30:00'),
        (1006, 102, 1200.00, TIMESTAMP '2025-11-15 14:30:00'),
        (1007, 102, 300.00, TIMESTAMP '2025-12-20 10:00:00'),
        (1008, 101, 400.00, TIMESTAMP '2025-12-25 09:45:00'),
        (1009, 103, 850.00, TIMESTAMP '2026-01-10 12:00:00'),
        (1010, 103, 400.00, TIMESTAMP '2026-01-14 09:30:00')
) AS t(order_id, order_customer_id, price, created_at);

Result

Customer OBT

FeatureQL

/* SQL */
CREATE OR REPLACE TABLE home.agg_customers_obt AS
SELECT
    customer_id::BIGINT AS customer_id,
    last_order_id::BIGINT AS last_order_id,
    orders::BIGINT[] AS orders
FROM (
    VALUES
        (100, 1005, [1001, 1002, 1005]),
        (101, 1008, [1003, 1004, 1008]),
        (102, 1007, [1006, 1007]),
        (103, 1010, [1009, 1010])
) AS t(customer_id, last_order_id, orders);

Result

Analytics / Training

Define entities and keys

Entities are the semantic foundation: customers and orders are business objects, not tables. Their keys are typed — BIGINT#customers is not interchangeable with BIGINT#orders — so invalid joins fail at compile time instead of in a dashboard review.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.HOME AS
SELECT
    -- Entities
    customers := ENTITY(),
    orders := ENTITY(),
    -- Primary keys
    customer_id := INPUT(BIGINT#customers),
    order_id := INPUT(BIGINT#orders),
    -- Customer dimensions
    tables.dim_customers := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        name VARCHAR,
        created_at TIMESTAMP
        FROM TABLE(home.dim_customers)
    ),
    customer_name := tables.dim_customers[name],
    customer_created_at := tables.dim_customers[created_at],
    -- Order facts
    tables.fct_orders := EXTERNAL_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        order_customer_id BIGINT#customers,
        price DECIMAL,
        created_at TIMESTAMP
        FROM TABLE(home.fct_orders)
    ),
    order_price := tables.fct_orders[price],
    order_customer_id := tables.fct_orders[order_customer_id],
    order_created_at := tables.fct_orders[created_at],
    -- Customer facts aggregations (OBT)
    tables.agg_customers_obt := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        last_order_id BIGINT#orders,
        orders ARRAY(BIGINT#orders)
        FROM TABLE(home.agg_customers_obt)
    ),
    last_order_id := tables.agg_customers_obt[last_order_id],
    customer_orders := tables.agg_customers_obt[orders],
    -- Keysets: Define where to find the keys for each entity.
    dim_customers_keyset := KEYSET(
        customers,
        'SELECT customer_id AS "fm.home.customer_id"
         FROM home.dim_customers'
    ),
    fct_orders_keyset := KEYSET(
        orders,
        'SELECT order_id AS "fm.home.order_id"
         FROM home.fct_orders'
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.HOME.CUSTOMERS	CREATED	Feature created as not exists
FM.HOME.ORDERS	CREATED	Feature created as not exists
FM.HOME.CUSTOMER_ID	CREATED	Feature created as not exists
FM.HOME.ORDER_ID	CREATED	Feature created as not exists
FM.HOME.TABLES.DIM_CUSTOMERS	CREATED	Feature created as not exists
FM.HOME.CUSTOMER_NAME	CREATED	Feature created as not exists
FM.HOME.CUSTOMER_CREATED_AT	CREATED	Feature created as not exists
FM.HOME.TABLES.FCT_ORDERS	CREATED	Feature created as not exists
FM.HOME.ORDER_PRICE	CREATED	Feature created as not exists
FM.HOME.ORDER_CUSTOMER_ID	CREATED	Feature created as not exists
FM.HOME.ORDER_CREATED_AT	CREATED	Feature created as not exists
FM.HOME.TABLES.AGG_CUSTOMERS_OBT	CREATED	Feature created as not exists
FM.HOME.LAST_ORDER_ID	CREATED	Feature created as not exists
FM.HOME.CUSTOMER_ORDERS	CREATED	Feature created as not exists
FM.HOME.DIM_CUSTOMERS_KEYSET	CREATED	Feature created as not exists
FM.HOME.FCT_ORDERS_KEYSET	CREATED	Feature created as not exists

The KEYSET() definitions at the bottom are how you enumerate “all customers” later — pin the keyset feature with @BIND_KEYSET(dim_customers_keyset), without hard-coding ids in every query.

Map features to columns

In the same statement, EXTERNAL_COLUMNS() turns warehouse columns into source features (order_price, customer_orders, last_order_id, …). BIND TO says which column is the entity key for that table.

Derived names stay separate from tables.* mappings on purpose: business logic composes on feature names, not on physical schemas. Swap the table tomorrow; keep the formulas.

Write transformations

Express the business decision as features — not as a one-off SQL report. Persist lifetime value, recency, and the batch promo rule. Lifetime value is also stored in cents so the same predicate can be reused for serving without floating-point compares.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.HOME AS
SELECT
    customer_ltv := customer_id.RELATED(
        SUM(order_price) GROUP BY order_customer_id
    ),
    -- Cents keep the promo rule BIGINT-comparable with Redis serving fields
    customer_ltv_cents := CAST(customer_ltv * 100 AS BIGINT),
    recency := DATE_DIFF(
        TIMESTAMP '2026-02-01',
        last_order_id.RELATED(order_created_at),
        'day'
    ),
    show_promocode_offline := recency > 30 AND customer_ltv_cents > 100000
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.HOME.CUSTOMER_LTV	CREATED	Feature created as not exists
FM.HOME.CUSTOMER_LTV_CENTS	CREATED	Feature created as not exists
FM.HOME.RECENCY	CREATED	Feature created as not exists
FM.HOME.SHOW_PROMOCODE_OFFLINE	CREATED	Feature created as not exists

show_promocode_offline is the canonical rule. Batch jobs, hybrid SQL, and the serving VARIANT() all reuse it.

For FeatureQL patterns such as RELATED() vs SQL, or array EXTEND(), see the FeatureQL homepage companion .

Compute features in batch

Bind every customer and project the persisted features.

FeatureQL

SELECT
    customer_id,
    customer_ltv,
    recency,
    show_promocode_offline,
FROM fm.home
FOR
    customer_id := @BIND_KEYSET(dim_customers_keyset),
    order_id := @BIND_KEYSET(fct_orders_keyset),
;

Result

fm.home.customer_id BIGINT	fm.home.customer_ltv DECIMAL	fm.home.recency BIGINT	fm.home.show_promocode_offline BOOLEAN
100	1350.0	81	true
101	1550.0	38	true
102	1500.0	43	true
103	1250.0	18	false

The result should contain four customers: three receive the promotion and customer 103 does not.

On the homepage, the same idea appears as hybrid SQL — the warehouse keeps speaking SQL while FeatureQL owns the definitions:

FeatureQL

/* SQL */
SELECT
    show_promocode_offline,
    COUNT(1) AS num_customers
FROM FEATUREQL(
    SELECT
        customer_id,
        show_promocode_offline := fm.home.show_promocode_offline
    FROM fm.home
    FOR
        customer_id := @BIND_KEYSET(dim_customers_keyset),
        order_id := @BIND_KEYSET(fct_orders_keyset)
)
GROUP BY show_promocode_offline
;

Result

show_promocode_offline BOOLEAN	num_customers BIGINT
false	1
true	3

One registry; many consumers (notebooks, dashboards, training jobs).

Serving

Same decision, live path. Analytics used warehouse tables; serving reads precomputed fields from Redis. The business predicate does not change — VARIANT() swaps the dependencies.

This section requires the serving backend and Redis. The generated Colab notebook installs Redis, verifies a write/read round trip, and connects the serving executor before these cells run.

Seed Redis

Load the precomputed fields first — each customer is a Redis hash with days_since_order and lifetime_value_cents. The next steps only read these keys.

Redis

DEL tutorial:featuremesh:100 tutorial:featuremesh:101 tutorial:featuremesh:102 tutorial:featuremesh:103
HSET tutorial:featuremesh:100 days_since_order 81 lifetime_value_cents 135000
HSET tutorial:featuremesh:101 days_since_order 38 lifetime_value_cents 155000
HSET tutorial:featuremesh:102 days_since_order 43 lifetime_value_cents 150000
HSET tutorial:featuremesh:103 days_since_order 18 lifetime_value_cents 125000

Result

Define serving sources

Connect Redis with SOURCE_REDIS(), then map hash fields with EXTERNAL_REDIS() into features that match the batch types (BIGINT recency and LTV cents).

FeatureQL

CREATE OR REPLACE FEATURES IN fm.home AS
SELECT
    redis_source := SOURCE_REDIS(
        'redis://host.docker.internal:6380'
        WITH (timeout='500ms')
    ),
    redis_key := 'tutorial:featuremesh:' || UNSAFE_CAST(customer_id AS VARCHAR),
    recency_online := CAST(
        EXTERNAL_REDIS(KEY redis_key FIELD 'days_since_order' FROM redis_source)
        AS BIGINT
    ),
    customer_ltv_cents_online := CAST(
        EXTERNAL_REDIS(KEY redis_key FIELD 'lifetime_value_cents' FROM redis_source)
        AS BIGINT
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.HOME.REDIS_SOURCE	CREATED	Feature created as not exists
FM.HOME.REDIS_KEY	CREATED	Feature created as not exists
FM.HOME.RECENCY_ONLINE	CREATED	Feature created as not exists
FM.HOME.CUSTOMER_LTV_CENTS_ONLINE	CREATED	Feature created as not exists

Re-use features for serving

VARIANT() takes show_promocode_offline and replaces warehouse dependencies with the Redis-backed ones — without rewriting the rule:

FeatureQL

CREATE OR REPLACE FEATURE fm.home.show_promocode_online AS
VARIANT(
    fm.home.show_promocode_offline
    REPLACING fm.home.recency, fm.home.customer_ltv_cents
    WITH fm.home.recency_online, fm.home.customer_ltv_cents_online
);

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.HOME.SHOW_PROMOCODE_ONLINE	CREATED	Feature created as not exists

Evaluate the serving variant:

FeatureQL

SELECT
    FM.HOME.CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102, 103]),
    FM.HOME.RECENCY_ONLINE,
    FM.HOME.CUSTOMER_LTV_CENTS_ONLINE,
    FM.HOME.SHOW_PROMOCODE_ONLINE
;

Result

FM.HOME.CUSTOMER_ID BIGINT	FM.HOME.RECENCY_ONLINE BIGINT	FM.HOME.CUSTOMER_LTV_CENTS_ONLINE BIGINT	FM.HOME.SHOW_PROMOCODE_ONLINE BOOLEAN
100	81	135000	TRUE
101	38	155000	TRUE
102	43	150000	TRUE
103	18	125000	FALSE

Customers 100, 101, and 102 return true; customer 103 returns false. One promo definition now runs in two execution contexts.

Compile as prepared statement

PREPARED_STATEMENT() compiles the serving variant for low-latency calls — bind customer_id, get the boolean back.

FeatureQL

CREATE OR REPLACE FEATURE fm.home.show_promocode_online_ps AS
PREPARED_STATEMENT(
    fm.home.show_promocode_online
    USING fm.home.customer_id
);

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.HOME.SHOW_PROMOCODE_ONLINE_PS	CREATED	Feature created as not exists

Prepared

FM.HOME.SHOW_PROMOCODE_ONLINE_PS

Call 1

{"input_table_1": [[100], [101], [102], [103]]}

CUSTOMER_ID BIGINT	SHOW_PROMOCODE_ONLINE_PS BOOLEAN
100	TRUE
101	TRUE
102	TRUE
103	FALSE

Call 2

{"input_table_1": [[100]]}

CUSTOMER_ID BIGINT	SHOW_PROMOCODE_ONLINE_PS BOOLEAN
100	TRUE

Integrate anywhere

Evaluation is an API call. For customer 100:

POST /api/evaluate
Content-Type: application/json

{
    "id": "fm.home.show_promocode_online_ps",
    "inputs": [
        ["100"]
    ]
}

What's next

Real-time segmentation — live Redis updates in more depth
Federated serving — PostgreSQL + Redis in one serving graph
E-commerce — entities, RELATED(), and EXTEND()
SaaS metrics — MRR, cohort NRR, and composable customer health

Last update at: 2026/08/04 11:25:38

On this page

Set up the analytics data Customers Orders Customer OBT Analytics / Training Define entities and keys Map features to columns Write transformations Compute features in batch Serving Seed Redis Define serving sources Re-use features for serving Compile as prepared statement Integrate anywhere What's next