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 thesold != 'true'subset (~166k vehicles, ~12.8M option rows of the 3.4M / 247M totals). -
Daily query: live vehicles plus vehicles with
solddatein 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 thesoldcolumn. 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 ownmodelyearvalues rather than a hardcoded list — so the window advances by itself and a backfilled partition keeps its own top three. Vehicles whosemodelyearwill notSAFE_CASTto an integer are dropped with the older years. This does not reduce scanned bytes (modelyearis already read viat.*); 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_statuscounts down as the vehicle advances.'7'is the earliest stage and'0'the latest, soorder_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_statusrows has production_datehas arrival_datehas sold_datereading 76,974 0% 0% 0% ordered (89% valid_customer_order)647,383 0% 0% 0% on order 518,101 2% 0% 0% entering production 49,866 99% 0% 0% produced 312,533 100% 0% 1% in transit 27,597 100% 1% 2% arriving 172,605 100% 100% 8% at dealer 01,335 100% 100% 33% delivered / sold -
eventidis the id of the vehicle's latest logistics event. It is a 3-digit code.eventdescriptiondoes not always matcheventid. Rows witheventid298have many different descriptions. Identify the event byeventidalone. The table lists each id observed in the 2026-10-15 partition with the description that most of its rows have:eventideventdescription047SPEC. 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 samearchive_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:
| 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).