Strategies for optimizing your BigQuery queries

Did you know that optimizing SQL queries can be cost efficient? In this episode of BigQuery Spotlight, we speak to some strategies for optimizing your BigQuery queries. We’ll walk through what happens behind the scenes for more complex queries, and show you specific tactics you can use to optimize your SQL. Watch to learn some great techniques on how to make your queries more performant!

Timestamps:
0:00 - Intro
0:55 - Necessary columns only
1:26 - Prune data with partitions and clusters
1:54 - Aggregations in BigQuery
2:52 - Late aggregation
3:19 - Nest repeated data
4:00 - Shuffle Joins in BigQuery
4:27 - Broadcast Joins in BigQuery
4:55 - JOIN order
5:03 - Filter before joins
5:25 - Cluster on join keys
5:48 - WHERE clause order matters
6:03 - ORDER BY with LIMIT
6:35 - Wrap up

#BigQuerySpotlight

product: Cloud - Data Analytics - BigQuery; fullname: Leigha Jarett;

[premmohantyagi]

Perfect and easy examples to describe the impact of optimization.

[AlesNajmann]

This is the simply most important video to watch by the users of BigQuery 🔥

[divertechnology]

great video i thought it gonna be light but is very deep in knowledge

[xiaoyangshawnhuang]

thanks for the great explanation. I like "stay curious"! :)

[SasawatChanate]

5:50
Does the common practice of putting WHERE 1=1 as the first expression has any affect on this?

[michelkant]

Nice one! At how much data processing do you recommend to start working on optimizing the queries? As in when will the effect really start to show an impact? 1gb of processing, 10, 1000 or 1tb?

[jackylee]

At 5:25, why does clustering based on common join keys improve the efficiency more than partitioning?

[majorcemp]

How do you know the "where" clause is performed before the join (5:17) ? Don't you have to do a sub query on the left joined (t2) table ? (also does this sub query be less performant ?)

[Alex]

really curious why such simple examples can not be automatically handled by query optimizer (which is already done by other databases)

[jaysyanshar]

is the concept can also be applied to other SQL databases?

[gangsterrapmix]

Google Google many businesses are always liked by people all over the world Thank you Google