ccdw_fact_promotion_line_item

This table records the application of promotions to individual line items. Each row represents a promotion line item in an order.

Column Alias	Data Type	Key	Description	Index?
`promotion_line_item_id`	BIGINT	PK	Surrogate key uniquely identifying this promotion line item. Use for joins and deduplication.	Yes
`promotion_id`	INTEGER	FK	Foreign key to `ccdw_dim_promotion`. Use for promotion-level reporting and attribution.	Yes
`sku_id`	INTEGER	FK	Foreign key to `ccdw_dim_product` for the SKU affected by the promotion. Use for product- or SKU-level promotion analysis.	Yes
`promotion_instance_id`	VARCHAR(50)		Unique key for one application of a promotion within an order. A single order-level promotion can generate multiple instances (one per qualifying line).	No
`line_item_id`	BIGINT	FK	Foreign key to `ccdw_fact_line_item`. Links to the fact line item for quantity, unit price, and other line-level details.	No
`nline_item_id`	VARCHAR(50)		Natural key for the line item in the source system, for example, OCAPI/SCAPI line item identifier). Normalized for cross-system use.	No
`order_id`	BIGINT	FK	Surrogate key for the order. Use to join with `ccdw_fact_line_item` and other order-level facts.	Yes
`norder_id`	VARCHAR(50)		Natural key for the order in the source system, for example, order number or document ID).	No
`utc_submit_timestamp`	TIMESTAMP		UTC timestamp when the promotion was applied to the line item. Use for exact-time filtering and incremental loads.	Yes
`utc_submit_day_id`	SMALLINT	FK	Foreign key to `ccdw_dim_date` for the UTC submission day. Use for UTC-based date partitioning and calendar joins.	Yes
`utc_submit_time_id`	SMALLINT	FK	Foreign key to `ccdw_dim_time` for the UTC submission time. Use for time-of-day analysis in UTC.	No
`utc_update_timestamp`	TIMESTAMP		Last modified UTC timestamp for this record. Use for incremental loads or change detection.	Yes
`std_submit_timestamp`	TIMESTAMP		Timestamp when the promotion was applied, expressed in the realm's standard (reporting) time zone.	No
`std_submit_day_id`	SMALLINT	FK	Foreign key to `ccdw_dim_date` for the standard submission day. Use for reporting-date partitioning and day-level reporting.	Yes
`std_submit_time_id`	SMALLINT	FK	Foreign key to `ccdw_dim_time` for the standard submission time. Use for time-of-day analysis in reporting time zone.	No
`std_timezone_id`	SMALLINT	FK	Foreign key to `ccdw_dim_timezone` for the time zone used to derive `std_submit_*` date and time columns (realm reporting time zone).	No
`locale_id`	SMALLINT	FK	Foreign key to `ccdw_dim_locale` (language and country). Represents the session locale when the promotion was applied.	Yes
`site_id`	SMALLINT	FK	Foreign key to `ccdw_dim_site` for the site where the order was placed (submission site).	Yes
`business_channel_id`	SMALLINT	FK	Foreign key to `ccdw_dim_business_channel`, for example, storefront, call center). Use for channel-level reporting.	Yes
`user_agent_id`	INTEGER	FK	Foreign key to `ccdw_dim_user_agent`. Represents device or browser context when the promotion was applied.	Yes
`campaign_id`	INTEGER	FK	Foreign key to `ccdw_dim_campaign` for the marketing campaign associated with this promotion, if any. Nullable.	No
`source_code_group_id`	INTEGER	FK	Foreign key to `ccdw_dim_source_code_group`. Use for source code or affiliate attribution. Nullable.	No
`coupon_id`	INTEGER	FK	Foreign key to `ccdw_dim_coupon` for the coupon applied with this promotion line item, if any. Null when no coupon was used.	No
`visitor_id`	VARCHAR(100)		Anonymous or persistent visitor identifier from the session. Use for visit-level or anonymous-user analysis.	No
`customer_id`	BIGINT	FK	Foreign key to `ccdw_dim_customer`. Use for customer-level promotion analysis. Might reference guest or registered customer.	Yes
`buyer_email`	VARCHAR(256)		Email address of the buyer.	No
`is_anonymous`	BOOLEAN		Indicates whether customer is anonymous or registered.	No
`do_not_track`	BOOLEAN		Indicates whether the customer has opted out of tracking.	No
`lcl_currency_id`	SMALLINT	FK	Foreign key to `ccdw_dim_currency` for the tendered (local) currency—the currency in which the order was placed.	No
`std_currency_id`	SMALLINT	FK	Foreign key to `ccdw_dim_currency` for the realm's reporting currency. All `std_*` monetary values in this row are converted to this currency.	No
`shipping_geography_id`	INTEGER	FK	Foreign key to `ccdw_dim_geography` for the shipping address (region/country). Use for geographic reporting.	No
`invoice_geography_id`	INTEGER	FK	Foreign key to `ccdw_dim_geography` for the invoice or billing address (region/country).	No
`customer_geography_id`	INTEGER	FK	Foreign key to `ccdw_dim_geography` for the customer's address (region/country).	No
`quantity`	NUMERIC		Number of units of the line item to which this promotion application applies (quantity purchased for this line).	No
`lcl_li_promo_disc`	NUMERIC		Line-item-level promotional discount amount in tendered (local) currency.	No
`lcl_li_promo_disc_order_alloc`	NUMERIC		Portion of an order-level promotional discount allocated to this line item (prorated), in tendered currency.	No
`lcl_li_promo_disc_ship_alloc`	NUMERIC		Portion of a shipping-level promotional discount allocated to this line item (prorated), in tendered currency.	No
`lcl_li_manual_disc`	NUMERIC		Line-item-level manual discount amount in tendered (local) currency.	No
`lcl_li_manual_disc_order_alloc`	NUMERIC		Portion of an order-level manual discount allocated to this line item (prorated), in tendered currency.	No
`lcl_li_manual_disc_ship_alloc`	NUMERIC		Portion of a shipping-level manual discount allocated to this line item (prorated), in tendered currency.	No
`lcl_li_tax`	NUMERIC		Line-item-level tax amount in tendered (local) currency.	No
`lcl_li_tax_order_alloc`	NUMERIC		Portion of order-level tax allocated to this line item (prorated), in tendered currency.	No
`lcl_li_shipping`	NUMERIC		Line-item-level shipping charge in tendered (local) currency.	No
`lcl_li_shipping_order_alloc`	NUMERIC		Portion of order-level shipping allocated to this line item (prorated), in tendered currency.	No
`lcl_li_gross_merchandise_value`	NUMERIC		Gross Merchandise Value (GMV) of the line item in tendered currency allocated to the promotion line item.	No
`std_li_promo_disc`	NUMERIC		Line-item-level promotional discount in standard (reporting) currency. Converted from tendered currency for cross-currency reporting.	No
`std_li_promo_disc_order_alloc`	NUMERIC		Portion of order-level promotional discount allocated to this line item (prorated), in standard reporting currency.	No
`std_li_promo_disc_ship_alloc`	NUMERIC		Portion of shipping-level promotional discount allocated to this line item (prorated), in standard reporting currency.	No
`std_li_manual_disc`	NUMERIC		Line-item-level manual discount in standard (reporting) currency. Converted from tendered currency.	No
`std_li_manual_disc_order_alloc`	NUMERIC		Portion of order-level manual discount allocated to this line item (prorated), in standard reporting currency.	No
`std_li_manual_disc_ship_alloc`	NUMERIC		Portion of shipping-level manual discount allocated to this line item (prorated), in standard reporting currency.	No
`std_li_tax`	NUMERIC		Line-item-level tax in standard (reporting) currency. Converted from tendered currency.	No
`std_li_tax_order_alloc`	NUMERIC		Portion of order-level tax allocated to this line item (prorated), in standard reporting currency.	No
`std_li_shipping`	NUMERIC		Line-item-level shipping charge in standard (reporting) currency. Converted from tendered currency.	No
`std_li_shipping_order_alloc`	NUMERIC		Portion of order-level shipping allocated to this line item (prorated), in standard reporting currency.	No
`std_li_gross_merchandise_value`	NUMERIC		Gross Merchandise Value (GMV) of the line item in standard currency allocated to the promotion line item.	No