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 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.

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.

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.

Extra fields​

extra_fields pulls additional non-key columns from the referenced entity onto the row, left-joined on the already-resolved target id — reusing the same reference frame fetched for ID resolution, with no extra asset input:

foreign_keys:
  - entity_type: models
    target: model_id
    "on": [model_code, model_year]
    extra_fields: [msrp, base_model_id]

Runs after drop_fields, so a column both retained on the parent row and requested via extra_fields collides and is written suffixed _right (standard Polars join behavior) — reference the suffixed name in any derived_fields expression that needs both values. Not supported on list-mode (dot-path) foreign keys, since a list target has no single row to attach scalar columns to.