Dealer-Website Inventory
Each dealer-website provider's transformed tier writes <oem>/<market>/transformed/<provider>_inventory.
The provider docs describe each source:
Each table holds one row per VIN. Each source's row model subclasses DealerSiteVehicle
(ai_public_api.dealer_providers.vehicle).
Sites and rooftops
A dealer group's website can list the vehicles of several of the group's rooftops, so a provider names two dealers on each listing, both by the provider's own ids:
provider_site_idis the site queried.provider_dealer_idis the rooftop holding the vehicle.
site_dealer_code is the OEM dealer code the raw request recorded for the queried site. On a
sister store's listing, it is not the code of the rooftop holding the vehicle.
listed_on_holder_site records whether the queried site is the holding rooftop's own site. On TVM,
DealerOn and Dealer.com a site and its own rooftop have the same id, so listed_on_holder_site is true
where provider_site_id equals provider_dealer_id and false where they differ. A Dealer
Inspire rooftop id is a different id from the site's. Some Dealer Inspire rooftop ids are dealer
codes, so listed_on_holder_site is true where the rooftop id equals site_dealer_code, and
null otherwise.
A VIN listed by several of a group's sites arrives once per site. The tiebreak prefers the row
from the holding rooftop's own site, then the smallest provider_dealer_id, then the smallest
provider_site_id. On the holding rooftop's own site, price, stock_number and vdp_url
are that rooftop's own. The tiebreak is DEALER_SITE_TIEBREAK in
ai_public.components.utils, which each market's dealer_site_tiebreak template variable
returns.
Group sites also list other makes. Each market's registry wraps the dealer-site extractors in
filter_makes, so a listing of another make is dropped before deduplication and before the
unkeyed_records check counts it.
The stellantis/us Motive entry also keeps the makes in _MOTIVE_ADDITIONAL_MAKES. Motive
writes Wagoneer as the make of the Grand Wagoneer and the Grand Wagoneer L.
Stock date
date_in_stock is the date the site gives for the vehicle entering the dealer's stock. Motive
gives none; its date_in_stock is counted back from the hit's age, as described in
Transformed tier. A
vehicle cannot be in stock before the year preceding its model year, so a date_in_stock
in an earlier year than model_year - 1 is written as null. Such a date is a site's
placeholder. Some Dealer Inspire sites send
1969-12-31 or 2021-01-01. A row with a null model_year keeps its date_in_stock.
Dealer codes
Every column the transformed tier writes comes from the raw row, so re-materializing a partition gives the same rows. Only a Dealer.com response names the holding rooftop's OEM dealer code; see Transformed tier in the Dealer.com source doc. The other sources' transformed tiers do not map a holding rooftop to an OEM dealer code.
A TVM, DealerOn, Dealer.com or Motive rooftop id is the provider's own id, and only some Dealer Inspire rooftop
ids are dealer codes. A Dealer Inspire api_id takes several formats, described
in A ccid can serve more than one rooftop.
One Dealer Inspire dealer can also list its stock under several api_ids, as described in
Transformed tier. Mapping rooftop ids to dealer
codes needs evidence from another source, such as the OEM feed's dealer code for the same VIN,
so it belongs in an asset that reads both.
Rows read from Dealer Inspire raw partitions fetched before the raw component began recording
dealer_code have a null site_dealer_code. Every TVM, DealerOn, Dealer.com and Motive raw partition records it.