Skip to main content

CompositeDealersComponent

The composite dealers asset lists the dealers of every OEM and brand in one market. Its key is market/<market>/composite/dealers, and it reads the transformed dealer asset of each OEM. Assets under market/<market>/ cover every OEM in that market, so they have no oem part in their key.

Each row is one franchise, which is one brand's dealer code. The key is oem, brand and dealer_code. A dealer that sells several brands has one row for each brand. For example, a Stellantis dealer that sells Jeep and Ram under one dealer code has a jeep row and a ram row.

How a partition is built​

For partition date D:

  1. Each source's partition D is read. A source without that partition fails the asset. No earlier partition is read.
  2. Each source's adapter renames and derives columns so that the source has every column in Output columns except _partition_date and oem.
  3. A brand column that holds a list gives one row per brand.
  4. Where a source sets keep_first_by, rows that share a brand and dealer code are reduced to the row with the smallest values of those columns.
  5. The sources are combined. Blank strings become null. A latitude or longitude of 0.0, NaN or infinity becomes null.
  6. The component's fields are applied to the combined rows.
  7. Each row's coordinates are checked against its postal code, as Coordinate check describes.

A key that repeats, or a row with a null key column, fails the asset.

Coordinate check​

A row's coordinates are checked when the row has a latitude and a longitude and its postal_code is the GEOID of a ZIP Code Tabulation Area (ZCTA) in the pinned vintage of census/tiger/cb_zcta520. The coordinates pass when the point lies inside the ZCTA's outline or within max_postal_code_km of it. The distance is measured in meters, in a Lambert azimuthal equal-area projection centered on the ZCTA's centroid.

When the source's coordinates fail, these repairs are tried in order, and the first coordinates that pass replace the source's:

  1. Negate the longitude: (latitude, −longitude).
  2. Swap the coordinates and negate the new latitude: (−longitude, latitude).

When no repair passes, the row's address is geocoded, as Geocoding rejected rows describes. When the geocoder gives no coordinates that pass, the row's latitude and longitude become null. The row stays in the asset.

The repairs cover the two errors in a source's coordinates that the right coordinates can be computed from:

  • a longitude without its minus sign, such as (40.75352, 73.91753) for a dealer in Long Island City, NY 11101;
  • a latitude and longitude swapped, with the minus sign on the wrong value, such as (-88.08234, -42.04988) for a dealer in Schaumburg, IL 60195.

The source's coordinates and the repairs differ by a sign change or a swap, so for a dealer in a US ZCTA they lie thousands of kilometers apart, and at most one of them passes. The coordinates cannot be computed back from a latitude copied into the longitude, such as (29.9884, -29.9884), or from a mistyped digit, so no repair passes for such rows.

A postal code is a USPS delivery area, and a ZCTA approximates it from census blocks. A dealer at the correct coordinates can therefore lie a few kilometers outside its ZCTA, most often near the ZCTA's edge. max_postal_code_km allows for that difference. The US composite sets it to 10 km. A dealer that another OEM also lists within 300 m of the same point most likely has correct coordinates. Such dealers lie up to about 10 km from their ZCTA, and few lie farther.

Geocoding rejected rows​

For every row that the check and both repairs reject, the asset sends the row's address, city, region and postal_code to the Census Geocoder batch endpoint (locations/addressbatch, benchmark Public_AR_Current). It sends up to 1,000 addresses per request, so that a request returns within the 120 s timeout. The endpoint needs no API key. The geocoder's results are public domain, so the asset writes them to Iceberg. The Google Geocoding API's terms allow caching its coordinates for at most 30 days, so the asset does not use it.

A row gets the geocoder's coordinates only when all of these hold:

  • The geocoder returns Match with match type Exact. A Non_Exact match, a Tie and a No_Match leave the row rejected.
  • The geocoded point passes the same check against the row's ZCTA, without a repair.

A Non_Exact match can lie on a different street from the address. For the address 84 Rt 206 N, the geocoder matched 84 N GATE, 11 km from the dealer, and that point still passes a 10 km check against the dealer's ZCTA. The geocoder interpolates an Exact match's point along the street's address range, so the point is near the dealer but not on its building.

A geocoder request times out after 120 s without a response. When a geocoder request still fails after its retries, the asset logs a warning, and every rejected row stays rejected, including the rows of the requests that succeeded. The asset does not fail.

coordinates_status records the result:

ValueMeaning
sourceThe source's coordinates passed the check
repairedA repair passed the check and replaced the source's coordinates
geocodedNeither the source's coordinates nor a repair passed, and an Exact geocoder match of the address passed and replaced them
rejectedNeither the source's coordinates, a repair nor the geocoder passed, so the coordinates are null
uncheckedThe row has coordinates, but its postal code is not a ZCTA in the vintage, so the source's coordinates are kept without a check
nullThe source gives the row no latitude or no longitude

The coordinates_postal_code asset check has WARN severity. It passes when no row is rejected. Its metadata has:

  • geocoder_status: ran, failed, or not_needed when no row was rejected before geocoding;
  • rows_repaired, rows_geocoded and rows_rejected, the number of rows with each of those statuses;
  • the number of rows of each coordinates_status per oem;
  • up to 50 rejected rows with their source coordinates and their distance in kilometers from the ZCTA, farthest first. A row whose latitude or longitude is out of range has no distance and is listed first.

Output columns​

ColumnMeaning
_partition_dateThe partition date D
oemThe first part of the source's asset key, such as gm
brandThe brand in lowercase, with spaces and hyphens replaced by _, such as land_rover
dealer_codeThe OEM's own code for the dealer
nameThe dealer's name
addressThe street address
city
regionThe state or province
postal_code
latitude, longitudeFloat64. Null when the coordinate check rejects the source's coordinates
phoneAs the source writes it
coordinates_statusThe result of the coordinate check

Choosing a source​

Use one source for each OEM and brand. Where an OEM has two dealer assets, use the one that lists every dealer and has the most complete columns:

OEMSourceNot used
Audipss_dealersonegraph_inventory_dealers, which has no partitions after the PSS source started
Genesisdealer_list_dealersinventory_search_dealers, which has no coordinates
Hyundaidealer_details_dealersdealer_locator_dealers, whose coordinates are always 0.0

The Mercedes-Benz source, mercedes/mbusa/transformed/nafta_dealers, is built from inventory records, so it includes only the dealers that have inventory listed. The Ford source covers Ford and Lincoln dealers. Its records have no make column, so every dealer in it has the brand ford.

BMW USA lists a dealer's satellite locations under the dealer's code, each with its own location_id. The BMW source sets keep_first_by: [location_id], so each dealer's row describes its primary location, whose location_id is 01.

Configuration​

type: ai_public.components.composite.dealers.CompositeDealersComponent
attributes:
market: us
start_date: "2026-07-29"
zcta_asset: census/tiger/cb_zcta520
zcta_vintage: 2020
max_postal_code_km: 10
fields:
- name: postal_code
expr: "LEFT(postal_code, 5)"
sources:
- asset: gm/us/transformed/quantum_dealers_dealers
adapter:
rename:
dealer_name: name
state: region
zip_code: postal_code
phone_number: phone
fields:
- name: address
expr: "CONCAT_WS(', ', address_1, address_2, address_3)"

The file is defs/market/<market>/composite/defs.yaml in ai_public.

FieldDefaultDescription
marketrequiredThe market's part of the asset key
start_daterequiredFirst partition. Set it to the latest start date among the sources
sourcesrequiredThe transformed dealer assets. Each asset may be listed only once
fieldsemptyPolars-SQL fields applied to the combined rows of every source, such as a field that applies the market's postal code format
zcta_assetrequiredThe asset of the ZCTA outlines
zcta_vintagerequiredThe partition of zcta_asset that the coordinates are checked against. The asset waits for that partition to be materialized
max_postal_code_kmrequiredThe distance in kilometers from a row's ZCTA within which its coordinates pass

Each source takes these fields:

FieldDefaultDescription
assetrequiredThe transformed dealer asset's key
adapteremptyrename, then Polars-SQL fields, as in a consolidated source group. A source with one brand derives it as a literal, such as expr: "'kia'"
keep_first_byemptyColumns that choose one row among the rows that share a brand and dealer code