Delta Table

The Delta Table is auxiliary table for a Daily Table. We use this table when we need to manipulate the rows in maner that may be temporary or even destructive. When a row is added to the Daily Table, is it also added to the Delta Table. For example: we have a table that contains multiple transactions where some transactions represent opening actions, while other actions represent closing actions, Specific opening transactions may be matched with specific closing transactions, When the match is made, both rows are removed from the table. The only rows that remain in the table are the unmatched ones. These unmatched rows are then passed to another system that uses them for other calculations.

Since we are removing the rows as they are matched, we are able to minimize the number of rows in the Delta Table. This in turns means any new rows added in later have to be compared against a much lower number of prior rows when looking for matches. Thus, over a day, then amount of work being done at any one point in time fairly small.

We could do this matching using only a Daily Table; however that would greatly impact performance. As the daily table builds up more and more rows, all of the data must be moved around for the matching calculation, thus wasting network bandwidth. Furthermore, the same work is going to be repeated throughout the day; with the end result of the matching taking longer and longer to perform. Since a Delta Table only keeps the unmatched rows, we only need to look at the specific unmatched rows, process them, and move on. This difference in performance may easily be on the order of processing 5 rows vs processing 10,000 rows at one time.

Note: a Delta Table should have the same structure as the Daily Table whose data it is manipulating. Thus, if one of the rows in the Daily Table has an edit applied to it, the Delta Table may simply clear the rows for the affected account, and then reload the information from the Daily Table. At that point, the Delta Table may then continue on normally.

Next: Conclusion