SQL Optimization

Hints for SQL Optimization #

Reviewing dbt and SQL pull requests almost every day, I am collecting some hands on SQL recommendations especially for BigQuery and DuckDB. There are some great resources on SQL Optimization out there like https://modern-sql.com/ and I am not trying to replicate those. This is a collection of hints that I have find to be frequently overlooked and have impact at scale.

Transformations in the Where Clause #

Move static operations, such as adding intervals or performing constant calculations, to the static/constant side of the where clause.

SQL Pitfalls

SQL Pitfalls #

Here I am collecting some pitfalls, that I either fell into myself or that I found interesting when I saw them in a pull request or elsewhere.

SUM with NULL Values #

When performing arithmetic operations with SUM in SQL, the handling of NULL values can lead to unexpected results. This is particularly noticeable when summing columns separately versus summing their combined values directly.

Consider this example: