Google Data Engineer Interview Questions

BigQuery separates storage (Colossus distributed filesystem) from compute (Dremel query engine), connected via Jupiter network at 1 Petabit/second. Dremel breaks queries into leaf servers scanning Colossus columns in parallel and aggregating results up the tree. Columnar storage means a query touching 3 of 50 columns reads 94% less data. Slot allocation auto-scales. No indexes needed — partition pruning and clustering are the primary access optimizations.

Apache Beam is a unified programming model for batch AND streaming pipelines — write once, run on any runner (Dataflow, Spark, Flink). Key abstractions: PCollections and PTransforms. Beam is runner-agnostic while Spark is tightly coupled to its own runtime. Beam handles event-time windowing and Watermarks more natively. Google Dataflow auto-scales workers based on pipeline throughput with no cluster management required.

Publishers to Pub/Sub (ordering keys by advertiser_id for partitioned ordering) to Dataflow streaming pipeline: parse Avro events, deduplicate with stateful 10-minute window, enrich from BigTable (low-latency key-value for real-time lookup), aggregate per campaign per minute. Write to BigQuery streaming buffer. Late data: 5-minute Watermark tolerance. Monitor with Cloud Monitoring alerting on data freshness lag.

Partitioning divides table by date/timestamp or integer range — BigQuery scans only relevant partitions, reducing bytes billed. Best for time-series queried by date range. Clustering physically co-locates rows by column values within partitions — reduces bytes scanned within a partition without eliminating partitions. Rule: always partition on most common date filter, then cluster on next most selective columns. Google recommends clustering up to 4 columns.

Strong consistency guarantees all reads after a write return the updated value immediately — Cloud Spanner achieves this using TrueTime. Eventual consistency allows replicas to temporarily diverge — reads may return stale data, but all nodes converge eventually. CAP theorem: during network partition, choose consistency or availability. Google services prefer high availability with application-layer consistency handling for most use cases.

Three approaches: 1) Idempotent writes — BigQuery MERGE by unique event_id so duplicate inserts produce one row. 2) Stateful deduplication in Beam — State API maintains a set of seen event_ids per key within a time window; if seen, drop; else process and add to state. 3) Pub/Sub exactly-once delivery combined with Dataflow exactly-once runner mode. In practice, combine idempotent writes with windowed deduplication for defense-in-depth.