RELATED() vs EXTEND()

Both RELATED() and EXTEND() join data across entities, but they serve different levels of complexity. RELATED() is concise syntax for the most common cross-entity patterns. EXTEND() is the general-purpose mechanism that handles everything, including cases RELATED() cannot express.

Rule of thumb: use RELATED() when you can, switch to EXTEND() when you need to work with multiple foreign keys or require explicit control over the binding logic.

Foreign key to primary key (lookup)

The most common pattern: you have a foreign key on one entity and want to look up a value from the related entity. Both functions produce the same result — RELATED() just needs less syntax:

SELECT
    CUSTOMER_ID,
    ORDER_CITY_RELATED := RELATED(TABLES.FCT_ORDERS[order_city_name] VIA TABLES.AGG_CUSTOMERS_OBT[last_order_id]),
    ORDER_CITY_EXTEND := EXTEND(
        ROW(TABLES.AGG_CUSTOMERS_OBT[last_order_id] as last_order_id)
        WITH TABLES.FCT_ORDERS[order_city_name] as CITY_NAME
        VIA last_order_id BIND TO ORDER_ID
    )[CITY_NAME]
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
;

With RELATED(), you specify what to fetch and which foreign key to follow. With EXTEND(), you explicitly construct a row, declare which features to enrich it with, and specify the binding between the foreign key and the target entity's input.

Foreign key to foreign key (aggregation)

When aggregating across a relationship — for example, counting how many orders belong to each customer — both functions work, but the syntax differs more:

SELECT
    CUSTOMER_ID,
    NUM_ORDERS_RELATED := RELATED(SUM(1) GROUP BY TABLES.FCT_ORDERS[order_customer_id] VIA CUSTOMER_ID),
    NUM_ORDERS_EXTEND := EXTEND(
        ROW(CUSTOMER_ID as CUSTOMER_ID)
        WITH SUM(1) GROUP BY TABLES.FCT_ORDERS[order_customer_id] as num_orders
        VIA CUSTOMER_ID BIND TO TABLES.FCT_ORDERS[order_customer_id]
    )[num_orders]
FROM FM.ECOMM
FOR CROSS
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
    ORDER_ID := BIND_VALUES(SEQUENCE(200,203)),
;

RELATED() expresses the aggregation and grouping in a single clause. EXTEND() requires you to build the row, declare the aggregation, and specify the binding explicitly. For simple aggregations, RELATED() is clearly more readable.

When to use EXTEND()

EXTEND() becomes necessary when:

• You need to enrich an existing array of rows with data from another entity (e.g., adding product details to order line items).
• The relationship involves multiple foreign keys or composite keys.
• You want to combine EXTEND() with TRANSFORM() for filtering or reshaping the enriched data.

See the RELATED() reference for the full syntax, and EXTEND() for advanced enrichment patterns.