Google Cloud Data Services Questions

Covers design and operational knowledge of Google Cloud Platform data products used for storage, processing, streaming, and analytics. Key skills include when and how to use BigQuery for serverless analytics and data warehousing, Dataflow for stream and batch pipelines built on Apache Beam, Cloud Storage for object store and data lake patterns, and Pub/Sub for messaging and event ingestion. Candidates should understand cost models, performance trade offs, schema and partitioning strategies, data ingestion and export patterns, pipeline monitoring and error handling, and integration between these services for end to end data solutions.

MediumTechnical

75 practiced

For a regulated client storing PII, outline a security design using GCP services covering: IAM controls for BigQuery and Cloud Storage, encryption with CMEK (Cloud KMS), VPC Service Controls, DLP scanning/tokenization, and audit logging. Explain trade-offs and operational tasks to maintain compliance.

Sample Answer

Requirements (assumed): store/process PII in BigQuery and Cloud Storage for a regulated client (e.g., PCI/PHI/GDPR). Controls must provide least privilege, customer-managed keys, perimeter protection, DLP controls, and immutable audit logs.

Design summary:- IAM: - Use principle of least privilege with predefined+custom roles. Grant BigQuery DataViewer/JobUser and specific dataset-level roles rather than project-wide. Use Cloud Storage fine-grained ACLs (Uniform bucket-level access) and Signed URLs for temporary access. - Use groups and service accounts for apps; avoid user keys. Enforce MFA and context-aware access (device, IP).- Encryption (CMEK via Cloud KMS): - Enable CMEK for BigQuery (dataset-level) and Cloud Storage buckets. Use separate key rings per environment and rotation policy. Store key IAM separately (separate admin group) and enable key version lifecycle/rotation.- VPC Service Controls: - Create service perimeter including BigQuery and Cloud Storage. Use access levels to restrict access to trusted networks and on-prem via Cloud VPN/Interconnect. Configure ingress/egress rules and private Google access for limited services.- DLP: - Run Cloud DLP discovery jobs on Cloud Storage and BigQuery. Classify and generate risk reports. Use tokenization/format-preserving encryption for PII fields, store mapping in a secured vault (separate project, restricted access). - For streaming data, integrate DLP API in ingestion pipeline to redact/tokenize before landing.- Audit logging: - Enable admin, data access, and system event logs. Route logs to a separate, immutable logging project with logs-based retention and BigQuery export for analytics. Protect logs with CMEK and restrict access. Consider log sinks to Pub/Sub for SIEM ingestion.Operational tasks & compliance maintenance:- Regular IAM reviews and access recertification; automated alerts for privilege escalation.- Key lifecycle: rotate CMEK routinely, revoke and re-encrypt if compromise suspected; periodic KMS audit.- Perimeter testing: pen-tests and configuration checks for VPC Service Controls; maintain allowlists.- DLP cadence: schedule automated scans, remediate findings, update detectors and templates.- Logging/monitoring: set alerts for anomalous data access, high-volume exports, or key usage spikes. Retention and legal hold policies aligned to regulation.- Documentation & evidence: maintain runbooks, change logs, policy docs, and recurring compliance reports for auditors.

Trade-offs:- CMEK gives control but increases operational burden (rotation, key availability during outages) and possible performance/latency for key operations.- VPC Service Controls add strong perimeter security but complicate cross-project integrations (requires connectors, VPNs).- Tokenization/redaction preserves privacy but can reduce analytical utility; format-preserving encryption maintains schema but is weaker than full encryption for some threat models.- Strict least-privilege + DLP preprocessing reduces risk but can increase development friction and pipeline latency.

Why this works: combines defense-in-depth (IAM, CMEK, perimeter, DLP, logging), separates duties (key/admin/logging projects), and provides operational controls for continuous compliance.

HardTechnical

69 practiced

Analytical queries over large partitioned tables are slow and expensive. Provide a step-by-step approach to optimize them: reading execution plans, enabling partition pruning, choosing clustering columns, adding materialized views, considering approximate aggregations, and tuning slot allocations. Describe how you'd measure and guard against regressions.

Sample Answer

Approach — step-by-step (practical BigQuery-focused):

1. Read the execution plan first- Use the Query Plan & Execution Details (or EXPLAIN) to identify hot spots: full table scans, shuffle steps, slot waits, and shuffles (stage durations, input/output rows, parallelism).- Look for “Read” bytes per stage and “Shuffle” or “Merge” heavy stages that indicate expensive repartitions.

2. Enable partition pruning- Ensure predicates reference the partition column directly (e.g., date = DATE('2025-01-01') or _PARTITIONTIME) — avoid functions that block pruning.- Convert queries to push filters early (filter in subqueries where scans occur).- Validate pruning in the plan: ensure scanned partitions << total partitions and bytes read drops.

3. Choose clustering columns- Pick columns used frequently in WHERE, GROUP BY, ORDER BY, or JOIN keys. Prefer moderate cardinality (not single-valued, not ultra-high-cardinality unique IDs).- Test: add clustering and re-run representative queries; check reduced scanned bytes and stage times.- If multiple query shapes exist, consider composite clustering order where the most selective filters come first.

4. Add materialized views (and summary tables)- Create materialized views for frequently-run, stable aggregations or join-heavy results. Use incremental maintenance to avoid full recompute.- Be mindful of MV limitations (e.g., determinism, allowed SQL features); fallback to scheduled summary tables if necessary.- Monitor MV refresh cost and staleness; prefer materialized views for repetitive low-latency queries.

5. Consider approximate aggregations- Replace exact DISTINCT/QUANTILE with APPROX_COUNT_DISTINCT(), APPROX_QUANTILES(), or HyperLogLog-based sketches where small error is acceptable.- Document acceptable error bounds with stakeholders; compare time/cost savings vs. precision loss.

6. Tune slot allocations & concurrency- Use reservations and assignments: isolate heavy workloads into dedicated reservations or move lighter ad-hoc to on-demand.- Right-size reservation size, set concurrency limits, and use idle reservations for flex capacity.- Monitor WAIT and QUEUED metrics to detect under/over-provisioning; set autoscaling policies if available.

Measuring & guarding against regressions- Define benchmark suite: representative queries (templates, parameterized), datasets, and baseline metrics (latency P50/P95, bytes scanned, slot-seconds, cost).- Automate CI: run benchmarks on each schema/change (partitioning, clustering, MV, SQL rewrite) and compare against baselines with thresholds (e.g., latency +10% alert).- Use production telemetry: dashboards for query latency, bytes scanned, slot utilization, and cost per workload; add alerts for anomalies.- Canary deployments: apply changes to a small subset of traffic/workload first (test reservation or new MV), monitor correctness and performance before full rollout.- Validate correctness: sample query results, row counts, checksums, and for approximations validate error bounds periodically.- Cost guardrails: budget alerts, query quotas, and IAM controls to prevent runaway queries.

Trade-offs and rollout- Start with low-risk optimizations (predicate/cost-based rewrites, pruning) → clustering → materialized views/summaries → slot reservation changes.- Include rollback plan: revert clustering changes, drop MV, or reassign reservations quickly if regressions occur.

This sequence minimizes risk, provides measurable wins (reduced bytes scanned, lower P95 latency, lower slot-seconds), and uses automated benchmarks and monitoring to prevent regressions.

EasyTechnical

96 practiced

Explain what BigQuery is and describe typical use-cases where you would choose BigQuery over Cloud SQL or Bigtable for a client's analytics needs. Include concrete examples of workloads (ad-hoc analytics, large-scale aggregations, BI dashboards) and why BigQuery is appropriate for those workloads.

Sample Answer

BigQuery is Google Cloud’s fully managed, serverless data warehouse for analytics: it stores petabyte-scale columnar data, separates storage and compute, and provides fast SQL-based analytics with built-in query optimization, automatic scaling, and cost control (on-demand or flat-rate). As a Solutions Architect, I pick BigQuery when workloads need high-throughput analytics, low operational overhead, and integration with BI tools.

When to choose BigQuery over Cloud SQL or Bigtable:

- Ad-hoc analytics / interactive SQL exploration: BigQuery is ideal for analysts running complex, ad-hoc joins and scans across large datasets (hundreds of GBs to PBs). Example: marketing team exploring campaign performance across user events and conversions with many exploratory queries. BigQuery’s columnar storage and distributed execution give fast response without managing infrastructure.

- Large-scale aggregations / ETL and reporting: For nightly aggregations across billions of rows (e.g., sessionizes web logs to produce daily active users, funnel metrics), BigQuery handles massive scans and GROUP BYs efficiently and scales automatically. Use partitioning/clustering to reduce costs.

- BI dashboards at scale: When dashboards (Looker/Tableau/Looker Studio) need low-latency access to aggregated metrics over massive data, BigQuery’s ability to serve materialized views or BI Engine (in-memory) provides fast dashboard performance without manual index management.

Why not Cloud SQL or Bigtable:- Cloud SQL (relational OLTP) is for transactional workloads with many small writes/reads, ACID requirements, and low-latency single-row ops. It doesn’t scale cost-effectively for PB analytics or wide table scans and complex joins.- Bigtable (wide-column, low-latency) excels at high-throughput key-value or time-series reads/writes (e.g., user profile lookups, telemetry ingestion) but lacks SQL analytics, joins, and efficient large-table aggregations.

Concrete recommendation patterns:- Ingest event streams to Pub/Sub → Dataflow → append to BigQuery for analytics and BI.- Use BigQuery for monthly/quarterly financial aggregations and Cloud SQL for transactional billing systems; sync summarized rows when needed.- Use Bigtable for real-time feature serving in ML, and export daily snapshots to BigQuery for model training and analytical reporting.

Trade-offs: BigQuery is batch/analytical-first (not ideal for low-latency point lookups) and charges per processed bytes (or flat-rate), so design partitions, clustering, and pruning to optimize cost and performance.

EasyTechnical

88 practiced

Explain how BigQuery supports nested and repeated fields (STRUCT and ARRAY). When is a nested schema preferable to normalizing data into multiple tables? Provide an example analytic query that benefits from nesting.

Sample Answer

BigQuery supports nested and repeated fields via STRUCT (record) and ARRAY types. A STRUCT groups named fields together (like a JSON object); an ARRAY is an ordered repeated field of a scalar or STRUCT. Together they let you store hierarchical records in a single table column while preserving schema, typing, and queryability.

When to prefer nested schema vs normalizing into multiple tables- Prefer nesting when related data is tightly coupled to the parent, accessed together, and has one-to-many that’s naturally bounded (e.g., order → order_items). Nesting reduces JOINs, improves read cost (fewer scanned bytes), and simplifies ingestion.- Prefer normalization when the child grows unbounded, is queried independently at high frequency, or needs different lifecycle/retention, or if transactional updates across rows are required.

Key querying pattern: UNNEST(array) to expand repeated fields for aggregation or filtering. BigQuery keeps performance and parallelism; avoid excessive repeated field sizes.

Example: analytics on e-commerce orders where each order has an items ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>>. To get top SKUs sold yesterday:

sql

SELECT
  itm.sku,
  SUM(itm.qty) AS total_qty,
  SUM(itm.qty * itm.price) AS revenue
FROM `project.dataset.orders` o,
UNNEST(o.items) AS itm
WHERE DATE(o.order_ts) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY itm.sku
ORDER BY total_qty DESC
LIMIT 20;

Why this benefits from nesting:- No JOIN required between orders and items tables — simpler ETL and fewer scanned bytes.- Schema retains context (item belongs to order), and query performance is excellent for analytics that process orders with their items together.

MediumSystem Design

88 practiced

Outline a migration plan for nightly Spark jobs running on-prem that produce denormalized tables, migrating them to GCP. Compare lift-and-shift to Dataproc vs refactoring to Dataflow + BigQuery, and include testing, validation, and rollback strategies.

Sample Answer

Requirements & constraints (clarifying): nightly SLA (completion window), data volume, throughput, latency requirements for downstream consumers, tolerances for schema drift, expected concurrency, security/compliance (VPC, IAM, encryption), budget, team skillset (Spark expertise vs Beam), and acceptable cutover downtime.

High-level migration plan (phased):1. Discovery & baselining: inventory jobs, inputs/outputs, data volumes, runtime, libraries, external systems, and produce test vectors + golden outputs for reconciliation.2. Proof-of-Concept (PoC): pick 1–2 representative jobs. - Path A (lift-and-shift Dataproc): create Dataproc cluster, run existing Spark with minimal change; validate runtime, performance, IAM, network. - Path B (refactor to Dataflow+BigQuery): reimplement logic in Apache Beam (Java/Python) with BigQuery as sink; validate correctness and performance.3. Pilot (parallel run): run chosen jobs nightly in parallel for 2–4 weeks: on-prem job vs Dataproc vs Dataflow. Capture metrics and data diffs.4. Full rollout: staggered migration by job-criticality, implement monitoring/alerts, cost controls.5. Decommission on-prem after stable period and reconciliation.

Comparison: Lift-and-shift (Dataproc) vs Refactor (Dataflow + BigQuery)- Speed to migrate: Dataproc >> Dataflow. Dataproc supports near drop-in Spark; minimal code change.- Operational model: Dataproc clusters are managed but require cluster lifecycle ops (autoscaling, image maintenance). Dataflow is serverless; less infra management.- Cost: Dataproc may be cheaper for predictable heavy batch if using preemptibles; Dataflow can be more cost-effective for streaming/elastic workloads, and BigQuery storage+query costs change cost profile (storage + query vs compute).- Performance: Spark on Dataproc yields similar performance to on-prem; Beam/Dataflow may require algorithmic refactor to match performance but often scales better and supports streaming.- Ecosystem/analytics: BigQuery provides fast ad-hoc querying, versioning, partitioning/clustering benefits vs HDFS/Snowflake-like storage.- Maintainability & future-proofing: Refactor offers long-term lower ops, easier scaling, unified streaming/batch, and simplifies downstream analytics; however it requires Beam expertise and rewriting denormalization logic.- Risk: Dataproc lower code risk; Dataflow higher code risk but lower infra risk.

Testing & validation strategy:- Unit & integration tests for data transformations (local Spark/Beam runners).- Regression test harness: run jobs on sampled data and compare row counts, checksums, key aggregates, histograms, and primary-key uniqueness.- Record-level diff for a subset (hash keys and values); full reconciliation weekly for larger datasets.- End-to-end consumer tests: downstream reports/DBs validated against golden outputs.- Performance/load testing: scale inputs to expected peak + buffer; measure runtime, memory, shuffle.- Schema & drift tests: detect extra/missing columns, types.- Automate tests in CI/CD with nightly validation runs and automated alerts.

Cutover, dual-write, and rollback strategies:- Parallel-run phase: keep on-prem production as source of truth while running cloud jobs in parallel. Do not switch consumers until reconciliation within tolerance.- Dual-write (if feasible): write simultaneously to legacy denormalized tables and BigQuery; consumers gradually switch to BigQuery read-only.- Canary cutover: route small percentage of jobs/consumers to BigQuery, monitor errors and metrics, then increase traffic.- Rollback options: - If Dataproc: stop using Dataproc and point consumers back to on-prem datastore; run backfill from on-prem to GCP if needed. - If Dataflow+BigQuery: keep on-prem pipeline live for rollback, or restore previous denormalized tables from export snapshots (BigQuery snapshots/partition export) and redirect consumers. - Always preserve at least N days of raw input (cloud storage) and job outputs to enable reprocessing/backfill.- Backfill plan: use windowed reprocessing (partitioned by date) with idempotent transforms and job bookkeeping (job-run metadata) to safely reprocess failed partitions.

Security, monitoring & governance:- Use VPC Service Controls, Private Google Access, Cloud NAT, and Private Service Connect where needed.- IAM least-privilege for service accounts; KMS for encryption.- Observability: Stackdriver/Cloud Monitoring, structured logs, metrics (runtime, records processed, error counts), and alerts on reconciliation divergence, SLA misses.- Cost governance: Budgets, alerts, slot reservations for BigQuery if predictable workloads.

Trade-offs summary:- Choose Dataproc when migration speed and code stability are priority, or when transformation logic is complex and costly to rewrite.- Choose Dataflow+BigQuery when long-term ops reduction, scale, interactive analytics, and streaming unification justify upfront rewrite cost and skill investment.

This plan yields low-risk staged migration: start with Dataproc for quick wins, run strategic refactors to Dataflow+BigQuery for high-value pipelines, and enforce automated validation, canary deployments, and robust rollback/backfill capabilities.

Unlock Full Question Bank

Get access to hundreds of Google Cloud Data Services interview questions and detailed answers.

Join thousands of developers preparing for their dream job.