GCP Data Engineer Interview Bank

Question 1

What is BigQuery and what makes it different from a traditional RDBMS?

Accepted Answer

BigQuery is Google Cloud's serverless, fully-managed, petabyte-scale analytical data warehouse. It separates storage (Colossus) from compute (Dremel/Borg), so they scale independently; uses a columnar storage format (Capacitor) and a tree-architecture massively-parallel SQL engine.

Question 2

Difference between partitioning and clustering in BigQuery?

Accepted Answer

Partitioning physically splits a table into segments based on a column (date/timestamp/integer-range/ingestion-time). Clustering sorts data within each partition by up to 4 columns. Partitioning reduces bytes scanned by pruning whole partitions; clustering improves filter and aggregation performance within a partition.

Question 3

How does BigQuery pricing work and how do you optimize cost?

Accepted Answer

Two cost dimensions: storage (active vs long-term, $/GB/month) and compute (on-demand at $/TB scanned OR flat-rate slots). Optimizations: (1) SELECT only needed columns (columnar storage), (2) partition + cluster, (3) use --dry_run to estimate bytes, (4) materialize repeated heavy joins, (5) use BI Engine / cached results, (6) avoid SELECT * and LIMIT on huge tables (LIMIT does NOT reduce bytes scanned), (7) move cold data to long-term storage tier automatically after 90 days.

Question 4

Explain BigQuery slots.

Accepted Answer

A slot is a virtual CPU used by BigQuery to execute SQL queries. Each query is broken into stages and each stage is executed by parallel workers consuming slots. On-demand uses a shared pool (default 2000 slots/project); flat-rate reserves a fixed number you can split across reservations.

Question 5

How do you handle slowly changing dimensions (SCD Type 2) in BigQuery?

Accepted Answer

Use MERGE: when target row matches source but attributes differ, UPDATE the existing row to set valid_to = CURRENT_TIMESTAMP() and is_current = FALSE, then INSERT a new row with valid_from = now, valid_to = NULL, is_current = TRUE. For very large dims, partition by valid_from date and cluster by surrogate_key to keep MERGEs efficient.

Question 6

What are materialized views in BigQuery and when would you use them?

Accepted Answer

Pre-computed views that incrementally refresh as base tables change. Best for repeated aggregations on append-mostly tables. Limitations: cannot use OUTER JOIN, non-deterministic functions, or self-joins; refresh cost is charged.

Question 7

What is a BigQuery external table and what are its trade-offs?

Accepted Answer

A table that references data stored outside BigQuery (GCS, Drive, Bigtable, Cloud SQL). Queryable via SQL but no BigQuery-managed storage. Pros: query data in place, no ingest cost, supports federated formats (Parquet, ORC, Avro, JSON, CSV). Cons: slower (no Capacitor format), no streaming, limited DML, no clustering.

Question 8

How do you load data into BigQuery? List the methods.

Accepted Answer

(1) Batch load from GCS via bq load or LOAD DATA SQL, (2) Streaming inserts via tabledata.insertAll API or Storage Write API, (3) BigQuery Data Transfer Service (SaaS connectors), (4) Dataflow / Dataproc / Spark connector, (5) Federated query over external tables, (6) Datastream for CDC from operational DBs.

Question 9

Difference between Storage Write API and legacy streaming insertAll?

Accepted Answer

Storage Write API uses gRPC, supports exactly-once delivery via stream offsets, has higher throughput, lower latency, and is cheaper. insertAll is REST-based, at-least-once, no offset commit, more expensive per row.

Question 10

How would you implement row-level and column-level security in BigQuery?

Accepted Answer

Column-level: attach policy tags from Data Catalog to columns; grant fine-grained reader role on the policy tag. Row-level: CREATE ROW ACCESS POLICY ON GRANT TO ('group:x@co') FILTER USING (region = 'EMEA'). Audit via INFORMATION_SCHEMA.ROW_ACCESS_POLICIES.

Question 11

A query suddenly takes 10x longer. How do you debug it?

Accepted Answer

Open the Query Plan tab → look at stage timing, shuffled bytes, slot utilization. Check INFORMATION_SCHEMA.JOBS for total_slot_ms, total_bytes_processed, error_result. Compare with previous runs. Common causes: data skew on JOIN key, missing partition filter (full scan), recently dropped clustering, schema change, slot contention from another workload, hot partition.

Question 12

What is denormalization and why is it preferred in BigQuery?

Accepted Answer

Combining related tables into a single wide table (often with STRUCT/ARRAY columns) to avoid JOINs. BigQuery's columnar engine handles wide tables efficiently; JOINs across huge tables trigger expensive shuffles. Nested+repeated fields preserve the relational meaning without join cost.

Question 13

Explain UNNEST in BigQuery.

Accepted Answer

UNNEST flattens an ARRAY column into rows, allowing you to join nested values back to the parent. Syntax: SELECT o.order_id, i.sku FROM orders o, UNNEST(o.items) AS i WHERE i.qty > 1.

Question 14

What are window functions and give a BigQuery example.

Accepted Answer

Functions that compute a value across a row set related to the current row without collapsing rows. Example: SELECT user_id, event_ts, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS rn FROM events — gives the most recent event per user with rn = 1.

Question 15

How do you sessionize events in BigQuery?

Accepted Answer

Use LAG to compute time gap, mark a new session when gap > threshold, then SUM the indicator over a window to get a session_id: WITH t AS (SELECT user_id, ts, TIMESTAMP_DIFF(ts, LAG(ts) OVER w, MINUTE) AS gap FROM events WINDOW w AS (PARTITION BY user_id ORDER BY ts)) SELECT user_id, ts, SUM(CASE WHEN gap > 30 OR gap IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY ts) AS session_id FROM t.

Question 16

What is BigLake?

Accepted Answer

A storage engine that unifies data lakes (GCS, S3, Azure Blob) and warehouses by exposing them as BigLake tables with fine-grained security, governance, and ACID via Iceberg/Delta/Hudi. Lets BigQuery, Spark, Trino query the same files with consistent permissions.

Question 17

How do you handle late-arriving facts in a BigQuery warehouse?

Accepted Answer

Partition fact tables by event_ts (not ingestion_ts) so late events land in correct partition. Use MERGE on (event_id, event_date). For dimension lookups that may also be late, keep an SCD2 dim and join on the event timestamp to retrieve the dim row valid at event time.

Question 18

What is Dataflow and Apache Beam?

Accepted Answer

Apache Beam is a unified batch+streaming programming model. Dataflow is Google's serverless, autoscaling runner for Beam pipelines. You write the pipeline once (Java/Python/Go), and Dataflow handles workers, autoscaling, shuffling, and exactly-once semantics.

Question 19

Explain PCollection, PTransform, and DoFn.

Accepted Answer

PCollection = distributed, immutable dataset (bounded or unbounded). PTransform = operation applied to a PCollection that returns one or more PCollections. DoFn = element-wise processing function used inside ParDo, with lifecycle methods (setup, startBundle, processElement, finishBundle, teardown).

Question 20

Windowing strategies in Beam — name and explain.

Accepted Answer

Fixed (tumbling): non-overlapping equal-duration windows. Sliding: overlapping windows of fixed size and period. Session: windows close after a gap of inactivity (key-based). Global: a single window for the whole pipeline (default for batch).

GCP Data Engineer Interview Preparation

What you will practice

How a practice session works

Continue learning

GCP interview overview

MySQL practice

PySpark practice