Question

During the design of a fact table, what is the difference between an &#x27;accumulating snapshot&#x27; and a &#x27;periodic snapshot&#x27; regarding how they track time-based business metrics?

Accepted Answer

A periodic snapshot fact table captures the state of a business process at regular, consistent intervals of time. It acts like a recurring status report, recording metrics such as daily account balances, monthly sales totals, or weekly inventory levels. Each row represents a summary of activity over a specific time period, such as a day, week, or month, regardless of whether any changes occurred during that time. Because it captures data at predefined intervals, it is ideal for analyzing trends and performance over time. An accumulating snapshot fact table tracks the entire lifespan of a business process that has a clear beginning and end. It contains multiple date columns representing milestones in the process, such as order received, order picked, order shipped, and order delivered. Unlike a periodic snapshot, the row is updated multiple times as the process moves through its stages. Each row stays in the table and is revisited to update status columns or duration metrics until the process is complete. While the periodic snapshot focuses on snapshots of status across time, the accumulating snapshot focuses on the progress of a specific transaction from start to finish.

Home → All Courses → Engineering and Technology Courses → Business Intelligence Engineering → Flashcard

During the design of a fact table, what is the difference between an 'accumulating snapshot' and a 'periodic snapshot' regarding how they track time-based business metrics?