CRS Summary - service_quotes Table
This page documents the service_quotes table from the CRS Summary dataset. This table contains one row per shipping service quote per order, including selected, rejected, upgrade, and void service options.
Table Overview
Attribute | Value |
|---|---|
Table Name | crs_summary_20262601_service_quotes |
Database | fenix_tms_analytics_prod |
Storage Location | s3://fenixlake/l1_processing/crs_summary/service_quotes/ |
File Format | Parquet (Snappy compressed) |
Row Grain | One row per (order_id, service_quote_type, carrier_name, service_code) |
Primary Key | order_id + service_quote_type + carrier_name + service_code (composite) |
Approximate Volume |
|
Source System | Elasticsearch (CRS Summary Index) |
ETL Job | crs_summary_job_scaled.py |
Refresh Frequency | Daily (Incremental) |
Business Description
The service_quotes table captures all shipping service options evaluated by the Carrier Rate Shopping (CRS) system for each order. It provides:
Selected Service: The carrier/service actually chosen for shipping
Rejected Services: Alternative options that were considered but not chosen
Upgrade Service: Premium shipping option available to customer
Void Service: Service that was voided/cancelled
Key Business Use Cases:
Carrier rate comparison analysis
Cost savings calculation (selected vs. alternatives)
Transit time analysis by carrier/service
Carrier market share analysis
Service Quote types
service_quote_type | Source JSON Path | Description | Cardinality |
|---|---|---|---|
selected |
| The service chosen for the order | 0-1 per order |
upgrade |
| Premium upgrade option | 0-1 per order |
void |
| Voided/cancelled service | 0-1 per order |
rejected |
| Services not selected | 0-N per order |
The rejected_service_summary is an ARRAY and is exploded to create multiple rows. Other service types are single objects with at most one row per order.
Source Mapping
Target Column | Source JSON Path | Transformation |
|---|---|---|
doc_id |
| Direct mapping |
source_id |
| Direct mapping |
source_order_id |
| Direct mapping |
source_order_number |
| Direct mapping |
order_id |
| Direct mapping |
service_quote_type |
| Derived: Literal based on source path |
carrier_name |
| Direct mapping |
service_code |
| Direct mapping |
service_name |
| Direct mapping (may be NULL) |
expected_shipping_ts |
|
|
expected_delivery_ts |
|
|
transit_time_days |
| Direct mapping |
base_price_amount |
| Direct mapping |
base_price_currency |
| Direct mapping |
total_price_amount |
| Direct mapping |
total_price_currency |
| Direct mapping |
total_surcharges_amount |
| Direct mapping |
total_surcharges_currency |
| Direct mapping |
client_name |
| Enriched: From |
order_year |
| Derived: |
order_month |
| Derived: |
order_date |
| Derived: |
ingested_at_utc |
| System: |
Column Definitions
Identifier Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
doc_id | STRING | No | Elasticsearch document ID | 9823192383842 |
source_id | STRING | No | Source system record ID | 9823192383842 |
source_order_id | STRING | No | Original order ID | 9823192383842 |
source_order_number | STRING | No | Human-readable order number | #26066057 |
order_id | STRING | No | Order identifier (FK to order_summary) | 9823192383842 |
Service Identification Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
service_quote_type | STRING | No | Type of quote: selected, rejected, upgrade, void | selected |
carrier_name | STRING | Yes | Shipping carrier name | USPS, UPS, FEDEX |
service_code | STRING | No | Carrier service code | usps_ground_advantage |
service_name | STRING | Yes | Human-readable service name | GROUND_HOME_DELIVERY |
Transit Time Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
expected_shipping_ts | TIMESTAMP | Yes | Expected ship date/time | 2025-07-23 11:00:00.000 |
expected_delivery_ts | TIMESTAMP | Yes | Expected delivery date/time | 2025-07-28 11:00:00.000 |
transit_time_days | INTEGER | Yes | Number of transit days | 4 |
Pricing Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
base_price_amount | DOUBLE | Yes | Base shipping rate before surcharges | 4.37 |
base_price_currency | STRING | Yes | Currency for base price | USD |
total_price_amount | DOUBLE | Yes | Total shipping cost including surcharges | 4.37 |
total_price_currency | STRING | Yes | Currency for total price | USD |
total_surcharges_amount | DOUBLE | Yes | Sum of all surcharges | 0.0 |
total_surcharges_currency | STRING | Yes | Currency for surcharges | USD |
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) | 7 |
order_date | DATE | No | Date order was created | 2025-07-23 |
Audit Columns
Column Name | Data Type | Nullable | Description | Example |
|---|---|---|---|---|
ingested_at_utc | TIMESTAMP | No | When record was ingested | 2026-01-31 11:02:57.622 |
Business Logic and Transformations
Union of Service Types
Four separate extractions are performed: 1. selected_df <- service_summary (single object) 2. upgrade_df <- upgrade_service_summary (single object) 3. void_df <- crs_void_service_summary (single object) 4. rejected_df <- rejected_service_summary (EXPLODE array) All four are combined using UNION BY NAME.
Pricing Calculations
total_price_amount = base_price_amount + total_surcharges_amount (This relationship should hold, but values come directly from source)
Data Quality Notes
Known Issues:1. service_name may be NULL for some carriers (USPS, UPS) 2. Some orders may have 0 service quotes if CRS processing failed 3. transit_time_days may differ from actual calendar days between ship and delivery dates
Relationships
Related Table | Join Key(s) | Relationship |
|---|