CRS Summary - shipping_surcharges Table
This page documents the shipping_surcharges table from the CRS Summary dataset. This table contains detailed surcharge breakdowns for each service quote, plus aggregate total rows for reporting convenience.
Table Overview
Attribute | Value |
|---|---|
Table Name | crs_summary_20262601_shipping_surcharges |
Database | fenix_tms_analytics_prod |
Storage Location | s3://fenixlake/l1_processing/crs_summary/shipping_surcharges/ |
File Format | Parquet (Snappy compressed) |
Row Grain | One row per surcharge + one aggregate row per service |
Primary Key | order_id + service_quote_type + carrier_name + service_code + surcharge_code (composite) |
Approximate Volume |
|
Source System | Elasticsearch (CRS Summary Index) |
ETL Job | crs_summary_job_scaled.py |
Refresh Frequency | Daily (Incremental) |
Business Description
The shipping_surcharges table provides granular visibility into shipping surcharges applied by carriers. It contains:
Individual Surcharge Rows (is_derived = false): One row per specific surcharge type
Aggregate Total Rows (is_derived = true): One summary row per service with total surcharges
Common Surcharge Types:
Key Business Use Cases:
Surcharge cost breakdown analysis
Fuel surcharge trending over time
Residential vs. commercial shipping cost comparison
Carrier surcharge policy comparison
Row Types
INDIVIDUAL SURCHARGE ROWS (is_derived = false)
surcharge_name: Specific surcharge type (e.g., "FUEL", "RESIDENTIAL_DELIVERY")
surcharge_code: Carrier's surcharge code
surcharge_amount: Amount for this specific surcharge
total_surcharge: NULL
AGGREGATE TOTAL ROWS (is_derived = true)
surcharge_name: "TOTAL"
surcharge_code: "TOTAL"
surcharge_amount: NULL
total_surcharge: SUM of all surcharges for this service
Source Mapping
Target Column | Source JSON Path | Transformation |
|---|---|---|
doc_id | _id | Direct mapping |
source_id | _source.id | Direct mapping |
source_order_id | _source.order_id | Direct mapping |
source_order_number | _source.order_number | Direct mapping |
order_id | _source.order_id | Direct mapping |
service_quote_type | N/A | Derived: Literal based on source path |
carrier_name | {service_path}.carrier | Direct mapping |
service_code | {service_path}.service.code | Direct mapping |
surcharge_name | shipping_surcharges[].name | EXPLODE array |
surcharge_code | shipping_surcharges[].code | EXPLODE array |
surcharge_description | shipping_surcharges[].description | EXPLODE array |
surcharge_amount | shipping_surcharges[].amount.amount | EXPLODE array |
currency | shipping_surcharges[].amount.currency | EXPLODE array |
total_surcharge | N/A | Derived: SUM(surcharge_amount) per group |
is_derived | N/A | Derived: false for individual, true for aggregate |
client_name | N/A | Enriched: From _index |
order_year | created_date.utc_dttm | Derived: YEAR() |
order_month | created_date.utc_dttm | Derived: MONTH() |
order_date | created_date.utc_dttm | Derived: TO_DATE() |
ingested_at_utc | N/A | System: CURRENT_TIMESTAMP() |
Column Definitions
Identifier Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
doc_id | STRING | No | Elasticsearch document ID | 9836158189922 |
source_id | STRING | No | Source system record ID | 9836158189922 |
source_order_id | STRING | No | Original order ID | 9836158189922 |
source_order_number | STRING | No | Human-readable order number | #26375734 |
order_id | STRING | No | Order identifier (FK to order_summary) | 9836158189922 |
Service Reference Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
service_quote_type | STRING | No | Type of quote: selected, rejected, upgrade, void | rejected |
carrier_name | STRING | Yes | Shipping carrier name | FEDEX |
service_code | STRING | No | Carrier service code | fedex_home_ground |
Surcharge Detail Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
surcharge_name | STRING | Yes | Surcharge type name | FUEL, RESIDENTIAL_DELIVERY, TOTAL |
surcharge_code | STRING | Yes | Carrier's internal surcharge code | FUEL, 1009, TOTAL |
surcharge_description | STRING | Yes | Human-readable description | Fuel Surcharge |
surcharge_amount | DOUBLE | Yes | Individual surcharge amount (NULL for aggregate rows) | 1.55 |
currency | STRING | Yes | Currency code | USD |
Aggregate & Flag Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
total_surcharge | DOUBLE | Yes | Sum of all surcharges (NULL for individual rows) | 4.17 |
is_derived | BOOLEAN | No | Flag: false = individual surcharge, true = aggregate total | true |
Partition Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
client_name | STRING | No | Client/tenant identifier |
|
order_year | INTEGER | No | Year order was created | 2025 |
order_month | INTEGER | No | Month order was created (1-12) | 9 |
order_date | DATE | No | Date order was created | 2025-09-01 |
Audit Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
ingested_at_utc | TIMESTAMP | No | When record was ingested | 2026-01-31 10:57:53.480 |
Relationships
Related Table | Join Key(s) | Relationship | Description |
|---|---|---|---|
service_quotes | order_id + service_quote_type + carrier_name + service_code | N:1 | Each surcharge belongs to one service quote |
order_summary | order_id | N:1 | Each surcharge belongs to one order |