Automotive EDW data model
Data Vault 2.0 raw vault for automotive: hubs (vehicle/customer/dealer/part/service_order), links (vehicle-customer, vehicle-dealer, service-order-vehicle, service part used), and satellites (vehicle, customer PII, dealer, part details + a service-order-status effectivity satellite and a vehicle-telemetry satellite). Hash keys and load metadata are generated by the DDL layer.
Automotive enterprise data warehouse modeled as a Data Vault 2.0 raw vault: vehicle (VIN), customer, dealer, part, and service-order hubs; vehicle-customer / vehicle-dealer / service-order-vehicle links; a non-historized service-part-used link carrying quantity and cost; and descriptive (PII), telemetry, and effectivity satellites — built to integrate DMS, CRM, connected-car telemetry, and parts systems with full historization for warranty, recall, and GDPR auditability. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 15
- tables
- 100
- columns
- 14
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs isolate automotive business keys
The VIN, customer, dealer, and part identities are mastered in separate DMS, CRM, and catalog systems; isolating them in hubs lets a vehicle's ownership, service, and telemetry histories attach over its whole lifecycle as it changes hands.
Non-historized service-part-used link
Parts issued against a repair order are immutable line-level events, so the usage link is insert-only and carries quantity and cost directly with no satellite.
Telemetry split into its own satellite
Connected-car readings change orders of magnitude faster than vehicle master data; a dedicated high-rate-of-change satellite keeps telemetry history isolated and cheap to load.
Customer PII isolated in its own satellite
Splitting personal data into a dedicated, masked satellite makes GDPR right-to-erasure and consent enforcement surgical without disturbing warranty and service structures.
Auditability drives Data Vault in automotive
Load_date + record_source on every row give queryable lineage for warranty claims, recall campaigns, and ISO-26262 traceability without scraping DMS logs.
Tables in the Automotive EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_vehicle
Grain: one row per unique hub_vehicle business key
| Column | Type | Keys |
|---|---|---|
| vin | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_customer
Grain: one row per unique hub_customer business key
| Column | Type | Keys |
|---|---|---|
| customer_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_dealer
Grain: one row per unique hub_dealer business key
| Column | Type | Keys |
|---|---|---|
| dealer_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_part
Grain: one row per unique hub_part business key
| Column | Type | Keys |
|---|---|---|
| part_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_service_order
Grain: one row per unique hub_service_order business key
| Column | Type | Keys |
|---|---|---|
| service_order_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_vehicle_customer
Grain: one row per lnk_vehicle_customer relationship
Which customer owns which vehicle.
| Column | Type | Keys |
|---|---|---|
| vehicle_hk | binary | |
| customer_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_vehicle_dealer
Grain: one row per lnk_vehicle_dealer relationship
Which dealer sold or services a vehicle.
| Column | Type | Keys |
|---|---|---|
| vehicle_hk | binary | |
| dealer_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_service_order_vehicle
Grain: one row per lnk_service_order_vehicle relationship
Which vehicle a service order is opened against.
| Column | Type | Keys |
|---|---|---|
| service_order_hk | binary | |
| vehicle_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_service_part_used
Grain: one row per part issued against a service order (non-historized link)
Transactional (non-historized) link — insert-only part-usage line events.
| Column | Type | Keys |
|---|---|---|
| service_order_hk | binary | |
| part_hk | binary | |
| usage_id | varchar | BK |
| usage_ts | timestamp | |
| quantity_used | decimal | |
| warranty_flag | boolean | |
| part_cost | decimal | |
| labor_cost | decimal | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_vehicle_details
Grain: one row per vehicle_hk per change
Vehicle descriptive history.
| Column | Type | Keys |
|---|---|---|
| vehicle_hk | binary | PK |
| load_date | timestamp | PK |
| make | varchar | |
| model | varchar | |
| model_year | smallint | |
| trim | varchar | |
| body_style | varchar | |
| powertrain | varchar | |
| color | varchar | |
| build_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_customer_details
Grain: one row per customer_hk per change
Customer descriptive history (PII; GDPR-governed).
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| national_id | varchar | PII |
| address | varchar | PII |
| marketing_consent | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_dealer_details
Grain: one row per dealer_hk per change
Dealer descriptive history.
| Column | Type | Keys |
|---|---|---|
| dealer_hk | binary | PK |
| load_date | timestamp | PK |
| dealer_name | varchar | |
| dealer_group | varchar | |
| region | varchar | |
| city | varchar | |
| country | varchar | |
| franchise_brand | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_part_details
Grain: one row per part_hk per change
Part descriptive history.
| Column | Type | Keys |
|---|---|---|
| part_hk | binary | PK |
| load_date | timestamp | PK |
| part_name | varchar | |
| part_category | varchar | |
| supplier | varchar | |
| superseded_by | varchar | |
| list_price | decimal | |
| warranty_months | smallint | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_service_order_status
Grain: one row per service order per status effectivity change
Effectivity satellite tracking the valid period of each service-order status.
| Column | Type | Keys |
|---|---|---|
| service_order_hk | binary | PK |
| load_date | timestamp | PK |
| order_status | varchar | |
| service_type | varchar | |
| technician_name | varchar | PII |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_vehicle_telemetry
Grain: one row per vehicle per telemetry reading (high-rate-of-change satellite)
Connected-car telemetry satellite split out for its high rate of change.
| Column | Type | Keys |
|---|---|---|
| vehicle_hk | binary | PK |
| load_date | timestamp | PK |
| odometer_km | decimal | |
| fuel_level_pct | decimal | |
| battery_soc_pct | decimal | |
| engine_health_score | decimal | |
| dtc_count | smallint | |
| reading_ts | timestamp | |
| load_end_date | timestamp | |
| record_source | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_vehicle_customer.vehicle_hk → hub_vehicle (many-to-one)
- lnk_vehicle_customer.customer_hk → hub_customer (many-to-one)
- lnk_vehicle_dealer.vehicle_hk → hub_vehicle (many-to-one)
- lnk_vehicle_dealer.dealer_hk → hub_dealer (many-to-one)
- lnk_service_order_vehicle.service_order_hk → hub_service_order (many-to-one)
- lnk_service_order_vehicle.vehicle_hk → hub_vehicle (many-to-one)
- lnk_service_part_used.service_order_hk → hub_service_order (many-to-one)
- lnk_service_part_used.part_hk → hub_part (many-to-one)
- sat_vehicle_details.vehicle_hk → hub_vehicle (many-to-one)
- sat_customer_details.customer_hk → hub_customer (many-to-one)
- sat_dealer_details.dealer_hk → hub_dealer (many-to-one)
- sat_part_details.part_hk → hub_part (many-to-one)
- sat_service_order_status.service_order_hk → hub_service_order (many-to-one)
- sat_vehicle_telemetry.vehicle_hk → hub_vehicle (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.