Common patterns

This guide explains common patterns when working with arrays of rows.

Array function vs transform()

SELECT
    ARRAY_SUM(ARRAY(1, 2, 3)) AS sum_of_array,
    ARRAY(1, 2, 3).TRANSFORM(`SELECT SUM()`).UNWRAP() AS transformed_array

Star-schema vs OBT

FeatureMesh supports both star-schema and OBT models.

In the case of star-schema, you will use RELATED() to join the fact table with the dimension tables.

In the case of OBT, you will already have a pre-materialized column that contains facts related to each entity.

Your OBT column is logically equivalent to: RELATED(ARRAY_AGG(fact) VIA entity_id). It is just more efficient to query.

Notes:

Directly using the aggregation function with EXTEND:

country,
revenue_by_country := EXTEND(
    ROW(order_country)
    WITH SUM(price) GROUP BY order_country as revenue_by_country
    VIA order_country BOUND TO order_country
)[revenue_by_country]

Is equivalent to transforming with filtering and aggregation on an array of rows generated via ARRAY_AGG(foreign_key) GROUP BY primary_key.

country,
-- country_orders := ARRAY_AGG(ROW(order_id, price)) GROUP BY order_country, -- That we cannot do because of the need to bind FK to PK
country_orders := EXTEND(
    ROW(country)
    WITH ARRAY_AGG(ROW(order_id, price)) GROUP BY order_country as country_orders
    VIA country BOUND TO order_country
)[country_orders], -- Can be persisted
revenue_by_country := country_orders.TRANSFORM(SELECT SUM(price)).unwrap_one(),

The latter is more verbose but also more flexible as it allows any sort of operation on the "country_orders" array of rows that can be persisted.