Skip to content

GCP Data: Cloud Storage and BigQuery

Most Google Cloud architectures keep files in Cloud Storage and analyze structured data in BigQuery. Cloud Storage is object storage (the equivalent of Amazon S3 or Azure Blob Storage); BigQuery is a serverless data warehouse that runs SQL over very large tables without servers to manage. They connect naturally: data lands in a bucket, is loaded into or queried from BigQuery, and results are exported back to a bucket.

The running example is the invented OrdersPortal shop in project ordersportal-prod: invoices and nightly order exports land in buckets, and an analytics dataset in BigQuery holds order events. Commands use the current Google Cloud CLI (gcloud storage and bq). Prices were taken from the Cloud Storage and BigQuery pricing pages in November 2026 and are list prices in USD; check those pages for your region and currency. The commands and SQL were not run here because they need a billing-enabled project.

Topic What it answers
Cloud Storage basics Buckets, objects, storage classes and locations
Working with objects Creating buckets, copying, listing and syncing
Access control Uniform bucket-level access, IAM and public access prevention
Lifecycle soft delete and versioning Automatic class changes, deletion and recovery
Signed URLs Time-limited access without a Google account
Static websites Serving a site from a bucket
BigQuery basics Projects, datasets, tables and jobs
Loading and querying data bq load, bq query and SQL examples
Partitioning and clustering Making queries cheaper and faster
BigQuery pricing and cost control On-demand pricing, dry runs and limits
BigQuery from Python The client library with query parameters

A bucket is a container with a globally unique name, a location and a default storage class. Objects inside it are immutable blobs addressed as gs://bucket/path/name; “folders” are just name prefixes unless you enable hierarchical namespace. A bucket’s location is a single region (lowest latency and cost, for example europe-west2), a dual-region, or a multi-region (EU, US) for higher availability.

The storage class sets the trade-off between storage price and access price:

Class Typical use Minimum storage duration
Standard Frequently read data, serving content, data being processed None
Nearline Read about once a month or less (recent backups) 30 days
Coldline Read about once a quarter or less 90 days
Archive Long-term retention read less than once a year 365 days

Colder classes cost less to store and more to read, and deleting or rewriting an object before its minimum duration incurs an early deletion charge. Classes are set per object, so one bucket can hold a mix. The Always Free tier includes 5 GB-months of Standard storage in some US regions.

gcloud storage is the current command-line tool; the older gsutil still exists but Google recommends gcloud storage, which is faster for large transfers.

Terminal window
# Create a regional bucket with uniform access and public access prevention
gcloud storage buckets create gs://ordersportal-invoices \
--location europe-west2 \
--default-storage-class STANDARD \
--uniform-bucket-level-access \
--public-access-prevention
gcloud storage cp ./INV-2026-0042.pdf gs://ordersportal-invoices/2026/11/
gcloud storage cp -r ./exports gs://ordersportal-exports/ # a folder, recursively
gcloud storage ls -l gs://ordersportal-invoices/2026/11/
gcloud storage rsync -r ./build gs://ordersportal-site # copy only changed files
gcloud storage rm gs://ordersportal-exports/exports/old.csv

Pitfall: rsync with --delete-unmatched-destination-objects deletes objects that are missing locally. Run it once with --dry-run before pointing it at a bucket with data you care about.

Use uniform bucket-level access, where only IAM controls access, for every new bucket. Object ACLs (the older, per-object model) are hard to audit and are disabled once uniform access is on. Grant predefined roles at the bucket level:

Terminal window
gcloud storage buckets add-iam-policy-binding gs://ordersportal-invoices \
--member serviceAccount:orders-api@ordersportal-prod.iam.gserviceaccount.com \
--role roles/storage.objectCreator
Role Allows
roles/storage.objectViewer List and read objects
roles/storage.objectCreator Create objects (not overwrite or delete)
roles/storage.objectUser Read, create, overwrite and delete objects
roles/storage.admin Full control of buckets and objects

Public access prevention blocks any grant to allUsers or allAuthenticatedUsers on the bucket, so a mistaken binding cannot publish invoices. Organizations can enforce it for all projects with an organization policy. Only turn it off for buckets that are deliberately public, such as a static website.

Object Lifecycle Management applies rules automatically. This configuration moves exports to Nearline after 30 days and deletes them after a year:

{
"rule": [
{ "action": { "type": "SetStorageClass", "storageClass": "NEARLINE" }, "condition": { "age": 30 } },
{ "action": { "type": "Delete" }, "condition": { "age": 365 } }
]
}
Terminal window
gcloud storage buckets update gs://ordersportal-exports --lifecycle-file=lifecycle.json

Soft delete is enabled on new buckets by default with a seven-day retention: deleted or overwritten objects can be restored during that window, and you pay for their storage until it ends. Raise the window (up to 90 days) for critical data, or set it to zero for scratch buckets with high churn. Object Versioning keeps noncurrent versions until a lifecycle rule removes them, which protects against overwrites for longer periods. A retention policy (optionally locked) prevents deletion for a fixed time, for compliance.

A signed URL gives anyone holding it time-limited access to one object, without a Google account, for example a link in an “Your invoice is ready” email. Signing needs a service account’s signing credentials. Client libraries can sign through the IAM Credentials API using the service account the code runs as, so no key file is required; the CLI can sign with a key file or by impersonating a service account.

Terminal window
gcloud storage sign-url gs://ordersportal-invoices/2026/11/INV-2026-0042.pdf \
--duration 1h \
--impersonate-service-account invoice-signer@ordersportal-prod.iam.gserviceaccount.com

Impersonation needs roles/iam.serviceAccountTokenCreator on the signing service account for whoever runs the command. Keep durations short, and remember a signed URL cannot be revoked before it expires except by deleting the object or disabling the signing identity.

A bucket can serve a static site: set --web-main-page-suffix=index.html and --web-error-page=404.html, allow public reads, and objects are available at https://storage.googleapis.com/BUCKET/index.html. For a custom domain with HTTPS, put an external Application Load Balancer with a backend bucket and a Google-managed certificate in front, optionally with Cloud CDN. For sites with build steps and previews, a static hosting platform is often simpler; see website hosting.

BigQuery separates storage from compute: tables are stored in a columnar format, and queries run on shared, Google-managed compute measured in slots. You never size a cluster.

Term Meaning
Project Billing and access boundary; queries are billed to the project that runs them
Dataset Container for tables and views, with a location (EU, US or a region) that cannot change
Table Columnar storage with a schema; native, external (data in Cloud Storage) or a snapshot
View A saved query; a materialized view stores and incrementally refreshes results
Job Any load, query, export or copy operation, with its own ID, status and bytes processed

Pitfall: dataset location mismatch. A query cannot join tables in different locations, and loading from a bucket in another location fails or adds transfer cost. Create datasets in the same location as the buckets that feed them.

Terminal window
bq --location=EU mk --dataset ordersportal-prod:analytics
# Load nightly CSV exports from Cloud Storage with an explicit schema
bq load --source_format=CSV --skip_leading_rows=1 \
analytics.order_events \
"gs://ordersportal-exports/order_events/2026-11-17/*.csv" \
order_id:STRING,customer_id:STRING,event_name:STRING,amount:NUMERIC,created_at:TIMESTAMP
bq query --use_legacy_sql=false \
'SELECT event_name, COUNT(*) AS events FROM analytics.order_events GROUP BY event_name'

Prefer an explicit schema (or a schema file) to --autodetect for recurring loads: autodetection guesses from a sample and can type an order ID as an integer one day and a string the next. Columnar formats such as Parquet and Avro load faster than CSV and carry their schema.

BigQuery uses GoogleSQL (standard SQL); always pass --use_legacy_sql=false to bq query unless your configuration already defaults to it.

-- Revenue and order count per day for the last 30 days
SELECT
DATE(created_at) AS order_date,
COUNT(DISTINCT order_id) AS orders,
SUM(amount) AS revenue
FROM `ordersportal-prod.analytics.order_events`
WHERE event_name = 'order_completed'
AND created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY order_date
ORDER BY order_date DESC;

Partitioning splits a table by a date, timestamp or integer column so queries that filter on it read only the matching partitions. Clustering sorts data within partitions by up to four columns, so filters and aggregations on those columns read fewer blocks.

CREATE TABLE `ordersportal-prod.analytics.order_events_p`
PARTITION BY DATE(created_at)
CLUSTER BY customer_id, event_name
OPTIONS (require_partition_filter = TRUE, partition_expiration_days = 730)
AS SELECT * FROM `ordersportal-prod.analytics.order_events`;

require_partition_filter rejects queries that do not filter on the partitioning column, which stops an accidental full scan of years of events. Partition on the column people actually filter by (usually the event or business date), and avoid partitions so small that a table has many thousands of tiny ones; cluster instead.

By default queries use on-demand pricing: you pay for the bytes each query processes, $6.25 per TiB, and the first 1 TiB per month is free. The alternative is capacity pricing (BigQuery editions), where you pay for slots over time with autoscaling or commitments, which suits steady heavy workloads. Storage is billed separately: active logical storage costs about $0.023 per GiB per month (1 TiB for a month is about $23.55 in us-central1), data untouched for 90 days drops to the cheaper long-term rate automatically, and the first 10 GiB per month is free.

Bytes processed depend on the columns a query reads, not the rows it returns:

  • LIMIT does not reduce cost. SELECT * FROM big_table LIMIT 10 is billed for every column of the whole table (clustered tables can sometimes stop early, but do not rely on it). Select only the columns you need and filter on partition columns.
  • Estimate before running. bq query --dry_run --use_legacy_sql=false '...' reports the bytes the query would process without running it; the console shows the same estimate.
  • Cap the damage. --maximum_bytes_billed=50000000000 makes a query fail instead of processing more than 50 GB. Custom quotas can limit bytes per user or per project per day.
  • Cached results are free. Re-running an identical query on unchanged tables returns cached results within about 24 hours at no charge.
Terminal window
bq query --dry_run --use_legacy_sql=false \
'SELECT customer_id, SUM(amount) FROM analytics.order_events_p
WHERE DATE(created_at) = "2026-11-17" GROUP BY customer_id'

The google-cloud-bigquery client uses Application Default Credentials (your gcloud auth application-default login locally, the attached service account on Cloud Run or GKE). Use query parameters for values, never string formatting, both for safety and so BigQuery can cache the query.

# fragment: needs google-cloud-bigquery and access to the dataset
from google.cloud import bigquery
client = bigquery.Client(project="ordersportal-prod")
sql = """
SELECT customer_id, SUM(amount) AS revenue
FROM `ordersportal-prod.analytics.order_events_p`
WHERE DATE(created_at) BETWEEN @start AND @end AND event_name = 'order_completed'
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 10
"""
job_config = bigquery.QueryJobConfig(
query_parameters=[
bigquery.ScalarQueryParameter("start", "DATE", "2026-11-01"),
bigquery.ScalarQueryParameter("end", "DATE", "2026-11-17"),
],
maximum_bytes_billed=10 * 1024**3, # fail rather than scan more than 10 GiB
)
for row in client.query(sql, job_config=job_config).result():
print(row.customer_id, row.revenue)

bq extract (or EXPORT DATA in SQL) writes query results or tables back to Cloud Storage as CSV, JSON, Avro or Parquet for other systems.

Need Use
Files, images, backups, exports, data lake raw zone Cloud Storage
SQL analytics and reporting over large structured data BigQuery native tables
Query files in a bucket without loading them BigQuery external or BigLake tables over Cloud Storage
Low-latency reads and writes for an application A database such as Cloud SQL, Firestore or Spanner, not BigQuery

1. A team’s BigQuery bill doubled after adding LIMIT 100 to dashboard queries “to save money”. What happened? LIMIT does not reduce bytes processed under on-demand pricing; cost depends on the columns read across the scanned partitions. The fix is selecting fewer columns, filtering on the partition column, clustering on common filters, and enforcing require_partition_filter and maximum_bytes_billed.

2. How do you make a Cloud Storage bucket of invoices safe from accidental exposure and deletion? Uniform bucket-level access, public access prevention, least-privilege IAM per service account, soft delete with a longer retention or Object Versioning, a retention policy if regulations require it, and signed URLs for customer downloads instead of public objects.

3. When would you choose Coldline over Nearline? When data is read less than about once a quarter and kept at least 90 days, such as quarterly audit exports. Reading it more often makes retrieval charges outweigh the storage savings, and deleting it early incurs an early deletion charge.

4. Partitioning or clustering? Partition by the date (or integer range) most queries filter on, to prune whole partitions and enable partition expiration. Cluster by the high-cardinality columns used in filters and joins, such as customer ID. Most large event tables use both.

5. Senior scenario: OrdersPortal wants near-real-time sales dashboards over 3 years of order events (about 8 TiB). Design it. Land events through Pub/Sub into a BigQuery table partitioned by event date and clustered by customer and event type (a BigQuery subscription or the Storage Write API), keep raw files in Cloud Storage with lifecycle rules for replay, and build dashboard tables as materialized views or scheduled aggregations so dashboards read small summary tables, not 8 TiB. Control cost with require_partition_filter, per-user byte quotas and, if usage is steady, capacity pricing with autoscaling slots. Grant analysts access through authorized views that hide personal data.