How it works
BigQuery separates durable storage from query compute. Google's distributed storage system, Colossus, stores native table data in a columnar format that supports nested and repeated fields. Queries can read only the necessary columns instead of complete rows.
The query engine uses a distributed execution architecture derived from Dremel. A query has stages that scan data, filter rows, join inputs, and aggregate results across workers. Large joins and GROUP BY operations often need a shuffle to redistribute intermediate data by key.
Slots measure compute capacity as units of query execution resources, not dedicated machines. BigQuery schedules work across slots and can change parallelism during execution. Reservations and edition-based autoscaling let teams allocate capacity and isolate workloads. On-demand pricing instead charges mainly for bytes processed.
Partitioning and clustering reduce the data that workers scan. Partitioning divides a table by a supported date, timestamp, or integer-range key. Clustering organizes storage blocks by selected columns. Eligible predicates let the engine prune partitions and blocks. Neither feature replaces transactional database indexes.
Data enters through batch loads, SQL transformations, change-data-capture pipelines, or stream APIs such as the Storage Write API. In a typical design, an event log absorbs bursts of incoming data. Stream processors produce low-latency views. BigQuery keeps detailed events for exploration and reconciliation. The durable event log is a separate component. BigQuery does not replace it.
What it gives you in a design
- Columnar SQL analytics
- BigQuery supports large scans, joins, aggregations, and window functions without a database cluster that you must manage. It suits questions that examine many records, not request paths that mainly read and write single rows.
- Partitioning and clustering
- Time partitioning suits event data. Clustering can improve pruning for filters such as tenant_id or campaign_id. Choose keys from actual query patterns. Consider mandatory partition filters to prevent accidental scans of the full history.
- Batch and streaming ingestion
- Batch loads suit periodic imports and backfills. The Storage Write API supports records that arrive continuously. Freshness, retry behavior, and deduplication depend on the ingestion method and stream configuration.
- Materialized views and precomputation
- Materialized views can accelerate supported query patterns. BigQuery can automatically rewrite eligible queries to reuse their results. Scheduled aggregate tables allow more flexible transformations but need explicit refresh logic and have a freshness delay.
- Workload and cost controls
- Reservations separate capacity for workloads such as dashboards and exploratory queries. Dry runs, maximum bytes billed, quotas, and partition filters help control cost. Predictable dashboard latency still requires capacity plans and suitable query structures.
- Analytical transactions and access controls
- BigQuery supports atomic statements and multi-statement transactions with snapshot isolation. It also supports row-level and column-level access controls. These features do not make it a good replacement for a high-concurrency transactional database.
Scaling, replication and failure
Storage scales independently of query capacity. Partitioning mainly supports data pruning and management. It is not application-managed sharding, where the client routes each request to a database server. Clustering further reduces scans when filters match the organization of storage blocks.
Query speed depends on more than table size. Slot availability, concurrent workloads, shuffle volume, join expansion, and skew can dominate execution time. More slots do not automatically fix one oversized aggregation group or an unnecessarily large intermediate result.
BigQuery keeps redundant storage across zones within a dataset's location. A multi-region location alone does not guarantee automatic cross-region disaster recovery. Cross-region dataset replication and managed disaster recovery are separate features. Check their availability and failover behavior for the selected configuration.
Queries read consistent snapshots, but pipeline freshness is separate. The warehouse does not contain an event that still waits in a queue. Atomic publication also does not prevent duplicates from producer retries. Define ingestion delivery guarantees, stable event IDs, and deduplication. Define how late events revise aggregates that the system previously published.
Limits and defaults to know
Many of these values are configurable. Check the current documentation before you use an exact value.
| Partitions per table | A partitioned table supports up to 10,000 partitions. Choose partition granularity and retention together. |
|---|---|
| Clustering columns | A table can have up to four clustering columns. Their order affects how well pruning works. |
| Time travel window | You can set the time travel window from 2 to 7 days. The default is 7 days. It does not replace a broader backup and disaster recovery strategy. |
| Query result cache | BigQuery typically keeps cached query results for about 24 hours but does not guarantee retention. Eligibility and invalidation rules apply. Cache hits do not guarantee latency. |
| Key constraints | BigQuery does not enforce declared primary key and foreign key constraints. The ingestion pipeline must preserve the uniqueness and referential integrity that the design requires. |
Use it when
- The design needs historical SQL analysis over large event datasets, with flexible joins and aggregations.
- Analysts need a warehouse that scales without application-managed shards or database servers.
- The design must isolate operational traffic from costly report queries and exploratory queries.
- You need to reconcile low-latency stream aggregates with detailed historical events.
- Teams can manage workload capacity, data freshness, and query cost separately from the path that serves transactional requests.
Avoid it when
- The main workload requires predictable millisecond point reads or high-QPS transactional updates.
- Correctness depends on enforced uniqueness, foreign keys, or frequent contention over individual records.
- Each incoming event must immediately trigger low-latency processing or an external side effect.
- The application needs a high-concurrency API with strict tail-latency guarantees and no precomputed layer to serve requests.
What interviewers ask about it
When BigQuery is on your whiteboard, expect these follow-up questions. Each answer gives the core of a strong answer.
- How can you control query costs?
- Partition by common time filters. Cluster by useful selective predicates. Select only the columns you need. Use dry runs and maximum bytes billed for on-demand queries. Use reservations and workload isolation for capacity-based operation. LIMIT alone usually does not reduce the bytes that queries scan.
- How can you ingest events without duplicate counts from retries?
- Give events stable IDs. Define deduplication explicitly in the pipeline or warehouse transformations. The Storage Write API supports exactly-once writes within a stream with supported stream types and offsets. This does not remove duplicate business events that sources submit independently to different streams.
- Should you query BigQuery directly for a customer dashboard?
- BigQuery can work when query latency, concurrency, and cost meet the product requirements. For dashboards with strict low-latency or high-QPS requirements, precompute aggregates. Serve them from a cache or an analytical database. Use BigQuery for historical analysis and reconciliation.
- How do you handle late events and corrected source data?
- Partition with an explicit event-time or ingestion-time policy. Define which historical periods can change. Recompute affected aggregates or apply idempotent MERGE operations. Show a freshness watermark so users know which results remain provisional.
- What do you inspect when a query becomes slow?
- Check the execution graph for excessive scans, large shuffles, skew, join expansion, and slot contention. Improve pruning, reduce intermediate data, or preaggregate before you assume that you need more compute.
- Can BigQuery be the source of truth for payments or inventory?
- Keep authoritative transactional state in a database that supports those updates. Replicate changes into BigQuery for analysis. BigQuery supports transactions. But its execution model and key constraints, which it does not enforce, poorly suit contention-heavy operational ledgers or seat-allocation paths.
Common mistakes
- You describe BigQuery as a key-value database or use it directly for every latency-sensitive application request.
- You assume partitioning guarantees cheap queries even when the SQL has no predicate that enables partition pruning.
- You assume successful ingestion or declared primary keys prove that business events cannot have duplicates.
- You assume a multi-region dataset automatically provides cross-region failover, or more slots fix every slow query.
Alternatives
- Snowflake
- A managed warehouse better meets the organization's platform and workload isolation requirements. It supports multiple cloud providers and virtual warehouses with independent sizes.
- Amazon Redshift
- The surrounding platform mainly uses AWS. The team wants a managed analytical warehouse that integrates with that ecosystem.
- ClickHouse
- The main requirement is low-latency, high-concurrency analytics over well-understood query patterns. The team can choose or operate a deployment with suitable capacity to serve queries.
- Trino over object storage
- Open table formats, direct control of lake storage, and queries across multiple data sources matter more than one integrated managed warehouse.
Where it is used
BigQuery is a good fit for these interview questions. Each line tells you why.
- Design Google AnalyticsBigQuery suits historical event exploration, session analysis, and distinct-user reports after retry-safe ingestion.
- Design an ad click aggregatorIt provides a historical analytical layer to reconcile provisional stream counts with deduplicated events and late corrections.
- Design an A/B testing platformIt supports warehouse queries that join experiment assignments, exposures, and outcomes for trustworthy experiment analysis.
- Design a metrics monitoring system like DatadogIt can support long-term analytical reports over exported metrics. A separate time-series system handles live dashboards and alert evaluation.
Common questions
What is BigQuery's architecture?
BigQuery separates distributed columnar storage from distributed SQL execution. Colossus stores table data. A Dremel-derived engine executes query stages with slots and exchanges intermediate data through shuffle. Partitioning and clustering help reduce the data that those stages must read.
Is BigQuery an OLTP or OLAP database?
BigQuery is primarily an OLAP system for analytical scans, joins, and aggregations. It supports DML and transactions. But it does not suit frequent single-record updates, enforced uniqueness, or low-latency transactional operations.
How do partitioning and clustering differ in BigQuery?
Partitioning divides a table into segments by a supported partition key. Eligible queries can skip entire partitions. Clustering organizes blocks within a table or partition by selected columns. Queries can then skip more blocks through block pruning. You can combine partitioning and clustering. Their value depends on the filters that your queries actually use.