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.
Topics on this page
Section titled “Topics on this page”| 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 |
Cloud Storage basics
Section titled “Cloud Storage basics”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.
Working with objects
Section titled “Working with objects”gcloud storage is the current command-line tool; the older gsutil still exists but Google recommends gcloud storage, which is faster for large transfers.
# Create a regional bucket with uniform access and public access preventiongcloud 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, recursivelygcloud storage ls -l gs://ordersportal-invoices/2026/11/gcloud storage rsync -r ./build gs://ordersportal-site # copy only changed filesgcloud storage rm gs://ordersportal-exports/exports/old.csvPitfall: 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.
Access control
Section titled “Access control”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:
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.
Lifecycle soft delete and versioning
Section titled “Lifecycle soft delete and versioning”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 } } ]}gcloud storage buckets update gs://ordersportal-exports --lifecycle-file=lifecycle.jsonSoft 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.
Signed URLs
Section titled “Signed URLs”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.
gcloud storage sign-url gs://ordersportal-invoices/2026/11/INV-2026-0042.pdf \ --duration 1h \ --impersonate-service-account invoice-signer@ordersportal-prod.iam.gserviceaccount.comImpersonation 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.
Static websites
Section titled “Static websites”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 basics
Section titled “BigQuery basics”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.
Loading and querying data
Section titled “Loading and querying data”bq --location=EU mk --dataset ordersportal-prod:analytics
# Load nightly CSV exports from Cloud Storage with an explicit schemabq 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 daysSELECT DATE(created_at) AS order_date, COUNT(DISTINCT order_id) AS orders, SUM(amount) AS revenueFROM `ordersportal-prod.analytics.order_events`WHERE event_name = 'order_completed' AND created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)GROUP BY order_dateORDER BY order_date DESC;Partitioning and clustering
Section titled “Partitioning and clustering”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_nameOPTIONS (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.
BigQuery pricing and cost control
Section titled “BigQuery pricing and cost control”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:
LIMITdoes not reduce cost.SELECT * FROM big_table LIMIT 10is 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=50000000000makes 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.
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'BigQuery from Python
Section titled “BigQuery from Python”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 datasetfrom google.cloud import bigquery
client = bigquery.Client(project="ordersportal-prod")sql = """SELECT customer_id, SUM(amount) AS revenueFROM `ordersportal-prod.analytics.order_events_p`WHERE DATE(created_at) BETWEEN @start AND @end AND event_name = 'order_completed'GROUP BY customer_idORDER BY revenue DESCLIMIT 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.
Choosing between them
Section titled “Choosing between them”| 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 |
Interview questions
Section titled “Interview questions”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.
Related pages
Section titled “Related pages”- Google Cloud overview and IAM — projects, regions and the roles used on this page
- GCP compute — the services that write to these buckets and datasets
- Azure vs AWS vs GCP — S3 and Blob Storage, Redshift and Synapse compared