Question 1

What is the architecture of BigQuery?

Accepted Answer

BigQuery uses a serverless , distributed architecture that separates storage and compute, allowing each to scale independently. Key architectural components: Dremel Execution Engine : The compute layer that processes SQL queries using a multi level serving tree. Queries are broken into smaller sub queries distributed across thousands of workers (called slots ) for massively parallel execution. Colossus (Distributed Storage) : BigQuery stores data in Google's Colossus distributed file system in a columnar format called Capacitor . Columnar storage enables efficient compression and allows queries to read only the columns they need. Jupiter Network : A petabit scale network that connects compute and storage, enabling extremely fast data shuffling between workers. Borg (Resource Management) : Google's cluster management system allocates compute resources (slots) dynamically based on query demand. How a query executes: 1. The query is received by the root server , which parses and optimizes it 2. The query plan is distributed across leaf nodes (workers) 3. Leaf nodes read data from Colossus in parallel 4. Results are aggregated up the serving tree and returned This separation of storage and compute means you pay for storage independently of query processing, and BigQuery can auto scale compute resources without any infrastructure management.

Question 2

What are the optimization techniques in BigQuery?

Accepted Answer

BigQuery performance can be significantly improved using several optimization techniques : 1. Partitioning Divides a table into segments based on a column (typically a DATE or TIMESTAMP column), an integer range , or ingestion time Queries that filter on the partition column scan only relevant partitions, reducing cost and latency 2. Clustering Sorts data within each partition by up to four columns Most effective on high cardinality columns used in WHERE , JOIN , or GROUP BY clauses 3. Column Pruning Avoid SELECT ; explicitly list only the columns you need BigQuery's columnar storage means fewer columns = less data scanned 4. Materialized Views Precompute and cache results of expensive aggregations BigQuery automatically refreshes them and rewrites queries to use them when possible 5. Avoid Anti Patterns Minimize cross joins and self joins on large tables Use approximate aggregation functions like APPROX COUNT DISTINCT() when exact precision is unnecessary Filter early in CTEs or subqueries to reduce intermediate data 6. Slot Reservations For predictable workloads, purchase flat rate slot reservations instead of on demand pricing to guarantee compute capacity

Question 3

How to encrypt data in GCS?

Accepted Answer

Google Cloud Storage (GCS) provides multiple layers of encryption to protect data both at rest and in transit . Encryption at Rest (three options): Google Managed Encryption Keys (GMEK) : Enabled by default on all GCS objects. Google manages the AES 256 encryption keys automatically with no configuration required. Customer Managed Encryption Keys (CMEK) : You create and manage keys in Cloud KMS . You control rotation schedules and can revoke access by disabling the key. Customer Supplied Encryption Keys (CSEK) : You provide your own encryption key with each API request. Google does not store the key you are fully responsible for key management. Setting up CMEK for a GCS bucket: Encryption in Transit: All data transferred between the client and GCS is encrypted using TLS (HTTPS) by default Data moving between Google data centers is encrypted via ALTS (Application Layer Transport Security) Best Practices: Use CMEK when regulatory or compliance requirements mandate key control Enable key rotation in Cloud KMS (e.g., every 90 days) Use CSEK only when you must retain full custody of encryption keys and accept the operational burden

Question 4

How can you retrieve a deleted table in BigQuery?

Accepted Answer

BigQuery provides a time travel feature that allows you to recover a deleted table within a configurable window. Using Time Travel (up to 7 days): BigQuery retains deleted table data for up to 7 days by default (configurable per dataset). You can restore a deleted table by copying it from a snapshot at a specific timestamp . Using Table Snapshots: For critical tables, you can proactively create table snapshots that persist beyond the time travel window. Key Considerations: The time travel window is configurable from 2 to 7 days at the dataset level (the max time travel hours setting) After the time travel window expires, the data is permanently deleted and cannot be recovered Table snapshots and dataset copies provide longer term protection For mission critical data, schedule regular exports to Cloud Storage as an additional backup layer

Question 5

How can you prevent a table from being deleted in BigQuery?

Accepted Answer

There is no single "delete lock" on a BigQuery table, but several strategies can prevent accidental or unauthorized deletion : 1. IAM Permissions (Primary Defense) Restrict who holds the bigquery.tables.delete permission Use the principle of least privilege : assign roles/bigquery.dataViewer or roles/bigquery.dataEditor instead of roles/bigquery.dataOwner or roles/bigquery.admin Create a custom IAM role that excludes delete permissions 2. Table Snapshots Periodically create table snapshots so data can be restored even if the source table is deleted 3. Organization Policy Constraints Use Organization Policy Service to restrict certain operations at the folder or organization level 4. Authorized Views and Datasets Expose data through authorized views so users interact with views rather than the underlying tables, reducing risk of accidental deletion 5. Audit Logging and Alerts Enable Cloud Audit Logs for BigQuery data access Create a log based alert that triggers when a tableDelete event occurs Best Practice: Combine IAM restrictions with automated snapshots and audit alerts for defense in depth.

Question 6

What are Materialized Views and Logical (Generic) Views in BigQuery?

Accepted Answer

BigQuery supports two types of views: Logical Views (standard views) and Materialized Views . They serve different purposes and have distinct performance and cost characteristics. Logical (Standard) Views: A saved SQL query that is executed every time the view is queried No data is stored the underlying query runs on each access Always returns fresh results since it reads directly from base tables Useful for access control (exposing a subset of columns/rows) and query simplification Materialized Views: Precomputed and cached results stored physically in BigQuery BigQuery automatically refreshes them when base table data changes (incremental refresh) Queries against materialized views are faster and cheaper because they read cached results BigQuery's smart tuning can automatically rewrite queries to use a materialized view even if the query references the base table Comparison: Feature Logical View Materialized View Data Storage None (query only) Cached results stored Freshness Always current Auto refreshed (near real time) Query Speed Same as base query Faster (precomputed) Cost Billed per query execution Storage cost + reduced query cost Use Case Access control, abstraction Expensive aggregations, dashboards When to use each: Use logical views for row/column level security, query reuse, and when freshness is paramount Use materialized views for repeated expensive aggregations , dashboard queries, and reducing slot consumption on hot queries

Question 7

What are slots in BigQuery?

Accepted Answer

Slots are BigQuery's unit of computational capacity . Each slot represents a virtual CPU used to execute SQL queries. How Slots Work: When you submit a query, BigQuery's Dremel execution engine breaks it into stages and distributes work across multiple slots Each slot performs a portion of the work: reading data from storage, filtering, aggregating, joining, and sorting BigQuery dynamically allocates slots based on query complexity and available resources Pricing Models: On Demand Pricing : You share a pool of slots and are billed per bytes scanned ($6.25 per TB as of standard pricing). BigQuery guarantees a minimum of 2,000 slots per project but may allocate more if available. Capacity Based Pricing (Editions) : You purchase a baseline number of dedicated slots (Standard, Enterprise, or Enterprise Plus editions). You get predictable performance and can enable autoscaling to burst beyond your baseline. Monitoring Slot Usage: Key Considerations: If queries are queued or slow, you may be hitting slot limits Use INFORMATION SCHEMA views to monitor slot consumption and identify expensive queries For workload isolation, create separate reservations for ETL vs. analytics workloads

Question 8

What file formats are supported by BigQuery?

Accepted Answer

BigQuery supports several file formats for loading , exporting , and querying external data . Supported Formats for Loading Data: Format Description Best For CSV Comma separated values Simple flat files, legacy systems JSON (Newline Delimited) One JSON object per line Semi structured data, nested fields Avro Binary row based format with embedded schema Schema evolution, Hadoop ecosystem Parquet Columnar binary format Analytics workloads, Spark/Hive output ORC Optimized Row Columnar format Hive based data warehouses Google Sheets Direct query via external table Ad hoc analysis of spreadsheet data Firestore/Datastore exports Native GCP format Application data analysis Loading data in different formats: Supported Formats for Export: CSV , JSON , Avro , and Parquet Exports go to Cloud Storage (not local) External Tables (Federated Queries): BigQuery can query data in place in Cloud Storage without loading it, using external tables or BigLake tables Supports CSV, JSON, Avro, Parquet, and ORC for external queries Best Practices: Prefer Avro or Parquet for large scale loads (compressed, schema embedded, faster to load) Use CSV/JSON only for small or ad hoc loads For recurring loads, use Parquet with Hive partitioned GCS paths for automatic partition detection

GCP Interview Questions