Question 1

Outline BigQuery architecture—columnar storage, Dremel execution.

Accepted Answer

BigQuery is Google's fully managed, serverless data warehouse. Storage layer (Capacitor) : Columnar format (similar to Parquet) stored on Google's distributed filesystem (Colossus) Data is encrypted at rest and replicated across multiple zones Tables are immutable — append only with copy on write for DML Execution engine (Dremel) : Tree shaped query execution — root server receives the query, fans it out to thousands of leaf servers that scan columns in parallel Leaf servers read only the requested columns from Capacitor files Results roll up the tree: leaf → intermediate → root Separation of storage and compute : no cluster to manage — compute scales automatically per query. You pay per TB scanned (on demand) or per slot hour (capacity pricing). Performance features : automatic partitioning detection, clustering, result caching (free for identical queries).

Question 2

Optimize BigQuery performance—partitioning, clustering.

Accepted Answer

Partitioning — reduces bytes scanned: Clustering — sorts data within each partition by column(s): Queries filtering on region or product id skip non matching blocks within a partition (block pruning). Combined impact : partition pruning skips entire date ranges; clustering reduces bytes scanned within a partition. Other tips : Use APPROX COUNT DISTINCT for dashboards Avoid SELECT — BigQuery charges per column scanned Materialize repeated subqueries as views or tables

Question 3

Compare BigQuery vs Redshift tradeoffs.

Accepted Answer

BigQuery Redshift Architecture Serverless (storage/compute separated) Cluster based (RA3: storage/compute separated) Scaling Fully automatic Manual cluster resize or auto scaling Pricing Per TB scanned (on demand) or slot commitment Per node hour (provisioned) or Redshift Serverless Ecosystem GCP native (Dataflow, Pub/Sub, Vertex AI) AWS native (S3, Kinesis, SageMaker) SQL dialect Standard SQL with ARRAY/STRUCT, BIGNUMERIC PostgreSQL based Nested data Native ARRAY/STRUCT support Requires SUPER type (newer) Concurrency Very high (automatic slot allocation) Limited by cluster WLM queues Choose BigQuery : GCP stack, ad hoc analytics, variable/unpredictable query volumes. Choose Redshift : AWS stack, predictable workloads with steady query patterns, need deep S3 Spectrum integration.

Question 4

Explain Dataflow (Apache Beam) pipeline design.

Accepted Answer

Apache Beam is the SDK; Dataflow is Google's managed runner for Beam pipelines. Core concepts : PCollection : distributed dataset (bounded for batch, unbounded for streaming) PTransform : operations on PCollections ( ParDo , GroupByKey , Combine , Flatten ) Pipeline : DAG of PTransforms Runner : Beam programs run on Dataflow, Spark, Flink, or locally Example pipeline : Unified batch/stream : the same Beam code runs as batch (bounded source) or streaming (unbounded source like Pub/Sub) — just swap the runner configuration.

Question 5

Use Pub/Sub for ingestion and scaling.

Accepted Answer

Pub/Sub is Google's fully managed message queue — analogous to Kafka. Architecture : Key features : At least once delivery — use message id for deduplication Ordering : enable message ordering per ordering key — guarantees ordered delivery per key BigQuery subscription : direct write from Pub/Sub to BigQuery table without a processing layer (for simple ingestion) Push vs Pull : push sends messages to an HTTPS endpoint (Cloud Run, App Engine); pull is consumer initiated Scaling : Pub/Sub scales automatically — no partition management (unlike Kafka). Throughput scales to millions of messages/second. Monitoring : subscription/oldest unacked message age is the key consumer lag metric — alert if it grows.

Question 6

Handle late data with watermarking and triggers.

Accepted Answer

Watermark : estimate of how far behind real time the pipeline is processing. If watermark is at T , events with event time T allowed lateness are considered on time. In Apache Beam : Late data strategies : Drop : ignore events after allowed lateness window Reprocess : route late events to a correction queue; update target with MERGE Side output : emit late records to a separate PCollection for manual review

Question 7

Compare Bigtable vs BigQuery use cases.

Accepted Answer

Bigtable BigQuery Type Wide column NoSQL store Analytical SQL data warehouse Latency Sub millisecond (single row lookups) Seconds to minutes (full scan queries) Access pattern Point reads/writes by row key Full scans with aggregations Schema Flexible — sparse columns, no SQL Structured — SQL, typed columns Scale Petabyte scale, millions of ops/sec Petabyte scale, complex analytical queries Use cases Time series, IoT telemetry, real time lookups, ad serving BI dashboards, reporting, ML training data Combined pattern : ingest real time events to Bigtable for low latency reads; batch export to BigQuery nightly for analytics. Example : Google Maps — location updates go to Bigtable (instant lookup); aggregated movement patterns analyzed in BigQuery.

Question 8

Write SQL to compute top N per group in BigQuery.

Accepted Answer

Top 3 products by sales per category : BigQuery specific alternative using ARRAY AGG with LIMIT :

Google Interview Questions