Federated serving tutorial

This tutorial joins customer state in PostgreSQL with order details in Redis, then compiles the cross-store feature into a prepared statement.

This is an advanced serving tutorial. Complete Real-time segmentation first. The Colab notebook installs PostgreSQL and Redis, verifies write/read round trips, and wires both into the serving executors; the website playground requires a serving environment with those executors configured.

The data model

The customer table in PostgreSQL contains customer_id and last_order_id. Redis stores each order as a JSON object keyed by order ID. The query must:

1. Read a customer's last order ID from PostgreSQL.
2. Build the matching Redis key.
3. Parse the order JSON and return its price.

Map both sources

The model declares CUSTOMERS and ORDERS entities and creates both source connections. It uses EXTERNAL_SQL() for the parameterized PostgreSQL lookup because persisted EXTERNAL_COLUMNS() mappings do not yet retain this source reference correctly. In the SQL template, %…POSTGRES_SOURCE[table] identifies the registered table and SELF.customer_id receives the bound customer key. The entity annotation on last_order_id provides the link between stores:

CREATE OR REPLACE FEATURES IN FM.TUTORIALS.ONLINE_FEDERATED AS
SELECT
    CUSTOMERS := ENTITY(),
    ORDERS := ENTITY(),
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
    POSTGRES_SOURCE := SOURCE_JDBC(
        'postgresql://featuremesh:featuremesh@host.docker.internal:5433/featuremesh?sslmode=disable'
        WITH (
            tables=ARRAY['tutorial_online_customers'],
            timeout='500ms'
        )
    ),
    REDIS_SOURCE := SOURCE_REDIS(
        'redis://host.docker.internal:6380'
        WITH (timeout='500ms')
    ),
    CUSTOMER_DETAILS := EXTERNAL_SQL(
        `SELECT customer_id, last_order_id
         FROM %FM.TUTORIALS.ONLINE_FEDERATED.POSTGRES_SOURCE[tutorial_online_customers]`
        ON `SELF.customer_id=%FM.TUTORIALS.ONLINE_FEDERATED.CUSTOMER_ID`
        AS ROW(customer_id BIGINT#CUSTOMERS, last_order_id BIGINT#ORDERS)
    ),
    LAST_ORDER_ID := CUSTOMER_DETAILS[last_order_id],
    REDIS_KEY := 'tutorial:federated:order:' || UNSAFE_CAST(LAST_ORDER_ID AS VARCHAR),
    ORDER_DETAILS_JSON := EXTERNAL_REDIS(KEY REDIS_KEY FROM REDIS_SOURCE),
    ORDER_DETAILS := JSON_PARSE_AS(
        ORDER_DETAILS_JSON,
        TYPE 'ROW(order_id BIGINT, price_cents BIGINT)'
    ),
    LAST_ORDER_PRICE_CENTS := ORDER_DETAILS[price_cents]
;

Install both connections together before executing the model:

REFRESH FEATURES
    FM.TUTORIALS.ONLINE_FEDERATED.POSTGRES_SOURCE,
    FM.TUTORIALS.ONLINE_FEDERATED.REDIS_SOURCE
;

Join PostgreSQL to Redis

CUSTOMER_DETAILS retrieves the relational row. Its LAST_ORDER_ID builds the Redis key directly, and the parsed order details provide the final price:

SELECT
    FM.TUTORIALS.ONLINE_FEDERATED.CUSTOMER_ID := BIND_VALUES(ARRAY[1, 2, 3]),
    FM.TUTORIALS.ONLINE_FEDERATED.LAST_ORDER_ID,
    FM.TUTORIALS.ONLINE_FEDERATED.LAST_ORDER_PRICE_CENTS
;

FeatureMesh follows the dependency graph in order: PostgreSQL first, then Redis. The caller supplies only customer IDs.

Prepare the federated feature

The prepared statement captures the same cross-store graph and exposes CUSTOMER_ID as its input:

CREATE OR REPLACE FEATURE FM.TUTORIALS.ONLINE_FEDERATED.LAST_ORDER_PRICE_PS AS
PREPARED_STATEMENT(
    FM.TUTORIALS.ONLINE_FEDERATED.LAST_ORDER_PRICE_CENTS
    USING FM.TUTORIALS.ONLINE_FEDERATED.CUSTOMER_ID
);

Refresh the compiled definition after persistence:

REFRESH FEATURES FM.TUTORIALS.ONLINE_FEDERATED.LAST_ORDER_PRICE_PS;

Multiple serving requests can batch different customer sets while preserving separate expected outputs:

FM.TUTORIALS.ONLINE_FEDERATED.LAST_ORDER_PRICE_PS

{"input_table_1": [[2], [1]]}

{"input_table_1": [[3]]}

What's next

• Multi-source queries — the core federation pattern
• JDBC data sources — relational mappings and parameterized views
• Redis data sources — low-latency key lookups
• Prepared statements — compile features for serving