Foreign Keys

foreign_keys attaches a resolved entity ID from another consolidated entity to each row. The referenced entity must be listed as an input to the asset — the component wires this automatically based on the declared entity_type.

Single-value join

A plain column join attaches one ID per row (or null if no match):

foreign_keys:
  - entity_type: dealers
    target: dealer_id
    "on": [mat_primary_code]

When the parent row and the referenced entity use different column names, use left_col>ref_col syntax in on:

foreign_keys:
  - entity_type: dealers
    target: dealer_id
    "on": [dealer_mat_primary_code>mat_primary_code]

Here dealer_mat_primary_code is the column on the inventory row; mat_primary_code is the column on the dealers entity.

List mode

A dot-path list_field.element_field traverses a list column on the parent row, joining each element against the referenced entity and collecting matched IDs into a list. Rows with no matches receive [] (empty list).

foreign_keys:
  - entity_type: features
    target: feature_ids
    "on": [model_catalog_id, feature_refs.pr3_id, feature_refs.parent_package_code]

model_catalog_id is a plain row-level field included as a join condition on every element. feature_refs.pr3_id reads the pr3_id field from each element of the feature_refs list column, joining each element individually against the features entity and collecting all matched IDs into feature_ids.

Multiple on conditions are ANDed — all must match for an element to resolve to an ID.