Practical SQL
Short, practical posts on SQL and BigQuery — from core language features to advanced query patterns. A reference for data practitioners at every level.
- 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 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.
- 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
- 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
- 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
- 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
- Expressing multiple repeated joins as a correlated subquery
Discover how to use correlated subqueries in SQL to optimize multiple joins and improve query efficiency
- Revisiting GROUP BY ROLLUP with a more realistic example
Learn how to effectively use GROUP BY ROLLUP to manage hierarchical data summaries in SQL, with practical examples and real-world insights
- Revisiting Why SQL’s Order of Execution Matters
Understanding SQL's execution order prevents query misjudgment: a deep dive into correct aggregation and window functions
- A closer look at STRING_AGG in BigQuery
Discover how to use BigQuery's STRING_AGG function to concat grouped values with custom separators and ordering
- Here's a great use case for GenAI writing SQL
Discover how generative AI assists in SQL writing with real use cases in data engineering, enhancing efficiency in specific tasks
- Using EXISTS with LOGICAL_OR in BigQuery
Learn to use EXISTS with LOGICAL_OR in BigQuery for efficient flag checks across orders in SQL
- Sometimes, you have to use subqueries!
Learn how to handle SQL subqueries to filter errors and construct arrays, even with NULL values in your database
- Not all NULLS are the same
Understanding how different NULLs behave in BigQuery SQL and their impact on data types
- Computing a hash aggregation in BigQuery
Learn to simulate Snowflake's HASH_AGG in BigQuery to check for data changes using existing functions
- Another look at ANY_VALUE in BigQuery
Use ANY_VALUE with HAVING MAX or MIN in BigQuery to select a row by a criterion without writing a subquery. Includes practical examples and caveats.
- Using RANGE_BUCKET in BigQuery
Learn how to use the RANGE_BUCKET function in BigQuery for effective data distribution and grouping in this concise guide
- Calculating the Median in BigQuery
BigQuery has no native MEDIAN(). Use PERCENTILE_CONT(0.5) for exact results or APPROX_QUANTILES for large datasets. Includes GROUP BY and window examples.
- Query parameters in BigQuery
Query parameters in BigQuery let you pass values into SQL safely without string concatenation, preventing injection and enabling query plan caching.
- System variables in BigQuery
BigQuery system variables like @@project_id and @@time_zone expose runtime context in SQL scripts and procedures.
- DECLARE and SET variables in BigQuery
Use DECLARE and SET in BigQuery SQL scripts to define reusable variables for incremental load watermarks, dynamic filters, and parameterized queries.
- Comments in SQL
BigQuery supports single-line comments with -- or #, and multi-line block comments with /* */. Learn the differences between inline and block comments to...
- NON-EQUI joins in SQL
Non-equi joins use comparison or BETWEEN operators instead of equality to match rows across tables in SQL. A practical pattern for range-based lookups...
- Replicating datasets across regions in BigQuery
BigQuery now supports native cross-region dataset replication, removing the need for manual Transfer Service jobs.
- NATURAL JOIN in SQL
NATURAL JOIN automatically joins tables on columns sharing the same name and datatype, with no explicit condition needed.
- Short, almost non-technical guide to SQL query tuning as a Data Engineer
A non-technical walkthrough of SQL query tuning for data engineers, covering early filtering, data reduction, partition usage, and avoiding unnecessary...
- SEMI-JOINS in SQL
A semi-join filters the left table to rows whose keys exist in the right table, without duplicating rows on multiple matches.
- Anti-joins in SQL
An anti-join uses LEFT JOIN with a WHERE IS NULL check to return rows from one table not found in another. Learn how it differs from EXCEPT and when to...
- Self-joins in SQL
Self-joins let you join a table with itself to retrieve related rows, such as resolving manager names from an employee table.
- Why basic roles in BigQuery are a bad idea
Basic IAM roles like Owner and Editor grant thousands of permissions across all BigQuery datasets, violating least privilege.
- A couple of fun things about NULL in SQL
NULL is not zero or empty string — it behaves differently in comparisons, aggregations, joins, and ORDER BY in SQL.
- FLOAT vs NUMERIC in BigQuery
FLOAT64 is fast but approximate; NUMERIC is exact at the cost of more storage. Learn when each matters in BigQuery and when rounding errors silently creep in.
- Easy with that SELECT DISTINCT!
Overusing SELECT DISTINCT masks bad joins, incorrect grain, and unhandled nested data in SQL queries. Learn a systematic debugging approach to find the...
- Here's how GROUP BY works differently across SQL dialects
Yes, BigQuery allows column aliases in GROUP BY. See how this differs from SQL Server and other dialects, with examples and edge cases.
- Joining with USING vs ON in BigQuery
Understand the difference between JOIN ON and JOIN USING in BigQuery SQL — USING is syntactic sugar for equality joins on same-named columns and...
- Another look at LOGICAL_AND & LOGICAL_OR in BigQuery
LOGICAL_AND and LOGICAL_OR in BigQuery aggregate boolean values across GROUP BY buckets or window partitions. Learn how to check if all or any rows meet a...
- The first thing I do when analyzing a SQL table
Run quick COUNT-based checks on NULL values and key columns to assess data quality before building a pipeline. A fast, repeatable SQL pattern for...
- How LIMIT helps you save time in BigQuery
LIMIT in BigQuery reduces query execution time during data validation even though it doesn't reduce bytes billed.
- Combining ANY_VALUE with HAVING in BigQuery
Combine ANY_VALUE with HAVING in BigQuery to filter aggregated groups based on the single value in a group. A practical pattern for finding orders with...
- What are GCP Cloud Workflows and how can they help you as a Data Engineer
Cloud Workflows is a serverless GCP orchestration engine that can replace Airflow for simple BigQuery pipelines.
- Using the bq CLI utility with BigQuery
The bq command-line tool lets you run queries, manage tables, load data, and view IAM policies in BigQuery without the Console.
- Why you should use parentheses with AND & OR in SQL
AND has higher operator precedence than OR in SQL, meaning mixed conditions without parentheses can produce unexpected results.
- The NOT NULL Constraint in BigQuery
BigQuery enforces NOT NULL via REQUIRED column mode. Learn how to set REQUIRED vs NULLABLE at table creation and how it interacts with INSERT and UPDATE.
- Generating a Random Number in BigQuery
RAND() in BigQuery returns a pseudo-random float between 0 and 1. Scale it for integers or use with ARRAY_LENGTH to pick a random element from an array.
- Using subqueries with Row Level Security in BigQuery
BigQuery now supports subqueries in CREATE ROW ACCESS POLICY, letting you reference a lookup table with SESSION_USER for dynamic row-level security — no...
- The order in which you ROUND matters in SQL
Rounding before vs after aggregation in SQL produces different results — learn when each approach is correct. Covers ROUND, FLOOR, and CEILING with...
- Order of precedence in SQL: WHERE vs HAVING
Understand the difference between WHERE and HAVING in SQL and why execution order matters when reusing column aliases.
- Transactions in BigQuery
Learn how BEGIN TRANSACTION and COMMIT TRANSACTION work in BigQuery for all-or-nothing multi-step operations. Any error inside the block rolls back all...
- Where does QUALIFY fit in the order of execution in BigQuery?
QUALIFY filters rows based on window function results in BigQuery, running after HAVING. Use it to deduplicate rows or keep only the latest record per group.
- Using GAPS_FILL in BigQuery
BigQuery's GAPS_FILL table-valued function fills missing intervals in DATE, DATETIME, or TIMESTAMP series without needing a date dimension or...
- Time Series functions in BigQuery
BigQuery's time series bucket functions — DATE_BUCKET, DATETIME_BUCKET, and TIMESTAMP_BUCKET — group temporal values into fixed-size intervals.
- A simple data validation scenario using FULL OUTER JOIN & ORDER BY
Use FULL OUTER JOIN combined with ORDER BY ABS to surface the largest discrepancies between two source systems in BigQuery.
- SESSION_USER in BigQuery
SESSION_USER() in BigQuery returns the email or principal of the user or service account running the current query.
- Concatenation operator in BigQuery
The || operator in BigQuery is the ANSI SQL standard concatenation operator, equivalent to CONCAT() for strings and ARRAY_CONCAT() for arrays.
- Using FORMAT_DATE in BigQuery
FORMAT_DATE in BigQuery extracts formatted date parts like abbreviated weekday names using strftime-style format elements.
- Raising ERRORS in BigQuery
BigQuery's ERROR() raises a custom message when a SQL condition fails. Use it for data quality assertions and catching unexpected values in pipelines.
- Cleaning up STRINGS in BigQuery
Clean messy string data in BigQuery using TRIM, REPLACE, UPPER, LOWER, SUBSTR, and NORMALIZE before analysis. The goal is a common denominator so matching...
- Using INSTR in BigQuery
BigQuery's INSTR function returns the 1-based index of a substring within a string, returning 0 if not found. You can also specify a start position and...
- Cross-dataset Foreign Key referencing in BigQuery
BigQuery primary and foreign key constraints require tables to be in the same dataset, but table clones offer a workaround.
- Sharded tables in BigQuery
Sharded tables in BigQuery use a name suffix pattern like table_YYYYMMDD and support wildcard queries, but carry schema and metadata overhead.
- Comparing tables with FULL OUTER JOIN
Use FULL OUTER JOIN in BigQuery to identify differences between dev and prod table versions before deploying changes.
- Splitting a STRING in BigQuery
SPLIT(string, delimiter) returns an array of substrings in BigQuery. Access elements with OFFSET, expand rows with UNNEST, or recombine with STRING_AGG.
- Extract all pattern occurrences in BigQuery
REGEXP_EXTRACT_ALL in BigQuery returns an array of all substrings matching a regular expression pattern. It supports a single capture group and pairs well...
- Why you should use UNION DISTINCT sparingly
Using UNION DISTINCT on already-distinct sources wastes slot time on unnecessary deduplication in BigQuery. Switch to UNION ALL when sources are known to...
- ORDER BY expressions in SQL
In SQL, ORDER BY accepts expressions, not just column names, letting you apply CASE WHEN logic to control sort priority.
- Boolean data type in BigQuery
BigQuery's BOOL type stores TRUE, FALSE, or NULL. Write boolean flags from comparisons, use them in WHERE clauses, and cast from INT64 or strings as needed.
- COALESCE vs IFNULL vs NULLIF in BigQuery
Learn the difference between COALESCE, IFNULL, and NULLIF in BigQuery with side-by-side examples and practical guidance on when to use each one.
- GREATEST & LEAST in BigQuery
GREATEST and LEAST compare values across multiple columns in one expression. They skip NULLs by default — learn the exact behavior and edge case handling.
- RANGE data type in BigQuery
BigQuery's RANGE data type stores time intervals in a single column instead of separate valid_from/valid_to fields.
- DELETE + INSERT vs MERGE in BigQuery
Comparing DELETE+INSERT and MERGE strategies for updating BigQuery partitioned tables. Free partition deletion makes DELETE+INSERT a cost-effective...
- Using GROUP BY ALL in BigQuery
BigQuery's GROUP BY ALL automatically groups by all non-aggregated columns in your SELECT, eliminating the need to list them manually.
- Calculating the MODE in BigQuery
No native MODE() in BigQuery. Compute the most frequent value with COUNT, GROUP BY, and QUALIFY. Covers ties, multiple modes, and window function approaches.
- Using INCLUDE NULLS with UNPIVOT in BigQuery
Learn how UNPIVOT in BigQuery drops NULL rows by default and how to retain them using the INCLUDE NULLS modifier.
- Watch out when using SAFE_CAST in BigQuery
Explains a subtle BigQuery SAFE_CAST pitfall where nanosecond-precision timestamps silently return NULL instead of raising an error.
- Generating date intervals in BigQuery
Learn how to use GENERATE_DATE_ARRAY and GENERATE_TIMESTAMP_ARRAY in BigQuery to create date or timestamp sequences at any interval.
- DATETIME vs TIMESTAMP in BigQuery
Clarifies the critical difference between DATETIME and TIMESTAMP types in BigQuery and why mixing them causes errors.
- Using COUNTIF() in BigQuery
Learn how to use COUNTIF in BigQuery to count rows matching a specific condition, as a cleaner alternative to COUNT combined with CASE WHEN.
- Approximate Aggregate Functions in BigQuery
Learn when and how to use APPROX_COUNT_DISTINCT and APPROX_TOP_COUNT in BigQuery to reduce compute cost for exploratory queries on large datasets.
- Using ANY_VALUE() in BigQUERY
Learn how to use ANY_VALUE in BigQuery, including with HAVING MAX and HAVING MIN to find top or bottom values within a group.
- Using LOGICAL_AND and LOGICAL_OR in BigQuery
Learn how to use LOGICAL_AND and LOGICAL_OR aggregate functions in BigQuery to evaluate boolean conditions across grouped rows.
- LIKE ALL and LIKE ANY in BigQuery
LIKE ANY matches if any pattern matches; LIKE ALL requires all to match. Learn BigQuery's quantified LIKE operators with practical SQL examples and edge cases.
- Does order of expressions in the WHERE clause matter?
Investigates whether the order of filter expressions in a BigQuery WHERE clause impacts query performance, based on a 200M row public dataset test.
- Using SELECT * with EXCEPT and REPLACE
Use SELECT * EXCEPT and REPLACE in BigQuery to exclude or transform columns without rewriting the full select list. Includes practical examples.
- Using Correlated Subqueries in BigQuery
Fix BigQuery's 'correlated subqueries that reference other tables are not supported' error by rewriting them as joins, window functions or LEFT JOIN UNNEST.
- Intersect and Except in BigQuery
INTERSECT DISTINCT finds common rows; EXCEPT DISTINCT finds rows missing from one side. Use them in BigQuery for data validation, deduplication, and diffing.
- Picking between CTE, View and Temp Table in BigQuery
A practical decision framework for choosing between CTEs, views, temp tables, materialized views, and staging tables in BigQuery.
- Using BigQuery hashing functions
Use FARM_FINGERPRINT in BigQuery for surrogate keys and change detection. Covers MD5, SHA256, and how to pick the right hash function for MERGE conditions.
- Using Dynamic SQL in BigQuery
EXECUTE IMMEDIATE in BigQuery runs a dynamically built SQL string at runtime. Use it to generate UNPIVOT queries from metadata, loop over values, or run DDL.
- Write better SQL queries faster using Mock Data
A practical SQL development technique: prototype complex BigQuery queries using inline CTE mock data to iterate quickly, cover edge cases, and reduce...
- The power of BigQuery INFORMATION_SCHEMA views
Ready-to-run BigQuery INFORMATION_SCHEMA queries for jobs, columns, constraints and table storage costs. Practical examples you can copy and adapt.
- MERGE ON FALSE in BigQuery
Learn how the ON FALSE clause in a BigQuery MERGE statement performs an atomic delete-then-insert (REPLACE) operation, and how pairing it with key...
- BigQuery Primary Key & Foreign Key constraints
BigQuery's unenforced primary and foreign key constraints are not just metadata — this post tests their actual impact on join optimization with real query...
- Using MAX_BY / MIN_BY in BigQuery
Discover MAX_BY and MIN_BY in BigQuery SQL, compact shortcuts for ANY_VALUE with HAVING MAX/MIN, and see exactly how to retrieve a column value based on...
- Using ROLLUP in BigQuery
Learn how GROUP BY ROLLUP works in BigQuery SQL to compute hierarchical subtotals and grand totals in a single query, with column order explained through...
- Recursive CTEs in BigQuery
BigQuery recursive CTEs use WITH RECURSIVE to self-reference. Use them for hierarchies, date sequences, and graph traversal — with syntax and worked examples.
- Rounding Timestamps in BigQuery
Round timestamps in BigQuery with TIMESTAMP_TRUNC and DATE_TRUNC. Covers hour, day, week, month granularity, time zone handling, and interval arithmetic.
- Generating a compact temporal table in BigQuery
Learn how to deduplicate and compact redundant SCD-2 rows in BigQuery using FARM_FINGERPRINT, LEAD, and QUALIFY to produce a clean valid_from/valid_to...
- Joining temporal tables in BigQuery
Learn how to join multiple temporal tables in BigQuery with SQL examples.
- Filling in missing data in BigQuery
Fill gaps in BigQuery time-series using LAG, LAST_VALUE IGNORE NULLS, UNPIVOT, and cross joins. Step-by-step examples with real datasets and edge cases.
- 9 tips on writing cleaner SQL
This article provides nine tips for writing cleaner SQL queries. The tips include formatting queries, using meaningful aliases, avoiding SELECT *, using com