CRS Summary - order_summary Table

This page documents the order_summary table from the CRS Summary dataset.
This table contains one row per order with shipping address, package details,
and order metadata.

Table Overview

Attribute	Value

Attribute	Value
Table Name	crs_summary_20262601_order_summary
Database	fenix_tms_analytics_prod
Storage Location	s3://fenixlake/l1_processing/crs_summary/order_summary/
File Format	Parquet (Snappy compressed)
Row Grain	One row per order_id
Primary Key	order_id
Approximate Volume
Source System	Elasticsearch (CRS Summary Index)
ETL Job	crs_summary_job_scaled.py
Refresh Frequency	Daily (Incremental)

Business Description

The order_summary table provides a consolidated view of each order processed through the Carrier Rate Shopping (CRS) system. It captures:

Order Identification: Unique identifiers linking to source systems (Shopify, etc.)
Timestamps: When the order was created and processed by CRS
Shipping Method: Customer-selected shipping option and price charged
Destination Address: City, state, country, and coordinates (PII removed)
Package Specifications: Dimensions and weight of the shipment package
Order Metrics: Item count in the order

Key Business Use Cases:

Shipping cost analysis by destination zone
Package size distribution analysis
Order volume trending by geography
Shipping method preference analysis

Source Mapping

Target Column	Source JSON Path	Transformation

Target Column	Source JSON Path	Transformation
doc_id	`_id`	Direct mapping
index_name	`_index`	Direct mapping
source_id	`_source.id`	Direct mapping
order_id	`_source.order_id`	Direct mapping
order_number	`_source.order_number`	Direct mapping
order_source	`_source.order_source`	Direct mapping
zone	`_source.zone`	Direct mapping
is_shipment	`_source.shipment`	Direct mapping
processed_utc_dttm	`_source.processed_date_time.utc_dttm`	Direct mapping
processed_actual_dttm	`_source.processed_date_time.actual_dttm`	Direct mapping
created_utc_dttm	`_source.order_summary.created_date.utc_dttm`	Direct mapping
created_actual_dttm	`_source.order_summary.created_date.actual_dttm`	Direct mapping
tags	`_source.order_summary.tags`	Direct mapping
shipping_title	`_source.order_summary.shipping_info.title`	Direct mapping
shipping_code	`_source.order_summary.shipping_info.code`	Direct mapping
shipping_source	`_source.order_summary.shipping_info.source`	Direct mapping
shipping_price_amount	`_source.order_summary.shipping_info.shipping_price.amount`	Direct mapping
shipping_price_currency	`_source.order_summary.shipping_info.shipping_price.currency`	Direct mapping
shipping_city	`_source.order_summary.shipping_adress.city`	Direct mapping (note: typo `adress` in source)
shipping_province	`_source.order_summary.shipping_adress.province`	Direct mapping
shipping_province_code	`_source.order_summary.shipping_adress.province_code`	Direct mapping
shipping_country	`_source.order_summary.shipping_adress.country`	Direct mapping
shipping_country_code	`_source.order_summary.shipping_adress.country_code`	Direct mapping
shipping_zip	`_source.order_summary.shipping_adress.zip_code`	Direct mapping
shipping_latitude	`_source.order_summary.shipping_adress.latitude`	Direct mapping
shipping_longitude	`_source.order_summary.shipping_adress.longitude`	Direct mapping
package_name	`_source.crs_shipment_summary.shipment_summary.package_name`	Direct mapping
package_length	`_source.crs_shipment_summary.shipment_summary.dimensions.length`	Direct mapping
package_width	`_source.crs_shipment_summary.shipment_summary.dimensions.width`	Direct mapping
package_height	`_source.crs_shipment_summary.shipment_summary.dimensions.height`	Direct mapping
package_dimension_unit	`_source.crs_shipment_summary.shipment_summary.dimensions.units`	Direct mapping
package_weight_value	`_source.crs_shipment_summary.shipment_summary.weight.value`	Direct mapping
package_weight_unit	`_source.crs_shipment_summary.shipment_summary.weight.units`	Direct mapping
order_items_qty	`_source.order_summary.shipping_info.line_items`	Derived: `SIZE(line_items array)`, 0 if null
file_year	`N/A`	Enriched: Extracted from `_index` pattern
file_month	`N/A`	Enriched: Extracted from `_index` pattern
client_name	`N/A`	Enriched: Extracted from `_index` pattern
order_year	`_source.order_summary.created_date.utc_dttm`	Derived: `YEAR(created_utc_ts)`
order_month	`_source.order_summary.created_date.utc_dttm`	Derived: `MONTH(created_utc_ts)`
order_date	`_source.order_summary.created_date.utc_dttm`	Derived: `TO_DATE(created_utc_ts)`
ingested_at_utc	`N/A`	System: `CURRENT_TIMESTAMP()`

Column Definitions

Identifier Columns:

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
doc_id	STRING	No	Elasticsearch document ID	9955996828002
index_name	STRING	No	Elasticsearch index name containing client and date	https://www.google.com/search?q=bombas.myshopify.com-crs_summary-2026.01
source_id	STRING	No	Source system record ID (same as order_id)	9955996828002
order_id	STRING	No	Primary Key - Unique order identifier	9955996828002
order_number	STRING	No	Human-readable order number from source system	#29430886
order_source	STRING	No	Source e-commerce platform	shopify

Order Attribute Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
zone	STRING	Yes	Shipping zone designation	5
is_shipment	BOOLEAN	No	Flag indicating if record is a shipment (always FALSE in this table)	false
tags	STRING	Yes	Comma-separated order tags from source system	Riskified::approved, Riskified::submitted

Timestamp Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
processed_utc_dttm	STRING	Yes	When CRS processed the order (UTC)	2026-01-01T06:26:54+00:00
processed_actual_dttm	STRING	Yes	When CRS processed the order (original timezone)	2026-01-01T06:26:54+00:00
created_utc_dttm	STRING	Yes	When order was created (UTC)	2026-01-01T06:18:25+00:00
created_actual_dttm	STRING	Yes	When order was created (original timezone)	2026-01-01T01:18:25-05:00

Shipping Method Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
shipping_title	STRING	Yes	Customer-facing shipping method name	Standard Shipping
shipping_code	STRING	Yes	Internal shipping method code	Standard Shipping
shipping_source	STRING	Yes	Source of shipping method	shopify
shipping_price_amount	DOUBLE	Yes	Price charged to customer for shipping	7.95
shipping_price_currency	STRING	Yes	Currency code for shipping price	USD

Shipping Address Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example