BMW USA Private Inventory (invsearch archive)

US-market private inventory for the bmwusa source (bmw/us/raw/bmwusa_private_inventory → bmw/us/transformed/bmwusa_private_inventory; the bmwusa_inventory name is reserved for the future public bmwusa.com scrape), read from BigQuery archive snapshots of the legacy inventory-live-feed-processor Postgres database. Interim source: a CDH private feed is expected to replace it soon (exact timing to be assessed with Heribert), so the integration deliberately adds no new infrastructure.

Provenance chain

BMW SOAP invoice events (STAR)
  → inventory-live-feed-processor (ECS, account 999655274916)
      → RDS Postgres (upsert + soft-delete; rows never deleted)
          → Datastream replica: ai-app-bmw.inventory_feed_datastream (merge-mode, current state only)
              → daily snapshot job (~03:00 US-Eastern):
                  bmw-prod-309117.inventory.invsearchvehicles_archive
                  bmw-prod-309117.inventory.invsearchvehicles_options_archive

The archive tables are DAY-partitioned on archive_date — one partition is the full Postgres state on that date (~2.3 years of history from 2024). This is the only point-in-time source; the Datastream replica cannot reconstruct past days.

Semantics

• Soft delete: a DELETE feed event sets sold='true' + solddate. Live inventory is the sold != 'true' subset (~166k vehicles, ~12.8M option rows of the 3.4M / 247M totals).
• Daily query: live vehicles plus vehicles with solddate in the trailing 7 days, in one pass. The same VIN appears in up to 7 consecutive partitions after a sale — partitions are windows, not disjoint sets; consumers distinguish rows by the sold column.
• Sold rows carry snapshot-date state: fields/options are as of archive_date, not as of the sale moment.
• Options are joined at fetch time (o.postgres_id = t.postgres_id, both pruned to the same archive_date) and nested per vehicle; the raw envelope is {"vehicles": [<vehicle with options array>, ...]} chunked ~100 vehicles per raw row.

Archive type quirks

The snapshot job stringifies Postgres types: all booleans are STRING ('true'/'false') — including sold, used, and every option is* flag — and money columns (basemsrp, totalmsrp, option price/wholesaleprice) are INTEGER. The INTEGER postgres_id on both tables is the join key (verified 100% match; STRING id/vehicle_id are its stringified equivalents).

Auth

Runs through the shared big_query_resource (GCP_CREDENTIALS service account, job project ai-app-bmw / BMW_GCP_PROJECT). The service account additionally needs read access to bmw-prod-309117 (BigQuery Data Viewer on the inventory dataset + Storage Read API), since the query job bills to ai-app-bmw but reads cross-project.

Entity model and column mapping

US inventory has its own transformed shape: BmwUsaInventoryEntity, a sibling of the global BmwInventoryEntity under the shared BmwInventoryBase (so shared concepts keep one canonical name while neither market carries the other's feed-specific fields). Model codes: agcode → manufacturer_code (BMW AG global code, aligns with stolo); modelcode → vg_model_code (regional VG code, the features-catalog join key, also carried on each BmwUsaFeatureRef.model_code; the global markets' FeatureRef is untouched).

The transformed field list follows the candidate-selection field reference (bmw-inventory-field-reference.md, 2026-07-17): Required fields, plus reviewed keepers (port_of_entry, port_of_departure, vehicle_processing_center, agcode, and the base-mapped entity-metadata fields such as series_*/basemsrp which aid consolidated FK resolution). Replaced/Not-Used columns stay raw-only — raw keeps every archive column. Notes:

Column	Disposition
salesperson	PII — excluded in the SQL, never enters the lake.
customername	Retained (customer_name) — Required downstream for fake-custom-order detection.
code	Exact duplicate of modelcode (verified) — raw-only.
id, postgres_id, source_timestamp, archive_date	Snapshot/replication bookkeeping; the stable vehicle id derives from productionnumber+modelrange+modelyear.

feature_refs carry join keys only (code, model_code, option_package_code_key). Feature attributes — name, prices, flags, classification, colors — are deliberately not derived from inventory: they will come from the dedicated BMW US product-data source (separate scrape, upcoming ticket), which feature_refs join to on feature_code+model_code. Until that source is ingested, the full option payload remains available in the raw envelope.

Known diff (reviewed decision, 2026-07-17): the archive snapshots ~91 of the ~124 Postgres columns. The team reviewed the missing set — e.g. exteriorgenericcolor, transmissiontype, doors, modelvariant, lotdate, dealer contact fields, *thirdparty prices — and confirmed none are needed: they are either unimportant or already covered by other raw sources. (retailtype is a coarser sibling of vehicleretailtype — not 1:1; the archive carries vehicleretailtype, the granular field the candidate-selection reference requires, mapped as retail_type.) No snapshot-job change is planned; this pipeline's raw tier is faithful to the archive, which is its source boundary.

Backfill

Native Dagster partition backfill — materializing any past partition reads archive_date = partition_date with as-of-date fidelity. Cost: each partition scans ~18–47 GB (≈ $0.30); a multi-year backfill is tens of TB. For large ranges, consider a one-time copy of the needed columns partitioned by archive_date first, or backfill in coarse batches. Check for archive_date gaps before launching a large range. Never materialize a partition for a date whose snapshot has not landed — the component fails loudly on an empty partition rather than writing an empty day.

Fixtures

tests/capture.py::capture_bmwusa_inventory captures the envelope fixture (bmw_bmwusa_inventory_us_sample.json) by running the production SQL against the latest landed archive partition (one partition scan per capture run).