3 BigQuery SQL Tricks to Undo Your Screw Ups

Messing up while writing and running SQL is inevitable; your recovery determines whether this is a hiccup or an apocalypse.

“It Began As A Mistake.” Photo by Varvara Grabova on Unsplash.

I have some bad news for you; while writing SQL you will screw up and sometimes it will be bad.

But the greatest thing about working in code is that if you stay calm and think on the fly, nearly everything is fixable.

Even though I’ve grown as a data engineer, I mess up queries and accidentally write to tables more than I care to admit. In the beginning, I panicked. Now, I immediately go into problem-solving mode.

Although, from experience, I can tell you there are millions of ways to screw up in your data job, the three tricks I’ll demonstrate below will serve as a handy control-z for every day situations you’ll face as a first-year SQL developer.

Please note that my preferred SQL dialect/environment is BigQuery, so all examples will be relevant to GCP. Either way, results may vary.

SQL Undo Trick # 1 — Time Travel

Analog clock. Photo by Agê Barros on Unsplash.

Oh no! You’ve (hopefully accidentally) just stumbled into a worst-case scenario: Deleted a production table.

Luckily, if you’re a SQL developer in BigQuery, you can use either the gcloud command line interface (CLI), the UI or the BigQuery Python client to restore a table before anyone notices.

When GCP restores a table it doesn’t just control-z the situation and undo your operation.

Under the hood, GCP is actually restoring a snapshot, or replica, of a table, based on a given epoch, or time, parameter.

Let’s take a look at this process using the Python client.

In your panic, if you can, try to remember the last time the table was ‘alive.’ If you can’t, you can always check either the personal history or project history tab in the UI to see what time you ran your delete operation.