Authorized Views

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 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