BigQuery interviews test whether you understand how a serverless warehouse spends money and time: bytes scanned and slots, partitioning and clustering, nested and repeated data, SQL patterns, time travel, caching, ingestion and security. Google Cloud's pricing page lists on-demand queries at $6.25 per TiB scanned with the first 1 TiB each month free, and its quotas page allows up to 10,000 partitions per table (both checked November 17, 2026). Below are 30 questions by level, five scenarios and how interviews test BigQuery.
BigQuery numbers to know
| Item | Per Google Cloud documentation (November 17, 2026) |
|---|---|
| On-demand queries | $6.25 per TiB processed; first 1 TiB per month free (prices in other currencies follow Cloud SKUs) |
| Capacity pricing | Slots in Standard, Enterprise and Enterprise Plus editions: pay as you go with autoscaling, or one- and three-year commitments |
| Partitions | Up to 10,000 per table; one job can modify up to 4,000 |
| Clustering | Up to four clustering columns; automatic reclustering is a free operation |
| Time travel | 2 to 7 days, 7 by default, set per dataset or project |
| Fail-safe | A further 7 days, not configurable |
| Query cache | Typically 24 hours |
Fundamentals
- How does BigQuery work under the hood? Storage and compute are separate: tables live in a columnar format on Google's distributed storage, and queries run on a shared pool of compute measured in slots, with a fast network shuffling data between stages. There are no servers to size.
- What is a slot? A unit of compute capacity used to run query stages. On-demand queries draw from a shared pool; capacity pricing reserves slots for your projects.
- On-demand or capacity pricing? On-demand bills bytes processed, which suits spiky or small workloads. Capacity pricing bills slot time through editions, which suits steady heavy workloads and gives predictable cost.
- Why is SELECT * expensive, and does LIMIT help? BigQuery bills the columns you read, so SELECT * reads every column. On an unclustered table, LIMIT doesn't reduce bytes billed; select only the columns you need and use table preview to look at data.
- How do you estimate cost before running a query? A dry run, such as
bq query --dry_run, or the bytes estimate in the console. Set maximum bytes billed on jobs to make an expensive mistake fail instead of run. - Which partitioning types exist? Time-unit column partitioning (hour, day, month or year), ingestion-time partitioning with the
_PARTITIONTIMEpseudo-column, and integer-range partitioning. - What does clustering do? It sorts storage blocks by up to four columns, so filters on them prune blocks and only scanned blocks are billed. BigQuery reclusters in the background for free, and clustering combines with partitioning.
- Partition or cluster? Partition on a commonly filtered date or integer when you also want partition-level management such as expiration; cluster for high-cardinality filters or several columns. Avoid thousands of tiny partitions.
- How do nested and repeated fields work? STRUCT and ARRAY columns store related data in one row, avoiding joins. Flatten with UNNEST:
SELECT o.id, item.sku FROM orders o, UNNEST(o.items) AS item. - How do you deduplicate rows? With a window function and QUALIFY:
SELECT * FROM events WHERE event_date >= '2026-11-01' QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) = 1. - Datasets, locations and cross-region joins? A dataset lives in one location, and a query can only read data from one location, so plan where datasets live before building pipelines that join them.
Intermediate
- How do you force partition filters? Set
require_partition_filteron the table, so queries without a filter on the partitioning column fail instead of scanning everything. - Why does a query on a partitioned table still scan everything? The filter doesn't constrain the partitioning column directly, for example it is computed through a subquery or wrapped in a function. Filter the column with constant expressions.
- What do materialized views give you? Precomputed results that BigQuery refreshes incrementally, and that the optimizer can use automatically when a query matches them, at the cost of storage and maintenance and some SQL limitations.
- How do you load data? Batch loads from Cloud Storage, which are free operations; the Storage Write API for streaming; the Data Transfer Service for scheduled imports; and external tables when data should stay where it is.
- How do time travel and snapshots work? Query an earlier state with
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)inside the time travel window, restore by copying from it, and use table snapshots or clones to keep a point in time longer. - When is the query cache not used? When the referenced tables changed, the query uses non-deterministic functions such as CURRENT_TIMESTAMP, results go to a named destination table, or the cache has expired, typically after 24 hours.
- How is storage billed? Per byte stored, with a lower long-term rate for tables or partitions not modified for 90 consecutive days, and a choice between logical and physical (compressed) storage billing per dataset.
- How do you secure data? IAM roles at project, dataset and table level; authorized views and datasets to share query results without sharing tables; row-level access policies; and column-level security with policy tags and data masking.
- How do you find the queries that cost the most? Query
INFORMATION_SCHEMA.JOBSfor total_bytes_billed and total_slot_ms by user, project and label over a period, then fix the top few. - What is BigQuery ML? Training and running models in SQL, for example
CREATE MODEL churn OPTIONS(model_type='logistic_reg') AS SELECT ...thenML.PREDICT, without moving data out of the warehouse.
Experienced and senior
- How do reservations and editions work? You buy slot capacity in an edition, set a baseline and autoscaling maximum, and assign projects or folders to reservations, so workloads such as ELT and BI get isolated capacity. Idle slots can be shared.
- How do you diagnose slot contention? Look at job timelines and stages in INFORMATION_SCHEMA and the execution graph: long waits, heavy shuffle and skewed stages show whether to add capacity, rewrite the query or split workloads.
- How do you speed up large joins? Filter and aggregate before joining, join on compact keys, remove skew from hot keys, and replace self-joins with window functions where possible.
- How do you govern cost across many teams? Separate projects per team, custom quotas on bytes per user or project per day, maximum bytes billed on jobs, labels for chargeback, and a weekly review of top queries.
- How do you handle duplicates in streamed data? Deduplicate on a business key during the merge into the reporting table, and use the Storage Write API's committed mode with offsets where exactly-once delivery matters.
- How do you migrate a warehouse to BigQuery? Inventory objects, translate SQL with Google's BigQuery Migration Service, transfer data, redesign for nested fields and partitioning rather than copying old indexes, and reconcile counts and sums before cutover.
- What is BI Engine? An in-memory acceleration layer for BigQuery that speeds dashboard queries from BI tools, sized by the memory reservation you buy.
- How would you model events for analytics? A wide, day-partitioned event table clustered by user or event type, with nested arrays for line items, plus aggregate tables for dashboards. Modelling trade-offs are in our data warehouse interview questions.
- BigQuery, Snowflake or Redshift? BigQuery is serverless and bills bytes or slots; Snowflake bills warehouse time you size; Redshift uses clusters or Serverless with distribution and sort keys. Compare our Redshift interview questions and Snowflake questions for experienced engineers.
Five scenarios interviewers give
- A daily dashboard scans 2 TB per refresh. Add a date filter on the partition column, cluster on the dashboard's filters, and serve it from an aggregate table or materialized view.
- "Resources exceeded during query execution." Usually a global ORDER BY or too much data per stage. Aggregate earlier, add LIMIT to ordered output, or use approximate functions.
- A streamed table shows duplicate events. Retries resent rows. Deduplicate on the event id in the merge step and consider exactly-once writes.
- A join fails because datasets are in different locations. Copy or replicate one dataset into the other's location; a query reads from one location only.
- Monthly spend doubled with no new dashboards. Find the top jobs by bytes billed in INFORMATION_SCHEMA.JOBS; look for SELECT * in scheduled queries and dropped partition filters.
How interviews test BigQuery
Employers don't publish question banks, but data engineer and analytics candidates commonly report a SQL round in BigQuery's dialect (window functions, UNNEST, deduplication), a modelling question, a cost scenario and Google Cloud architecture questions. Practice SQL with our SQL interview questions and pipelines with our Google Cloud interview questions.
Where CoPilot Interview fits
CoPilot Interview is an AI interview assistant whose free AI mock interview builds BigQuery and data engineering questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10. The browser demo at /try has 145 practice questions in 10 tracks, scored out of 10, with no signup. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included; Standard costs $24.99 a month and Pro $39.99. For data roles, see our guide to practicing data engineer interviews with AI. Live coding tasks, take-homes and online assessments usually forbid outside help, so prepare for those; in any live interview, follow your employer's rules.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.
Frequently asked questions
How does BigQuery pricing work?
Compute is either on-demand by bytes processed, listed at $6.25 per TiB with the first 1 TiB per month free on November 17, 2026, or capacity-based by slots through editions. Storage is billed separately.
What is the difference between partitioning and clustering in BigQuery?
Partitioning splits a table into segments by date or integer range that queries can skip; clustering sorts data within the table or partition by up to four columns so filters prune blocks.
Does LIMIT reduce BigQuery cost?
Not on an unclustered table: you are billed for the columns read. Select fewer columns and filter on partition or cluster columns instead.
Is BigQuery a data warehouse or a data lake?
A serverless data warehouse that can also query external data in Cloud Storage and open table formats, so many teams use it across both roles.
How do I prepare for a BigQuery interview?
Load a public dataset, partition and cluster it, compare bytes billed for a few queries with dry runs, and practice window-function and UNNEST queries out loud.
Practice this interview before the real one
The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.
Start a mock interview