BigQuery Best Practices: Complete Guide for Data Teams That Care About Cost and Performance

By Amir Peres
May 26, 2026 | 5 min read

Most BigQuery best practice guides stop at “avoid SELECT *.” That’s fine advice for a junior analyst, but not especially useful for teams running hundreds of concurrent dbt jobs, managing a sprawling reservation strategy, or trying to figure out why your BigQuery bill spiked 40% last month with no clear explanation. 

This guide goes deeper. We’ll cover the fundamentals – because they do matter – but we’ll spend time on the stuff that actually moves the needle for engineering teams at scale: 

  • Never use SELECT *
  • Partition your tables correctly
  • Cluster on the columns you actually filter and join on
  • Use materialized views for repeated aggregations
  • Optimize your JOIN patterns
  • Understand the on-demand vs. reservations decision
  • Design your reservation architecture thoughtfully 
  • Leverage INFORMATION_SCHEMA for continuous cost visibility
  • Use query labels to track costs by team, pipeline, and project 
  • Choose the right storage billing model
  • Manage AI and exploratory query costs proactively 
  • Invest in autonomous optimization 

By the time you finish reading, you’ll have a clearer picture of why BigQuery quietly burns money – and how to get your monthly budget under control. 

Best Practice #1: Never Use SELECT * 

This is table stakes, but still worth explaining properly because the reason matters. 

BigQuery is a columnar database. That means when you run a query, you’re billed based on the total bytes scanned across the columns you select, not the rows returned. So if you run SELECT * against a 50-column, 10TB table, you don’t just scan the rows you care about, but all 50 columns, even if your WHERE clause filters down to 200 rows. 

The fix is easy enough: 

-- Expensive: scans all columns
SELECT *
FROM `project.dataset.events`
WHERE event_date = '2025-01-01';

-- Efficient: scans only what you need
SELECT user_id, event_type, session_id
FROM `project.dataset.events`
WHERE event_date = '2025-01-01';

If you’re on on-demand pricing at $6.25/TiB, the difference between selecting three columns versus 50 can be enormous at scale. In comparison with capacity pricing, you’re not billed per byte, but you’re still consuming slots, which means slower queries and more contention across your team.

Note: WITH clauses (CTEs) are evaluated for readability, not performance. BigQuery’s query optimizer may evaluate a CTE multiple times depending on how it’s used. If your team is referencing a heavy subquery multiple times, materialize it instead. 

Best Practice #2: Partition Your Tables Correctly

Partitioning is one of the highest-impact optimizations available in BigQuery. When done correctly, it can massively reduce bytes scanned. Done incorrectly – or ignored – it’s invisible money burning every time a query runs. 

BigQuery supports partitioning by: 

  • Ingestion time
  • DATE/TIMESTAMP/DATETIME column
  • Integer range

For most analytical workloads, date-based partitioning on a meaningful timestamp column is the best default. 

CREATE OR REPLACE TABLE `project.dataset.transactions`
(
  transaction_id STRING,
  user_id STRING,
  amount NUMERIC,
  created_at TIMESTAMP
)
PARTITION BY DATE(created_at)
OPTIONS (
  partition_expiration_days = 365
);

Pro tip: You must filter on the partition column to get partition pruning. If you filter on a derived column or a column that isn’t the partition key, BigQuery will scan the entire table. 

-- This prunes partitions correctly
SELECT * FROM `project.dataset.transactions`
WHERE created_at >= TIMESTAMP('2025-01-01')
AND created_at < TIMESTAMP('2025-01-02')

-- This does NOT prune - full scan
SELECT * FROM `project.dataset.transactions`
WHERE EXTRACT(YEAR FROM created_at) = 2025;

Note: Date-named table sharding is an antipattern. BigQuery has to maintain separate schema metadata for each table, verify permissions individually, and can’t apply partition pruning across them. If you’re still using date-sharded tables, migrating to time-partitioned tables should be near the top of your backlog. 

Best Practice #3: Cluster on the Columns You Actually Filter and Join On

Clustering works alongside partitioning by physically sorting data within each partition based on one or more column values. When your queries filter or join on those columns, BigQuery can skip entire blocks of data it doesn’t need to read. 

CREATE OR REPLACE TABLE `project.dataset.events`

(

  event_id STRING,

  user_id STRING,

  event_type STRING,

  platform STRING,

  event_date DATE

)

PARTITION BY event_date

CLUSTER BY user_id, event_type, platform;

The right clustering columns are the ones that appear most frequently in your WHERE, GROUP BY, and JOIN clauses. BigQuery supports up to four clustering columns. Order matters – put the highest-cardinality, most-filtered columns first. 

That combination of partitioning and clustering is where you see real wins. A well-designed table partitioned by date and clustered by user segment can reduce bytes scanned by an enormous amount. 

Note: Clustering benefits are most visible when you’re filtering on the cluster columns. If your query doesn’t filter on any clustered column, clustering provides no scan benefit. Make sure your cluster choices reflect actual query patterns, not theoretical ones. 

Best Practice #4: Use Materialized Views for Repeated Aggregations

If the same aggregation runs dozens or hundreds of times a day across your team’s dashboards and reports, you’re paying for that compute over and over again. Materialized views let you pre-compute and cache query results so that subsequent queries can read the cached output instead of re-executing the full computation against base tables. 

CREATE MATERIALIZED VIEW `project.dataset.daily_revenue_mv`
AS
SELECT
  DATE(created_at) AS date,
  product_category,
  SUM(amount) AS total_revenue,
  COUNT(*) AS transaction_count
FROM `project.dataset.transactions`
GROUP BY 1, 2;

BigQuery automatically refreshes materialized views when the base table changes. Queries against the base table can also automatically benefit from the materialized view if BigQuery’s optimizer determines the cached result is cheaper to use. 

This is particularly valuable for: 

  • BI tool queries
  • Recurring dashboard loads
  • Any pattern where the same aggregation is repeated across different sessions or users

The big tradeoff here is storage cost and refresh overhead. Focus materialized views on your most expensive, most frequently run queries – not everything. 

Best Practice #5: Optimize Your JOIN Patterns

JOINs are one of the most common sources of performance problems in queries. The two big issues here are join order and data skew: 

  • Join order: BigQuery’s query optimizer often reorders joins automatically, so manual join ordering matters less than it does in traditional databases. Your bigger focus should be reducing unnecessary joins, filtering data earlier, and addressing skewed join keys. 
  • Skewed joins: If your join key has a highly uneven distribution, BigQuery sends a disproportionate amount of work to a small number of slots. This means some slots finish quickly while others are left processing massive amounts of data. 

There are two ways to address this: 

  • Salting: Add a random suffix to the key on both sides to distribute the load across more slots, then aggregate after the join. 
  • Avoid cross joins: Unless you explicitly need a Cartesian product. Otherwise, unintentional cross joins are often due to unequal join conditions or missing join predicates on partitioned columns – and they can silently produce billions of costly rows. 

Best Practice #6: Understand the On-Demand vs. Reservations Decision

BigQuery offers two pricing models for compute: 

  • On-demand: You typically pay per TiB of data processed (pricing varies by region and may change over time). You get up to 2,000 concurrent slots per project
  • Capacity (Reservations): You pay per slot-hour. You control how much compute is available, with optional commitments at discounted rates. 

The choice here is rarely clear – and it’s not permanent. For small teams with low, unpredictable query volumes, on-demand is often fine. As query volume grows and workloads become more predictable, capacity pricing typically becomes significantly cheaper. 

The issue is that many teams treat this as a one-time decision and then leave it alone. In reality, the most effective strategy is a hybrid approach. You should run some jobs on reservations and routing and others on demand depending on their cost profile under each model. 

Best Practice #7: Design Your Reservation Architecture Thoughtfully

If you’re using capacity pricing, how you structure reservations has a major impact on both cost and performance. The default approach of one reservation for everything only works at a small scale. 

A more effective pattern is to separate reservations by workload type: 

  • prod-critical: ETL pipelines, SLA-bound workloads
  • analytics: ad hoc queries, dashboards, BI tools
  • dev-test: development, CI, exploratory work

Each reservation gets an appropriate slot allocation. Production workloads get priority and a guaranteed slot floor, while development workloads get capped so they can’t cannibalize production slots during a big exploratory query. 

Here’s what your team should do to set this up correctly: 

  • Baseline slots should reflect predictable workload demand. Teams with highly variable workloads may benefit from lower baselines and more autoscaling flexibility, while stable production workloads often justify committed baseline capacity. 
  • Set a realistic max slots: The max slot value determines how aggressively BigQuery autoscales your reservation. Setting it too low caps performance during peak periods. Setting it too high means BigQuery may scale up and bill you for a full 60-second minimum window for even short bursts.  
  • Use idle slot sharing carefully: Idle slots from one reservation can spill to another if ignore_idle_slots is set to false. This can be great for efficiency, but it means your dev workloads can consume production idle capacity and introduce unpredictability. 

Best Practice #8: Leverage INFORMATION_SCHEMA for Continuous Cost Visibility

You can’t organize what you can’t see. BigQuery exposes rich metadata through INFORMATION_SCHEMA views, and building regular queries against these should be part of your data team’s standard operating practice. 

-- Find the most expensive queries in the last 7 days
SELECT
  job_id,
  user_email,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd,
  total_slot_ms,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 20;
-- Identify users generating the most bytes processed
SELECT
  user_email,
  COUNT(*) AS query_count,
  SUM(total_bytes_processed) AS total_bytes,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
GROUP BY 1
ORDER BY total_bytes DESC
LIMIT 20;

Run these regularly and you’ll surface your main query cost drivers. 

Best Practice #9: Use Query Labels to Track Cost by Team, Pipeline, and Project

Once you start looking at INFORMATION_SCHEMA data seriously, you’ll want to slice costs by team, pipeline, or use case. The best way to get that isn’t through default metadata but query labels. 

Labels are key-value pairs you attach to query jobs. They show up in INFORMATION_SCHEMA and in your Cloud Billing export, allowing for real chargeback and cost attribution. Query labels are typically applied through these methods instead of directly in SQL queries: 

  • Job configurations
  • dbt settings
  • Orchestration tools
  • BigQuery APIs 

For dbt users, you can set labels in your BigQuery connection configurations so every dbt-generated query is automatically tagged: 

# profiles.yml
my_project:
  target: prod
  outputs:
    prod:
      type: bigquery
      project: my-gcp-project
      dataset: dbt_prod
      job_labels:
        team: data-engineering
        orchestrator: dbt
        environment: production

Best Practice #10: Choose the Right Storage Billing Model

BigQuery offers two storage billing models: logical and physical. Most teams end up logical by default and never revisit that decision. 

  • Logical billing: Charges based on the uncompressed size of your data. It’s the simpler model and doesn’t charge for Time Travel or Fail-Safe storage. 
  • Physical billing: Charges based on the actual compressed bytes stored on disk. If your data compresses well, you should see significant storage cost reductions by switching to physical billing. The tradeoff is that Time Travel and Fail-Safe bytes do count toward your bill under physical billing. 

For tables with long retention requirements and high compressibility, physical billing is often cheaper. For tables with lots of Time Travel activity or sparse, poorly compressing data, logical billing is usually the better choice. 

Make sure to check your current storage usage before making the switch: 

SELECT

  table_id,

  ROUND(size_bytes / POW(1024, 3), 2) AS size_gb,

  ROUND(active_logical_bytes / POW(1024, 3), 2) AS active_logical_gb,

  ROUND(long_term_logical_bytes / POW(1024, 3), 2) AS long_term_logical_gb

FROM `project.dataset.__TABLES__`

ORDER BY size_bytes DESC;

Best Practice #11: Manage AI and Exploratory Query Costs Proactively 

AI-driven workloads running large exploratory queries, ML feature pipelines, or LLM-adjacent data processing tend to spike compute consumption in ways that don’t fit into your normal reservation strategy. 

There are a few practices that can help with this: 

  • Set custom quotas per user or project: Use BigQuery quotas, budgets, IAM controls, and reservation isolation strategies to prevent exploratory workloads from consuming disproportinate compute resources. This prevents any single query or user from consuming an outsized share of your capacity. 
  • Use separate reservations for exploratory workloads with a hard slot cap. This keeps AI and data science queries from competing with production ETL. 
  • Enable query dry runs: Use this in your data science workflows so analysts can see estimated bytes before executing expensive scans. 

Best Practice #12: Invest in Autonomous Optimization

Implementing all of these practices may seem straightforward, but maintaining them at scale across dozens of pipelines, multiple teams, shifting workload patterns, and the constant push-pull between on-demand and reservation pricing is far from easy. 

Most data teams don’t have the bandwidth to continuously audit reservation sizing, slot utilization, route individual jobs to their optimal billing model, and tune clustering columns as query patterns evolve. These BigQuery optimizations get done during a quarterly review – and when the next shocking bill arrives. 

That’s the problem Yuki was built to solve. 

Yuki acts as an intelligent optimization layer between your BigQuery workloads and Google’s compute infrastructure. It analyzes every query job in real time and automatically routes each one to the most efficient billing option based on the actual cost of that query. No code changes, query rewrites, or infrastructure migrations required. 

The manual practices in this guide are worth implementing. But if you want to close the gap between “we followed the BigQuery documentation” and “we actually optimized our compute spend,” the answer is autonomous real-time routing – not quarterly tuning sessions. 

Start Cutting Your BigQuery Costs Today

If your team is still relying on manual optimization cycles to keep BigQuery costs in check, you’re leaving money on the table daily. The workloads that should be running on reservations aren’t. The idle capacity you’re paying for isn’t reclaimed. And the next billing spike – from a runaway query, a new AI pipeline, or an analyst who didn’t run a dry run – is already on its way. 

Yuki gives data teams a better path: autonomous, real-time BigQuery cost optimization with no code changes, no query rewrites, no engineering overhead. 

See how Yuki optimizes BigQuery and request a free demo to see exactly where your current spend is going. 

By Amir Peres
Amir Peres is CTO and Co-Founder of Yuki, where he drives technical vision for automated Snowflake cost optimization. With 12+ years in data architecture, ML, and large-scale infrastructure, he previously led engineering at Lightico (building GDPR-compliant multi-region data lakes) and Payoneer (ML product development). Amir specializes in scalable, secure, cost-efficient data systems that maximize ROI while reducing manual effort. He has presented at Data TLV Summit 2025 and appeared on the Jon Myer podcast. Find more of his insights on LinkedIn.

Table of Contents

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Follow us on LinkedIn

Related posts

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Skip to content