E-commerce

Map a small retail dataset — customers, orders, and items — then walk every relationship shape you’ll need later: same-entity joins, aggregations, lookups, multi-hop paths, and array enrichment with EXTEND().

New to FeatureQL? The homepage companion gives the shortest SQL comparison. You can also start here with this map:

ENTITY() declares the business objects; INPUT(BIGINT#CUSTOMERS) declares a typed customer key.
EXTERNAL_COLUMNS() maps physical table columns to reusable features.
FROM FM.ECOMM loads the persisted model; FOR binds the concrete customer, order, or item keys to evaluate.
Features on the same entity align directly. RELATED() crosses entity relationships for lookups and aggregations.
NESTED bindings provide keys used inside a relationship without multiplying the outer result.

Run Data and Model before the relationship examples. After a notebook restart, rerun those sections rather than executing a downstream cell in isolation.

Data

Three customers, four orders, seven line items. Two small OBT helpers store each customer’s order-id array (plus last order) and each order’s total price. They are tutorial shortcuts that keep the array/EXTEND() examples focused; a production model could derive or materialize them upstream.

FeatureQL

/* SQL */
SELECT customer_id, name
FROM tutorial_ecomm.customers
ORDER BY customer_id;

Result

CUSTOMER_ID BIGINT	NAME VARCHAR
100	John Doe
101	Jane Doe
102	Jack Doe

John has two orders (Barcelona + Madrid). Jane and Jack have one each.

Model

Declare entities and primary-key inputs. The #CUSTOMERS / #ORDERS / #ITEMS annotations are how FeatureQL knows which relationships are valid.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.ECOMM AS
SELECT
    customers := ENTITY(),
    orders := ENTITY(),
    items := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    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 tables with EXTERNAL_COLUMNS(). TABLES.* holds raw sources; queries below compose on short names via FROM FM.ECOMM.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.ECOMM AS
SELECT
    tables.customers := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        name VARCHAR,
        time_create TIMESTAMP
        FROM TABLE(tutorial_ecomm.customers)
    ),
    tables.orders := EXTERNAL_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        order_customer_id BIGINT#customers,
        order_city_name VARCHAR,
        time_create TIMESTAMP
        FROM TABLE(tutorial_ecomm.orders)
    ),
    tables.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(tutorial_ecomm.items)
    ),
    tables.customer_orders := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        last_order_id BIGINT#orders,
        orders ARRAY(BIGINT#orders)
        FROM TABLE(tutorial_ecomm.customer_orders)
    ),
    tables.order_totals := EXTERNAL_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        price DECIMAL(10,2)
        FROM TABLE(tutorial_ecomm.order_totals)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.ECOMM.TABLES.CUSTOMERS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.ORDERS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.ITEMS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.CUSTOMER_ORDERS	CREATED	Feature created as not exists
FM.ECOMM.TABLES.ORDER_TOTALS	CREATED	Feature created as not exists

Peek at the bindings

Bind customer ids → dimension rows. Bind order ids → order facts.

FeatureQL

SELECT
    CUSTOMER_ID,
    NAME := TABLES.CUSTOMERS[name],
    TIME_CREATE := TABLES.CUSTOMERS[time_create]
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_COLUMNS(customer_id FROM TABLE(tutorial_ecomm.customers))
ORDER BY CUSTOMER_ID;

Result

CUSTOMER_ID BIGINT	NAME VARCHAR	TIME_CREATE VARCHAR
100	John Doe	2025-09-18T14:30:00
101	Jane Doe	2024-12-08T09:45:00
102	Jack Doe	2025-02-03T12:00:02

FeatureQL

SELECT
    ORDER_ID,
    ORDER_CUSTOMER_ID := TABLES.ORDERS[order_customer_id],
    CITY := TABLES.ORDERS[order_city_name],
    TIME_CREATE := TABLES.ORDERS[time_create]
FROM FM.ECOMM
FOR
    ORDER_ID := BIND_COLUMNS(order_id FROM TABLE(tutorial_ecomm.orders))
ORDER BY ORDER_ID;

Result

ORDER_ID BIGINT	ORDER_CUSTOMER_ID BIGINT	CITY VARCHAR	TIME_CREATE VARCHAR
200	100	Barcelona	2025-09-18T15:00:00
201	101	Barcelona	2024-12-08T09:45:00
202	102	Barcelona	2025-02-03T12:00:02
203	100	Madrid	2025-09-18T14:30:00

Same-entity join (PK → PK)

Features that share CUSTOMER_ID align automatically — no RELATED() needed.

FeatureQL

SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME := TABLES.CUSTOMERS[name],
    CUSTOMER_ORDERS := TABLES.CUSTOMER_ORDERS[orders]
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102])
ORDER BY CUSTOMER_ID;

Result

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

John → orders [200, 203].

Aggregation (PK → FK)

Count orders per customer: aggregate on the FK, join back to the PK. Replaces a GROUP BY subquery + LEFT JOIN.

FeatureQL

SELECT
    CUSTOMER_ID,
    NUM_ORDERS := RELATED(SUM(1) GROUP BY TABLES.ORDERS[order_customer_id] VIA CUSTOMER_ID)
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102]),
    NESTED ORDER_ID := BIND_VALUES(ARRAY[200, 201, 202, 203])
ORDER BY CUSTOMER_ID;

Result

CUSTOMER_ID BIGINT	NUM_ORDERS BIGINT
100	2
101	1
102	1

John 2, Jane 1, Jack 1.

Lookup (FK → PK)

Follow last_order_id to the order’s city.

FeatureQL

WITH
    LAST_ORDER_CITY := RELATED(TABLES.ORDERS[order_city_name] VIA TABLES.CUSTOMER_ORDERS[last_order_id])
SELECT
    CUSTOMER_ID,
    LAST_ORDER_CITY
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102])
ORDER BY CUSTOMER_ID;

Result

CUSTOMER_ID BIGINT	LAST_ORDER_CITY VARCHAR
100	Barcelona
101	Barcelona
102	Barcelona

All three current last orders are in Barcelona.

Multi-hop (FK → FK)

From the customer’s last order, sum item price × quantity on that order.

FeatureQL

WITH
    LAST_ORDER_ID := TABLES.CUSTOMER_ORDERS[last_order_id],
    LAST_ORDER_PRICE := RELATED(
        SUM(TABLES.ITEMS[price] * TABLES.ITEMS[quantity]::DECIMAL) GROUP BY TABLES.ITEMS[item_order_id]
        VIA LAST_ORDER_ID
    )
SELECT
    CUSTOMER_ID,
    LAST_ORDER_ID,
    LAST_ORDER_PRICE
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102]),
    NESTED ITEM_ID := BIND_VALUES(ARRAY[300, 301, 302, 303, 304, 305, 306])
ORDER BY CUSTOMER_ID;

Result

CUSTOMER_ID BIGINT	LAST_ORDER_ID BIGINT	LAST_ORDER_PRICE DECIMAL
100	200	31.15
101	201	36.15
102	202	55.20

John’s last order 200 → 31.15. Jane 36.15. Jack 55.20.

Enrich arrays with `EXTEND()`

ZIP the order-id array into rows, then look up each order’s total price.

FeatureQL

SELECT
    CUSTOMER_ID,
    CUSTOMER_ORDERS := TABLES.CUSTOMER_ORDERS[orders],
    CUSTOMER_ORDERS_DETAILS := EXTEND(
        ZIP(CUSTOMER_ORDERS AS order_id)
        WITH TABLES.ORDER_TOTALS[price] AS ORDER_PRICE
    )
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102])
ORDER BY CUSTOMER_ID;

Result

CUSTOMER_ID BIGINT	CUSTOMER_ORDERS ARRAY	CUSTOMER_ORDERS_DETAILS VARBINARY
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}]

John → order 200 at 31.15, order 203 at 16.05.

All four patterns together

One query: name + order array, last-order city, order count, last-order price. Bind every entity that appears in the dependency graph (NESTED for orders and items).

FeatureQL

WITH
    CUSTOMER_NAME := TABLES.CUSTOMERS[name],
    CUSTOMER_ORDERS := TABLES.CUSTOMER_ORDERS[orders],
    LAST_ORDER_ID := TABLES.CUSTOMER_ORDERS[last_order_id],
    LAST_ORDER_CITY := LAST_ORDER_ID.RELATED(TABLES.ORDERS[order_city_name]),
    NUM_ORDERS := CUSTOMER_ID.RELATED(SUM(1) GROUP BY TABLES.ORDERS[order_customer_id]),
    LAST_ORDER_PRICE := LAST_ORDER_ID.RELATED(
        SUM(TABLES.ITEMS[price] * TABLES.ITEMS[quantity]::DECIMAL)
        GROUP BY TABLES.ITEMS[item_order_id]
    )
SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    CUSTOMER_ORDERS,
    LAST_ORDER_ID,
    LAST_ORDER_CITY,
    NUM_ORDERS,
    LAST_ORDER_PRICE
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[100, 101, 102]),
    NESTED ORDER_ID := BIND_VALUES(ARRAY[200, 201, 202, 203]),
    NESTED ITEM_ID := BIND_VALUES(ARRAY[300, 301, 302, 303, 304, 305, 306])
ORDER BY CUSTOMER_ID;

Result

CUSTOMER_ID BIGINT	CUSTOMER_NAME VARCHAR	CUSTOMER_ORDERS ARRAY	LAST_ORDER_ID BIGINT	LAST_ORDER_CITY VARCHAR	NUM_ORDERS BIGINT	LAST_ORDER_PRICE DECIMAL
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

What's next

Analytics overview — concept map for this series
SaaS metrics — MRR and customer health on a persisted model
OBT modeling — deeper ARRAY(ROW) work
RELATED / EXTEND — full syntax

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

On this page

Data Model Peek at the bindings Same-entity join (PK → PK)Aggregation (PK → FK)Lookup (FK → PK)Multi-hop (FK → FK)Enrich arrays with EXTEND()All four patterns together What's next