Analytics, serving and experiments with FeatureMesh

This tutorial takes one promotion rule from a three-line definition to a measured experiment in production. The business logic is written once and never rewritten.

It reproduces the FeatureMesh homepage How it works walkthrough on a small dataset. The work is split the way it is split in a real team:

1. Foundations. Data engineers map the tables and define the shared metrics. This is done once.
2. A new segment. Someone (or an AI agent) adds a promotion rule on top. It takes three lines.
3. Production. The same rule is served from Redis through a prepared statement.
4. Experiment. A variant of the rule is proposed, backtested, assigned, deployed and measured.

This tutorial assumes you recognize entities, bindings, and RELATED(). If those are new, complete the FeatureQL homepage companion first. Parts 1 and 2 run on DuckDB alone. Part 3 and the deployment step of part 4 also require the serving backend and Redis.

Meet the data

The example has four customers, fifteen orders, and a short log of exposure events. All of it is gold-layer data: clean tables, each row addressable by a key.

The data covers two periods, split by the launch date of the promotion (2026-02-01):

• Before launch: customers and their first ten orders. Parts 1 to 3 use only this data to define the rule and serve it.
• After launch: five more orders and the exposure log. They are generated here on purpose for part 4, where we run an experiment on the rule and measure what customers bought after being exposed to it.

Customers

The customer dimension. The IDs will become typed customer keys in the feature model.

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);
--
SELECT customer_id, name, created_at FROM home.dim_customers ORDER BY customer_id;

Orders

Each order points to a customer and carries the price and timestamp needed for lifetime value and recency.

Orders 1001 to 1010 predate the launch. The promotion rule is computed from them. Orders 1011 to 1015 come after the launch. The rule ignores them, and part 4 uses them as the revenue of the experiment.

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'),
        -- After the 2026-02-01 launch: used by the experiment analysis
        (1011, 102, 150.00, TIMESTAMP '2026-02-02 09:10:00'),
        (1012, 100, 300.00, TIMESTAMP '2026-02-05 12:15:00'),
        (1013, 101, 250.00, TIMESTAMP '2026-02-08 20:05:00'),
        (1014, 103, 500.00, TIMESTAMP '2026-02-09 11:00:00'),
        (1015, 102, 200.00, TIMESTAMP '2026-02-10 16:40:00')
) AS t(order_id, order_customer_id, price, created_at);
--
SELECT order_id, order_customer_id, price, created_at FROM home.fct_orders ORDER BY order_id;

Exposure events

You will not need this table before part 4. It is created now so that all the data setup sits in one place.

It records each time a customer opened the page where the promotion decision is taken, from 2026-02-02 onwards. Revenue is not stored here: it stays in the orders table, which is its single source.

CREATE OR REPLACE TABLE home.fct_events AS
SELECT
    event_id::BIGINT AS event_id,
    event_customer_id::BIGINT AS event_customer_id,
    event_type,
    created_at
FROM (
    VALUES
        (5001, 100, 'exposure', TIMESTAMP '2026-02-03 10:00:00'),
        (5002, 102, 'exposure', TIMESTAMP '2026-02-04 18:30:00'),
        (5003, 101, 'exposure', TIMESTAMP '2026-02-06 08:45:00'),
        (5004, 102, 'exposure', TIMESTAMP '2026-02-12 09:00:00')
) AS t(event_id, event_customer_id, event_type, created_at);
--
SELECT event_id, event_customer_id, event_type, created_at FROM home.fct_events ORDER BY event_id;

The post-launch rows are chosen to exercise the experiment analysis of part 4. Read the orders and the exposures together and you will find two deliberate traps:

• Customer 102 places order 1011 two days before their first exposure.
• Customer 103 places order 1014 and is never exposed.

A correct analysis must ignore both orders.

Part 1: Foundations, built once

Everything in this part is the job of data engineers. They do it once. Nothing here is specific to the promotion.

Map the tables

The next statement creates the fm.home feature model in one pass:

• customers, orders and events become entities.
• Their integer IDs become distinct typed keys. A customer ID can no longer be compared to an order ID by mistake.
• EXTERNAL_COLUMNS() maps warehouse columns to source features.
• KEYSET() records how to enumerate every customer, order and event.

The events table is mapped here with the rest. Mapping a source is foundation work, whatever uses it later.

CREATE OR REPLACE FEATURES IN FM.HOME AS
PERSIST
    -- Entities
    customers := ENTITY(),
    orders := ENTITY(),
    events := ENTITY(),
    -- Primary keys
    customer_id := INPUT(BIGINT#customers),
    order_id := INPUT(BIGINT#orders),
    event_id := INPUT(BIGINT#events),
    -- 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],
    -- Application events: promotion exposures
    tables.fct_events := EXTERNAL_COLUMNS(
        event_id BIGINT#events BIND TO event_id,
        event_customer_id BIGINT#customers,
        event_type VARCHAR,
        created_at TIMESTAMP
        FROM TABLE(home.fct_events)
    ),
    event_customer_id := tables.fct_events[event_customer_id],
    event_type := tables.fct_events[event_type],
    event_created_at := tables.fct_events[created_at],
    -- 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'
    ),
    fct_events_keyset := KEYSET(
        events,
        'SELECT event_id AS "fm.home.event_id"
         FROM home.fct_events'
    )
;

The BIND TO clauses identify each table's entity key. Names such as order_price, order_created_at and event_type then sit above the tables.* mappings. From here on, formulas depend on feature names. They never mention a physical table again.

Define the shared metrics

Lifetime value and recency are not promotion logic. Finance, marketing and support all need them. They belong to the foundations and get one definition each.

CREATE OR REPLACE FEATURES IN FM.HOME AS
PERSIST
    -- Launch date of the promotion: both metrics are computed as of this date
    reference_date := TIMESTAMP '2026-02-01',
    customer_ltv := customer_id.RELATED(
        SUM(order_price)
        FILTER (WHERE order_created_at < reference_date)
        GROUP BY (order_customer_id)
    ),
    -- Cents keep rules BIGINT-comparable with Redis serving fields
    customer_ltv_cents := (customer_ltv::DECIMAL(18,2) * 100)::BIGINT,
    last_order_at := customer_id.RELATED(
        MAX(order_created_at)
        FILTER (WHERE order_created_at < reference_date)
        GROUP BY (order_customer_id)
    ),
    recency := DATE_DIFF(reference_date, last_order_at, 'day')
;

• reference_date is the launch date of the promotion. It is a named feature, so the date is written once.
• customer_ltv sums the prices of the customer's orders placed before that date.
• customer_ltv_cents is the same value as integer cents. The serving data in part 3 uses this representation, so the rule never compares floating-point numbers.
• last_order_at is the customer's latest order placed before that date.
• recency counts the days between last_order_at and the reference date.

Both metrics are "as of launch", by the same mechanism: a filter on reference_date. That is why the five post-launch orders do not move them, and why the batch values will equal the Redis values in part 3.

Part 2: A new segment in three lines

The foundations exist. Now marketing wants a promotion for valuable customers who have gone quiet: lifetime value above 1000, last order more than 30 days ago.

The author of this rule needs no knowledge of tables, joins or grains. They need the names of two metrics.

CREATE OR REPLACE FEATURES IN FM.HOME AS
PERSIST
    promo_min_recency_days := 30,
    promo_min_ltv_cents := 100000,
    show_promocode_offline := recency > promo_min_recency_days
    AND customer_ltv_cents > promo_min_ltv_cents
;

The two thresholds are named features, not numbers buried in the predicate. This costs two lines and pays off in part 4: an experiment will replace one threshold without touching the rule.

Run it in batch

Use the keysets to evaluate the rule for every customer:

RETURN
    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)
;

Customers 100, 101 and 102 receive the promotion. Customer 103 has enough lifetime value, but their last order is only 18 days old.

FeatureQL can also sit inside a regular SQL query. Here FeatureQL supplies the decision and the outer SQL does a grouped count:

SELECT
    show_promocode_offline,
    COUNT() AS num_customers
FROM FEATUREQL(
    RETURN
        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)
;

Three eligible customers, one ineligible. A dashboard, a notebook or a training job can consume the same persisted definition.

Part 3: Run it in production

The batch rule reads recency and lifetime value from warehouse tables. In production those two values are precomputed in Redis. Only the data-source features change. The rule itself stays as written in part 2.

The rest of this part requires the serving backend and Redis.

Seed Redis

Load one Redis hash per customer. Each hash holds the two values the rule needs: days_since_order and lifetime_value_cents.

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

Map the online sources

This is foundation work again, done once by engineers. SOURCE_REDIS() declares the connection. EXTERNAL_REDIS() maps the hash fields to recency_online and customer_ltv_cents_online.

CREATE OR REPLACE FEATURES IN fm.home AS
PERSIST
    redis_source := SOURCE_REDIS(
        'redis://host.docker.internal:6380'
        WITH (timeout='500ms')
    ),
    redis_key := 'tutorial:featuremesh:' || CAST(KEY_TO_SCALAR(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
    )
;

The online features have the same types as their batch counterparts. That is what makes them valid replacements.

Reuse the rule

VARIANT() takes show_promocode_offline and swaps its two warehouse dependencies for the Redis-backed ones:

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
);

show_promocode_online is not a second implementation. It is the same formula with a different path to its inputs. If the rule changes, both versions change.

Evaluate it for the four customers:

RETURN
    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
;

The results match the batch query: true for 100, 101 and 102, false for 103.

Compile it as a prepared statement

PREPARED_STATEMENT() compiles the variant into an endpoint. Its contract is small: send a customer_id, receive the decision.

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

Creating the statement writes its definition to the registry. REFRESH is the step that deploys it to the serving engine:

REFRESH FEATURES FM.HOME.SHOW_PROMOCODE_ONLINE_PS;

Call it for all customers, then for customer 100 alone:

FM.HOME.SHOW_PROMOCODE_ONLINE_PS

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

{"input_table_1": [[100]]}

Call it from an application

The same prepared statement is available through the evaluation API. A request for customer 100 looks like this:

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

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

The application now depends on one thing: the id fm.home.show_promocode_online_ps. Part 4 changes what happens behind that id without changing the application.

Part 4: Experiment

Someone proposes to wait longer before showing the promotion: 40 days of inactivity instead of 30. The claim is that customers who come back on their own should not get a discount. This part tests that claim.

Propose a variant

The test rule is the existing rule with one threshold replaced:

CREATE OR REPLACE FEATURES IN FM.HOME AS
PERSIST
    promo_min_recency_days_test := 40,
    show_promocode_offline_test := VARIANT(
        show_promocode_offline REPLACING promo_min_recency_days WITH promo_min_recency_days_test
    )
;

No logic was copied. show_promocode_offline_test will follow any future change to the base rule, except for the threshold it overrides.

Backtest before exposing anyone

Evaluate both rules on every customer. This costs one batch query and involves no production traffic.

RETURN
    customer_id,
    recency,
    control := show_promocode_offline,
    test := show_promocode_offline_test
FROM FM.HOME
FOR
    customer_id := @BIND_KEYSET(dim_customers_keyset),
    order_id := @BIND_KEYSET(fct_orders_keyset)
;

The two rules disagree on one customer: 101, whose last order is 38 days old. That is the population the experiment is really about. If the backtest showed no difference, or a difference on half the customer base, you would know before shipping anything.

Assign customers to arms

SAMPLE_BOOL() hashes its first argument and returns true for the given share of values. The same customer always gets the same answer. The suffix 'promo_40d' is a salt: another experiment with another salt produces an independent split.

CREATE OR REPLACE FEATURES IN FM.HOME AS
PERSIST
    promo_in_test := SAMPLE_BOOL(
        KEY_TO_SCALAR(customer_id)::VARCHAR || 'promo_40d',
        0.5e0
    ),
    promo_min_recency_days_assigned := IF(
        promo_in_test,
        promo_min_recency_days_test,
        promo_min_recency_days
    ),
    show_promocode_offline_experiment := VARIANT(
        show_promocode_offline REPLACING promo_min_recency_days WITH promo_min_recency_days_assigned
    )
;

The experiment is carried by the threshold, not by the rule:

• promo_in_test decides the arm.
• promo_min_recency_days_assigned is 40 in the test arm and 30 in the control arm.
• show_promocode_offline_experiment is the base rule reading that per-customer threshold.

Check the assignment:

RETURN
    customer_id,
    promo_in_test,
    promo_min_recency_days_assigned,
    show_promocode_offline_experiment
FROM FM.HOME
FOR
    customer_id := @BIND_KEYSET(dim_customers_keyset),
    order_id := @BIND_KEYSET(fct_orders_keyset)
;

Customers 101 and 103 are in the test arm. Customer 101 loses the promotion, as the backtest predicted.

Deploy behind the same endpoint

The online experiment is one VARIANT() of the base rule. It replaces the two warehouse inputs with their Redis versions and the threshold with the assigned one:

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

Now point the existing prepared statement at it. The id and the contract are unchanged, so the application is not redeployed.

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

At this point the registry holds the new definition, but the serving engine still answers with the old rule. Replacing a prepared statement does not deploy it. REFRESH does:

REFRESH FEATURES FM.HOME.SHOW_PROMOCODE_ONLINE_PS;

This two-step sequence is deliberate. You can replace, review and test a definition in the registry while production keeps its current behavior. The switch happens when you refresh, and only then.

Call the endpoint again:

FM.HOME.SHOW_PROMOCODE_ONLINE_PS

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

{"input_table_1": [[101]]}

Customer 101 now receives false. The experiment is live.

Measure after first exposure

An experiment result is only valid on customers who actually met the decision, and only on what they did afterwards. Two mistakes are common:

• Counting customers who were assigned but never opened the page. They dilute the effect.
• Counting orders placed before the customer saw anything. They cannot be caused by the treatment.

The outcome metrics encode both rules once, as features:

CREATE OR REPLACE FEATURES IN FM.HOME AS
PERSIST
    -- First time the customer met the promotion decision
    first_exposure_at := customer_id.RELATED(
        MIN(event_created_at)
        FILTER (WHERE event_type = 'exposure')
        GROUP BY (event_customer_id)
    ),
    is_exposed := first_exposure_at IS NOT NULL,
    -- Every order of the customer, nested on the customer
    customer_order_history := customer_id.RELATED(
        ARRAY_AGG(ROW(order_created_at AS ordered_at, order_price AS price))
        GROUP BY (order_customer_id)
    ),
    -- Orders count only from the first exposure onwards
    post_exposure := CARRY(
        first_exposure_at AS exposed_at
        INTO customer_order_history
    ).TRANSFORM(
        RETURN
            orders := COUNT() FILTER (WHERE ordered_at >= exposed_at),
            revenue := SUM(price) FILTER (WHERE ordered_at >= exposed_at)
    )[1],
    orders_after_exposure := post_exposure[orders],
    revenue_after_exposure := post_exposure[revenue]
;

• first_exposure_at is the customer's earliest exposure event. It is NULL for a customer who was never exposed.
• customer_order_history nests each customer's orders as an array of rows on the customer. It reads order_price and order_created_at, the same order features the rule is built on.
• post_exposure carries the exposure date into that array and keeps only the orders placed at or after it.

No new source was needed for revenue. The foundations of part 1 already had it.

These are foundation-grade metrics. The next experiment reuses them as they are.

Look at them per customer first:

RETURN
    customer_id,
    arm := IF(promo_in_test, 'test', 'control'),
    show_promocode_offline_experiment,
    first_exposure_at,
    orders_after_exposure,
    revenue_after_exposure,
FROM fm.home
FOR
    customer_id := @BIND_KEYSET(dim_customers_keyset),
    order_id := @BIND_KEYSET(fct_orders_keyset),
    event_id := @BIND_KEYSET(fct_events_keyset),
;

The two traps from the data section are handled. Customer 102 counts one order for 200: order 1011 (150), placed before exposure, is excluded. Customer 103 was never exposed, so order 1014 (500) is not attributed to the experiment.

Then aggregate per arm:

RETURN
    arm := IF(promo_in_test, 'test', 'control'),
    exposed_customers := COUNT() FILTER (WHERE is_exposed) GROUP BY (arm),
    promocodes_shown := COUNT()
    FILTER (WHERE is_exposed AND show_promocode_offline_experiment)
    GROUP BY (arm),
    buyers := COUNT()
    FILTER (WHERE is_exposed AND orders_after_exposure > 0)
    GROUP BY (arm),
    revenue := SUM(revenue_after_exposure)
    FILTER (WHERE is_exposed)
    GROUP BY (arm)
FROM FM.HOME
FOR
    customer_id := @BIND_KEYSET(dim_customers_keyset),
    order_id := @BIND_KEYSET(fct_orders_keyset),
    event_id := @BIND_KEYSET(fct_events_keyset)
;

Read the two rows side by side:

• Control: two exposed customers, both shown the promotion, both bought, 500 in revenue.
• Test: one exposed customer, not shown the promotion, bought anyway, 250 in revenue.

With four customers this proves nothing statistically, and the tutorial does not pretend otherwise. What it shows is the method. A naive query summing all post-launch orders per arm would report 650 for control and 750 for test, and would conclude that the test arm earns more. Both extra amounts are orders the experiment could not have caused.

What you built

• Foundations: three entities, their sources, and two shared metrics. Written once by engineers.
• A segment: three lines on top of the foundations.
• A production endpoint: the same rule, reading Redis, behind a stable id.
• An experiment: one replaced threshold, backtested in batch, deployed with REFRESH, measured after first exposure.

The promotion predicate was written exactly once, in part 2.

What's next

Continue with Real-time segmentation for a deeper Redis workflow, or Federated serving to combine PostgreSQL and Redis in one feature graph.