Append-only tables in Snowflake

Snowflake is pretty fast and can handle updates like all other SQL databases. But the cost of performing updates depends on the number of rewritten micro-partitions. How do I avoid updates and still have data updated?

Table structure

The main key in the solution is that each row in the table points at its previous version. The table also contains updated and deleted timestamps.

Querying data

You cannot just query all the data from such table. You need to check if the row you are getting is the newest row. You will do it by checking if any previous pointer does not point at the row.

Performance

The performance of such data structure depends on the dynamics of your data and how often you update them. If you have 1 billion rows table and perform 300k inserts and 10k updates once a day, the previous query generates the following query plan.