Daily balance snapshots

A finance analytics team needs to trend account balances by day. The source system stores the current balance on each account, but analysts need reproducible daily balance history for end-of-day reporting.

Periodic snapshot facts are a classic grain decision. If learners only store current account balance on a dimension, historical reporting becomes impossible; if they model each transaction instead, every balance query has to recompute state from events.

• Keep account identity and descriptive attributes in an account dimension.
• Create a daily balance snapshot fact with one row per account per snapshot date.
• Include a balance measure and an explicit snapshot date on the fact.
• Declare a primary or unique key that protects the account plus snapshot-date grain.
• Declare an explicit foreign-key relationship from the fact to the account dimension surrogate key.