In this blog post we’ll calculate the share of a row per group. There are multiple ways to do this: with aggregate functions and with analytic functions. We’ll go over both and demonstrate how analytic functions are far superior in terms of readability. First, let’s create a table to demonstrate…
One of Google’s latest acquisitions, ELT data orchestration tool Dataform, supports JavaScript. This is a great way to chain Dataform’s actions. In this blog post we explore how to automatically create a destination table in your data warehouse, before an incremental definition is run. If you are interested in a…
With the acquisition of Dataform by Google, it is steadily becoming the ELT data orchestration tool par préférence. In this blog post we explore how to automatically create a table in your data warehouse that will function as an incremental data destination in Dataform. If you are interested in a…
In this blog post, I discuss the difference between several BigQuery functions that appear to do the same: JSON_EXTRACT and JSON_QUERY — and their value counterparts JSON_EXTRACT_SCALAR and JSON_VALUE. MPP Data Warehouses are increasingly becoming data lakes. In this light, extracting strings/values from semistructured data (stored as a JSON STRING)…
In this blog post, I elaborate on two specific operations you’ll run into when using dates in BigQuery. Subtracting an interval from a data and calculating the difference between two dates. Subtracting a unit Subtracting a specific amount of days, weeks, months, quarters, or years from a date can be…
Fundamental to data modeling is that you need a fact table or a table with a unique key per row. That is no different in GoodData. In this blog post, I tackle a specific error that you might run into if your dataset doesn’t meet the necessary requirements. When I…
Here’s a blog on GoodData. They have a clever way of prepping and loading data from data warehouses. I was under the impression that reading rights would suffice for GoodData to access my BigQuery datasets, but clearly, that wasn’t the case. In GoodData’s Data Integration Console, I got this big…
Recently I decided to start streaming clickstream data (from this blog, indeed) into BigQuery using the insertAll method. It is a relatively cheap solution that requires almost no technical configuration. Until I bumped into an error. Let’s tackle it in this blog post. The error I encountered is: “google.api_core.exceptions.BadRequest: 400…
I’ve done a lot of web scraping in my life. Recently, I started using Google Cloud Platform to automate these kind of jobs. In this blog post I explain how to do it. Throughout this article I scrape the comments, categories and tags of the top clips on the popular…
In this blog post, I explain why a certain error in BigQuery arises and how you can get rid of it. Although I have abandoned the comma join syntax a while ago, I do happen to use it within the context of arrays in Google BigQuery (all the cool kids…