BigQuery Interview Questions

Common diagnostic. SELECT * scans every column; filtering on a non-partition column scans every partition. The fix: project only needed columns (column pruning saves 10-90% depending on schema), filter on the partition column (partition pruning saves the proportional amount of skipped partitions), ensure clustering aligns with frequently-filtered columns.

-- Bad: scans 10TB
SELECT *
FROM `project.dataset.events`
WHERE event_type = 'purchase'
  AND user_id = '12345';

-- Good: scans ~100GB
SELECT user_id, event_ts, amount_usd
FROM `project.dataset.events`
WHERE _PARTITIONDATE >= '2026-01-01'
  AND _PARTITIONDATE < '2026-02-01'
  AND event_type = 'purchase'
  AND user_id = '12345';
-- Required: table partitioned by _PARTITIONDATE
-- and clustered by (event_type, user_id)

ARRAY_AGG with ORDER BY produces an ordered array of values per group. Common pattern for user-event sequence analysis. Combined with STRUCT for multi-field rows.

SELECT
  user_id,
  ARRAY_AGG(
    STRUCT(event_type, event_ts, page_url)
    ORDER BY event_ts
  ) AS event_sequence
FROM events
WHERE _PARTITIONDATE >= CURRENT_DATE() - 7
GROUP BY user_id;

-- Downstream UNNEST to flatten back to rows when needed:
SELECT user_id, e.event_type, e.event_ts
FROM user_events_with_sequence,
UNNEST(event_sequence) AS e
WHERE e.event_type = 'purchase';

COUNT(DISTINCT) at billion-row scale is expensive because BigQuery materializes the distinct set. Use APPROX_COUNT_DISTINCT (HyperLogLog++) for estimates within ~2% of true count at constant memory. For rolling distinct counts, store HLL_SKETCH per bucket and merge.

-- Approximate count distinct (~2% error, constant memory)
SELECT
  date_trunc(event_ts, DAY) AS day,
  APPROX_COUNT_DISTINCT(user_id) AS approx_dau
FROM events
WHERE _PARTITIONDATE >= '2026-01-01'
GROUP BY day;

-- Mergeable HLL sketches for rolling unique counts
WITH daily_sketches AS (
  SELECT
    date_trunc(event_ts, DAY) AS day,
    HLL_COUNT.INIT(user_id) AS sketch
  FROM events
  WHERE _PARTITIONDATE >= '2026-01-01'
  GROUP BY day
)
SELECT
  day,
  HLL_COUNT.MERGE(sketch) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_unique_users
FROM daily_sketches
ORDER BY day;

QUALIFY is BigQuery's shortcut for filtering on window function output without a CTE wrapper. Cleaner than the SELECT FROM (SELECT ... ROW_NUMBER ...) WHERE rn = 1 pattern. Standard in BigQuery and Snowflake.

-- Top 1 event per user
SELECT user_id, event_ts, event_type
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY event_ts DESC
) = 1;

-- Equivalent without QUALIFY (more verbose):
WITH ranked AS (
  SELECT
    user_id, event_ts, event_type,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_ts DESC
    ) AS rn
  FROM events
)
SELECT user_id, event_ts, event_type
FROM ranked
WHERE rn = 1;

Materialized views: BigQuery auto-refreshes when source data changes; queries automatically rewrite to use the view when applicable. Best for high-frequency aggregates with low-frequency source changes. Limitations: only support a subset of SQL, can't use window functions (until 2025), can't join across datasets without authorized views. Scheduled queries: run on a cron, write to a regular table, full SQL support, you control refresh frequency. Best for complex transformations or when MV restrictions block the use case.

Cluster on the columns most-filtered in queries. Order matters: clustering keys are sorted in the declared order, so the first key has the strongest pruning effect. Common pattern: cluster by (event_type, user_id) when event_type filter is common and user_id is the secondary filter. Don't cluster on too many columns (BigQuery limit is 4 but diminishing returns past 2 in most cases).

Producer sets insertId per record. BigQuery deduplicates within a 1-minute window using insertId for streaming inserts. For exactly-once at scale, use the BigQuery Storage Write API with default streams in committed mode, which provides true exactly-once semantics across the entire pipeline. Discuss why streaming inserts have a per-row cost ($0.05 per GB) on top of storage cost; for high- volume use cases, switch to Storage Write API or batch loading.

Per-tenant project for isolation: each tenant gets its own project, shared dataset via authorized views. Cost attribution via labels on jobs and datasets. Slot reservations per tenant tier (Tier 1 customers get dedicated slots; Tier 2 share a flex pool). Row-level security via BigQuery row access policies for fine-grained isolation. Discuss the trade-off: per-project isolation is most secure but adds operational overhead; shared dataset with row-level security is simpler but requires careful policy management.