BigQuery Interview Questions cheat sheet
Storage internals, partitioning, slots, nested data, and cost control answers that BigQuery-focused interviews look for.
Architecture and storage
How does BigQuery separate storage and compute?- Data is stored in Colossus in the Capacitor columnar format, compute runs as slots in the Dremel execution engine, and the Jupiter network moves data between them at petabit scale. Because they scale independently, storage cost is unaffected by query volume and adding compute needs no data reshuffling — the point interviewers want you to contrast with node-based warehouses.
What is the difference between active and long-term storage?- A table or partition not modified for 90 consecutive days automatically drops to long-term storage at roughly half the price, with no change in performance or access. Any modification resets the clock, which is why an unnecessary daily full rebuild of a historical table quietly doubles storage cost. Reads do not reset it.
Does BigQuery have indexes?- Not in the traditional sense. Pruning comes from partition elimination and clustering block metadata. Search indexes exist for text lookups with the SEARCH function, and primary and foreign key constraints can be declared as unenforced metadata that the optimizer uses for join elimination — but there is no B-tree you maintain.
How does BigQuery handle concurrency?- On-demand queries share a large shared slot pool with a fair scheduler; with reservations you assign slots to workloads and idle slots can be shared across reservations in the same edition. There is a default limit of 100 concurrent interactive queries per project, and excess queries queue rather than error, so contention shows as latency.
What is the difference between logical and physical storage billing?- Logical billing charges for uncompressed bytes as BigQuery sees them; physical billing charges for compressed bytes actually stored, including time travel and fail-safe, at a higher unit rate. Physical usually wins when your data compresses well, which is most analytical data, and the choice is made per dataset and changeable every 14 days.
Table design and partitioning
What partitioning options exist and how do you choose?- Ingestion time using the pseudo-column _PARTITIONTIME, a DATE, DATETIME, or TIMESTAMP column, or an integer range. Prefer a real event or business date column so backfills land in the right partition, and use ingestion time only when the arrival date genuinely is the analytical dimension. The limit is 10,000 partitions per table, so daily granularity covers 27 years.
What does require_partition_filter do and why enable it?- It rejects any query on the table that does not filter on the partitioning column, so nobody can accidentally scan the whole history. On a multi-terabyte table this single setting prevents the most common runaway cost incident, and the friction it creates on ad hoc queries is the intended behavior.
How is clustering different from a sort key in other warehouses?- Clustering sorts data within each partition by up to four columns in the order you list them, and BigQuery re-clusters automatically in the background at no cost as data is written. Pruning benefits follow the prefix order, so filtering only on the third clustering column helps far less. There is no manual VACUUM or re-sort step.
When would you use a nested or repeated column instead of a join?- When the child records are always queried with the parent and have a strict containment relationship, such as order line items inside an order. Storing them as an ARRAY of STRUCT avoids a shuffle-heavy join and keeps the natural grain. The trade-off is that updating one nested element rewrites the parent row and BI tools handle nesting unevenly.
Explain UNNEST and the fan-out it causes.- UNNEST flattens an array into rows, producing one output row per element per parent, so any parent-level measure will double count after unnesting unless you aggregate carefully. The usual safe pattern is to aggregate inside a correlated subquery over the array rather than unnesting at the top level of the query.
What is a table snapshot versus a clone?- A snapshot is a read-only, point-in-time copy that stores only the delta from the base table, useful as a cheap recovery point before a risky migration. A table clone is a writable copy that likewise starts charging only for divergence. Both are metadata operations, so they are effectively instant regardless of table size.
Query performance
How do you read the BigQuery query plan?- Look at the stage timeline for the slowest stage, then compare wait, read, compute, and write time within it. High wait means slot contention; a large shuffle output relative to input means an exploding join; and a skewed max-versus-average worker time in a stage means a hot key. Bytes shuffled is often more diagnostic than bytes scanned.
What causes a query to spill to disk or fail with resources exceeded?- Operations that cannot be distributed — an ORDER BY over the full result, a window function without PARTITION BY, or a huge GROUP BY with a single hot key — force data onto one worker. Fixes are adding a partition clause, aggregating before sorting, or approximate functions. Raising the reservation rarely helps because the constraint is per-worker.
How do you optimize a join in BigQuery?- Put the larger table on the left so the smaller side can be broadcast, filter and aggregate before joining rather than after, cluster both sides on the join key, and avoid joining on expressions or casts that block pruning. If one side fits comfortably in memory, BigQuery will broadcast it automatically — verify it did in the plan.
When are approximate aggregation functions appropriate?- APPROX_COUNT_DISTINCT uses HyperLogLog++ with roughly 1% error and a fraction of the memory and shuffle of an exact COUNT DISTINCT, and there are approximate quantile and top-count equivalents. Use them for dashboards and exploration; do not use them for billing, reconciliation, or anything a regulator will check.
What are the caching layers and when do they miss?- Query results are cached for 24 hours per user and returned free if the exact query text runs again against unchanged tables. It misses on nondeterministic functions such as CURRENT_TIMESTAMP, wildcard and external tables, any change to the underlying data, and when caching is disabled. BI Engine is a separate in-memory acceleration layer for dashboards.
What is a materialized view and what are its restrictions?- A precomputed result that BigQuery refreshes incrementally and can substitute into queries automatically even when they do not reference it by name. Restrictions matter: no outer joins in older support tiers, limited window function and UNION support, and it must be able to compute the delta — which rules out many analytic patterns.
Loading, streaming, and DML
Compare batch loads, the Storage Write API, and external tables.- Batch load jobs from GCS are free of query charges and best for bulk ingestion; the Storage Write API is the unified streaming path with exactly-once semantics through stream offsets and lower cost than the legacy insertAll; external and BigLake tables query files in place with no load at all but no BigQuery storage optimizations.
Why should you avoid row-by-row INSERT statements?- DML statements have per-statement overhead and table modification quotas, and each one creates a new commit, producing many small storage fragments. Batch into large loads or use the Storage Write API. The interview point is that BigQuery is an analytical store — treating it like an OLTP database will hit quota errors long before it hits a cost limit.
How do you implement an upsert in BigQuery?- MERGE on a business key from a staging table, deduplicating the source first with ROW_NUMBER because MERGE errors if one target row matches multiple source rows. Include a partition predicate in the ON clause so BigQuery prunes rather than rewriting the whole table, and be aware MERGE rewrites every partition it touches.
What is time travel and how far back does it go?- You can query a table as of any point in a configurable window of 2 to 7 days, defaulting to 7, using FOR SYSTEM_TIME AS OF, and restore a dropped table within it. After that a fail-safe period of 7 days exists for Google-assisted recovery under physical storage billing. Both add to physical storage cost, which is a reason teams shorten the window on huge churn tables.
How do wildcard tables and _TABLE_SUFFIX work, and should you use them?- A wildcard in the table name queries many similarly named tables at once, and _TABLE_SUFFIX filters which. They are a legacy pattern from before partitioning existed, they cannot use cached results, and they scan every matching table's metadata. Prefer a single partitioned table; use wildcards only for genuinely separate tables like exported daily events.
Cost, security, and operations
What are the concrete levers for reducing BigQuery spend?- Select only needed columns, filter on partition and cluster columns, set maximum bytes billed per query, preview data with the table preview instead of SELECT star LIMIT 10 (which still scans), materialize repeated expensive subqueries, and move steady workloads to capacity pricing with autoscaling. Then attribute cost with labels and INFORMATION_SCHEMA.JOBS.
Why does LIMIT not reduce the bytes billed?- Billing is based on bytes read from the columns referenced before the limit is applied, so LIMIT 10 on a full scan costs the same as no limit. The exceptions are the table preview in the console and reading from cache, both of which are free. This is the single most common BigQuery cost misconception.
How do you enforce cost guardrails across an organization?- Custom quotas per project or per user on daily bytes billed, maximum bytes billed defaults in tooling, require_partition_filter on large tables, reservations that cap slots for a workload, and budget alerts through billing. Quotas are the only mechanism that hard-stops a query — budget alerts only notify after the money is spent.
What are authorized views and authorized datasets for?- An authorized view can read a source dataset that its own users cannot access, so you expose a filtered or aggregated slice without granting the underlying tables. Authorized datasets extend that to every view in a dataset, and authorized routines do the same for functions. This is the standard way to serve a restricted subset without copying data.
How does BigQuery handle encryption and key management?- Everything is encrypted at rest by default with Google-managed keys; customer-managed encryption keys in Cloud KMS give you rotation control and the ability to disable a key and render data unreadable. There is no customer-supplied key option for BigQuery tables, and column-level encryption with AEAD functions is the tool when you need per-value control.
How do you audit who queried what?- INFORMATION_SCHEMA.JOBS_BY_PROJECT and JOBS_BY_ORGANIZATION give query text, user, bytes billed, and slot time for the last 180 days, and Cloud Audit Logs record data access events routed to a log sink for longer retention. Building a scheduled query over JOBS into a dashboard is the usual way teams find their top ten expensive queries.
From DataLane — tutorials at/blog, practice SQL live in theplayground.