Introduction

I have been using Google’s pipe query syntax (henceforth just “pipe syntax”) as my daily driver in work for about a year now. I find myself a lot more productive when developing queries, and find it a lot easier to underestand queries that I wrote several months ago when reviewing them.

I want to start documenting my learnings and experience in the form of hands-on exercises that hopefully readers can follow along with.

Any and all feedback is welcome at any time. Please email them to: michael[at]fullstackdata.dev

Lessons

Quick links for readers to access other lessons.

1. Introduction (this article)
2. WIP

Prerequisites

I will assume the reader is familiar with basic SQL concepts (e.g. joins, aggregation, CTEs, structs, arrays) and is willing to reference Google’s SQL docs or other reading materials as needed. Most importantly, I will assume the reader is not a complete novice in data modelling using SQL. After all, the intended audience for the tutorial’s full scope are analysts/engineers that find conventional SQL unnecessarily tedious to develop and maintain for complex data needs. There’s not much point in learning pipe syntax if all you need is to count or sum over a column.

Some readers may find that the difficulty of the exercises ramp up pretty quickly. I expect this to be the case since I need non-trivial (sometimes complex) examples for data modelling exercises to demonstrate some more powerful uses of Pipe syntax. Furthermore, working with an unfamiliar dataset almost always comes at the cost of additional headspace required, so don’t be discouraged and reread the lessons at your own pace.

Setting up a BigQuery sandbox

Since this series of lessons is intended to be hands-on, I encourage the reader to setup a sandbox in BigQuery to play around with the queries or even attempt to develop the query before reading the sample solution. See Google’s guide here on setting up a sandbox.

We will be querying Google’s public dataset bigquery-public-data.google_analytics_sample throughout the lessons. To test you have setup your sandbox correctly, run the following query in the BigQuery console and check that the query returns 2556.

FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
|> AGGREGATE
    SUM(totals.visits) AS total_visits,

While you can choose to learn more about the dataset in Google’s guide, I don’t expect you to, and will provide my interpretation of the data where needed.