I had an issue when someone from my team recreated the table more than once using CREATE OR REPLACE as there were some new fields that had to be added. However, instead of adding a column using ALTER TABLE, the entire table was recreated.

I tried getting the data back using time travel. But since CREATE OR REPLACE drops and recreates the table, the time travel was not working.

SELECT * FROM {MY_TABLE} AT (OFFSET => -60*60*23);

Time travel data is not available for table {MY_TABLE}. The requested
time is either beyond the allowed time travel period or before the
object creation time.

I tried unropping the table using UNDROP {MY_TABLE} after renaming the current version of the table to something else. But no luck! I still don’t see the data. I am wondering if there is any other way to get the data back as it is important since we follow incremental load instead of kill and fill.

ALTER TABLE {MY_TABLE} RENAME TO {MY_TABLE_BLANK};

UNDROP TABLE {MY_TABLE};