Datawise — SQL, BigQuery & Python for Data Engineers
Practical SQL, BigQuery, and Python tutorials for data engineers. Real-world case studies, no fluff — written by a Staff Data Engineer with 12+ years in the field.
Series
Latest posts
- Using WHERE inside aggregate functions
Filter the input rows of any BigQuery aggregate with WHERE. What it does, how it compares to HAVING MAX / HAVING MIN, and why it beats CASE WHEN.
- pre_ and post_operations in Dataform
Learn how to use pre_ and post_operations in Dataform to manage variables, incremental watermarks, cleanup logic, and post-run tasks.
- BigQuery Object Tables: A Practical Introduction
Learn how BigQuery Object Tables work, how to create them, and how to use ML.GENERATE_TEXT to run AI inference on unstructured data stored in GCS.
- BigQuery BigLake Tables Explained: What They Are and When to Use Them
BigQuery BigLake tables improve on classic external tables with access delegation, row-level security, and metadata caching. Includes a SQL example.
- BigQuery Saves Your Query Results — Here's How to Find Them
BigQuery keeps every query result in a temporary table for 24 hours. Here's where to find it, how it powers free cached results, and the 10 GB cache limit.
- Parameters in BigQuery
Query parameters and variables both hold typed values in BigQuery. Learn when to pass parameters in from outside and when to DECLARE and SET variables.
- Flattening JSON arrays in BigQuery
Learn how to use BigQuery's JSON_FLATTEN to handle complex JSON arrays efficiently without losing data context when hierarchy isn't important
- Using LAST_VALUE with STRUCTS
Learn how to correctly use LAST_VALUE with STRUCTs in SQL and handle non-null structures with practical examples and solutions
- Table grain quick validation with SQL
Quickly validate table grain, spot duplicate rows, and test key assumptions using SQL patterns built on TO_JSON_STRING and FARM_FINGERPRINT.
- WITH expressions in BigQuery
Learn how WITH expressions in BigQuery can simplify complex SQL queries and reduce boilerplate code by defining scoped variables within expressions
- Beware of ROW_NUMBER without ORDER BY
Avoid using ROW_NUMBER() without ORDER BY to prevent random changes in reports. Understand its pitfalls and ensure data accuracy
- Aggregating Multiple SCD-2 Attribute Timelines in BigQuery
Learn how to aggregate SCD-2 attribute timelines in BigQuery using SQL techniques to preserve temporal context efficiently
- Why you should think twice before UNNESTing arrays or date intervals
Avoid unnecessary unnesting of arrays and date intervals in BigQuery to improve efficiency and manage data cardinality effectively
- Cross-dataset foreign key relationships in BigQuery
Learn to create cross-dataset foreign key relationships in BigQuery and explore metadata enhancements for SQL tables with ease
- Compacting date intervals in BigQuery
Compact adjacent date intervals in BigQuery by hashing payload columns, detecting boundaries with LAG, and grouping contiguous segments step by step.
- Null-safe comparison: IS DISTINCT/NOT DISTINCT FROM
Discover NULL-safe SQL operators IS DISTINCT FROM and IS NOT DISTINCT FROM for safer comparisons without IFNULLs or COALESCE in BigQuery
- Transforming cumulative sums into monthly values
Reverse cumulative sums to monthly values using BigQuery, focusing on fiscal year, lag function, and grain considerations
- A quick walkthrough BigQuery Remote Functions
Explore how BigQuery Remote Functions enable data processing using external resources with step-by-step guidance
- Change history in BigQuery
Discover how BigQuery's Change History features track data changes, including updates and deletes, using APPENDS and CHANGES TVFs
- A quick overview of BigQuery functions
Explore BigQuery functions: built-in, custom, and remote. Learn about their types, uses, and how they enhance SQL capabilities