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
house_amount_total	Financial charge line item total to the house account or user tenant
charge_type	A field to distinguish financial charge fields between Customer, Provider, and House financials

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
load_board_id	A temporary id given to shipments on the load board prior to tendering

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_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	The customer name on the order
temp_unit	temperature unit associated with temperature value on a line item
refrigeration_required	Does this line item require refrigeration?
destination_address_book_entry_id	key to join to destination address book
destination_address_id	key to join on the destination address
destination_dock_external_id	key to join on the destination dock
destination_stop_id	key to join on the destination stop
order_id	key to join on orders
invoice_external_id	key to join on invoices
order_name	name of an order
origin_address_book_entry_id	key to join the origin address book
origin_address_id	key to join on the origin address
origin_dock_external_id	key to join on the origin dock
origin_stop_id	key to join on 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 on 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 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 subcode 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 weight on an order
item_count	the count of line items on an order
released_onto_shipment_reference_id	the reference id is an order that 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 an order is assigned to
resource_id	the id of a shipment an order is assigned to
updated_at	the date an order was updated
supplier_name	the name of the supplier associated with an order
order_stop_custom_data	custom data fields on order stops

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 for the fact_shipment_tender table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
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 for the fact_shipment_tag table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
name	The human-readable name of the tag

Table: fact_invoice

Each row represents an invoice.

Name	Description
fact_invoice_key	Primary key for the fact_invoice table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
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
fact_shipment_container_detail_key	Primary key for the fact_shipment_container_detail_key table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
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	Primary key for the dim_rep_role table
dim_rep_role_key	Foreign key referencing the fact_shipment_financials table
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	Primary key for the dim_shipment_documents table
dim_shipment_documents_key	Foreign key referencing the fact_shipment_summary table
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 the dim_customer table
company_id	ID of the tenant company that the customer belongs to
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 for the fact_dock_appointment_key table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
fact_stop_key	Foreign key referencing 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 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 for the fact_shipment_timeline_event table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
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
event_created_by_user	The user that created this timeline event
user_email	The email associated with the user account who created a timeline event

Table: dim_address_book

Address book entries that belong to a Shipwell tenant.

Name	Description
dim_address_book_key	Primary key for the dim_address_book table
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

Table: dim_shipment_note

Internal note entries from the shipment details page.

Name	Description
shipment_note_key	Primary key for the dim_shipment_note table
dim_shipment_detail_key	Foreign key referencing the dim_shipment_detail table
company_id	The company that created the shipment
shipment_note_created_at	The date a shipment internal note was created
creator_id	Unique identifier for the user that created an internal note
shipment_note_deleted_at	The date a shipment internal note was deleted
id	record id
is_pinned	Boolean describes 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
shipment_note_updated_at	The date an internal note is updated

Table: dim_shipment_bill_to

Shipment bill to information

Name	Description
shipment_bill_to_key	Primary key
dim_shipment_detail_key	Foreign key
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

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

Adding custom fields to the data model

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.

Overview

Diagram

Tables

Table: fact_shipment_summary

Table: fact_shipment_financials

Table: fact_stop

Table: dim_shipment_detail

Table: dim_customer

Table: dim_logistics_provider

Table: dim_stoplocation

Table: dim_order_lineitem

Table: fact_quote_summary

Table: dim_quote_detail

Table: fact_quote_financials

Table: fact_spot_negotiation (not released yet)

Table: fact_shipment_tender

Table: fact_shipment_tag

Table: fact_invoice

Table: fact_shipment_container_details

Table: dim_rep_role

Table: dim_shipment_documents

Table: dim_customer

Table: fact_dock_appointment

Table: fact_shipment_timeline_event

Table: dim_address_book

Table: dim_shipment_note

Table: dim_shipment_bill_to

Example SQL queries

Example query for a summary list of shipments.

Example query to see carrier performance

Adding custom fields to the data model

Past schema changes

Coming soon