Dropping tables in a particular schema after X number of days from table creation date

Question

I have a schema specific for temporary tables in redshift. Eventually, as creation of a lot of tables takes a lot of space, I would like to know the following:

• Is there a way to automate deletion of tables in that schema after X days(lets say 30 days) after the table's creation date?
• Any articles on the above question I can refer to?

Thanks.

Answer 1

You could start on Is there any way to find table creation date in redshift? You can first collect the output to a temporary table and then run something that DROPs tables that have age over your threshold or you can do it in one step.