Overview

For the data share, Shipwell implemented a “star schema”, which is a simplified version of our internal data models. The goal of the star schema is to be simple, easy to understand, and still capture all the relevant data that you need. Read here for general context/background on star schemas.

Please do not hesitate to leave comments in this document or reach out to your Shipwell counterparts with questions.

Shipwell Data Share - Table Documentation

Core Shipment Tables

Table: dim_shipment_detail

This table contains details of the shipment.

Field Name	Field Description
dim_shipment_detail_key	Primary key
platform_uuid	Shipment's platform unique identifier
reference_id	Shipment's 6-digit unique identifier
company_id	Company ID of the tenant company running the report
created_at	Shipment's created date
status	Current shipment status, i.e., Delivered, In Transit
status_updated_at	Last date when the status was updated
is_archived	Is this shipment archived?
is_open	Is this shipment in the "Open" sub-status?
equipment_type	Shipment's equipment type. Example ie. Dry Van, Flatbed, etc.
mode	Shipment's mode. ie. FTL, LTL, Drayage, etc.
bol_number	Shipment's bill of lading number
customer_reference_number	Shipment's customer reference number
pickup_number	Shipment's pickup number; often assigned by the carrier
pro_number	Shipment's pro number
purchase_order_number	Shipment's purchase order number
name	Name of the shipment
custom_data	JSON object containing optional client manual entry data
cancellation_reason	Reason why a shipment was placed in cancelled status
created_by_user_id	User ID of the user who created the shipment
created_by_user_name	The name of the user who created the shipment
logistics_provider_assigned_at	Date the logistics provider was booked for the shipment
carrier_name	Name of the carrier on the shipment
carrier_reference_code	Carrier-specific reference code for the shipment
has_spot_negotiation	True if there is a spot negotiation for this shipment
logistics_provider_relationship_id	Unique ID of the tenant company's logistics provider
customer_relationship_id	Unique ID of the tenant company's customer
driver_name	Full name of the driver assigned to the shipment
load_board_id	A temporary id given to shipments on the load board prior to tendering
trailer_number	Unique identifier for the trailer used in this shipment
contract_id	Unique identifier for the contract used to book this shipment (if applicable)
contract_name	Human-readable name of the contract used to book this shipment (if applicable)
contract_status	Current status of the contract associated with this shipment
contract_applied_at	Timestamp when the contract was applied to this shipment
stage_id	Multi-stage shipment stage ID (for v3 multi-stage shipments)
multi_stage_shipment_id	v3 multi-stage shipment ID
multi_stage_reference_id	v3 multi-stage shipment reference ID
total_stages_in_shipment	Count of stages in multi-stage shipment
shipment_updated_at	Timestamp when the shipment was last updated
power_unit	Power unit/truck identifier
tracking_source	Source of tracking data (e.g., EDI, ELD, API)

Table: fact_shipment_summary

This table represents the essential elements of a shipment, and links to other tables (e.g., pickup stop, shipment details).

Field Name	Field Description
fact_shipment_summary_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
dim_shipment_documents_key	Foreign key to the dim_shipment_documents table
pickup_key	Key to the shipment's first pickup stop
delivery_key	Key to the shipment's last delivery stop
number_of_stops	Total number of stops belonging to this shipment
customer_amount_total	Total charges for the customer on this shipment
logistics_provider_amount_total	Total charges for the logistics provider on this shipment
total_weight	Total weight of the packages associated with the shipment
package_count	Total of package count across line items for this shipment
piece_count	Total piece count across line items for this shipment
total_weight_override	Manual override for the total weight of items in an order, typically used for shipping. If set, this value will be used instead of the automatically calculated weight.
total_weight_unit	Unit of measurement for the shipment's weight
total_density	total shipment weight divided by total shipment volume
total_distance	An approximate total of the distance a truck has to travel to complete this shipment
total_distance_unit	Unit of measurement for the shipment's distance
total_volume	The volume of a line item is measured in cubic ft
pickup_planned_date	Pickup planned date of the shipment's first pickup stop
delivery_planned_date	Delivery planned date of the shipment's last delivery stop
shipment_updated_at	Date the shipment was last updated
total_quantity_override	Manual override for the total package quantity on line items
total_declared_value	Total value declared on the commodity of a shipment
total_declared_value_currency	The currency used when declaring a shipment's commodity value
company_id	Company ID of the tenant company running the report
reference_id	Shipment's 6-digit unique identifier

Table: fact_shipment_financials

Financial charge line items for shipments.

Field Name	Field Description
fact_shipment_financials_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
charge_category	Financial charge line item category
charge_description	Financial charge line item description
charge_code	Financial charge line item code
currency_code	Financial charge line item currency code
customer_amount_total	Financial charge line item total to the customer
provider_amount_total	Financial charge line item total to the carrier
custom_data	JSON object containing optional client manual entry data
charge_type	a field to distinguish financial charge fields between Customer, Provider, and House financials
house_amount_total	Financial charge line item total to the house account or user tenant
dim_rep_role_key	Foreign key to the dim_rep_role table
reference_id	Shipment's 6-digit unique identifier
charge_lineitem_updated_at	Timestamp when the charge line item was last updated

Stop and Location Tables

Table: fact_stop

Each row represents a stop on a shipment.

Field Name	Field Description
fact_stop_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
dim_stoplocation_key	Foreign key to the dim_stoplocation table
planned_date	The current planned date for this stop
planned_window_start	The current planned window start time for this stop
planned_window_end	The current planned window end time for this stop
actual_arrival_at	Stop's arrival datetime
actual_departure_at	Stop's departure datetime
on_time	A flag indicating whether an event, activity, or delivery occurred as scheduled. True if on time or within an acceptable window; False otherwise.
status	Current stop status
is_pickup	A flag indicating whether the stop is categorized as a pickup. True if it's a pickup; False otherwise.
is_delivery	A flag indicating whether the stop is categorized as delivery. True if it's a delivery; False otherwise.
is_first	True if this stop is the first stop of the shipment
is_last	True if this stop is the last stop of the shipment
status_reason_code	Long description of the stop status.
timezone	Timezone used on this stop
initial_eta_window_start	The initial stop ETA window start time
initial_eta_window_end	The initial stop ETA window end time
sequence_number	Automatic incremental index to control the number of stops for a shipment.
custom_data	Free text entry done by the client.
reference_id	Shipment's 6-digit unique identifier
company_id	Company ID of the tenant company running the report
stop_id	Internal stop ID
stop_updated_at	Timestamp when the stop was last updated

Table: dim_stoplocation

Location details for stops.

Field Name	Field Description
dim_stoplocation_key	Primary key
dim_address_book_key	Foreign key to the dim_address_book table
company_id	Company ID of the tenant company running the report
location_name	Stop's location name
company_name	Stop's company name
address_line_1	Stop's Address line 1
address_line_2	Stop's Address line 2
city	Stop's City
postal_code	Stop's postal code
country	Stop's Country
city_state	Stop's City and State
state_province	Stop's state and province
latitude	Stop's latitude
longitude	Stop's longitude
address_book_entry_id	ID used to join the address book entry to stops

Table: dim_address_book

Address book entries that belong to a Shipwell tenant.

Field Name	Field Description
dim_address_book_key	Primary key
external_reference	External reference field on the address book page
company_id	Company ID of the tenant that this address book belongs to
location_name	Address book entry's location name
company_name	Address book entry's company name
open_monday	Is the facility open on Mondays?
open_tuesday	Is the facility open on Tuesdays?
open_wednesday	Is the facility open on Wednesdays?
open_thursday	Is the facility open on Thursday?
open_friday	Is the facility open on Friday?
open_saturday	Is the facility open on Saturday?
open_sunday	Is the facility open on Sunday?
open_weekdays	Is the facility open Monday through Friday?
open_weekends	Is the facility open on Saturday and Sunday?
weekday_open_hours	The hours this location is open on weekdays
weekend_open_hours	The hours this location is open on weekends
location_type	The type of location assigned to this address book entry
address	Address book location's address
city	Address book location's city
state_province	Address book location's state or province
postal_code	Address book location's postal code
country	Address book location's country
first_name	The first name of a point of contact associated with this address book location
last_name	The last name of a point of contact associated with this address book location
phone_number	The phone number belongs to a point of contact associated with this address book location
email	The email belongs to a point of contact associated with this address book location
dock_hours_start	The time this dock's open window begins
dock_hours_end	The time this dock's open window ends
notes	Notes associated with this address book entry
appointment_type	Type of appointment assigned to this address book entry
accessorials	Accessorials assigned to this address book entry
address_book_entry_id	Internal ID for the address book entry

Company and Relationship Tables

Table: dim_company

Represents companies in the Shipwell platform.

Field Name	Field Description
dim_company_key	Primary key
name	Company's name
primary_email	Company's primary email
primary_phone_number	Company's primary phone number
created_at	Date when this company was created on the platform
company_updated_at	Date when the company data was last updated in the source database

Table: dim_customer

Details about customers managed in the Shipwell platform.

Field Name	Field Description
dim_customer_key	Primary key
company_id	Company Id of the tenant company running the report
name	Customer's name
primary_email	Customer's primary email
primary_phone_number	Customer's primary phone number
credit_limit	The dollar limit we are allowed to credit for this customer
created_at	Date when this customer was created in the platform
customer_id	Customer's unique identifier. Use this to match customer IDs shown in the Shipwell platform

Table: dim_logistics_provider

Details about logistics providers.

Field Name	Field Description
dim_logistics_provider_key	Primary key
company_id	Company ID of the tenant company running the report
name	Logistics provider's name
email	Logistics provider's primary email
primary_phone_number	Logistics provider's primary phone number
status	Logistics provider's current status
status_last_updated	Date when the most recent status update happened for this logistics provider
dba_name	Doing business as the name for this logistics provider
created_at	Date when this logistics provider was created on the platform
identifying_code_type	The type of carrier identifying code
identifying_code_value	The value of the carrier identifying code field
scac	The carrier's Standard Carrier Alpha Code or SCAC
usdot	a unique identifier used when collecting and monitoring a carrier's safety information
mc_number	a unique identifier issued by the Federal Motor Carrier Safety Administration. Used to distinguish carriers' operations across state lines
duns	Data Universal Numbering System Number is a location-specific identifier used to communicate creditworthiness
nsc_number	National Stock Number or National Safety Code used when moving good across Canada
mx_number	The Federal Motor Carrier Safety Administration assigns mx_numbers to Mexico Domiciled carriers who want to operate within the United States
ff_number	Freight Forwarder Number is granted by Federal Motor Carrier Safety Administration to companies that operate interstate or foreign commerce

Table: dim_carrier

Details about carriers.

Field Name	Field Description
dim_carrier_key	Primary key
carrier_id	Internal carrier ID
company_id	Company ID of the tenant company running the report
name	Carrier's name
primary_email	Carrier's primary email
primary_phone_number	Carrier's primary phone number
status	Carrier's provider's current status
status_last_updated	Date when the most recent status update happened for this carrier
dba_name	Doing business as the name for this carrier
created_at	Date when this carrier was created in the platform
identifying_code_type	The type of carrier identifying code
identifying_code_value	The value of the carrier identifying code field
scac	The carrier's Standard Carrier Alpha Code or SCAC
usdot	a unique identifier used when collecting and monitoring a carrier's safety information
mc_number	a unique identifier issued by the Federal Motor Carrier Safety Administration. Used to distinguish carriers' operations across state lines
duns	Data Universal Numbering System Number is a location-specific identifier used to communicate creditworthiness
nsc_number	National Stock Number or National Safety Code used when moving goods across Canada
mx_numberThe	The Federal Motor Carrier Safety Administration assigns mx_numbers to Mexico-domiciled carriers who want to operate within the United States
ff_number	Freight Forwarder Number is granted by Federal Motor Carrier Safety Administration to companies that operate interstate or foreign commerce

Order and Line Item Tables

Table: dim_order_lineitem

Purchase Order line items linked to shipments. A row is only created if a Purchase Order is created in Shipwell and linked to a shipment.

Field Name	Field Description
dim_order_lineitem_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
company_id	Company ID of the tenant company running the report
lineitem_description	Description of the line item on the order
lineitem_ref	Reference number for a line item of an order
order_description	Description of the order
order_number	Order Number of the order
order_custom_data	JSON object containing optional client manual entry data
lineitem_custom_data	JSON object containing optional client manual entry data
total_packages	Total number of packages for the line item
total_pieces	Total number of pieces for the line item
customer_name	Customer name on the order
temp_unit	temperature unit associated with the temperature value on a line item
refrigeration_required	Does this line item require refrigeration?
destination_address_book_entry_id	key to join the destination address book
destination_address_id	key to join the destination address
destination_dock_external_id	key to join the destination dock
destination_stop_id	key to join the destination stop
order_id	key to join orders
invoice_external_id	key to join invoices
order_name	name of an order
origin_address_book_entry_id	key to join to origin address book
origin_address_id	key to join the origin address
origin_dock_external_id	key to join the origin dock
origin_stop_id	key to join the origin stop
planned_delivery_date	planned delivery date on the order
planned_delivery_end_datetime	Planned delivery date/time on the order
planned_delivery_start_datetime	Planned delivery date/time on the order
planned_pickup_date	planned pickup date on the order
planned_pickup_end_datetime	Planned pickup date/time on the order
planned_pickup_start_datetime	Planned pickup date/time on the order
purchase_order_number	The purchase order number belonging to an order
source	an order's source of creation
status	an order's transit status
lineitem_id	key to join an order line item
lineitem_created_at	The created date of an order line item
lineitem_deleted_at	The date an order line item was deleted
lineitem_freight_class	The freight class of an order line item that applies to LTL shipments
hazmat_hazard_class	The class of hazmat materials on the shipment
hazmat_identification_number	An ID associated with the type of hazmat material
hazmat_packing_group	The packing group associated with the hazmat material being hauled
hazmat_proper_shipping_name	The proper shipping name associated with hazardous materials
height	The height measurement of a package
length	The length measurement of a package
length_unit	The unit of measurement used to measure package dimensionality
nmfc_item_code	The NMFC code associated with a line item
nmfc_sub_code	The NMFC sub code associated with the line item
package_type	an order line item's type of packaging
package_weight	the weight of a lineitem package
package_weight_unit	The unit of measurement used to measure package weight
product_category	The category of product associated with a line item
refrigeration_max_temp	The maximum temperature limit of a refrigerated line item
refrigeration_min_temp	The minimum temperature limit of a refrigerated line item
stackable	Is a line item package stackable?
value_per_piece	The value of individual pieces on a line item package
lineitem_updated_at	the date a line item was updated
width	The width measurement of a package
total_weight	The sum of all the weights on an order
item_count	The count of line items on an order
released_onto_shipment_reference_id	The reference id an order was released onto
unassigned_at	The date an order was unassigned from a shipment
released_onto_shipment_id	the unique id of the shipment, an order was released onto
order_source	The source of an order creation
order_type	whether an order is a purchase order or a sales order
references	an order's reference fields
resource	The shipment of an order is assigned to
resource_id	The ID of a shipment or an order is assigned to
supplier_name	The name of the supplier associated with an order
order_stop_custom_data	custom data fields on order stops
order_total_weight	Total weight of the order calculated from order line items (gross_weight_value * quantity).
shipment_weight	The total weight of the shipment to which the order is assigned. Calculated from shipment line items (package_weight * quantity).
shipment_cost	Total logistics provider cost of the shipment the order is assigned to
order_allocated_cost	Allocated cost for this order based on weight proportion. Calculated as (order_total_weight / shipment_weight) * shipment_cost.
allocated_cost_per_weight	Cost per unit of weight for this order. Calculated as order_allocated_cost / order_total_weight.
is_weight_reconciled	Boolean flag indicating whether the sum of all order weights on the shipment matches the shipment weight within 2% tolerance.

Table: dim_shipment_lineitem

Line items on shipments.

Field Name	Field Description
dim_shipment_lineitem_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
lineitem_id	key to join a shipment line item
contains_hazmat	Does a line item contain hazmat cargo?
country_of_manufacture	The country where the product was produced or assembled
lineitem_created_at	The created date of a shipment line item
lineitem_custom_data	JSON object containing optional client manual entry data
lineitem_description	Description of the line items on a shipment
destination_stop_id	key to join the destination stop
lineitem_freight_class	The freight class of a shipment line item that applies to LTL shipments
hazmat_hazard_class	The class of hazmat materials on the shipment
hazmat_identification_number	An ID associated with the type of hazmat material
hazmat_packing_group	The packing group associated with the hazmat material being hauled
hazmat_proper_shipping_name	The proper shipping name associated with hazardous materials
height	The height measurement of a package
length	The length measurement of a package
length_unit	The unit of measurement used to measure package dimensionality
nmfc_item_code	The NMFC code associated with a line item
nmfc_sub_code	The NMFC sub code associated with the line item
origin_stop_id	key to join the origin stop
package_type	an order line item's type of packaging
package_weight	the weight of a lineitem package
piece_type	The type of packaging or container used for the shipment
lineitem_ref	Reference number for a line item on a shipment
provider_specific_packaging	Special packaging requirements or standards specified by the shipping provider
purchase_order_id	A unique identifier for the purchase order associated with the shipment
purchase_order_line_item_id	A unique identifier for a specific line item within a purchase order
quantity	The number of packages on a line item
refrigeration_required	Does this line item require refrigeration?
maximum_temperature	The maximum temperature limit of a refrigerated line item
minimum_temperature	The minimum temperature limit of a refrigerated line item
serial_number	A unique identifier assigned to a specific product or item for tracking purposes
shipment_id	A unique identifier for the entire shipment, used for tracking and reference
stackable	Is a line item package stackable?
temperature_unit	temperature unit associated with the temperature value on a line item
shipment_packages	The sum of all packages on a shipment
shipment_volume	The volume of a line item is measured in cubic ft
shipment_density	total shipment weight divided by total shipment volume
total_pieces	Total number of pieces for the line item
shipment_weight	The sum of all the weights on a shipment
shipment_item_count	The sum of all line items on a shipment
shipment_piece_count	The sum of all pieces on a shipment
lineitem_updated_at	the date a line item was updated
value_per_piece	The value of individual pieces on a line item package
value_per_piece_currency	The currency used to measure value per piece on a line item package
package_weight_unit	The unit of measurement used to measure package weight
width	The width measurement of a package

Table: dim_order_stopaddress

Address information for order stops.

Field Name	Field Description
stopaddress_id	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
address_1	Street address line 1
address_2	Street address line 2
city	city
country	country
created_at	when the entry was created
latitude	latitude
longitude	longitude
phone_number	phone number with country code
postal_code	postal_code
state_province	state or province
timezone	utc timezone
stopaddress_updated_at	last updated timestamp

Quote Tables

Table: fact_quote_summary

Main fact table for quotes.

Field Name	Field Description
fact_quote_summary_key	Primary key
dim_quote_detail_key	Foreign key to the dim_quote_detail table
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
dim_rfq_key	Key for the RFQ of a given quote
fact_spot_negotiation_key	Foreign key to the fact_spot_negotiation table
spot_negotiation_response_time_minutes	The time in minutes between when the spot negotiation was created and the time of the quote
amount_total	The sum of all line items for the quote
amount_rank	The ranking compared to all quotes for the given shipment, with 1 being the cheapest quote for the shipment
created_rank	The ranking compared to all quotes for the given shipment, with 1 being the first quote created for the shipment
created_at	Timestamp when the quote was created
quote_updated_at	Timestamp when the quote was last updated

Table: dim_quote_detail

Details of a quote.

Field Name	Field Description
dim_quote_detail_key	Primary key
accepted_at	The timestamp at which the quote was accepted
revoked_at	The timestamp at which the quote was revoked
expires_at	The timestamp at which the quote expires
delivery_date	The planned delivery date for the quote
transit_days	The expected transit days for the quote
lane_type	The type of lane, e.g., direct
reference_number	A reference number for the quote
service_level	The service level of the quote, e.g., STD
mode	Mode for the quote, e.g., LTL
equipment_type	The equipment type of the quote, e.g., DRY_VAN
capacity_provider_name	The company providing the quote, which may be a carrier or a 3PL
carrier_name	The carrier that would pick up the shipment
created_at	Timestamp when the quote was created
quote_updated_at	Timestamp when the quote was last updated

Table: fact_quote_financials

Line item financial data for quotes.

Field Name	Field Description
fact_quote_financials_key	Primary key
dim_quote_detail_key	Foreign key to the dim_quote_detail table
currency	Currency, typically USD
amount	Amount for the line item
charge_code	Centralized and structured charge code for the line item
description	Description of the line item
created_at	Timestamp when the quote line item was created
updated_at	Timestamp when the quote line item was last updated

Table: fact_spot_negotiation

Each row represents a spot negotiation between a customer and a logistics provider. A negotiation can have 0 to many quotes.

Field Name	Field Description
fact_spot_negotiation_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
dim_logistics_provider_key	Foreign key to the dim_logistics_provider table
dim_customer_key	Foreign key to the dim_customer table
is_most_recent_winner	A boolean flag for whether this logistics provider is the most recent winner on this shipment
created_at	Timestamp when the spot negotiation was created

Tender and Tag Tables

Table: fact_shipment_tender

Each row represents a tender associated with a shipment.

Field Name	Field Description
fact_shipment_tender_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
reference_id	Shipment's 6-digit unique identifier
tender_to_company_name	The company that is receiving the tender
tender_from_company_name	The company issuing the tender
expires_at	The time that the tender expires
accepted_at	If relevant, the time the tender was accepted
revoked_at	If relevant, the time the tender was revoked
rejected_at	If relevant, the time the tender was rejected
status	The status of the tender
created_at	Timestamp when the tender was created

Table: fact_shipment_tag

Each row represents a tag associated with a shipment.

Field Name	Field Description
fact_shipment_tag_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
name	The human-readable name of the tag
created_at	Timestamp when the tag was created

Invoice and Document Tables

Table: fact_invoice

Each row represents an invoice.

Field Name	Field Description
fact_invoice_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
reference_id	Shipment's reference ID
created_date	Shipment's Created Date
due_date	The due date for the invoice
invoice_number	The invoice number
total_amount	The total amount of the invoice
currency	The currency of the invoice amount
status	Status of the invoice
sub_status	Sub status of the invoice
exceptions	A semi-colon-separated list of exceptions
pod_count	The number of proof of delivery documents associated with this invoice
invoice_updated_at	date an invoice got updated
created_at	Timestamp when the invoice was created

Table: dim_shipment_documents

Document types associated with a shipment (i.e., BOL, DELIVERY_RECEIPT, or RATE_CONFIRMATION).

Field Name	Field Description
shipment_documents_key	Primary key
dim_shipment_documents_key	Foreign key to the fact_shipment_summary table
company_id	The company that created the shipment
updated_at	Last date/time the document was updated
document_type	Type of document attached to the shipment
has_document_type	Boolean expression for the presence of the document type
reference_id	Shipment's 6-digit unique identifier

Dock Appointment Table

Table: fact_dock_appointment

Each row represents a dock appointment for a given shipment stop.

Field Name	Field Description
fact_dock_appointment_key	Primary Key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
fact_stop_key	Foreign key to the fact_stop table
facility_name	The name of the facility for the appointment
dock_name	The name of the dock for the appointment
carrier_name	The name of the carrier on the shipment
status	The status of the appointment, e.g., SCHEDULED
level	Whether the appointment is at a dock or a facility level
appointment_type	The type of appointment, e.g., first come first serve
delivery_type	The type of delivery, e.g., receiving
checked_in_at	The actual check-in time
checked_out_at	The actual checkout time
scheduled_start_at	The scheduled appointment start time
scheduled_end_at	The scheduled appointment end time
rejected_at	The time of the appointment is being rejected
rejected_reasons	The reason for the appointment rejection, e.g., bad equipment
load_type	The type of load, e.g., live outbound
dwell_time_minutes	The number of minutes between check-in and check-out
checked_in_by_user	The user who checked in an appointment
checked_out_by_user	The user who checked out an appointment
created_at	Timestamp when the dock appointment was created

Container Details Table

Table: fact_shipment_container_details

Details of a container associated with a shipment (primarily for drayage shipments).

Field Name	Field Description
fact_shipment_container_detail_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
arrived_at_rail_facility	date arrived at rail facility
available_at_rail_facility	data available at the rail facility
booking_number	Drayage booking number
carrier_scac_code	Ocean carrier's SCAC code
chassis_number	Drayage chassis number
container_number	Ocean container number
container_return_date	If the date a container needs to be returned, additional charges are incurred
container_type	container_type
customs_hold_resolve_date	Customs hold resolve date
customs_hold_type	type of customs hold
customs_release_date	Date customs is released
demurrage_incurred	amount of demurrage incurred
demurrage_incurred_currency	currency of demurrage incurred
departed_on_rail	date departed on the rail
discharged	date discharged
empty_outgated	date empty outgated
empty_returned	date empty returned
estimated_arrival_date	The estimated date of when the container will arrive at the port of discharge
final_rail_terminal	final rail terminal
full_ingated	date full ingated
full_outgated	date full outgated
full_outgated_rail_facility	date full outgated at rail facility
house_bol_number	Receipt issued by a non-vessel operating common carrier to a shipper
id	record id
last_free_date	The last day of free storage time during which the container can be picked up without paying demurrage
lfd_rail_terminal	date lfd rail terminal
loaded	date loaded
loaded_on_rail	date loaded on the rail
origin_etd_date	date origin etd
pod_terminal	pod terminal
pod_terminal_address	address of the pod terminal
port_of_discharge	port of discharge
port_of_loading	port of loading
rail_carrier	rail carrier
release_date	The date the container is released
seal_number	The number that identifies a shipping container's seal that is attached to the container's locking mechanism after it's loaded
shipment_id	id of shipment
steamship_line	steamship name
transshipment_arrival	date of transshipment arrival
transshipment_departure	date of transshipment departure
unloaded_from_rail	date unloaded from rail
vessel_arrived	date vessel_arrived
vessel_berthed	date vessel berthed
vessel_departed	date vessel departed
vessel_imo	vessel imo
vessel_mmsi	vessel mmsi
vessel_name	vessel name
voyage_number	voyage number
container_detail_updated_at	container detail updated at
inland_port_rail	The inland port rail associated with this container
transshipment_port	is a port where cargo is transferred from one vessel to another
terminal_appointment_date	The date a container is expected to be at a terminal
eta_destination_port	estimated time of arrival at the destination port
eta_destination_rail	estimated time of arrival at the destination rail terminal

Rep Role Table

Table: dim_rep_role

Roles associated with a shipment (i.e., Carrier Rep, Sales Rep, or Account Rep).

Field Name	Field Description
rep_role_key	Primary key
dim_rep_role_key	Foreign key to the dim_shipment_detail table
shipment_rep_updated_at	Timestamp when the shipment rep assignment was last updated
rep_name	Full name of the person assigned to this role (first name and last name concatenated)
role_name	Name of the role (e.g., Carrier Rep, Sales Rep, Account Rep)
platform_uuid	Shipment's platform unique identifier (natural key for joining to dim_shipment_detail.platform_uuid)

Timeline and Notes Tables

Table: fact_shipment_timeline_event

Each row represents a timeline event.

Field Name	Field Description
fact_shipment_timeline_event_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
actor_id	id of the user who created the timeline event
created_date	Timeline event created date
custom_data	Custom data fields on timeline events
deleted_at	Timeline event deleted date
description	Timeline event description
event_type	Timeline event type
event_sub_type	Stop the event described on the timeline event
manually_created	Was the timeline event manually created?
occurred_at	The date of the event described in the timeline event
source	Source of timeline event creation
timeline_updated_at	Timeline event updated date
assignment_order	Assignment order of all timeline events
event_created_by_user	The user who created this timeline event
user_email	The email associated with the user account that created a timeline event

Table: dim_shipment_note

Internal note messages on a shipment.

Field Name	Field Description
shipment_note_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
company_id	The company that created the shipment
created_at	The date a shipment's internal note was created
creator_id	Unique identifier for the user who created an internal note
deleted_at	The date a shipment's internal note was deleted
id	record id
is_pinned	Boolean describing if an internal is a pinned message or not
message	The text that makes up an internal note
note_creator_name	The name of the user who created an internal note
shipment_id	record id for the shipment associated with internal notes
updated_at	The date an internal note is updated

Bill To Table

Table: dim_shipment_bill_to

Shipment bill to information.

Field Name	Field Description
shipment_bill_to_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
billing_id	The shipment's bill to billing id
company_address	The bill to company name
contact_email	The bill to company contact email
contact_phone	The bill to company contact phone number
created_at	The date the shipment bill to item was created
direction	The billing direction
updated_at	The date the shipment bill to item was updated
company_name	Name of the bill-to company

Group Tables

Table: fact_shipment_group

Each row represents a shipment-to-group association at the shipment metadata level. This table uses shipment-level groups, which are only available in PROD. In the sandbox environment, this table returns empty results.

Field Name	Field Description
fact_shipment_group_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
group_id	The internal group ID
group_external_id	The external identifier for the group
group_name	The human-readable name of the group
group_description	A description of the group's purpose or contents
parent_group_id	The ID of the parent group if this is a nested group
group_status	The current status of the group
created_at	When the shipment-group association was created
group_updated_at	The latest update time between the shipment metadata and the group

Table: fact_shipment_group_summary

Each row represents a shipment with its associated groups aggregated into summary fields. This is a rollup of fact_shipment_group, providing comma-separated lists of group names and IDs per shipment. Only populated in PROD (returns empty in sandbox).

Field Name	Field Description
dim_shipment_detail_key	Primary key and foreign key to the dim_shipment_detail table
group_names	Comma-separated list of all group names associated with this shipment
group_external_ids	Comma-separated list of all external group IDs associated with this shipment
group_count	The total number of distinct groups associated with this shipment
group_updated_at	The most recent update time across all groups for this shipment

Table: fact_shipment_order_group

Each row represents a shipment-to-group association via purchase orders.

Field Name	Field Description
fact_shipment_order_group_key	Primary key
dim_shipment_detail_key	Foreign key to the dim_shipment_detail table
shipment_id	The shipment ID
product_order_id	The product order ID that links the shipment to the group
product_order_number	The human-readable product order number
group_id	The internal group ID from product_order_group
group_external_id	The external identifier for the group
group_name	The human-readable name of the group
group_description	A description of the group's purpose or contents
parent_group_id	The ID of the parent group if this is a nested group
tenant_id	The tenant ID that owns this group
group_updated_at	The latest update time among shipments, product orders, and the group

Contract Table

Table: dim_contract

Consolidated contract dimension supporting both legacy and lane-based contract patterns. Each row represents a contract lane configuration.

Field Name	Field Description
dim_contract_key	Primary key - surrogate key based on contract, lane, rate table, rate band, and origin/destination
contract_id	The internal contract ID
contract_name	Human-readable name of the contract
status	Current status of the contract (e.g., ACTIVE, EXPIRED)
origin_city	City of the origin location for this contract lane
origin_state_province	State or province of the origin location
origin_postal_code	Postal code of the origin location
destination_city	City of the destination location for this contract lane
destination_state_province	State or province of the destination location
destination_postal_code	Postal code of the destination location
modes	Transportation modes covered by this contract (e.g., FTL, LTL)
equipment_types	Equipment types covered by this contract (e.g., DRY_VAN, FLATBED)
minimum_rate	Minimum rate for this contract or rate table
band_calculation_measurement	Measurement type used for rate band calculations (e.g., WEIGHT, DISTANCE)
start_datetime	Contract effective start date
end_datetime	Contract expiration date
contract_rate	Base rate is defined at the contract level
rate_table_rate	Rate defined in the rate table (for lane-based contracts)
contract_rate_type	Type of rate at contract level (e.g., FLAT, PER_MILE, RATE_TABLE)
rate_table_rate_type	Type of rate in the rate table
rate_application_type	How the rate band is applied
distance_miles	Distance in miles for this contract lane
carrier_relationship_id	Foreign key to the carrier vendor relationship
carrier_name	Name of the carrier associated with this contract
company_id	Company ID of the tenant that owns this contract
company_name	Name of the company that owns this contract
fuel_included	Whether the fuel surcharge is included in the contract rate
additional_stop_charge_flat_fee	Flat fee charged for additional stops
created_at	When the contract was created
updated_at	When the contract was last updated
lane_id	Foreign key to the contract lane (for lane-based contracts)
rate_table_id	Foreign key to the rate table (for lane-based contracts)
rate_band_id	Foreign key to the rate band (for lane-based contracts)

Key Relationships and How to Join Tables

To help you join tables together, here are the most common join patterns:

Shipment-centered queries

Start with dim_shipment_detail (primary shipment information)
Join to fact_shipment_summary using dim_shipment_detail_key (get aggregated metrics)
Join to fact_shipment_financials using dim_shipment_detail_key (get line item charges)
Join the fact_stop using dim_shipment_detail_key (get stop details)
Join to dim_order_lineitem using dim_shipment_detail_key (get purchase order line items)
Join to dim_shipment_lineitem using dim_shipment_detail_key (get shipment line items)

Location information

Join fact_stop to dim_stoplocation using dim_stoplocation_key (get location details)
Join dim_stoplocation to dim_address_book using dim_address_book_key (get address book information)

Company and relationship information

Use company_id to filter data for your tenant
Use customer_relationship_id from dim_shipment_detail to join to dim_customer
Use logistics_provider_relationship_id from dim_shipment_detail to join to dim_logistics_provider
Use carrier_name to match carrier information (note: this is a text field, not a key)

Quote information

Start with fact_quote_summary
Join to dim_quote_detail using dim_quote_detail_key
Join to fact_quote_financials using dim_quote_detail_key (get quote line items)
Join to dim_shipment_detail using dim_shipment_detail_key (get shipment information)
Join fact_spot_negotiation using fact_spot_negotiation_key

Container tracking

Join fact_shipment_container_details to dim_shipment_detail using dim_shipment_detail_key

Appointments

Join fact_dock_appointment to dim_shipment_detail using dim_shipment_detail_key
Join fact_dock_appointment to fact_stop using fact_stop_key

Contracts

Join dim_contract to dim_shipment_detail using contract_id

Rep roles

Join dim_rep_role to dim_shipment_detail using:
- Via fact_shipment_financial table: dim_shipment_detail → fact_shipment_financials → dim_rep_role (join on dim_rep_role_key)
- Direct join: dim_shipment_detail.platform_uuid = dim_rep_role.platform_uuid

Example SQL queries

Example query for a summary list of shipments.

The output of this query:

  SELECT  dim_shipment_detail."STATUS"  AS "dim_shipment_detail.status",

    dim_shipment_detail."REFERENCE_ID"  AS "dim_shipment_detail.reference_id",

    dim_shipment_detail."CREATED_AT",

    fact_shipment_summary."PICKUP_PLANNED_DATE",

    fact_shipment_summary."DELIVERY_PLANNED_DATE",

    dim_shipment_detail."MODE"  AS "dim_shipment_detail.mode",

    dim_logistics_provider."NAME"  AS "dim_logistics_provider.name",

    (CAST((dim_stoplocation_pickup."CITY_STATE") AS VARCHAR) || CAST(' - ' AS VARCHAR) || CAST((dim_stoplocation_delivery."CITY_STATE") AS VARCHAR)) AS lane,

    COALESCE(SUM(( fact_shipment_summary."LOGISTICS_PROVIDER_AMOUNT_TOTAL"  ) ), 0) AS "fact_shipment_summary.total_logistic_provider_amount"

FROM "SHIPWELL_DATA_MODEL"."FACT_SHIPMENT_SUMMARY"  AS fact_shipment_summary

LEFT JOIN "SHIPWELL_DATA_MODEL"."DIM_SHIPMENT_DETAIL"  AS dim_shipment_detail ON (fact_shipment_summary."DIM_SHIPMENT_DETAIL_KEY") = (dim_shipment_detail."DIM_SHIPMENT_DETAIL_KEY")

LEFT JOIN "SHIPWELL_DATA_MODEL"."DIM_LOGISTICS_PROVIDER"  AS dim_logistics_provider ON (dim_shipment_detail."LOGISTICS_PROVIDER_RELATIONSHIP_ID") = (dim_logistics_provider."DIM_LOGISTICS_PROVIDER_KEY")

LEFT JOIN "SHIPWELL_DATA_MODEL"."DIM_STOPLOCATION"  AS dim_stoplocation_delivery ON (fact_shipment_summary."DELIVERY_KEY") = (dim_stoplocation_delivery."DIM_STOPLOCATION_KEY")

LEFT JOIN "SHIPWELL_DATA_MODEL"."DIM_STOPLOCATION"  AS dim_stoplocation_pickup ON (fact_shipment_summary."PICKUP_KEY") = (dim_stoplocation_pickup."DIM_STOPLOCATION_KEY")

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8

ORDER BY 3 DESC

Example query to see carrier performance

SELECT

    dim_logistics_provider."NAME"  AS "dim_logistics_provider.name",

    COUNT(*) AS "dim_shipment_detail.count",

    COUNT(CASE WHEN (fact_stop."ON_TIME") AND fact_stop.IS_PICKUP THEN 1 ELSE NULL END) AS pickup_on_time_count,

    COUNT(CASE WHEN (fact_stop."ON_TIME") AND fact_stop.IS_DELIVERY THEN 1 ELSE NULL END) AS delivery_on_time_count,

    1.0 * ( COUNT(CASE WHEN fact_stop."ON_TIME"  THEN fact_stop."FACT_STOP_KEY"  ELSE NULL END) )/NULLIF(( COUNT(*) ),0)  AS pickup_on_time,

    1.0 * ( COUNT(CASE WHEN fact_stop."ON_TIME"  THEN fact_stop."FACT_STOP_KEY"  ELSE NULL END) )/NULLIF(( COUNT(*) ),0)  AS delivery_on_time

FROM "SHIPWELL_DATA_MODEL"."FACT_STOP"  AS fact_stop

LEFT JOIN "SHIPWELL_DATA_MODEL"."DIM_SHIPMENT_DETAIL"  AS dim_shipment_detail ON (fact_stop."DIM_SHIPMENT_DETAIL_KEY") = (dim_shipment_detail."DIM_SHIPMENT_DETAIL_KEY")

LEFT JOIN "SHIPWELL_DATA_MODEL"."DIM_LOGISTICS_PROVIDER"  AS dim_logistics_provider ON (dim_shipment_detail."LOGISTICS_PROVIDER_RELATIONSHIP_ID") = (dim_logistics_provider."DIM_LOGISTICS_PROVIDER_KEY")

GROUP BY 1

ORDER BY 4 DESC

Using custom fields

For Report Builder users. The process to get a custom data field into the data model is to start a feature request. The feature request should consist of the exact name of the custom field and the name of the customer tenant.

Example:
Shipwell wants to get a custom reference field called "Example field" to the data model.

Once the request is received, we will add the custom field in the back end, and the field will be available in the following views for builder users.

For Data Share users, you can access these custom fields from the Custom_data field in the various tables. The custom reference fields for example can be found in the dim_shipment_detail.custom_data field. The data is in a JSON package and will need to be extracted. We have the following types of custom fields and the associated tables to find them.
Custom Field Types:

Reference Fields

Shipments
- DIM_SHIPMENT_DETAIL.CUSTOM_DATA
Orders
- DIM_ORDER_LINEITEM.ORDER_CUSTOM_DATA

Customer Fields > Not currently supported in data share,

Line Item Fields

Shipments
- DIM_SHIPMENT_LINEITEM.LINEITEM_CUSTOM_DATA
Orders
- DIM_ORDER_LINEITEM.LINEITEM_CUSTOM_DATA

Charge Line Item Fields

Shipments
- FACT_SHIPMENT_FINANCIALS.CUSTOM_DATA

Stop Fields

Shipments
- FACT_STOP.CUSTOM_DATA

Example JSON extraction queries for various Custom Fields:
SELECT

CUSTOM_DATA:shipwell_custom_data:shipment:"5834da21-5056-4479-a202-73f3ddwg5dd4"::VARCHAR AS LTL_TYPE

FROM

SHIPWELL_DATA_MODEL.DIM_SHIPMENT_DETAIL

---------------------

SELECT

ORDER_CUSTOM_DATA:shipwell_custom_data:purchase_order:"12re1add-33dd-4c91-bb35-6312sda2b6dd"::VARCHAR AS PARTIAL

FROM

SHIPWELL_DATA_MODEL.DIM_ORDER_LINEITEM

---------------------

SELECT

LINEITEM_CUSTOM_DATA:shipwell_custom_data:"2a1f590c-5418-d5d6-8cad-f9ae368432a0"::VARCHAR AS TANKER_ENDORSEMENT

FROM

SHIPWELL_DATA_MODEL.DIM_SHIPMENT_LINEITEM

---------------------

SELECT

CUSTOM_DATA:"custom_charge_line_field_name"::VARCHAR AS CUSTOMER_MARK_UP_TYPE

FROM

SHIPWELL_DATA_MODEL.FACT_SHIPMENT_FINANCIALS

WHERE

CHARGE_TYPE = 'Provider'

Shipwell Data Shared

Overview

Shipwell Data Share - Table Documentation

Core Shipment Tables

Table: dim_shipment_detail

Table: fact_shipment_summary

Table: fact_shipment_financials

Stop and Location Tables

Table: fact_stop

Table: dim_stoplocation

Table: dim_address_book

Company and Relationship Tables

Table: dim_company

Table: dim_customer

Table: dim_logistics_provider

Table: dim_carrier

Order and Line Item Tables

Table: dim_order_lineitem

Table: dim_shipment_lineitem

Table: dim_order_stopaddress

Quote Tables

Table: fact_quote_summary

Table: dim_quote_detail

Table: fact_quote_financials

Table: fact_spot_negotiation

Tender and Tag Tables

Table: fact_shipment_tender

Table: fact_shipment_tag

Invoice and Document Tables

Table: fact_invoice

Table: dim_shipment_documents

Dock Appointment Table

Table: fact_dock_appointment

Container Details Table

Table: fact_shipment_container_details

Rep Role Table

Table: dim_rep_role

Timeline and Notes Tables

Table: fact_shipment_timeline_event

Table: dim_shipment_note

Bill To Table

Table: dim_shipment_bill_to

Group Tables

Table: fact_shipment_group

Table: fact_shipment_group_summary

Table: fact_shipment_order_group

Contract Table

Table: dim_contract

Key Relationships and How to Join Tables

Shipment-centered queries

Location information

Company and relationship information

Quote information

Container tracking

Appointments

Contracts

Rep roles

Example SQL queries

Example query for a summary list of shipments.

Example query to see carrier performance

Using custom fields