Skip to main content

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.