E-commerce data model tutorial

This tutorial builds a complete e-commerce feature model with three entities — customers, orders, and items — and walks through the relationship patterns you'll use in real projects. By the end, you'll understand how to map tables to features, traverse relationships with RELATED(), and enrich arrays with EXTEND().

Create the SQL tables

The dataset has five tables: a customer dimension, order and item fact tables, and two pre-aggregated OBT tables. The OBT tables store denormalized data — each customer's order IDs as an array, and each order's total price.

FeatureQL

/* SQL */
--
CREATE SCHEMA IF NOT exists ecomm;
--
-- ** CUSTOMERS 100 ** --
--
CREATE OR REPLACE TABLE ecomm.dim_customers (
    customer_id BIGINT,
    name VARCHAR,
    time_create TIMESTAMP,
);
--
INSERT INTO ecomm.dim_customers SELECT * FROM (
    VALUES
        (100, 'John Doe', '2025-09-18 14:30:00'),
        (101, 'Jane Doe', '2024-12-08 09:45:00'),
        (102, 'Jack Doe', '2025-02-03 12:00:02')
);
--
-- ** ORDERS 200 ** --
--
CREATE OR REPLACE TABLE ecomm.fct_orders (
    order_id BIGINT,
    order_customer_id BIGINT,
    order_city_name VARCHAR,
    time_create TIMESTAMP,
    time_update TIMESTAMP,
    time_delete TIMESTAMP,
);
--
INSERT INTO ecomm.fct_orders SELECT * FROM (
    VALUES
        (200, 100, 'Barcelona', '2025-09-18 14:30:00', NULL, NULL),
        (201, 101, 'Barcelona', '2024-12-08 09:45:00', NULL, NULL),
        (202, 102, 'Barcelona', '2025-02-03 12:00:02', NULL, NULL),
        (203, 100, 'Madrid', '2025-09-18 14:30:00', NULL, NULL),
);
--
-- ** ITEMS 300 ** --
--
CREATE OR REPLACE TABLE ecomm.fct_items (
    item_id BIGINT,
    item_order_id BIGINT,
    item_product_name VARCHAR,
    price DECIMAL,
    quantity BIGINT,
);
--
INSERT INTO ecomm.fct_items SELECT * FROM (
    VALUES
        (300, 200, 400, 10.05, 2),
        (301, 200, 400, 11.05, 1),
        (302, 201, 401, 12.05, 3),
        (303, 202, 402, 13.05, 2),
        (304, 202, 402, 14.05, 1),
        (305, 202, 402, 15.05, 1),
        (306, 203, 400, 16.05, 1),
);
--
-- ** CUSTOMERS OBT 100 ** --
--
CREATE OR REPLACE TABLE ecomm.agg_customers_obt (
    customer_id BIGINT,
    last_order_id BIGINT,
    orders BIGINT[],
    orders_details ROW(order_id BIGINT, time_create TIMESTAMP)[],
);
--
INSERT INTO ecomm.agg_customers_obt
SELECT
    order_customer_id as customer_id,
    MAX_BY(order_id, time_create) as last_order_id,
    ARRAY_AGG(order_id),
    ARRAY_AGG(ROW(order_id, time_create))
FROM ecomm.fct_orders
GROUP BY order_customer_id;
--
-- ** ORDERS OBT 200 ** --
--
CREATE OR REPLACE TABLE ecomm.agg_orders_obt (
    order_id BIGINT,
    price DECIMAL,
);
--
INSERT INTO ecomm.agg_orders_obt
SELECT item_order_id as order_id, SUM(price*quantity) as price
FROM ecomm.fct_items
GROUP BY item_order_id;

Result

Count BIGINT
4

Define the semantic model

Every FeatureQL model starts with entities and their primary key inputs. This e-commerce model has three entities: CUSTOMERS (IDs in the 100s), ORDERS (200s), and ITEMS (300s). The #ENTITY type annotations on each INPUT() tell FeatureQL which entity a key belongs to — this is how it knows which relationships are valid.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.ECOMM AS
SELECT
    -- Entities
    CUSTOMERS := ENTITY(),  -- ids in the 100s
    ORDERS := ENTITY(),  -- ids in the 200s
    ITEMS := ENTITY(),  -- ids in the 300s
    -- Inputs
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),  -- TODO: Namespace propagation
    ORDER_ID := INPUT(BIGINT#ORDERS),
    ITEM_ID := INPUT(BIGINT#ITEMS),
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.ECOMM.CUSTOMERS	CREATED	Feature created as not exists
FM.ECOMM.ORDERS	CREATED	Feature created as not exists
FM.ECOMM.ITEMS	CREATED	Feature created as not exists
FM.ECOMM.CUSTOMER_ID	CREATED	Feature created as not exists
FM.ECOMM.ORDER_ID	CREATED	Feature created as not exists
FM.ECOMM.ITEM_ID	CREATED	Feature created as not exists

Map columns to features

With entities declared, the next step is mapping table columns to features using EXTERNAL_COLUMNS(). Each mapping declares the key column (BIND TO the entity's input), the value columns, and the source table. Notice the TABLES. prefix — this is a naming convention that groups all raw table mappings together, keeping them separate from derived features.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.ECOMM AS
SELECT
    TABLES.DIM_CUSTOMERS := EXTERNAL_COLUMNS(
        customer_id BIGINT#CUSTOMERS BIND TO CUSTOMER_ID,
        name VARCHAR,
        time_create TIMESTAMP,
        FROM TABLE(ecomm.dim_customers)
    ),
    TABLES.FCT_ORDERS := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        order_customer_id BIGINT#CUSTOMERS,
        order_city_name VARCHAR,
        time_create TIMESTAMP,
        time_update TIMESTAMP,
        time_delete TIMESTAMP,
        FROM TABLE(ecomm.fct_orders)
    ),
    TABLES.FCT_ITEMS := EXTERNAL_COLUMNS(
        item_id BIGINT#ITEMS BIND TO ITEM_ID,
        item_order_id BIGINT#ORDERS,
        item_product_name VARCHAR,
        price DECIMAL(10,2),
        quantity BIGINT,
        FROM TABLE(ecomm.fct_items)
    ),
    TABLES.AGG_CUSTOMERS_OBT := EXTERNAL_COLUMNS(
        customer_id BIGINT#CUSTOMERS BIND TO CUSTOMER_ID,
        last_order_id BIGINT#ORDERS,
        orders ARRAY(BIGINT#ORDERS),
        orders_details ARRAY(ROW(order_id BIGINT#ORDERS)),
        FROM TABLE(ecomm.agg_customers_obt)
    ),
    TABLES.AGG_ORDERS_OBT := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        price DECIMAL(10,2),
        FROM TABLE(ecomm.agg_orders_obt)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.ECOMM.TABLES.DIM_CUSTOMERS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.FCT_ORDERS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.FCT_ITEMS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.AGG_CUSTOMERS_OBT	CREATED	Feature created as not exists
FM.ECOMM.TABLES.AGG_ORDERS_OBT	CREATED	Feature created as not exists

Query the mapped data

Once features are mapped, you can query them by binding values to the entity inputs. The first query binds CUSTOMER_ID and returns the raw dimension row. The second binds ORDER_ID and returns order facts. Notice how FROM FM.ECOMM lets you use short feature names instead of fully qualified FM.ECOMM.TABLES.FCT_ORDERS.

FeatureQL

SELECT
    FM.ECOMM.CUSTOMER_ID,  -- Using the absolute feature name
    FM.ECOMM.TABLES.DIM_CUSTOMERS,
FOR
    FM.ECOMM.CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(ecomm.dim_customers))
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	FM.ECOMM.TABLES.DIM_CUSTOMERS VARCHAR
100	{customer_id: 100, name: John Doe, time_create: 2025-09-18T14:30:00}
101	{customer_id: 101, name: Jane Doe, time_create: 2024-12-08T09:45:00}
102	{customer_id: 102, name: Jack Doe, time_create: 2025-02-03T12:00:02}

FeatureQL

SELECT
    ORDER_ID,  -- Using the relative feature name with FROM
    TABLES.FCT_ORDERS,
FROM FM.ECOMM
FOR
    ORDER_ID := BIND_COLUMNS(order_id FROM TABLE(ecomm.fct_orders))
;

Result

FM.ECOMM.ORDER_ID BIGINT	FM.ECOMM.TABLES.FCT_ORDERS VARCHAR
200	{order_id: 200, order_customer_id: 100, order_city_name: Barcelona, time_create: 2025-09-18T14:30:00, time_update: NULL, time_delete: NULL}
201	{order_id: 201, order_customer_id: 101, order_city_name: Barcelona, time_create: 2024-12-08T09:45:00, time_update: NULL, time_delete: NULL}
202	{order_id: 202, order_customer_id: 102, order_city_name: Barcelona, time_create: 2025-02-03T12:00:02, time_update: NULL, time_delete: NULL}
203	{order_id: 203, order_customer_id: 100, order_city_name: Madrid, time_create: 2025-09-18T14:30:00, time_update: NULL, time_delete: NULL}

Relationship patterns

FeatureQL supports four relationship patterns, all built on the same RELATED() function. The pattern depends on which side of the relationship you're querying from.

Same-entity join (PK to PK)

When two features share the same INPUT(), they align automatically — no RELATED() needed. Here, CUSTOMER_NAME and CUSTOMER_ORDERS both depend on CUSTOMER_ID, so they appear side by side in the result.

FeatureQL

SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME := TABLES.DIM_CUSTOMERS[name],
    CUSTOMER_ORDERS := TABLES.AGG_CUSTOMERS_OBT[orders],
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102))
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	CUSTOMER_NAME VARCHAR	CUSTOMER_ORDERS VARCHAR
100	John Doe	[200, 203]
101	Jane Doe	[201]
102	Jack Doe	[202]

Aggregation (PK to FK)

To count how many orders each customer has, RELATED() aggregates across the foreign key order_customer_id and joins the result back to CUSTOMER_ID. This replaces the SQL pattern of a GROUP BY subquery followed by a LEFT JOIN.

FeatureQL

SELECT
    CUSTOMER_ID,
    NUM_ORDERS := RELATED(SUM(1) GROUP BY TABLES.FCT_ORDERS[order_customer_id] VIA CUSTOMER_ID),
FROM FM.ECOMM
FOR CROSS
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
    ORDER_ID := BIND_VALUES(SEQUENCE(200,203)),
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	NUM_ORDERS VARCHAR
100	2
101	1
102	1

Lookup (FK to PK)

To look up a value from another entity through a foreign key, RELATED() follows the key directly. Here, last_order_id is a foreign key to the orders entity, and RELATED() retrieves the order's city name.

FeatureQL

WITH
    CUSTOMER_CITY_NAME := RELATED(TABLES.FCT_ORDERS[order_city_name] VIA TABLES.AGG_CUSTOMERS_OBT[last_order_id])
SELECT
    CUSTOMER_ID,
    CUSTOMER_CITY_NAME
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102))
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	CUSTOMER_CITY_NAME VARCHAR
100	Barcelona
101	Barcelona
102	Barcelona

Multi-hop (FK to FK)

When the relationship crosses two entity boundaries, you chain through intermediate features. Here, each customer's last_order_id points to an order, and RELATED() aggregates item prices for that order — crossing from customers to orders to items.

FeatureQL

WITH
    LAST_ORDER_ID := TABLES.AGG_CUSTOMERS_OBT[last_order_id],
    LAST_ORDER_PRICE := RELATED(
        SUM(TABLES.FCT_ITEMS[price] * TABLES.FCT_ITEMS[quantity]::DECIMAL) GROUP BY TABLES.FCT_ITEMS[item_order_id]
        VIA LAST_ORDER_ID
    )
SELECT
    CUSTOMER_ID,
    LAST_ORDER_ID,
    LAST_ORDER_PRICE
FROM FM.ECOMM
FOR CROSS
    ITEM_ID := BIND_VALUES(SEQUENCE(300,306)),
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	LAST_ORDER_ID BIGINT	LAST_ORDER_PRICE VARCHAR
100	200	31.15
101	201	36.15
102	202	55.20

Enriching arrays with EXTEND()

When a feature contains an array of foreign keys (like customer_orders), EXTEND() enriches each element with data from the related entity. ZIP() first converts the scalar array into an array of rows, then EXTEND() looks up each order's price.

FeatureQL

SELECT
    CUSTOMER_ID,
    CUSTOMER_ORDERS := TABLES.AGG_CUSTOMERS_OBT[orders],
    CUSTOMER_ORDERS_DETAILS := EXTEND(
        ZIP(CUSTOMER_ORDERS AS order_id)
        WITH TABLES.AGG_ORDERS_OBT[price] AS ORDER_PRICE
    ),
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102))
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	CUSTOMER_ORDERS ARRAY	CUSTOMER_ORDERS_DETAILS VARCHAR
100	[200, 203]	[{order_id: 200, order_price: 31.15}, {order_id: 203, order_price: 16.05}]
101	[201]	[{order_id: 201, order_price: 36.15}]
102	[202]	[{order_id: 202, order_price: 55.20}]

Putting it all together

This query combines all four relationship patterns in a single statement: same-entity alignment for customer name and orders, FK-to-PK lookup for the last order's city, PK-to-FK aggregation for order count, and FK-to-FK aggregation for the last order's total price.

FeatureQL

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
;

Result

FM.ECOMM.CUSTOMER_ID BIGINT	CUSTOMER_NAME VARCHAR	CUSTOMER_ORDERS ARRAY	LAST_ORDER_ID BIGINT	LAST_ORDER_CITY_NAME VARCHAR	NUM_ORDERS BIGINT	LAST_ORDER_PRICE VARCHAR
100	John Doe	[200, 203]	200	Barcelona	2	31.15
101	Jane Doe	[201]	201	Barcelona	1	36.15
102	Jack Doe	[202]	202	Barcelona	1	55.20

Notice the FOR clause binds all three entity inputs — CUSTOMER_ID, ORDER_ID, and ITEM_ID. FeatureQL needs keys for every entity involved in the query's dependency graph, even if the final SELECT only returns customer-level features.

What's next

For a quicker introduction focused on a single use case, see the Customer analytics tutorial . To learn more about RELATED() and EXTEND(), see the RELATED reference and EXTEND reference .

Suggest changes to this page

Last update at: 2026/03/18 16:18:57

On this page

Create the SQL tables Define the semantic model Map columns to features Query the mapped data Relationship patterns Same-entity join (PK to PK)Aggregation (PK to FK)Lookup (FK to PK)Multi-hop (FK to FK)Enriching arrays with EXTEND()Putting it all together What's next