The great SQL leading vs trailing commas debate

It might seem a small thing, but I noticed that a recent update of the Snowflake database now allows you to have a trailing comma at the end of the SQL's SELECT statement. For example, this now works: SELECT my_field, my_field_2, FROM my_table Whereas before that'd give an error. It's arguably bad form nonetheless, but … Continue reading The great SQL leading vs trailing commas debate →

A quick way to count the number of null values in each field of a BigQuery table

After perhaps day 2 of many real-world jobs, most analysts have likely learnt to never fully trust any dataset without doing at least a little pre-exploration of the content and quality of the data. Before beginning the work to generate your world-shattering insights, it's therefore usually wise to run a few checks. One of my … Continue reading A quick way to count the number of null values in each field of a BigQuery table →

Travelling through time to query a BigQuery database from the past

Whilst looking into a broken Google BigQuery query recently, I chanced upon the "time travel" feature. This lets you query your BQ database to see what results it would have returned given the state of the data in the past, even if they are different to the results it now returns. I used that to … Continue reading Travelling through time to query a BigQuery database from the past →

Code-efficient alternatives to CASE WHEN statements in SQL

The CASE statement in SQL is the archetypal conditional statement, corresponding to the "if <A> then <B> else <C>" construct in other languages. Here's a quick refresher on what it looks like. Imagine we have a data table consisting of people, their ages and the number of years they've lived at their current and previous … Continue reading Code-efficient alternatives to CASE WHEN statements in SQL →

Extracting the date and time a UUID was created with Bigquery SQL (with a brief foray into the history of the Gregorian calendar)

I was recently working with records in a database that were identified by a Universally Unique Identifier, aka a UUID. These IDs are strings of characters that look something like "31ae75f0-cbe0-11e8-b568-0800200c9a66". I needed to know which records were generated during in a particular time period, but sadly there was no field about dates to be … Continue reading Extracting the date and time a UUID was created with Bigquery SQL (with a brief foray into the history of the Gregorian calendar) →

Actually you can use variables, CTEs and other fancy SQL with Tableau after all

A few months ago, I blogged about how you can use Tableau parameters when connecting to many database datasources in order to exert the same sort of flexibility that SQL coders can build into their queries using SQL variables. This was necessary because Tableau does not let you use SQL variables, common table expressions, temp table creation … Continue reading Actually you can use variables, CTEs and other fancy SQL with Tableau after all →