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.

Note that we are still improving the documentation. We will also publish more schemas in the first half of 2024.

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

Diagram

Tables

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 for the Fact_shipment_summary table
dim_shipment_detail_key	Foreign key to the dim_shipments_detail 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	Unit of measurement for the shipment's weight
package_count	Total of package count across line items for this shipment
total_weight_override	Manual override for the total weight of items in an order
total_weight_unit	Unit of measurement for the shipment's weight
total_distance	An approximate total of all the distance a truck has to travel
total_distance_unit	Unit of measurement for the shipment's distance
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 quantity of packages on items in an order

Table: fact_shipment_financials

Field Name	Field Description
fact_shipment_financials_key	Primary key for the fact_shipment_financials table
dim_shipment_detail_key	Foreign key to the dim_shipments_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

Table: fact_stop

Field Name	Field Description
fact_stop_key	Primary key for the Fact_Stop table
dim_shipment_detail_key	Foreign key joining dim_shipment_detail dimension
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
status	Current stop status
is_pickup	A flag indicating whether the stop is categorized as pickup
is_delivery	A flag indicating whether the stop is categorized as delivery
is_first	Is this stop the first of the shipment?
is_last	Is this stop the last of the shipment?
status_reason_code	Long description of the stop status
timezone	Timezone used on this stop
initial_eta_window_start	The initial ETA for the stop start
initial_eta_window_end	The initial ETA for the stop end
sequence_number	Automatic incremental index to control the number of stop for a shipment
custom_data	Free text entry done by the client

Table: dim_shipment_detail

This table contains details of the shipment.

Field Name	Field Description
dim_shipment_detail_key	Primary key of the dim_shipment_detail table
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, e.g., Dry Van, Flatbed
mode	Shipment's mode, e.g., FTL, LTL, Drayage
bol_number	Shipment's bill of lading number
customer_reference_number	Shipment's customer reference number
pro_number	Shipment's pro number
name	Name of the shipment
custom_data	JSON object containing optional client manual entry data
cancellation_reason	Reason for 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
driver_name	The driver on the shipment
tracking_source	The source of tracking updates, or null if the shipment is not receiving tracking updates
carrier_name	Name of the carrier on the shipment
has_spot_negotiation	True if the shipment has a spot negotiation
logistics_provider_assigned_at	Date the logistics provider was booked on the 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

Table: dim_customer

This table represents customized customers managed in the Shipwell platform, as per here.

Field Name	Field Description
dim_customer_key	Primary key for dim_customer table
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	Dollar limit we are allowed to credit for this customer
created_at	Date when this customer was created in the platform

Table: dim_logistics_provider

Field Name	Field Description
dim_logistics_provider_key	Primary key for the dim_logistics_provider table
company_id	Company ID of the tenant company running the report
name	Logistics provider's name
primary_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
dba_name	Doing business as name for this logistics provider
created_at	Date when this logistics provider was created in the platform

Table: dim_stoplocation

Field Name	Field Description
dim_stoplocation_key	Primary key of the dim_stoplocation 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

Table: dim_order_lineitem

This table is for Purchase Order data entered into Shipwell, as per here. A row is only created if a Purchase Order is created in Shipwell and linked to a shipment.

Field Name	Field Description
dim_shipment_detail_key	Primary key of the dim_shipment_detail table
company_id	Company ID of the tenant company running the report
lineitem_id	ID for the line item
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
planned_delivery_start_datetime	Planned delivery date/time on the order
created_at

Table: fact_quote_summary

Name	Description
fact_quote_summary_key	Primary key for the fact_quote_summary table
dim_quote_detail_key	Foreign key referencing the dim_quote_detail table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
dim_rfq_key	Key of the RFQ (currently, no separate RFQ table)
fact_spot_negotiation_key	Foreign key referencing the fact_spot_negotiation table
created_at	Timestamp indicating when the record was created
updated_at	Timestamp indicating when the record was last updated

Table: dim_quote_detail

Name	Description
dim_quote_detail_key	Primary key for the table
created_at	Timestamp indicating when the quote was created
quote_updated_at	Timestamp indicating the last update to the quote
accepted_at	Timestamp indicating when the quote was accepted
revoked_at	Timestamp indicating when the quote was revoked
expires_at	Timestamp indicating the expiration date of the quote
delivery_date	Date when the delivery is scheduled to occur
transit_days	Number of days the transit is expected to take
lane_type	Type of transportation lane (e.g., direct)
reference_number	Reference # to identify the quote, originates from the capacity provider
service_level	Level of service requested for the shipment (e.g., STD for standard)
mode	Mode of transportation (e.g., LTL, FTL)
equipment_type	Type of equipment requested for transportation (e.g., dry_van, drop_trailer_
capacity_provider_name	Name of the capacity provider offering the quote
carrier_name	Name of the carrier for the quote
_loaded_datetime	Timestamp indicating when the record was loaded into the data warehouse

Table: fact_quote_financials

Name	Description
unit_name	Describes the charge, e.g., Fuel
unit_quantity	The amount of the given charge, usually 1
unit_amount_currency	Currency of the charge
unit_amount	The amount for 1 unit of the charge (should multiply unit_amount * unit_quantity)
charge_code	Formatted code for the charge, e.g., FSC for Fuel Surcharge
created_at	Timestamp indicating when the record was created
updated_at	Timestamp indicating when the record was last updated

Table: fact_spot_negotiation (not released yet)

Name	Description
fact_spot_negotiation_key	Primary key for the fact_spot_negotiation table
dim_shipment_detail_key	The associated shipment for this spot negotiation
dim_logistics_provider_key	The logistics provider that is providing quotes
dim_customer_key	The customer that is requesting quotes
is_most_recent_winner	A boolean flag for whether this logistics provider is the most recent winner on this shipment

Table: fact_shipment_tender

Each row represents a tender associated to a shipment.

Name	Description
fact_shipment_tender_key	Primary key
dim_shipment_detail_key	The associated shipment
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

Table: fact_shipment_tag

Each row represents a tag associated to a shipment.

Name	Description
fact_shipment_tag_key	Primary key
dim_shipment_detail_key	The associated shipment for this tag
name	The human-readable name of the tag

Table: fact_invoice

Each row represents an invoice.

Name	Description
fact_invoice_key	Primary key
dim_shipment_detail_key	The associated shipment for this invoice
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
exceptions	A semi-colon separated list of exceptions
pod_count	The number of proof of delivery documents associated with this invoice

Table: fact_shipment_container_details

Each row represents a container

Name	Description
dim_shipment_detail_key	The associated shipment for this container
fact_shipment_container_detail_key	Primary key
arrived_at_rail_facility	date arrived at rail facility
available_at_rail_facility	data available at 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	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 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 arrival 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
last_free_date	The last day of free storage time in 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 rail
origin_etd_date	date origin etd
pod_terminal	pod terminal
pod_terminal_address	address of 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	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

Table: dim_rep_role

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

Name	Description
rep_role_key	Unique key
dim_rep_role_key	Foreign key
carrier_rep	Full name of the person who assigned the carrier to the shipment
sales_rep	Full name of the person who is assigned as the Sales Rep for the customer on the shipment
account_rep	Full name of the person who is assigned as the Account Rep for the customer on the shipment

Table: dim_shipment_documents

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

Name	Description
shipment_documents_key	Unique key
dim_shipment_documents_key	Foreign key
company_id	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

Table: dim_customer

Represents a dimension table detailing information about the various logistics or shipping providers involved in the supply chain or delivery process.

Name	Description
dim_customer_key	Primary key for dim_customer table
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	Dollar limit we are allowed to credit for this customer
created_at	Date when this customer was created in the platform

Table: fact_dock_appointment

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

Name	Description
fact_dock_appointment_key	Primary key
dim_shipment_detail_key	The associated shipment
fact_stop_key	The associated stop
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 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 check-out time
scheduled_start_at	The scheduled appointment start time
scheduled_end_at	The scheduled appointment end time
rejected_at	The time of the appointment was rejected
rejected_reasons	The reason for 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

Table: fact_shipment_timeline_event

Each row represents a timeline event.

Name	Description
fact_shipment_timeline_event_key	Primary key
dim_shipment_detail_key	The associated shipment for this timeline event
actor_id	id of 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 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

Table: dim_address_book

Address book entries that belong to a Shipwell tenant.

Name	Description
dim_address_book_key	Unique 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 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 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 belonging to a point of contact associated with this address book location
email	The email belonging 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

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

Past schema changes

Schema changes released on Mar 7

New tables:

Fact_invoice
Fact_shipment_tender
Fact_shipment_tag
Fact_quote_summary
dim_quote_detail

Schema changes released on Tuesday, Feb 27

All tables:

_datashare_last_synced_at: the time of the datashare sync, to help identify an upstream error if it happens

Dim_shipment_detail: Add the following

Tracking_source: The source of tracking updates
Carrier_name: Name of the carrier on the shipment
Has_spot_negotiation: true if the shipment has a spot negotiation
Created_by_user_name
Driver_name

Dim_order_lineitem: Add the following

Created_at: When the record was created
_loaded_datetime: This is the time that the data was refreshed against the underlying databases

Schema changes on Thursday, Jan 25

We planned the following changes, which should make the data models more intuitive and remove some duplication.

After the changes, the table documentation will be updated.

Fact_stop

Rename initial_window_start to initial_eta_window_start (to avoid confusion with planned_window_start)
Rename initial_window_end to initial_eta_window_end

Dim_stoplocation

Add timezone
Remove dim_shipment_detail_key (this can be joined via fact_stop)

Dim_shipment_detail: Remove the following duplicate fields which are already present in fact_shipment_summary

Planned_date
Planned_window_start
Package_count
Total_weight
Total_weight_override
Total_weight_unit
Total_distance
Total_distance_unit

Coming soon

We are working on a simplified data model for quoting, so you can analyze bids and spot market performance (ETA end of Feb 2024)
Dock scheduling