DealerConnect Source

The dealerconnect source reads pre-parsed PDF blob data from BigQuery tables populated by the ppm-ordering-services collection pipeline. It is specific to the Stellantis project (projects/ai_stellantis).

BigQuery Tables

Table pattern	Description	Partition
inventory_{dealerId}	In-stock vehicles with POC blobs	DAY (fetch_date)
sales_{dealerId}	Sold vehicles with POC blobs	None (filtered by fetch_date + sale_date)
feature_catalog	Features available per model/trim (structured rows, not blobs)	None (filtered by exact DATE(fetch_date))

Dealer tables are discovered dynamically at runtime by listing all tables in the configured dataset that match the relevant prefix (inventory_ or sales_). There is no static dealer ID list — new dealers are picked up automatically as they are onboarded to the collection pipeline.

Required Environment Variables

Variable	Description	Example
STELLANTIS_GCP_PROJECT	GCP project ID containing the DealerConnect dataset (defaults to ai-app-stellantis)	my-gcp-project
GCP_CREDENTIALS	Service account JSON key content (production only; omit to use ADC locally)	{"type":"service_account",...}

The BigQuery dataset name defaults to dealerconnect and is set as a component YAML attribute (dataset:) on each DealerConnectRawSourceComponent subclass — it is not an environment variable.

Authentication

In local development, authenticate with Application Default Credentials (ADC):

gcloud auth application-default login

In production, set GCP_CREDENTIALS to the JSON content of a service account key (not a file path). The BigQueryResourceComponent declared in src/ai_stellantis/defs/resources/defs.yaml reads this env var via gcp_credentials: "{{ env('GCP_CREDENTIALS', '') }}".

The service account requires the following BigQuery IAM roles on the dataset:

• roles/bigquery.dataViewer — read table data
• roles/bigquery.metadataViewer — list tables (required for dealer discovery)
• roles/bigquery.jobUser — run queries

Dagster Resource Key

The BigQuery client is injected via the standard BIG_QUERY_RESOURCE_KEY resource (value: "big_query_resource") exported from ai_dagster.components. All DealerConnectRawSourceComponent subclasses declare this key in _required_resource_keys() and obtain the client via get_bq_resource(context) (exported from ai_dagster.components).

Model Code Structure

A model_code is six characters, opening with a two-character body code: DT1H41 is a DT (RAM 1500), WLTH74 a WL (Grand Cherokee), JLJS74 a JL (Wrangler).

The body code is what ties a model to its place in the EZ Order wizard. A family's order_make opens with the same two characters — DT1500DTLIGHTDUTYCREWCAB… holds the DT… model codes — and order_year is the year code concatenated with it (T + DT = TDT for MY2026 RAM). Ordering uses this to reach a model's own families first instead of walking every family the dealer offers.

The relationship is a naming convention DealerConnect follows, not a guarantee it publishes, so resolution treats it as an ordering hint and still falls back to every family.

Raw Asset Contract

Each raw asset row has the standard RAW_ROW_SCHEMA columns. The _response_body column contains a JSON-serialized BQ row dict with all original columns from the source table, including poc_raw_blob (for inventory/sales) or pdf_raw_blob (for code guides), which may be null if the collection pipeline failed to parse that PDF.

Feature Catalog

feature_catalog is a structured (non-blob) table: one row per (brand_code, model_code, model_year, package_code, feature_code), describing a feature available on a model/trim. It feeds both the features and models entities.

Wiring: sources/feature_catalog.py (BQ fetch + extractors), StellantisFeatureCatalogRawComponent (raw), StellantisFeatureCatalogTransformedComponent (transformed, two assets: feature_catalog_features + feature_catalog_models).

Raw query

BQHistoricalRawComponent._fetch_from_bq queries WHERE DATE(fetch_date) = @partition_date. The table holds many intra-day captures across multiple dealers, so duplicate rows per feature are kept verbatim in raw; the transformed tier's _clean collapses them to one row per entity key. The nested package_contents array is preserved in the raw _response_body.

Field mapping (transformed)

Entity field	Source
option_code	feature_code (package row) / package_contents[].code (member rows), prefix-stripped
feature_prefix	leading * (seat) or - (interior color) stripped off the code
lower_level_package	package_code
trim_identifier	package_code[-1]
model_code, model_year	direct
brand	brand_code via dealer_locator.BRAND_CODES (J→jeep, C→chrysler, …)
short_description	feature_name / content name
msrp	msrp (dropped on split seat/color rows — a combo price is not a per-half price)
category_* / subcategory_*	backfilled by joining equipment_categories (not in this table)

Code normalization

feature_code comes in two formats: 3-char codes (e.g. ACG) and 7-char concatenated seat/trim codes (*SEAT/-COLOR, e.g. *E7/-X9). The concatenated form is split on / into one feature per half, and the leading */- prefix is stripped into feature_prefix (bare code in option_code). This matches how every other source (equipment_categories, configure, catalog_option, inventory) represents seat and interior-color options, so the codes join cross-source.

package_contents explosion

Each row is emitted as a feature, and every package_contents item becomes a member row keyed parent_package_code = feature_code.

parent_package_code is null only where the feed offers the code in its own right. A member reaches us only through its package, so it gets no row of its own. A Package Content row is content of the CPP it names in package_code and is parented to that CPP — JHC (rain sensitive wipers) on Compass VF1L13 is offered nowhere on its own, and becomes parent_package_code = 22G / 22H.

feature_group gates none of this. A row is both an offer line and a package wherever it has members: all 40 STJ rows are filed under Additional Features, priced $1,495–$3,295, and list the four codes Wrangler 279738 p29 prints beneath STJ SKY ONE-TOUCH POWER TOP in GROUP DETAIL.

This matches code_guides, which is what lets the two feeds join.

Flags

Entity field	Source
is_initially_selected	initially_selected
is_required	required
is_orderable_standalone	the row is one of the feed's own offer lines, not a member

is_initially_selected is not standard. equipment_categories and code_guides own that field and mean the code is standard equipment; this feed says only what its configurator opens with.

This table does not populate is_configurable — code_guides answers it for consolidated (priority 3) and configure covers the brands the guides do not (priority 5). It is not a source group for that field.

Co-occurring spare-tire codes on Grand Cherokee

Grand Cherokee Laredo 4WD (model_code WLJH74 / WLJH75) installs exactly one spare tire, but the POC can list several spare-tire codes at once. Three codes matter here: ABR is the trailer-tow package, whose POC content includes the full-size spare TBP; TBK is the standalone compact spare; and TBP is the standalone full-size spare. The inventory transformed source builds feature_refs from the POC parent + indented children (option_details), so a car ordered with the compact spare TBK and the tow package ABR returns all of ABR, TBK, and TBP — TBP arriving as ABR's package child even though the flat order code string carries only TBK.

StellantisInventoryTransformedComponent strips the superseded codes via the shared feature_co_occurrence_removals template var: ABR supersedes both standalone spares (if_present: ABR drops TBK and TBP), and TBK supersedes TBP. This mirrors the synthetic_candidates spare-tire mutual exclusion for the same body, so real inventory and synthetic candidates carry the same one-spare invariant.

Feature reference parentage

The POC pricing matrix prints package content indented under its package, so the inventory transformed source populates feature_refs[].parent_package_code directly from the blob — DealerConnect is the only Stellantis inventory feed that knows its own package membership. The public search reports a bare comma-separated option string and has none.

Both feeds are routed through EnrichedInventoryComponent, which preserves a parent the blob populated and infers the rest from consolidated features. DealerConnect needs it for the codes the blob prints flat: POC indentation covers the packages the pricing matrix chose to nest, not every package membership the feature catalog records.

feature_co_occurrence_removals matches on option_code alone, so a superseded code is dropped whether the feed reported it standalone or as package content — on a WLJH74 car carrying ABR, the TBP printed as ABR's child is dropped along with the standalone TBP, leaving ABR to carry the spare.

Operational Notes

• NULL blobs are retained in the raw asset so collection failures are auditable. The transform-tier extractors skip NULL blobs gracefully and log a warning per skip.
• Missing dealer tables are warned and skipped — a partition can materialize successfully even if some dealers have no data for that date.
• Zero tables found is logged at info level and results in an empty DataFrame — this is valid for early backfill dates before any dealers were onboarded.

Upstream fragility: Struts transaction tokens

The collection pipeline's allocation table (allocations_{dealerId}) is fed by a DealerConnect action that is guarded by a single-use Struts transaction token: the landing page (allocationMain.do) mints one, the report request spends it, and a replay gets a generic "can not process your request" HTML page that is not a login page. DealerConnect can change which actions are guarded (or how strictly) server-side, without notice, and independently of its sibling endpoints — a single table going stale while the rest keep updating is consistent with exactly this.

Two consequences for this repo:

• load_allocations falls back to MAX(fetch_date), so a dead collector is invisible to ordering runs — they keep succeeding on the last good snapshot. A staleness tripwire in allocations_bq.py ERROR-logs (→ Sentry) when the snapshot is older than _STALENESS_ALERT_DAYS, a threshold set just above the longest benign collection gap. It logs rather than raises: staleness is a paging matter, not grounds to fail an in-flight run.
• Any direct DealerConnect fetch added here (see ai_dealer_connect) must treat a non-PDF/non-login HTML response as a portal-side fault, not an expired session, and should check whether the action's landing page mints a strutsToken before assuming a bare GET works.

Full details: ppm-ordering-services docs/oems/stellantis/architecture.md, "Struts transaction tokens".