Skip to main content

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​

VariableDefaultPurpose
MERCEDES_GCP_PROJECTai-app-mercedesGCP 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​

ResourceTableNotes
Modelsnetstar.model-detailsModel variants. baumuster is the manufacturer code
Featuresnetstar.accessoriesOption and package catalog keyed by baumuster and modelYear
Inventorynetstar.inventory_*Wildcard over per-day tables. The latest revision per id is kept, ordered by _PARTITIONTIME
Salesnetstar.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:

RowConsolidated inventory
retailType CThe 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 CVEThe 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 MVPinferred_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 FMCA 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_typeis_configurable of a code that Netstar lists
UPHOLSTERY, WHEEL, INTERIOR_TRIM, HEADLINER, PACKAGEnot hidden and not defaultFlag
PAINT, OPTION, ACCESSORYnot 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.