version: 2

models:
- name: sales_order_detail
description: |
Individual line items representing products sold within each sales order.

Purpose: Line-item transaction table enabling revenue analysis, product performance tracking, discount effectiveness measurement, and basket composition analysis. Foundation for calculating revenue metrics, product-level profitability, and customer purchasing patterns. Used extensively by metrics models for calculating CLV, average order value, gross profit, and product-specific KPIs.

Contents: One row per product line item on a sales order. Composite key: (salesOrderID, salesOrderDetailID). Scale: ~121K line items across ~31K orders spanning Sept 2022 to July 2025 (date-shifted to align with current date).

Lineage: Direct pass-through from stg_sales_order_detail, which sources from sales.salesorderdetail. Staging layer calculates lineTotal field and applies date shifting to modifiedDate.

Usage Guidance:
Foundational fact table for sales analytics. Essential for calculating revenue totals, analyzing product performance, measuring discount impact, and understanding purchasing behavior. Most revenue metrics aggregate lineTotal; product analysis groups by productID; discount analysis filters or segments by unitPriceDiscount or specialOfferID. For customer behavior analysis, aggregate to order level first via salesOrderID to avoid over-counting multi-item orders. For product profitability, join to product table for cost data then calculate margin (lineTotal - cost). When analyzing average order value, aggregate line items by order first to get order-level totals.

Critical Context:
- lineTotal is calculated in staging as (unitPrice * orderQty * (1 - unitPriceDiscount)) and represents net revenue after discounts but before taxes/freight. This is the primary revenue metric field.
- All dates shifted forward using shift_date() macro to make dataset feel current (max date aligns with March 28, 2025). Historical patterns span ~3 years.
- Null carrierTrackingNumber doesn't indicate data quality issue - reflects legitimate business states (orders not shipped yet, certain ship methods, or in-store pickup).
- salesOrderDetailID is unique within entire table (not just within order) - serves as primary key alone, though conceptually represents line item number within order.
- unitPrice reflects actual selling price at time of sale (may differ from product.listPrice due to negotiated pricing, promotions, or price changes over time).
- High orderQty outliers (>20) typically involve accessories or components sold in bulk, not bikes.
- No line items exist without corresponding order in sales_order_header - referential integrity is clean.

relationships:
- name: sales_order_header
description: >
Business relationship: Every line item belongs to exactly one sales order. Order header provides order-level context (customer, dates, shipping, totals, status) that applies to all line items within that order. Join to get customer attribution, order timing, territory assignment, shipping details, and order-level calculated fields (purchase context filters, consultation level, etc.).
Join considerations: Many-to-one from detail to header. Each salesOrderID in details appears in header exactly once. Each order in header typically has multiple detail rows (avg 3.9 line items per order, but distribution is right-skewed with many single-item orders).
Coverage: 100% of line items match to header. Clean referential integrity - no orphaned details.
Cardinality notes: Standard fact-to-dimension pattern. When joining, expect row count to remain same (detail-level grain preserved). When aggregating metrics from details, group by salesOrderID first to get order-level aggregates before further analysis to avoid over-representing multi-item orders.
source_col: salesOrderID
ref_col: salesOrderID
cardinality: many-to-one