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 inventory-live-feed-processor Postgres database.

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. The same live + trailing-7-day-sold population is also used for per-dealer repeated customer-name detection.

• Model-year scope: limited to the RECENT_MODEL_YEARS (3) newest model years present in that snapshot, via a CTE over the partition's own modelyear values rather than a hardcoded list — so the window advances by itself and a backfilled partition keeps its own top three. Vehicles whose modelyear will not SAFE_CAST to an integer are dropped with the older years. This does not reduce scanned bytes (modelyear is already read via t.*); it cuts downstream row volume only.

• Sold rows carry snapshot-date state: fields/options are as of archive_date, not as of the sale moment.

• order_status counts down as the vehicle advances. '7' is the earliest stage and '0' the latest, so order_status <= '5' selects "in production or later". Every value is a single digit, which is what makes the string comparison equivalent to a numeric one. A two-digit status would silently break that, since '10' < '6' lexicographically. Stage inferred from field-presence correlations, not vendor documentation; shares below are one partition's 176,394 rows:

  order_status	rows	has production_date	has arrival_date	has sold_date	reading
  7	6,974	0%	0%	0%	ordered (89% valid_customer_order)
  6	47,383	0%	0%	0%	on order
  5	18,101	2%	0%	0%	entering production
  4	9,866	99%	0%	0%	produced
  3	12,533	100%	0%	1%	in transit
  2	7,597	100%	1%	2%	arriving
  1	72,605	100%	100%	8%	at dealer
  0	1,335	100%	100%	33%	delivered / sold

• 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 structure: 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; PII/bookkeeping fields listed below are excluded in SQL before raw JSON serialization. Notes:

Column	Disposition
salesperson	PII — excluded in the SQL, never enters the lake.
customername	PII — used only inside BigQuery to derive customer-order booleans, then excluded before raw JSON serialization.
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, which feature_refs join to on feature_code+model_code. The full option payload remains available in the raw envelope.

Known diff: 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.

Customer-name privacy boundary

customername is used only inside the BigQuery query and is excluded before raw JSON serialization. The lake stores non-PII booleans instead:

Field	Meaning
customer_name_present	Source value is non-null/non-empty after trimming.
customer_name_valid_pattern	Legacy valid_customername() pattern: present, long enough (or literal sold), and not a placeholder/color/stock keyword.
customer_name_internal_use_pattern	Matches internal/rental/service-loaner/demo patterns used by legacy abnormal_sale.
customer_name_repeated_for_dealer	Same normalized name appears at least 10 times for the dealer within the emitted inventory population.
valid_customer_order	Priority 1/U row with a valid pattern, no internal-use match, and no repeated-name match.

True pre_inventory_sale remains a downstream point-in-time lifecycle concept; it depends on history/status timing and should not be inferred from one raw row.

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 completed — 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 completed archive partition (one partition scan per capture run).