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:
- Each source's partition
Dis read. A source without that partition fails the asset. No earlier partition is read. - Each source's
adapterrenames and derives columns so that the source has every column in Output columns except_partition_dateandoem. - A
brandcolumn that holds a list gives one row per brand. - 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. - The sources are combined. Blank strings become null. A latitude or longitude of 0.0, NaN or infinity becomes null.
- The component's
fieldsare applied to the combined rows. - 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:
- Negate the longitude: (
latitude, −longitude). - 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
Matchwith match typeExact. ANon_Exactmatch, aTieand aNo_Matchleave 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:
| Value | Meaning |
|---|---|
source | The source's coordinates passed the check |
repaired | A repair passed the check and replaced the source's coordinates |
geocoded | Neither the source's coordinates nor a repair passed, and an Exact geocoder match of the address passed and replaced them |
rejected | Neither the source's coordinates, a repair nor the geocoder passed, so the coordinates are null |
unchecked | The row has coordinates, but its postal code is not a ZCTA in the vintage, so the source's coordinates are kept without a check |
| null | The 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, ornot_neededwhen no row was rejected before geocoding;rows_repaired,rows_geocodedandrows_rejected, the number of rows with each of those statuses;- the number of rows of each
coordinates_statusperoem; - 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
| Column | Meaning |
|---|---|
_partition_date | The partition date D |
oem | The first part of the source's asset key, such as gm |
brand | The brand in lowercase, with spaces and hyphens replaced by _, such as land_rover |
dealer_code | The OEM's own code for the dealer |
name | The dealer's name |
address | The street address |
city | |
region | The state or province |
postal_code | |
latitude, longitude | Float64. Null when the coordinate check rejects the source's coordinates |
phone | As the source writes it |
coordinates_status | The 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:
| OEM | Source | Not used |
|---|---|---|
| Audi | pss_dealers | onegraph_inventory_dealers, which has no partitions after the PSS source started |
| Genesis | dealer_list_dealers | inventory_search_dealers, which has no coordinates |
| Hyundai | dealer_details_dealers | dealer_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.
| Field | Default | Description |
|---|---|---|
market | required | The market's part of the asset key |
start_date | required | First partition. Set it to the latest start date among the sources |
sources | required | The transformed dealer assets. Each asset may be listed only once |
fields | empty | Polars-SQL fields applied to the combined rows of every source, such as a field that applies the market's postal code format |
zcta_asset | required | The asset of the ZCTA outlines |
zcta_vintage | required | The partition of zcta_asset that the coordinates are checked against. The asset waits for that partition to be materialized |
max_postal_code_km | required | The distance in kilometers from a row's ZCTA within which its coordinates pass |
Each source takes these fields:
| Field | Default | Description |
|---|---|---|
asset | required | The transformed dealer asset's key |
adapter | empty | rename, 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_by | empty | Columns that choose one row among the rows that share a brand and dealer code |