BigQuery Study Notes

BigQuery is Google Cloud's fully managed, serverless enterprise data warehouse for fast SQL analytics over very large datasets. You load or query data without provisioning servers, clusters, or storage disks, and Google handles scaling, availability, and maintenance behind the scenes. It separates storage and compute, so you can scale each independently and query petabytes with standard SQL.

Key facts

• BigQuery organises resources as projects containing datasets, and datasets containing tables, views, and routines, with access controlled at each level using IAM roles.
• Storage and compute are decoupled: data is stored in Google's distributed Colossus file system in a columnar format, while queries run on dynamically allocated compute called slots.
• A slot is a unit of virtual CPU used to execute a query, and reservations let you purchase dedicated slot capacity instead of relying only on on-demand pricing capacity.
• Partitioning divides a table by date, timestamp, integer range, or ingestion time to reduce the amount of data scanned and improve query performance and cost control.
• Clustering sorts data within partitions by one or more columns, which further prunes blocks scanned when queries filter on those clustered columns.
• BigQuery supports federated queries that read external sources such as Cloud Storage, Bigtable, and Google Sheets without loading the data first, though performance will vary.
• Materialised views store precomputed query results that BigQuery can refresh automatically and use to accelerate repeated queries over base tables.
• By default, query results are cached for about 24 hours at the time of writing, and identical queries can return cached results without consuming compute.
• BigQuery ML lets you create and run machine learning models using SQL statements directly inside the warehouse, without exporting data to a separate ML platform.

What the exam expects you to know

• The exam tests whether you choose BigQuery for analytical OLAP workloads such as reporting and ad hoc SQL, rather than for transactional OLTP workloads needing frequent row updates.
• Expect questions on controlling costs and performance with partitioning and clustering, including which column to partition on for time-based queries.
• You should know dataset-level IAM, authorised views, and column-level security for sharing data safely without giving access to underlying tables.
• Questions often cover loading options: batch loads, streaming inserts, Data Transfer Service, and federated queries, and when each is appropriate.
• Know the difference between on-demand and reservation-based capacity, and how flat-rate slots help stabilise costs for predictable heavy workloads.
• The exam checks that you can use the INFORMATION_SCHEMA views and audit logs to monitor jobs, storage, and slot usage for troubleshooting.

BigQuery vs Bigtable: when to use which

Choose BigQuery when the workload is analytics: complex SQL, aggregations, joins, and scans over historical data for dashboards and reporting. Choose Bigtable when the workload is operational: very high-throughput, low-latency reads and writes of individual rows by key, such as IoT events or user profiles, with no need for joins. A useful rule is that BigQuery answers questions about many rows, while Bigtable serves single rows very fast. If a scenario mentions SQL analysts and petabyte reports, pick BigQuery; if it mentions milliseconds latency and a row key, pick Bigtable. See also the Bigtable notes.

Useful gcloud commands

bq ls
bq mk --dataset myproject:mydataset
bq load --source_format=CSV myproject:mydataset.mytable gs://my-bucket/data.csv
bq query --use_legacy_sql=false 'SELECT * FROM myproject.mydataset.mytable LIMIT 10'

Common exam traps

• BigQuery is not a transactional database, so do not choose it for high-frequency single-row updates or an application backend needing strong OLTP behaviour.
• Partitioning alone does not guarantee savings: a query without a filter on the partition column can still scan the whole table and incur full cost.
• Exporting or copying data and running queries are separate concerns; a cached result is not a backup, and BigQuery is not a substitute for a backup strategy.
• Dataset access does not automatically flow from project-level viewer roles in the way candidates expect, so authorised views are often the correct sharing answer.

Frequently asked questions

What is the difference between partitioning and clustering in BigQuery?

Partitioning splits a table into segments, usually by date, so queries with a date filter scan only relevant partitions. Clustering then sorts data inside each partition by chosen columns, so filtered queries skip more data blocks. Use partitioning first for the main division, especially time, and clustering to refine performance for common filter columns.

How does BigQuery control who can see data?

Access is managed with IAM at the project, dataset, table, and column levels, using predefined roles such as data viewer, data editor, and job user. For sharing, authorised views let users query a view without having direct access to the source tables, and policy tags can restrict sensitive columns to approved groups only.

When should I stream data into BigQuery instead of loading it in batches?

Use streaming inserts when data needs to be available for querying within seconds, such as clickstream events or live dashboards. Use batch loading when data arrives in files on a schedule and a delay of minutes is acceptable, because batch loads are simpler to manage and avoid the extra considerations that come with continuous streaming ingestion.

Study aid disclaimer: Cloud Cert Notes is an independent educational study aid. It is not affiliated with, endorsed by, or sponsored by Google, Microsoft, Amazon, Oracle, or Alibaba. Google Cloud, Associate Cloud Engineer, and Cloud Digital Leader are trademarks of Google LLC; all other certification names and product names are trademarks of their respective owners. Always confirm service details against the official provider documentation before sitting an exam.