TopolearnKnowing the internals explains BigQuery's cost and performance rules and helps you recover from mistakes.
Under the hood, BigQuery separates every layer:
- Storage: tables are stored in Capacitor, a columnar format that encodes and compresses each column and keeps per-block statistics, on Colossus, Google's distributed file system, replicated across zones. Storage is immutable files plus metadata, so DML writes new files and the metadata swaps them in.
- Compute: the Dremel engine executes query stages on slots scheduled by Borg.
- Shuffle: intermediate data between stages goes to a distributed in-memory shuffle tier over Google's Jupiter network, which is why large joins and aggregations scale.
- Streaming: the Storage Write API writes to a write-optimized store that's later converted to columnar storage in the background.
Immutability makes recovery features cheap.
- Time travel: query any table as of a past moment within the dataset's window, 7 days by default and configurable down to 2:
FROM t FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR). Deleted tables can be restored within the window too, for example withbq cpand a snapshot decorator. - Fail-safe: a further 7 days, recoverable only through Google support.
- Table snapshots (
CREATE SNAPSHOT TABLE) are read-only, point-in-time copies that store only the bytes that differ from the base table. - Table clones (
CREATE TABLE ... CLONE) are writable, cheap copies that also bill only for their differences.
With physical storage billing, time-travel and fail-safe bytes count toward storage, so tables with heavy churn can have a large hidden footprint.
Example
-- What did the table look like an hour ago?
SELECT COUNT(*)
FROM shop.orders FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
-- Restore after a bad DELETE: rebuild from the earlier version
CREATE OR REPLACE TABLE shop.orders_restored AS
SELECT * FROM shop.orders
FOR SYSTEM_TIME AS OF TIMESTAMP '2025-06-01 09:00:00+00';
-- Cheap, read-only point-in-time snapshot (kept 30 days)
CREATE SNAPSHOT TABLE backups.orders_20250601
CLONE shop.orders
OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 30 DAY));
-- Writable clone for testing a migration
CREATE TABLE sandbox.orders_test CLONE shop.orders;
-- Shorten the time-travel window of a high-churn staging dataset
ALTER SCHEMA staging SET OPTIONS (max_time_travel_hours = 48);Remember
- Capacitor columnar files on Colossus; Dremel runs stages; shuffle is in memory
- FOR SYSTEM_TIME AS OF reads past versions within the time-travel window
- Snapshots (read-only) and clones (writable) store only differences
Watch out for
- Assuming time travel lasts indefinitely; after the window and fail-safe the data is gone.