BMWUSA Build Your Own (BYO) Source

Overview

Public, unauthenticated API serving model catalog and feature/option data for BMW USA. Raw + transformed tiers only.

API

Base URL: http://qa.configure.bmwusa.com/UBYOConfigurator Auth: None (public) Schedule: Daily at 08:00 UTC / 04:00 EDT (0 8 * * *). BYO catalog refreshes 02:00–03:00 EDT (06:00–07:00 UTC); the schedule runs >=1h after refresh completes.

Endpoint	Purpose
GET /v2/static/typelist?full=true	All product codes with series/bodystyle metadata
GET /v4/BM/model/{productCode}	Model detail (specs, pricing, flags)
GET /v4/BM/techspecs/{productCode}	Nested techspecs (engine, transmission, performance)
GET /v4/BM/options/{productCode}	Features/options array per model
GET /v4/configuration/start/{productCode}?trustedSource=true	Configurator session — which features are configurable

Assets

One raw asset per API endpoint, each with a corresponding transformed asset.

Dependency chain (raw):

• Typelist: no upstream (fetches the full product array as a single raw row)
• Models, Techspecs: upstream = typelist (reads product codes from the typelist body array)
• Configuration, Options: upstream = models (reads product codes from _request_params)

Raw asset	Component	Transformed entity
bmw/us/raw/bmwusa_byo_typelist	BmwUsaByoTypelistRawComponent	BmwUsaTypelistEntity via extract_typelist
bmw/us/raw/bmwusa_byo_models	BmwUsaByoModelsRawComponent	BmwUsaModelEntity via extract_models
bmw/us/raw/bmwusa_byo_techspecs	BmwUsaByoTechspecsRawComponent	(raw only — no transformed asset; consumed at consolidated)
bmw/us/raw/bmwusa_byo_configuration	BmwUsaByoConfigurationRawComponent	BmwUsaFeatureEntity via extract_configuration (is_configurable=True)
bmw/us/raw/bmwusa_byo_options	BmwUsaByoOptionsRawComponent	BmwUsaFeatureEntity via extract_options (is_configurable=None)

Transformed assets:

Transformed asset	Entity	Source key
bmw/us/transformed/bmwusa_byo_typelist	BmwUsaTypelistEntity	[bmwusa_byo, typelist]
bmw/us/transformed/bmwusa_byo_models	BmwUsaModelEntity	[bmwusa_byo, models]
bmw/us/transformed/bmwusa_byo_configurable_features	BmwUsaFeatureEntity	[bmwusa_byo, configuration]
bmw/us/transformed/bmwusa_byo_options	BmwUsaFeatureEntity	[bmwusa_byo, options]

Start dates

Typelist, models, and options share byo_start_date (matches the 1-year inventory backfill horizon). Configuration uses byo_configuration_start_date — no legacy BQ data exists for this endpoint, so no backfill is possible.

Entity mapping

Typelist → BmwUsaTypelistEntity

Standalone entity in entities.py with validation_alias mappings. Extractor uses model_validate.

BYO field	Entity field	Notes
code	model_code	Product code (e.g. 267S)
agCode	ag_code
name	model_name
series.code	series_code	Nested object
series.name	series_name	Nested object
modelRange	model_range_code
modelYear	model_year
bodyStyle.code	body_type_code	Nested object
bodyStyle.name	body_type_name	Nested object
engineDriveType	drive_type
sportsFlag	sports_flag
fuelType	fuel_type
mPerformanceFlag	m_performance_flag

Models → BmwUsaModelEntity

Standalone entity in entities.py with validation_alias mappings. Extractor uses model_validate_json.

Series/bodyStyle fields are NOT in the model detail response — they come from the typelist and are joined at the consolidated tier.

BYO field	Entity field	Notes
code	model_code	Product code
modelRange	model_range_code
name	model_name
year	model_year
bodyStyle	body_type_name	String (not nested object)
fuelType	fuel_type	Single letter (E, D, G)
driveTrain	drive_train
driveType	drive_type
price	base_price
horsepower	horsepower
acceleration	acceleration
sportsFlag	sports_flag
mPerformanceFlag	m_performance_flag
isElectrified	is_electrified
isSdrive	is_sdrive
isXdrive	is_xdrive
destinationAndHandling	destination_and_handling
modelRangeName	model_range

Features (options + configuration) → BmwUsaFeatureEntity

Standalone entity in entities.py with validation_alias mappings. Extractor uses model_validate on the option dict; context-driven fields (model_code, brand, currency, is_configurable) are set post-validation.

BYO field	Entity field	Notes
code	feature_code
(from params)	model_code	Injected from product code
name	name
longDescription	description
type	feature_type
familyCode	family_code
isStandard	is_standard
listPrice	price_gross
isPackage	is_package
(derived)	is_configurable	True from configuration; None from options
combinationPrices	combination_prices	[{combFeatCode, combPrice}] → [CombinationPrice]
(flattened)	parent_package_code	Set when feature is a package alternative
(flattened)	slot_default	Code of the default alternative in the slot

Key fields: feature_code, model_code, parent_package_code.

Package flattening

Packages with packageContent.packageSlots are flattened to one row per alternative:

• Package row: feature_code="S0ZPK", parent_package_code=None, is_package=True
• Alternative rows: feature_code="S0776", parent_package_code="S0ZPK", slot_default="S0776" (first alternative = default)

Standalone features that also appear inside packages get both a standalone row and a row per package appearance.

Proxy-code slot re-listing

Some option codes are not a wheel themselves but always add a corresponding wheel — selecting S0ZWH adds wheel S01U0 on the affected models. A package slot built around the proxy therefore offers the same choice as the slot built around the wheel it adds. Both the options endpoint (loadtype=full) and the configuration endpoint (trustedSource=true) return hidden package content, so both slots come back. Without handling, one feature appears in two slots of the same package with conflicting slot_default, which appears as a discrepancies check failure on bmwusa_byo_features and bmwusa_byo_configurable_features.

_drop_relisted_slots resolves this at extract time: two slots whose alternative code sets differ by exactly one code on each side offer the same choice when one of those codes is a proxy for the other, so the proxy copy is dropped. Observed structure for model 274M, package S0ZJJ:

copy	alternatives	slot_default
proxy (dropped)	S01T8, S01T9, S01U1, S0ZWH	S01T8
wheel (kept)	S01U0, S01T8, S01T9, S01U1	S01U0

_PROXY_OPTION_CODES holds the known proxy codes. It is a single entry today (S0ZWH); add to it if another proxy code turns up, rather than broadening the matching rule.

Two properties worth preserving if this is ever revisited:

• Set comparison, not position. BMW's packageSlots order is not stable between fetches, and the proxy copy is not reliably last — in 274M it is at index 0. Any positional rule ("take the first slot") picks a different winner on different days.
• Pair-scoped. A slot is only dropped once its wheel twin is found in the same package, so a package exposing only the proxy copy keeps it. The filter never causes a net loss of slots.

Configurable derivation

Options and configuration produce separate assets at the transformed tier. At the consolidated tier, the two are unioned on (feature_code, model_code, parent_package_code). The options asset has is_configurable=None; the configuration asset has is_configurable=True. The tiebreak picks non-null, so any feature present in both gets is_configurable=True in the consolidated output.

The configuration extractor collects all feature codes from the configuration/start response blocks (designs, tops, colors, wheels, upholstery, trims, packages, featuredPackages, vehiclePrograms as lists; options, accessories as dicts of lists), then looks up each code in optionDetails to build full feature records.

BQ backfill

Legacy tables in app-template-382723 — the project gcp-dataform-inventory declares as the source of record. A same-named pair exists in ai-app-bmw; do not use it. It is missing model codes (156 vs 202 distinct code for 2026-06-25) and truncates package_content.packageSlots[].alternatives to one entry per slot, so it looks plausible while silently under-reporting.

Table	Backfill target	Reshape
product.raw_model_metadata	bmwusa_byo_typelist	_reshape_byo_typelist
product.raw_model_metadata	bmwusa_byo_models	_reshape_byo_model
product.raw_model_metadata	bmwusa_byo_techspecs	_reshape_byo_techspecs
product.raw_feature_metadata	bmwusa_byo_options	_reshape_byo_options

No backfill for configuration — the legacy scraper did not call the configuration/start endpoint.

The model metadata table (~272k rows) serves three backfill targets. Each reshape reverses the legacy scraper's flat snake_case storage back to camelCase BYO response format. The techspecs reshape unflattens __-separated columns (e.g. engine__combined_horse_power) into the nested dict the API originally returned.

The feature metadata table (~42M rows) uses ByoFeatureBackfillReader, which groups rows by product_code with ARRAY_AGG before reshaping.

Duplicate source rows

The legacy collector runs twice daily, and both tables are append-per-fetch, so every entity has exactly two rows per date (rows / distinct code = 2.0 on every partition checked). bq_dedup_on keeps the newest row per entity — [code] for model metadata, [product_code, code] for feature metadata. For the feature reader the dedup sits inside the ARRAY_AGG, since GROUP BY product_code would otherwise collapse the duplicates into one row with every feature listed twice.

Structure parity with live

typelist is a single /v2/static/typelist call stored as one row holding the whole array, so its backfill uses ByoTypelistBackfillReader to aggregate the partition into one row. Emitting one row per model gives backfilled partitions a structure no live partition has, breaking raw row counts and the row-count-deviation check. models and techspecs are genuinely per-code (/v4/BM/model/{code}, /v4/BM/techspecs/{code}), so one row per model is correct for those.

Fidelity limit: hidden package content is not recoverable

The legacy collector called /v4/BM/options/{code} without loadtype=full (byo.ts getFeatures), so hidden package content was never captured. Measured against the live endpoint for 262V:

	slots	alternatives
default (what the backfill has)	54	83
loadtype=full (what the live asset fetches)	88	182

Backfilled bmwusa_byo_features therefore runs roughly 73% of live on package children (mean 250 vs 342 features per model across 176 shared model codes; 172 of 176 lower, none higher). That is a property of the source, not a bug — do not compare feature counts across the backfill boundary. It also means _drop_relisted_slots is inert on backfilled partitions: the proxy/wheel duplicate slot pairs exist only in hidden content.

One further gap, unrelated to loadtype: raw_model_metadata holds 181 codes where typelist lists 182, because getProduct returns null when /v4/BM/model/{code} fails and drops the product. Probing all 182 codes found exactly one 404 (262X).

VDML vs BYO decision

The legacy Dataform pipeline unions VDML and BYO data with VDML winning per model_code. Analysis shows VDML adds no unique value for active inventory:

• Models: 302 VDML-only model codes are all retired years (2018–2023). BYO covers all 486 active models.
• Features: 770 VDML-only feature codes are from those same retired years. BYO covers all active features.
• Fields: VDML's unique fields (productClass, salesGroup) are not consumed in the pipeline. productClass ≡ BYO's familyCode (which is used). salesGroup exists in BYO's raw_feature_metadata but is unused.

Decision: Ingest BYO only. Feature-type classification that VDML provides via pre-typed arrays is handled at the transformed tier using BYO's type field.