This week, I want to talk about something we’ve all done at least once – especially before our first cup of coffee in the morning. Yes, that’s right – dropping tables and databases.
So we have our table from last time still existing in our database:
We’re trying to get rid of a table from the week before titled TestOutChanging2 and our fingers slip and we whack the wrong table!
Thankfully, in Snowflake, undoing our mistake is as easy as typing the following:
UNDROP TABLE TestOutChanging;
Now, thankfully, we realized our mistake within seconds of doing it. That makes it easy, but what if you don’t realize it until after lunch or at midnight? The lunch remembrance should work – midnight, maybe not depending…
So, this relies on Time Travel which by default is 24 hours. So, just remember to fix it within your time travel window, or you’re going to be stuck working with backups. You do have a working backup, right?
You also cannot undrop the table in a schema other than the one it was created in. For example, if I was working in the public schema, but wanted to restore it to the Staging schema, then that’s not allowed. You have to restore it to the public schema in this example’s case.
If you have semi-noticed that your table doesn’t exist any more and create it from scratch, but give up after a bit and try to undrop it – that doesn’t work either. Complete and utter fail.
That’s it for this post. Hopefully, if you do have a complete fail before your morning coffee hits, remember to simply undrop it. Maybe, that will save you hours of work and a lifetime of regret.