Question 1

What is BigQuery?

Accepted Answer

BigQuery is a serverless, highly scalable, columnar data warehouse. You load or stream data in and query it with standard SQL; there are no clusters, nodes or indexes to manage, and storage and compute are separated so each scales and is billed independently. It handles petabyte-scale analytical queries in seconds. The separation of storage and compute is the architectural fact to lead with, because it explains almost everything else — why you can query a table nobody has "attached" to a cluster, why storage is cheap, and why cost control is about how much data a query *scans* rather than how big the warehouse is.

Question 2

How does BigQuery's architecture work under the hood?

Accepted Answer

Storage is Colossus, Google's distributed file system, holding data in the columnar Capacitor format. Compute is Dremel, which decomposes a query into a tree of execution stages across thousands of slots. The two are connected by Jupiter, Google's petabit-scale network, which is what makes separating them practical. Shuffle happens in memory between stages. Naming Dremel, Colossus, Capacitor and Jupiter demonstrates you have read beyond the marketing page. The point to draw out is that because the network is fast enough, compute does not need data locality, which is precisely why BigQuery can be serverless while traditional warehouses cannot.

Question 3

How is BigQuery priced?

Accepted Answer

Two components. Compute: on-demand pricing charges per TB of data *scanned* by a query, or you buy capacity as slot-based editions (Standard, Enterprise, Enterprise Plus) with autoscaling and optional commitments. Storage: active storage per GB per month, dropping to a lower long-term rate for table partitions not modified in 90 days, with a choice of logical or physical (compressed) billing. The insight interviewers want is that on-demand cost depends on columns and partitions read, not on rows returned — so SELECT * on a wide table costs far more than selecting three columns, and LIMIT does not reduce cost at all. That single fact drives most BigQuery cost optimisation.

Question 4

Why does SELECT * cost more than selecting specific columns?

Accepted Answer

BigQuery stores data column by column, so a query only reads the columns it references. SELECT * forces every column to be read, which on a wide table can be an order of magnitude more bytes scanned — and on-demand billing charges per byte scanned. LIMIT does not help because the scan happens before the limit is applied. The LIMIT point is the trap. Candidates routinely say "I would add LIMIT 10 to test cheaply", which is wrong on both counts — it does not reduce the scan, and the right way to test cheaply is the dry-run flag or querying a table preview, which is free.

Question 5

What is table partitioning in BigQuery and what types exist?

Accepted Answer

Partitioning splits a table into segments so queries can prune to only the relevant ones. Three types: time-unit partitioning on a DATE, DATETIME or TIMESTAMP column by hour, day, month or year; ingestion-time partitioning using the pseudo-column _PARTITIONTIME; and integer-range partitioning on an integer column with a defined start, end and interval. The operational lever to name is require_partition_filter, which rejects any query that does not filter on the partition column. On a large table that single setting prevents the accidental full-table scan that generates a shock bill, and mentioning it is a strong signal of production experience.

Question 6

What is clustering and how does it differ from partitioning?

Accepted Answer

Clustering sorts data within each partition by up to four columns, so BigQuery can skip blocks that cannot match a filter. Partitioning gives coarse, guaranteed pruning on one column with a known cost benefit before the query runs; clustering gives finer, best-effort pruning on several columns, and the saving is only known after execution. The combination is the standard design: partition by date, cluster by the high-cardinality columns you filter or join on most, such as customer_id and country. Column order in the clustering definition matters because pruning works left to right — filtering on the second clustering column alone gives much less benefit.

Question 7

What is a slot and how does slot-based pricing work?

Accepted Answer

A slot is a unit of BigQuery compute capacity — effectively a virtual CPU with associated memory used to execute one stage of a query. With on-demand pricing you get a large shared pool and pay per byte scanned. With editions you reserve a baseline of slots with optional autoscaling up to a maximum, pay per slot-hour, and get predictable cost regardless of how much data your queries scan. The decision rule to state: move from on-demand to reservations when your monthly on-demand spend is consistently high and your workload is steady, because reservations convert a variable per-query cost into a capacity cost. The other benefit is workload isolation — separate reservations for ETL and BI stop a heavy load job from starving dashboards.

Question 8

What is a reservation and an assignment in BigQuery?

Accepted Answer

You purchase slot capacity as a commitment or on-demand autoscaling within an edition, carve it into named reservations, and then create assignments that map projects, folders or the organisation to a reservation. A workload runs against whichever reservation its project is assigned to, so you can guarantee ETL never competes with executive dashboards. Idle slot sharing is the detail worth adding: by default, unused slots in one reservation can be borrowed by another, which improves utilisation but weakens isolation. Turning that off is how you enforce a hard boundary, and knowing the trade-off is what makes this a senior answer.

Question 9

How do you control BigQuery costs?

Accepted Answer

Partition and cluster large tables and require a partition filter; avoid SELECT * and select only needed columns; use dry runs and the query validator before running expensive queries; set custom quotas on daily bytes billed per project and per user; use materialised views or scheduled aggregates instead of repeatedly scanning raw data; set table and partition expiration on transient data; and move steady workloads to slot reservations. Custom quotas are the guardrail people forget — a per-user daily bytes-billed limit stops a single runaway query from consuming the month's budget. Naming that alongside the query-level techniques shows you think about controls, not just habits.

Question 10

What is a materialised view and how does it differ from a regular view?

Accepted Answer

A regular view is a stored query that is re-executed and re-billed every time it is referenced. A materialised view precomputes and stores the result, refreshes incrementally as the base table changes, and — crucially — BigQuery can automatically rewrite a query against the base table to use the materialised view when it is beneficial, even if the query does not mention it. That automatic query rewrite is the feature to name, because it means existing dashboards get faster and cheaper with no query changes. The limitations are real though: restricted SQL support, no outer joins in older versions, and a limited set of aggregate functions.

Question 11

What is an external table and when would you use one?

Accepted Answer

An external table lets BigQuery query data that lives outside BigQuery storage — files in Cloud Storage (Parquet, ORC, Avro, CSV, JSON), Bigtable, Cloud SQL or Google Sheets — without loading it. You use it for exploration, for data that changes at source, or to avoid duplicating a data lake, accepting slower performance and no partitioning or clustering benefits. The right recommendation is usually "explore externally, then load for production" because native storage is columnar, compressed and far faster. BigLake tables are the modern evolution, adding fine-grained security and better performance over open formats in Cloud Storage.

Question 12

What is BigLake?

Accepted Answer

BigLake extends BigQuery's governance and performance to data stored in open formats in Cloud Storage or other clouds. A BigLake table gives you row and column level security, caching and metadata acceleration over Parquet or Iceberg files, and lets non-BigQuery engines such as Spark read the same data through a connector that respects the same access policies. The problem it solves is the governance gap of a data lake: with plain external tables, anyone with bucket access bypasses your column-level security entirely. BigLake decouples the access decision from the storage permission, which is the whole point.

Question 13

What are the ways to load data into BigQuery?

Accepted Answer

Batch load jobs from Cloud Storage or local files, which are free of compute charge; the legacy streaming insert API and the newer Storage Write API for real-time ingestion, both charged per volume; Data Transfer Service for scheduled loads from SaaS sources and other warehouses; Dataflow or Dataproc pipelines; Datastream for change-data-capture from operational databases; and federated queries against external sources. That batch loads are free while streaming is charged is the cost fact interviewers probe. The follow-up is when streaming is genuinely justified — sub-minute freshness requirements — versus when micro-batch loading every few minutes achieves the business need at a fraction of the cost.

Question 14

What is the Storage Write API and why did it replace streaming inserts?

Accepted Answer

The Storage Write API is a gRPC-based, higher-throughput and lower-cost streaming ingestion path that supports exactly-once delivery through stream offsets, and stream-level transactions where data becomes visible only on commit. The legacy insertAll streaming API offered only best-effort deduplication over a short window and cost more. Exactly-once semantics is the substantive improvement. With the old API, duplicates during retries were a real operational problem that teams solved with downstream deduplication queries; the Storage Write API removes that class of work entirely.

Question 15

What is the streaming buffer and why does it affect DML?

Accepted Answer

Rows ingested via streaming sit in a write-optimised streaming buffer before being committed to columnar storage. They are immediately queryable, but for a period they cannot be modified or deleted by UPDATE or DELETE statements, and partition metadata for them may not be final. This is the answer to "why did my DELETE fail on recently streamed rows?" — a genuinely confusing error message for someone who has not met it. It also explains why a table snapshot or export taken immediately after streaming may not include the newest rows in the expected partition.

Question 16

What is time travel in BigQuery and how long does it last?

Accepted Answer

Time travel lets you query a table as it existed at any point within its time-travel window — configurable from two to seven days, defaulting to seven — using FOR SYSTEM_TIME AS OF. It is how you recover from an accidental UPDATE, DELETE or DROP without restoring a backup. The complement to name is table snapshots and table clones for longer retention: snapshots are cheap point-in-time copies that only store the delta, clones are writable copies that also only bill the difference. Time travel covers accidents in the last week; snapshots cover anything longer.

Question 17

What are nested and repeated fields, and why does BigQuery encourage them?

Accepted Answer

BigQuery supports STRUCT (nested) and ARRAY (repeated) types, so a single row can hold a hierarchy — an order with an array of line-item structs. This denormalises one-to-many relationships into one table, avoiding joins entirely, which is faster because a join requires a shuffle across the cluster while a nested field is read locally with the parent row. The idiom to demonstrate is UNNEST in the FROM clause to flatten an array for querying. Interviewers use this to distinguish people who write BigQuery SQL from people who write ordinary relational SQL against BigQuery — the star-schema-with-joins habit is often the wrong shape here.

Question 18

What causes a query to be slow in BigQuery and how do you diagnose it?

Accepted Answer

Read the query execution details and the stage timeline in the job information. The usual causes are data skew, where one key dominates and a few workers do most of the work; excessive shuffle from large joins; reading far more data than necessary because partitioning or clustering is missing; a self-join or cross join producing an enormous intermediate; or slot contention with other workloads. Skew is the classic and it shows up as a huge gap between the average and maximum worker time in a stage. The fixes to name are salting the skewed key, filtering earlier, or restructuring the join so the small side is broadcast — BigQuery does this automatically when the small table fits, which is why join order and filter placement matter.

Question 19

How do you handle slowly changing dimensions in BigQuery?

Accepted Answer

Type 1 (overwrite) with a MERGE statement, Type 2 (history) by adding valid_from and valid_to columns and closing the previous row when a change arrives, again with MERGE. BigQuery supports MERGE natively, and the table should be partitioned by the effective date and clustered by the business key for efficient matching. The BigQuery-specific consideration is that DML costs a full scan of the affected partitions, so an unpartitioned dimension table rewritten daily is expensive. Partitioning and restricting the MERGE with a partition filter on the target is the practical optimisation.

Question 20

What is BigQuery ML?

Accepted Answer

BigQuery ML lets you create and run machine-learning models directly in SQL — linear and logistic regression, k-means, matrix factorisation, boosted trees, time-series ARIMA_PLUS, and imported or remote TensorFlow and Vertex AI models. It removes the need to export data to a separate ML environment for many common problems. The argument for it is data gravity: moving terabytes out to train a model is slow, expensive and creates a governance problem, whereas training in place does not. The limitation to be honest about is that it is not a substitute for Vertex AI when you need custom architectures, deep learning or sophisticated experiment tracking.

Question 21

What is a BigQuery scheduled query and when is it the wrong tool?

Accepted Answer

A scheduled query runs a SQL statement on a cron-like schedule, writing results to a table, with a service account and basic failure notification. It is right for simple, single-statement, independent transformations. It is the wrong tool when you need dependencies between steps, retries with backoff, backfills, data-quality gates or lineage — that is Cloud Composer, Dataform or a workflow orchestrator. The failure mode to describe is a chain of scheduled queries timed by hope — step B runs at 02:15 because step A "usually finishes by 02:10". Recognising that dependencies need an orchestrator rather than staggered schedules is exactly the judgement being tested.

Question 22

What is Dataform?

Accepted Answer

Dataform is GCP's managed service for SQL-based transformation inside BigQuery, providing dependency resolution between models, version control through Git, assertions for data quality, environment separation and generated lineage. It is conceptually equivalent to dbt and is now integrated into the BigQuery console. The value over hand-managed scheduled queries is the dependency graph and the assertions: transformations run in the correct order automatically and the pipeline fails loudly when a uniqueness or null assertion breaks, rather than silently publishing bad data.

Question 23

How do you implement column-level security in BigQuery?

Accepted Answer

Create a taxonomy with policy tags in Data Catalog, attach a policy tag to sensitive columns in the table schema, and grant the Fine-Grained Reader role on the tag only to authorised principals. Users without that role can query the table but not the tagged columns, which fail with a permission error rather than returning nulls. The advantage over authorised views is that one physical table serves all consumers with the policy enforced in the storage layer — no duplicate views to maintain and no way to query around it. Pair it with dynamic data masking when you want unauthorised users to see a masked value rather than an error.

Question 24

What is row-level security in BigQuery?

Accepted Answer

A row access policy is a filter attached to a table that restricts which rows a principal can see, defined with a predicate that can reference SESSION_USER() or check group membership. Multiple policies are combined with OR, and a user with no matching policy sees no rows. The classic use is multi-tenancy: one events table with a policy per tenant group so each customer's analysts see only their own rows. The caveat to note is that aggregates respect the filter, so totals differ per user, which is correct but can confuse people comparing numbers.

Question 25

What is the difference between a table snapshot, a table clone and a copy?

Accepted Answer

A snapshot is a read-only point-in-time capture that stores only the bytes that differ from the base table, so it is very cheap. A clone is a writable copy that also starts as a delta and only bills for changes you make. A copy is a full independent duplicate billed at full storage cost. The use cases separate cleanly: snapshots for backup and audit points, clones for giving a team a safe production-like environment to experiment in without duplicating cost, copies when you genuinely need independence such as moving data between organisations.

Question 26

What are BigQuery quotas and limits worth knowing?

Accepted Answer

Notable ones include a maximum of 1,500 table-modifying operations per table per day, limits on concurrent interactive queries, a six-hour maximum query execution time, limits on load jobs per table per day, and a maximum result size for interactive queries unless you write to a destination table. The 1,500 modifications per day limit is the one that catches teams who write a streaming pipeline as one DML statement per event — it works in testing and fails in production. The right pattern is batching or streaming ingestion, and recognising that anti-pattern is the point of the question.

Question 27

How would you design a table for 10 billion events per day?

Accepted Answer

Partition by ingestion date or event date with require_partition_filter enabled and a partition expiration that matches retention; cluster by the two or three columns most used in filters and joins, ordered by selectivity; use nested and repeated fields instead of joins for one-to-many detail; ingest with the Storage Write API or micro-batch loads rather than per-row DML; and build materialised views or scheduled aggregates for the dashboards so they never touch raw data. The reasoning to make explicit is that partitioning controls the *cost floor* — every query must prune — while clustering controls the marginal cost within a partition. Adding retention and aggregate layers shows you are designing a warehouse, not just a table.

Question 28

What is the difference between BigQuery and Cloud SQL?

Accepted Answer

BigQuery is an OLAP analytical warehouse: columnar, distributed, optimised for scanning huge volumes with aggregate queries, with high per-query latency and no support for high-frequency single-row updates. Cloud SQL is OLTP: row-oriented managed MySQL, PostgreSQL or SQL Server, optimised for many small, low-latency transactional reads and writes with indexes and constraints. The mistake this question catches is using BigQuery as an application backend. Point lookups take on the order of a second and cost a scan, and there are no unique constraints or foreign keys. The right architecture pairs them: Cloud SQL for the application, BigQuery for analytics, with Datastream or Dataflow replicating between them.

Question 29

What is Datastream and how does it fit with BigQuery?

Accepted Answer

Datastream is a serverless change-data-capture service that replicates changes from MySQL, PostgreSQL, Oracle and SQL Server into BigQuery or Cloud Storage with low latency. Its BigQuery destination applies inserts, updates and deletes directly so the target stays in sync without you writing merge logic. The value over a nightly batch export is freshness plus reduced load on the source database, because CDC reads the transaction log rather than running large SELECTs. It is the standard modern answer to "how do we get our operational data into the warehouse continuously?"

Question 30

What is a BigQuery INFORMATION_SCHEMA view and what would you use it for?

Accepted Answer

INFORMATION_SCHEMA exposes metadata as queryable views — job history, table and column definitions, partition details, streaming buffer state, reservation and slot usage, and access policies. It is how you build cost and performance dashboards, find unused tables, or identify the most expensive queries and users. The concrete answer to give is a query over INFORMATION_SCHEMA.JOBS grouping total_bytes_billed by user and query hash over the last 30 days — that single query is how most teams find their cost problem. Being able to describe it makes the whole cost-optimisation topic concrete.

Question 31

What is query caching in BigQuery?

Accepted Answer

BigQuery caches query results for roughly 24 hours, and an identical query against unchanged tables returns from cache instantly and free of charge. The cache is per user and is invalidated by any change to the referenced tables, and it is bypassed for queries using non-deterministic functions such as CURRENT_TIMESTAMP() or for queries writing to a destination table. The non-determinism point explains a common surprise: adding CURRENT_TIMESTAMP() to a dashboard query silently disables caching and multiplies its cost. Interviewers like it because it connects a small SQL habit to a large bill.

Question 32

What is BigQuery Omni?

Accepted Answer

BigQuery Omni lets you run BigQuery analytics on data stored in Amazon S3 or Azure Blob Storage without moving it, by running BigQuery compute in those clouds and returning only results. It gives one query interface and one governance model across clouds while avoiding large egress charges. The driver is usually regulatory or contractual — data that cannot leave a particular cloud — or simply the cost of egressing petabytes. The limitation to acknowledge is that cross-cloud joins move data and that not every BigQuery feature is available in Omni regions.

Question 33

How do you share BigQuery data with an external organisation?

Accepted Answer

Use Analytics Hub, which lets a publisher create a listing in an exchange and subscribers attach it as a linked dataset in their own project. No data is copied — subscribers query the publisher's data in place and pay their own compute cost, while the publisher retains control and can see usage metrics. The contrast is with the old approach of exporting to Cloud Storage and having the partner load it, which creates stale copies, no revocation and no usage visibility. Analytics Hub gives revocable, in-place, auditable sharing, which is a genuinely different capability.

Question 34

What is the difference between logical and physical storage billing?

Accepted Answer

Logical billing charges for the uncompressed size of your data. Physical billing charges for the actual compressed bytes stored, at a higher per-GB rate but usually a much lower total because compression ratios are often 4x or better. Physical billing also charges separately for time-travel and fail-safe storage. The decision is a per-dataset calculation, and INFORMATION_SCHEMA exposes both figures so you can compute the break-even directly rather than guessing. Highly repetitive data compresses extremely well and almost always wins on physical billing; the caveat is that switching has a cooling-off period.

Question 35

A dashboard query that ran in 3 seconds now takes 90 seconds. What do you check?

Accepted Answer

Compare job execution details between a fast and a slow run. Check whether the underlying table grew or lost its partition pruning because a filter changed to a non-partition column or wrapped it in a function; whether slot availability dropped because another workload is consuming the reservation; whether the query stopped hitting cache; and whether a schema or clustering change altered the plan. The subtlest cause worth naming is a filter like WHERE DATE(event_ts) = ... on a table partitioned by a different expression, or CAST on the partition column, which defeats pruning silently. The query still returns correct results, just after scanning everything.

Question 36

What is a wildcard table and what is _TABLE_SUFFIX?

Accepted Answer

A wildcard table queries many similarly-named tables at once, such as `events_*`, and the pseudo-column _TABLE_SUFFIX holds the part matched by the wildcard so you can filter to a subset. It was the standard pattern for date-sharded tables before partitioning existed. The recommendation is to prefer a single partitioned table over date-sharded tables — partitioning has better metadata handling, lower per-table overhead and cleaner DML. Wildcards remain useful for legacy datasets and for the GA4 export, which is still date-sharded.

Question 37

How do you handle a very large JOIN in BigQuery?

Accepted Answer

Filter both sides as early as possible so less data enters the shuffle; ensure both tables are clustered on the join key; check for skew on the join key and salt it if one value dominates; consider denormalising the smaller side into nested fields to avoid the join entirely; and, if one side is small, rely on BigQuery's broadcast join, which avoids shuffling the large side. Understanding that a shuffle join redistributes both tables across workers by key, while a broadcast join copies the small table to every worker, is what lets you reason about all of these fixes rather than memorising them. Interviewers push on this to see whether you understand distributed execution.

Question 38

What are BigQuery best practices for schema design?

Accepted Answer

Prefer denormalisation with nested and repeated fields over star-schema joins; choose the narrowest appropriate types; avoid enormous string columns in frequently-scanned tables; partition every large fact table and set expiration; cluster on the columns used in filters; use descriptions on tables and columns because they feed Data Catalog; and avoid tables with thousands of columns since wide schemas hurt both cost and readability. The reasoning to give is that BigQuery costs are driven by bytes read per column, so schema design is cost design. That reframing — schema as a cost decision rather than only a modelling decision — is what distinguishes a BigQuery answer from a generic data-warehouse answer.

Question 39

What is the BigQuery Data Transfer Service?

Accepted Answer

A managed service that schedules and automates recurring loads into BigQuery from SaaS applications such as Google Ads, YouTube, Campaign Manager and Google Play, from other warehouses like Amazon Redshift and Teradata for migration, and from Cloud Storage and Amazon S3 on a schedule. The migration connectors are the underrated part — the Teradata and Redshift transfers include schema translation and are how large lift-and-shift warehouse migrations are actually executed, rather than by hand-writing extracts.

Question 40

Design an analytics platform on GCP for a retailer with 500 stores, real-time sales and daily reporting.

Accepted Answer

Ingest point-of-sale events through Pub/Sub, process with Dataflow for validation and enrichment, and write to BigQuery with the Storage Write API for exactly-once delivery. Land raw immutable events in a partitioned table; build curated and aggregate layers with Dataform on a schedule; serve dashboards from materialised views in Looker or Looker Studio. Replicate the operational inventory database with Datastream. Apply column-level policy tags to customer PII and row-level policies per region. Use slot reservations with separate assignments for ETL and BI, and BigQuery ML for demand forecasting. The closing scenario. The markers of a senior answer are separating raw, curated and aggregate layers; isolating ETL from BI slots so a heavy load cannot slow the morning dashboards; and building governance in from the start rather than adding it later.

GCP BigQuery Interview Questions and Answers

How to use this set