Running on BigQuery?

BigQuery scanning too much or slots exhausted? We ship it properly.

A dashboard that used to answer in two seconds now spins for forty, the on-demand bill jumped twice in a row, and someone hid a SELECT * in a materialized view. We open the INFORMATION_SCHEMA jobs tables, find the queries that are scanning the terabytes, and quietly bring both the slot graph and the invoice back to something defensible.

  • Since 2015Fixing technology
  • FreeEstimates
  • US teamReal engineers

bigquery-jobs.log

still failing

What it is

Google BigQuery at a glance

BigQuery is Google Cloud's serverless data warehouse. Storage lives in Google's distributed file system, compute runs on a shared pool of units called slots, and SQL queries are planned and distributed across those slots without a warehouse for you to size. Pricing comes in two main shapes: on-demand, where you pay per terabyte of data scanned, and editions with capacity reservations, where you commit to a certain number of slots and queries draw from that pool.

The product has been around since 2010 and sits underneath a lot of marketing analytics, product data pipelines and ML workloads inside Google Cloud. It speaks standard SQL with extensions for geospatial, ML inside the warehouse through BQML, and streaming ingestion through the Storage Write API. The main places people get hurt are missing partition or cluster filters, materialized views that silently fall back to base tables, and reservation sizing that does not match when the workloads actually run.

Type
Serverless cloud data warehouse
First released
2010 preview, 2012 general availability
License
Proprietary SaaS on Google Cloud
Maintained by
Google Cloud, Alphabet Inc.
Typically used for
Analytics, marketing and product data, ML on structured data, Looker back ends, event pipelines

The honest version

Google BigQuery pros and cons

No technology is all upside. This is where Google BigQuery earns its place and where it tends to cause trouble.

Where Google BigQuery is strong

  • No warehouse to size or wakeQueries run on a managed slot pool, so there is nothing to spin up and nothing to remember to suspend. Teams with sporadic workloads save a lot of operational attention.
  • Scales straight to petabytesA single query across tens of terabytes is routine rather than scary, assuming it is written to prune partitions and clusters.
  • Tight integration with the Google stackGCS, Pub/Sub, Dataflow, Looker, Vertex AI and Google Ads data transfer all plug in without custom glue, which keeps pipelines short for teams already on GCP.
  • BQML brings models into SQLYou can train and run logistic regression, forecasting and some neural network models from a SELECT, which is enough for a lot of analytics-adjacent ML.
  • Streaming and batch share one tableThe Storage Write API lets you ingest in near real time while batch loads run against the same table, with consistent reads.
  • Free tier that actually covers prototypesThe first terabyte scanned per month and ten gigabytes of storage are free, which is enough to prove out an idea before anything hits the bill.

Where Google BigQuery bites

  • On-demand pricing punishes SELECT *Every column referenced is billed by compressed bytes scanned. One lazy query against a wide fact table can cost more than the rest of the day's analytics.
  • Reservations are easy to misconfigureCommit too few slots and analysts queue for minutes; commit too many and the floor payment dwarfs real usage. Few teams get the mix right without measuring.
  • Partition and cluster filters are mandatory in practiceNothing stops you writing a query that scans the full table, but your invoice will not let you forget it. BI tools that generate SQL need careful review.
  • Streaming inserts have a separate price and quotasThe legacy streaming insert API meters per row and runs into per-table quotas that batch loads do not, which surprises teams moving from batch to realtime.
  • Soft lock-in to the Google stackBigQuery UDFs, scripting, authorized views and BQML are productive and rarely portable. Leaving the platform usually means rewriting, not exporting.
  • Diagnosing slow queries needs the right viewsThe INFORMATION_SCHEMA jobs and job stages views hold the real story, and most teams never look at them until a bill forces it.

Sound familiar?

Common Google BigQuery problems we fix

The Google BigQuery problems that bring people to us, and what you have once each one is fixed.

Query scanned 2.8 TB, estimated cost $14

On-demand bytes scanned keep climbing

A dashboard that was fine on small tables, now querying a fact table that has grown by an order of magnitude, with no partition filter in the where clause and no clustering to help.

Scans measured in gigabytes, not terabytes

Partition filter missing on events_raw

A Looker tile scans the whole table

Explore derived tables that do not apply the partition filter, or LookML that references events_raw without a date range, so every refresh reads every day ever loaded.

Partitions enforced, refreshes cheap

Slot utilization 98% sustained

The reservation is saturating during business hours

A workload that used to fit now queues behind long-running transforms, and analysts are waiting on short queries because a batch job is holding the pool.

Reservations sized and workloads separated

Resources exceeded during query execution

Big queries run out of resources mid-flight

A query with enormous shuffles, a window function without partitioning, or an ARRAY_AGG building a giant structure that will not fit on a single worker.

Shuffles reshaped, query fits the engine

Billing alert: daily cap exceeded

The on-demand cap kept firing and work stopped

A per-project cap set at a safe number for last year is now cutting off real workloads during month-end reporting.

Caps and reservations chosen for current reality

Materialized view refresh failed

Materialized views quietly fall back to base tables

A base table was repartitioned or altered in a way that invalidates the MV, so every query against it now scans the full table instead of the view.

Views healthy, base tables kept compatible

Streaming insert errors: invalid row

Streaming inserts are dropping rows

Schema drift at the source, a JSON field that stopped matching the column type, or a row exceeding the streaming insert row size limit.

Pipeline resilient, dead letters captured

18.4 TB dry run for a single tile

A BI tool is generating very expensive SQL

Looker, Tableau or a notebook calling SELECT * against a wide, deeply nested table, then filtering in the tool rather than in SQL.

SQL pushed down, columns selected deliberately

What we do

BigQuery help, from bill audit to day-to-day support

Call us for one runaway query, or let us audit the whole project.

Cost audit
A deep look at INFORMATION_SCHEMA, the jobs that scanned the most bytes, and the reservations that cost the most slots. You get a ranked list of fixes with dollar amounts attached.
Query and schema tuning
Partitioning, clustering, materialized views and shuffle-aware rewrites applied to the queries that matter most, with measured before and after.
Reservations and editions
On-demand versus Standard, Enterprise or Enterprise Plus editions chosen against actual workload shape, with autoscaling policies set deliberately.
dbt and pipelines
dbt and Dataflow pipelines that are slow, fragile or overpriced, rebuilt around incremental models and sensible partitions.
BI cleanup
Looker, Tableau and Mode projects that generate expensive SQL, tidied so the warehouse does the filtering rather than the dashboard.
Managed Tech
Monthly review of slot usage and bytes scanned, alerts set against reality, and a team to call when a quota or a reservation decision needs making.

How it works

From first message to fixed

  1. Look Free estimate

    Tell us what's going wrong. We look at it, then tell you plainly what's wrong and what it will cost.

  2. Fix

    We fix the cause, not the symptom. If something is down, getting it back up comes first.

  3. Harden

    Backups, monitoring, updates and access, so the same problem can't quietly come back.

  4. Hand back or stay on

    You get a plain-language write-up. Or we stay on for hosting, upkeep and new work.

Often found together

Running Google BigQuery alongside something else?

We work on the whole stack, so the problem can be wherever it actually is.

Questions

Google BigQuery questions

Something else? Call+1 737 727 7327

Why did our BigQuery bill jump this month?

In most cases the culprit is a dashboard refresh or a scheduled query scanning far more bytes than it used to, because an underlying table grew and the partition filter was never added. We open the jobs view, rank the heavy queries by bytes billed, and show you which ones account for the increase. Fixing the top three or four usually brings the invoice back under control.

On-demand or reservations: which should we use for BigQuery?

On-demand suits bursty workloads where usage is unpredictable and most queries are cheap. Reservations, under one of the editions, make sense once the monthly bytes-scanned bill passes roughly the cost of a baseline slot commitment, or when you need predictable concurrency for BI. We model your actual job history before recommending either.

Can you tune a slow BigQuery query without just scanning more slots at it?

Yes, and that is normally where the biggest wins live. The query stages view tells you whether time is going to shuffle, reads, compute or waiting for slots. Most slow queries we see need a partition filter, a clustering choice revisited, a window function partitioned, or a join rewritten to avoid a cross-worker explosion. Throwing more slots at a bad plan rarely ends well.

Can you take over a BigQuery project we inherited?

Yes. Inherited projects are a common part of this work. We inventory the datasets, scheduled queries, data transfers and external services, review IAM, document what each pipeline does and does not do, and bring the configuration back into Terraform or dbt so the next person has a map.

How do we stop expensive BI queries from hitting the warehouse?

By pushing filters down into SQL rather than doing them in the tool, by replacing SELECT * with explicit columns, and by using authorized views or materialized views for the heavy aggregates. We review the LookML or the Tableau datasources, flag the tiles that scan the most, and rewrite the worst offenders.

How much does BigQuery help cost?

The quote is free. Grant us read access to the project or send a month of job history, and we return a short price with a prioritized list of the queries and reservations worth touching first.

Free estimate

BigQuery costs or slots worrying you?

Tell us what you're running and what's going wrong. A person reads it and comes back with an estimate.

  • No charge to find out what it will take
  • If it's urgent, say so

Replies come from a person. By sending, you authorize us to email you back. No marketing.