Tricky modeling patterns

Patterns that look straightforward but require careful entity and FK reasoning. Load this page when you face multi-FK, multi-hop, or grain-mismatch problems.

Dual FK — same entity referenced twice

When a table has two FK columns pointing to the same entity (e.g., flight_origin_id and flight_dest_id both referencing AIRPORTS), use two separate RELATED() calls with different VIA paths:

ORIGIN_CITY := RELATED(AIRPORT_CITY VIA FCT_FLIGHTS[flight_origin_id]),
DEST_CITY   := RELATED(AIRPORT_CITY VIA FCT_FLIGHTS[flight_dest_id]),

For reverse aggregations (count per entity), use two RELATED(COUNT(1) GROUP BY ...) calls:

NUM_DEPARTURES := AIRPORT_ID.RELATED(COUNT(1) GROUP BY FCT_FLIGHTS[flight_origin_id]),
NUM_ARRIVALS   := AIRPORT_ID.RELATED(COUNT(1) GROUP BY FCT_FLIGHTS[flight_dest_id]),
TOTAL := COALESCE(NUM_DEPARTURES, 0) + COALESCE(NUM_ARRIVALS, 0),

Use COALESCE() because an entity with zero matches on one FK column gets NULL.

Successive aggregations — chain TRANSFORM

When you need to aggregate, then re-aggregate (e.g., revenue by category, then top category), chain two .TRANSFORM() calls:

REVENUE_BY_CATEGORY := ENRICHED_ORDERS.TRANSFORM(
    SELECT category, SUM(amount) GROUP BY category AS total
    ORDER BY total DESC
),
TOP_CATEGORY := REVENUE_BY_CATEGORY.TRANSFORM(
    SELECT category, total ORDER BY total DESC LIMIT 1
),

Common mistake: trying to nest GROUP BY inside a single TRANSFORM or using a sub-TRANSFORM. Each TRANSFORM operates on the output of the previous one.

Many-to-many through junction table

For a junction table (e.g., enrollments linking students to courses):

1. Model the junction table as its own entity
2. Create an OBT with the array of junction IDs per parent entity
3. EXTEND the array to enrich each junction row

STUDENTS := ENTITY(),
ENROLLMENTS := ENTITY(),
STUDENT_OBT := EXTERNAL_COLUMNS(
    student_id BIGINT#STUDENTS BIND TO STUDENT_ID,
    enrollment_ids ARRAY(BIGINT#ENROLLMENTS)
    FROM TABLE(school.student_enrollments_obt)
),
ENROLL_DATA := EXTERNAL_COLUMNS(
    enrollment_id BIGINT#ENROLLMENTS BIND TO ENROLLMENT_ID,
    enroll_course_id BIGINT,
    grade DOUBLE
    FROM TABLE(school.enrollments)
),
ENRICHED := EXTEND(
    ZIP(STUDENT_OBT[enrollment_ids] AS enrollment_id)
    WITH ENROLL_DATA[grade] AS grade, ENROLL_DATA[enroll_course_id] AS course_id
    VIA enrollment_id BIND TO ENROLLMENT_ID
),
GPA := ENRICHED.TRANSFORM(SELECT AVG(grade)).UNWRAP_ONE(),

Important: when including a column that is a FK to a third entity (like enroll_course_id), declare it as plain BIGINT (not BIGINT#COURSES) to avoid the cross-entity RELATED-in-EXTEND limitation.