BigQuery Partners

On-demand and capacity pricing solve different problems

BigQuery bills compute two ways. On-demand charges you for the bytes each query scans, so cost tracks table size and how carelessly the SQL is written. Capacity pricing charges for slots instead — a slot is a unit of query compute — bought by the hour, with autoscaling and optional one-year or three-year commitments at a lower rate. On-demand suits unpredictable, low-volume use and needs no capacity planning. Capacity suits steady, high-throughput workloads and converts a variable bill into something closer to a fixed one. Neither is inherently cheaper. The answer depends entirely on the shape of your workload across a month, not on a vendor rule of thumb.

Good delivery work models both against your own query history rather than an estimate. Every job BigQuery runs is recorded in the INFORMATION_SCHEMA views, including bytes scanned, slot-milliseconds consumed and who ran it. A few days of that data tells you your peak concurrency, your idle hours and which handful of queries account for most of the spend. That analysis should come before anyone buys a commitment, and it should be repeatable, because workloads change when new teams onboard. Commitments are also the reversible part of the decision: you can move a project between on-demand and a reservation far more easily than you can restructure tables after the fact.

Guardrails that stop one query costing four figures

• Maximum bytes billed — set a per-query ceiling at the project or job level so a query that would scan the whole estate fails immediately instead of completing and appearing on the bill.
• Custom quotas — cap query usage per user per day and per project per day, which turns an expensive mistake into an error message rather than an incident review.
• Workload isolation — put scheduled loads, BI traffic and ad-hoc analysis in separate reservations so an analyst exploring a raw table cannot starve the pipeline that feeds tomorrow's reporting.
• Required partition filters — mark large tables so that any query omitting a partition predicate is rejected, which prevents accidental full-table scans at source.
• Spend visibility analysts can see — a dashboard built on the jobs metadata showing the twenty most expensive queries of the week, attributed to a person or a service account, changes behavior faster than a policy document.

Partitioning and clustering do different jobs

Partitioning splits a table into segments, normally by a date or timestamp column, sometimes by ingestion time or an integer range. When a query filters on the partitioning column, BigQuery reads only the relevant segments and you are billed only for those. You get one partitioning column per table, so choosing it is a real decision: partition by the date the business filters on, which is often the event date rather than the load date. Get this wrong and every downstream query pays for it permanently, because changing the partitioning column means rebuilding the table and re-pointing everything that reads from it.

Clustering sorts the data inside each partition by up to four columns, in the order you specify, and that order matters because the sort is hierarchical. It is the right tool for high-cardinality fields that appear in filters and joins — customer ID, product SKU, session ID — where partitioning would produce far too many segments. Most large tables want both: partitioned by date, clustered by the one or two identifiers people actually filter on. The way to verify any of this is a dry run, which reports estimated bytes scanned without executing the query. A partner should be able to show you before and after figures for your real reporting queries.

Layout choices that show up on the invoice

• Storage billing model — each dataset can be billed on logical or physical bytes; physical billing is cheaper for well-compressed data and more expensive for poorly compressing data, so the choice should follow a measurement, not a default.
• Long-term storage behavior — a partition untouched for ninety days drops to a lower storage rate, which is quietly undone by pipelines that rewrite whole tables nightly instead of writing incrementally.
• Nested and repeated fields — storing order lines inside the order record avoids a join and often reduces scanned bytes, at the cost of SQL that less experienced analysts find harder to read.
• Materialized views versus scheduled aggregates — materialized views refresh incrementally and are used automatically by the optimiser; a scheduled aggregate table costs a full rebuild but gives you control over timing and shape.
• BI Engine reservation — an in-memory layer sized for dashboard queries, which can cut both latency and scanned bytes for the repetitive queries a BI tool generates.
• Column discipline — billing is columnar, so selecting every column from a wide table is not a style issue, it is a direct multiplier on cost.

Turning raw exports into tables analysts will trust

Raw data landing in BigQuery is not a data model. Ads exports, product events and application replicas arrive with vendor-specific naming, duplicate rows, late-arriving corrections and their own idea of what a customer is. The work that makes BigQuery useful is layering: an immutable raw zone nobody queries directly, a staging layer that cleans types and names, and a modeled layer of business tables with a declared grain. Grain means what one row represents — one order, one order line, one customer-day. Writing that down for every published table removes most of the ambiguity that produces three different revenue numbers in three different dashboards.

Trust comes from tests running on every build, not from documentation written once. The useful set is small and specific: the primary key is unique and not null, foreign keys resolve, categorical columns contain only expected values, row counts sit within a sane range, and the freshness of the newest partition is inside an agreed window. Each published table should have exactly one owner and one definition, and derived tables should be built from the modeled layer rather than from raw, so a fix applied once propagates. This is the difference between a warehouse analysts query and a warehouse they quietly work around by exporting to spreadsheets.

Transformation and orchestration expectations

• A deliberate transformation tool — dbt or Dataform, chosen for a stated reason, with all model code in version control rather than scheduled queries saved in the console.
• Incremental models with a stated late-data policy — how many days of history each incremental build reprocesses, so corrections that arrive three days late are not silently lost.
• Dependency-aware orchestration — Cloud Composer, Workflows or the transformation tool's own scheduler, where a failed upstream step stops downstream builds instead of publishing partial data.
• Column-level lineage analysts can open — the ability to trace a field on a dashboard back through the models to the source column, without reading the SQL.
• Tests running on change, not just on schedule — a continuous integration step that builds and tests changed models against a sandbox dataset before they reach production.
• A rehearsed backfill procedure — the exact steps and expected cost to rebuild a year of a large table, tested once rather than improvised during an incident.

Access design is a deliverable, not an afterthought

How you split projects and datasets is the access model, the cost model and the blast radius all at once. A workable default separates raw, modeled and consumption layers into their own datasets, with raw readable by the pipelines and a small group of engineers rather than by everyone with a BI login. Authorized views and authorized datasets are the mechanism that lets a consumer query a curated result without holding read access to the underlying tables, which is what stops teams from solving access problems by copying data into a second table nobody governs.

Below that sit the finer controls. Column-level security uses policy tags to restrict named fields — email address, date of birth, card fragments — to specific groups, so a table can be broadly available while sensitive columns are not. Row-level security filters what a given group sees within the same table, which is how one regional table serves several regional teams. The same boundaries should carry your cost attribution: labels on datasets and jobs, and reservations aligned to teams, so that when the bill moves you can say which workload moved it without a forensic exercise.

Boundary questions to settle before data lands

• Where raw personal data lands — which dataset receives it, which region it sits in, and which identities can read it before masking is applied.
• Authorized views instead of copies — an explicit rule that access is granted through views, because every convenience copy becomes an ungoverned table with its own refresh schedule.
• One service account per pipeline — least-privilege roles per workload, rather than a shared account with broad editor rights that nobody dares to narrow later.
• Labels agreed up front — a small, enforced label vocabulary on datasets and jobs, since cost attribution added retrospectively never covers the historical spend you actually want to explain.
• External and BigLake tables — who owns the underlying Cloud Storage buckets and how their permissions stay aligned with the dataset permissions that sit in front of them.
• Region and residency — the regions each dataset is created in, and what that rules out later, because datasets cannot simply be moved between regions.

Programs rarely stop at one product. Buyers hiring for BigQuery often pair it with Campaign Manager 360 partners , Display & Video 360 partners or Google Analytics 4 partners , or review the whole Google Cloud landscape before committing.