Snowflake SQL - How do I compare the current row counts of all tables to the row counts from 24 hours ago?

Question

Snowflake has time travel, which allows the querying of an object at a specific time stamp. It also has information_schema.tables, which is a view that shows the row counts for all tables as they exist currently. Since this is a view, it obviously can't be used in conjunction with time travel. This is a problem, as I need to compare the current row counts of all individual tables to what they were 24 hours ago.

Since information_schema.tables is a view, by definition it's a query - is there a place to find the script for this, so I can then use time travel in conjunction with it?

Failing that, is there some way to join onto a table using the table name present in the information schema?

Otherwise, is there a different/better way to do this? (taking daily snapshots is unfortunately not an option)

Answer 1

In my opinion you can achieve this with two ways:

1. Materializing the row count from information_schema.tables once a day into a new table (i.e. not materializing all the data from all your tables but just materializing the results of the view or even less just materializing the select sum(ROW_COUNT) from information_schema.tables;)
2. Using time travel and run select count(*) from myTable at(offset => -60*60*24) for each of your tables to get their individual row counts and then sum them up.