Data Engineer interview prep for Google.

First, inspect the BigQuery execution plan and slot utilization to locate bottlenecks (shuffling, data skew, spilled bytes). Second, partition the target table on event ingestion date or timestamp to prune unneeded partitions at query time. Third, cluster the table on high-cardinality query filter columns (e.g., user_id, event_type) to minimize scanned storage bytes. Fourth, replace expensive SELECT * queries with explicit column projections, avoid self-joins by leveraging analytical window functions, and pre-aggregate frequently queried metrics using materialized views or summary roll-up tables.

At-least-once message brokers inevitably produce duplicate events during network retries. To achieve end-to-end exactly-once semantics, pair deterministic deduplication with idempotent writes. Extract a unique natural key (e.g., UUID or source_id + timestamp). In the streaming layer (Beam/Dataflow), deduplicate using sliding state windows backed by State Spec or key-value lookup. For destination databases, use atomic upsert operations (e.g., MERGE INTO in BigQuery or SQL) or append-only logs with downstream deduplication queries (`ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) = 1`).