OBT modeling

This tutorial analyzes an entity-centric one-big-table without flattening its nested arrays into a fan-out join.

Each account row contains contacts, opportunities with nested line items, and activities. You will count, filter, aggregate, and select top rows inside those arrays, then package the results into one account profile.

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

Meet the three accounts

The table contains three B2B accounts:

Alpha has a won deal and an open upsell.
Beta is active in March but has not closed a deal.
Delta signed long ago, has no decision-maker contact, and has gone quiet.

FeatureQL

SELECT id, name
FROM tutorial_obt.accounts
ORDER BY id;

Result

ACCOUNT_ID BIGINT	ACCOUNT_NAME VARCHAR
1	Alpha
2	Beta
3	Delta

Behind each account row, contacts, opportunities, and activities have independent lengths; opportunity line items add another nested level.

Map one row per account

Declare accounts and their typed key:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.OBT AS
PERSIST
    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

Map the scalar and nested columns. The arrays become typed ARRAY(ROW(...)) features:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.OBT AS
PERSIST
    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

Every following query binds ACCOUNT_ID, so the outer result remains one row per account. TRANSFORM() works inside an array without changing that grain.

Count matching contacts inside an array

Filter contacts[] to decision-makers and count them inside each account's array. The nested query returns one aggregate row, which UNWRAP_ONE() converts back to a scalar:

FeatureQL

DEFINE
    dm_count := COALESCE(
        contacts.TRANSFORM(
            RETURN COUNT() FILTER (WHERE role = 'decision_maker')
        ).UNWRAP_ONE(),
        0::BIGINT
    )
RETURN
    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 and Beta each have 1 decision-maker. Delta has 0.

Keep the whole latest activity row

MAX(ts) would tell us when the latest activity occurred but not which activity it was. Instead, order each account's activities by timestamp and keep the first row:

FeatureQL

DEFINE
    last_act := activities.TRANSFORM(
        RETURN
            activity_type,
            ts
        ORDER BY
            ts DESC,
            activity_id DESC
        LIMIT 1
    ),
    activity_type := last_act[1][activity_type],
    activity_date := last_act[1][ts]::DATE,
    days_since := DATE_DIFF(DATE '2024-04-01', activity_date, 'day')
RETURN
    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 DATE	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 April 1, Alpha's latest activity is a page visit 4 days ago, Beta's is a call 7 days ago, and Delta's is a contract signing 60 days ago.

This is an argmax pattern: choose the complete row associated with the greatest timestamp.

Aggregate two arrays without a cross product

We need total opportunity value and activity count on the same account row. Calculate them in separate array transforms:

FeatureQL

DEFINE
    opp_value := COALESCE(
        opportunities.TRANSFORM(RETURN SUM(amount)).UNWRAP_ONE(),
        0::BIGINT
    ),
    activity_count := COALESCE(
        activities.TRANSFORM(RETURN COUNT()).UNWRAP_ONE(),
        0::BIGINT
    )
RETURN
    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 has 170000 / 5, Beta 30000 / 7, and Delta 10000 / 1 for opportunity value / activity count.

Unnesting both arrays into one flat relation would multiply every opportunity by every activity. Independent transforms avoid that fan-out.

Combine evidence from different arrays

Define an engaged but not buying account as having recent activity and no closed_won opportunity. Each condition is calculated in its own array and combined at the account grain:

FeatureQL

DEFINE
    recent_count := COALESCE(
        activities.TRANSFORM(
            RETURN 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(RETURN BOOL_OR(stage = 'closed_won')).UNWRAP_ONE(),
        FALSE
    )
RETURN
    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

Only Beta qualifies, with six March activities and an open proposal.

Find a top deal and its top line item

Opportunities contain their own line_items[]. First rank opportunities by the sum of their line items. Then, inside the winning opportunity, rank the line items themselves:

FeatureQL

DEFINE
    top_opp := opportunities.TRANSFORM(
        DEFINE
            items_total := COALESCE(ARRAY_SUM(line_items[amount]), 0::BIGINT)
        RETURN
            name,
            items_total,
            line_items
        ORDER BY
            items_total DESC,
            opp_id
        LIMIT 1
    ),
    opp_name := top_opp[1][name],
    opp_total := top_opp[1][items_total],
    top_item_row := top_opp[1][line_items].TRANSFORM(
        RETURN
            item_name,
            amount
        ORDER BY
            amount DESC,
            item_name
        LIMIT 1
    ),
    top_item := top_item_row[1][item_name],
    top_item_amt := top_item_row[1][amount]
RETURN
    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

For Alpha, the top opportunity is Alpha Upsell at 120000, and its largest line is Enterprise License at 80000. Beta returns Beta Initial / 30000 / Platform License / 20000; Delta returns Delta Pilot / 10000 / Starter License / 8000.

The two argmax operations occur at different nested levels but still return one account row.

Derive lifecycle from the latest activity

Classify lifecycle from the latest activity:

latest contract_signed → customer;
otherwise, a demo with no contract → prospect;
otherwise → other.

FeatureQL

DEFINE
    last_activity_type := activities.TRANSFORM(
        RETURN activity_type
        ORDER BY
            ts DESC,
            activity_id DESC
        LIMIT 1
    ).UNWRAP_ONE(),
    has_demo := COALESCE(
        activities.TRANSFORM(RETURN BOOL_OR(activity_type = 'demo')).UNWRAP_ONE(),
        FALSE
    ),
    has_contract := COALESCE(
        activities.TRANSFORM(RETURN 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
RETURN
    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 is other, Beta is prospect, and Delta is customer.

Delta's result may look surprising after the inactivity check, but it follows the rule exactly: contract_signed is still Delta's latest activity. Conversely, a historical contract does not automatically classify an account when a newer activity exists.

Package the metrics into a profile

The capstone folds the account metrics into one typed ROW and adds a variable-length array of risk flags:

FeatureQL

DEFINE
    decision_maker_count := COALESCE(
        contacts.TRANSFORM(
            RETURN COUNT() FILTER (WHERE role = 'decision_maker')
        ).UNWRAP_ONE(),
        0::BIGINT
    ),
    last_act := activities.TRANSFORM(
        RETURN
            activity_type,
            ts
        ORDER BY
            ts DESC,
            activity_id DESC
        LIMIT 1
    ),
    last_activity_type := last_act[1][activity_type],
    days_since_last_activity := DATE_DIFF(
        DATE '2024-04-01',
        last_act[1][ts]::DATE,
        'day'
    ),
    total_opportunity_value := COALESCE(
        opportunities.TRANSFORM(RETURN SUM(amount)).UNWRAP_ONE(),
        0::BIGINT
    ),
    has_demo := COALESCE(
        activities.TRANSFORM(RETURN BOOL_OR(activity_type = 'demo')).UNWRAP_ONE(),
        FALSE
    ),
    has_contract := COALESCE(
        activities.TRANSFORM(RETURN 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(
        RETURN *
        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
    )
RETURN
    profile
FROM FM.OBT
FOR
    account_id := BIND_VALUES(ARRAY(1, 2, 3))
ORDER BY profile[account_name]
;

Result

PROFILE ROW<account_name VARCHAR, decision_maker_count BIGINT, days_since_last_activity BIGINT, total_opportunity_value BIGINT, lifecycle_stage VARCHAR, risk_flags ARRAY<ROW<flag VARCHAR>>>
{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 flag arrays.

The result is ready for an entity API or feature consumer: one account, one structured profile, and no accidental row multiplication.

What's next

You have analyzed independent and nested arrays, kept full rows at an argmax, combined account-level signals, and re-nested the result.

Return to the analytics overview to compare this entity-centric shape with normalized and event-stream tutorials, or try Graphs & referrals for recursive structure.

Last update at: 2026/10/13 08:32:21

On this page

Meet the three accounts Map one row per account Count matching contacts inside an array Keep the whole latest activity row Aggregate two arrays without a cross product Combine evidence from different arrays Find a top deal and its top line item Derive lifecycle from the latest activity Package the metrics into a profile What's next