roadmap
- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
Prepare for BigQuery interview questions on how a serverless warehouse stores, scans and bills data. Covers its MPP architecture, nested SQL, partitioning and clustering, loading, and slot pricing.
98 of 98 questions in your plan: 0 known, 0 in progress, 0 due, 0 skipped, 98 new. 0 cut, outside your plan.
BigQuery questions arrive folded into other rounds: the SQL round of data engineer, analytics engineer and analyst loops on teams that run on Google Cloud, and pipeline design, where BigQuery is usually the store the design ends in. With no servers to size, the questions go straight to what a query reads and what it costs: why LIMIT 10 on a SELECT * over an unclustered table does not make it cheaper, why a filter that does arithmetic on the partitioning column can still scan every partition, and why a retried streaming insert can land the same row twice.
Earlier in a career the expectation is the vocabulary: a slot as a unit of parallel compute, a partition as storage the engine can skip, a nested record as a join done at write time. Senior candidates are handed a review instead: here is a query, its execution details and last month's bill, so find where the bytes and the slot time went. A strong answer finds the stage stuck in shuffle, names the skewed key behind it, and weighs a new clustering key, a materialized view or reserved capacity by what each saves against what each costs to keep.
The opening module, Under the Hood, is about analytical warehouses in general, and deliberately so: much of BigQuery's behavior is a general mechanism under a Google name. Massively parallel execution explains what a slot is a share of, and shuffle is where a large join or a skewed GROUP BY spends its time. Columnar layout is why the bill follows the columns you name rather than the rows you keep, and zone maps and pruning are the mechanism behind clustering.
The Core Service module then attaches BigQuery's names to that ground. Its architecture comes first, then nested and repeated types, because table design starts with the schema, then partitioning and clustering, the layout levers the pruning topic prepared. Loading and streaming sits before pricing and slots so that the bill can include how the data arrived, since batch loads and streamed rows are charged differently. ML and BI closes on the consumers that query the tables most.
The last stretch puts those mechanisms under analytical load. Star-schema joins return to shuffle: broadcast the small dimension, or repartition both sides. Window functions show how an OVER clause with no PARTITION BY can funnel a whole table through one worker. Materialized views and approximate aggregation pay less for an answer by computing it ahead of time or accepting one that is close. Column masking ends the plan with who may read a column in the clear once dashboards reach the tables.
A data engineer with a couple of months has time for the by topics study order, with a BigQuery sandbox project open alongside: run each pattern against a public dataset, note the bytes estimate before the run and read the stages in the execution details after it. Analysts are seldom handed the senior cost reviews, so a week out they can cap level at the seniority the posting asks for and put the hours into Core Service and the query patterns, opening Under the Hood when an answer needs a reason. Coming from Snowflake or Redshift, the first module is revision; habits break in the second, where there is no warehouse to size and a nested column often replaces a join. If a posting names Snowflake as well, the [Snowflake roadmap](/roadmaps/snowflake) shares the opening module and parts ways where the money goes, with virtual warehouses billed for the time they run. Neither covers the work around the warehouse, orchestrating dependent jobs or versioning and testing the SQL that transforms the tables; the [Data Engineer roadmap](/roadmaps/data-engineer) places BigQuery inside that job.
A data engineer with a couple of months has time for the by topics study order, with a BigQuery sandbox project open alongside: run each pattern against a public dataset, note the bytes estimate before the run and read the stages in the execution details after it. Analysts are seldom handed the senior cost reviews, so a week out they can cap level at the seniority the posting asks for and put the hours into Core Service and the query patterns, opening Under the Hood when an answer needs a reason. Coming from Snowflake or Redshift, the first module is revision; habits break in the second, where there is no warehouse to size and a nested column often replaces a join.
If a posting names Snowflake as well, the Snowflake roadmap shares the opening module and parts ways where the money goes, with virtual warehouses billed for the time they run. Neither covers the work around the warehouse, orchestrating dependent jobs or versioning and testing the SQL that transforms the tables; the Data Engineer roadmap places BigQuery inside that job.