What are Snapshot Tables?

To preserve records from a given transactional source for given day, Office of Decision Support has developed a number of "snapshot" tables.  A snapshot table holds the same raw, transactional data as its source in the transactional system, with additional fields for tracking the snapshot date. Snapshot tables are populated nightly with those rows of data that are considered effective (i.e., current) in the source table at the time of the extract. For snapshots that are based on effective-dated PeopleSoft source tables, the appropriate effective–dating logic is built in to the data extract to ensure the snapshot table contains the effective rows for any given extract date.

In addition to providing point-in-time data, some snapshot tables contain fields that are derived from one or more transactional elements using business logic to construct a single, frequently used reporting element. Derived elements are structured to improve consistency, performance, and ease of use in reporting.