> For the complete documentation index, see [llms.txt](https://data-engineering-zoomcamp-2025-t.gitbook.io/tinker0425/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://data-engineering-zoomcamp-2025-t.gitbook.io/tinker0425/module-3/3.2-bigquery-internals-and-best-practices/3.2.1-bigquery-best-practices.md). # 3.2.1 - BigQuery Best Practices Last updated Feb 5, 2025 :clock4: *Estimated time spent on this lesson | \~15 min* Youtube Video | \~4 min {% embed url="" %} {% endembed %} :writing\_hand: In this video we will learn the do's and don'ts of using BigQuery ## BigQuery Best Practices ### :moneybag: **For** **Cost** **reduction, be sure to:** * [x] :warning: Avoid SELECT \* | :ballot\_box\_with\_check: instead specify the name of the columns * [x] :money\_mouth: Price your queries before running them, which is given in bytes to be processed in the upper right corner of the query screen * [x] :notebook: Use clustered or partitioned tables * [x] :exclamation:Be cautious with streaming inserts as they can increase your costs drastically * [x] :zap: Materialize query results in stages, which allows BigQuery to allocate resources more efficiently. Only the necessary compute and storage resources are used at each stage of the query execution, reducing overall resource consumption. ### :moneybag: **For** Query performance be sure to**:** * [x] :put\_litter\_in\_its\_place: Filter on partitioned or clustered columns * [x] :file\_cabinet: Denormalizing your data, which is a database design technique where redundant copies of data from one or more normalized tables are combined and stored together in a single table. Retrieving data from a single denormalized table is typically faster than joining multiple normalized tables. * [x] :recycle: Use nested or repeated columns to denormalize your tables * [x] :exclamation:Use external data sources appropriately, as overuse could introduce latency and impact query performance. If too many of these sources live on GCP, you may run out of space. * [x] :arrow\_down: Reduce data before using a JOIN, by a filter or aggregate the data.. This method should improve query performance and reduce resource usage by eliminating unnecessary data from the tables before executing the join. * [x] :x: NOT treat `WITH` clauses as prepared statements, because `WITH` clauses are primarily for readablity and not materialized. For example, placing all your queries in `WITH` clauses and then running `UNION ALL` is a misuse of the `WITH` clause. If a query appears in more than one `WITH` clause, it executes in each clause. * [x] :thumbsup: Avoid oversharding tables. Sharding a table involves partitioning the data into smaller subsets or shards and distributing them across multiple storage locations or nodes to improve scalability, performance, and availability in distributed database systems. (Partitioning as we have already discussed is not a form of sharding.) But if you overshard, that means you create too many partitions and may incur a performance drop. Sometimes the complexity and overhead of sharding outweigh the potential performance benefits, especially in distributed systems with a large number of shards. * [x] :x: Avoid JavaScript user-defined functions | :heavy\_check\_mark: Use native UDFs instead * [x] :heavy\_check\_mark: Use approximate aggregation functions (HyperLogLog++) * [x] :file\_folder: do ORDER BY last, for query operations to maximize performance * [x] :blue\_circle: Optimize your JOIN patterns by placing the largest table first, then the smallest, and then order the rest of the tables (if any) by decreasing size. However, it's best to experiment with the order of the tables before executing the query, using the price given in bytes processed in the upper right of the query view. {% embed url="" %} {% endembed %} --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://data-engineering-zoomcamp-2025-t.gitbook.io/tinker0425/module-3/3.2-bigquery-internals-and-best-practices/3.2.1-bigquery-best-practices.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.