Logistics EDW data model
Data Vault 2.0 raw vault for logistics: hubs (shipment/carrier/location/order/vehicle), links (shipment-carrier, shipment-order, shipment-route, shipment-vehicle, tracking events), and satellites (carrier, location, order, vehicle details + shipment-status effectivity + vehicle telemetry). Hash keys and load metadata are generated by the DDL layer.
Logistics and freight enterprise data warehouse modeled as a Data Vault 2.0 raw vault: shipment, carrier, location, order, and vehicle hubs; carrier, order, route, and vehicle-assignment links plus a non-historized tracking-event link; and descriptive, effectivity, and telemetry satellites — built for integrating TMS, OMS, carrier, and telematics systems with full historization and end-to-end shipment lineage across a multi-carrier supply chain. 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.
- 17
- tables
- 103
- columns
- 18
- relationships
- 0
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Multi-location route link
A shipment route is a relationship between a shipment and two location roles (origin, destination); a single link with two location hash keys models this without duplicating the location hub or creating a cycle.
Non-historized tracking-event link
Tracking scans are immutable point-in-time events, so the tracking-event link is insert-only and carries its measures (geo, temperature) directly — no satellite needed.
Effectivity satellite on the shipment
A shipment moves through created, in-transit, delivered, and exception states; an effectivity satellite records the valid period of each status for as-of OTIF and exception reporting.
Telemetry split into its own satellite
Vehicle telemetry (speed, fuel, odometer) streams far more frequently than static vehicle attributes; a separate high-rate-of-change satellite keeps history compact and loads efficient.
Integration agility drives Data Vault in logistics
Carriers, telematics providers, and TMS instances change constantly; hubs of stable business keys plus load_date + record_source let new sources attach without reworking the warehouse.
Tables in the Logistics 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_shipment
Grain: one row per unique hub_shipment business key
| Column | Type | Keys |
|---|---|---|
| shipment_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_carrier
Grain: one row per unique hub_carrier business key
| Column | Type | Keys |
|---|---|---|
| carrier_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_location
Grain: one row per unique hub_location business key
| Column | Type | Keys |
|---|---|---|
| location_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_order
Grain: one row per unique hub_order business key
| Column | Type | Keys |
|---|---|---|
| order_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_vehicle
Grain: one row per unique hub_vehicle business key
| Column | Type | Keys |
|---|---|---|
| vehicle_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (5)
lnk_shipment_carrier
Grain: one row per lnk_shipment_carrier relationship
Which carrier is moving which shipment.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | |
| carrier_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_shipment_order
Grain: one row per lnk_shipment_order relationship
Which orders are consolidated into a shipment.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | |
| order_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_shipment_route
Grain: one row per lnk_shipment_route relationship
Origin and destination locations of a shipment.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | |
| origin_location_hk | binary | |
| destination_location_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_shipment_vehicle
Grain: one row per lnk_shipment_vehicle relationship
Which vehicle is assigned to a shipment.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | |
| vehicle_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_tracking_event
Grain: one row per shipment tracking scan (non-historized link)
Transactional (non-historized) link — insert-only tracking scans.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | |
| location_hk | binary | |
| vehicle_hk | binary | |
| tracking_event_id | varchar | BK |
| event_ts | timestamp | |
| event_type | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| temperature_c | decimal | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_shipment_status
Grain: one row per shipment status per effectivity change
Effectivity satellite tracking shipment lifecycle over time.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | PK |
| load_date | timestamp | PK |
| status_code | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| status_reason | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_carrier_details
Grain: one row per carrier_hk per change
Carrier descriptive history.
| Column | Type | Keys |
|---|---|---|
| carrier_hk | binary | PK |
| load_date | timestamp | PK |
| carrier_name | varchar | |
| carrier_mode | varchar | |
| scac_code | varchar | |
| service_level | varchar | |
| contract_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_location_details
Grain: one row per location_hk per change
Location descriptive history.
| Column | Type | Keys |
|---|---|---|
| location_hk | binary | PK |
| load_date | timestamp | PK |
| location_name | varchar | |
| location_type | varchar | |
| address | varchar | |
| city | varchar | |
| country | varchar | |
| timezone | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_order_details
Grain: one row per order_hk per change
Order descriptive history.
| Column | Type | Keys |
|---|---|---|
| order_hk | binary | PK |
| load_date | timestamp | PK |
| order_date | date | |
| customer_name | varchar | |
| incoterm | varchar | |
| order_value | decimal | |
| weight_kg | decimal | |
| volume_m3 | decimal | |
| priority | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
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 |
| vehicle_type | varchar | |
| license_plate | varchar | |
| capacity_kg | decimal | |
| reefer_capable | boolean | |
| ownership | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_tracking_telemetry
Grain: one row per vehicle per telemetry reading (high-rate-of-change satellite)
Telemetry satellite split out for its high rate of change.
| Column | Type | Keys |
|---|---|---|
| vehicle_hk | binary | PK |
| load_date | timestamp | PK |
| speed_kmh | decimal | |
| odometer_km | decimal | |
| fuel_level_pct | decimal | |
| engine_status | varchar | |
| reading_ts | timestamp | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_service_level
Grain: one row per carrier service level
| Column | Type | Keys |
|---|---|---|
| service_level_code | varchar | BK |
| service_level_name | varchar | |
| target_transit_days | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_shipment_carrier.shipment_hk → hub_shipment (many-to-one)
- lnk_shipment_carrier.carrier_hk → hub_carrier (many-to-one)
- lnk_shipment_order.shipment_hk → hub_shipment (many-to-one)
- lnk_shipment_order.order_hk → hub_order (many-to-one)
- lnk_shipment_route.shipment_hk → hub_shipment (many-to-one)
- lnk_shipment_route.origin_location_hk → hub_location (many-to-one)
- lnk_shipment_route.destination_location_hk → hub_location (many-to-one)
- lnk_shipment_vehicle.shipment_hk → hub_shipment (many-to-one)
- lnk_shipment_vehicle.vehicle_hk → hub_vehicle (many-to-one)
- lnk_tracking_event.shipment_hk → hub_shipment (many-to-one)
- lnk_tracking_event.location_hk → hub_location (many-to-one)
- lnk_tracking_event.vehicle_hk → hub_vehicle (many-to-one)
- sat_shipment_status.shipment_hk → hub_shipment (many-to-one)
- sat_carrier_details.carrier_hk → hub_carrier (many-to-one)
- sat_location_details.location_hk → hub_location (many-to-one)
- sat_order_details.order_hk → hub_order (many-to-one)
- sat_vehicle_details.vehicle_hk → hub_vehicle (many-to-one)
- sat_tracking_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.