OBT modeling

Work an entity-centric one-big-table: each account holds nested contacts, opportunities (with line items), and activities. Filter and aggregate inside those arrays, pick the top deal and line item, derive a lifecycle stage, and pack the metrics into one profile ROW — without fan-out joins across arrays.

This advanced tutorial assumes entity bindings from E-commerce . Read each TRANSFORM(SELECT …) as a SQL query scoped to one account's nested array. Run Data and Model before the analysis, and treat the profile section as the capstone.

Data

Three B2B accounts in one denormalized table. Alpha has a won deal and an open upsell. Beta is busy in March but has not closed. Delta signed long ago, has no decision-maker, and has gone quiet.

FeatureQL

/* SQL */
SELECT id, name
FROM tutorial_obt.accounts
ORDER BY id;

Result

ACCOUNT_ID BIGINT	ACCOUNT_NAME VARCHAR
1	Alpha
2	Beta
3	Delta

Model

Bind ACCOUNT_ID. Nested columns become typed ARRAY(ROW(...)) features you can TRANSFORM without unnesting into the outer query.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.OBT AS
SELECT
    accounts := ENTITY(),
    account_id := INPUT(BIGINT#accounts)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.OBT.ACCOUNTS	CREATED	Feature created as not exists
FM.OBT.ACCOUNT_ID	CREATED	Feature created as not exists

FeatureQL

CREATE OR REPLACE FEATURES IN FM.OBT AS
SELECT
    tables.accounts := EXTERNAL_COLUMNS(
        id BIGINT#accounts BIND TO account_id,
        name VARCHAR,
        contacts ARRAY(ROW(name VARCHAR, role VARCHAR)),
        opportunities ARRAY(
            ROW(
                opp_id BIGINT,
                name VARCHAR,
                amount BIGINT,
                stage VARCHAR,
                line_items ARRAY(ROW(item_name VARCHAR, amount BIGINT))
            )
        ),
        activities ARRAY(
            ROW(activity_id BIGINT, activity_type VARCHAR, ts TIMESTAMP)
        )
        FROM SQL(
            SELECT id, name, contacts, opportunities, activities
            FROM tutorial_obt.accounts
        )
    ),
    account_name := tables.accounts[name],
    contacts := tables.accounts[contacts],
    opportunities := tables.accounts[opportunities],
    activities := tables.accounts[activities]
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.OBT.TABLES.ACCOUNTS	CREATED	Feature created as not exists
FM.OBT.ACCOUNT_NAME	CREATED	Feature created as not exists
FM.OBT.CONTACTS	CREATED	Feature created as not exists
FM.OBT.OPPORTUNITIES	CREATED	Feature created as not exists
FM.OBT.ACTIVITIES	CREATED	Feature created as not exists

Count inside an array

Decision-makers live in contacts[]. Aggregate inside the array; unwrap the scalar.

FeatureQL

WITH
    DM_COUNT := COALESCE(
        CONTACTS.TRANSFORM(SELECT COUNT(*) FILTER (WHERE role = 'decision_maker')).UNWRAP_ONE(),
        0::BIGINT
    ),
SELECT
    ACCOUNT_NAME,
    DM_COUNT
FROM FM.OBT
FOR
    ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3])
ORDER BY ACCOUNT_NAME;

Result

ACCOUNT_NAME VARCHAR	DM_COUNT BIGINT
Alpha	1
Beta	1
Delta	0

Alpha 1, Beta 1, Delta 0.

Argmax: last activity

ORDER BY … LIMIT 1 inside TRANSFORM returns the whole activity row — not just MAX(ts).

FeatureQL

WITH
    LAST_ACT := ACTIVITIES.TRANSFORM(
        SELECT activity_type, ts ORDER BY ts DESC, activity_id DESC LIMIT 1
    ),
    ACTIVITY_TYPE := LAST_ACT.TRANSFORM(SELECT activity_type).UNWRAP_ONE(),
    ACTIVITY_DATE := CAST(LAST_ACT.TRANSFORM(SELECT ts).UNWRAP_ONE() AS DATE),
    DAYS_SINCE := DATE_DIFF(DATE '2024-04-01', ACTIVITY_DATE, 'day'),
SELECT
    ACCOUNT_NAME,
    ACTIVITY_TYPE,
    ACTIVITY_DATE,
    DAYS_SINCE
FROM FM.OBT
FOR
    ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3])
ORDER BY ACCOUNT_NAME;

Result

ACCOUNT_NAME VARCHAR	ACTIVITY_TYPE VARCHAR	ACTIVITY_DATE TIMESTAMP	DAYS_SINCE BIGINT
Alpha	page_visit	2024-03-28	4
Beta	call	2024-03-25	7
Delta	contract_signed	2024-02-01	60

As of 2024-04-01: Alpha 4 days (page_visit), Beta 7 (call), Delta 60 (contract_signed).

Two arrays, no cross product

Sum opportunity amounts and count activities as separate array transforms. Dual UNNEST would explode; this does not.

FeatureQL

WITH
    OPP_VALUE := COALESCE(OPPORTUNITIES.TRANSFORM(SELECT SUM(amount)).UNWRAP_ONE(), 0::BIGINT),
    ACTIVITY_COUNT := COALESCE(ACTIVITIES.TRANSFORM(SELECT COUNT(*)).UNWRAP_ONE(), 0::BIGINT),
SELECT
    ACCOUNT_NAME,
    OPP_VALUE,
    ACTIVITY_COUNT
FROM FM.OBT
FOR
    ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3])
ORDER BY OPP_VALUE DESC, ACCOUNT_NAME;

Result

ACCOUNT_NAME VARCHAR	OPP_VALUE BIGINT	ACTIVITY_COUNT BIGINT
Alpha	170000	5
Beta	30000	7
Delta	10000	1

Alpha 170000 / 5, Beta 30000 / 7, Delta 10000 / 1.

Engaged but not buying

Recent activity count (array predicate) and no closed_won (other array). Anded at account grain.

FeatureQL

WITH
    RECENT_COUNT := COALESCE(
        ACTIVITIES.TRANSFORM(
            SELECT COUNT(*) FILTER (
                WHERE ts >= TIMESTAMP '2024-03-02 00:00:00'
                  AND ts < TIMESTAMP '2024-04-01 00:00:00'
            )
        ).UNWRAP_ONE(),
        0::BIGINT
    ),
    HAS_CLOSED_WON := COALESCE(
        OPPORTUNITIES.TRANSFORM(SELECT BOOL_OR(stage = 'closed_won')).UNWRAP_ONE(),
        FALSE
    ),
SELECT
    ACCOUNT_NAME,
    RECENT_COUNT,
    HAS_CLOSED_WON
FROM FM.OBT
FOR
    ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3])
WHERE RECENT_COUNT > 5 AND NOT HAS_CLOSED_WON
ORDER BY ACCOUNT_NAME;

Result

ACCOUNT_NAME VARCHAR	RECENT_COUNT BIGINT	HAS_CLOSED_WON BOOLEAN
Beta	6	false

Beta only (6 March activities, open proposal).

Nested arrays: top deal + top line item

Argmax on opportunities by sum of line_items[], then argmax again inside that opportunity’s items.

FeatureQL

WITH
    TOP_OPP := OPPORTUNITIES.TRANSFORM(
        WITH items_total := COALESCE(ARRAY_SUM(line_items[amount]), 0::BIGINT)
        SELECT name, items_total, line_items
        ORDER BY items_total DESC, opp_id ASC
        LIMIT 1
    ),
    OPP_NAME := TOP_OPP.TRANSFORM(SELECT name).UNWRAP_ONE(),
    OPP_TOTAL := TOP_OPP.TRANSFORM(SELECT items_total).UNWRAP_ONE(),
    TOP_ITEM_ROW := TOP_OPP.TRANSFORM(SELECT line_items).UNWRAP_ONE().TRANSFORM(
        SELECT item_name, amount ORDER BY amount DESC, item_name ASC LIMIT 1
    ),
    TOP_ITEM := TOP_ITEM_ROW.TRANSFORM(SELECT item_name).UNWRAP_ONE(),
    TOP_ITEM_AMT := TOP_ITEM_ROW.TRANSFORM(SELECT amount).UNWRAP_ONE(),
SELECT
    ACCOUNT_NAME,
    OPP_NAME,
    OPP_TOTAL,
    TOP_ITEM,
    TOP_ITEM_AMT
FROM FM.OBT
FOR
    ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3])
ORDER BY ACCOUNT_NAME;

Result

ACCOUNT_NAME VARCHAR	OPP_NAME VARCHAR	OPP_TOTAL BIGINT	TOP_ITEM VARCHAR	TOP_ITEM_AMT BIGINT
Alpha	Alpha Upsell	120000	Enterprise License	80000
Beta	Beta Initial	30000	Platform License	20000
Delta	Delta Pilot	10000	Starter License	8000

Alpha → Alpha Upsell / 120000 / Enterprise License / 80000.

Lifecycle from the activity array

Latest type drives customer; else demo-without-contract → prospect; else other. Historical contract_signed does not make you a customer if something newer happened.

FeatureQL

WITH
    LAST_ACTIVITY_TYPE := ACTIVITIES.TRANSFORM(
        SELECT activity_type ORDER BY ts DESC, activity_id DESC LIMIT 1
    ).UNWRAP_ONE(),
    HAS_DEMO := COALESCE(ACTIVITIES.TRANSFORM(SELECT BOOL_OR(activity_type = 'demo')).UNWRAP_ONE(), FALSE),
    HAS_CONTRACT := COALESCE(ACTIVITIES.TRANSFORM(SELECT BOOL_OR(activity_type = 'contract_signed')).UNWRAP_ONE(), FALSE),
    LIFECYCLE_STAGE := CASE
        WHEN LAST_ACTIVITY_TYPE = 'contract_signed' THEN 'customer'
        WHEN HAS_DEMO AND NOT HAS_CONTRACT THEN 'prospect'
        ELSE 'other'
    END,
SELECT
    ACCOUNT_NAME,
    LIFECYCLE_STAGE
FROM FM.OBT
FOR
    ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3])
ORDER BY ACCOUNT_NAME;

Result

ACCOUNT_NAME VARCHAR	LIFECYCLE_STAGE VARCHAR
Alpha	other
Beta	prospect
Delta	customer

Alpha other, Beta prospect, Delta customer.

Re-nest a profile

Fold the metrics into one ROW per account, plus a variable-length risk_flags array.

FeatureQL

WITH
    decision_maker_count := COALESCE(
        contacts.TRANSFORM(
            SELECT COUNT(*) FILTER (WHERE role = 'decision_maker')
        ).UNWRAP_ONE(),
        0::BIGINT
    ),
    last_act := activities.TRANSFORM(
        SELECT ACTIVITY_TYPE, TS ORDER BY TS DESC, ACTIVITY_ID DESC LIMIT 1
    ),
    last_activity_type := last_act.TRANSFORM(SELECT ACTIVITY_TYPE).UNWRAP_ONE(),
    days_since_last_activity := DATE_DIFF(
        DATE '2024-04-01',
        CAST(last_act.TRANSFORM(SELECT TS).UNWRAP_ONE() AS DATE),
        'day'
    ),
    total_opportunity_value := COALESCE(
        opportunities.TRANSFORM(SELECT SUM(amount)).UNWRAP_ONE(),
        0::BIGINT
    ),
    has_demo := COALESCE(
        activities.TRANSFORM(SELECT BOOL_OR(activity_type = 'demo')).UNWRAP_ONE(),
        FALSE
    ),
    has_contract := COALESCE(
        activities.TRANSFORM(SELECT BOOL_OR(activity_type = 'contract_signed')).UNWRAP_ONE(),
        FALSE
    ),
    lifecycle_stage := CASE
        WHEN last_activity_type = 'contract_signed' THEN 'customer'
        WHEN has_demo AND NOT has_contract THEN 'prospect'
        ELSE 'other'
    END,
    risk_flags := ARRAY(
        IF(
            decision_maker_count = 0,
            ROW('no_decision_maker' AS flag),
            NULL(ROW(flag VARCHAR))
        ),
        IF(
            days_since_last_activity > 30,
            ROW('gone_dark' AS flag),
            NULL(ROW(flag VARCHAR))
        ),
        IF(
            total_opportunity_value = 0,
            ROW('no_pipeline' AS flag),
            NULL(ROW(flag VARCHAR))
        )
    ).TRANSFORM(SELECT * WHERE FLAG IS NOT NULL),
    profile := ROW(
        account_name AS account_name,
        decision_maker_count AS decision_maker_count,
        days_since_last_activity AS days_since_last_activity,
        total_opportunity_value AS total_opportunity_value,
        lifecycle_stage AS lifecycle_stage,
        risk_flags AS risk_flags
    )
SELECT
    profile
FROM FM.OBT
FOR
    account_id := BIND_VALUES(ARRAY(1, 2, 3))
ORDER BY profile[account_name]
;

Result

PROFILE ROW
{account_name: Alpha, decision_maker_count: 1, days_since_last_activity: 4, total_opportunity_value: 170000, lifecycle_stage: other, risk_flags: []}
{account_name: Beta, decision_maker_count: 1, days_since_last_activity: 7, total_opportunity_value: 30000, lifecycle_stage: prospect, risk_flags: []}
{account_name: Delta, decision_maker_count: 0, days_since_last_activity: 60, total_opportunity_value: 10000, lifecycle_stage: customer, risk_flags: [{flag: no_decision_maker}, {flag: gone_dark}]}

Delta carries no_decision_maker and gone_dark. Alpha and Beta have empty flags.

What's next

Analytics overview — full series map
Graphs & referrals — RECURSE() over parent links
Marketing attribution — TRANSFORM over journey arrays
E-commerce — relationship shapes that feed nested models

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

On this page

Data Model Count inside an array Argmax: last activity Two arrays, no cross product Engaged but not buying Nested arrays: top deal + top line item Lifecycle from the activity array Re-nest a profile What's next