BigQuery Interview Questions and Answers for 2026: Architecture, Partitioning, and Cost

BigQuery is Google Cloud's serverless, fully managed analytics warehouse. You load data and run GoogleSQL; there are no servers, indexes, or vacuum jobs to manage. The key design decision is separation of storage and compute: data lives in Colossus, Google's distributed file system, in a columnar format called Capacitor, while queries execute on a separate pool of compute units called slots — either side scales independently. Compared with a traditional RDBMS: BigQuery is built for OLAP scans over billions of rows, not OLTP point lookups; it has no B-tree indexes, and primary/foreign keys can be declared but are not enforced; DML works but is optimized for bulk MERGE-style mutation, not high-frequency single-row updates; and billing is by data scanned or slot capacity, not instance size. If pushed, contrast it with a classic MPP warehouse: BigQuery removes cluster sizing entirely.

Partitioning physically splits a table into segments by one column: a DATE/TIMESTAMP/DATETIME column, an integer range, or ingestion time. When a query filters on the partition column, BigQuery prunes whole partitions and never reads them, cutting cost and latency. Partitions also enable per-partition expiration and require_partition_filter, which blocks accidental full scans. Clustering sorts data within storage blocks by up to four columns; at query time BigQuery uses block-level min/max metadata to skip blocks that cannot match a filter. Clustering suits high-cardinality columns like user_id, where partitioning would create too many segments. Two differences worth volunteering: partition pruning is reflected in the dry-run cost estimate, while cluster pruning only reduces bytes at runtime; and background re-clustering is automatic and free. The standard pattern combines both — partition by event date, cluster by the two or three most-filtered columns.

A slot is BigQuery's unit of compute: roughly a virtual CPU with attached memory that executes part of a query. When you submit SQL, the planner breaks it into stages; each stage is split into work units that slots execute in parallel, passing intermediate results through the shuffle layer. More slots means more parallelism, up to the natural parallelism of the query itself. On-demand projects draw from a shared pool with a default per-project cap of roughly 2,000 concurrent slots; when demand exceeds supply, work queues rather than fails. With capacity-based pricing (BigQuery Editions), you buy slot capacity directly, with a baseline and an autoscaling maximum. In an interview, connect slots to diagnosis: contention shows up as queries spending time queued or stages waiting on capacity, which you can inspect in query execution details and the INFORMATION_SCHEMA jobs views.

There are two compute pricing models. On-demand bills each query by bytes processed: because storage is columnar, you pay only for the columns you reference across the partitions and blocks that survive pruning. It needs no commitment and suits spiky or low-volume workloads. Capacity-based pricing — BigQuery Editions (Standard, Enterprise, Enterprise Plus) — bills for slot capacity over time instead of per query, autoscaling between a baseline and a maximum, with optional one- or three-year commitments for discounts. It suits heavy, steady workloads where per-scan billing becomes expensive or unpredictable. Storage is billed separately under both models, with a lower long-term rate for tables or partitions left unmodified for 90 consecutive days. A good closing point: the choice is empirical — compare what your actual monthly bytes billed would cost on-demand against the slot capacity those same queries consume.

ARRAY and STRUCT let BigQuery store nested, denormalized data inside a single row. An ARRAY is an ordered list of values of one type; a STRUCT is a container of named fields — a row inside a column. Combined as ARRAY<STRUCT<...>>, they model one-to-many relationships without a separate table: an orders row can carry all its line items. Capacitor stores nested fields column-by-column, so reading one struct field does not pay for its siblings. The design exists because joins at warehouse scale are expensive; shipping child records inside the parent row turns a join into a scan. You flatten arrays with UNNEST and access struct fields with dot notation. Mention the schema vocabulary too: these surface as REPEATED and RECORD fields, and they are the natural shape for JSON-like event data such as analytics exports.

Four Google systems matter. Dremel is the execution engine: it compiles SQL into a DAG of stages, each executed by many slots in parallel, with a dedicated shuffle tier moving intermediate data between stages. Colossus is the distributed file system holding table data in Capacitor's columnar format, with encodings and statistics that make column scans cheap. Borg, Google's cluster manager, allocates the machines slots run on, and the Jupiter network provides enough bandwidth that compute can read remote storage fast enough to make local disks unnecessary. The interview-worthy consequence is storage/compute separation: loading data does not consume query capacity, compute scales independently of data volume, and features like time travel and zero-copy table clones fall naturally out of immutable columnar storage. Then narrate a query: parse and plan, prune partitions from metadata, execute stage by stage through shuffle, write results to a temporary cached table.

UNNEST turns an array into rows so you can filter, join, or aggregate its elements. In a FROM clause it acts as a correlated cross join: FROM orders, UNNEST(items) AS item pairs each order with each of its items. Three details separate strong candidates. First, a plain CROSS JOIN UNNEST silently drops rows whose array is empty or NULL — use LEFT JOIN UNNEST(items) AS item ON TRUE when parent rows must survive. Second, WITH OFFSET exposes each element's position for order-sensitive logic. Third, for arrays of structs, the alias becomes a struct accessed with dot notation (item.sku, item.qty). Also mention the reverse direction: ARRAY_AGG re-nests rows into arrays after aggregation. A typical exercise: given events carrying ARRAY<STRUCT<key, value>> parameters, extract one parameter with a scalar subquery — (SELECT value FROM UNNEST(params) WHERE key = 'x').

A materialized view precomputes and stores the result of a query over base tables, and BigQuery keeps it incrementally up to date in the background — you do not schedule refreshes. Two behaviors matter. First, smart rewrite: queries written against the base table can be transparently answered from the materialized view when that is cheaper, so consumers benefit without changing SQL. Second, freshness: when the view lags the base table, BigQuery combines the stored result with the delta of recent changes at query time, so reads stay correct; a max_staleness option lets you trade freshness for cost by reading the stored result directly. Limitations to volunteer: only a restricted SQL surface is supported — aggregations and certain joins, no non-deterministic functions; materialized views cannot be layered on other materialized views; and you pay for the view's storage plus refresh compute. Classic use: pre-aggregated daily metrics over a large raw events table.

Batch loading via load jobs uses a free shared compute pool and is the default for anything tolerating minutes of latency; data lands atomically and is immediately consistent. Streaming makes rows queryable within seconds but is billed per volume ingested, so reserve it for genuinely real-time needs — live dashboards, fraud signals. There are two streaming paths. The legacy insertAll API offers best-effort deduplication via an insertId, and rows sit in a streaming buffer before commitment to columnar storage. The Storage Write API is the recommended path: gRPC with protocol buffers, higher throughput at lower cost, a default stream for at-least-once delivery, and application-created streams with offsets for exactly-once semantics. Pre-empt the interview trap: rows still in the streaming buffer cannot be modified by DML and can be missed by copy jobs, so design downstream MERGE or dedup logic to run after the buffer drains.

Group techniques by what drives billing. Scan less: select only needed columns (columnar storage bills per column read), filter on partition columns, cluster on common predicates, and set require_partition_filter on big time-series tables. Guard: use dry runs to estimate, and set maximum_bytes_billed so a runaway query fails instead of costing money; state plainly that LIMIT does not reduce bytes billed. Reuse: the 24-hour query cache is free — identical queries over unchanged tables cost nothing — and materialized views eliminate repeated aggregation. Right-size the model: if steady spend is high, compare on-demand bytes-billed cost against Editions slot capacity using INFORMATION_SCHEMA.JOBS, which records bytes and slot-milliseconds per query. Storage side: partition expiration, dropping abandoned tables, and the automatic long-term rate after 90 unmodified days. If forced to one answer: rank recurring queries by cost in JOBS and fix the top ten — spend is always concentrated.

Three modes exist. Time-unit column partitioning divides on a DATE, TIMESTAMP, or DATETIME column at hourly, daily, monthly, or yearly granularity. Integer-range partitioning buckets an integer column into fixed-width ranges — useful for tenant or ID-based segmentation. Ingestion-time partitioning assigns rows by arrival time and exposes the _PARTITIONTIME and _PARTITIONDATE pseudo-columns for filtering. Pruning fires only when the optimizer can decide which partitions to read from metadata alone, so filters should compare the partition column against constants or constant expressions; deriving the filter value from a join or subquery generally defeats pruning and full-scans the table. Two operational features round out the answer: per-partition expiration ages data out automatically, and require_partition_filter rejects queries that omit a partition filter — cheap insurance on shared tables. Know that partitions per table are capped in the thousands, which is why high-cardinality segmentation belongs to clustering instead.

Date-sharding is the legacy pattern: many tables named events_20260101, events_20260102, queried together with a wildcard (FROM dataset.events_*) and filtered via the _TABLE_SUFFIX pseudo-column. Partitioned tables hold the same data in one table with internal segments. Prefer partitioning: one table means one schema to evolve and one set of permissions; the planner opens far less metadata, so queries start faster; and features like require_partition_filter, partition expiration, and accurate dry-run estimates work properly. Sharding survives in the wild — Google Analytics' BigQuery export ships date-sharded events tables — so know the wildcard syntax, and know that a wildcard query reads every table whose suffix survives the _TABLE_SUFFIX filter. If asked about migration: copy shards into a partitioned table (copy jobs are free of compute charges) or run a scheduled backfill, then repoint consumers.

BigQuery picks between two physical strategies. When one input is small, it broadcasts: the small side is replicated to every slot processing the big side, avoiding repartitioning. Otherwise it performs a shuffle hash join — both inputs are hash-partitioned on the join key through the shuffle tier so matching keys meet on the same workers. That shuffle is where large joins hurt, and where skew appears: if a few key values dominate (NULL, empty string, a default ID), the workers owning those keys become stragglers while everything else idles. In execution details this looks like a stage whose maximum worker time dwarfs the average. Mitigations: filter and pre-aggregate before joining to shrink shuffle volume; route hot keys down a separate broadcast path or exclude them; clean the data so NULL-heavy keys never join; and select only the columns the join needs, since shuffled bytes scale with row width.

Streamed rows land first in the streaming buffer: queryable within seconds, but not yet in columnar storage. Consequences you are expected to know: UPDATE, DELETE, and MERGE fail if they would touch rows still in the buffer, and copy jobs or exports can miss buffered rows — so 'stream, then immediately MERGE' is a broken design. Deduplication depends on the API. Legacy insertAll dedup via insertId is best-effort over a short window: it reduces duplicates, it guarantees nothing. The Storage Write API gives real semantics: the default stream is at-least-once, while application-created streams with tracked offsets give exactly-once within a stream, because a retried append at an already-committed offset is rejected rather than duplicated. A robust pipeline: write with the Storage Write API, treat the raw table as append-only, and build a clean table downstream with periodic MERGE or a ROW_NUMBER()-based dedup keyed on a business key, processing only data older than the buffer horizon.

Make it empirical, from INFORMATION_SCHEMA.JOBS, which records per-query total_bytes_billed and total_slot_ms. Step one: compute what current workloads would cost on-demand — bytes billed times the on-demand rate. Step two: convert slot-milliseconds into an average and peak slot profile; steady 500-slot usage with bursts to 2,000 is a very different purchase from rare spikes. On-demand wins for spiky, low-volume, or exploratory workloads: zero commitment, no idle cost. Editions win when utilization is high and steady: capacity autoscales between a baseline and a maximum, committed-use discounts reward predictability, and per-query cost stops depending on how much data someone scans — which also changes engineer behavior. Hybrid is normal: reservations attach to specific projects or folders, so production ELT runs on capacity while ad-hoc analysis stays on-demand. Close with governance either way: maximum_bytes_billed and custom quotas for on-demand; baseline and autoscale ceilings for capacity.

On-demand dry runs compute an upper bound from metadata: referenced columns, minus whole partitions eliminated by partition pruning. That works because a partition filter maps to partition IDs before execution. Cluster pruning cannot be pre-computed: clustered tables keep min/max metadata per storage block, and blocks are skipped during execution as filters are checked against that metadata. So for a clustered table, the pre-run estimate is a ceiling, and actual bytes billed after completion is often dramatically lower. Practical consequences worth stating: cost reporting must use actual bytes billed from job metadata, never estimates; proving clustering ROI means running representative filtered queries and comparing billed bytes before and after clustering; and clustering column order matters — the sort is hierarchical, so lead with the most selective, most frequently filtered column to keep block ranges tight where it counts. This estimate-versus-actual gap is a favorite senior-round probe because it reveals who has watched real bills.