Three Sources, One Revenue Model

Grain: one row per order line item.

Reasoning:

• Both transactional sources already emit line-level records. Line grain preserves maximum detail and can be rolled up to order, day, or month later. The reverse is impossible.
• An order-level fact collapses multiple products into one row, so revenue by product category would need a second fact table or a non-additive workaround.
• A daily aggregate would satisfy the first question but permanently kill the second. Point-in-time region attribution cannot be re-derived from a daily total - you would have to re-read the source, which defeats the warehouse.

Measures and additivity: quantity, unit_price, discount_amount, extended_amount.

• extended_amount and quantity are additive across every dimension.
• unit_price is not additive - a region's unit price is SUM(extended_amount)/SUM(quantity), a weighted average. Summing prices produces a meaningless number and is a classic first-draft error.

Keys: order_id + line_number form the degenerate dimension identifying a row. Surrogate keys for date, product, customer, store, and a role-playing region dimension (order region vs. shipping region).

One caution: if historical orders are re-loaded, the fact insert must be idempotent on (order_id, line_number) or revenue double-counts.

Type 2 slowly changing dimension on customer.

• Type 2 adds effective_start_date, effective_end_date, is_current, and a version number, and issues a new surrogate key when region changes. Facts keep pointing at the surrogate key that was current at order time, so history stays correct with no fact-table rewrite.
• Point-in-time join: order_date BETWEEN effective_start_date AND effective_end_date.
• Type 1 is wrong here. Overwriting region would re-attribute every historical order to the new region and silently restate prior-period revenue - exactly the number finance signs off on.

Limitation to document: the 2024 re-organization moved 12,000 customers, but if no source system captured when each customer moved, you can only create version rows using the re-org date as an approximation, or start tracking forward from the modeling date. Either way, pre-re-org attribution is inferred, not observed. Say so on the dashboard footnote rather than presenting it as fact.

Alternative worth weighing: if customers change region often (mobility, remote moves), a monthly periodic snapshot fact at customer-month grain is cheaper to maintain than a long Type 2 chain and handles many attribute changes at once.

1. customer_id collision → composite natural key plus a crosswalk.

• customer_id is only unique within a store. The natural key is (store_id, customer_id).
• Build a mapping table (crosswalk) from each source natural key to one warehouse surrogate customer key. Without it you will either merge two different people with the same numeric ID - inflating that 'customer's' lifetime value - or split one person across two records, which understates repeat rate.
• Deduplicate with deterministic rules first (exact match on normalized name + DOB + phone), then fuzzy match on the remainder, and store the match confidence in the crosswalk so the merge is auditable and reversible. Never fuzzy-merge silently.
• The repeated address on every POS row is denormalized; normalize it into the customer dimension. But addresses change - decide deliberately whether address is Type 1 (fix typos) or Type 2 (real moves).

2. Nested supplier array → bridge table.

• Flatten to bridge_product_supplier (product_key, supplier_key, supplier_rank, is_primary) with a separate supplier dimension.
• Leave the array as a JSON blob only if no one will ever filter on supplier. The moment someone asks 'revenue by supplier' (they will), a blob forces a full scan and a schema change.
• Validate cardinality: confirm no product has zero suppliers (orphan) and none has an unexpected count, or the bridge will drop products from revenue totals.