BigQueryAuthorCloudBrum editorial teamLast reviewed28 Oct 2026Reading time~12 minLevelBeginner to advanced

• Storage and compute kept apartData sits in BigQuery’s managed columnar storage, and queries run on a separate pool of compute that Google scales for you. You don’t provision clusters, and you don’t pay for compute while nothing runs.
• Two ways to pay for queriesOn-demand billing charges for the bytes a query reads, about $6.25 per TiB with the first TiB each month free. Capacity pricing reserves slots, the units of compute, in Standard, Enterprise and Enterprise Plus editions, billed by the slot-hour.
• Query files in Cloud StorageExternal tables and BigLake tables let SQL read Parquet, ORC, Avro, CSV or JSON files in a bucket without loading them. It’s the same idea as Athena, with the choice to load the data later for speed.
• Partitioning and clusteringPartition a table by a date or integer column, and cluster it by up to four more. Queries that filter on those columns read less, and cost less.
• Guard rails on spendA dry run reports the bytes a query would process before you run it. maximum_bytes_billed makes a query fail instead of exceeding a limit, and a table can require a partition filter on every query.
• Fine-grained accessIAM roles control datasets and tables. Policy tags protect columns, row-level access policies filter rows per user, and authorised views share a slice without exposing the base table.
• SQL that does machine learningBigQuery ML trains and runs models with SQL statements, and BI Engine caches data in memory to speed up dashboards.
• Streaming and scheduled workLoad files in batches for free, stream rows in for a charge, schedule queries and use Dataform or other tools for transformations.

Dataset

A container for tables and views in one location. Access rules and default settings are often set at this level.

Location

The region or multi-region where a dataset and its data live. Queries and the data they read must be in the same location.

Native table

A table stored in BigQuery’s own columnar storage. It’s the fastest to query and the one that supports partitioning and clustering.

External table

A table definition over files in Cloud Storage or another source. The data stays where it is, and queries are generally slower than on native tables.

BigLake table

An external table with BigQuery security applied, including column and row rules, and access to storage through a connection rather than the user’s own rights.

Partitioned table

A table split by a time or integer column, so queries filtering on it read only the matching partitions.

Clustered table

A table whose storage is sorted by up to four columns, so filters and aggregations on them read fewer blocks.

Slot

A unit of BigQuery compute. On-demand queries use a shared pool, and editions let you reserve or autoscale slots.

Edition

A capacity pricing tier: Standard, Enterprise or Enterprise Plus, each with its own features and slot-hour price.

Dry run

A request that validates a query and returns the bytes it would process, without running it or billing you.

Maximum bytes billed

A per-query setting that makes the query fail if it would process more than the limit.

Policy tag

A label from a taxonomy that restricts who can read a column. It’s the basis for column-level security.

Materialized view

A precomputed result that BigQuery keeps up to date, so repeated aggregations read less data.

INFORMATION_SCHEMA

Built-in views with metadata, including JOBS, which lists every query with bytes processed and the user who ran it.

Query files and a native table with bq

1. Pick the project, the location and the layoutUse a project with billing, and choose a dataset location near your data, such as asia-south1 for Mumbai. Keep the bucket and the dataset in the same location, because BigQuery can’t query across locations.
2. Store files as Parquet in foldersWrite files under paths such as orders/order_date=2026-10-28/. BigQuery can read that layout as Hive partitions, which lets filters on order_date skip folders.
3. Create the datasetRun bq mk --dataset with the location. Give it a short description and, if your rules need it, a default encryption key.
4. Create an external tableGenerate a table definition with bq mkdef, using Hive partitioning, and create the external table from it. Now you can query the files straight away.
5. Dry-run, then run with a capRun the query with --dry_run to see the bytes it would process. Then run it with --maximum_bytes_billed, so a mistake fails instead of costing money.
6. Load a native table for the hot dataUse CREATE TABLE AS SELECT to copy the data into a partitioned, clustered native table, with require_partition_filter turned on so every query has to name a date range.
7. Share it safelyGrant roles/bigquery.jobUser on the project, and roles/bigquery.dataViewer on the dataset, to the groups that should query. Use views or policy tags for sensitive columns.
8. Watch the spendQuery INFORMATION_SCHEMA.JOBS for the top queries by bytes processed, and set project or user quotas as a backstop.
9. Decide on capacityIf on-demand spend is large and steady, compare it with an edition and a slot reservation or autoscaling for a month.

• Partition large tables by the date you filter on, and cluster by the columns that appear in filters and joins, most selective first.
• Name columns and avoid SELECT *. BigQuery stores data by column, so unread columns cost nothing.
• Filter early, and put filters on the partition and clustering columns using plain comparisons.
• Use materialized views or summary tables for repeated aggregations, and BI Engine for dashboards that need instant response.
• Denormalise with nested and repeated fields where it fits, which avoids large joins.
• Avoid skewed joins and global sorts. Aggregate before joining and use LIMIT with ORDER BY only on small results.
• Load external data into native tables when you query it often, since native storage is faster.
• Look at the query execution graph in the console for stages that are slow or spilling.

FinOps is the habit of managing cloud spend together with engineering. These are the FinOps checks that matter most for BigQuery.

• On-demand queries bill by bytes processed, about $6.25 per TiB with the first TiB each month free. Columns, partitions and clustering are your levers.
• Turn on require partition filter for big tables, and set maximum bytes billed in scripts and tools.
• Run a dry run before unfamiliar queries, and teach analysts to read the bytes estimate.
• Use expiration on scratch tables and partitions, and let old data move to cheaper long-term storage pricing automatically.
• Compare on-demand with an edition for steady, heavy workloads, and start with autoscaling before committing to slots.
• Use materialized views and summary tables so dashboards read small tables.
• Prefer batch loads, which are free, to streaming inserts when seconds of delay are acceptable.
• Set custom quotas per project and per user as a backstop against runaway queries.
• Export billing data and query INFORMATION_SCHEMA.JOBS to find the most expensive queries and users.
• Label datasets and jobs by team, and set budget alerts.

• Grant IAM roles to groups at the dataset level, with bigquery.jobUser on the billing project and dataViewer on the data only.
• Protect sensitive columns with policy tags and use row-level access policies where users should see different rows.
• Share slices of data through authorised views and authorised datasets, not by copying tables.
• Use customer-managed encryption keys if your rules need them, and set a default key for the dataset.
• Put BigQuery inside a VPC Service Controls perimeter to limit data exfiltration, and use Private Google Access.
• Use service accounts without keys for jobs and BI tools, and block service account key creation with an organisation policy.
• Enable Cloud Audit Logs for data access, and export them to a central project and to BigQuery for analysis.
• Choose the dataset location to meet data residency rules, and enforce it with an organisation policy.
• Control the buckets behind external and BigLake tables: write access to the files is write access to your results.
• Review access with IAM recommender and the INFORMATION_SCHEMA views, and remove unused roles.

Cloud Audit Logs record who ran queries and changed datasets, INFORMATION_SCHEMA shows every job with its cost, Security Command Center reports findings, Dataplex can catalogue and classify data, and the Compliance resource center holds Google’s certificates. Google runs the storage and the query engine, while you own the data, its layout, access rules, locations and the habits that decide the bill.

• Read less: partitions, clustering and named columns cut the work for each query.
• Use summary tables and materialized views, so dashboards don’t recompute the same result.
• Set expiry on scratch data, so storage doesn’t grow without a reason.
• Choose a location with a high carbon-free energy share when data rules allow. Google publishes the percentage per region.
• Prefer batch loads and scheduled queries at flexible times over constant streaming where it isn’t needed.
• Compress and export only what you need, and avoid copying data between locations.
• Cloud Carbon Footprint in the console estimates emissions per project and service. Check what it shows for BigQuery before you quote a figure.

Purchase options

What you actually pay for

• Bytes processed by on-demand queries, or slot-hours for an edition
• Active and long-term storage of tables
• Streaming writes and some export or load paths
• Cloud Storage and network costs for external tables
• BI Engine, BigQuery ML and other add-ons you turn on

Order data stored as Parquet in Cloud Storage, partitioned by day. The bq tab creates a dataset, an external table over the files, runs a dry run and a capped query for the last seven days, then copies the data into a partitioned, clustered native table that requires a partition filter. Terraform builds the dataset, both tables and access for an analyst group. The Python tab estimates the cost with a dry run before it runs a query, and the C# tab runs a parameterised query with a byte cap. Replace the project, bucket and group with your own. Queries cost by the data they read, so start with a dry run.

# Placeholders: replace the project, bucket and group with your own.
PROJECT=my-orders-project
LOCATION=asia-south1
BUCKET=orders-lake-example
ANALYSTS=analysts@example.com

gcloud config set project $PROJECT
gcloud services enable bigquery.googleapis.com

# Who may do what: run jobs in the project, read the dataset.
gcloud projects add-iam-policy-binding $PROJECT --member="group:$ANALYSTS" --role=roles/bigquery.jobUser

# The dataset sits in the same location as the bucket. Scratch tables expire after 30 days.
bq --location=$LOCATION mk --dataset --description "Order analytics" --default_table_expiration 2592000 $PROJECT:orders_lake

# An external table over the Parquet files in Cloud Storage; folders named order_date=YYYY-MM-DD become a partition column.
bq mkdef --source_format=PARQUET --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://$BUCKET/orders/ "gs://$BUCKET/orders/*" > orders_def.json
bq --location=$LOCATION mk --table --external_table_definition=orders_def.json $PROJECT:orders_lake.orders_ext

# Dry run first: how many bytes would the last seven days read?
SQL="SELECT order_date, COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue
FROM \`$PROJECT.orders_lake.orders_ext\`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND status = 'PAID'
GROUP BY order_date ORDER BY order_date"
bq --location=$LOCATION query --use_legacy_sql=false --dry_run "$SQL"

# Run it, but fail instead of reading more than 10 GiB.
bq --location=$LOCATION query --use_legacy_sql=false --maximum_bytes_billed=10737418240 "$SQL"

# Copy the data into a native table. Every query on it has to name a date range.
bq --location=$LOCATION query --use_legacy_sql=false "
CREATE TABLE \`$PROJECT.orders_lake.orders\`
PARTITION BY order_date
CLUSTER BY status, customer_id
OPTIONS (require_partition_filter = TRUE)
AS SELECT order_id, customer_id, total, currency, status, CAST(order_date AS DATE) AS order_date
FROM \`$PROJECT.orders_lake.orders_ext\`"

# The five queries that processed the most data in the last day.
bq --location=$LOCATION query --use_legacy_sql=false "
SELECT user_email, job_id, ROUND(total_bytes_processed / POW(1024, 3), 1) AS gib_processed
FROM \`region-$LOCATION\`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND job_type = 'QUERY'
ORDER BY total_bytes_processed DESC LIMIT 5"

# Placeholders: pass in your project, bucket and analyst group.
variable "project_id" {
  type = string
}

variable "location" {
  type    = string
  default = "asia-south1"
}

variable "lake_bucket" {
  type    = string
  default = "orders-lake-example"
}

variable "analysts_group" {
  type    = string
  default = "analysts@example.com"
}

resource "google_bigquery_dataset" "orders_lake" {
  dataset_id                  = "orders_lake"
  location                    = var.location
  description                 = "Order analytics"
  default_table_expiration_ms = 2592000000
  delete_contents_on_destroy  = false
}

# Files stay in Cloud Storage; folders named order_date=YYYY-MM-DD become a partition column.
resource "google_bigquery_table" "orders_ext" {
  dataset_id          = google_bigquery_dataset.orders_lake.dataset_id
  table_id            = "orders_ext"
  deletion_protection = true

  external_data_configuration {
    source_format = "PARQUET"
    autodetect    = true
    source_uris   = ["gs://${var.lake_bucket}/orders/*"]

    hive_partitioning_options {
      mode              = "AUTO"
      source_uri_prefix = "gs://${var.lake_bucket}/orders/"
    }
  }
}

# The hot copy: partitioned by day, clustered, and every query must name a date range.
resource "google_bigquery_table" "orders" {
  dataset_id               = google_bigquery_dataset.orders_lake.dataset_id
  table_id                 = "orders"
  deletion_protection      = true
  require_partition_filter = true
  clustering               = ["status", "customer_id"]

  time_partitioning {
    type  = "DAY"
    field = "order_date"
  }

  schema = jsonencode([
    { name = "order_id", type = "STRING", mode = "REQUIRED" },
    { name = "customer_id", type = "STRING", mode = "NULLABLE" },
    { name = "total", type = "FLOAT", mode = "NULLABLE" },
    { name = "currency", type = "STRING", mode = "NULLABLE" },
    { name = "status", type = "STRING", mode = "NULLABLE" },
    { name = "order_date", type = "DATE", mode = "REQUIRED" },
  ])
}

# Analysts may run queries in the project and read this dataset, nothing more.
resource "google_project_iam_member" "analysts_jobs" {
  project = var.project_id
  role    = "roles/bigquery.jobUser"
  member  = "group:${var.analysts_group}"
}

resource "google_bigquery_dataset_iam_member" "analysts_read" {
  dataset_id = google_bigquery_dataset.orders_lake.dataset_id
  role       = "roles/bigquery.dataViewer"
  member     = "group:${var.analysts_group}"
}

# Estimates a query with a dry run, then runs it with a cap on the bytes billed.
# pip install google-cloud-bigquery   (uses Application Default Credentials)
import sys

from google.cloud import bigquery

client = bigquery.Client(location="asia-south1")
RATE_PER_TIB = 6.25      # USD, the on-demand list price at the time of writing; the first TiB each month is free

SQL = """
SELECT order_date, COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue
FROM `my-orders-project.orders_lake.orders`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL @days DAY) AND status = @status
GROUP BY order_date ORDER BY order_date
"""


def estimate_and_run(days=7, status="PAID", cap_gib=10):
    params = [bigquery.ScalarQueryParameter("days", "INT64", days), bigquery.ScalarQueryParameter("status", "STRING", status)]

    dry = client.query(SQL, job_config=bigquery.QueryJobConfig(query_parameters=params, dry_run=True, use_query_cache=False))
    gib = dry.total_bytes_processed / 1024 ** 3
    print("this query would process %.3f GiB, about $%.4f at the list rate" % (gib, gib / 1024 * RATE_PER_TIB))
    if gib > cap_gib:
        raise SystemExit("more than %d GiB, not running it" % cap_gib)

    config = bigquery.QueryJobConfig(query_parameters=params, maximum_bytes_billed=cap_gib * 1024 ** 3)
    job = client.query(SQL, job_config=config)
    for row in job.result():
        print(row.order_date, row.orders, row.revenue)
    print("billed: %.3f GiB (cache hit: %s)" % ((job.total_bytes_billed or 0) / 1024 ** 3, job.cache_hit))


if __name__ == "__main__":
    estimate_and_run(days=int(sys.argv[1]) if len(sys.argv) > 1 else 7)

// dotnet add package Google.Cloud.BigQuery.V2
// Runs a parameterised query and refuses to bill more than 10 GiB for it.
// Uses Application Default Credentials (gcloud auth application-default login, or the service account on the machine).
using Google.Cloud.BigQuery.V2;

string project = args.Length > 0 ? args[0] : "my-orders-project";
BigQueryClient client = await BigQueryClient.CreateAsync(project);

const string sql = @"
SELECT order_date, COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue
FROM `my-orders-project.orders_lake.orders`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL @days DAY) AND status = @status
GROUP BY order_date ORDER BY order_date";

var parameters = new[]
{
    new BigQueryParameter("days", BigQueryDbType.Int64, 7),
    new BigQueryParameter("status", BigQueryDbType.String, "PAID")
};
var options = new QueryOptions { MaximumBytesBilled = 10L * 1024 * 1024 * 1024 };

BigQueryJob job = await client.CreateQueryJobAsync(sql, parameters, options);
job = await job.PollUntilCompletedAsync();
job.ThrowOnAnyError();

BigQueryResults results = await client.GetQueryResultsAsync(job.Reference);
foreach (BigQueryRow row in results)
    Console.WriteLine($"{row["order_date"]}  orders={row["orders"],5}  revenue={row["revenue"],12:N2}");

Console.WriteLine($"Billed {(job.Statistics.Query.TotalBytesBilled ?? 0) / 1_073_741_824.0:F3} GiB.");

Short walkthrough videos for this service are being prepared. They will appear here and load only when you press play. Until then, follow CloudBrum on YouTube for new videos.

If you only need ad hoc SQL over files, external tables are the nearest match to Amazon Athena. For application data and transactions use a database such as Cloud SQL. The files themselves live in Cloud Storage, and big code-based transformations belong in Dataflow or Dataproc.