Storing the Data
BigQuery physical design
Partition large fact tables by date or ingestion time and cluster on the columns most used in filters and joins - clustering helps only when the filter order matches the cluster column order, and a table is limited to 4,000 partitions. Require partition filters on big tables so a stray query cannot scan years of data. Choose the cheapest storage for the access pattern: Cloud Storage classes (Standard, Nearline, Coldline, Archive) have 30/90/365-day minimum durations, so early deletion incurs charges. Lifecycle rules and Autoclass automate tiering; bucket names are globally unique and objects are immutable, so a rename is a copy plus delete.
Choosing a database
Bigtable for high-throughput single-key reads and time series; design row keys to avoid hotspots (no monotonically increasing keys, use salting or field promotion). Cloud SQL for regional relational workloads up to a few TB; Spanner for horizontal scale, strong consistency and multi-region. Firestore for mobile/web app documents. BigLake unifies governance across GCS and BigQuery.
Security and governance
Encrypt with CMEK where required, restrict data exfiltration with VPC Service Controls, and manage access at dataset, table, view, row and column granularity. Set retention windows on tables and datasets deliberately.
Traps
- Bigtable does not do multi-row joins or aggregations; it is a serving store.
- BigQuery does not enforce primary keys; deduplication is your job.
- Clustering on a column never filtered on provides no benefit.
Rule of thumb
Query pattern first, storage engine second: if you cannot state the top three filters, you cannot choose the partition and cluster keys.