All posts
208 posts on SQL, BigQuery, Python and data engineering.
2026
2025
- Flattening JSON arrays in BigQuery
- Using LAST_VALUE with STRUCTS
- Table grain quick validation with SQL
- WITH expressions in BigQuery
- Beware of ROW_NUMBER without ORDER BY
- Aggregating Multiple SCD-2 Attribute Timelines in BigQuery
- Why you should think twice before UNNESTing arrays or date intervals
- Cross-dataset foreign key relationships in BigQuery
- Compacting date intervals in BigQuery
- Null-safe comparison: IS DISTINCT/NOT DISTINCT FROM
- Transforming cumulative sums into monthly values
- A quick walkthrough BigQuery Remote Functions
- Change history in BigQuery
- A quick overview of BigQuery functions
- Expressing multiple repeated joins as a correlated subquery
- Revisiting GROUP BY ROLLUP with a more realistic example
- Revisiting Why SQL’s Order of Execution Matters
- Exploring the BigQuery Query History
- How ARRAY() can function as UNPIVOT and UNNEST as PIVOT?
- Choosing the Right Ranking Function: Why Ties in SQL Matter
- A closer look at STRING_AGG in BigQuery
- Here's a great use case for GenAI writing SQL
- Using EXISTS with LOGICAL_OR in BigQuery
2024
- Extended data type support for BigQuery search indexes
- Why you should care about VS Code Remote Development
- For quality work, understand your data grain first
- Sometimes, you have to use subqueries!
- Not all NULLS are the same
- Extracting keys from JSON in BigQuery
- Computing a hash aggregation in BigQuery
- Another look at ANY_VALUE in BigQuery
- Why partitioning tables is not a silver bullet for BigQuery performance
- Using RANGE_BUCKET in BigQuery
- Calculating the Median in BigQuery
- Dynamically extracting JSON data in BigQuery
- Using tempfile module in Python
- A quick look at the json module in Python
- Why how the data is collected matters
- Retrying in Python using tenacity
- sorted(List) vs List.sorted() in Python
- Using virtual environments in Python
- Installing Python packages with pip
- A practical exercise working with ARRAYS and correlated subqueries in BigQuery
- Query parameters in BigQuery
- System variables in BigQuery
- Ingestion-time partitioning in BigQuery
- My day as a Data Engineer 8 years ago
- DECLARE and SET variables in BigQuery
- Comments in SQL
- NON-EQUI joins in SQL
- LAX JSON conversion functions in BigQuery
- Determining JSON types in BigQuery
- Replicating datasets across regions in BigQuery
- NATURAL JOIN in SQL
- Short, almost non-technical guide to SQL query tuning as a Data Engineer
- SEMI-JOINS in SQL
- Anti-joins in SQL
- Self-joins in SQL
- The relationship between ARRAY_AGG and UNNEST
- Why basic roles in BigQuery are a bad idea
- A couple of fun things about NULL in SQL
- FLOAT vs NUMERIC in BigQuery
- Breaking Free from Tutorial Hell: Tips for Aspiring Data Professionals
- Easy with that SELECT DISTINCT!
- The PARTITIONS view in INFORMATION_SCHEMA
- Here's how GROUP BY works differently across SQL dialects
- Controlling ordering of NULL values in the ORDER BY clause
- Joining with USING vs ON in BigQuery
- Another look at LOGICAL_AND & LOGICAL_OR in BigQuery
- The first thing I do when analyzing a SQL table
- How LIMIT helps you save time in BigQuery
- Combining ANY_VALUE with HAVING in BigQuery
- What are GCP Cloud Workflows and how can they help you as a Data Engineer
- Using the bq CLI utility with BigQuery
- Why you should use parentheses with AND & OR in SQL
- The NOT NULL Constraint in BigQuery
- Generating a Random Number in BigQuery
- Using subqueries with Row Level Security in BigQuery
- The order in which you ROUND matters in SQL
- Order of precedence in SQL: WHERE vs HAVING
- Transactions in BigQuery
- Where does QUALIFY fit in the order of execution in BigQuery?
- Using GAPS_FILL in BigQuery
- Time Series functions in BigQuery
- JSON datatype vs JSON-like STRING in BigQuery
- A simple data validation scenario using FULL OUTER JOIN & ORDER BY
- The JSON datatype in BigQuery
- SESSION_USER in BigQuery
- Concatenation operator in BigQuery
- Using FORMAT_DATE in BigQuery
- Raising ERRORS in BigQuery
- Cleaning up STRINGS in BigQuery
- Lessons from Chernobyl and how it relates to software engineering
- Using INSTR in BigQuery
- Cross-dataset Foreign Key referencing in BigQuery
- Pay attention to cardinality & grain when UNNESTING in BigQuery!
- Constructing STRUCTS in BigQuery
- Using STRUCTS for quick analysis in BigQuery
- Understanding STRUCTS in BigQuery
- Sharded tables in BigQuery
- Why does your Data Warehouse need to look more like a pharmacy than a retail store?
- Comparing tables with FULL OUTER JOIN
- Splitting a STRING in BigQuery
- Search Indexes in BigQuery
- A portable Data Analytics stack using Docker, Mage, dbt-core, DuckDB and Superset
- Extract all pattern occurrences in BigQuery
- Why you should use UNION DISTINCT sparingly
- ORDER BY expressions in SQL
- Accessing ARRAY elements in BigQuery
- Enumerating ARRAY elements in BigQuery using WITH OFFSET
- UNNESTING ARRAYS in BigQuery
- Boolean data type in BigQuery
- COALESCE vs IFNULL vs NULLIF in BigQuery
- GREATEST & LEAST in BigQuery
- RANGE data type in BigQuery
- DELETE + INSERT vs MERGE in BigQuery
- Using GROUP BY ALL in BigQuery
- Using zip in Python
- Why you should care about partition pruning in BigQuery
- Calculating the MODE in BigQuery
- Using INCLUDE NULLS with UNPIVOT in BigQuery
- Watch out when using SAFE_CAST in BigQuery
- Rolling period calculation in BigQuery
- Generating date intervals in BigQuery
- DATETIME vs TIMESTAMP in BigQuery
- Using RANGE in Window Functions in BigQuery
- Computing a cumulative sum in BigQuery
2023
- Optimizing storage costs in BigQuery
- Optimizing compute cost in BigQuery
- Leveraging ARRAYS in BigQuery for query performance
- Decorators in Python
- Using COUNTIF() in BigQuery
- Partial functions in Python
- Approximate Aggregate Functions in BigQuery
- Using ARRAY_CONCAT_AGG() in BigQuery
- Using ANY_VALUE() in BigQUERY
- Using LOGICAL_AND and LOGICAL_OR in BigQuery
- Comprehensions in Python
- LIKE ALL and LIKE ANY in BigQuery
- SELECT AS STRUCT and SELECT AS VALUE
- Does order of expressions in the WHERE clause matter?
- Filling up missing values with LAST_VALUE
- De-duplicating with ROW_NUMBER vs ARRAY_AGG
- Using SELECT * with EXCEPT and REPLACE
- Dunder / magic functions in Python
- Using STRUCTS for Audit Fields in BigQuery
- Comparing ranking functions in BigQuery
- Tidying up WINDOW functions in BigQuery with named windows
- Using ARRAY_AGG in BigQuery
- Using Correlated Subqueries in BigQuery
- Intersect and Except in BigQuery
- Picking between CTE, View and Temp Table in BigQuery
- Table Sampling in BigQuery
- Importing Google Sheets into BigQuery
- Using BigQuery hashing functions
- Pay attention to this when UNNESTING in BigQuery
- Combining STRUCTs with Window Functions in BigQuery
- Using Dynamic SQL in BigQuery
- Linting BigQuery SQL with sqlfluff
- Context Managers in Python
- Using GCP Cloud Functions in Data Engineering
- Write better SQL queries faster using Mock Data
- Swapping Partitions in BigQuery
- Using Enums in Python
- The power of BigQuery INFORMATION_SCHEMA views
- Using Labels in BigQuery
- Python Showdown: Namedtuple vs SimpleNamespace vs DataClass
- Understanding Generators in Python 🐍
- Hands-on with DuckDB
- Structural Pattern Matching in Python
- MERGE ON FALSE in BigQuery
- BigQuery Primary Key & Foreign Key constraints
- Using TUPLES in Python
- Using SETS in Python
- Using MAX_BY / MIN_BY in BigQuery
- Dictionary Unpacking in Python
- Scheduled queries in BigQuery
- Row-level access security in BigQuery
- A few thoughts about recent Python integration into Excel
- Optimizing SQL queries in BigQuery
- Using ROLLUP in BigQuery
- A portable data stack with Dagster, Docker, DuckDB, dbt and Superset
- 📢 Why Having a Pet Project Can Supercharge Your Programming Learning Journey! 🚀🖥️
- Preparing for the AWS Certified Data Analytics - Specialty (DAS-C01) Certification
- Recursive CTEs in BigQuery
- Using BigQuery Time Travel
- Rounding Timestamps in BigQuery
- Generating a compact temporal table in BigQuery
- Joining temporal tables in BigQuery
- Filling in missing data in BigQuery