Skip to main content

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_statusrowshas production_datehas arrival_datehas sold_datereading
    76,9740%0%0%ordered (89% valid_customer_order)
    647,3830%0%0%on order
    518,1012%0%0%entering production
    49,86699%0%0%produced
    312,533100%0%1%in transit
    27,597100%1%2%arriving
    172,605100%100%8%at dealer
    01,335100%100%33%delivered / sold
  • eventid is the id of the vehicle's latest logistics event. It is a 3-digit code. eventdescription does not always match eventid. Rows with eventid 298 have many different descriptions. Identify the event by eventid alone. The table lists each id observed in the 2026-10-15 partition with the description that most of its rows have:

    eventideventdescription
    047SPEC. RELEASED-NO QUOTA AVAIL.
    087PROD. WEEK ASSIGNED
    100ORDER DELETED BY AG
    102SPECIAL REQUEST ORDER REC. AG
    105NO PRODUCTION WEEK AVAILABLE
    111ORDER ACCEPTED AT AG
    112SCHEDULED FOR DAILY PRODUCTION
    150PRODUCTION STARTED
    151ENTRY BODY SHOP/SERIAL # ASSGN
    152ENTRY PAINT SHOP
    153ENTRY ASSEMBLY
    154ASSEMBLY
    155ASSEMBLY FINISH /QUALITY CHECK
    160VEHICLE HANDOVER TO SALES
    165TRANSIT FROM PLANT TO PORT DEP
    181HANDOVER TO DISTRIBUTION
    182HANDOVER TO CARRIER/LOAD ASSIG
    190DISPATCHED FROM AG
    193ARRIVAL AT PORT OR COMPOUND
    195EXIT FROM PORT OR COMPOUND
    198ARRIVAL AT DEALERSHIP
    295RETAIL SALE - SF
    298no majority; BMW's event code list defines it as a retail sale
  • 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, with job project ai-app-bmw / BMW_GCP_PROJECT. GCP Access describes the credentials. The query job, and the Storage Read session that streams its results, run in ai-app-bmw. The query reads the archive tables in bmw-prod-309117, so the federated role also needs BigQuery Data Viewer in bmw-prod-309117.

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:

ColumnDisposition
salespersonPII — excluded in the SQL, never enters the lake.
customernamePII — used only inside BigQuery to derive customer-order booleans, then excluded before raw JSON serialization.
codeExact duplicate of modelcode (verified) — raw-only.
id, postgres_id, source_timestamp, archive_dateSnapshot/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:

FieldMeaning
customer_name_presentSource value is non-null/non-empty after trimming.
customer_name_valid_patternLegacy valid_customername() pattern: present, long enough (or literal sold), and not a placeholder/color/stock keyword.
customer_name_internal_use_patternMatches internal/rental/service-loaner/demo patterns used by legacy abnormal_sale.
customer_name_repeated_for_dealerSame normalized name appears at least 10 times for the dealer within the emitted inventory population.
valid_customer_orderPriority 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).