Google BigQuery
BigQuery is the serverless end of the spectrum: no cluster to size, with queries billed by bytes scanned or against reserved slots. Interviewers use it to test whether I can control cost through table design rather than through infrastructure.
on this pageshowhide
guide
overview
~1 minGoogle BigQuery is a serverless analytical warehouse: you create datasets and tables, write SQL, and Google supplies compute for each query. Since nothing needs provisioning, interviewers move the conversation to what you *do* control — how tables are laid out, how data arrives, and how every query turns into a bill. A strong BigQuery answer ties a design choice to the bytes it saves or the slots it burns. The hub follows that thread. [Serverless architecture](/topics/db-bigquery-architecture) explains where data lives and how a query fans out across shared compute, the ground every cost argument stands on. [SQL and data types](/topics/db-bigquery-sql-types) covers the nested, repeated schema BigQuery favours over normalised joins. [Partitioning and clustering](/topics/db-bigquery-partitioning-clustering) is where most "make this query cheaper" questions are settled, and [pricing and slots](/topics/db-bigquery-pricing-slots) turns that into money: on-demand bytes versus reserved capacity, estimates and guardrails. [Data loading and streaming](/topics/db-bigquery-ingest) contrasts batch and streaming paths on freshness, duplicates and price. [ML and BI integration](/topics/db-bigquery-ml-bi) covers the consumers — dashboards, spreadsheets, in-warehouse models — and the access controls that sit in front of raw tables. Junior rounds check the model: what a slot is, what an array column holds. Senior and principal rounds are cost and design reviews — a runaway dashboard, a table that scans everything, a reservation plan for several teams. Learn the architecture first, then table design, then pricing; loading and the BI layer read easily once those three are in place.
primer
### Storage and compute are separate services Table data sits in Google-managed, columnar storage; compute is a shared pool handed to each query as **slots** for as long as it runs. That separation is why nothing needs provisioning — and why table reads travel over the network rather than from a local disk. ### You pay for what you touch Under on-demand billing the meter is **bytes processed**: the columns a query references, across the partitions it cannot rule out. Under capacity billing it becomes slot time against a reservation you bought. Either way, cost follows the query and the table layout, so interviewers treat it as a design question, not an operations one. ### Layout is the main cost lever - **Column choice** limits width: naming columns instead of `SELECT *` is the first saving. - **Partitioning** limits which slices of a table the planner considers, decided before any data is read. - **Clustering** orders data inside those slices so whole blocks can be skipped while the query runs. A good "why is this expensive" answer names which lever failed. ### Denormalise with nesting, not duplication BigQuery favours wide rows where a parent carries its children as `ARRAY<STRUCT<...>>`. The join disappears, and columnar storage still reads only the leaf fields a query names. The cost moves elsewhere: flattening arrays changes row counts, and an aggregate written for one grain silently runs at another. ### Serverless is not limitless The pool scales by adding workers, not by enlarging one, so work that must gather everything in a single place can still fail or crawl. Recognising that shape is a senior-level signal. ### Freshness has a price and a guarantee Batch loads and streaming writes land in the same tables but differ in latency, billing and duplicate handling. Choosing between them means stating which of the three you are optimising.
- Slot
- A unit of BigQuery compute that the scheduler assigns to query stages. Capacity pricing buys slots; on-demand borrows them from a shared pool.
- Dremel
- The query engine behind BigQuery. It splits SQL into stages that run in parallel on slots, exchanging intermediate data through a shuffle layer.
- Colossus
- Google's distributed file system, where BigQuery keeps table data. Compute reads from it over the network rather than from worker disks.
- Bytes processed
- The on-demand billing measure: the uncompressed size of every column a query reads from the partitions it could not exclude.
- Dry run
- A query submitted for validation and estimation only. It reports expected bytes processed without executing or billing the query.
- Partition pruning
- The planner excluding whole partitions from a query because its filter on the partitioning column rules them out before execution.
- Clustered table
- A table whose data is sorted by up to four chosen columns, letting queries filtering on them skip storage blocks at runtime.
- ARRAY and STRUCT
- BigQuery's repeated and record types. Combined, they let one row hold a list of sub-records, the idiomatic alternative to a child table.
- UNNEST
- The operator that turns an array into a set of rows, usually joined back to its parent row to query the elements.
- Reservation
- A named pool of purchased slots. Assignments bind projects, folders or the organisation to it, so their jobs run on that capacity.
- Storage Write API
- BigQuery's gRPC write interface, whose stream types trade when rows become visible against delivery guarantees.
- Authorized view
- A view granted read access to source data on its own behalf, so readers query the view without holding permissions on the underlying tables.
- INFORMATION_SCHEMA jobs views
- System views listing past jobs with bytes billed, slot time and stage statistics, used to attribute cost and diagnose slow queries.
Follow a table from arrival to a dashboard. Rows arrive by a load job from Cloud Storage, by the Storage Write API, or through a managed transfer, and land in columnar storage split by the table's partitioning and sorted by its clustering. When a query arrives, the planner discards partitions its filter excludes, then compiles the SQL into stages; each slot fetches just the columns named, skips blocks that clustering rules out, and passes intermediate rows through shuffle until the last stage produces the output. The bill is either the bytes those reads covered or the slot time charged to a reservation. Downstream, BI tools, spreadsheets and BigQuery ML issue ordinary query jobs against the same tables, often through views and row policies rather than raw access. Each section of the hub owns one step of that path, and the BI layer multiplies all of them, because every dashboard refresh is another job. The table definition is where several of these decisions meet at once: ```sql CREATE TABLE analytics.events ( event_ts TIMESTAMP, user_id STRING, event_name STRING, items ARRAY<STRUCT<sku STRING, qty INT64>> -- children nested, no join ) PARTITION BY DATE(event_ts) -- planner-time pruning CLUSTER BY user_id, event_name -- runtime block skipping OPTIONS (require_partition_filter = TRUE); -- refuse unfiltered scans ``` Each line is a cost decision made before any query is written. The partition key only pays off if queries filter on it in a form the planner can resolve, and the clustering order only helps queries that filter on its leading columns.
- Serverless Architecture →
Where the subject starts: separated storage and compute, slots and stages, the model every cost answer relies on.
- SQL and Data Types →
Nested and repeated fields shape every table, and flattening mistakes appear at every level.
- Partitioning and Clustering →
The main lever on bytes scanned, and the home of the most practical cost questions.
- Pricing and Slots →
Turns table design into money: on-demand versus capacity, estimates and guardrails.
- Data Loading and Streaming →
Batch versus streaming, compared on freshness, price and duplicate risk.
- ML and BI Integration →
The consumers and access controls layered on top, where cost multiplies across dashboards.
Expecting
LIMITto shrink an on-demand bill: the meter follows columns read, not rows returned — see on-demand pricing.Filtering a partitioned table through a subquery or an opaque expression and assuming pruning still happens — see pruning blockers.
Quoting a dry-run estimate as the exact cost of a query on a clustered table; block skipping happens later, so the figure is a ceiling.
Flattening an array with a comma join and then summing a parent-level column, which silently drops empty parents and multiplies totals.
Answering "Resources exceeded" with "it scales automatically" instead of finding the operation that funnels all rows through one worker.
Claiming streaming ingestion is exactly-once by default; retried appends without offsets are at-least-once, and duplicates are the usual result.
BigQuery is a managed service with no release numbers to pin, so this guide assumes it as it runs today: GoogleSQL as the dialect, the Storage Write API for streaming, and edition-based capacity pricing. Interviewers still probe the older layers, because long-lived projects carry them: - **Legacy SQL versus GoogleSQL.** BigQuery began with its own dialect; the standard-compliant one, now called GoogleSQL, is the recommended choice, yet old scripts and some tool defaults still use legacy SQL. - **`insertAll` versus the Storage Write API.** Row-at-a-time streaming predates the Storage Write API and offered only best-effort deduplication; a duplicate hunt often starts by asking which one a pipeline uses. - **Flat-rate versus editions.** In 2023 capacity pricing moved from flat-rate commitments to editions with slot autoscaling, so "flat-rate" in an answer dates it.
Interviewers expect you to place BigQuery among cloud warehouses. Its closest rivals are Snowflake, Amazon Redshift and Databricks SQL. The difference that comes up most is who decides compute size: Snowflake has you choose and start virtual warehouses, Redshift grew up around provisioned clusters, while BigQuery hands out slots per query. That moves cost control from infrastructure to table layout and query discipline. Around it sit the Google Cloud neighbours a BigQuery pipeline is usually wired to: Cloud Storage as the landing zone for files and external tables, Pub/Sub and Dataflow for streaming, and Looker, Looker Studio and Connected Sheets as consumers. Tools such as dbt run their models as ordinary BigQuery jobs, so the same cost rules apply. Spiky, ad-hoc analytics over large append-heavy data suits BigQuery; steady, latency-sensitive serving belongs to an operational database in front of it.
explore
- Serverless Architecture6 questions
- SQL and Data Types6 questions
- Partitioning and Clustering6 questions
- Data Loading and Streaming6 questions
- Pricing and Slots6 questions
- ML and BI Integration6 questions
questions
page 2 of 2In BigQuery's Connected Sheets, what happens when a pivot table refreshes?
basics
~20 sConnected Sheets issues a real BigQuery query job on every refresh and writes only the results into the spreadsheet. The table itself is never copied into the sheet, so each refresh is billed and subject to the user's BigQuery permissions.
How does BigQuery's Capacitor format store a repeated field such as an ARRAY column?
basics
~20 sCapacitor stores each leaf field of the nested schema as its own compressed column, alongside repetition and definition levels that record where each value sat in the record structure. Reading a nested field touches only that column, and the record shape is rebuilt at read time.
What do _PARTITIONTIME and _PARTITIONDATE expose in an ingestion-time partitioned BigQuery table?
basics
~20 sThey are pseudo-columns on ingestion-time partitioned BigQuery tables giving the partition a row landed in: _PARTITIONTIME as a UTC TIMESTAMP truncated to the partition boundary, _PARTITIONDATE as the DATE. They reflect arrival time, not any timestamp inside the row.
Under BigQuery capacity pricing, do batch load jobs consume your reserved slots?
basics
~10 sOnly if an assignment with job type PIPELINE routes them to a reservation. Otherwise load and export jobs keep running on BigQuery's shared pool, exactly as they do under on-demand pricing.
In BigQuery, how does a multi-statement script execute, and what does BEGIN ... EXCEPTION WHEN ERROR add?
basics
~20 sA script is submitted as one request that runs a parent job, with each statement executing as its own child job and billed as a query. An EXCEPTION WHEN ERROR block catches a failing statement so the script can log it, roll back, or continue.
How do you choose among Pub/Sub BigQuery subscriptions, Dataflow, direct Storage Write API and the Data Transfer Service?
basics
~20 sPick by how much transformation you need and who owns the failure modes. Pub/Sub BigQuery subscriptions for raw pass-through, Dataflow when rows need enrichment or windowing, the Storage Write API when your own service already holds the rows, and the Data Transfer Service for scheduled pulls from SaaS and other warehouses.
showing 31–36 of 36