We can improve performance by having tables that only contain the transactions for the current business day. If we combine the Daily Table information with the Snapshot information, we may easily calculate any values for the current business day in a much more efficient manner. For example, instead of looking at all 1000+ days of data for a three year old account, we only need to look at one day's data for that account. When this reduction of labor is applied to ten thousand accounts, then the savings is even greater.
An extremely important facet of using a Daily table centers around data integrity: any transaction that appears in the Daily Table also needs to appear in the corresponding Historical Table as well. The question becomes: when does the transaction get placed into each table? It is possible to place the transaction into each table at the same time, or to place it only in the Daily Table, and then move it to the Historical table during a batch process. The final answer to this involves looking at the requirements as well as desired performance of the system.
Design note: you most likely only need one Daily Table for a given Historical table: and that Daily Table would be for the current day. Most of the time, that should be sufficient, since the real purpose of the Daily Table is to make everything faster while processing transactions in real time. It is possible to create them as needed when auditing older history in an account; however, simply using the Snapshots and gathering the historical rows should be sufficient since auditing rarely needs real time analysis.
Next: Delta Table