Netstar Source (BigQuery)
Netstar is Mercedes-Benz's internal system of record. The ai_mercedes project
reads BigQuery tables that mirror the Netstar data.
Required Environment Variables
| Variable | Default | Purpose |
|---|---|---|
MERCEDES_GCP_PROJECT | ai-app-mercedes | GCP project that holds the Netstar dataset |
The BigQuery dataset is always netstar.
Authentication
GCP Access describes the BigQuery credentials.
Dagster Resource Key
The BigQuery client is the BIG_QUERY_RESOURCE_KEY resource ("big_query_resource")
exported from ai_dagster.components. Every Netstar raw component extends
NetstarBQRawSourceComponent, which declares that key in _required_resource_keys()
and has a _bq_dataset(bq) helper that builds the fully qualified dataset string from
the resource's project.
Tables
| Resource | Table | Notes |
|---|---|---|
| Models | netstar.model-details | Model variants. baumuster is the manufacturer code |
| Features | netstar.accessories | Option and package catalog keyed by baumuster and modelYear |
| Inventory | netstar.inventory_* | Wildcard over per-day tables. The latest revision per id is kept, ordered by _PARTITIONTIME |
| Sales | netstar.sales_* | Retailed vehicles. Source of the sold date and the rolling metrics. A partition reads the rows loaded on its date and the 2 days before. dealerCode is the table suffix, the dealer whose user collected the table |
The features fetch depends on models. The models response gives a map from
baumuster to model_code, and each accessories row resolves to a model_code through
it. The raw component YAML declares this dependency in upstream_dep.
Retail type and program
Each sales row has a retailType and a program. The transformed tier writes them to
retail_type and sales_program. The consolidated tier reads them as follows:
| Row | Consolidated inventory |
|---|---|
retailType C | The dealer cancelled the retail. inferred_sold_date is not the retail date. It is the last day netstar.inventory_* or NAFTA listed the vehicle. It is null until that day is more than 4 days before the partition date. A row in the sales table is not a listing. |
retailType R and program CVP or CVE | The dealer retailed the vehicle into a company vehicle program, such as its service loaner fleet. inferred_sold_date is the retail date, and sale_type is DEALER_USE. |
retailType R and a customer program: blank, NONE, MIB, MIBJU, MIBFS or MVP | inferred_sold_date is the retail date, and sale_type is CUSTOMER_RETAIL. |
retailType R and any other program, such as EAB, FEP, STFL, CORP, DEAP or FMC | A fleet or other non-retail sale. inferred_sold_date is the retail date, and sale_type is FLEET_RETAIL. |
A company vehicle has a sold date, so consolidated inventory does not count it as in
inventory. Nobody bought it, so the Mercedes inventory statistics list DEALER_USE in
excluded_sale_types, which leaves it out of the sales counts (see
Inventory Statistics). Dealers often retail several aged
vehicles into CVP on one day. Counted as sales, those retails would raise the dealer's
sales rate. A fleet sale is a real sale, so the sales counts keep it.
A vehicle's sales records can name different programs. The consolidated tier reads the latest record for each PO number.
Sentinel prices
Netstar writes the placeholder price 7,777,777 on some rows. Extraction sets it to null, so a real price wins the coalesce at the consolidated tier.
Visibility flags
Each netstar.accessories row has three boolean flags: hidden, mboHidden and
defaultFlag. defaultFlag marks standard equipment.
The features extractor sets is_configurable to not hidden and not defaultFlag. It writes
mboHidden to a separate mbo_hidden column. The components of a package take the package's
flags.
Dealers order codes that have mboHidden set, and the factory builds vehicles with them. The
consolidated tier therefore applies mbo_hidden only to some values of feature_type:
feature_type | is_configurable of a code that Netstar lists |
|---|---|
UPHOLSTERY, WHEEL, INTERIOR_TRIM, HEADLINER, PACKAGE | not hidden and not defaultFlag |
PAINT, OPTION, ACCESSORY | not hidden and not mboHidden and not defaultFlag |
PAINT keeps the mboHidden condition because most paints with mboHidden set are
MANUFAKTUR paints, which customers order through P1 orders. OPTION keeps it because most
options with mboHidden set are standard or internal codes.
Netstar has no HEADLINER or INTERIOR_TRIM type. Only the consolidated tier derives these
values of feature_type, so the consolidated tier applies the rule. A code that Netstar does
not list keeps the is_configurable of the source that lists it. Where an order guide lists
the code, the guide's is_configurable overrides the value from Netstar or any other source;
see Order guides.
Category of package components
A package's components entry does not give the component's type or trim flag, so the
extractor gives every component the category option. The consolidated tier derives
feature_type from the category, which it reads as source_category. A component with the
category option gets the type OPTION, even when it is a wheel or a trim.
A code's standalone row is a row that lists the code with no parent_package_code. The
transformed asset gives each component the category of its code's standalone row in the same
model, such as wheel or trim. A component whose code has no standalone row in that model
keeps option.
Option ID normalization
Netstar accessory IDs separate the type code with an underscore ("0_413"). NAFTA configurator
IDs use a colon ("0:413"). The extractor rewrites the underscore to a colon, so the
same option has one option_id from either source.