Skip to content

Instantly share code, notes, and snippets.

@dodeja
Last active August 16, 2023 21:11
Show Gist options
  • Save dodeja/cb890eb21709837b411c9d3eb6419617 to your computer and use it in GitHub Desktop.
Save dodeja/cb890eb21709837b411c9d3eb6419617 to your computer and use it in GitHub Desktop.
Terminal49 Data Sync Schema

Terminal49 Data Sync Table Schema

These files define the models (schema) that are used to synchronize the Terminal49 data with customer's datawarehouse.

More details on https://www.terminal49.com/data-sync/

{
"model_name": "containers",
"columns": [
{
"name_in_source": "container_id",
"name_in_destination": "container_id",
"data_type": "text",
"is_primary_key": true,
"description": "Container ID. This is the unique key of the table."
},
{
"name_in_source": "container_number",
"name_in_destination": "container_number",
"data_type": "text",
"description": "Container number"
},
{
"name_in_source": "shipment_id",
"name_in_destination": "shipment_id",
"data_type": "text",
"description": "Shipment ID associated to the container"
},
{
"name_in_source": "shipment_bill_of_lading_number",
"name_in_destination": "shipment_bill_of_lading_number",
"data_type": "text",
"description": "Shipment number from the tracking request"
},
{
"name_in_source": "shipment_normalized_number",
"name_in_destination": "shipment_normalized_number",
"data_type": "text",
"description": "The normalized version of the shipment number used for querying the carrier"
},
{
"name_in_source": "shipment_reference_numbers",
"name_in_destination": "shipment_reference_numbers",
"data_type": "text",
"description": "Reference numbers of the shipment, concatenated"
},
{
"name_in_source": "container_reference_numbers",
"name_in_destination": "container_reference_numbers",
"data_type": "text",
"description": "Reference numbers of the container, concatenated"
},
{
"name_in_source": "shipment_tags",
"name_in_destination": "shipment_tags",
"data_type": "text",
"description": "Tags added to the shipment, sorted by alphabetical order, concatenated and separated by a comma"
},
{
"name_in_source": "customer_id",
"name_in_destination": "customer_id",
"data_type": "text",
"description": "Account ID of the customer"
},
{
"name_in_source": "customer_name",
"name_in_destination": "customer_name",
"data_type": "text",
"description": "Name of the customer"
},
{
"name_in_source": "shipping_line_scac",
"name_in_destination": "shipping_line_scac",
"data_type": "text",
"description": "Standard carrier alpha numeric code of the shipping line"
},
{
"name_in_source": "shipping_line_name",
"name_in_destination": "shipping_line_name",
"data_type": "text",
"description": "Name of the shipping line"
},
{
"name_in_source": "pol_country_code",
"name_in_destination": "pol_country_code",
"data_type": "text",
"description": "Port of Lading country code"
},
{
"name_in_source": "pol_locode",
"name_in_destination": "pol_locode",
"data_type": "text",
"description": "Port of Lading UN/LOCODE"
},
{
"name_in_source": "pol_city",
"name_in_destination": "pol_city",
"data_type": "text",
"description": "Port of Lading city"
},
{
"name_in_source": "pol_timezone",
"name_in_destination": "pol_timezone",
"data_type": "text",
"description": "Port of Lading time zone"
},
{
"name_in_source": "pod_country_code",
"name_in_destination": "pod_country_code",
"data_type": "text",
"description": "Port of Discharge country code"
},
{
"name_in_source": "pod_locode",
"name_in_destination": "pod_locode",
"data_type": "text",
"description": "Port of Discharge UN/LOCODE"
},
{
"name_in_source": "pod_city",
"name_in_destination": "pod_city",
"data_type": "text",
"description": "Port of Discharge city"
},
{
"name_in_source": "pod_timezone",
"name_in_destination": "pod_timezone",
"data_type": "text",
"description": "Port of Discharge time zone"
},
{
"name_in_source": "pod_terminal_firms_code",
"name_in_destination": "pod_terminal_firms_code",
"data_type": "text",
"description": "Port of Discharge terminal firms code"
},
{
"name_in_source": "pod_terminal_nickname",
"name_in_destination": "pod_terminal_nickname",
"data_type": "text",
"description": "Port of Discharge terminal nickname"
},
{
"name_in_source": "pod_terminal_name",
"name_in_destination": "pod_terminal_name",
"data_type": "text",
"description": "Port of Discharge terminal name"
},
{
"name_in_source": "destination_country_code",
"name_in_destination": "destination_country_code",
"data_type": "text",
"description": "Destination country code"
},
{
"name_in_source": "destination_locode",
"name_in_destination": "destination_locode",
"data_type": "text",
"description": "Destination UN/LOCODE"
},
{
"name_in_source": "destination_city",
"name_in_destination": "destination_city",
"data_type": "text",
"description": "Destination city"
},
{
"name_in_source": "destination_timezone",
"name_in_destination": "destination_timezone",
"data_type": "text",
"description": "Destination time zone"
},
{
"name_in_source": "destination_terminal_firms_code",
"name_in_destination": "destination_terminal_firms_code",
"data_type": "text",
"description": "Destination terminal firms code"
},
{
"name_in_source": "destination_terminal_nickname",
"name_in_destination": "destination_terminal_nickname",
"data_type": "text",
"description": "Destination terminal nickname"
},
{
"name_in_source": "destination_terminal_name",
"name_in_destination": "destination_terminal_name",
"data_type": "text",
"description": "Destination terminal name"
},
{
"name_in_source": "pol_empty_out_at",
"name_in_destination": "pol_empty_out_at",
"data_type": "timestamp",
"description": "Port of Lading Empty Out, as a UTC timestamp"
},
{
"name_in_source": "pol_empty_out_at_local",
"name_in_destination": "pol_empty_out_at_local",
"data_type": "text",
"description": "Port of Lading Empty Out, as a string in the POL local time zone"
},
{
"name_in_source": "pol_full_in_at",
"name_in_destination": "pol_full_in_at",
"data_type": "timestamp",
"description": "Port of Lading Full In event, as a UTC timestamp"
},
{
"name_in_source": "pol_full_in_at_local",
"name_in_destination": "pol_full_in_at_local",
"data_type": "text",
"description": "Port of Lading Full In event, as a string in the POL local time zone"
},
{
"name_in_source": "pol_loaded_at",
"name_in_destination": "pol_loaded_at",
"data_type": "timestamp",
"description": "Port of Lading Loaded event, as a UTC timestamp"
},
{
"name_in_source": "pol_loaded_at_local",
"name_in_destination": "pol_loaded_at_local",
"data_type": "text",
"description": "Port of Lading Loaded event, as a string in the POL local time zone"
},
{
"name_in_source": "pol_etd_at",
"name_in_destination": "pol_etd_at",
"data_type": "timestamp",
"description": "Port of Lading Estimated Time of Departure, as a UTC timestamp"
},
{
"name_in_source": "pol_etd_at_local",
"name_in_destination": "pol_etd_at_local",
"data_type": "text",
"description": "Port of Lading Estimated Time of Departure, as a string in the POL local time zone"
},
{
"name_in_source": "pol_atd_at",
"name_in_destination": "pol_atd_at",
"data_type": "timestamp",
"description": "Port of Lading Actual Time of Departure, as a UTC timestamp"
},
{
"name_in_source": "pol_atd_at_local",
"name_in_destination": "pol_atd_at_local",
"data_type": "text",
"description": "Port of Lading Actual Time of Departure, as a string in the POL local time zone"
},
{
"name_in_source": "pod_eta_at",
"name_in_destination": "pod_eta_at",
"data_type": "timestamp",
"description": "Port of Discharge Estimated Time of Arrival, as a UTC timestamp"
},
{
"name_in_source": "pod_eta_at_local",
"name_in_destination": "pod_eta_at_local",
"data_type": "text",
"description": "Port of Discharge Estimated Time of Arrival, as a string in the POD local time zone"
},
{
"name_in_source": "pod_arrived_at",
"name_in_destination": "pod_arrived_at",
"data_type": "timestamp",
"description": "Port of Discharge Actual Time of Arrival, as a UTC timestamp"
},
{
"name_in_source": "pod_arrived_at_local",
"name_in_destination": "pod_arrived_at_local",
"data_type": "text",
"description": "Port of Discharge Actual Time of Arrival, as a string in the POD local time zone"
},
{
"name_in_source": "pod_berthed_at",
"name_in_destination": "pod_berthed_at",
"data_type": "timestamp",
"description": "Port of Discharge Berthed event, as a UTC timestamp"
},
{
"name_in_source": "pod_berthed_at_local",
"name_in_destination": "pod_berthed_at_local",
"data_type": "text",
"description": "Port of Discharge Berthed event, as a string in the POD local time zone"
},
{
"name_in_source": "pod_discharged_at",
"name_in_destination": "pod_discharged_at",
"data_type": "timestamp",
"description": "Port of Discharge Discharged event, as a UTC timestamp"
},
{
"name_in_source": "pod_discharged_at_local",
"name_in_destination": "pod_discharged_at_local",
"data_type": "text",
"description": "Port of Discharge Discharged event, as a string in the POD local time zone"
},
{
"name_in_source": "pod_last_free_day_on",
"name_in_destination": "pod_last_free_day_on",
"data_type": "timestamp",
"description": "Current Last Free Day at the POD terminal, as a UTC timestamp"
},
{
"name_in_source": "pod_last_free_day_on_local",
"name_in_destination": "pod_last_free_day_on_local",
"data_type": "text",
"description": "Current Last Free Day at the POD terminal, as a string in the POD local time zone"
},
{
"name_in_source": "pod_pickup_appointment_at",
"name_in_destination": "pod_pickup_appointment_at",
"data_type": "timestamp",
"description": "Port of Discharge Pickup Appointment, as a UTC timestamp"
},
{
"name_in_source": "pod_pickup_appointment_at_local",
"name_in_destination": "pod_pickup_appointment_at_local",
"data_type": "text",
"description": "Port of Discharge Pickup Appointment, as a string in the POD local time zone"
},
{
"name_in_source": "pod_full_out_at",
"name_in_destination": "pod_full_out_at",
"data_type": "timestamp",
"description": "Port of Discharge Full Out event, as a UTC timestamp"
},
{
"name_in_source": "pod_full_out_at_local",
"name_in_destination": "pod_full_out_at_local",
"data_type": "text",
"description": "Port of Discharge Full Out event, as a string in the POD local time zone"
},
{
"name_in_source": "rail_departed_at",
"name_in_destination": "rail_departed_at",
"data_type": "timestamp",
"description": "First rail departure after the POD discharge, as a UTC timestamp"
},
{
"name_in_source": "rail_departed_at_local",
"name_in_destination": "rail_departed_at_local",
"data_type": "text",
"description": "First rail departure after the POD discharge, as a string in the POD local time zone"
},
{
"name_in_source": "destination_eta_at",
"name_in_destination": "destination_eta_at",
"data_type": "timestamp",
"description": "Destination Estimated Time of Arrival, as a UTC timestamp"
},
{
"name_in_source": "destination_eta_at_local",
"name_in_destination": "destination_eta_at_local",
"data_type": "text",
"description": "Destination Estimated Time of Arrival, as a string in the Destination local time zone"
},
{
"name_in_source": "destination_arrived_at",
"name_in_destination": "destination_arrived_at",
"data_type": "timestamp",
"description": "Destination Actual Time of Arrival, as a UTC timestamp"
},
{
"name_in_source": "destination_arrived_at_local",
"name_in_destination": "destination_arrived_at_local",
"data_type": "text",
"description": "Destination Actual Time of Arrival, as a string in the Destination local time zone"
},
{
"name_in_source": "destination_full_out_at",
"name_in_destination": "destination_full_out_at",
"data_type": "timestamp",
"description": "Destination Full Out event, as a UTC timestamp"
},
{
"name_in_source": "destination_full_out_at_local",
"name_in_destination": "destination_full_out_at_local",
"data_type": "text",
"description": "Destination Full Out event, as a string in the Destination local time zone"
},
{
"name_in_source": "empty_terminated_at",
"name_in_destination": "empty_terminated_at",
"data_type": "timestamp",
"description": "Container Empty Returned event, as a UTC timestamp"
},
{
"name_in_source": "empty_terminated_at_local",
"name_in_destination": "empty_terminated_at_local",
"data_type": "text",
"description": "Container Empty Returned event, as a string in the POD local time zone"
},
{
"name_in_source": "fees_at_pod_terminal",
"name_in_destination": "fees_at_pod_terminal",
"data_type": "text",
"description": "Current fee amounts, in JSON format"
},
{
"name_in_source": "demurrage_at_pod_terminal",
"name_in_destination": "demurrage_at_pod_terminal",
"data_type": "text",
"description": "Current demurrage amount owed"
},
{
"name_in_source": "holds_at_pod_terminal",
"name_in_destination": "holds_at_pod_terminal",
"data_type": "text",
"description": "Current terminal hold statuses, in JSON format"
},
{
"name_in_source": "freight_hold_at_pod_terminal",
"name_in_destination": "freight_hold_at_pod_terminal",
"data_type": "text",
"description": "Current freight hold, value is either Hold or empty"
},
{
"name_in_source": "customs_hold_at_pod_terminal",
"name_in_destination": "customs_hold_at_pod_terminal",
"data_type": "text",
"description": "Current customs hold, value is either Hold or empty"
},
{
"name_in_source": "usda_hold_at_pod_terminal",
"name_in_destination": "usda_hold_at_pod_terminal",
"data_type": "text",
"description": "Current USDA hold, value is either Hold or empty"
},
{
"name_in_source": "tmf_hold_at_pod_terminal",
"name_in_destination": "tmf_hold_at_pod_terminal",
"data_type": "text",
"description": "Current Traffic Mitigation Fee hold, value is either Hold or empty"
},
{
"name_in_source": "other_hold_at_pod_terminal",
"name_in_destination": "other_hold_at_pod_terminal",
"data_type": "text",
"description": "Any other current hold, value is either Hold or empty"
},
{
"name_in_source": "location_at_pod_terminal",
"name_in_destination": "location_at_pod_terminal",
"data_type": "text",
"description": "Location at port of discharge terminal"
},
{
"name_in_source": "availability_known",
"name_in_destination": "availability_known",
"data_type": "text",
"description": "Yes if Terminal49 is receiving availability status from the terminal, No otherwise."
},
{
"name_in_source": "available_for_pickup",
"name_in_destination": "available_for_pickup",
"data_type": "text",
"description": "If availability_known is Yes, then Yes if the container is available to be picked up at terminal, No otherwise"
},
{
"name_in_source": "equipment_length",
"name_in_destination": "equipment_length",
"data_type": "integer",
"description": "Length of the container"
},
{
"name_in_source": "equipment_type",
"name_in_destination": "equipment_type",
"data_type": "text",
"description": "Container type: Dry, Flat Rack, Open Top, Reefer, Tank, unknown"
},
{
"name_in_source": "equipment_height",
"name_in_destination": "equipment_height",
"data_type": "text",
"description": "Container height: High Cube, Standard, unknown"
},
{
"name_in_source": "equipment",
"name_in_destination": "equipment",
"data_type": "text",
"description": "Concatenation of the equipment_length, equipment_type, and equipment_height"
},
{
"name_in_source": "weight_in_lbs",
"name_in_destination": "weight_in_lbs",
"data_type": "integer",
"description": "Weight of the containre in lbs"
},
{
"name_in_source": "seal_number",
"name_in_destination": "seal_number",
"data_type": "text",
"description": "Seal number of the container"
},
{
"name_in_source": "pod_full_out_chassis_number",
"name_in_destination": "pod_full_out_chassis_number",
"data_type": "text",
"description": "The chassis number used when container was picked up at POD, if available"
},
{
"name_in_source": "pod_voyage_number",
"name_in_destination": "pod_voyage_number",
"data_type": "text",
"description": "Voyage number of the vessel that arrived or will arrive at the POD"
},
{
"name_in_source": "pod_vessel_name",
"name_in_destination": "pod_vessel_name",
"data_type": "text",
"description": "Name of the vessel that arrived or will arrive at the POD"
},
{
"name_in_source": "pod_vessel_imo",
"name_in_destination": "pod_vessel_imo",
"data_type": "text",
"description": "IMO of the vessel that arrived or will arrive at the POD"
},
{
"name_in_source": "terminal_checked_at",
"name_in_destination": "terminal_checked_at",
"data_type": "timestamp",
"description": "When the terminal was last checked, as a UTC timestamp"
},
{
"name_in_source": "line_tracking_last_succeeded_at",
"name_in_destination": "line_tracking_last_succeeded_at",
"data_type": "timestamp",
"description": "When the shipment information was last refreshed from the shipping line, as a UTC timestamp"
},
{
"name_in_source": "line_tracking_stopped_at",
"name_in_destination": "line_tracking_stopped_at",
"data_type": "timestamp",
"description": "When the tracking of the container stopped, as a UTC timestamp"
},
{
"name_in_source": "line_tracking_stopped_reason",
"name_in_destination": "line_tracking_stopped_reason",
"data_type": "text",
"description": "The reason Terminal49 stopped the tracking"
},
{
"name_in_source": "created_at",
"name_in_destination": "created_at",
"data_type": "timestamp",
"description": "When the container was added, as a UTC timestamp"
},
{
"name_in_source": "updated_at",
"name_in_destination": "updated_at",
"data_type": "timestamp",
"is_last_modified": true,
"description": "When the container was last updated, as a UTC timestamp"
}
],
"source_table": "analytics.customer_sync_containers",
"organization_column": "account_id",
"description": "This table contains 1 row per container (container_id is the unique key). Each container is part of 1 shipment (shipment_id). This is a large table with denormalized columns to make it easy to use for reporting purposes."
}
{
"model_name": "shipments",
"columns": [
{
"name_in_source": "shipment_id",
"name_in_destination": "shipment_id",
"data_type": "text",
"is_primary_key": true,
"description": "Shipment ID. This is the unique key of the table."
},
{
"name_in_source": "shipping_line_scac",
"name_in_destination": "shipping_line_scac",
"data_type": "text",
"description": "Standard carrier alpha numeric code of the shipping line"
},
{
"name_in_source": "shipping_line_name",
"name_in_destination": "shipping_line_name",
"data_type": "text",
"description": "Name of the shipping line"
},
{
"name_in_source": "bill_of_lading_number",
"name_in_destination": "bill_of_lading_number",
"data_type": "text",
"description": "Shipment number from the tracking request"
},
{
"name_in_source": "normalized_number",
"name_in_destination": "normalized_number",
"data_type": "text",
"description": "The normalized version of the shipment number used for querying the carrier"
},
{
"name_in_source": "reference_numbers",
"name_in_destination": "reference_numbers",
"data_type": "text",
"description": "Reference numbers of the shipment, contatenated"
},
{
"name_in_source": "tags",
"name_in_destination": "tags",
"data_type": "text",
"description": "Tags added to the shipment, sorted by alphabetical order, concatenated and separated by a comma"
},
{
"name_in_source": "customer_id",
"name_in_destination": "customer_id",
"data_type": "text",
"description": "Account ID of the customer"
},
{
"name_in_source": "customer_name",
"name_in_destination": "customer_name",
"data_type": "text",
"description": "Name of the customer"
},
{
"name_in_source": "pol_locode",
"name_in_destination": "pol_locode",
"data_type": "text",
"description": "Port of Lading UN/LOCODE"
},
{
"name_in_source": "pod_locode",
"name_in_destination": "pod_locode",
"data_type": "text",
"description": "Port of Discharge UN/LOCODE"
},
{
"name_in_source": "pod_terminal_firms_code",
"name_in_destination": "pod_terminal_firms_code",
"data_type": "text",
"description": "Port of Discharge terminal firms code"
},
{
"name_in_source": "destination_locode",
"name_in_destination": "destination_locode",
"data_type": "text",
"description": "Destination UN/LOCODE"
},
{
"name_in_source": "destination_terminal_firms_code",
"name_in_destination": "destination_terminal_firms_code",
"data_type": "text",
"description": "Destination terminal firms code"
},
{
"name_in_source": "pol_atd_at",
"name_in_destination": "pol_atd_at",
"data_type": "timestamp",
"description": "Port of Lading Actual Time of Departure, as a UTC timestamp"
},
{
"name_in_source": "pol_etd_at",
"name_in_destination": "pol_etd_at",
"data_type": "timestamp",
"description": "Port of Lading Estimated Time of Departure, as a UTC timestamp"
},
{
"name_in_source": "pod_eta_at",
"name_in_destination": "pod_eta_at",
"data_type": "timestamp",
"description": "Port of Discharge Estimated Time of Arrival, as a UTC timestamp"
},
{
"name_in_source": "pod_arrived_at",
"name_in_destination": "pod_arrived_at",
"data_type": "timestamp",
"description": "Port of Discharge Actual Time of Arrival, as a UTC timestamp"
},
{
"name_in_source": "pod_voyage_number",
"name_in_destination": "pod_voyage_number",
"data_type": "text",
"description": "Voyage number of the vessel that arrived or will arrive at the POD"
},
{
"name_in_source": "pod_vessel_name",
"name_in_destination": "pod_vessel_name",
"data_type": "text",
"description": "Name of the vessel that arrived or will arrive at the POD"
},
{
"name_in_source": "pod_vessel_imo",
"name_in_destination": "pod_vessel_imo",
"data_type": "text",
"description": "IMO of the vessel that arrived or will arrive at the POD"
},
{
"name_in_source": "line_tracking_last_succeeded_at",
"name_in_destination": "line_tracking_last_succeeded_at",
"data_type": "timestamp",
"description": "When the shipment information was last refreshed from the shipping line, as a UTC timestamp"
},
{
"name_in_source": "line_tracking_stopped_at",
"name_in_destination": "line_tracking_stopped_at",
"data_type": "timestamp",
"description": "When the tracking of the shipment stopped, as a UTC timestamp"
},
{
"name_in_source": "line_tracking_stopped_reason",
"name_in_destination": "line_tracking_stopped_reason",
"data_type": "text",
"description": "Reason why the tracking of the shipment stopped"
},
{
"name_in_source": "created_at",
"name_in_destination": "created_at",
"data_type": "timestamp",
"description": "When the shipment was added, as a UTC timestamp"
},
{
"name_in_source": "updated_at",
"name_in_destination": "updated_at",
"data_type": "timestamp",
"is_last_modified": true,
"description": "When the shipment was last updated, as a UTC timestamp"
}
],
"source_table": "analytics.customer_sync_shipments",
"organization_column": "account_id",
"description": "This table contains 1 row per shipment (shipment_id is the unique key). A shipment contains 1 or more containers."
}
{
"model_name": "tracking_requests",
"columns": [
{
"name_in_source": "tracking_request_id",
"name_in_destination": "tracking_request_id",
"data_type": "text",
"is_primary_key": true,
"description": "Tracking request ID. This is the unique key of the table."
},
{
"name_in_source": "request_number",
"name_in_destination": "request_number",
"data_type": "text",
"description": "Number requested to be tracked"
},
{
"name_in_source": "reference_numbers",
"name_in_destination": "reference_numbers",
"data_type": "text",
"description": "Reference numbers associated to the tracking request, concatenated"
},
{
"name_in_source": "shipment_tags",
"name_in_destination": "shipment_tags",
"data_type": "text",
"description": "Tags added to the request, concatenated and separated by a comma"
},
{
"name_in_source": "status",
"name_in_destination": "status",
"data_type": "text",
"description": "Status of the tracking request: created, pending, awaiting_manifest, failed, tracking_stopped"
},
{
"name_in_source": "failed_reason",
"name_in_destination": "failed_reason",
"data_type": "text",
"description": "For tracking requests that failed, a description of the error"
},
{
"name_in_source": "request_type",
"name_in_destination": "request_type",
"data_type": "text",
"description": "Type of tracking request: bill_of_lading, booking_number, or container"
},
{
"name_in_source": "scac",
"name_in_destination": "scac",
"data_type": "text",
"description": "Standard carrier alpha numeric code of the shipping line"
},
{
"name_in_source": "shipment_id",
"name_in_destination": "shipment_id",
"data_type": "text",
"description": "If the tracking request succeeded, this is the ID of the shipment that was created"
},
{
"name_in_source": "created_at",
"name_in_destination": "created_at",
"data_type": "timestamp",
"description": "When the tracking was requested, as a UTC timestamp"
},
{
"name_in_source": "updated_at",
"name_in_destination": "updated_at",
"data_type": "timestamp",
"is_last_modified": true,
"description": "When the tracking request was last updated, as a UTC timestamp"
}
],
"source_table": "analytics.customer_sync_tracking_requests",
"organization_column": "account_id",
"description": "This table contains 1 row per tracking request (tracking_request_id is the unique key). A tracking request can fail or succeed (status column). A successful tracking request will lead to the creation of a shipment (shipment_id). There can be multiple tracking requests for the same requested number (possibly failing before finally succeeding)."
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment