How to get last modified datetime of tables in Redshift Serverless

Just to add a "bit more technical detail" for anyone implementing this:

It is important to distinguish between the two timestamps provided by SHOW TABLES and SVV_ALL_TABLES:

• table_last_altered_time: This reflects DDL changes. It updates when the table structure is modified (e.g., ALTER TABLE, TRUNCATE, or when the table is dropped and recreated).
• table_last_modified_time: This reflects DML operations. It updates when data is actually added or changed (e.g., INSERT, UPDATE, DELETE, or COPY).

In my experience, as mentioned, keep in mind the asynchronous update of these metadata fields; it can take up to 20 minutes for a data load to be reflected in the last_modified timestamp. This matches to the AWS documentation "The modification and alteration timestamps can lag behind the table updates by approximately 20 minutes." source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TABLES.html

If you need to query this programmatically for a specific schema, you can use:

SELECT 
    table_name, 
    table_last_modified_time 
FROM svv_all_tables 
WHERE table_schema = 'your_schema_name'
AND table_type = 'BASE TABLE';

source: https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALL_TABLES.html#r_SVV_ALL_TABLES-sample-queries