BigQuery and New Users: The Top 3 “WTF!?” Moments

I use Google BigQuery a lot. On a daily basis, I run dozens of queries, use it to build massively scalable data pipelines for our clients, and regularly help new users who are navigating it for the first time. Suffice it to say, I’m somewhat accustomed to its little quirks. Unfortunately, the same can’t be said for the new users who are commonly left scratching their heads, and shouting “What the fudge!?” at their monitors.

Here are the top three WTFs that I regularly hear from new BigQuery users.

BigQuery has an awesome streaming API. When new users start streaming data into BigQuery, the first thing they do is eyeball the table using the preview to check if the data have arrived safe and sound (and understandably so). The problem is that there is a delay in streamed data showing up in the table preview. In addition to that, over the years, I’ve come to learn that the table preview (and the table details for that matter) is buggy, and unfortunately, can’t be trusted. Mwaaah!

How to turn that frown upside down: Always query the table directly to check whether or not it contains data. Never rely on the table preview.

This one can be absolute noodle-scratcher for new users. I’ve even witnessed some folk have total meltdowns and hurl their machines across the office (at me) in utter frustration. BigQuery has two SQL dialects: standard and legacy.

Legacy is the dialect that was initially used when BigQuery was released into the wild back in 2012. It’s not pure SQL, and it’s not that bad to use – it’s just a bit funky (the syntax that is). I must admit, it took me some time to get used to it. However, sometime last year, Google made the (smart) move to support the 2011 SQL standard, which made a lot of people very happy indeed.

The confusion arises from the fact that by default BigQuery runs queries using legacy SQL. As a result, I commonly see new users start writing their queries using standard dialect (as they should do), but they fail to realize that they need to explicitly tell BigQuery they are using standard SQL.