A curated selection of new BigQuery features, offering a high-level overview of what they are, when to use them, and when to avoid them—in easy-to-digest, bite-sized chunks with helpful links.
What is it?
History-based optimisations in BigQuery are features that automatically improve the performance of queries over time by analysing historical query patterns and usage data. These optimisations help BigQuery learn from previous queries to make future ones faster and more efficient.
Why is it useful?
These optimisations are useful because they reduce query processing time and cost by reusing information learned from previous queries. By continuously improving performance based on historical data, users get faster results, especially for repeated or similar queries, without having to manually optimise their queries.
When to use it?
History-based optimisations are automatically applied, so you benefit from them whenever you run queries, especially for datasets you query frequently. For example, if you regularly run the same sales performance report at the end of each month, BigQuery will optimise these queries over time to return results faster.
When not to use it?
There's no need to avoid using history-based optimisations, as they work automatically in the background. However, if you're running completely new or one-off queries that don't rely on past patterns, you might not see as much performance improvement from these optimisations.
Gotchas
-
Ensure you enable Query history using alter project, you can also disable
-
May of your queries will show null query optimisations, so it's worth filtering these
-
It can also be quite expensive, remember to check the bytes used before running
-
Ignore the scary warning signs - these are default and don't actually impact your project
-
You can measure the impact of the query optimisations on processing but remember some edits may not be worth it
What is it?
BigQuery now supports more flexible column naming, allowing you to name columns using your preferred international language and special characters, such as the ampersand (&) and percent sign (%). This feature is particularly useful for managing international data and handling migrations with complex schemas. Or even aligning naming conventions to your internal business taxonomy.
Why is it useful?
It improves data accessibility and usability by enabling the use of diverse naming conventions and characters, making it easier to work with data in multiple languages. This flexibility helps reduce friction when migrating datasets or integrating international business data, ensuring that column names better align with your data’s original structure.
When to use it ?
Use flexible column names when dealing with datasets that require international characters or special symbols in column names, such as a dataset with column names in Chinese (列名) or column names containing special characters like int-col. This is also useful when migrating datasets from systems with non-standard column names.
When not to use it?
Avoid using flexible column names when working with systems or tools that may not fully support special characters in column names, or when you don’t require multi-language or special character support in your datasets. Remember to also consider if translation is the right choice if you have a multilingual organisation. You cannot use flexible names on external tables at present.
