BMW BigQuery Source

BQ is used as a backfill source across multiple entities. The primary BQ project for dealer and inventory data is bmw-prod-309117; product/feature data lives in app-template-382723.product.

---

BmwDealerEntity

BQ source of truth: bmw-prod-309117.inventory.dealer_locator_data

BQ sample (US, 349 total): 34400 Herb Chambers BMW, Boston MA · 56357 BMW of Westlake, OH · 16815 Nashua NH

New fields to add (in BQ, not yet in entity)

BQ column	Entity field	C2B path	BQ backfill	║	C2B sample	BQ ✓	manual check	code note
dealer_id	new: us_dealer_id	—	✓	║	—	34400 · 56357 · 16815	✓	US-market operational dealer ID — what the InvSearch feed uses in dealerid. No C2B equivalent; BQ-backfill only. Needed to join dealer records to inventory. Different from ag_code for ~30% of dealers (e.g. Rallye BMW: dealer_id=69000, ag_code=56519).
address_line_two_primary	new	additionalStreet	✓	║	`` (empty)	Herb Chambers BMW · BMW of Westlake	✓	BQ stores the DBA/marketing display name here, not a secondary street address; no direct C2B equivalent
zipcode_long_primary	new	—	✓	║	—	02134-4618 · 44145-1531 · 03060-5224	✓	ZIP+4 format; C2B only provides short zip
market_id	new	—	✓	║	—	10 (New England) · 4 (Cleveland/Dayton)	✓	US BMW market ID; all 349 BQ rows populated; no C2B equivalent
market_name	new	—	✓	║	—	New England · Cleveland/Dayton · Central South	✓	all 349 BQ rows populated; no C2B equivalent
region_code	new	—	✓	║	—	E · C · A	✓	1-letter code; all 349 BQ rows populated; no C2B equivalent
region_name	new	—	✓	║	—	Eastern · Central · Southern	✓	all 349 BQ rows populated; no C2B equivalent

BQ columns not mapped

BQ column	Type	BQ sample	Notes
lat_wavg	FLOAT	42.350171	weighted-average lat — likely a derived/enriched field
long_wavg	FLOAT	-71.128163	weighted-average long — likely a derived/enriched field
sites	RECORD	—	unknown structure — needs inspection

---

BmwModelEntity

BQ source table: app-template-382723.product.raw_model_metadata — one row per model code, full specs

BQ samples (all i7, MY2026): 267S = i7 M70, 267R = i7 eDrive50, 267Q = i7 xDrive60

New fields to add (BQ-only)

BQ column	Proposed entity field	BQ 1 (267S)	BQ 2 (267R)	BQ 3 (267Q)	manual check	code note
name	model_name	i7 M70	i7 eDrive50	i7 xDrive60		need UCP path for non-BQ population
year	model_year	2026	2026	2026		BQ only; Stolo has no model year (same as inventory)
body_style	body_type_name	Sedan	Sedan	Sedan		natural language; consistent
bodystyle_code	body_type_code	02	02	02		numeric code; same coding as inventory bodystyle_code
fuel_type	fuel_type	E	E	E		single-letter code (E=electric); same coding as inventory fueltype
drive_train	drive_type	xDrive all-wheel drive	Rear-wheel drive	xDrive all-wheel drive		long-form natural language string; different from inventory enginedrivetype_name (xDrive)
price	base_price	168500	105700	124200
horsepower	horsepower	650	449	536
series_code	series_code	i7	i7	i7		same as series for i-models; may differ for others
sports_flag (BOOL)	sports_flag	false	false	false
m_performance_flag (BOOL)	m_performance_flag	true	false	false

Notes:

• series vs series_code in BQ: identical for i-models (i7). For standard numeric series likely match too — to confirm with a broader sample.
• model_to_visible_features.code is the model code (matches raw_model_metadata.code). The features array gives all visible feature codes for that model with their family_type.

---

BmwFeatureEntity

BQ source tables (access via app-template-382723.product):

• raw_feature_metadata — one row per feature/option code, full metadata
• model_to_visible_features — junction: model code → feature codes with family_type

BQ samples from raw_feature_metadata: S08S6 ("Prespecified for Export"), S08S9 ("Non Fuel Fahrzeug"), S08SM ("Visible chassis number") — these are internal/export-marker features with list_price=0 and empty family_code. More typical consumer option codes appear in model_to_visible_features (e.g., P0300=paint, S01T6=wheel, S0ZDY=package).

raw_feature_metadata — key columns

BQ column	Type	Maps to entity field	Notes
code	STRING	feature_code	5-char option code (e.g. S08SM)
product_code	STRING	model_code	model the feature belongs to (e.g. 242R)
name	STRING	name	English name (e.g. "Visible chassis number")
long_description	STRING	description	appears empty in sampled rows; marketing_text also null for service codes
type	STRING	feature_type	single-letter code (S, F, I, P) — same format as UCP ✓
family_code	STRING	family_code	empty string for sampled service codes; non-empty for other types
is_standard	BOOL	is_standard	boolean — format matches UCP ✓
list_price	FLOAT64	price_gross	only price field — no separate net price column
is_package	BOOL	—	no entity field yet; candidate for future addition
sales_group	STRUCT<code, name>	—	e.g. {code: "Others", name: "Others"}
short_name	STRING	—
marketing_text	STRING	—	separate from long_description
rgb_values	ARRAY<STRING>	—	paint colors
cosy_url	STRING	—	360° image URL
is_paint, is_upholstery, is_wheel, is_trim, is_accessory, is_package, is_line, is_individual	BOOL	—	feature category flags
package_content	STRUCT	—	package slot definitions
update_timestamp	TIMESTAMP	—

Not in BQ: price_net, currency, brand — currency is USD (inferred for US market); brand must be set from request context.

raw_feature_metadata sample rows

feature_code	name	feature_type	family_code	is_standard	list_price	product_code	is_package
S08S6	Prespecified for Export	S	``	false	0	242R	false
S08S9	Non Fuel Fahrzeug	S	``	false	0	242R	false
S08SM	Visible chassis number	S	``	true	0	242R	false

242R is a model code — these service-type S options have empty family_code and 0 price. Real option prices and descriptions will appear in non-service feature records.

model_to_visible_features — structure

BQ column	Type	Notes
code	STRING	model code (e.g. 26TQ, 268A)
features	ARRAY<STRUCT<code STRING, family_type STRING>>	visible features per model
upload_timestamp	DATETIME

family_type values: OPTION, PACKAGE, PAINT, WHEEL, FABRIC, TRIM, PROGRAM — this is a model-level grouping distinct from raw_feature_metadata.type.

model_to_visible_features sample (model 26TQ)

family_type distribution: 14× PAINT, 6× WHEEL, 16× FABRIC, 3× TRIM, 4× PACKAGE, 5× PROGRAM, 14× OPTION (incl. S07ME).

Paint codes (P0300, P0475, P0C31, ...) appear here — confirms paint codes are tracked in this US system as visible features, separate from the options table.

BQ options table cross-check (public_invsearchvehicles_options)

Code	US BQ found	US BQ name	Rows
S05DM	✅	Parking Assistant	185k
S06NX	✅	Wireless Device Charging	1.21M
S0534	✅	Automatic climate control / Dual Zone Auto Climate Control	1.01M
S03DZ	✅	Without Lines designation	1.41M
S0478	❌ EU	child seat ISOFIX EU standard	—
S0654	❌ EU	DAB radio — EU-only	—
P0C6R	❌	Individual paint — tracked in model_to_visible_features as PAINT, not options table	—

orderable_attributes cross-check (bmw-prod-309117.dataform)

Schema: series_code, body_style_group, sports_group, model_id, model_code, attribute_type, attribute_code, attribute_name.

Code	found	US name
S05DM	✅	Parking Assistant
S06NX	✅	Wireless Device Charging
S0534	✅	Automatic climate control
S07ME	✅	M Driver's Package
S07DS	❌	EU service package
FKUFU	❌	upholstery code

Notes

• family_type in model_to_visible_features (OPTION, PACKAGE, PAINT, ...) ≠ type in raw_feature_metadata (single-letter) — different categorization systems.
• family_code in BQ appears empty for service-type options; populated for paint/fabric/trim.
• Paint codes (P*) tracked as features in model_to_visible_features (family_type=PAINT), not in InvSearch options table.
• is_package is in BQ as a BOOL; no entity field yet — candidate addition.
• raw_feature_metadata.product_code (e.g., 242R) is a product-line grouping, not a model code — do not confuse with model_code.