For context, we have a variety of data sources being ingested into our Redshift instance. Our ingestion tool marks rows as deleted if they are deleted from the original source with a marked_deleted column.
Now this is where it gets kind of complicated, some of the sources we ingest data from already do this form of soft deletion and have their own marked_deleted or deleted_at columns. We're aware of these. But we'd like to find the tables that don't have soft deletes enabled on the data source. The tables that have their rows hard deleted.
Does a query exist that can query all tables on a Redshift instance to find out where marked_deleted = true and return a list of those table names? We have 250+ tables already and that number is set to grow fast, so ideally we would like a query we can periodically run to update the list of tables we need to be aware of that contain hard deletes.
I have no idea where to even begin with a query like this, so any information is helpful!