134 BigQuery Interview Questions and Answers (2026)

BigQuery is Google Cloud's fully managed, serverless data warehouse built for analytics at petabyte scale. Unlike a traditional relational database, it separates storage from compute, uses columnar storage with a massively parallel query engine (Dremel), and is designed for large analytical scans rather than transactional row-by-row operations.

• Purpose: OLAP, not OLTP:

  • Traditional RDBMS (e.g. MySQL, Postgres) optimizes for many small transactional reads/writes with row storage and indexes.

  • BigQuery optimizes for scanning huge volumes to aggregate/analyze, not high-frequency single-row updates.

• Storage and compute are decoupled: Data lives in Google's Colossus filesystem; queries run on ephemeral compute (slots) provisioned on demand, so you scale each independently.

• Columnar storage (Capacitor): Only the columns a query touches are read, which is far cheaper for wide analytical scans.

• No infrastructure to manage: No servers, no indexes to tune, no vacuuming: you write SQL and Google manages provisioning and scaling.

BigQuery excels at analytics because it removes operational burden while delivering elastic, massively parallel performance and a pay-for-what-you-use cost model.

• Serverless elasticity: It automatically allocates thousands of slots to parallelize a query, so scale grows with the workload without capacity planning.

• Separation of storage and compute: You store cheaply and only pay compute when querying; multiple workloads share the same data without copies.

• Standard SQL plus rich features: ANSI SQL, nested/repeated fields (STRUCT/ARRAY), window functions, and built-in ML (BigQuery ML) and geospatial.

• Cost control levers: Partitioning and clustering prune scanned bytes; on-demand (per-TB) or capacity (reserved slots) pricing fits different patterns.

• Ecosystem integration: Streaming ingestion, federated queries, and native connectors to Looker, Dataflow, and BI tools.

BigQuery is a data warehouse, not a general-purpose database. It uses SQL and looks database-like, but it's engineered for large-scale analytical querying (OLAP), not for transactional workloads (OLTP).

• Why it's a warehouse: Columnar storage, massive parallel scans, and optimization for aggregation over billions of rows.

• Why it's not a typical database:

  • No traditional indexes, no primary-key enforcement, and mutations (UPDATE/DELETE) are supported but not meant for high-frequency single-row transactions.

  • Not for low-latency point lookups or high write concurrency: use Cloud SQL, Spanner, or Bigtable for that.

• Practical framing: Think of it as the analytical layer downstream of your operational databases.

BigQuery is an OLAP analytical engine built for large scans, not row-level transactions: it lacks fast single-row lookups, low-latency point updates, and true multi-statement transactional semantics, so OLTP-style workloads perform poorly and cost too much.

• Columnar storage favors scans, not point reads: Fetching a single row still reads column blocks; there are no indexes for millisecond key lookups.

• High per-query latency and overhead: Each query has planning/startup cost, unsuited to thousands of tiny requests per second.

• Mutations are expensive and rate-limited: Frequent UPDATE/DELETE rewrite storage and hit DML quotas; not designed for constant small writes.

• No OLTP transactional guarantees: It is not built for concurrent ACID transactions across many small rows the way Postgres or Spanner are.

• Right tool instead: Use Cloud SQL, Spanner, or Bigtable for transactional/high-frequency workloads.

Datasets are logical containers for tables and views within a project; tables store the actual data; views are saved queries that present data without storing it. You use datasets to organize and control access, tables to hold data, and views to abstract or restrict what consumers see.

• Dataset: A top-level grouping bound to a location/region; the unit for IAM access control and organization.

• Table:

  • Physically stores rows in columnar format; can be partitioned and clustered for performance.

  • Use for raw and curated data you actually persist.

• View (logical):

  • A stored SQL query re-run each time it is queried; no storage, always fresh, but recomputes cost.

  • Use to simplify complex joins, enforce column/row-level security, or expose a stable interface.

• Materialized view: Precomputes and stores results, auto-refreshed; use when the same aggregation is queried often for speed and cost savings.

They are BigQuery's complex/nested types: an ARRAY is an ordered list of values of the same type, and a STRUCT is a container of named, typed fields. Together they let you model nested and repeated data inside a single row, avoiding joins.

• ARRAY (repeated field):

  • Ordered collection of elements of one type; cannot contain NULLs directly and cannot nest an ARRAY of ARRAY (wrap in a STRUCT).

  • Flatten with UNNEST() to turn elements into rows.

• STRUCT (record):

  • Groups multiple named fields of possibly different types; accessed with dot notation like person.name.

  • Can be nested and combined, e.g. an ARRAY of STRUCT models a repeated record (one-to-many).

• Why they matter: Enable denormalized storage that mirrors JSON, reducing costly joins and improving performance on analytical queries.

A table physically stores data (and costs storage), while a view is a saved query that stores no data and computes results at query time from underlying tables.

• Table:

  • Holds actual rows in columnar storage; you pay for storage and for bytes scanned when queried.

  • Variants: native, external, partitioned, clustered.

• Standard view:

  • A logical, virtual table defined by SQL; re-runs the query each time, so it reflects the latest base data.

  • No storage cost, but you pay to scan the underlying tables on each query. Useful for abstraction and column/row-level security.

• Materialized view (middle ground): Precomputes and stores results, refreshing incrementally; faster and cheaper for repeated aggregations, but has restrictions on the SQL allowed.

BigQuery (GoogleSQL) supports a rich set of scalar types for numbers, text, time, and geography, plus complex types for nesting. Knowing the categories helps you pick precise, storage-efficient columns.

• Numeric: INT64, FLOAT64, and exact decimals NUMERIC and BIGNUMERIC (use for money to avoid float error).

• String and binary: STRING (UTF-8) and BYTES.

• Boolean: BOOL.

• Date and time: DATE, TIME, DATETIME (no zone), and TIMESTAMP (UTC-based, zone-aware).

• Specialized: GEOGRAPHY for spatial data, JSON for semi-structured data, INTERVAL, and RANGE.

• Complex/nested: STRUCT (record of named fields) and ARRAY (repeated values), which can be combined for nested data.

BigQuery supports a rich set of scalar types (INT64, FLOAT64, NUMERIC, BOOL, STRING, BYTES, DATE, TIMESTAMP) plus complex types (STRUCT, ARRAY, JSON, GEOGRAPHY), and the type you pick affects how bytes are stored, scanned, and billed.

• Numeric types: INT64 and FLOAT64 are fixed 8 bytes; NUMERIC/BIGNUMERIC cost more storage (16/32 bytes) but give exact decimal precision.

• String and byte types: Billed by actual UTF-8 length + 2 bytes, so storing numbers or dates as STRING wastes space and blocks range pruning.

• Temporal types: Native DATE/TIMESTAMP enable partitioning and cheaper scans versus strings you must PARSE at query time.

• Complex types: STRUCT and ARRAY model nested/repeated data without joins, reducing shuffle and duplication.

• Cost/performance link: Columnar storage means you pay for bytes in the columns scanned, so a tight type (INT64 over STRING) shrinks scans and query cost.

Schema auto-detection samples a subset of the input data (typically up to the first ~100/500 rows) to infer column names and types, which is convenient for exploration but risky for production where correctness and stability matter.

• How it works:

  • Enabled via --autodetect (or in the console); BigQuery scans sample rows and picks the most likely type per column, using headers for names in CSV.

  • Works for CSV, JSON, and self-describing formats (Avro, Parquet, ORC carry their own schema).

• Limitations:

  • Sampling can misread types (an all-integer sample becomes INT64, then a later decimal fails) or infer STRING too eagerly.

  • No control over modes, descriptions, or NUMERIC vs FLOAT64 choices.

• Use an explicit schema when:

  • Production/repeatable loads where the schema must be stable and enforced.

  • You need precise types (dates, decimals), REQUIRED/NULLABLE modes, or nested STRUCT/ARRAY definitions.

  • Data is sparse or inconsistent early on, so a sample would mislead detection.

Loaded data is physically stored in Google's distributed file system, Colossus, written out in the columnar Capacitor format, completely separate from the compute that queries it.

• Storage lives in Colossus:

  • Google's global distributed storage system (successor to GFS), not on the query nodes' local disks.

  • Data is chunked, replicated, and spread across many machines for durability and parallel reads.

• Separation of storage and compute: Because tables live in Colossus and not on slots, storage and compute scale independently.

• Format on disk: Stored column-by-column in Capacitor, compressed and encoded, not as the original CSV/JSON/etc.

• You interact with it only as a logical table; the physical files are managed by Google and not directly accessible.

No: for native BigQuery tables you cannot access the underlying physical files, because they are stored internally in Google's proprietary Capacitor format on Colossus and managed entirely by the service.

• Native tables are opaque:

  • You see a logical table and query it with SQL; the physical layout, files, and replicas are hidden.

  • There is no file path or bucket to browse.

• How to get the data out: Export it, e.g. EXPORT DATA or bq extract, to Cloud Storage as CSV, JSON, Avro, or Parquet.

• External tables are the exception: If a table is defined over files in Cloud Storage (external/BigLake), those source files are directly accessible because they live in your bucket.

Internally BigQuery stores data in Capacitor, its proprietary columnar, compressed storage format, held on the Colossus file system.

• Columnar: Each column is stored separately so queries scan only referenced columns.

• Compressed and encoded: Uses techniques like dictionary and run-length encoding, plus stored statistics for pruning.

• Not your source format: Whatever you load (CSV, JSON, Avro, Parquet) is converted into Capacitor on ingest.

• Distinct from open formats: Capacitor is internal to BigQuery, unlike Parquet/ORC used for external tables.

Partitioning splits a table into segments based on a column value, letting BigQuery scan only the partitions relevant to a query (partition pruning) instead of the whole table, which lowers both query time and cost.

• Types of partitioning:

  • Time-unit column: partition by a DATE/TIMESTAMP/DATETIME column (daily, hourly, monthly, yearly).

  • Ingestion time: BigQuery partitions by load time, exposed as _PARTITIONTIME.

  • Integer range: partition by buckets of an integer column.

• How it helps performance and cost:

  • A WHERE filter on the partition column prunes irrelevant partitions, so fewer bytes are scanned.

  • On-demand pricing bills by bytes scanned, so pruning directly cuts cost, and less data means faster queries.

• Practical notes:

  • Limit of ~4,000 partitions per table, so choose a granularity that fits.

  • Enable require_partition_filter to force queries to filter on the partition column and avoid accidental full scans.

  • A query without a partition filter scans all partitions, negating the benefit.

Batch loading from GCS is done through BigQuery load jobs, which you can trigger via the console, bq load CLI, the API/client libraries, or automatically via scheduling and event-driven triggers.

• Manual / interactive:

  • Cloud Console UI for one-off loads.

  • bq load CLI, e.g. specifying source URI, dataset.table, and format.

• Programmatic:

  • Load-job API through client libraries (Python, Java) for embedding in applications.

  • Supports schema autodetection or explicit schema, plus WRITE_APPEND/WRITE_TRUNCATE dispositions.

• Scheduled / automated:

  • BigQuery Data Transfer Service for recurring GCS loads.

  • Event-driven: Cloud Functions/Cloud Run triggered by GCS object-finalize events, or orchestrated via Cloud Composer/Airflow.

• Formats and wildcards: Accepts CSV, JSON, Avro, Parquet, ORC; use URI wildcards (gs://bucket/path/*.parquet) to load many sharded files in parallel.

You export by either running an EXPORT DATA SQL statement or submitting an extract job (via API, CLI bq extract, or console): both write table data to Cloud Storage, but the SQL statement is query-driven while the extract job exports a whole table.

• EXPORT DATA statement:

  • A SQL statement whose result set (from any SELECT) is written to Cloud Storage.

  • Lets you filter, join, and transform before export, and supports formats like CSV, JSON, Avro, Parquet.

  • Runs as a query, so it consumes query resources (on-demand bytes scanned or slots).

• Extract job:

  • Exports an entire table or partition as-is, with no transformation.

  • Runs as a free extract job (no query cost), though limited by daily export quotas.

  • Triggered by bq extract, the jobs.insert API, or the console Export button.

• Choosing between them:

  • Need filtering/transformation or complex output layout: use EXPORT DATA.

  • Need a cheap, plain dump of a full table: use an extract job.

  • Both write to Cloud Storage (not local disk) and support wildcard URIs for sharded output of large tables.

BigQuery separates storage and compute, so you pay for them independently: storage by the amount of data stored, and compute (analysis) by either bytes scanned (on-demand) or reserved slots (capacity), plus some ingestion/streaming charges.

• Storage costs:

  • Active storage: tables/partitions modified in the last 90 days.

  • Long-term storage: data untouched for 90+ days is automatically discounted (~50%), with no action needed.

  • Priced per GB per month; choose logical (uncompressed) or physical (compressed) billing.

• Compute (query) costs:

  • On-demand: per TB of bytes scanned, driven by which columns/partitions your query reads.

  • Capacity: per slot-hour via editions reservations or commitments.

• Ingestion and other charges:

  • Batch load and extract jobs are free.

  • Streaming inserts and the Storage Write API are billed by data volume.

  • Storage Read API charges for bytes read by external consumers.

• Free tier: First 10 GB storage and first 1 TB of query processing per month are free.

Because BigQuery stores data column-by-column and (on-demand) bills by bytes scanned, SELECT * forces it to read every column, maximizing both cost and I/O even if you only need a few fields.

• Columnar storage rewards projection:

  • Each column is stored separately, so selecting 3 of 50 columns reads roughly 3 columns' worth of data.

  • SELECT * defeats this by touching all columns.

• Direct cost impact:

  • On-demand pricing = bytes scanned, so wider scans cost proportionally more money.

  • Wide, nested, or STRING columns can dominate the bill even when unused.

• Performance impact:

  • More bytes read means more slot time, more shuffle, and slower queries.

  • It also breaks downstream schema stability if columns are added/reordered.

• Better practice:

  • Select only needed columns; if you truly need many, SELECT * EXCEPT(col) can drop expensive ones.

  • Note: SELECT * with a LIMIT still scans all columns; LIMIT alone does not reduce bytes scanned.

You estimate cost by getting the bytes a query will scan (via a dry run) and multiplying by the on-demand per-TB rate, since bytes scanned is what you're billed for.

• Dry run:

  • Runs validation and returns estimated bytes processed without executing or charging.

  • Use bq query --dry_run or set dryRun / JobConfiguration.dryRun in the API/client libraries.

• Console estimator: The query editor shows a "This query will process X bytes" validator before you run.

• Convert bytes to dollars:

  • Cost ≈ (bytes / 2^40) × on-demand price per TB for your region.

  • Use the Google Cloud Pricing Calculator for a formal quote.

• Caveats:

  • Estimates are upper bounds: partition pruning and cached results may reduce actual bytes.

  • Dry runs don't estimate slot cost under capacity pricing, only bytes scanned.

  • Use maximum_bytes_billed as a guardrail to fail queries that exceed a limit.

No: LIMIT almost never reduces cost. BigQuery bills on bytes scanned from columns, and it must scan the qualifying data before it can apply the limit, so the row cap only trims the returned result, not the data read.

• Why it doesn't help:

  • Cost = bytes read from the columns you select, regardless of how many rows you finally keep.

  • A full column scan still happens even with LIMIT 10.

• What actually reduces cost: Selecting fewer columns, partition pruning via WHERE on the partition column, and clustering.

• The one exception: Previewing data via the table Preview tab or tabledata.list API is free, but that is not a query with LIMIT.

Expiration automatically deletes tables or partitions after a set age, controlling storage cost and enforcing data retention without manual cleanup. You can set it at the dataset default level, on individual tables, or per partition.

• Table expiration:

  • An expiration timestamp on the table; the whole table is deleted when reached.

  • A dataset default expiration applies to newly created tables automatically.

• Partition expiration:

  • Each partition is dropped once its age exceeds the configured limit, so old data rolls off automatically.

  • Measured from the partition's date/time value, not table creation.

• Why configure it:

  • Cost: stops paying to store data you no longer need.

  • Compliance: enforces retention windows (e.g. delete after 90 days).

  • Hygiene: keeps scratch/temp datasets from accumulating.

Standard SQL (now GoogleSQL) is BigQuery's ANSI-compliant, recommended dialect, while Legacy SQL is the older, deprecated BigQuery-specific dialect kept only for backward compatibility.

• GoogleSQL (Standard SQL):

  • ANSI:2011 compliant with modern features: correlated subqueries, complex JOINs, and rich support for ARRAY and STRUCT types.

  • Required for DML, DDL, scripting, and most newer features.

  • The default dialect today.

• Legacy SQL:

  • BigQuery's original dialect with non-standard syntax and quirks (e.g. comma means UNION ALL, not JOIN).

  • Deprecated: no new features, and it lacks standard DML/DDL support.

• Syntax differences to know: Table references: GoogleSQL uses backticks `project.dataset.table`; Legacy uses brackets [project:dataset.table].

• Takeaway: always use GoogleSQL for new work; you switch dialects via a query setting or the #legacySQL prefix.

Once BigQuery is set up, you can access it through the Google Cloud Console UI, the command-line tools, client libraries, the REST API, and various JDBC/ODBC and BI integrations: all backed by the same service and IAM permissions.

• Cloud Console (Web UI): Browser-based interface for writing queries, browsing datasets, and viewing job history.

• Command line: The bq tool (part of the gcloud SDK) for scripting and automation.

• Client libraries: Official SDKs for Python, Java, Go, Node.js, and more for programmatic access from applications.

• REST API: The underlying HTTP API that all other tools call; use it directly for custom integrations.

• Connectivity and BI tools:

  • JDBC/ODBC drivers, plus native connectors for Looker, Data Studio/Looker Studio, and third-party BI tools.

  • BigQuery DataFrames and notebook/Jupyter access for data science workflows.

Yes, BigQuery is genuinely serverless: there are no clusters, nodes, or instances for users to provision, size, or patch. You interact only with datasets and SQL, and Google dynamically allocates the underlying compute (slots) behind the scenes.

• No infrastructure decisions: You never choose node types, disk sizes, or replica counts: capacity scales up and down transparently per query.

• Automatic scaling and maintenance: Google handles provisioning, patching, replication, and failover; there is no downtime for upgrades.

• Pay per use: On-demand billing is by bytes scanned; storage is billed separately. You aren't paying for idle servers.

• Nuance: serverless is not limitless:

  • On-demand queries draw from a shared slot pool subject to quotas; for predictable concurrency you buy reserved capacity (slots/editions).

  • Serverless removes operations, not the need to design good schemas, partitioning, and queries.

Pick the store by workload shape: BigQuery for large-scale analytics (OLAP), Bigtable for high-throughput low-latency key lookups, Spanner for globally distributed strongly-consistent transactions, and Cloud SQL for traditional relational OLTP.

• BigQuery: Ad-hoc analytics, aggregations, reporting, and ML over huge datasets; not for single-row low-latency serving.

• Bigtable: Wide-column NoSQL for massive write/read throughput and millisecond lookups by key (time series, IoT, personalization).

• Spanner: Horizontally scalable relational database with strong consistency and global transactions; for large mission-critical OLTP.

• Cloud SQL: Managed MySQL/Postgres/SQL Server for conventional transactional apps at moderate scale.

• Rule of thumb: "Analyze a lot" → BigQuery; "serve fast at scale" → Bigtable/Spanner; "classic app DB" → Cloud SQL.

BigQuery is a destination and processing engine for analytics, whereas traditional ETL tools are the pipelines that move and transform data. Increasingly BigQuery enables an ELT pattern: load raw data first, then transform it in-warehouse with SQL, reducing the need for separate transformation infrastructure.

• Different roles: ETL tools (Informatica, Talend, Dataflow) extract, transform, and load; BigQuery stores and queries the result.

• ETL vs ELT: Traditional ETL transforms before loading, often on separate compute; BigQuery favors ELT, transforming after load using its own scalable engine.

• Scale and compute: Transformations run as SQL on BigQuery's massively parallel slots instead of a fixed ETL server, so they scale automatically.

• They complement, not replace: You still need ingestion/orchestration (e.g. Dataflow, Fivetran, Dataform, dbt) to land data and manage transformation workflows.

BigQuery can cost more because its default on-demand model charges per byte scanned rather than for provisioned hardware, so poorly pruned queries over large tables get expensive fast, whereas Redshift bills mainly for cluster uptime regardless of query volume.

• Different pricing models:

  • BigQuery on-demand charges by data scanned per query, so many large scans add up quickly.

  • Redshift charges for a running cluster (fixed hourly cost) no matter how many queries you run.

• Workload shape decides the winner:

  • High query volume on stable data: a reserved Redshift cluster or BigQuery editions/slot reservations can be cheaper.

  • Sporadic queries: BigQuery's serverless, pay-per-use model usually wins because there is nothing idling.

• Cost is controllable, not inherent: Partitioning, clustering, avoiding SELECT *, and using flat-rate/reservation pricing sharply reduce BigQuery cost.

Athena can overlap with BigQuery for ad-hoc, serverless SQL over data in object storage, but it is not a full replacement: BigQuery is a managed data warehouse with its own storage engine, while Athena is a query layer (Presto/Trino) that queries files sitting in S3.

• Athena strengths:

  • Serverless SQL directly over S3 files (Parquet, ORC, JSON), no loading step.

  • Natural fit when data already lives in AWS S3 and you want cheap, occasional querying.

• BigQuery strengths:

  • Managed columnar storage optimized for fast, repeated analytics at scale.

  • Rich extras: streaming ingestion, BI Engine, ML with BigQuery ML, materialized views.

• How to choose:

  • Ecosystem: Athena for AWS-centric stacks, BigQuery for GCP.

  • Use Athena for occasional queries over a data lake; use BigQuery as a performant, feature-rich warehouse for regular analytics.

Native tables store data in BigQuery's own managed columnar storage; external tables query data left in place (GCS, Bigtable, etc.) without loading; BigLake tables are external tables enhanced with fine-grained governance and better performance so lake data behaves more like native data.

• Native tables:

  • Data loaded into BigQuery storage; fastest queries, full DML, partitioning/clustering.

  • Use for your core, frequently-queried warehouse data.

• External tables:

  • Point to files in GCS/other sources; no ingestion, always reflects source, but slower and limited features.

  • Use for ad-hoc querying of a data lake or one-time loads.

• BigLake tables:

  • External data plus a metadata layer: row/column-level security, caching, and access delegation so users need no direct file permissions.

  • Use to govern and performantly analyze lake data across engines without copying it into BigQuery.

UNNEST takes an ARRAY and turns its elements into rows, letting you flatten repeated data so it can be filtered, joined, and aggregated with normal SQL. It is typically used in a correlated cross join against the array column so each parent row expands into one row per array element.

• What it does:

  • Converts an array into a table of rows, one per element.

  • For an array of STRUCTs, each resulting row exposes the struct's fields as columns.

• How it is written: Joined via a comma or CROSS JOIN so array elements stay tied to their parent row (a correlated/lateral join).

• Common uses:

  • Filtering on array members, aggregating them, or joining nested items to other tables.

  • Note: a struct alone is accessed with dot notation; you only UNNEST the array that contains it.

BigQuery supports declaring PRIMARY KEY and FOREIGN KEY constraints, but they are informational (NOT ENFORCED) only: the engine does not validate or reject duplicate/orphan rows the way a traditional RDBMS does. They exist mainly to give the query optimizer hints.

• Not enforced by design:

  • You can declare them, but you must specify NOT ENFORCED; BigQuery never checks uniqueness or referential integrity at write time.

  • It is an OLAP/columnar warehouse optimized for scans, not transactional integrity.

• Why the optimizer cares:

  • Constraints act as metadata the optimizer trusts to apply join eliminations and other rewrites, so declaring them can improve plans.

  • If you declare keys that aren't actually unique/valid, you risk wrong results because the optimizer assumes they hold.

• Implications vs a traditional RDBMS:

  • Data quality (dedup, orphan checks) is your responsibility via ETL or QUALIFY/aggregation logic.

  • Modeling often favors denormalization with nested STRUCT/ARRAY over many normalized tables with FKs.

BigQuery is available in many but not all Google Cloud regions, and dataset location is a permanent choice made at creation that dictates where your data physically lives and is processed. It matters for compliance, performance, cost, and cross-region join limitations.

• Location is set per dataset and is immutable: Choose a region (e.g. us-central1) or a multi-region (US, EU); you cannot change it later, only recreate/copy.

• Data residency and compliance: Regulations (GDPR, etc.) may require data stay in a specific geography.

• Query and join constraints:

  • A query processes data in the location of the referenced datasets; you cannot join tables across different locations in one query.

  • Load, export, and copy jobs must respect location alignment.

• Performance and cost: Co-locating data with other services (GCS buckets, apps) reduces latency and egress; pricing can vary by region.

BigQuery supports evolving a table's schema without rewriting or losing data, but only for a safe set of additive/relaxing changes; anything destructive requires recreating the table or a workaround.

• Allowed without data loss:

  • Adding a new column (new column is NULL for existing rows).

  • Relaxing a column's mode from REQUIRED to NULLABLE.

  • Adding fields to a STRUCT (nested column).

• How it's applied: Via ALTER TABLE ADD COLUMN, the API/bq update, or auto-detect on load jobs with ALLOW_FIELD_ADDITION / ALLOW_FIELD_RELAXATION.

• Not directly allowed:

  • Renaming (limited support), dropping columns without rewrite historically, changing a column's data type, or tightening NULLABLE to REQUIRED.

  • Workaround: recreate the table via CREATE TABLE AS SELECT with casts/renames.

Use NUMERIC or BIGNUMERIC for exact decimal arithmetic (money, tax, currency) and FLOAT64 for scientific or approximate measurements where tiny rounding error is acceptable: floating point can't represent many decimals exactly, which is unacceptable when cents must add up.

• FLOAT64 (double precision, 8 bytes):

  • Fast and compact, but binary floating point (e.g. 0.1 + 0.2 != 0.3) introduces rounding drift over aggregations.

  • Good for analytics, ratios, physics, ML features.

• NUMERIC (DECIMAL, 38 digits, scale 9, 16 bytes): Exact base-10 arithmetic covering most financial needs (up to 9 decimal places).

• BIGNUMERIC (76 digits, scale 38, 32 bytes): Same exactness with far greater range/precision: use for high-precision rates, crypto, or intermediate products that overflow NUMERIC.

• Why precision matters for finance: Summing millions of rows in FLOAT64 accumulates error, breaking reconciliation and audits; decimal types guarantee reproducible, penny-accurate totals.

• Trade-off: Decimal types cost more storage and compute, so don't reach for BIGNUMERIC unless the range truly demands it.

The native JSON type stores semi-structured data in a parsed, columnar-friendly form that you can query with field access directly, whereas a STRING column holds raw text you must parse on every query.

• Querying: Native JSON lets you use dot/bracket access like data.address.city; a string requires JSON_EXTRACT/PARSE_JSON first.

• Performance: BigQuery parses and stores JSON once at ingest and prunes to accessed fields, so queries scan less than re-parsing full text strings.

• Validation and types: Native JSON is validated on load and preserves value types (numbers, bools); string storage is opaque and unchecked.

• Flexibility: Both handle schema-drift data, but native JSON keeps you queryable without predefining every field as columns.

• When string is fine: If you only archive the payload and rarely query inside it, STRING avoids parse overhead at ingest.

BigQuery is a serverless, fully managed data warehouse built on Google's internal infrastructure, with a fundamental separation of storage and compute connected by a high-speed network, so each can scale independently.

• Storage layer: Data is held in Colossus, Google's distributed file system, in the columnar Capacitor format, compressed and replicated.

• Compute layer: The Dremel execution engine runs SQL across many workers (slots), building a multi-level serving tree.

• Network layer: The Jupiter network delivers petabit-scale bandwidth so compute reads from storage without a bottleneck.

• Shuffle: An in-memory shuffle tier passes intermediate results between execution stages (joins, aggregations).

• Serverless model: No clusters to manage; you submit SQL and Google provisions capacity automatically.

A slot is BigQuery's unit of compute capacity: a virtual CPU/memory worker that executes one piece of a query's execution plan, and BigQuery dynamically allocates slots across the stages of your query.

• What a slot does: Each slot performs a unit of work (a scan, filter, join, or aggregation step) on a slice of data in parallel with others.

• Dynamic allocation: BigQuery's scheduler distributes available slots across query stages and shifts them as stages complete, so a query with more parallel work gets more slots.

• Pricing models:

  • On-demand: you're billed by bytes scanned and get a large shared slot pool automatically.

  • Capacity/editions: you reserve a fixed number of slots (reservations) for predictable cost and concurrency control.

• Why it matters: Slots are the throughput lever: too few and queries queue or slow down; monitoring slot utilization guides reservation sizing.

Nothing is lost: BigQuery separates storage from compute, so a failed compute node just causes its work to be rescheduled onto other slots, while the data itself sits safely replicated in Colossus.

• Compute is stateless and disposable:

  • Slots hold no permanent data; if a worker dies mid-query, its stage/task is retried on another worker.

  • The user typically sees only a slight delay, not a failure.

• Data is safe independently: Because storage lives in Colossus with replication and erasure coding, a machine loss does not lose data.

• This decoupling is why failures degrade gracefully rather than causing outages or data loss.

Partitioning divides a table into segments by a column so queries can skip whole partitions, and clustering sorts data within each partition by chosen columns so BigQuery reads fewer blocks. Together they cut bytes scanned, which reduces both latency and cost.

• Partitioning: coarse pruning:

  • By date/timestamp (daily, hourly, monthly, yearly), by an integer range, or by ingestion time (_PARTITIONTIME).

  • A filter on the partition column lets BigQuery prune entire partitions, scanning only what's needed.

• Clustering: fine-grained sorting:

  • Up to 4 columns; data is physically co-located and sorted by the clustering key prefix.

  • Filters and aggregations on those columns read fewer storage blocks (block pruning).

• Cost and performance impact:

  • On-demand billing is per bytes scanned, so pruning directly lowers cost.

  • Order clustering columns from most to least frequently filtered, since pruning follows the key prefix.

• Best practices and caveats:

  • Combine both: partition by date, cluster by common filter columns like user_id.

  • A query with no filter on the partition/cluster columns still scans everything; filter early.

Clustering physically sorts and co-locates rows within a table (within each partition, if partitioned) by up to four columns, so BigQuery can prune storage blocks that can't match a filter. It differs from partitioning in granularity and how it's maintained.

• How clustering works:

  • Rows are sorted by the clustering columns in prefix order; each block stores min/max metadata used to skip non-matching blocks.

  • Helps filters, range scans, and aggregations on the clustering columns.

• Clustering vs partitioning:

  • Granularity: partitions are discrete named segments; clustering is a soft sort within data, giving block-level (not partition-level) pruning.

  • Columns: partition by one column; cluster by up to four.

  • Cardinality: clustering handles high-cardinality columns well, whereas partitioning is limited (about 4,000 partitions) and suits low/medium cardinality.

  • Cost predictability: partition pruning gives an upfront bytes estimate before running; clustering benefit is only known after execution.

  • Maintenance: BigQuery automatically re-clusters (via automatic reclustering) as data changes, at no cost.

• Use together: Partition by date for coarse pruning, then cluster by frequently filtered high-cardinality columns for finer pruning.

Both create date/time partitions, but ingestion-time uses when the row arrived while column-based uses a value stored in your data.

• Ingestion-time partitioning:

  • BigQuery partitions by the row's load timestamp, exposed via the pseudo-column _PARTITIONTIME (and _PARTITIONDATE).

  • No real column needed, but you can't control which partition a row lands in beyond load time.

• Column-based (time-unit) partitioning:

  • Partitions by a DATE, TIMESTAMP, DATETIME column (or an integer range).

  • The partition reflects the business meaning of the data (e.g. event date), not arrival time.

• Key practical difference:

  • Column-based lets late-arriving data go into the correct historical partition; ingestion-time would put it in today's partition.

  • Prefer column-based when a meaningful timestamp exists; use ingestion-time for streaming logs where load order equals event order.

Partitioning splits a table into segments that BigQuery can scan selectively, reducing bytes read and cost. BigQuery supports three partition types: time-unit column, ingestion-time, and integer range.

• Time-unit column partitioning:

  • Based on a DATE, TIMESTAMP, or DATETIME column at daily, hourly, monthly, or yearly granularity.

  • Choose when queries filter on a meaningful date/time.

• Ingestion-time partitioning:

  • Partitioned automatically by load time via _PARTITIONTIME.

  • Choose for append-only logs/streams with no natural date column.

• Integer range partitioning:

  • Defined by start, end, and interval on an integer column (e.g. customer_id buckets).

  • Choose when a numeric key drives your filters.

• Limits to remember:

  • Max 10,000 partitions per table; only one partitioning column allowed.

  • For high-cardinality or string keys, use clustering instead.

Use partitioning to prune whole date/range segments before scanning, and clustering to sort data within partitions for finer filtering; they are complementary, not alternatives.

• Partitioning:

  • Best for a single low-cardinality dimension, especially date/time or integer ranges.

  • Gives predictable up-front cost savings via partition pruning and lets you set require_partition_filter.

• Clustering:

  • Best for high-cardinality columns and multi-column filters (up to 4 clustering columns).

  • Sorts blocks so BigQuery skips irrelevant blocks; savings are estimated, not guaranteed up front.

• Cost and performance impact:

  • Both reduce bytes scanned (the main cost driver) and speed up queries.

  • Combine them: partition by date, then cluster by frequently filtered columns for the biggest win.

• Rule of thumb: Partition on the coarse time/range dimension; cluster on the columns used in WHERE, GROUP BY, and joins.

Partition pruning is BigQuery eliminating partitions that can't match your filter, so it only scans the relevant ones and you pay for fewer bytes. It works only when the optimizer can statically resolve the filter against the partitioning column.

• How it works: At query planning, BigQuery reads the filter on the partition column and selects the matching partitions before scanning.

• Requirements to trigger pruning:

  • Filter directly on the partitioning column (or _PARTITIONTIME), not a derived expression.

  • Compare against constants or literals; a subquery or non-deterministic function may prevent pruning.

  • Avoid wrapping the column in a function (e.g. CAST(ts AS STRING)) that hides its value.

• Watch-outs:

  • Filtering a joined column that is only indirectly related to the partition key won't prune.

  • Check the query dry-run bytes to confirm pruning actually happened.

Time/integer-range partitioning doesn't fit string columns directly, so either partition on a derived/ingestion-time key or, more appropriately, use clustering on the string columns.

• Preferred: cluster instead of partition: Clustering supports string columns and high cardinality; cluster by id (or the most-filtered column).

• If you truly need partitions:

  • Use ingestion-time partitioning (_PARTITIONTIME) so data still segments by load date.

  • Or derive an integer-range key, e.g. bucket a hash of id with MOD(FARM_FINGERPRINT(id), N), then PARTITION BY RANGE_BUCKET.

• Rule of thumb: String-only, no time dimension: clustering is usually the right answer; reach for a derived partition only when you need enforced pruning or partition management.

Clustering physically sorts a table's data by one to four columns so BigQuery stores related rows in the same blocks and can skip blocks that don't match a filter, cutting bytes scanned.

• How it works:

  • Rows are sorted by the clustering columns in the order declared; column order matters (leftmost is most selective for pruning).

  • BigQuery keeps block-level metadata (min/max) to skip non-matching blocks (block pruning).

• Benefits:

  • Faster and cheaper filters, joins, and aggregations on the clustering columns, including high-cardinality strings.

  • Works alongside partitioning for a two-level pruning strategy.

• Caveats:

  • Cost savings are estimated, not guaranteed at query time (unlike partitioning).

  • BigQuery auto-reclusters over time as data changes; best gains come when filters use the leading clustering columns.

require_partition_filter is a table option that forces every query to include a filter on the partitioning column, so no one can accidentally run a full-table scan.

• What it does: Queries without a predicate on the partition column fail with an error instead of scanning all partitions.

• Why enable it:

  • Cost control: prevents expensive accidental scans on large partitioned tables.

  • Governance: enforces good query hygiene across teams.

• How to set it:

  • At creation with OPTIONS(require_partition_filter=true) or altered later.

  • The filter must actually prune (literal on the partition column), not just reference it in a way that scans everything.

Federated queries let BigQuery query data that lives outside its managed storage (external sources like Cloud SQL, Bigtable, Cloud Storage, or Spanner) in place, without first loading it. You use them for occasional access or freshness needs; you load data when you want the best performance and lowest cost for repeated querying.

• What a federated query is:

  • BigQuery reads directly from an external system at query time, either via external tables or the EXTERNAL_QUERY() function for operational databases like Cloud SQL/Spanner.

  • Data is never copied into BigQuery-managed storage.

• When to use federation:

  • You need live/fresh data from a source of truth without an ETL lag.

  • Ad hoc or infrequent access where loading isn't worth the effort.

  • Joining BigQuery data with a small external lookup table.

• When to load instead:

  • Repeated, high-volume analytical queries: native columnar storage is faster and cheaper.

  • You want partitioning, clustering, and accurate cost/performance predictability, which external sources limit.

• Trade-offs: Federation avoids duplication and staleness but adds per-query latency and load on the source system, and can't fully leverage BigQuery optimizations.

Google Cloud Storage (GCS) is the natural staging layer because BigQuery batch loads read directly and in parallel from GCS at no ingestion cost, giving you a durable, decoupled buffer between source systems and the warehouse.

• Free, fast, parallel loads: Batch loading from GCS is free (you pay only storage), and BigQuery reads many files in parallel for high throughput.

• Decoupling and durability: GCS acts as a durable landing zone: producers write files independently of when BigQuery ingests them, enabling retries and replays.

• Format and scale flexibility:

  • Supports Avro, Parquet, ORC, CSV, JSON; large exports/imports naturally land in GCS as sharded files.

  • Self-describing formats like Avro/Parquet allow schema autodetection.

• Interoperability: Most GCP tools (Dataflow, Dataproc, Transfer Service) and external pipelines already write to GCS, making it the common integration point.

BigQuery offers batch loading, streaming ingestion, query-result materialization, and third-party/managed transfers; the choice depends on latency needs, cost, and whether the data already lives in GCP.

• Batch load jobs:

  • Load files from GCS or local upload (CSV, JSON, Avro, Parquet, ORC).

  • Free and high throughput; pick it for periodic bulk ingestion where minutes of latency are fine.

• Streaming ingestion:

  • Via the Storage Write API (or legacy tabledata.insertAll); rows are queryable within seconds.

  • Pick it for real-time dashboards and event pipelines; it costs money per row.

• Query results / DML: Write results of a query into a table with CREATE TABLE AS or INSERT; ideal for transformations between BigQuery tables (ELT).

• Managed transfers / connectors:

  • BigQuery Data Transfer Service for SaaS sources and scheduled loads; Dataflow/Dataproc for transform-heavy pipelines.

  • Pick these when you want scheduling, connectors, or transformation without hand-built code.

The BigQuery Data Transfer Service (DTS) is a managed, scheduled ingestion service that automatically pulls data from SaaS apps, Google services, and other warehouses into BigQuery without you writing or hosting pipeline code.

• What it does:

  • Runs recurring, configurable loads on a schedule (e.g. daily) into target tables.

  • Handles retries, backfills, and incremental refreshes for you.

• Common sources:

  • Google products: Google Ads, Campaign Manager, YouTube, Google Merchant Center, Google Analytics.

  • Cloud storage: Amazon S3, Cloud Storage.

  • Warehouses: Teradata, Amazon Redshift (migration transfers).

• Why it simplifies ingestion:

  • No servers or ETL code to maintain: it's fully managed and serverless.

  • You can also run scheduled queries as a DTS transfer for transformations after load.

  • Trade-off: it's extract/load oriented, so heavy transformation still belongs in SQL or a tool like Dataflow.

On-demand pricing charges per byte scanned by each query with no capacity to manage, while capacity-based (slot) pricing charges for a reserved or committed amount of compute (slots) regardless of bytes scanned: you pick based on workload predictability and volume.

• On-demand pricing:

  • Billed per TB of data processed (bytes scanned), with a shared pool of slots.

  • Zero capacity planning; great for spiky, unpredictable, or low-volume usage.

  • Risk: a few large or careless queries can be expensive, and concurrency is capped by the shared pool.

• Capacity-based (slot) pricing:

  • You buy slot capacity via BigQuery editions (Standard/Enterprise/Enterprise Plus) as autoscaling reservations or 1-year/3-year commitments.

  • Cost is fixed/predictable and decoupled from bytes scanned: heavy scanning doesn't raise the bill.

  • Reservations and assignments let you allocate slots across teams/workloads.

• When to choose which:

  • On-demand: small/new teams, exploratory or bursty workloads, hard-to-forecast usage.

  • Capacity: high, steady query volume where fixed cost is cheaper and predictable, or when you need guaranteed concurrency and cost caps.

  • Common pattern: mix them: on-demand for ad hoc, reservations for production pipelines.

Optimize storage cost by reducing how much data you keep hot, letting BigQuery's automatic discounts apply, and choosing the right billing model and table settings.

• Leverage long-term storage: Data not modified for 90 days is auto-discounted ~50%: avoid needlessly rewriting old partitions, which resets the clock.

• Set expiration policies:

  • Use table, partition, and dataset default expiration to auto-delete stale data.

  • Partition expiration is ideal for rolling time windows (e.g. keep 13 months).

• Choose physical (compressed) billing: Billing by compressed bytes is often much cheaper for well-compressing data than logical (uncompressed) billing.

• Reduce redundancy:

  • Avoid duplicate copies and materialized intermediate tables you don't need.

  • Prefer views or clustering over storing many derived tables.

• Manage time travel and snapshots: Lower the time travel window (default 7 days) if you don't need long recovery, since it adds to storage.

Query caching in BigQuery stores the results of a query in a temporary cached table, so an identical query re-run returns instantly and at no cost (0 bytes billed). It is enabled by default per project and per user.

• When the cache applies:

  • The query text is byte-for-byte identical (whitespace/comments count) and references the same tables.

  • The underlying tables have not changed since the result was cached.

  • Results are cached per user for roughly 24 hours (best effort).

• When the cache does NOT apply:

  • Non-deterministic functions in the query (e.g. CURRENT_TIMESTAMP(), RAND(), CURRENT_USER()).

  • Any referenced table changed, or the query hits a streaming buffer, external table, or wildcard tables.

  • Results are too large to cache, or the query uses DML/DDL, or destination table is specified.

  • Caching is explicitly disabled via use_query_cache=false.

• Key point for interviews: Cache is per-user and per-project by default, so one user's cached result isn't automatically shared with others.

Because on-demand billing charges by bytes scanned, reducing cost means reducing the data BigQuery has to read: select fewer columns, prune partitions, cluster, and avoid re-scanning.

• Select only needed columns: BigQuery is columnar, so SELECT * reads every column. Name the columns you need.

• Partition pruning: Partition tables (e.g. by date) and filter on the partition column so only relevant partitions are scanned.

• Clustering: Cluster on high-cardinality filter columns; BigQuery skips blocks that can't match the predicate.

• Materialize expensive results: Use materialized views or pre-aggregated tables so heavy scans run once, not per query.

• Leverage caching and estimate first: Reuse cached results, and check the dry-run byte estimate before running.

Both are storage prices; long-term storage is about half the active rate and kicks in automatically when a table (or partition) hasn't been modified for 90 consecutive days. There is no performance or access difference: it's purely a discount.

• Active storage: Full-rate pricing for tables/partitions edited within the last 90 days.

• Long-term storage:

  • Automatic ~50% discount once 90 days pass with no modification to that table or partition.

  • Applied per partition for partitioned tables, so cold partitions can be discounted while hot ones aren't.

• What resets the clock (back to active):

  • Any modification: loading, appending, DML, or streaming into the table/partition.

  • Querying, copying, or exporting does NOT reset it: reads are free of this effect.

• Practical note: No action needed to opt in; avoid needless rewrites of stable tables so they keep the discount.

You layer two kinds of guardrails: maximum_bytes_billed caps a single query's scan (it fails instead of running if it would exceed the limit), while custom quotas cap total bytes across a whole project or per user per day. Together they stop both one bad query and cumulative overspend.

• maximum_bytes_billed (per query):

  • If the query would scan more than the limit, it errors out and bills nothing.

  • Set it in job config, as a session/default, or per statement; great default safety net.

• Custom cost controls / quotas (aggregate):

  • Project-level query usage per day caps total bytes the whole project can scan daily.

  • Per-user-per-day quota limits any single user, containing one person's runaway usage.

• Complementary guardrails: Billing budget alerts, dry-run estimates before running, and reservations to make heavy workloads cost-predictable.

Approximate aggregate functions like APPROX_COUNT_DISTINCT use probabilistic sketches (HyperLogLog++) to estimate results with far less memory and time than exact aggregation, accepting a small, bounded error in exchange for speed and lower cost at scale.

• Why they exist:

  • Exact COUNT(DISTINCT ...) on billions of rows must track every unique value, which is memory-heavy and can spill or slow down.

  • Sketch-based functions keep a small fixed-size summary, so cost and runtime stay low regardless of cardinality.

• Common functions:

  • APPROX_COUNT_DISTINCT (distinct count), APPROX_QUANTILES (percentiles), APPROX_TOP_COUNT and APPROX_TOP_SUM (most frequent items).

  • Typical error for distinct counts is around 1-2%.

• When to trade exactness:

  • Dashboards, exploratory analysis, and trend monitoring where a small error is irrelevant.

  • Very high-cardinality columns where exact counts are slow or expensive.

• When NOT to use them: Billing, financial reconciliation, or compliance figures that must be exact.

• Advanced control: BigQuery exposes HLL++ sketch functions (HLL_COUNT.INIT, HLL_COUNT.MERGE) so you can precompute and combine sketches across partitions.

Window (analytic) functions compute a value across a set of rows related to the current row while keeping every row in the output, whereas aggregate functions collapse groups of rows into a single summary row.

• Key difference: row preservation:

  • GROUP BY aggregation returns one row per group.

  • A window function returns one row per input row, with the computed value alongside it.

• Anatomy of the OVER() clause:

  • PARTITION BY divides rows into groups (like GROUP BY but without collapsing).

  • ORDER BY defines ordering within each partition (needed for ranking and running totals).

  • An optional window frame (ROWS BETWEEN ...) limits which rows contribute.

• Function categories:

  • Ranking: ROW_NUMBER, RANK, DENSE_RANK.

  • Navigation: LAG, LEAD, FIRST_VALUE.

  • Aggregates as windows: SUM, AVG with OVER() for running totals and moving averages.

• Common use cases: running totals, per-group rankings, deduplication, and comparing a row to the previous period.

BigQuery supports SQL UDFs, JavaScript UDFs, and Remote Functions: choose SQL for simple in-SQL logic, JavaScript for procedural logic SQL can't express, and Remote Functions when you need external code or services (via Cloud Functions/Cloud Run).

• SQL UDFs:

  • Body is a single SQL expression; fastest and best-optimized because the engine can inline them.

  • Use for reusable calculations and business logic expressible in SQL.

• JavaScript UDFs:

  • Body is JavaScript; run in a sandbox, so they handle procedural loops, string parsing, or custom math.

  • Slower and more resource-intensive; can load external libraries from GCS via library=.

• Remote Functions:

  • Delegate execution to a Cloud Function or Cloud Run endpoint over a connection resource.

  • Use when you need external APIs, ML inference, or code in languages/libraries not available in-engine.

  • Adds network latency and requires managing the external service and IAM.

• Scope note: UDFs can be temporary (per-query) or persistent (stored in a dataset and shared).

Wildcard tables let a single query scan many similarly-named tables at once by using a * in the table name, which is ideal for querying across tables split by date or shard without writing a giant UNION ALL.

• How they work:

  • FROM `project.dataset.events_*` matches every table whose name starts with events_.

  • The pseudo-column _TABLE_SUFFIX holds the part matched by the wildcard, so you can filter which tables are scanned.

• When they're useful:

  • Date-sharded tables (a table per day, e.g. events_20240101).

  • Querying a range of shards without listing each table.

• Cost tip: Filtering _TABLE_SUFFIX with a constant range prunes tables and reduces bytes scanned.

• Caveats:

  • All matched tables must have compatible schemas.

  • Prefer real partitioned tables when possible: they give better pruning and simpler management than date-sharded wildcards.

To remove all rows, prefer TRUNCATE TABLE: it's a fast, free metadata operation, whereas DELETE is a row-level DML operation that scans and rewrites data and is generally slower and less efficient for wiping a whole table.

• TRUNCATE TABLE:

  • Removes all rows while keeping the schema; treated as a metadata operation, so it does not scan data and incurs no query bytes cost.

  • Much faster for clearing an entire table.

• DELETE:

  • A DML statement (DELETE FROM t WHERE true) that processes rows and counts toward query cost and DML quotas.

  • Right choice when you only need to remove a subset of rows by predicate.

• Time travel implication: Both are recoverable via time travel within the retention window (default 7 days).

• Rule of thumb: clearing everything, use TRUNCATE; conditional removal, use DELETE.

QUALIFY filters rows based on the result of a window function, playing the same role for window functions that HAVING plays for aggregates: it lets you reference an analytic result in the WHERE-style filter without a subquery.

• Why it's needed:

  • You can't filter on a window function in WHERE because window functions are evaluated after WHERE.

  • Without QUALIFY you'd compute the window in a subquery/CTE and filter in an outer query.

• Evaluation order: Runs after WHERE, GROUP BY, and HAVING, so the window functions are already computed when it filters.

• Common use case: Deduplication or top-N-per-group: keep only ROW_NUMBER() = 1 per partition.

• Benefit: cleaner, more readable queries with less nesting.

Scripting lets you run multi-statement programs in a single request using variables, control flow (IF, WHILE, loops), and exception handling, while stored procedures are reusable named routines that encapsulate that scripting logic and accept parameters. You reach for them to orchestrate multi-step SQL logic that would otherwise need an external tool.

• Scripting features:

  • DECLARE / SET for variables, IF/LOOP/WHILE for control flow, and BEGIN...EXCEPTION...END for error handling.

  • Multiple statements run in one job with a shared context.

• Stored procedures: Created with CREATE PROCEDURE, called with CALL, support IN/OUT/INOUT parameters, and can be written in SQL or (for Spark) other languages.

• When to use:

  • Multi-step ELT: stage, transform, MERGE, log, all in one callable unit.

  • Conditional or iterative logic that plain SQL can't express.

  • Encapsulating reusable business logic with parameters.

• When not to: Heavy orchestration with dependencies, retries, and scheduling is often better handled by a dedicated tool (Dataform, Airflow), not procedural SQL.

A CROSS JOIN produces the Cartesian product of two inputs (every row on the left paired with every row on the right), so row counts multiply and can explode into billions of rows, causing huge cost, slow queries, or resource-exceeded errors. The danger is that an unintended one can appear silently from a missing or wrong join condition.

• Why it's dangerous:

  • Output size is left rows × right rows, so 1M × 1M = 1 trillion rows.

  • Downstream aggregations then run over that inflated set, inflating cost and time.

• How accidental ones happen:

  • Comma-separated tables in FROM a, b with no join predicate.

  • A JOIN whose ON condition is always true or matches a non-unique key on both sides (fan-out).

• How to avoid:

  • Always specify explicit ON conditions and confirm join keys are unique on at least one side.

  • Preview row counts and inspect the query plan / bytes estimate before running.

  • When you genuinely need a cross product, write CROSS JOIN explicitly so intent is clear, and keep inputs tiny.

  • For array expansion use CROSS JOIN UNNEST(), which is bounded per-row and safe.

INFORMATION_SCHEMA is a set of read-only, system-defined views that expose metadata about your BigQuery resources: datasets, tables, columns, jobs, reservations, and more. You query it with plain SQL to answer both schema questions and operational questions like cost, performance, and usage.

• Schema / structural metadata:

  • TABLES, COLUMNS, VIEWS, ROUTINES: what objects exist, their columns, types, and definitions.

  • TABLE_STORAGE, PARTITIONS: row counts, bytes, and partition-level detail.

• Operational / job metadata:

  • JOBS (and JOBS_BY_PROJECT/BY_USER/BY_ORGANIZATION): who ran what, bytes processed/billed, duration, errors.

  • Great for cost attribution, finding expensive or slow queries, and spotting failures.

• Governance metadata: OBJECT_PRIVILEGES, SCHEMATA: access grants and dataset inventory for audits.

• Typical questions answered: Which queries burned the most bytes last week? Which tables are largest? What columns exist across a dataset? Are any scheduled jobs failing?

A standard (logical) view stores only a query definition and recomputes it from base tables every time it's read, whereas a materialized view stores precomputed results that BigQuery keeps up to date incrementally. Use a standard view for abstraction and always-fresh logic; use a materialized view to speed up and cheapen repeated aggregations.

• Standard view:

  • Just saved SQL: no storage, always reflects current base data.

  • Every read re-scans the underlying tables, so cost/performance equal running the query each time.

  • Supports arbitrary SQL (joins, window functions, subqueries).

• Materialized view:

  • Physically stores results and refreshes incrementally as base data changes.

  • Cheaper and faster for repeated aggregations; the optimizer can even auto-substitute it into queries that hit the base table.

  • Restricted SQL: primarily aggregations, with limits on joins and non-deterministic functions.

• When to use each:

  • Standard view: simplify/secure access, encapsulate logic, guarantee live data.

  • Materialized view: accelerate hot aggregate queries over large, frequently-read tables.

A materialized view in BigQuery is a precomputed, physically stored result of a query that BigQuery maintains automatically and incrementally, so reads are faster and cheaper than recomputing from scratch. It combines the abstraction of a view with the performance of a cached table, while staying in sync with the base data.

• How it works:

  • Results are stored and refreshed incrementally: only the base-table changes since the last refresh are processed, not the whole dataset.

  • Refresh is automatic by default, and reads can blend stored data with recent base-table deltas to stay current.

• Automatic query rewrite: The optimizer can transparently route queries against the base table to the materialized view when it covers the query, so you get savings without changing SQL.

• Benefits: Lower cost and latency for repeated aggregations over large, frequently-updated tables.

• Limitations:

  • Supported SQL is restricted (focused on aggregations, with constraints on joins and non-deterministic functions).

  • Incurs storage cost and refresh compute; best when read savings outweigh maintenance.

All three encapsulate a query, but they differ in lifespan, persistence, and whether results are stored: a view is a saved query that re-runs each time, while a materialized view precomputes and stores results.

• Temporary view:

  • Defined with WITH (a CTE) or a session-scoped CREATE TEMP VIEW; exists only for the query or session.

  • Not stored in a dataset; nothing to manage or clean up.

• Permanent (logical) view:

  • Created with CREATE VIEW; stored as metadata (the SQL text), not data.

  • The underlying query re-executes on every access, so it costs a full scan each time and reflects live base-table data.

• Materialized view:

  • Created with CREATE MATERIALIZED VIEW; precomputes and physically stores results.

  • BigQuery incrementally refreshes it as base data changes and can auto-rewrite queries to use it, cutting cost and latency.

  • Restricted to certain SQL (aggregations, no outer joins historically); best for repeated, expensive aggregations.

A table-valued function (TVF) is a parameterized function that returns a table, letting you reuse a query with arguments: it is like a view that takes parameters, and unlike a scalar UDF it returns rows and columns instead of a single value.

• What a TVF is:

  • Defined with CREATE TABLE FUNCTION and called in the FROM clause like a table.

  • Accepts typed parameters that get injected into its body.

• vs. views: A view is fixed logic with no arguments; a TVF parameterizes that logic so you push filters in at call time.

• vs. scalar UDFs: A scalar UDF returns one value per row (used in SELECT/WHERE); a TVF returns an entire result set.

• Benefit: Encourages reusable, DRY logic; parameters used in WHERE can enable partition pruning.

BigQuery bills by bytes scanned (on-demand), so the core rule is: read less data and let the engine prune. Optimize by limiting columns and partitions, structuring tables well, and avoiding wasteful patterns.

• Scan less data:

  • Select only needed columns; never SELECT * on wide tables (columnar storage means unused columns cost nothing).

  • Filter on the partition column so BigQuery prunes partitions.

• Design tables for pruning: Partition by date/timestamp; cluster by high-cardinality filter/join keys.

• Query patterns:

  • Filter early, join late; put the largest table first in a join.

  • Avoid self-joins and repeated CTE evaluation; use approximate functions (APPROX_COUNT_DISTINCT) when exactness isn't required.

• Reuse and control cost:

  • Leverage cached results, materialized views, and maximum_bytes_billed limits.

  • Use the dry-run/query validator to estimate bytes before running.

BigQuery is massively parallel, but a global ORDER BY requires producing a single total ordering, which forces data onto one final worker: that serialization defeats parallelism and can exhaust that worker's memory.

• It breaks parallelism: Sorting the whole result set globally can't be split across many workers the way filters and aggregations can; the last stage funnels to one node.

• Memory pressure: A large sort on one worker can spill or throw "resources exceeded" errors.

• Cheaper alternatives:

  • Pair ORDER BY with LIMIT so BigQuery keeps only top-N and avoids sorting everything.

  • Sort only the final small result, not intermediate CTEs or subqueries.

  • Use window functions or ORDER BY within partitions when you don't need a global order.

A query stage is a unit of work in BigQuery's parallel execution DAG: the engine breaks your SQL into stages (read, aggregate, join, sort, write) that run across many slots, passing intermediate results between stages via shuffle. Reading the plan tells you where time and bytes actually went.

• What a stage contains:

  • A set of parallel workers running the same operations (e.g. filter then partial aggregate).

  • Input rows read, output rows produced, and records written to shuffle for the next stage.

• Key timing metrics per stage:

  • Wait, read, compute, and write times: shows whether the stage was slot-starved (wait) or doing heavy compute.

  • Avg vs. max time across workers: a large gap signals data skew.

• How to read it:

  • Find the stage consuming the most time or bytes: that's your bottleneck.

  • High shuffle bytes between stages points to expensive joins or GROUP BY on high-cardinality keys.

  • Repartition steps or spilling to disk indicate too much intermediate data.

• Where to see it: The Execution Details tab in the console, or the plan in INFORMATION_SCHEMA.JOBS.

BigQuery access control is layered on Google Cloud IAM: you grant roles at the organization, project, dataset, or table level, then optionally narrow access further with row-level and column-level security. IAM controls who can do what; the granular features control which rows and columns they see.

• IAM roles (the who and what):

  • Predefined roles like roles/bigquery.dataViewer, dataEditor, jobUser, and admin separate reading data from running jobs.

  • Roles attach to members (users, groups, service accounts) and inherit down the resource hierarchy.

• Resource-level granularity: You can grant access at dataset or even individual table/view level, not just project-wide.

• Column-level security: Uses policy tags from Data Catalog on sensitive columns; only principals with the taxonomy reader role can read those columns.

• Row-level security: Row access policies filter rows per user, e.g. a regional analyst sees only their region.

• Principle of least privilege: Prefer groups over individuals, grant the narrowest role at the lowest scope needed.

Authorized views and authorized datasets let you share query results from a source dataset without granting direct access to the underlying tables: the view (or an entire dataset) is authorized to read the source on the user's behalf, so users query the view but never see the base data.

• Authorized view:

  • A view in one dataset that is granted permission to read tables in another dataset.

  • Users get access to the view only; the view's SQL restricts columns/rows they can see.

  • Classic use: expose a filtered or aggregated slice of sensitive data to a wider audience.

• Authorized dataset:

  • Authorizes an entire dataset at once instead of registering each view individually.

  • Any authorized view in that dataset automatically gains access to the source, reducing admin overhead.

• Why it's secure:

  • Consumers never receive IAM access to the raw tables, so they can't bypass the view.

  • You control exactly what's exposed via the view's definition.

• Related: authorized routines: The same mechanism exists for UDFs/stored procedures that need to read protected source data.

Data Catalog (now part of Dataplex) is Google Cloud's fully managed metadata and discovery service: it automatically catalogs BigQuery assets, lets you search and tag them, and hosts the policy tag taxonomies that drive column-level security, making it central to governance.

• Metadata discovery: Automatically indexes datasets, tables, and views so users can search for data across projects.

• Tagging and business context: Tag templates attach structured metadata (owner, sensitivity, data quality, PII flags) to assets.

• Governance via policy tags:

  • Taxonomies of policy tags defined in the catalog are applied to BigQuery columns to enforce column-level access.

  • Central place to classify sensitive fields consistently across the org.

• Why it matters for governance:

  • Provides a single source of truth for what data exists, who owns it, and how sensitive it is.

  • Supports auditing and compliance by documenting lineage and classification.

BigQuery encrypts all data automatically, with no action required: data at rest is encrypted before being written to disk, and data in transit is encrypted with TLS. You can optionally control the keys used for at-rest encryption.

• Encryption at rest:

  • Every table is encrypted by default using Google-managed keys (AES-256), transparent to users.

  • Data is chunked and each chunk encrypted with its own key, wrapped by key-encryption keys.

• Customer-managed keys (CMEK):

  • You can supply keys from Cloud KMS to control rotation and revocation for compliance.

  • For stricter control, customer-supplied keys and Cloud EKM (external key manager) are options.

• Encryption in transit: All client-to-service and internal service-to-service traffic is encrypted with TLS.

• Complementary protections: Column-level encryption functions (AEAD) let you encrypt specific fields with your own keys inside SQL.

The distinction separates the right to run queries (compute) from the right to read data (storage). roles/bigquery.jobUser lets a principal run jobs and pay for compute in a project, while roles/bigquery.dataViewer lets them read data in a dataset. You typically need both to actually query a table.

• jobUser:

  • Granted at the project level; allows creating/running jobs (queries, loads, exports) but grants no access to any data by itself.

  • Determines which project's slots/billing the job consumes.

• dataViewer: Granted at dataset (or table) level; allows reading data and metadata but not running jobs in the billing project.

• Why the distinction matters:

  • Implements least privilege: separates compute cost/billing from data access.

  • A user with dataViewer but no jobUser cannot run a query; one with jobUser but no data role can run jobs only on data they're separately granted.

  • Enables cross-project patterns: run (and pay for) jobs in project A while reading data shared from project B.

BigQuery has a native Scheduled Queries feature for running SQL on a recurring cadence, and for broader automation you combine it with external schedulers/orchestrators. Scheduled Queries is the simplest built-in option; heavier workflows use Cloud Scheduler, Cloud Composer, or Workflows.

• Scheduled Queries (native):

  • Built on the BigQuery Data Transfer Service; runs a SQL statement on a cron-like schedule.

  • Supports parameters like @run_date and writing results to a destination table (append/truncate).

  • Best for simple periodic transforms (ELT, rollups) with no external dependencies.

• External automation:

  • Cloud Scheduler: cron trigger that calls an API, Pub/Sub, or a function to kick off a job.

  • Cloud Composer (managed Airflow): for multi-step, dependency-aware pipelines.

  • Workflows / Cloud Functions: lightweight, event-driven orchestration.

• Rule of thumb: single recurring query, use Scheduled Queries; multi-step DAG with dependencies and retries, use Composer.

Time travel lets you query and restore BigQuery table data as it existed at any point in a recent window (default 7 days, configurable 2 to 7). It provides quick recovery from accidental deletes, bad updates, or dropped tables without needing a separate backup.

• How it works:

  • BigQuery retains historical versions of table data for the time-travel window automatically.

  • Query a past state with FOR SYSTEM_TIME AS OF using a timestamp within the window.

• Recovery use cases:

  • Undo a bad UPDATE/DELETE by reading the pre-change snapshot and rewriting the table.

  • Recover a dropped table (if within window) by querying/copying its prior state; a dropped table can be restored while the window is active.

• Limits:

  • Only covers the configured window (max 7 days): for longer retention use table snapshots or scheduled exports.

  • After the window, data ages into fail-safe (Google-managed, not user-queryable) then is purged.

BI Engine is an in-memory analysis service inside BigQuery that caches frequently accessed data in a fast, columnar memory layer so interactive dashboards return sub-second results without re-scanning storage each time.

• In-memory acceleration:

  • Reserved RAM caches hot columns/partitions, so repeated queries hit memory instead of columnar storage.

  • Also adds vectorized processing optimizations for supported query shapes.

• Transparent to the client: No new API or syntax: queries from Looker, Looker Studio, or any BI tool automatically benefit when the tables fit in the reservation.

• You buy a memory capacity: You reserve BI Engine memory (GB) per project/location; BigQuery manages what stays cached.

• Best for dashboards/reporting:

  • High-concurrency, repeated, aggregation-style queries over the same data see the biggest gains.

  • Caveat: partial support (some SQL features or very large scans) may fall back to normal slot execution.

A slot is BigQuery's unit of compute (a virtual CPU/worker) used to execute queries; a slot reservation is a named allocation of purchased slot capacity assigned to specific workloads so they get guaranteed compute.

• Slot = compute unit:

  • BigQuery breaks a query into stages and executes them across many slots in parallel.

  • In on-demand mode you don't manage slots directly: you pay per bytes scanned with a fair-share pool.

• Reservation = allocated capacity:

  • With capacity-based pricing you buy slots, then carve them into reservations (e.g. etl, bi) to isolate and budget workloads.

  • Projects are attached to a reservation via an assignment, controlling which slots each workload uses.

• Why it matters: Reservations give predictable cost and prevent one team's queries from starving another's.

A table snapshot is a read-only, point-in-time copy of a table; a table clone is a writable, independent copy. Both start as lightweight metadata pointers and only store bytes that differ from the source, so they're cheap to create.

• Snapshot:

  • Read-only, preserves table state at a moment (or an expiration-bounded window).

  • Use for backups, audits, and recovery points; you pay only for storage that diverges from the base.

• Clone:

  • Fully writable copy; you can modify it without affecting the source.

  • Use for dev/test, experimentation, or what-if analysis on production data.

  • Storage billing: only the delta you write is charged beyond the shared base.

• Choosing: Need an immutable restore point, use a snapshot; need to edit a copy, use a clone.

Both protect against data loss, but time travel is user-accessible recovery of recent table states, while fail-safe is an additional Google-managed recovery window you cannot query directly.

• Time travel:

  • Access historical data up to 7 days back (configurable 2-7 days).

  • You query it yourself with FOR SYSTEM_TIME AS OF to read or restore deleted/changed rows and tables.

• Fail-safe:

  • An extra ~7 days after time travel expires where data is still retained.

  • Not self-service: only Google Cloud support can recover from it, as a last resort.

• Key distinction: Time travel = you control it; fail-safe = safety net beyond your reach.

Analytics Hub is a data-sharing service that lets a publisher expose datasets as listings inside an exchange, which subscribers can add to their own projects as live, read-only linked datasets, without copying or moving the data.

• Core objects:

  • Exchange: a container that groups listings for a set of audiences.

  • Listing: a published, shareable reference to a dataset (or view).

  • Linked dataset: what the subscriber gets, a read-only pointer that queries the source in place.

• No-copy sharing:

  • Subscribers always see the latest data since it reads the publisher's storage directly.

  • Subscribers pay for their own query compute; the publisher pays storage.

• Cross-organization use: Enables secure data marketplaces and monetization across companies with governed access controls.

A cross-region join fails because BigQuery requires all datasets in a query to live in the same location: it does not automatically move data across regions, so a query referencing a US dataset and an EU dataset errors out. You fix it by first copying one dataset into the other's region, then joining locally.

• Why it fails:

  • Query processing is bound to a single region for data residency, compliance, and latency reasons.

  • All tables in a single job must share the same location (e.g. all in US or all in europe-west1).

• How to move data across regions:

  1. Use the cross-region dataset copy feature (Data Transfer Service or bq cp) to replicate a dataset into the target region.

  2. Alternatively, export to Cloud Storage in one region, copy the bucket, then load into a dataset in the other region.

  3. Once both tables are co-located, run the join normally.

• Trade-offs:

  • Copying incurs egress cost and storage duplication, and adds sync latency (the copy can go stale).

  • Prefer designing datasets in a common region up front when they will be joined regularly.

BigQuery ML lets you create, train, and serve machine learning models directly in BigQuery using standard SQL, so data never leaves the warehouse and analysts don't need a separate ML stack. You define a model with CREATE MODEL and specify a model type in the options.

• Supervised / regression & classification: Linear and logistic regression, boosted trees (XGBoost), random forest, and deep neural networks (DNN).

• Unsupervised:

  • K-means clustering and matrix factorization (recommenders).

  • PCA and autoencoders for dimensionality reduction / anomaly detection.

• Time series: ARIMA_PLUS for forecasting with automatic seasonality and holiday handling.

• Imported & remote models: Import TensorFlow/ONNX models, or call remote Vertex AI endpoints and hosted LLMs via CREATE MODEL ... REMOTE.

These are the SQL functions that operate on a trained model across its lifecycle: ML.EVALUATE measures quality, ML.PREDICT scores new rows, and ML.FORECAST produces future values for time-series models.

• ML.EVALUATE:

  • Returns quality metrics (accuracy, precision/recall, AUC, RMSE, etc.) on eval or holdout data.

  • Used after training to validate before deployment.

• ML.PREDICT:

  • Applies a trained supervised or clustering model to new input rows and returns predictions.

  • Automatically applies any TRANSFORM preprocessing baked into the model.

• ML.FORECAST:

  • Specific to time-series models (ARIMA_PLUS); returns forecasted points plus prediction intervals over a horizon.

  • Use it instead of ML.PREDICT for forecasting models.

• Lifecycle fit: Train with CREATE MODEL, validate with ML.EVALUATE, then serve with ML.PREDICT or ML.FORECAST.

A managed OLAP service like BigQuery trades some raw tuning control and lock-in risk for near-zero administration and elastic concurrency, while a self-managed columnar cluster (e.g. Redshift-managed, ClickHouse, self-hosted) gives more predictable dedicated performance and portability at the cost of heavy operational work. For 20 analysts with occasional heavy ad-hoc queries, BigQuery is usually the better fit.

• Performance predictability: Self-managed clusters give consistent latency on fixed hardware; serverless can vary with a shared pool but can be made predictable with reserved slots.

• TCO: Self-managed pays for always-on capacity even when idle; BigQuery's pay-per-query (or right-sized reservations) is cheaper for bursty, intermittent workloads.

• Administrative overhead: Managed = almost none; self-managed requires sizing, patching, scaling, vacuum/analyze, and backups.

• Query concurrency: BigQuery scales concurrency elastically; fixed clusters contend for finite nodes and often need workload management queues.

• Vendor lock-in: Managed services couple you to a provider's SQL dialect and APIs; open-source columnar engines are more portable.

• Recommendation: For 20 analysts with occasional heavy ad-hoc queries, BigQuery: no idle cost, elastic concurrency for spikes, and minimal admin let the team focus on analysis instead of infrastructure.

BigQuery generally scales more transparently because it's serverless and decouples storage from compute, so you don't manage or resize clusters. Redshift has closed much of the gap with RA3 nodes and serverless options, but its scaling is more tied to explicit capacity decisions.

• BigQuery: automatic, elastic: Slots are allocated per query from a large shared pool; you don't provision nodes, and storage grows independently of compute.

• Redshift: node/cluster oriented: Classic Redshift ties compute and storage to a cluster you size and resize; RA3 separates storage, and Redshift Serverless adds elasticity but still within provider-managed capacity units.

• Concurrency scaling: BigQuery handles bursts by pulling more slots; Redshift uses concurrency scaling clusters that can add cost and configuration.

• Honest caveat: "Scales better" depends on the workload: Redshift can give more predictable, tunable performance on steady loads, while BigQuery shines for spiky, unpredictable analytical demand with zero ops.

You model nested data with STRUCT (a record with named fields) and repeated data with ARRAY (a list), often combining them as an array of structs; you UNNEST when you need to flatten one-to-many data for querying, and you normalize into separate tables only when the child data is large, updated independently, or shared across parents.

• Nesting with STRUCT: Groups related columns (e.g. address.city, address.zip) without a join.

• Repetition with ARRAY: Stores one-to-many (e.g. a customer's orders) inline in one row, preserving locality.

• When to UNNEST vs keep nested: UNNEST at query time to filter/aggregate over the child items; keep nested at rest to avoid repeated joins.

• When to normalize into separate tables: Child records are very large, change frequently on their own, or are referenced by many parents (avoid rewriting whole rows).

• Rule of thumb: Prefer nested/repeated for tightly-coupled, read-mostly hierarchies; normalize when independence or reuse matters.

An ARRAY is an ordered list of values of the same type, and a STRUCT is a record grouping named, possibly differently-typed fields; combining them (an array of structs) lets you embed one-to-many relationships inside a single row. This denormalization is idiomatic because BigQuery's columnar engine reads it efficiently and it eliminates costly joins.

• ARRAY: repeated field: Holds multiple values in one column (e.g. tags, line items).

• STRUCT: nested record: Bundles related sub-fields accessed by dot notation (e.g. event.user.id).

• Why denormalizing is performant:

  • Avoids joins: related data is co-located in the same row, so no shuffle across tables.

  • Columnar storage only reads the nested fields you touch, so nesting adds little cost.

  • Preserves one-to-many structure without duplicating parent columns across many flat rows.

• Trade-off: Updating individual nested items is harder; best for read-mostly analytical data.

Design for BigQuery's columnar, scan-based, pay-per-byte engine: denormalize with nested/repeated fields, choose partitioning and clustering to prune data, and pick the right types. The goal is to minimize bytes scanned while keeping queries simple.

• Denormalize instead of over-normalizing: Use STRUCT and ARRAY to embed one-to-many relationships and avoid expensive joins.

• Partition large tables: Partition by a date/timestamp or integer range so queries scan only relevant partitions; combine with require_partition_filter to enforce pruning.

• Cluster on high-cardinality filter/join columns: Clustering sorts data so filters and aggregations on those columns read less; order columns from most to least filtered.

• Choose efficient, precise types: Use NUMERIC for money, avoid oversized strings, and only select needed columns (never SELECT *) since storage is columnar.

• Model for query patterns: Shape tables around how they'll be queried; consider materialized views or pre-aggregations for hot dashboards.

A lakehouse combines the cheap, open-format storage of a data lake with the governance, performance, and SQL analytics of a data warehouse. For BigQuery, BigLake tables deliver this by letting the BigQuery engine query and govern data sitting in object storage (like open formats on GCS) as if it were a native table.

• Lakehouse idea: One layer serving both lake (raw, open files) and warehouse (structured, governed analytics) instead of copying data between silos.

• How BigLake advances it:

  • Queries data in GCS in open formats (Parquet, ORC, Avro) and table formats like Iceberg without loading it into BigQuery storage.

  • Applies fine-grained security (row/column-level access) uniformly, even for engines outside BigQuery via connectors.

  • Uses metadata caching and the BigQuery engine to give near-native performance on external data.

• Net effect: Unified governance and analytics across lake and warehouse data, avoiding data duplication and lock-in.

BigQuery stores data in Colossus and runs queries in the Dremel compute engine, with the Jupiter network linking them; because storage and compute are decoupled, you scale (and pay for) each independently instead of resizing a fixed cluster.

• Independent scaling: Store petabytes cheaply while spinning up thousands of slots only for the seconds a query runs.

• Elasticity: Compute is allocated per query, so a heavy query gets more workers without pre-provisioning hardware.

• Cost efficiency: Storage is billed by bytes stored, compute by bytes scanned or reserved slots; idle storage never pays for idle compute.

• Concurrency and isolation: Many workloads read the same stored data simultaneously without contending for a shared cluster.

• Enabler: This only works because Jupiter's huge bandwidth makes reading remote storage nearly as fast as local disk.

These are the three core pillars: Colossus is the storage system, Dremel is the query execution engine, and Jupiter is the network that connects compute to storage.

• Colossus (storage): Google's distributed file system storing table data in columnar Capacitor files, handling replication, durability, and compression.

• Dremel (compute):

  • Turns SQL into an execution tree: a root/mixer distributes the query to leaf workers (slots) that scan Colossus in parallel.

  • Intermediate results flow up through a shuffle tier for joins and aggregations.

• Jupiter (network): Petabit-scale bandwidth lets thousands of Dremel workers read remote Colossus data fast enough to keep compute and storage separate.

• How they work together: A query enters Dremel, which reads column data from Colossus over Jupiter, distributes work across slots, and merges results up the tree.

A columnar database stores each column's values together on disk rather than storing whole rows contiguously. BigQuery uses its Capacitor columnar format so analytical queries read only the columns they need and compress data aggressively.

• Row vs columnar layout:

  • Row stores (OLTP) keep a full record together, great for reading/writing single rows.

  • Columnar stores keep a whole column together, great for scanning few columns across billions of rows (OLAP).

• Why it speeds up analytics:

  • A query like SELECT SUM(revenue) reads only the revenue column, skipping all others, which drastically cuts bytes scanned (and cost).

  • Data in one column is homogeneous, so encoding/compression (dictionary, run-length) is far more effective.

• What Capacitor adds:

  • BigQuery's proprietary format that heavily compresses and reorders/encodes column values.

  • Stores statistics so the engine can prune and read column segments efficiently.

• Trade-off: columnar is poor for single-row point lookups and frequent updates, which is why BigQuery is built for analytical scans, not OLTP.

When you submit a query, BigQuery parses and plans it, then the Dremel engine breaks it into a tree of stages executed in parallel by many worker units (slots) that read columnar data from Colossus over Google's Jupiter network, shuffle intermediate results, and return the final output.

• End-to-end flow:

  1. The query is parsed, validated, and the optimizer builds an execution plan (a DAG of stages).

  2. The Dremel engine dispatches stages to available slots (units of CPU/RAM).

  3. Leaf workers read only the needed columns from Capacitor files in Colossus.

  4. Intermediate results are exchanged via an in-memory shuffle over the Jupiter network.

  5. Aggregation/final stage assembles results, cached and returned.

• Factors affecting speed:

  • Bytes scanned: selecting fewer columns and filtering on partition/cluster keys reads less data.

  • Slot availability: more concurrent slots means more parallelism; contention slows queries.

  • Data skew and heavy shuffles/joins: uneven key distribution stalls stages.

  • Partitioning and clustering: enable pruning so the engine skips irrelevant blocks.

  • Cache: exact repeat queries can return from the results cache instantly.

Colossus is Google's distributed file system that serves as BigQuery's persistence layer: it physically stores all table data and guarantees durability through replication and erasure coding across many machines and failure domains.

• What it is:

  • The successor to Google File System (GFS); manages data as chunks distributed across the cluster.

  • Handles metadata, replication, and healing without user involvement.

• How durability is ensured:

  • Data is replicated and/or erasure-coded so it survives disk and machine failures.

  • Copies are spread across zones/failure domains for resilience.

  • Corrupt or lost chunks are automatically re-created from redundancy.

• Why it matters for BigQuery:

  • Storage persists independently of compute, so slots can fail or scale without affecting stored data.

  • Enables high parallel read throughput since data is spread across many disks.

BigQuery is a serverless, distributed analytics engine that fundamentally decouples storage from compute: data lives in the columnar Capacitor format on Colossus, while the Dremel engine runs queries on ephemeral compute units called slots, connected by the Jupiter network. Traditional databases instead bundle storage and compute on the same fixed servers.

• Columnar storage (Capacitor on Colossus):

  • Data stored column-by-column, compressed, so queries scan only needed columns.

  • Persisted durably and replicated in Colossus, independent of any compute node.

• Slot-based compute (Dremel):

  • A slot is a virtual unit of CPU/RAM; queries are split into a tree of parallel stages across many slots.

  • Slots are allocated dynamically (on-demand or reservations), so compute scales elastically.

  • Intermediate data moves through an in-memory shuffle over the Jupiter network.

• How it differs from traditional databases:

  • Storage and compute scale independently instead of being tied to one server's disk and CPU.

  • Serverless: no provisioning, indexing, or vacuuming; you just run SQL.

  • Optimized for large analytical scans (OLAP), not single-row transactions (OLTP) with indexes and row locks.

  • Fault tolerance is built in: failed slots just retry, and data stays safe in Colossus.