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 registers 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.
No column in on may be named after target. The join writes the resolved ID to
target, so a same-named join column collides and the resolved ID is written suffixed
_right while target keeps the parent row's original value. The failure is
silent: the unresolved-count check reads target, which is never null, and
reports full resolution. Where the referenced entity keys on the same concept as
its own entity ID — dealers keyed on a natural dealer code, resolving to
dealer_id — name the natural key for what it is (dealer_code, pa_code) on
both sides. enrich_hash_key writes the entity ID to target on the referenced
entity too, so a natural key named target there is overwritten by its own hash.
A source column named after target collides the same way. Rename it with the
source group's adapter.rename, which is applied to the source before anything else
reads it.
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.
A dot-path takes the same > rename as a plain column, written after the element field:
foreign_keys:
- entity_type: features
target: rule_if_feature_ids
"on": [model_code, rule_if.feature_code>pr3_id]
Here feature_code is the field read from each element of the rule_if list column; pr3_id is the column on the features entity. Use this where the struct field belongs to a shared entity and so cannot be renamed to match.
base_model_id
include_base_model_id copies the referenced model's base_model_id onto the row with a left join on the resolved target id:
foreign_keys:
- entity_type: models
target: model_id
"on": [model_code, model_year]
include_base_model_id: true
The join reads base_model_id from the consolidated models input that ID resolution already reads, so the option adds no asset input.
Consolidated inventory holds base_model_id as well as model_id because take rates and inventory statistics group inventory rows by base_model_id.
A consumer that needs any other column of a referenced entity joins that entity on the target id.
include_base_model_id requires entity_type: models. It is not supported on list-mode (dot-path) foreign keys, since a list target has no single model to read base_model_id from.