Skip to main content

ConsolidatedComponent

Each OEM uses one ConsolidatedComponent to merge transformed or enriched records across sources and resolve foreign keys between entities. The component requires configuration for dealers, models, features, and inventory. Rules and offers are optional. Each entity has its own key within the component's attributes.

Top-level attributes​

AttributeRequiredDescription
oemyesOEM identifier (e.g. "audi")
marketyesMarket identifier (e.g. "us")
start_dateyesEarliest partition date for the DailyPartitionsDefinition
dealersyesPer-entity spec for the dealers entity
modelsyesPer-entity spec for the models entity
featuresyesPer-entity spec for the features entity
inventoryyesPer-entity spec for the inventory entity
rulesnoPer-entity spec for rules with resolved feature references. Omitting rules produces no rules asset.
offersnoPer-entity spec for offers, whose rows must have the columns of ai_core.entities.OfferEntity; see Offers. Omitting offers produces no offers asset.

Per-entity spec​

Each entity block (dealers, models, features, inventory, rules, offers) shares the same structure:

FieldDescription
start_dateEarliest partition of this entity's asset. Set it for an entity whose sources start later than the component's start_date. Defaults to the component's start_date. Must not precede the component's start_date or the start_date of any entity named in this entity's foreign_keys
source_groupsPriority-ranked list of source groups — see Source Groups
mergeCross-source matching and field resolution — see Merge Spec
key_fieldsFields used to identify and deduplicate records across sources; also used to compute the entity ID hash
id_policy"hash_all" (default) hashes all key_fields together; "first_non_empty" hashes only the first non-empty value — useful when sources populate mutually exclusive identifiers (e.g. VIN vs order number). The inventory entity defaults to "first_non_empty"
foreign_keysCross-entity reference lookups — see Foreign Keys
field_castsPolars-SQL CAST target types for merged columns — see Field Casts below
derived_fieldsPolars-SQL expressions computing additional columns — see Derived Fields below

The inventory and models blocks each accept a few additional fields, covered below.

Common Schema​

Every consolidated entity must have the columns its entity type declares in ai_core.entities.COMMON_ENTITY_SCHEMAS, at the declared dtype. The merged frame is compared against that schema before the write. A mismatch raises an error that names each missing field or wrong dtype, so the partition fails instead of writing a table that differs from its peers. Each source group maps its native column names onto the common ones with adapter.rename or adapter.fields — see Source Groups.

Identity and key fields stay out of the common schema: consolidated keys range from a single code to three-part composites, so each entity keys on its own key_fields.

Canonical values​

Where a common field names one concept, its value carries that concept alone — a source value that packs several is narrowed before it is written.

model_code is the model line only; model year and trim belong to model_year and trim_code:

Source valueCanonical model_code
BMW NA 27XDXD
Stellantis Public CUJ202510JLJS74AJLJS74
Audi Public FU2AAY0_2026FU2AAY

Option codes follow the same rule: a type-related prefix (Mercedes 2:040, Stellantis -XY) is not part of the option's identity.

Models destination_charge is the manufacturer's fixed destination and handling fee for the model. A vehicle's sticker MSRP includes it, and models msrp does not. A source group renames its own column to it with adapter.rename, such as BMW US destination_and_handling. A source group that publishes no fee sets the column to null. The merged entity then has the column even when no source group publishes the fee. The merge keeps a fee that another source group publishes, because a null does not replace a non-null value:

adapter:
fields:
- name: destination_charge
expr: CAST(NULL AS DOUBLE)

Inventory order_number is the OEM ordering identifier for the unit.

A dealer stock number is a dealer or marketplace inventory reference for a vehicle. It can exist alongside an OEM order number, but it is a different identifier and must not be mapped into order_number.

Inventory arrival_date is the date the unit reached its dealer, or an estimate of that date, such as an expected delivery date or a production date. The OEM's inventory block derives the date or the estimate from the OEM's feeds. An inventory block that derives neither sets arrival_date to CAST(NULL AS DATE).

Inventory is_dealer_modifiable is true when the dealer had a chance to change or refuse the order. It is false for a unit the OEM assigned to the dealer without giving the dealer that chance. Audi derives it from the AccessAudi tag described in Refill allocation units. Mercedes derives it from whether Netstar ever listed the order in an unlocked status. Both set it to true where that feed did not list the vehicle in the window: Audi for a vehicle that only OneGraph or accessaudi_search lists, and Mercedes for a vehicle that only NAFTA or Netstar sales lists. An inventory block that does not derive it sets it to CAST(NULL AS BOOLEAN).

Inventory vehicle status​

Every consolidated inventory has a vehicle_status column that records where a unit is between order and sale. Its values are the members of ai_core.entities.VehicleStatus. The blocking vehicle_status_valid check fails a partition that has any other value.

vehicle_status is null in two cases:

  • the feeds report no status for the unit;
  • the feeds report a status that maps to no VehicleStatus value, such as KZY1 for a damaged Stellantis unit.

Each OEM sets vehicle_status in its inventory derived_fields. Audi, BMW, Mercedes and Stellantis map the latest status their feeds reported. Every other OEM sets it to null.

vehicle_statusThe unit isAudi status_codeBMW event idMercedes (Netstar)Stellantis status_code
ON_ORDERordered, with its build not started; scheduled orders includednone047, 087, 102, 105, 111, 112vehicle_type IP with status_code LF03, LF04 or LF05every B code, C, D, D1
IN_PRODUCTIONbeing built, or built and still at the plantFACTORY150–160vehicle_type IP with any other status_codeE, F, G, I, J, JB, JE, JJ, JS
IN_TRANSITshipped from the plant and not yet at the dealer, including time at a port, a compound or a vehicle processing center (VPC)IN_TRANSIT_FROM_FACTORY, PORT, IN_TRANSIT_TO_DEALER165, 181, 182, 190, 193, 195vehicle_type OTW, PNS, VPCevery KZ code except KZX, KZY1 and KZY2
AT_DEALERon the dealer lotDEALER_STOCK, CENTER_STOCK198vehicle_type INVKZX
SOLDsoldis_in_inventory falseis_in_inventory false, or 295, 298is_in_inventory falseis_in_inventory false
CANCELLEDa cancelled ordernone100noneZA

A cancelled Stellantis order also has is_in_inventory false, so the expressions test for CANCELLED before SOLD. BMW events 295 and 298 are retail sales, so a unit with either event is SOLD even where is_in_inventory is still true.

The BMW codes are logistics event ids, listed in BMW USA source. Stolo's orderStatus uses the same ids (see Stolo source). The BMW US expression reads the id from event_id, and the expressions for the Stolo markets read it from status_code. The Stellantis codes are listed in Order status codes.

No OEM reference describes these mapped codes:

  • Audi CENTER_STOCK, a OneGraph status, is mapped to AT_DEALER on the assumption that it means stock at an Audi dealer.
  • Mercedes LF03, LF04 and LF05 are the status_code values that the Netstar locked_when condition treats as unlocked. An IP unit with one of these values is mapped to ON_ORDER.

Inventory sale type​

Every consolidated inventory has a sale_type column that records how a sold unit left the dealer's new inventory. Its values are the members of ai_core.entities.SaleType. The blocking sale_type_valid check fails a partition that has any other value.

sale_typeThe unit was
CUSTOMER_RETAILsold to a retail customer
FLEET_RETAILsold to a fleet, or in another sale that is neither a customer retail nor dealer use
DEALER_USEretailed by the dealer into its own use, such as its service loaner fleet

sale_type is null for a unit that is not sold. It is also null for a sold unit where the OEM's feeds do not say how the unit was sold. Each OEM sets it in its inventory derived_fields. Mercedes maps the Netstar sales program (see Netstar). Every other OEM sets it to null.

Inventory statistics leave the sale types in excluded_sale_types out of the sales counts (see Inventory Statistics). A new kind of sale that should count differently is a new SaleType member that the OEM's sale_type expression maps to.

Field Casts​

field_casts maps a merged column to a Polars-SQL CAST target type (e.g. BIGINT), applied right after the entity ID hash is computed and before foreign-key resolution runs. Use it when a column's native type varies by source, or when a foreign key joins two entities on a field that different transformed sources represent differently. A plain Polars join fails with a dtype-mismatch error if the two sides disagree, and this normalizes the type once per entity instead of once per source group:

models:
# ...
key_fields: [model_code, model_year]
field_casts:
model_year: BIGINT
inventory:
# ...
foreign_keys:
- entity_type: models
target: model_id
"on": [model_code, model_year]
field_casts:
model_year: BIGINT

Every entity that carries the field — as a key field or as part of an on join condition — needs the same cast. The mechanism normalizes one entity's own merged frame, not the field across entities. enrich_hash_key casts key fields to Utf8 before hashing, so casting a clean numeric string to an integer type does not change the entity ID.

Derived Fields​

derived_fields, available on every entity block, computes columns from a Polars-SQL expression, evaluated after foreign keys resolve and drop_fields runs. Each entry has a name and an expr. Expressions apply sequentially against the full row (every column, including earlier derived fields, is in scope), so a later expression can reference a column an earlier one produced:

inventory:
# ...
derived_fields:
- name: inferred_sold_date
expr: >-
CASE
WHEN private_sold_date IS NOT NULL
THEN private_sold_date
ELSE GREATEST(public_last_seen, private_last_seen)
END
- name: days_on_lot
expr: >-
CASE
WHEN inferred_sold_date IS NOT NULL
THEN DATEDIFF('day', arrival_date, inferred_sold_date)
ELSE NULL
END
- name: is_sold_in_window
expr: >-
inferred_sold_date IS NOT NULL
AND inferred_sold_date >= DATE('{partition_date}') - INTERVAL 365 DAYS
AND inferred_sold_date < DATE('{partition_date}')
- name: is_in_inventory
expr: inferred_sold_date IS NULL

Any expr may contain {partition_date}, replaced at materialization time with the current partition key (e.g. 2025-04-15) — essential for sold-window flags. Expressions without {partition_date} are unaffected. Field names in {...} are Python str.format() placeholders — escape literal braces as {{ or }}.

Gap Fill​

See Gap Fill for gap_fill, which estimates sale and delivery dates for vehicles caught in a known collection gap.

Model hierarchy​

The models entity hashes two grouping ids above model_id, both declared per OEM in model_hierarchy as either fields (hashed together) or an expr (a polars-SQL expression whose value is hashed):

  • base_model_id — a year-independent hash, key_fields without model_year, so a model's years share one id.
  • model_group_id — a hash of a coarser car-line grouping.
models:
# ...
model_hierarchy:
base_model_id: { fields: [model_code, trim_code] } # key_fields without model_year
model_group_id: { fields: [carline_group_id] }
# model_group_id: { expr: "SUBSTR(model_code, 1, 3)" } # transform form
base_model_id_aliases:
- match: {model_code: "X5B"}
canonical: {model_code: "X5"}
FieldDescription
model_hierarchyHashed grouping ids above model_id, each hashed from fields or an expr. Must define both base_model_id and model_group_id
base_model_id_aliasesRemaps the base_model_id hash input for rows whose identifier changed between years (e.g. a renamed model code), so they still hash to their canonical counterpart. Only the hash input is remapped — real column values are unchanged

A blocking base_model_id_unique_per_model_year check fails if two distinct rows hash to the same (base_model_id, model_year); it runs when key_fields includes model_year.

Example​

type: ai_dagster.components.ConsolidatedComponent
attributes:
oem: audi
market: us
start_date: "2025-01-01"
dealers:
source_groups:
- name: pss
source: pss_dealers
priority: 1
history_days: 365
merge:
match_on: [[mat_primary_code]]
key_fields: [mat_primary_code]
models:
source_groups:
- name: onegraph
source: onegraph_models
priority: 1
history_days: 365
merge:
match_on: [[sales_model_year, model_code, trimline_id]]
key_fields: [sales_model_year, model_code, trimline_id]
features:
source_groups:
- name: catalog
source: catalog_features
priority: 1
history_days: 365
merge:
match_on: [[model_catalog_id, pr3_id, parent_package_code]]
key_fields: [model_catalog_id, pr3_id, parent_package_code]
foreign_keys:
- entity_type: models
target: model_id
"on": [model_catalog_id]
inventory:
source_groups:
- name: scs
source: scs_inventory
priority: 1
history_days: 365
merge:
match_on: [[vin]]
key_fields: [vin]
foreign_keys:
- entity_type: dealers
target: dealer_id
"on": [dealer_mat_primary_code>mat_primary_code]
- entity_type: models
target: model_id
"on": [model_catalog_id]
- entity_type: features
target: feature_ids
"on": [model_catalog_id, feature_refs.pr3_id, feature_refs.parent_package_code]