BigQuery

BigQuery is a fully-managed data analysis service that enables businesses to analyze Big Data. It features highly scalable data storage that accommodates up to hundreds of terabytes, the ability to perform ad hoc queries on multi-terabyte datasets, and the ability to share data insights via the web.

BigQuery Security – IAM, Encryption & Row-Level

~ Last updated on : ~ jayendrapatil

Google Cloud BigQuery Security

BigQuery Encryption

  • BigQuery automatically encrypts all data before it is written to disk.
  • Each BigQuery object’s data and metadata is encrypted using the Advanced Encryption Standard (AES).
  • By default, Google uses Google default encryption at rest and manages the key encryption keys used for data protection.
  • BigQuery supports Customer-Managed Encryption Keys (CMEK) using Cloud KMS, allowing you to control the encryption keys for datasets, tables, and query results.
    • CMEK can be set as a default for a dataset, ensuring any tables created in future use the specified CMEK.
    • BigQuery does NOT support Customer-Supplied Encryption Keys (CSEK).
  • BigQuery supports Column-level encryption with Cloud KMS using AEAD encryption SQL functions, providing a second layer of protection at the column level.
    • AEAD encryption functions allow you to create keysets for encrypting and decrypting individual values in a table.
    • Provides “double access control” — users need both BigQuery access and Cloud KMS key access to decrypt data.
    • Supports deterministic encryption and decryption interoperable with Sensitive Data Protection.
  • BigQuery uses TLS for data in transit encryption.
  • Sensitive Data Protection (formerly Cloud Data Loss Prevention / Cloud DLP) can be used to scan BigQuery tables and to protect sensitive data and meet compliance requirements.

BigQuery IAM Roles

  • BigQuery supports access control of datasets and tables using IAM.
  • Basic Roles (formerly called “Primitive Roles”)
    • Basic roles act at the project level.
    • By default, granting access to a project also grants access to datasets within it unless overridden.
    • Are not limited to BigQuery resources only.
    • Can separate data access permissions from job-running permissions.
    • Viewer
      • View all datasets
      • Run Jobs/Queries
      • View and update all jobs that they started
    • Editor
      • All Viewer access
      • Modify or delete all tables
      • Create new datasets
    • Owner
      • All Editor access
      • List, modify, or delete all datasets
      • View all jobs
  • Predefined Roles
    • dataViewer, dataEditor, and dataOwner roles
      • are similar to the basic roles except
        • can be assigned for individual datasets
        • don’t give users permission to run jobs or queries
    • user, jobUser roles
      • give users permission to run jobs or queries
      • A jobUser can only start jobs and cancel jobs, but cannot list datasets or tables
      • A user, on the other hand, can perform a variety of other tasks, such as listing or creating datasets
      • User or group granted the user role at the project level can create datasets and can run query jobs against tables in those datasets.
      • user role does not give permission to query data, view table data, or view table schema details for datasets the user did not create. Need to have the dataViewer role for the same.
    • bigquery.admin role — provides permissions to manage all resources within the project, manage all data, and cancel jobs from other users.

BigQuery Access Control Levels

  • BigQuery supports access controls at multiple levels:
    • Organization level — policies applied across all projects
    • Project level — IAM policies on the project resource
    • Dataset level — IAM policies on individual datasets
    • Table/View level — IAM policies on individual tables or views
    • Column level — policy tags for column-level access control
    • Row level — row access policies for row-level security
  • Access control can also be managed using IAM Conditions for attribute-based access control and Tags for tag-based access control.

Authorized Views

  • Authorized views help provide view access to a dataset.
  • Use authorized views to restrict access at a lower resource level such as the table, column, row, or cell.
  • An authorized view allows sharing query results with particular users and groups without giving them access to the underlying tables.
  • Authorized View’s SQL query can be used to restrict the columns (fields) the users are able to query.
  • Authorized views HAVE to be created in a separate dataset from the source dataset. As access controls can be assigned only at the dataset level, if the view is created in the same dataset as the source data, the users would have access to both the view and the data.
  • Authorized View creation process
    • Create a separate dataset to store the view.
    • Create the view in the new dataset.
    • Give the group read access to the dataset containing the view.
    • Authorize the view to access the source dataset.
    • Give the group bigquery.user role to run jobs, including query jobs within the project.
  • Project-level bigquery.user role does not give the users the ability to view or query table data in the dataset containing the tables queried by the view. They need READER access to the dataset containing the view.

Authorized Datasets

  • An authorized dataset lets you authorize all of the views in a specified dataset to access the data in a second dataset.
  • Simplifies management over individual authorized views — instead of authorizing each view separately, the entire dataset is authorized.
  • Any new views added to the authorized dataset automatically gain access to the source dataset.
  • Useful when you have many views that need access to the same source data.

Authorized Routines

  • Authorized routines let you share query results with specific users or groups without giving them access to the underlying tables that generated the results.
  • For UDFs and table functions, you can authorize the function to access source dataset resources on the caller’s behalf.
  • Authorized routines must be created in a separate dataset from the source data.
  • Useful for sharing data transformations while protecting raw data access.

Fine-Grained Access Control

  • BigQuery provides fine-grained access to sensitive columns using policy tags, or type-based classification of data.
  • Policy tags are managed in a hierarchical taxonomy in Dataplex Universal Catalog (formerly Data Catalog, which was deprecated Feb 2025 and discontinued Jan 30, 2026).
  • Using BigQuery column-level security, you can create policies that check, at query time, whether a user has proper access.
  • Users with the Data Catalog Fine-Grained Reader role on a policy tag can access unmasked column data.
  • Row-level security extends the principle of least privilege by enabling fine-grained access control to a subset of data in a BigQuery table, by means of row-level access policies.
    • Row access policies use a filter expression (e.g., FILTER USING (region = "US")).
    • One table can have multiple row-level access policies.
    • Row-level access policies can coexist with column-level security as well as dataset-level, table-level, and project-level access controls.

Dynamic Data Masking

  • BigQuery supports dynamic data masking at the column level, allowing you to selectively obscure column data for user groups while still allowing them access to the column.
  • Data masking is built on top of column-level access control and uses policy tags and data policies.
  • Unlike column-level access control alone, masked users don’t get “permission denied” — they see obscured data instead.
  • Existing queries automatically mask column data based on the roles the user has been granted.
  • Data Masking Rules available:
    • Nullify — returns NULL instead of the column value (highest security)
    • Default masking value — returns a type-appropriate default (e.g., “” for STRING, 0 for INTEGER)
    • Hash (SHA-256) — returns deterministic hash of the value (supports JOINs)
    • Random Hash — uses per-query random salt for stronger security (supports JOINs within same query only)
    • Email mask — replaces username with XXXXX (e.g., XXXXX@gmail.com)
    • First four characters — shows only first 4 chars, rest replaced with XXXXX
    • Last four characters — shows only last 4 chars, rest replaced with XXXXX
    • Date year mask — truncates dates to year only (e.g., 2030-07-17 → 2030-01-01)
    • Custom masking routine — applies a user-defined function (UDF) for custom masking logic
  • Key Roles:
    • BigQuery Masked Reader — can see masked (obscured) column data
    • Data Catalog Fine-Grained Reader — can see unmasked (original) column data
    • Users with neither role get permission denied
  • Up to nine data policies can be configured per policy tag.

Differential Privacy

  • BigQuery supports differential privacy, an anonymization technique that limits the personal information revealed by query outputs.
  • Allows statistical queries on datasets while preventing identification of individual records.
  • Uses a privacy budget (epsilon) to limit how much information can be extracted from the data.
  • Supports differential privacy analysis rules that can be applied to shared datasets, particularly in data clean rooms.
  • Can be extended to multi-cloud data sources and external differential privacy libraries.

VPC Service Controls for BigQuery

  • VPC Service Controls create a security perimeter around BigQuery resources to prevent data exfiltration.
  • Controls data export from BigQuery to Cloud Storage or other targets.
  • Prevents data from being copied to unauthorized resources outside the perimeter using service operations.
  • Restricts resource access to allowed IP addresses, identities, and trusted client devices.
  • BigQuery supports regional endpoints to ensure data stays within a specific region.

Data Governance with Dataplex Universal Catalog

  • BigQuery’s data governance capabilities are powered by Dataplex Universal Catalog (also known as Knowledge Catalog), which replaced Data Catalog (deprecated Feb 2025, discontinued Jan 30, 2026).
  • Provides unified, AI-powered data cataloging integrating discovery, security, and metastore capabilities.
  • Policy tags and policy tag taxonomies used for column-level access control in BigQuery are NOT deprecated — they continue to work with Knowledge Catalog.
  • Supports column-level lineage for BigQuery data at no extra cost.
  • Sensitive Data Protection (formerly Cloud DLP) integrates with Dataplex for automated discovery and classification of sensitive data across BigQuery datasets.

GCP Certification Exam Practice Questions

  • Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
  • GCP services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
  • GCP exam questions are not updated to keep up the pace with GCP updates, so even if the underlying feature has changed the question might not be updated
  • Open to further feedback, discussion and correction.
  1. You have multiple Data Analysts who work with the dataset hosted in BigQuery within the same project. As a BigQuery Administrator, you are required to grant the data analyst only the privilege to create jobs/queries and the ability to cancel self-submitted jobs. Which role should assign to the user?
    1. User
    2. Jobuser
    3. Owner
    4. Viewer
  2. Your analytics system executes queries against a BigQuery dataset. The SQL query is executed in batch and passes the contents of a SQL file to the BigQuery CLI. Then it redirects the BigQuery CLI output to another process. However, you are getting a permission error from the BigQuery CLI when the queries are executed. You want to resolve the issue. What should you do?
    1. Grant the service account BigQuery Data Viewer and BigQuery Job User roles.
    2. Grant the service account BigQuery Data Editor and BigQuery Data Viewer roles.
    3. Create a view in BigQuery from the SQL query and SELECT * from the view in the CLI.
    4. Create a new dataset in BigQuery, and copy the source table to the new dataset. Query the new dataset and table from the CLI.
  3. You are responsible for the security and access control to a BigQuery dataset hosted within a project. Multiple users from multiple teams need to have access to the different tables within the dataset. How can access be controlled?
    1. Create Authorized views for tables in a separate project and grant access to the teams
    2. Create Authorized views for tables in the same project and grant access to the teams
    3. Create Materialized views for tables in a separate project and grant access to the teams
    4. Create Materialized views for tables in the same project and grant access to the teams
  4. Your organization stores sensitive PII data in BigQuery. The security team wants different user groups to see different levels of data: the accounting team should see full SSN values, the analytics team should see hashed values for joining purposes, and general users should not see the data at all. What BigQuery feature should you use?
    1. Row-level security policies
    2. Authorized views with different SQL queries per team
    3. Dynamic data masking with policy tags and multiple data policies
    4. Column-level encryption with CMEK
  5. You need to prevent BigQuery data from being copied to unauthorized projects outside your organization while still allowing legitimate cross-project queries within your organization. What should you implement?
    1. Row-level access policies
    2. Column-level security with policy tags
    3. Authorized datasets
    4. VPC Service Controls with a service perimeter
  6. Your company wants to share a BigQuery dataset with a partner organization for analytics while ensuring that individual records cannot be identified. The privacy team requires mathematical guarantees of privacy protection. Which feature should you use?
    1. Dynamic data masking with SHA-256 hash
    2. Column-level security with authorized views
    3. Differential privacy with analysis rules in a data clean room
    4. Row-level security to filter out sensitive records

References

Google Cloud Data Analytics Services Cheat Sheet

~ Last updated on : ~ jayendrapatil

Cloud Pub/Sub

  • Pub/Sub is a fully managed, asynchronous messaging service designed to be highly reliable and scalable with latencies on the order of 100 ms
  • Pub/Sub offers at-least-once message delivery and best-effort ordering to existing subscribers
  • Pub/Sub also supports exactly-once delivery (GA since 2022) for pull subscriptions and StreamingPull API, ensuring messages are not redelivered after successful acknowledgment. Push and export subscriptions do not support exactly-once delivery.
  • Pub/Sub enables the creation of event producers and consumers, called publishers and subscribers.
  • Pub/Sub messages should be no greater than 10MB in size.
  • Messages can be received with pull or push delivery.
  • Messages published before a subscription is created will not be delivered to that subscription
  • Acknowledged messages are no longer available to subscribers and are deleted, by default. However, can be retained setting retention period.
  • Publishers can send messages with an ordering key and message ordering is set, Pub/Sub delivers the messages in order.
  • Pub/Sub support encryption at rest and encryption in transit.
  • Seek feature allows subscribers to alter the acknowledgment state of messages in bulk to replay or purge messages in bulk.
  • Supports BigQuery subscriptions to write messages directly to BigQuery tables without additional processing.
  • Supports Cloud Storage subscriptions to write messages to Cloud Storage buckets in Avro or Text format.
  • Message filtering allows subscribers to receive a subset of messages published to a topic using filter expressions.
  • Pub/Sub Lite is deprecated (EOL March 18, 2026). Migrate to standard Pub/Sub for cost-effective messaging.

BigQuery

  • BigQuery is a fully managed, durable, petabyte scale, serverless, highly scalable, and cost-effective multi-cloud data warehouse that has evolved into an AI data platform.
  • supports a standard SQL dialect. Legacy SQL is deprecated — effective June 1, 2026, BigQuery limits legacy SQL use for organizations that have not used it between Nov 2025–Jun 2026.
  • automatically replicates data and keeps a seven-day history of changes (time travel), allowing easy restoration and comparison of data from different times
  • supports federated data and can process external data sources in GCS for Parquet and ORC open-source file formats, transactional databases (Bigtable, Cloud SQL), or spreadsheets in Drive without moving the data.
  • BigLake provides a unified storage engine for data lakehouse workloads, supporting Apache Iceberg tables with fine-grained governance across multiple engines (Spark, Flink, Trino, BigQuery).
  • Data model consists of Datasets, tables
  • BigQuery performance can be improved using Partitioned tables and Clustered tables.
  • BigQuery encrypts all data at rest and supports encryption in transit.
  • BigQuery Data Transfer Service automates data movement into BigQuery on a scheduled, managed basis
  • BigQuery Editions (Standard, Enterprise, Enterprise Plus) provide different capability tiers with slot-based pricing, autoscaling reservations, and capacity commitments.
  • BigQuery Studio provides a unified workspace with SQL editor, notebooks (Colab Enterprise), and data canvas for end-to-end analytics workflows.
  • BigQuery ML (BQML) allows building and deploying ML models using SQL, including integration with Gemini and Vertex AI for generative AI tasks like text summarization, sentiment analysis, and embeddings.
  • Vector Search enables similarity search using embeddings directly in BigQuery, supporting RAG applications, semantic search, and KNN-based retrieval without needing external vector databases.
  • BI Engine provides in-memory caching and vectorized processing for sub-second query response times, accelerating dashboards and visualization tools.
  • Best Practices
    • Control projection, avoid select *
    • Estimate costs as queries are billed according to the number of bytes read and the cost can be estimated using --dry-run feature
    • Use the maximum bytes billed setting to limit query costs.
    • Use clustering and partitioning to reduce the amount of data scanned.
    • Avoid repeatedly transforming data via SQL queries. Materialize the query results in stages.
    • Use streaming inserts only if the data must be immediately available as streaming data is charged.
    • Prune partitioned queries, use the _PARTITIONTIME pseudo column to filter the partitions.
    • Denormalize data whenever possible using nested and repeated fields.
    • Avoid external data sources, if query performance is a top priority
    • Avoid using Javascript user-defined functions
    • Optimize Join patterns. Start with the largest table.
    • Use the expiration settings to remove unneeded tables and partitions
    • Keep the data in BigQuery to take advantage of the long-term storage cost benefits rather than exporting to other storage options.
    • Use BigQuery editions with autoscaling for predictable costs and optimal performance.

Bigtable

  • Bigtable is a fully managed, scalable, wide-column NoSQL database service with up to 99.999% availability.
  • ideal for applications that need very high throughput and scalability for key/value data, where each value is max. of 10 MB.
  • supports high read and write throughput at low latency and provides consistent sub-10ms latency – handles millions of requests/second
  • is a sparsely populated table that can scale to billions of rows and thousands of columns,
  • supports storage of terabytes or even petabytes of data
  • is not a relational database. It does not support joins or multi-row transactions.
  • Now supports GoogleSQL for querying data, including window functions for advanced analytic operations (GA 2026).
  • handles upgrades and restarts transparently, and it automatically maintains high data durability.
  • scales linearly in direct proportion to the number of nodes in the cluster
  • stores data in tables, which is composed of rows, each of which typically describes a single entity, and columns, which contain individual values for each row.
  • Each table has only one index, the row key. There are no secondary indices. Each row key must be unique.
  • Single-cluster Bigtable instances provide strong consistency.
  • Multi-cluster instances, by default, provide eventual consistency but can be configured to provide read-over-write consistency or strong consistency, depending on the workload and app profile settings
  • Bigtable Editions (Enterprise and Enterprise Plus) provide advanced features for performance, analytic query capabilities, and resource management (GA 2026).
  • Data Boost provides serverless compute for analytical queries without impacting operational workloads, eliminating the need for multiple data copies.
  • In-memory tier delivers hotspot resistance supporting up to 120,000 queries per second on a single row for ultra-low latency use cases.

Cloud Dataflow

  • Cloud Dataflow is a managed, serverless service for unified stream and batch data processing requirements
  • provides Horizontal autoscaling to automatically choose the appropriate number of worker instances required to run the job.
  • is based on Apache Beam, an open-source, unified model for defining both batch and streaming-data parallel-processing pipelines.
  • supports Windowing which enables grouping operations over unbounded collections by dividing the collection into windows of finite collections according to the timestamps of the individual elements.
  • supports drain feature to deploy incompatible updates
  • Runner v2 supports cross-language transforms, allowing use of Java transforms from Python pipelines and vice versa.
  • Dataflow Prime provides advanced features including Job Visualizer, Smart Recommendations, vertical autoscaling, and right-fitting for optimal resource utilization.
  • GPU and TPU support (TPU V5E, V5P, V6E) enables running high-volume, low-latency ML inference workloads directly within Dataflow jobs.
  • Global Compute enables enormous scaling by dynamically scheduling workloads across Google’s global infrastructure, automatically determining optimal locations based on data locality and resource availability.
  • Speculative Execution for batch pipelines mitigates the impact of slow-running tasks (stragglers) by launching redundant executions.
  • Scales to 4,000 workers per job and routinely processes petabytes of data.
  • Dataflow SQL was deprecated (July 31, 2024) and is no longer available in Google Cloud CLI as of January 31, 2025.

Managed Service for Apache Spark (formerly Cloud Dataproc)

  • Note: Cloud Dataproc has been rebranded to Managed Service for Apache Spark (2025), consolidating Dataproc on Compute Engine and Google Cloud Serverless for Apache Spark under a unified brand.
  • Managed Service for Apache Spark is a managed Spark and Hadoop service to take advantage of open-source data tools for batch processing, querying, streaming, and machine learning.
  • helps to create clusters quickly, manage them easily, and save money by turning clusters on and off as needed.
  • helps reduce time and money spent on administration and lets you focus on your jobs and your data.
  • has built-in integration with other GCP services, such as BigQuery, Cloud Storage, Bigtable, Cloud Logging, and Monitoring
  • support preemptible instances (now called Spot VMs) that have lower compute prices to reduce costs further.
  • also supports HBase, Flink, Hive WebHcat, Druid, Jupyter, Presto, Solr, Zeppelin, Ranger, Zookeeper, and much more.
  • supports connectors for BigQuery, Bigtable, Cloud Storage
  • can be configured for High Availability by specifying the number of master instances in the cluster
  • All nodes in a High Availability cluster reside in the same zone. If there is a failure that impacts all nodes in a zone, the failure will not be mitigated.
  • supports cluster scaling by increasing or decreasing the number of primary or secondary worker nodes (horizontal scaling)
  • supports Autoscaling that provides a mechanism for automating cluster resource management and enables cluster autoscaling.
  • supports initialization actions in executables or scripts that will run on all nodes in the cluster immediately after the cluster is set up
  • Serverless Spark allows submitting batch workloads without provisioning or managing clusters, with automatic scaling and resource management.
  • Supports Zero-Scale clusters for cost optimization when clusters are idle (2026).
  • Includes Lightning Engine for boosted Spark performance.

Cloud Dataprep

⚠️ SERVICE END OF SUPPORT

Cloud Dataprep by Trifacta reached End of Support in December 2025. Trifacta was acquired by Alteryx, and the service has transitioned to Alteryx Designer Cloud.

Migration Options:

  • Alteryx Designer Cloud — successor to Dataprep by Trifacta with enhanced AI/ML capabilities
  • Cloud Data Fusion — Google Cloud’s managed data integration service for visual ETL/ELT pipelines
  • Dataform — for SQL-based data transformations in BigQuery
  • BigQuery Data Preparations — built-in data preparation within BigQuery Studio
  • Cloud Dataprep by Trifacta was an intelligent data service for visually exploring, cleaning, and preparing structured and unstructured data for analysis, reporting, and machine learning.
  • was fully managed, serverless, and scaled on-demand with no infrastructure to deploy or manage
  • provided easy data preparation with clicks and no code.
  • automatically identified data anomalies & helped take fast corrective action
  • automatically detected schemas, data types, possible joins, and anomalies such as missing values, outliers, and duplicates
  • used Dataflow or BigQuery under the hood, enabling unstructured or structured datasets processing of any size with the ease of clicks, not code

Cloud Datalab

⚠️ SERVICE DEPRECATED

Cloud Datalab was deprecated on September 2, 2022.

Migration Options:

  • Vertex AI Workbench — managed notebook environment with JupyterLab, providing capabilities beyond Datalab with integrated ML workflows
  • Colab Enterprise — collaborative notebook environment integrated with BigQuery Studio
  • Cloud Datalab was a powerful interactive tool created to explore, analyze, transform and visualize data and build machine learning models using familiar languages, such as Python and SQL, interactively.
  • ran on Google Compute Engine and connected to multiple cloud services easily so you could focus on data science tasks.
  • was built on Jupyter (formerly IPython)
  • enabled analysis of the data on Google BigQuery, Cloud Machine Learning Engine, Google Compute Engine, and Google Cloud Storage using Python, SQL, and JavaScript (for BigQuery user-defined functions).

Dataplex / Knowledge Catalog

  • Dataplex (now Knowledge Catalog) is an AI-powered, unified data governance solution for managing, understanding, and governing data and AI assets across Google Cloud.
  • Provides centralized inventory to discover, manage, and govern data across BigQuery, Cloud Storage, Pub/Sub, and Spanner.
  • Supports data products — curated, ready-to-use packages of data assets, documentation, and governance controls assembled to solve specific business problems.
  • Offers automated data discovery, metadata management, data quality checks, data lineage, and semantic search.
  • Integrates with BigQuery Studio for unified governance across analytical workflows.

Related Posts

Google Cloud BigQuery – Data Warehouse & Analytics

~ Last updated on : ~ jayendrapatil

Google Cloud BigQuery

  • Google Cloud BigQuery is a fully managed, peta-byte scale, serverless, highly scalable, and cost-effective multi-cloud data-to-AI platform.
  • BigQuery supports GoogleSQL (standard SQL dialect that is ANSI:2011 compliant), which reduces the need for code rewrites.
  • BigQuery transparently and automatically provides highly durable, replicated storage in multiple locations and high availability with no extra charge and no additional setup.
  • BigQuery supports federated data and can process external data sources in GCS for Parquet and ORC open-source file formats, transactional databases (Bigtable, Cloud SQL, Spanner, AlloyDB), or spreadsheets in Drive without moving the data.
  • BigQuery automatically replicates data and keeps a seven-day history of changes, allowing easy restoration and comparison of data from different times (time travel).
  • BigQuery Data Transfer Service automatically transfers data from external data sources, like Google Marketing Platform, Google Ads, YouTube, external sources like S3 or Teradata, and partner SaaS applications to BigQuery on a scheduled and fully managed basis.
  • BigQuery provides a REST API for easy programmatic access and application integration. Client libraries are available in Java, Python, Node.js, C#, Go, Ruby, and PHP.
  • BigQuery provides three editions — Standard, Enterprise, and Enterprise Plus — each offering different feature sets and pricing tiers for capacity-based compute.
  • BigQuery supports both on-demand pricing (pay per TiB processed) and capacity-based pricing (pay per slot-hour via editions).

⚠️ Legacy SQL Deprecation Notice

Legacy SQL is being phased out. For organizations and projects that don’t use legacy SQL between November 1, 2025, and June 1, 2026, legacy SQL becomes unavailable after the evaluation period ends. Migrate all queries to GoogleSQL (Standard SQL).

BigQuery Key Features (2024-2026 Updates)

  • Gemini in BigQuery — AI-powered SQL code generation, explanation, Python code generation, data canvas, data insights, and partitioning/clustering recommendations (GA since 2024).
  • BigQuery Data Canvas — Visual, natural-language-driven workspace for data discovery, preparation, querying, and visualization all within BigQuery.
  • Vector Search — Native vector search with embedding generation, vector indexes, and semantic search capabilities for RAG and AI applications (GA since 2024).
  • BigQuery Graph — Graph analytics solution for modeling, analyzing, and visualizing massive-scale relationships using graph queries (Preview 2025).
  • Continuous Queries — Real-time, always-on queries that process streaming data as it arrives, enabling true real-time analytics (Enterprise/Enterprise Plus editions).
  • Apache Iceberg Managed Tables — Fully managed tables using open Iceberg format stored in customer-owned buckets, with interoperability across open-source engines.
  • BigQuery Studio — Unified workspace for SQL, Python notebooks, Spark, and data pipelines in a single interface.
  • Change Data Capture (CDC) — Native CDC ingestion via Storage Write API for streaming row-level changes (inserts, updates, deletes) directly.
  • MCP Server — Model Context Protocol server for connecting LLMs and AI agents directly to BigQuery.
  • Multimodal Data Analysis — Analyze images, audio, video, and documents directly using SQL with Gemini integration.

BigQuery Resources

BigQuery Resources

Datasets

  • Datasets are the top-level containers used to organize and control access to the BigQuery tables and views.
  • Datasets frequently map to schemas in standard relational databases and data warehouses.
  • Datasets are scoped to the Cloud project.
  • A dataset is bound to a location and can be defined as
    • Regional: A specific geographic place, such as London.
    • Multi-regional: A large geographic area, such as the United States, that contains two or more geographic places.
  • Dataset location can be set only at the time of its creation.
  • A query can contain tables or views from different datasets in the same location.
  • Dataset names must be unique for each project.
  • BigQuery supports cross-region dataset replication for disaster recovery and data residency requirements.
  • BigQuery supports dataset data retention (time travel) allowing access to data at any point within the configured window.

Tables

  • BigQuery tables are row-column structures that hold the data.
  • A BigQuery table contains individual records organized in rows. Each record is composed of columns (also called fields).
  • Every table is defined by a schema that describes the column names, data types, and other information.
  • BigQuery has the following types of tables:
    • Native tables: Tables backed by native BigQuery storage.
    • External tables: Tables backed by storage external to BigQuery.
    • BigLake tables: Tables backed by external storage with fine-grained access control and metadata caching.
    • Apache Iceberg managed tables: Fully managed tables using open Iceberg format in customer-owned buckets.
    • Views: Virtual tables defined by a SQL query.
  • Schema of a table can either be defined during creation or specified in the query job or load job that first populates it with data.
  • Schema auto-detection is also supported when data is loaded from BigQuery or an external data source. BigQuery makes a best-effort attempt to automatically infer the schema for CSV and JSON files.
  • Columns datatype cannot be changed once defined (except for supported type widening conversions in Iceberg tables).
  • BigQuery supports table clones (lightweight, writable copies) and table snapshots (point-in-time read-only copies) for efficient data management.
  • BigQuery supports primary and foreign keys for query optimization (used by the optimizer for better join planning).

Partitioned Tables

  • A partitioned table is a special table that is divided into segments, called partitions, that make it easier to manage and query your data.
  • By dividing a large table into smaller partitions, query performance and costs can be controlled by reducing the number of bytes read by a query.
  • BigQuery tables can be partitioned by:
    • Time-unit column: Tables are partitioned based on a TIMESTAMP, DATE, or DATETIME column in the table.
    • Ingestion time: Tables are partitioned based on the timestamp when BigQuery ingests the data.
    • Integer range: Tables are partitioned based on an integer column.
  • If a query filters on the value of the partitioning column, BigQuery can scan the partitions that match the filter and skip the remaining partitions. This process is called pruning.
  • BigQuery provides partition and cluster recommendations based on query patterns to help optimize table layouts automatically.

Clustered Tables

  • With Clustered tables, the table data is automatically organized based on the contents of one or more columns in the table’s schema.
  • Columns specified are used to colocate the data.
  • Clustering can be performed on multiple columns, where the order of the columns is important as it determines the sort order of the data.
  • Clustering can improve query performance for specific filter queries or ones that aggregate data as BigQuery uses the sorted blocks to eliminate scans of unnecessary data.
  • Clustering does not provide cost guarantees before running the query.
  • Partitioning can be used with clustering where data is first partitioned and then data in each partition is clustered by the clustering columns. When the table is queried, partitioning sets an upper bound of the query cost based on partition pruning.
  • BigQuery supports automatic clustering for Iceberg managed tables, which optimizes file layout without manual configuration.

Views

  • A View is a virtual table defined by a SQL query.
  • View query results contain data only from the tables and fields specified in the query that defines the view.
  • Views are read-only and do not support DML queries.
  • Dataset that contains the view and the dataset that contains the tables referenced by the view must be in the same location.
  • View does not support BigQuery job that exports data.
  • View does not support JSON API to retrieve data from the view.
  • Standard SQL and legacy SQL queries cannot be mixed.
  • Legacy SQL view cannot be automatically updated to standard SQL syntax.
  • No user-defined functions allowed.
  • No wildcard table references allowed.
  • BigQuery supports authorized views to share query results with particular users/groups without giving access to underlying tables.

Materialized Views

  • Materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency.
  • BigQuery leverages pre-computed results from materialized views and whenever possible reads only delta changes from the base table to compute up-to-date results.
  • Materialized views can be queried directly or can be used by the BigQuery optimizer to process queries to the base tables (smart tuning).
  • Materialized views queries are generally faster and consume fewer resources than queries that retrieve the same data only from the base table.
  • Materialized views can significantly improve the performance of workloads that have the characteristic of common and repeated queries.
  • BigQuery supports automatic refresh and manual refresh of materialized views.
  • BigQuery provides materialized view recommendations based on query history to suggest views that would improve performance.
  • Creating and refreshing materialized views requires Enterprise edition or higher (Standard edition can only query existing materialized views directly).

Jobs

  • Jobs are actions that BigQuery runs on your behalf to load data, export data, query data, or copy data.
  • Jobs are not linked to the same project that the data is stored in. However, the location where the job can execute is linked to the dataset location.

External Data Sources

  • An external data source (federated data source) is a data source that can be queried directly even though the data is not stored in BigQuery.
  • Instead of loading or streaming the data, a table can be created that references the external data source.
  • BigQuery offers support for querying data directly from:
    • Cloud Bigtable
    • Cloud Storage
    • Google Drive
    • Cloud SQL
    • Spanner
    • AlloyDB
    • Amazon S3 (via BigQuery Omni)
    • Azure Blob Storage (via BigQuery Omni)
  • Supported formats are:
    • Avro
    • CSV
    • JSON (newline delimited only)
    • ORC
    • Parquet
    • Apache Iceberg
  • External data sources use cases
    • Loading and cleaning the data in one pass by querying the data from an external data source and writing the cleaned result into BigQuery storage.
    • Having a small amount of frequently changing data that needs to be joined with other tables. As an external data source, the frequently changing data does not need to be reloaded every time it is updated.
  • Permanent vs Temporary external tables
    • The external data sources can be queried in BigQuery by using a permanent table or a temporary table.
    • Permanent Table
      • is a table that is created in a dataset and is linked to the external data source.
      • access controls can be used to share the table with others who also have access to the underlying external data source.
    • Temporary Table
      • you submit a command that includes a query and creates a non-permanent table linked to the external data source.
      • no table is created in the BigQuery datasets.
      • cannot be shared with others.
      • Querying an external data source using a temporary table is useful for one-time, ad-hoc queries over external data, or for extract, transform, and load (ETL) processes.
  • Limitations
    • does not guarantee data consistency for external data sources.
    • query performance for external data sources may not be as high as querying data in a native BigQuery table.
    • cannot reference an external data source in a wildcard table query.
    • support table partitioning or clustering in limited ways.
    • results are not cached and would be charged for each query execution.
  • Metadata Caching — BigQuery supports metadata caching for external tables to improve query planning performance and reduce latency.

BigQuery Editions

  • BigQuery provides three editions for capacity-based pricing, each with different feature sets:
    • Standard — Autoscaling only (max 1,600 slots per reservation), no capacity commitments, 99.9% SLO. No access to BigQuery ML, continuous queries, BigQuery Graph, or fine-grained security controls.
    • Enterprise — Autoscaling + Baseline slots, advanced workload management, BigQuery ML, continuous queries, BigQuery Omni, VPC Service Controls, CMEK, column/row-level security, 99.99% SLO. Supports 1-year (20% discount) and 3-year (40% discount) commitments.
    • Enterprise Plus — All Enterprise features plus managed disaster recovery, Assured Workloads compliance controls, 99.99% SLO.
  • BigQuery also offers on-demand pricing at $6.25 per TiB processed (first 1 TiB/month free), which includes BigQuery ML, CMEK, and fine-grained security.
  • Editions are a property of reservations (compute), not storage. Datasets and tables are unaffected by edition choice.
  • To change an edition, you must delete and recreate the reservation.
  • Slots autoscaling automatically scales capacity to accommodate workload demands without pre-provisioning.
  • BigQuery fluid scaling removes the 1-minute minimum duration for slot usage.

BigLake

  • BigLake is a storage engine that unifies data warehouses and data lakes by extending BigQuery’s fine-grained security and governance to data stored in Cloud Storage, Amazon S3, and Azure Blob Storage.
  • BigLake tables provide:
    • Fine-grained access control (column-level and row-level security) on external data.
    • Metadata caching for improved query performance on external data.
    • Support for open formats (Parquet, ORC, Avro, JSON, CSV, Iceberg).
    • Consistent governance across multi-cloud storage.
  • Apache Iceberg Managed Tables (formerly BigLake tables for Apache Iceberg) provide:
    • Fully managed experience similar to native BigQuery tables but with data stored in customer-owned buckets.
    • DML support, streaming via Storage Write API, schema evolution.
    • Automatic storage optimization (adaptive file sizing, automatic clustering, garbage collection).
    • Iceberg V2 snapshot export for interoperability with Spark, Flink, Presto, and other engines.
    • Time travel, column-level security, and data masking.

BigQuery Omni

  • BigQuery Omni is a multi-cloud analytics solution that lets you query data across Google Cloud, AWS, and Azure without moving data.
  • Uses BigLake tables to access data stored in Amazon S3 and Azure Blob Storage.
  • Supports cross-cloud joins — query data across different clouds in a single SQL statement.
  • Supports cross-cloud transfer — move data between clouds for consolidation.
  • Available in Enterprise edition with on-demand pricing.
  • Compute runs locally in the cloud where data resides (data never leaves the cloud it’s stored in).

BigQuery AI and ML

  • BigQuery ML allows building and deploying ML models directly within BigQuery using SQL, supporting classification, regression, clustering, forecasting, recommendation, and anomaly detection.
  • Gemini Integration — BigQuery integrates with Gemini models via Vertex AI for:
    • Text generation and summarization using ML.GENERATE_TEXT
    • Embedding generation using ML.GENERATE_EMBEDDING
    • Image, audio, and video analysis
    • Document processing and OCR
    • Sentiment analysis and text understanding
  • Generative AI Functions — SQL functions like AI.GENERATE for calling AI models directly from queries.
  • Remote Models — Connect to models hosted on Vertex AI, Cloud AI services, or custom endpoints.
  • Gemini in BigQuery (Assistive AI) — AI-powered assistance for:
    • SQL code generation and explanation
    • Python code generation in notebooks
    • Data insights and recommendations
    • Partitioning and clustering recommendations
  • Requires Enterprise edition or higher (not available in Standard edition).

BigQuery Vector Search

  • Vector Search enables semantic search and similarity matching directly in BigQuery using vector embeddings.
  • Supports generating embeddings using Vertex AI models, then searching with the VECTOR_SEARCH function.
  • Vector Indexes optimize search performance for large-scale embedding datasets (requires Enterprise edition or higher for index acceleration).
  • Use cases include: RAG (Retrieval-Augmented Generation), product recommendations, anomaly detection, multi-modal search, and semantic analysis.
  • Supports automated embedding generation that automatically generates and maintains embeddings as data changes.
  • Eliminates the need for specialized vector databases for many use cases.

BigQuery Graph

  • BigQuery Graph (Preview 2025) provides graph analytics capabilities for modeling, analyzing, and visualizing relationships in data.
  • Enables multi-hop traversals, path finding, and relationship analysis that would require complex nested JOINs in traditional SQL.
  • Use cases: fraud detection, social network analysis, supply chain mapping, knowledge graphs.
  • Includes a visual graph modeler for schema design and graph visualization tools.
  • Available in Enterprise and Enterprise Plus editions only.

BigQuery Continuous Queries

  • Continuous queries are always-on queries that process data in real-time as it arrives via streaming.
  • Enables true real-time analytics without the latency of batch processing.
  • Supports stream-to-stream joins and window aggregations.
  • Can export results to Pub/Sub, Bigtable, or other BigQuery tables for downstream consumption.
  • Available in Enterprise and Enterprise Plus editions only.
  • Uses dedicated CONTINUOUS assignment type in reservations.

BigQuery Security

Refer blog post @ BigQuery Security

BigQuery Best Practices

  • Cost Control
    • Query only the needed columns and avoid select * as BigQuery does a full scan of every column in the table.
    • Don’t run queries to explore or preview table data. Use preview option.
    • Before running queries, preview them to estimate costs. Queries are billed according to the number of bytes read and the cost can be estimated using --dry-run feature.
    • Use the maximum bytes billed setting to limit query costs.
    • Use clustering and partitioning to reduce the amount of data scanned.
    • For non-clustered tables, do not use a LIMIT clause as a method of cost control. Applying a LIMIT clause to a query does not affect the amount of data read, but shows limited results only. With a clustered table, a LIMIT clause can reduce the number of bytes scanned.
    • Partition the tables by date which helps query relevant subsets of data which improves performance and reduces costs.
    • Materialize the query results in stages. Break the query into stages where each stage materializes the query results by writing them to a destination table.
    • Use streaming inserts only if the data must be immediately available as streaming data is charged.
    • Consider editions with capacity-based pricing for predictable workloads to reduce costs vs. on-demand.
    • Use commitment plans (1-year at 20% or 3-year at 40% discount) for sustained workloads.
  • Query Performance
    • Control projection, Query only the needed columns. Avoid SELECT *.
    • Prune partitioned queries, use the _PARTITIONTIME pseudo column to filter the partitions.
    • Denormalize data whenever possible using nested and repeated fields.
    • Avoid external data sources, if query performance is a top priority.
    • Avoid repeatedly transforming data via SQL queries, use materialized views instead.
    • Avoid using Javascript user-defined functions.
    • Optimize Join patterns. Start with the largest table.
    • Use BI Engine for sub-second query response on dashboards and BI tools (Enterprise edition+).
    • Use search indexes for full-text search acceleration on large text columns.
    • Use vector indexes to accelerate vector search on embedding columns.
    • Leverage history-based optimizations where BigQuery automatically optimizes repeated query patterns.
  • Optimizing Storage
    • Use the expiration settings to remove unneeded tables and partitions.
    • Keep the data in BigQuery to take advantage of the long-term storage cost benefits rather than exporting to other storage options.
    • Long-term storage pricing automatically applies to data not modified for 90 consecutive days.
    • Use physical storage billing (compressed bytes) instead of logical storage billing for better cost efficiency on compressed data.

BigQuery Data Transfer Service

Refer GCP blog post @ Google Cloud BigQuery Data Transfer Service

GCP Certification Exam Practice Questions

  • Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
  • GCP services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
  • GCP exam questions are not updated to keep up the pace with GCP updates, so even if the underlying feature has changed the question might not be updated.
  • Open to further feedback, discussion and correction.
  1. A user wishes to generate reports on petabyte-scale data using Business Intelligence (BI) tools. Which storage option provides integration with BI tools and supports OLAP workloads up to petabyte-scale?
    1. Bigtable
    2. Cloud Datastore
    3. Cloud Storage
    4. BigQuery
  2. Your company uses Google Analytics for tracking. You need to export the session and hit data from a Google Analytics 360 reporting view on a scheduled basis into BigQuery for analysis. How can the data be exported?
    1. Configure a scheduler in Google Analytics to convert the Google Analytics data to JSON format, then import directly into BigQuery using bq command line.
    2. Use gsutil to export the Google Analytics data to Cloud Storage, then import into BigQuery and schedule it using Cron.
    3. Import data to BigQuery directly from Google Analytics using Cron.
    4. Use BigQuery Data Transfer Service to import the data from Google Analytics.
  3. A company wants to run machine learning models on their BigQuery data without moving it to a separate ML platform. Which feature should they use?
    1. Export data to Vertex AI Workbench
    2. BigQuery ML
    3. Dataflow ML
    4. Cloud Composer with TensorFlow
  4. Your organization needs to query data stored in Amazon S3 from BigQuery without copying the data to Google Cloud. Which feature enables this?
    1. BigQuery Data Transfer Service
    2. Cloud Storage Transfer Service
    3. BigQuery Omni
    4. Storage Transfer Service
  5. A data team needs to perform real-time analytics on streaming data arriving in BigQuery. They need results to update continuously as new data arrives. Which BigQuery feature should they use?
    1. Scheduled queries with 1-minute interval
    2. Materialized views with automatic refresh
    3. Continuous queries
    4. Streaming buffer queries
  6. You need to store data in an open format that can be accessed by both BigQuery and Apache Spark, while maintaining BigQuery’s fully managed experience. Which table type should you use?
    1. External tables on Cloud Storage
    2. BigQuery native tables
    3. Apache Iceberg managed tables (BigLake)
    4. Bigtable federated tables
  7. A company wants to build a semantic search application using product descriptions stored in BigQuery. They need to find products based on meaning rather than exact keyword matches. Which BigQuery feature should they use?
    1. SEARCH function with search indexes
    2. LIKE operator with wildcard patterns
    3. Full-text search with text analyzers
    4. Vector Search with ML.GENERATE_EMBEDDING
  8. Which BigQuery edition is required to use BigQuery ML and continuous queries?
    1. Standard edition
    2. Enterprise edition or higher
    3. Enterprise Plus edition only
    4. On-demand pricing only

References

Google Cloud Storage & Database Options Comparison

~ Last updated on : ~ jayendrapatil ~ 2 Comments

GCP Storage Options

GCP provides various storage options and the selection can be based on

  • Structured vs Unstructured
  • Relational (SQL) vs Non-Relational (NoSQL)
  • Transactional (OLTP) vs Analytical (OLAP)
  • Fully Managed vs Requires Provisioning
  • Global vs Regional
  • Horizontal vs Vertical scaling

Cloud Firestore

  • Cloud Firestore is a fully managed, highly scalable, serverless, non-relational NoSQL document database
  • fully managed with no-ops and no planned downtime and no need to provision database instances (vs Bigtable)
  • uses a distributed architecture to automatically manage scaling.
  • queries scale with the size of the result set, not the size of the data set
  • supports ACID Atomic transactions – all or nothing (vs Bigtable)
  • provides High availability of reads and writes – runs in Google data centers, which use redundancy to minimize impact from points of failure.
  • provides massive scalability with high performance – uses a distributed architecture to automatically manage scaling.
  • scales from zero to terabytes with flexible storage and querying of data
  • provides SQL-like query language
  • supports strong consistency
  • supports data encryption at rest and in transit
  • provides terabytes of capacity with a maximum unit size of 1 MB per entity (vs Bigtable)
  • Firestore Editions (2025)
    • Standard edition – core Firestore capabilities with standard querying support
    • Enterprise edition – provides MongoDB API compatibility, a new pipeline query engine with 200+ query operations, additional data types, new index types, and text/geospatial search
  • Enterprise Edition Features
    • MongoDB Compatibility (GA Aug 2025) – use existing MongoDB application code, drivers, and tools as a drop-in replacement while getting Firestore’s auto-scaling and high availability
    • Pipeline Query Engine – supports 200+ new query capabilities (pipeline operations) for complex queries directly within the database
    • Text Search and Geospatial Search – native full-text and geospatial query support without external services
    • Maximum document size increased to 16 MiB (Enterprise edition)
    • Indexes are not required for queries in Enterprise edition
  • Consider using Cloud Firestore if you need to store semi-structured objects, or if require support for transactions and SQL-like queries.

Cloud Bigtable

  • Bigtable provides a scalable, fully managed, non-relational NoSQL wide-column analytical big data database service suitable for both low-latency single-point lookups and precalculated analytics.
  • supports large quantities (>1 TB) of semi-structured or structured data (vs Datastore)
  • supports high throughput or rapidly changing data (vs BigQuery)
  • managed, but needs provisioning of nodes and can be expensive (vs Datastore and BigQuery)
  • does not support transactions or strong relational semantics (vs Datastore)
  • Now supports GoogleSQL queries (GA 2024) – familiar SQL syntax for querying Bigtable data directly
  • Not Transactional and does not support ACID
  • provides eventual consistency
  • ideal for time-series or natural semantic ordering data
  • can run asynchronous batch or real-time processing on the data
  • can run machine learning algorithms on the data
  • provides petabytes of capacity with a maximum unit size of 10 MB per cell and 100 MB per row.
  • Bigtable Editions (GA April 2026)
    • Enterprise edition – advanced features in performance, analytic query capability, and resource management
    • Enterprise Plus edition – includes in-memory tier with sub-millisecond latency and hotspot resistance supporting up to 120,000 queries per second on a single row
  • New Features (2024-2026)
    • Bigtable SQL (GoogleSQL) – query data using familiar SQL syntax with specialized features preserving flexible schema
    • Data Boost – serverless analytical queries without impacting operational workloads
    • Incremental Materialized Views – simplify creation of real-time metrics
    • Window Functions (GA April 2026) – advanced analytic operations over multiple table rows
    • KNN Vector Search – K nearest neighbors similarity search for AI/ML use cases
    • Distributed Counting – instant metric retrieval for real-time dashboards
    • In-Memory Tier – hotspot resistance with sub-millisecond latency
    • Agent Skills (April 2026) – let AI agents assist with schema design, SQL queries, and infrastructure management
  • Usage Patterns
    • Low-latency read/write access
    • High-throughput data processing
    • Time series support
  • Anti Patterns
    • Not an ideal storage option for future analysis – Use BigQuery instead
    • Not an ideal storage option for transactional data – Use relational database or Datastore
  • Common Use cases
    • IoT, finance, adtech
    • Personalization, recommendations
    • Monitoring
    • Geospatial datasets
    • Graphs
    • Real-time AI/ML inference and vector search
  • Consider using Cloud Bigtable, if you need high-performance datastore to perform analytics on a large number of structured objects

Cloud Storage

  • Cloud Storage provides durable and highly available object storage.
  • fully managed, simple administration, cost-effective, and scalable service that does not require capacity management
  • supports unstructured data storage like binary or raw objects
  • provides high performance, internet-scale
  • supports data encryption at rest and in transit
  • provides 99.999999999% (11 nines) annual durability
  • Storage Classes: Standard, Nearline (30-day min), Coldline (90-day min), Archive (365-day min)
  • Autoclass – automatically transitions objects to appropriate storage classes based on access patterns
  • New Features (2024-2026)
    • Cloud Storage Rapid (2025-2026) – high-performance storage tier for AI/ML workloads
      • Rapid Bucket (formerly Rapid Storage) – zonal object storage with <1ms random read/write latency, 6 TB/s throughput
      • Rapid Cache (formerly Anywhere Cache) – accelerates reads and colocates compute with data, up to 20 Tbps throughput
    • Smart Storage – automated metadata annotation for unstructured data with AI agent connectivity via MCP
    • Storage Intelligence – zero-configuration dashboards, aggregated activity views, and enhanced batch operations
    • Bucket Relocation – move buckets between regions with minimal downtime
    • Batch Operations Dry Run Mode – simulate batch jobs without modifying data
  • Consider using Cloud Storage, if you need to store immutable blobs larger than 10 MB, such as large images or movies. This storage service provides petabytes of capacity with a maximum unit size of 5 TB per object.
  • Usage Patterns
    • Images, pictures, and videos
    • Objects and blobs
    • Unstructured data
    • Long term storage for archival or compliance
    • AI/ML training data and model checkpoints
  • Anti Patterns
    • Not ideal for structured/relational data
    • Not ideal for frequently changing data requiring low-latency updates
  • Common Use cases
    • Storing and streaming multimedia
    • Storage for custom data analytics pipelines
    • Archive, backup, and disaster recovery
    • AI/ML training datasets and model serving

Cloud SQL

  • provides fully managed, relational SQL databases
  • offers MySQL, PostgreSQL, and SQL Server databases as a service
  • manages OS & Software installation, patches and updates, backups and configuring replications, failover however needs to select and provision machines (vs Cloud Spanner)
  • single region only – although it now supports cross-region read replicas (vs Cloud Spanner)
  • Cloud SQL Editions
    • Enterprise edition – core capabilities, suitable for applications with less stringent availability/performance requirements. Up to 96 vCPU, 624 GB RAM.
    • Enterprise Plus edition – highest performance with optimized software/hardware stack. Up to 128 vCPU, 864 GB RAM. Includes data cache, up to 35-day point-in-time log retention, sub-second maintenance downtime, and advanced disaster recovery.
  • Scaling
    • provides vertical scalability (Max. storage of 64 TB)
    • storage can be increased without incurring any downtime
    • provides an option to increase the storage automatically
    • storage CANNOT be decreased
    • supports Horizontal scaling for read-only using read replicas (vs Cloud Spanner)
    • performance is linked to the disk size
  • Security
    • data is encrypted when stored in database tables, temporary files, and backups.
    • external connections can be encrypted by using SSL, or by using the Cloud SQL Proxy.
    • Private Service Connect (PSC) support for simplified private connectivity
  • High Availability
    • fault-tolerance across zones can be achieved by configuring the instance for high availability by adding a failover replica
    • failover is automatic
    • can be created from primary instance only
    • replication from the primary instance to failover replica is semi-synchronous.
    • failover replica must be in the same region as the primary instance, but in a different zone
    • only one instance for every primary instance allowed
    • supports managed backups and backups are created on primary instance only
    • supports automatic replication
    • Enterprise Plus: sub-second maintenance downtime (vs up to 120 seconds for Enterprise)
  • Backups
    • Automated backups can be configured and are stored for 7 days
    • Manual backups (snapshots) can be created and are not deleted automatically
    • Fast Clone (GA) – clone operations within the same zone for rapid environment creation
  • Point-in-time recovery
    • requires binary logging enabled.
    • every update to the database is written to an independent log, which involves a small reduction in write performance.
    • performance of the read operations is unaffected by binary logging, regardless of the size of the binary log files.
    • Enterprise Plus: up to 35-day log retention (vs 7 days for Enterprise)
  • Usage Patterns
    • direct lift and shift for MySQL, PostgreSQL, SQL Server database only
    • relational database service with strong consistency
    • OLTP workloads
  • Anti Patterns
    • need data storage more than 64 TB or horizontal write scaling, use Cloud Spanner
    • need global availability with low latency, use Cloud Spanner
    • not a direct replacement for Oracle – use installation on GCE or consider AlloyDB for PostgreSQL workloads
  • Common Use cases
    • Websites, blogs, and content management systems (CMS)
    • Business intelligence (BI) applications
    • ERP, CRM, and eCommerce applications
    • Geospatial applications
  • Consider using Cloud SQL for full relational SQL support for OLTP and lift and shift of MySQL, PostgreSQL, SQL Server databases

Cloud Spanner

  • Cloud Spanner provides fully managed, relational SQL databases with joins and secondary indexes
  • provides cross-region, global, horizontal scalability, and availability
  • supports strong consistency, including strongly consistent secondary indexes
  • provides high availability through synchronous and built-in data replication.
  • provides strong global consistency
  • supports database sizes exceeding ~2 TB (vs Cloud SQL)
  • does not provide direct lift and shift for relational databases (vs Cloud SQL)
  • expensive as compared to Cloud SQL
  • Multi-Model Database (2024-2025)
    • Spanner Graph (GA Jan 2025) – supports industry-standard Graph Query Language (GQL) with full SQL interoperability for querying structured and connected data
    • Vector Search – native vector embeddings and similarity search for AI/ML and RAG applications
    • Full-Text Search – native text search capabilities without external services
    • Hybrid Search – combine vector search, full-text search, and ML model reranking in a unified platform
    • Vertex AI Integration – native integration for model serving and inferencing with SQL
  • Spanner Omni (2026 Preview)
    • Self-managed version of Spanner that runs on-premises, across clouds, or on a laptop
    • Brings Spanner’s scalability, high availability, strong consistency, and multi-model capabilities anywhere
    • Supports air-gapped or connected deployments, single machine to clusters of thousands
  • Tiered Storage (GA) – store data across SSD or HDD tiers for cost optimization
  • Consider using Cloud Spanner for full relational SQL support, with horizontal scalability spanning petabytes for OLTP, or as a multi-model database supporting relational, graph, vector, and text search workloads

BigQuery

  • provides fully managed, no-ops, OLAP, enterprise data warehouse (EDW) with SQL and fast ad-hoc queries.
  • provides high capacity, data warehousing analytics solution
  • ideal for big data exploration and processing
  • not ideal for operational or transactional databases
  • provides SQL interface
  • A scalable, fully managed data-to-AI platform
  • BigQuery Editions – Standard, Enterprise, and Enterprise Plus with different pricing and feature tiers
  • New Features (2024-2026)
    • Conversational Analytics (Preview Jan 2026) – analyze data using natural language with AI-powered data agents that understand context and generate SQL
    • BigQuery Graph – uncover complex relationships and patterns in data
    • Vector Search – embeddings and hybrid search for RAG applications
    • BigQuery ML – train and run ML models directly in BigQuery using SQL
    • Data Engineering Agent – automates data preparation, error detection, and pipeline building
    • Data Science Agent – automates data loading, feature engineering, model training and evaluation
    • BigQuery Studio – unified workspace with Gemini-powered assistant for resource discovery and query generation
    • MCP Integration – Model Context Protocol for AI agent connectivity
  • Usage Patterns
    • OLAP workloads up to petabyte-scale
    • Big data exploration and processing
    • Reporting via business intelligence (BI) tools
    • AI/ML model training and inference at scale
  • Anti Patterns
    • Not an ideal storage option for transactional data or OLTP – Use Cloud SQL or Cloud Spanner instead
    • Low-latency read/write access – Use Bigtable instead
  • Common Use cases
    • Analytical reporting on large data
    • Data science and advanced analyses
    • Big data processing using SQL
    • GenAI and agentic AI applications with data

AlloyDB for PostgreSQL

  • AlloyDB is a fully managed, PostgreSQL-compatible database designed for enterprise-grade OLTP and hybrid transactional/analytical (HTAP) workloads
  • wire-compatible with PostgreSQL 14 and 15 – existing drivers, ORMs, and most extensions work without modification
  • provides up to 4x faster for transactional workloads and up to 100x faster for analytical queries compared to standard PostgreSQL
  • uses a scale-out architecture with compute and storage separation
  • built-in AI capabilities with Google’s cutting-edge technology
  • AlloyDB AI
    • Generate vector embeddings from within the database
    • Native vector search with up to 10x faster index creation and 4x faster search queries
    • Filtered vector search up to 10x faster than standard PostgreSQL HNSW
    • Integration with Vertex AI for model serving and inferencing
    • AlloyDB AI query engine with Vertex AI Ranking API
  • AlloyDB Omni – downloadable version that runs on-premises or in other clouds
  • 99.99% availability SLA with automated backups, replication, and failover
  • Usage Patterns
    • Enterprise PostgreSQL workloads requiring high performance
    • HTAP (hybrid transactional/analytical) workloads
    • AI-powered applications requiring vector search
    • Migration from commercial databases (Oracle, SQL Server) to PostgreSQL
  • Anti Patterns
    • Need global horizontal scaling – Use Cloud Spanner
    • Need non-relational/NoSQL – Use Firestore or Bigtable
    • Need MySQL or SQL Server compatibility – Use Cloud SQL
  • Consider using AlloyDB for PostgreSQL workloads requiring high performance, AI integration, or migration from commercial databases

Memorystore

  • provides scalable, secure, and highly available in-memory service
  • fully managed as provisioning, replication, failover, and patching are all automated
  • is protected from the internet using VPC networks and private IP and comes with IAM integration
  • Supported Engines
    • Memorystore for Valkey (GA 2025) – open-source, high-performance key-value store (successor to Redis OSS). Supports Valkey 8.0 and 9.0. 99.99% availability SLA, instances up to 14.5 TB, cross-region replication, Private Service Connect, multi-VPC access.
    • Memorystore for Redis Cluster – managed Redis cluster mode with zero-downtime scaling
    • Memorystore for Redis – standard Redis instances (standalone and high availability)
    • Memorystore for Memcached – distributed in-memory caching
  • Valkey 9.0 Features (GA 2026)
    • SIMD optimizations for improved throughput and latency
    • Enhanced performance over previous versions
    • Full compatibility with Redis OSS commands
  • Usage Patterns
    • Lift and shift migration of applications
    • Low latency data caching and retrieval
    • Session management
    • Real-time leaderboards and counting
  • Anti Patterns
    • Relational or NoSQL database
    • Analytics solution
    • Persistent primary data store (use as cache layer)
  • Common Use cases
    • User session management
    • Application caching
    • Real-time analytics and pub/sub
    • Gaming leaderboards

GCP Storage Options Decision Tree

GCP Storage Options Decision Tree

GCP Certification Exam Practice Questions

  • Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
  • GCP services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
  • GCP exam questions are not updated to keep up the pace with GCP updates, so even if the underlying feature has changed the question might not be updated
  • Open to further feedback, discussion and correction.
  1. Your application is hosted across multiple regions and consists of both relational database data and static images. Your database has over 10 TB of data. You want to use a single storage repository for each data type across all regions. Which two products would you choose for this task? (Choose two)
    1. Cloud Bigtable
    2. Cloud Spanner
    3. Cloud SQL
    4. Cloud Storage
  2. You are building an application that stores relational data from users. Users across the globe will use this application. Your CTO is concerned about the scaling requirements because the size of the user base is unknown. You need to implement a database solution that can scale with your user growth with minimum configuration changes. Which storage solution should you use?
    1. Cloud SQL
    2. Cloud Spanner
    3. Cloud Firestore
    4. Cloud Datastore
  3. Your company processes high volumes of IoT data that are time-stamped. The total data volume can be several petabytes. The data needs to be written and changed at a high speed. You want to use the most performant storage option for your data. Which product should you use?
    1. Cloud Datastore
    2. Cloud Storage
    3. Cloud Bigtable
    4. BigQuery
  4. Your App Engine application needs to store stateful data in a proper storage service. Your data is non-relational database data. You do not expect the database size to grow beyond 10 GB and you need to have the ability to scale down to zero to avoid unnecessary costs. Which storage service should you use?
    1. Cloud Bigtable
    2. Cloud Dataproc
    3. Cloud SQL
    4. Cloud Firestore (Datastore mode)
  5. A financial organization wishes to develop a global application to store transactions happening from different part of the world. The storage system must provide low latency transaction support and horizontal scaling. Which GCP service is appropriate for this use case?
    1. Bigtable
    2. Datastore
    3. Cloud Storage
    4. Cloud Spanner
  6. You work for a mid-sized enterprise that needs to move its operational system transaction data from an on-premises database to GCP. The database is about 20 TB in size. Which database should you choose?
    1. Cloud SQL
    2. Cloud Bigtable
    3. Cloud Spanner
    4. Cloud Datastore

    Note: With Cloud SQL now supporting up to 64 TB, Cloud SQL could also be a valid option for 20 TB. However, for operational transactional data requiring high scalability, Cloud Spanner remains the better choice.

  7. Your team needs a PostgreSQL-compatible database that can handle both transactional and analytical queries with high performance. The application also requires built-in vector search capabilities for an AI-powered recommendation engine. Which GCP service should you choose?
    1. Cloud SQL for PostgreSQL
    2. AlloyDB for PostgreSQL
    3. Cloud Spanner
    4. BigQuery
  8. Your company is building a real-time fraud detection system that needs to query relationships between entities (accounts, transactions, merchants) while also performing vector similarity searches on transaction patterns. The system must provide strong consistency and global availability. Which database should you use?
    1. Cloud Bigtable
    2. BigQuery
    3. Cloud Spanner
    4. Cloud Firestore
  9. Your organization is migrating from MongoDB to Google Cloud. You want to minimize code changes and use existing MongoDB drivers and tools. The application requires automatic scaling and high availability. Which GCP service should you use?
    1. Cloud SQL for PostgreSQL
    2. Cloud Bigtable
    3. Cloud Firestore (Enterprise edition with MongoDB compatibility)
    4. AlloyDB for PostgreSQL
  10. You need a high-performance caching layer for your microservices application on GCP. The solution must support cross-region replication, provide 99.99% availability, and be compatible with open-source tooling. Which service should you choose?
    1. Memorystore for Redis
    2. Cloud CDN
    3. Memorystore for Valkey
    4. Cloud Firestore

See also: Google Cloud Storage Services Cheat Sheet