Delta: Vacuum delta table based on version numbers

[prasadvaze]

Currently, the vacuum command uses time internal to determine the version history to be retained in the delta table. But this approach does not work when different rows in a table can version (get changes) at different times. For e.g. some rows may update daily versus others may update monthly. When the requirement is to keep x number of history versions for each row then time-internal based history retention is not ideal. Instead, there should be the capability to retain history based on the number of past versions that get applied to all rows in a table irrespective of the time-interval.

[tdas]

If you have to maintain a fixed number of versions per key/unique-row-id (i.e. fine-grained versioning), then full table (i.e., coarse-grained) versioning is not the right mechanism to use for that purpose. This is because

1. It is prohibitively expensive for the infrastructure to maintain mapping between row-level version for each rows to table-level versions.
2. Even if you do that, it may be prohibitive to maintain enough full-table versions just to ensure every row has N versions. For example, if a row changes once a month, maintaining 6 versions for that row will require maintaining all the versions across 6 months, which may be 1000 versions of other rows and therefore is a waste of storage space.

The absolutely traditional way to have fine-grained control on the history of a key/row is to use SCD Type 2 and Type 3 operations. There you explicitly maintain all the historical values of a key in the latest snapshot of the table. So you dont need to maintain arbitrary number of table versions for this purpose.

Basic SCD Type 2 can be implemented using the merge operation as shown in this example in the docs - https://docs.delta.io/latest/delta-update.html#slowly-changing-data-scd-type-2-operation-into-delta-tables

While SCD Type 2 is specifically used to maintain all historical versions, I am sure it can be extended to maintain explicitly delete any row version older than the latest N for that row. For example, if you use the basic SCD Type 2 operation to maintain an auto-incrementing version number for each key, (e.g, (k, version1, value1), (k, version2, value2), etc.), then you can write another merge operation that finds the max version for every key (say, M) and deletes all rows for each key where version < M - N (where N is the number of versions to maintain).