CRS Summary - order_line_items Table

This page documents the order_line_items table from the CRS Summary dataset. This table contains one row per line item per order, including product details and physical shipping attributes.

Table Overview

Attribute	Value

Attribute	Value
Table Name	crs_summary_20262601_order_line_items
Database	fenix_tms_analytics_prod
Storage Location	s3://fenixlake/l1_processing/crs_summary/order_line_items/
File Format	Parquet (Snappy compressed)
Row Grain	One row per (order_id, line_item_id)
Primary Key	order_id + line_item_id (composite)
Approximate Volume
Source System	Elasticsearch (CRS Summary Index)
Refresh Frequency	Daily (Incremental)

Business Description

The order_line_items table provides detailed product-level information for each item in an order. It combines:

Commercial Line Item Data: Product IDs, SKUs, prices, quantities from the order
Physical Product Data: Weight and dimensions from the shipment product catalog

Data Sources (within the JSON):

order_summary.shipping_info.line_items[] - Commercial order data
crs_shipment_summary.shipment_summary.product_infos[] - Physical product attributes

Source Mapping

Target Column	Source JSON Path	Transformation

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
line_item_id	`line_items[].id`	EXPLODE array, extract ids
sku	`line_items[].sku`	EXPLODE array, extract sku
product_id	`line_items[].product_id`	EXPLODE array
variant_id	`line_items[].variantId`	EXPLODE array (note: camelCase in source)
vendor	`line_items[].vendor`	EXPLODE array
item_name	`line_items[].name`	EXPLODE array
quantity	`line_items[].quantity`	EXPLODE array
unit_price_amount	`line_items[].price.amount`	EXPLODE array, nested extraction
currency	`line_items[].price.currency`	EXPLODE array, nested extraction
requires_shipping	`line_items[].requires_shipping`	EXPLODE array
gift_card	`line_items[].gift_card`	EXPLODE array
lead_time	`product_infos[].lead_time`	JOIN on (order_id, sku)
gift_sku	`product_infos[].gift_sku`	JOIN on (order_id, sku)
item_weight_value	`product_infos[].weight.value`	JOIN on (order_id, sku)
item_weight_unit	`product_infos[].weight.units`	JOIN on (order_id, sku)
item_length	`product_infos[].dimensions.length`	JOIN on (order_id, sku)
item_width	`product_infos[].dimensions.width`	JOIN on (order_id, sku)
item_height	`product_infos[].dimensions.height`	JOIN on (order_id, sku)
item_dimension_unit	`product_infos[].dimensions.units`	JOIN on (order_id, sku)
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

Column Name	Data Type	Nullable	Description	Example
doc_id	STRING	No	Elasticsearch document ID	9837237895522
source_id	STRING	No	Source system record ID	9837237895522
source_order_id	STRING	No	Original order ID from source	9837237895522
source_order_number	STRING	No	Human-readable order number	#26401760
order_id	STRING	No	Order identifier (FK to order_summary)	9837237895522
line_item_id	STRING	No	Unique line item identifier within the order	31613392617826

Product Identification Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
sku	STRING	Yes	Stock Keeping Unit code	A-E-A-400L27-MARL-01P-BURGU-XS-R
product_id	STRING	Yes	Product catalog ID	7682993946796
variant_id	STRING	Yes	Product variant ID	47902442553698
vendor	STRING	Yes	Product vendor/brand	bombas
item_name	STRING	Yes	Full product name with variant details	Women's Gripper Slipper - burgundy / xs

Order Quantity & Pricing Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
quantity	INTEGER	Yes	Quantity ordered of this line item	1
unit_price_amount	DOUBLE	Yes	Price per unit	50.0
currency	STRING	Yes	Currency code	USD

Shipping Flag Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
requires_shipping	BOOLEAN	Yes	Whether item requires physical shipping	true
gift_card	BOOLEAN	Yes	Whether item is a gift card (no shipping)	false
gift_sku	BOOLEAN	Yes	Whether SKU is marked as gift in catalog	false

Product Physical Attributes (from product_infos)

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
lead_time	INTEGER	Yes	Product lead time in days	0
item_weight_value	DOUBLE	Yes	Individual item weight	0.45
item_weight_unit	STRING	Yes	Weight unit of measure	LB
item_length	DOUBLE	Yes	Item length dimension	12.2
item_width	DOUBLE	Yes	Item width dimension	6.0
item_height	DOUBLE	Yes	Item height dimension	2.8
item_dimension_unit	STRING	Yes	Dimension unit of measure	IN

Partition Columns

Column Name	Data Type	Nullable	Description	Example

Column Name	Data Type	Nullable	Description	Example
client_name	STRING	No	Client/tenant identifier	🔎 bombas.myshopify.com - Google Search
order_year	INTEGER	No	Year order was created	2025
order_month	INTEGER	No	Month order was created (1-12)