Storing the data

Choosing a storage system

Fast triage:

• “Petabyte, SQL, analysts” = BigQuery. “Millions of writes per second, lookup by key, time series” = Bigtable. “Strong consistency across regions, relational” = Spanner. “Existing MySQL/PostgreSQL, small to medium” = Cloud SQL. “Postgres, heavy workload plus reports” = AlloyDB. “Cheap files and archives” = Cloud Storage. “Cache in front of a database” = Memorystore. “Documents for an app” = Firestore.
• Data access patterns (read/write ratio, scan vs point lookup, latency, concurrency, update frequency, retention) decide the answer, so name the pattern before the product.

Cloud Storage details

• Classes: Standard (hot), Nearline (min 30 days), Coldline (min 90 days), Archive (min 365 days). All have the same latency and durability; difference is price and retrieval fees. Autoclass moves objects automatically based on access; lifecycle rules (age, version count, created before, storage class) transition or delete.
• Locations: regional, dual-region (with optional turbo replication, 15 minute RPO), multi-region. Pick by latency, availability and residency.
• Protection: object versioning, soft delete (default 7 days), retention policy plus Bucket Lock (irreversible WORM for compliance), holds, uniform bucket-level access (IAM only, recommended), signed URLs, CMEK, Hierarchical namespace buckets for folder-level operations and better analytics performance, Storage Insights inventory.
• Performance: object names with sequential prefixes no longer hotspot as they used to; use parallel composite uploads for large files; co-locate with compute. Requester Pays shifts cost to the reader.

Bigtable details

• Row key is everything. Rows sorted lexicographically by key. Design to spread load and support the main query as a range scan.
  • Avoid hotspotting: monotonically increasing keys (timestamps, sequence ids) concentrate writes on one tablet. Fixes: field promotion (put a high-cardinality field first, e.g. deviceId#timestamp), salting (a hash prefix), reversing domain names, and reverse timestamps to get newest first.
  • Don’t start keys with a low-cardinality or sequential value. Keep keys short (under 4 KB, shorter is better). Don’t use hashing if you need range scans over the original value.
• Column families group related columns, with a garbage collection policy (max age, max versions). A cell holds timestamped versions. Single row up to 256 MB, cell up to 100 MB; keep rows far smaller.
• Scaling: add nodes for throughput; autoscaling by CPU target; keep CPU under about 70% on a single cluster (35 to 40% when you rely on a cluster as failover). SSD for latency; HDD only for huge, rarely read data. Data sits in Colossus, so nodes can be changed without moving data.
• Replication: add clusters in other zones or regions; app profiles choose single-cluster routing (strong consistency, manual failover) or multi-cluster routing (high availability, eventual consistency). Use separate app profiles to isolate batch from serving traffic.
• Key Visualizer finds hotspots; Cloud Monitoring shows CPU and storage. Time series and many small wide rows suit it; BigQuery can query Bigtable through external tables and Data Boost for analytics without hurting serving. Bigtable also supports SQL (GoogleSQL for Bigtable) and continuous materialized views in newer releases.
• Not for: transactions across rows, ad hoc joins, tiny datasets (minimum cost applies), analytics scans.

Spanner details

• Relational, SQL, external consistency (TrueTime), automatic sharding. Instance configurations: regional (99.99%), dual-region, multi-region (99.999%). Capacity in processing units or nodes with autoscaler; editions (Standard, Enterprise, Enterprise Plus) differ by features.
• Primary key design: avoid monotonically increasing keys; use UUIDv4, bit-reversed sequences, or hash prefixes. Interleaved tables (parent-child co-location) speed joins; secondary indexes, foreign keys, change streams (CDC to Dataflow/BigQuery), read-only and stale reads for cheaper replicas, Spanner Graph and vector search, and Data Boost for analytics. Query data from BigQuery through federated queries (external connection) and from Dataflow.
• Choose Spanner when scale or availability exceeds a single-primary relational database, or global writes need consistency.

Relational and cache details

• Cloud SQL: HA configuration (regional, synchronous standby, automatic failover, same IP), read replicas (async; offload reads; cross-region for DR; promoted manually), automatic backups, point-in-time recovery (needs binary logging or WAL), maintenance windows, private IP, Enterprise vs Enterprise Plus editions (99.99%, near-zero-downtime maintenance). Scale up = vertical; scale reads = replicas; need horizontal write scale = Spanner.
• AlloyDB: PostgreSQL-compatible, disaggregated storage, read pool instances, columnar engine for analytics, cross-region replication, vector and ScaNN index for AI search. Pick for Postgres that outgrows Cloud SQL or mixes OLTP and analytics.
• Memorystore: Redis (Basic: no replication; Standard: replica with failover; Redis Cluster: sharded for scale and 99.99%), Valkey, Memcached (multi-node cache, no persistence or replication). Cache-aside pattern; set TTLs; can use RDB persistence on Redis.
• Firestore: Native mode (real-time sync, multi-region) vs Datastore mode (server-only, legacy Datastore apps). Indexes automatic for single fields; composite indexes needed for combined filters.

Cost, performance and lifecycle

• Storage cost levers: Cloud Storage classes plus lifecycle; BigQuery long-term storage (a table or partition unmodified for 90 days costs about half); physical storage billing (compressed bytes, often cheaper for highly compressible data; includes time travel storage); table and partition expiration; delete unused snapshots.
• Performance levers: right key design, SSD vs HDD, partition and cluster, caching and materialized views, co-locating compute and data in one region.
• Lifecycle: raw (keep for replay) → curated → archived; apply retention rules (legal and cost), versioning and deletion; set default table expiration on sandbox datasets.

Planning for a data warehouse

BigQuery architecture facts that drive design

• Dremel-based execution, Capacitor columnar storage on Colossus, Jupiter network, separation of storage and compute. Slots are units of compute.
• On-demand pricing charges per TiB scanned (columns read, so SELECT * is costly; LIMIT does not reduce bytes billed on a non-clustered table); capacity pricing (editions) charges for slots.
• Queries are cached for 24 hours per user for identical queries when the data hasn’t changed (no charge, no slots).

Data modelling

• Star schema (fact plus dimensions) still works and BI tools expect it; denormalise to reduce joins. BigQuery prefers wide denormalised tables, and uses nested and repeated fields (STRUCT, ARRAY) to keep one-to-many relations in one row without joins (orders with line items). Don’t denormalise when dimension values change often and need history, or when one parent has enormous child arrays.
• Degree of normalisation: OLTP sources are normalised (3NF); the warehouse layer is denormalised for reads; keep small, slowly changing dimensions separate and join (broadcast joins are cheap). Choose by update pattern, query pattern and cost.
• Slowly changing dimensions: type 1 overwrite, type 2 add row with valid_from/valid_to/is_current, type 3 add column. Implement with MERGE or Dataform incremental tables.
• Layers (medallion): bronze/raw, silver/cleansed, gold/curated marts; or staging, core, marts.
• Constraints: primary and foreign keys can be declared but are not enforced; the optimizer can use them (with NOT ENFORCED) for join elimination.
• Choose types carefully (DATE/TIMESTAMP, NUMERIC/BIGNUMERIC, JSON for semi-structured, GEOGRAPHY for spatial).

Partitioning, clustering and table design

• Storage vs compute trade-offs: partitioning is free to use but adds metadata limits; clustering costs re-clustering in the background; materialised views cost storage and refresh.
• Business requirements to architecture: data freshness (streaming vs batch), concurrency (reservations, BI Engine), data volume growth (partitioning), sharing (Analytics Hub), regionality (dataset location), and cost (editions, storage billing model).
• Access patterns: dashboards with repeated queries = materialised views, BI Engine and result cache; ad hoc exploration = on-demand or autoscaling slots; heavy ETL = dedicated baseline slots with batch priority.

Using a data lake

• A data lake holds raw and semi-structured data in Cloud Storage in open formats (Parquet, Avro, ORC, Iceberg), organised by zone (raw, curated) and partitioned paths (dt=2026-11-09/). Open formats and columnar compression make it queryable by many engines.
• BigLake tables and external tables let BigQuery query lake files; BigLake adds row- and column-level security and masking on lake files, metadata caching for speed, and managed Iceberg tables (BigQuery-managed storage in Cloud Storage with Iceberg metadata, DML and streaming support). Object tables expose unstructured files (images, PDFs) to SQL and models.
• Dataplex Universal Catalog manages the lake:
  • Lakes, zones (raw and curated) and assets group Cloud Storage buckets and BigQuery datasets; automatic discovery scans files, infers schemas and registers tables (BigQuery external/BigLake tables and Dataproc Metastore entries).
  • Data quality scans, data profile scans, lineage (BigQuery, Dataflow, Composer, Dataproc, Spark show column-level flow), business glossary, aspect types and entry groups for custom metadata, and data products with documentation and contracts.
  • Security: IAM at lake, zone, or asset; Dataplex propagates policies.
• Processing the lake: Dataproc/Serverless Spark, Dataflow, BigQuery SQL; ephemeral compute over durable storage; scheduled Dataplex tasks (Spark, notebooks).
• Controls: cost (lifecycle to colder classes, partitioned layout, avoid many small files; compact them, Requester Pays, budgets and alerts, quotas), access (IAM, uniform access, VPC-SC, policy tags), monitoring (Cloud Monitoring on bucket metrics, audit logs, Storage Insights, Dataplex quality scores, lineage).
• Lake vs warehouse: load into BigQuery when you need performance, concurrency and managed governance; leave in the lake for cheap, multi-engine, infrequently queried data; the lakehouse (BigLake plus Iceberg) combines both.

Designing a data platform and federated governance

• Build the platform from: Cloud Storage (landing), BigQuery (warehouse and marts), Dataplex (catalog, quality, lineage, policies), Analytics Hub (sharing), IAM and policy tags (access), Composer/Dataform (orchestration), Looker and BI (consumption).
• Data mesh / federated governance: each business domain owns its data products (its own projects, datasets, pipelines), exposes them with contracts and metadata, while a central team sets global policies (taxonomy of policy tags, naming, quality standards, org policies, security baseline). In Dataplex, domains map to lakes or projects, and the central team defines glossary terms, aspect types and tag templates. Consumers discover data in the catalog and request access.
• Principles: decouple ownership from platform, publish discoverable metadata, apply policy tags consistently, automate access reviews, audit through Cloud Audit Logs, and measure data quality as a product metric.
• Governance tools: policy tags (column security), row access policies, authorized views, data masking, DLP discovery, IAM Conditions (time-bound or resource-tag based access), tags and org policy, Access Context Manager and VPC-SC.