关于AdventureWorksDW中FactProductInventory表UnitCost变动的技术问询

Great question—this ties directly into how the AdventureWorksDW's inventory fact table is designed, so let's unpack it step by step:

1. First, recognize the table type: Periodic Snapshot Fact Table #

The FactProductInventory is a periodic snapshot fact table, not a transactional fact table. That means it captures the state of inventory at regular intervals (usually daily) regardless of whether any inventory transactions (like UnitIn or UnitOut) occurred. So it's totally normal to see rows with identical UnitIn/UnitOut values (both 0) but different dates—those are just daily snapshots of the same static inventory level.

2. Why does UnitCost change when inventory quantity doesn't? #

The fluctuating UnitCost even with no inventory movement comes down to how product costs are managed in the AdventureWorks ecosystem:

• Standard Cost Updates: The source OLTP system (AdventureWorks) allows for periodic updates to a product's standard cost—this could be due to supplier price changes, shifts in production costs, or internal accounting adjustments. The AWDW ETL process pulls this updated cost into the daily snapshot, so even if inventory quantity stays the same, the latest cost is reflected in subsequent rows.
• Cost Calculation Methods: If the business uses a cost method like weighted average cost, a prior inventory receipt (with a different unit cost) would update the overall average unit cost of the inventory. Once that average is recalculated, all future snapshots will show this new cost until another transaction or cost adjustment happens—even if no new inventory moves in or out in the interim.
• ETL Sync Logic: The AWDW's ETL is designed to capture the current cost of each product every day as part of the snapshot process. It doesn't check for inventory movement first; it just grabs the latest cost value from the source system and writes it into the fact table alongside the current inventory level.

Example Scenario #

Let's say ProductKey=1 has a standard cost of $15.00 for the first 21 days, so all those snapshots show $15.00 with no inventory movement. On day 22, the finance team updates the standard cost to $16.50 to account for increased material costs. From that day forward, every daily snapshot (even with UnitIn/UnitOut still at 0) will show $16.50 as the UnitCost—that's exactly the fluctuation you're seeing.

This design is intentional: it lets analysts track how the value of inventory changes over time, even when the physical quantity stays constant, which is critical for financial reporting and inventory valuation.

内容的提问来源于stack exchange，提问作者Nico M.