Series

Practical BigQuery

Articles in this series

De-duplicating with ROW_NUMBER vs ARRAY_AGG

Dec 2, 20231 min read

What function do you use to explicitly de-duplicate in BigQuery?I normally use ROW_NUMBER(), but I've recently encountered a really interesting blog...

De-duplicating with ROW_NUMBER vs ARRAY_AGG

Using SELECT * with EXCEPT and REPLACE

Nov 30, 20231 min read

SELECT * is not a good practice in production, but I still use it for spot checks, when debugging, analyzing or validating data - especially when...

Using SELECT * with EXCEPT and REPLACE

Using STRUCTS for Audit Fields in BigQuery

Nov 25, 20233 min read

I use STRUCTS in #BigQuery quite a bit. One interesting use case for them is for audit purposes a separate column containing metadata about how the...

Using STRUCTS for Audit Fields in BigQuery

Comparing ranking functions in BigQuery

Nov 15, 20231 min read

One of the most common sightings in SQL code is using ranking functions. It's simple but we must surely get it right. I use ROW_NUMBER very often for...

Comparing ranking functions in BigQuery

Tidying up WINDOW functions in BigQuery with named windows

Nov 8, 20234 min read

So, if you ever find yourself working with multiple window functions in BigQuery, leverage the named windows specification for tidier, leaner...

Tidying up WINDOW functions in BigQuery with named windows

Using ARRAY_AGG in BigQuery

Nov 7, 20231 min read

Almost everybody knows the usual standard SQL aggregation functions like SUM, MAX or AVG. In this short post, we're going to look at ARRAY_AGG:...

Using ARRAY_AGG in BigQuery