Introduction DBT is a great tool for orchestrating and managing SQL running against Data Warehouses. When using BigQuery, it can be useful to profile the DBT runs and capture the slot usage and the bytes processed to measure the cost. DBT Enhancement DBT issue 2808 and PR 2809 added a label to each…

Introduction GKE Usage Metering is a great feature that enables GKE profiling, capturing the usage and cost of CPU, Memory, Storage, and Network Egress (optional). There are provided example queries and Data Studio dashboards. The goal of this blog post is to show you an alternative hour-by-hour analysis and a detailed…

Another day, another challenge — a customer wanted to parse and compare fixed-width text files. These files are used frequently by mainframes and for data interchange. This is to validate some of their new BigQuery-based pipelines with legacy pipelines. These files are - Newline delimited (so just like a normal…

Introduction We’re very happy to announce the availability of Liquibase Spanner extension beta version 1.0. This brings all of Liquibase’s CI/CD benefits to Spanner. You can find the source and detailed information in GitHub here: cloudspannerecosystem/liquibase-spanner A Liquibase extension adding support for Google Cloud Spanner. Include this in your application project to run…github.com Supported Features

The Code I’m happy to announce a comprehensive sample SQL parser, translator, and formatter written in Python. It is available open source at https://github.com/google/sample-sql-translator. Overview It is intended to do the following - Provide an example of a recursive descent parser for SQL. Provide a template for SQL parser, rewriting, analysis, and formatting. …

The Challenge Building on BigQuery: delta to latest, rather than taking multiple changes and finding the latest value we want to capture the changes over time. Rather than using ARRAY_AGG this will use LAST_VALUE as an analytic function. The Solution Test Data First we will create a set of the test data called testing.sparsedata. This will…

The Challenge A colleague presented me with a challenge: I have a series of updates with only the changed fields set. NULLs indicate no change. I have the original records. How do I handle this in BigQuery? Putting this together into a BigQuery script will be for another day, but for now…

Introduction Sometimes you need to compare data across two BigQuery tables. You may want to do this generically (match entire-row-by-row), sometimes comparing by key. This is far easier than you may think! Sample Data NOTE: You need the transactions dataset for this SQL. Create a left_table with a million rows. …

Do you ever get duplicate rows in BigQuery? I’m going to explore some techniques for deduplication in BigQuery both for the whole table and by partition. It assumes you have the dataset transactions. Create Your Test Data This will create a table containing two columns (date, v). There will be 21 days of data…