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.

HardTechnical

117 practiced

A data warehouse contains many wide nested JSON fields that analysts frequently query on only a few fields. Propose a schema redesign and ingestion strategy to reduce query cost and improve performance. Consider denormalization, generated columns, flattening nested fields, using repeated fields efficiently, and when to use JSON type vs strongly-typed columns.

Sample Answer

Requirements & constraints:- Analysts only query a small subset of fields frequently; raw nested JSON is wide.- Goal: lower scan costs, improve query latency, preserve full raw data for rare use.

High-level redesign:1. Partition & cluster the table (ingest date/time partitioning; cluster on high-cardinality filter cols like user_id, event_type).2. Keep a raw JSON column (JSON or STRING) for audit / rare queries, but extract hot fields into strongly-typed columns.3. For nested/repeated objects that are frequently queried, normalize into narrow, read-optimized tables (denormalized for analytic reads) or use repeated RECORDs in BigQuery where beneficial.

Ingestion strategy:- ETL job parses incoming JSON and: - Writes a landing/raw table with full JSON (partitioned). - Writes a curated table with: - Strongly-typed columns for commonly queried scalars (timestamp, user_id, event_type, country, product_id). - Generated/virtual columns for derived values (e.g., date, hour, event_category) so analysts don't recompute. - Flattened or exploded repeated fields only when queries need row-per-item semantics; otherwise keep as ARRAY<STRUCT> for efficient storage and selective UNNESTing.- Maintain change logs / backfills via CDC.

Schema choices & rationale:- Strongly-typed columns when field is queried, filtered, joined, or aggregated often: types enable predicate pushdown, better compression, statistics, and lower scan cost.- Generated columns (BigQuery: use SQL in ETL or CREATE TABLE with computed columns) for common transformations to avoid repeated compute in queries.- JSON type: keep for low-frequency exploratory fields or evolving schema; use JSON_EXTRACT for ad-hoc work. Don't keep all hot fields in JSON.- Flattening: fully flatten only if query patterns require single-row-per-nested-item. Otherwise keep nested RECORD/ARRAY to avoid explosion of row count and allow selective UNNEST when needed.- Repeated fields: represent as ARRAY<STRUCT> when queries often access array length, filter by element property, or need aggregation across elements. If joins/filters target individual elements frequently, consider an exploded (denormalized) table with one row per element for performance.

Cost & performance tactics:- Materialized views for common aggregations; scheduled precomputed tables for heavy joins.- Column selection: curate a “wide-light” view with only hot columns to reduce scanned bytes.- Use clustering to reduce scanned partitions for selective predicates.- Use table decorators and partition pruning for backfills.

Example: ETL extract for BigQuery (curated table):

sql

INSERT INTO dataset.events_curated (event_date, user_id, event_type, product_id, event_ts, raw_payload)
SELECT
  DATE(TIMESTAMP_SECONDS(CAST(JSON_VALUE(raw, '$.ts') AS INT64))) AS event_date,
  CAST(JSON_VALUE(raw, '$.user.id') AS INT64) AS user_id,
  JSON_VALUE(raw, '$.type') AS event_type,
  JSON_VALUE(raw, '$.product.id') AS product_id,
  TIMESTAMP_SECONDS(CAST(JSON_VALUE(raw, '$.ts') AS INT64)) AS event_ts,
  raw
FROM dataset.events_raw
WHERE event_date = DATE(_PARTITIONTIME);

Trade-offs:- Duplication increases storage but reduces scan cost and latency — acceptable when hot fields are small relative to JSON size.- Keeping raw JSON preserves flexibility but makes ad-hoc queries more expensive; balance by promoting fields to typed columns when usage grows.

Monitoring & governance:- Track query logs to detect hot fields; automate promotion of fields from JSON to typed columns.- Schema registry and transformation tests to handle evolving JSON schema.

This approach reduces scanned bytes, speeds up filters/joins, and keeps flexibility for analytical exploration.

HardSystem Design

92 practiced

Design a highly available, multi-region, real-time analytics pipeline that ingests events from global clients (1M events/sec), provides per-user ordering guarantees, and supports low-latency (under 5s) rollup metrics exposed to dashboards. Use Pub/Sub, Dataflow, BigQuery, and any other GCP components you need. Explain how you will achieve ordering, deduplication, cross-region traffic, failover, and cost controls.

Sample Answer

Requirements & constraints:- Ingest 1M events/sec globally, per-user ordering, <5s rollup latency, multi-region HA, dedupe, cost control.

High-level architecture:- Regional edge: clients send events to nearest regional HTTPS endpoint (Cloud Load Balancing + Cloud CDN optional) → regional Pub/Sub topics (one topic per region).- Global routing: Publish with attributes: user_id, event_id, timestamp, region.- Per-user ordering & low latency: Use Dataflow (Apache Beam) regional workers reading from regional Pub/Sub. Use a keyed pipeline keyed by user_id to ensure ordering semantics within a worker using event-time processing and Window.of(…) with allowedLateness small (e.g., 2s) and a per-key buffer. For strict ordering use per-key single-threaded processing via stateful DoFn with event buffers and sequence numbers.- Deduplication: Maintain short-lived dedupe state in Dataflow using per-key state (store last-seen event_id or a Bloom filter/timestamp TTL). Persist long-lived uniques to Bigtable/Spanner only if needed.- Global rollups: Each regional Dataflow produces partial per-user / per-metric aggregates and writes to regional BigQuery streaming inserts (or to Bigtable for low-latency reads). A global Dataflow job periodically (every 1s) merges regionals into global rollups (sharded by metric) and writes final rollups to BigQuery partitioned tables for dashboards.- Multi-region failover: Replicate Pub/Sub topics across regions using Pub/Sub Lite or multi-region topics (use multi-region Pub/Sub if available). Use regional Dataflow jobs with health checks; if a region fails, clients failover to next nearest endpoint; streaming buffers in Pub/Sub keep events durable.- Ordering across regions: Since per-user events normally hit nearest region, enforce global ordering by sequence numbers from clients. Dataflow merges sequences; if out-of-order but within allowed lateness, reorder using stateful buffering; if missing sequences beyond TTL emit gap-handling logic.- Cost controls: - Use Pub/Sub batching and compression, adjust ack deadlines. - Use Dataflow autoscaling with max workers and worker type tuned (n1-highmem only when needed). - Use BigQuery streaming for hot data into small tables, then batch load from GCS (Dataflow writes AVRO to GCS and load jobs) for cheaper storage/ingest at scale. - TTL on state stores; sample / downsample raw events into cheap cold storage (GCS) for long-term. - Monitor with Stackdriver, set alerts and autoscale thresholds.

Key trade-offs:- Strong per-user ordering with client-assigned seq numbers + stateful reordering increases memory/state; mitigated by TTL and sharding.- Multi-region consistency vs latency: allowing small allowedLateness (<2s) balances correctness and <5s SLA.- BigQuery streaming is low-latency but costly; use hybrid streaming+batch.

Operational considerations:- End-to-end observability: metrics on ingestion, per-key backlog, watermark lag.- Chaos tests for region failover.- Backpressure: enforce client rate-limits and token buckets at edge.

This design meets 1M/s by regional sharding, enforces per-user ordering via keyed stateful Dataflow with client sequence numbers, deduplicates with state, supports <5s rollups via regional partials + fast merges, and uses replication + autoscaling for HA while controlling cost via batching and hybrid streaming/batch loads.

HardTechnical

79 practiced

Implement a deduplication transform for a Dataflow pipeline using Apache Beam in Python. Messages are JSON with fields event_id (string) and event_time (ISO timestamp). Ensure duplicates are suppressed within a 24-hour watermark window and that the solution tolerates worker restarts and retries. Describe state schema, keying strategy, and timers, or provide a code sketch if appropriate.

Sample Answer

Approach: key by event_id, use a stateful DoFn that records a "seen" flag with the event's watermark-expiring timer set to event_time + 24h. Use event-time timers so deduplication window follows event timestamps and is robust to retries/workers (state is persisted). On arrival: if state empty -> emit and set state + timer; if state present -> drop. When timer fires, clear state (allow future events after 24h). Use Beam's ValueState and TimerSpec.

Code sketch:

python

import json
from datetime import timedelta
import apache_beam as beam
from apache_beam.coders import StrUtf8Coder
from apache_beam.transforms.userstate import TimerSpec, on_timer
from apache_beam.transforms.userstate import ValueStateSpec
from apache_beam.utils.timestamp import Timestamp

class DeduplicateFn(beam.DoFn):
    SEEN_STATE = ValueStateSpec('seen', StrUtf8Coder())
    EXPIRY_TIMER = TimerSpec('expiry', time_domain=beam.TimeDomain.WATERMARK)

    def process(self, element, timestamp=beam.DoFn.TimestampParam, seen=beam.DoFn.StateParam(SEEN_STATE), expiry=beam.DoFn.TimerParam(EXPIRY_TIMER)):
        # element is (key,event_json)
        event = json.loads(element[1])
        if seen.read() is None:
            seen.write('1')
            # set timer to event_time + 24h
            event_time = Timestamp.from_utc_datetime(beam.utils.timestamp.parse_rfc3339(event['event_time']))
            expiry.set(event_time + timedelta(hours=24))
            yield element[1]  # emit original message
        else:
            return  # duplicate within 24h => drop

    @on_timer(EXPIRY_TIMER)
    def clear_state(self, seen=beam.DoFn.StateParam(SEEN_STATE)):
        seen.clear()

Keying: PCollection of (event_id, json) via beam.Map(lambda m: (m['event_id'], json.dumps(m))). Use event timestamps when reading (with WithTimestamps(parse event_time)).

Why this works:- State is persisted across worker restarts and retries (Beam state backend).- Event-time timers tied to watermark ensure 24h window anchored to event_time, not processing time.Edge cases:- Late events beyond allowed lateness must be handled (set allowed_lateness on windowing or drop).- Clock skew / out-of-order events: rely on watermark accuracy.- State growth: per-key state limited to small flag; if high cardinality consider TTL or external dedup store.

EasyTechnical

88 practiced

Explain Google BigQuery's architecture and primary use cases as a serverless data warehouse. Compare BigQuery to Cloud SQL and Cloud Spanner for analytical workloads, focusing on schema flexibility, concurrency, expected query latency, cost model (storage vs compute), and operational maintenance. As a data engineer, give concrete examples of when you'd choose BigQuery over a managed relational database.

MediumTechnical

89 practiced

Write a BigQuery SQL query to flag outlier transactions per user where an outlier is defined as amount > mean + 3 * stddev over that user's past 365 days. Given table transactions(transaction_id STRING, user_id STRING, amount NUMERIC, occurred_at TIMESTAMP), show how you'd handle users with fewer than 30 samples and explain assumptions about timezones and nulls.

Sample Answer

Approach: for each transaction, compute the mean and stddev of that user's transactions in the prior 365 days (including the current row), then flag amount > mean + 3*stddev. Exclude nulls and handle low-sample users by requiring at least 30 historical samples (otherwise mark as "insufficient_data"). Assume timestamps are stored in UTC; if per-user timezone is required, normalize occurred_at to user_timezone before windowing.

sql

WITH clean AS (
  SELECT
    transaction_id,
    user_id,
    amount,
    occurred_at
  FROM transactions
  WHERE amount IS NOT NULL
    AND occurred_at IS NOT NULL
),

stats AS (
  SELECT
    transaction_id,
    user_id,
    amount,
    occurred_at,
    COUNT(amount) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at
      RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND CURRENT ROW
    ) AS sample_count,
    AVG(amount) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at
      RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND CURRENT ROW
    ) AS mean_365,
    STDDEV_POP(amount) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at
      RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND CURRENT ROW
    ) AS stddev_365
  FROM clean
)

SELECT
  transaction_id,
  user_id,
  amount,
  occurred_at,
  sample_count,
  mean_365,
  stddev_365,
  CASE
    WHEN sample_count < 30 THEN 'insufficient_data'
    WHEN stddev_365 IS NULL THEN 'insufficient_variance'
    WHEN amount > mean_365 + 3 * stddev_365 THEN 'outlier'
    ELSE 'normal'
  END AS outlier_label
FROM stats;

Key points and assumptions:- Exclude null amounts/occurred_at earlier to avoid skew.- Using RANGE BETWEEN INTERVAL 365 DAY PRECEDING ensures a rolling 365-day window per user ordered by timestamp.- For users with <30 samples we mark as 'insufficient_data' (alternatives: lower threshold, use robust metric like median+6*MAD, or expand lookback).- Timezone: query treats occurred_at as UTC; if users have local timezones, join a user_timezones table and convert occurred_at AT TIME ZONE before windowing.- stddev_pop used; if population vs sample distinction matters, use STDDEV_SAMP.- Performance: ensure partition pruning (cluster/partition table by user_id/date) and consider pre-aggregating daily stats if table is huge.

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.