Amazon Redshift how to get the last date a table inserted data

Question

I am trying to get the last date an insert was performed in a table (on Amazon Redshift), is there any way to do this using the metadata? The tables do not store any timestamp column, and even if they had it, we need to find out for 3k tables so it would be impractical so a metadata approach is our strategy. Any tips?

Answer 1

All insert execution steps for queries are logged in STL_INSERT. This query should give you the information you're looking for:

SELECT sti.schema, sti.table, sq.endtime, sq.querytxt
FROM 
    (SELECT MAX(query) as query, tbl, MAX(i.endtime) as last_insert
    FROM stl_insert i
    GROUP BY tbl
    ORDER BY tbl) inserts
JOIN stl_query sq ON sq.query = inserts.query
JOIN svv_table_info sti ON sti.table_id = inserts.tbl
ORDER BY inserts.last_insert DESC;

Note: The STL tables only retain approximately two to five days of log history.