GCP Database Engineer cheat sheet

Cloud SQL high availability uses a regional persistent-disk pair with a standby in a second zone; failover is automatic but the standby cannot serve reads.

Cloud SQL read replicas are asynchronous by default; enterprise/enterprise plus editions support cascading replicas, and replica lag monitoring matters for read-scaling designs.

Cloud SQL Enterprise Plus adds near-zero-downtime maintenance and data cache (local SSD) for higher throughput than Enterprise.

Spanner achieves external consistency via TrueTime and synchronous replication; regional config tolerates a zone failure, multi-region configs tolerate a region failure with 99.999% SLA.

Spanner read-write transactions default to strong; stale reads at a timestamp can be served from a nearby replica to cut latency.

Firestore/Datastore is multi-region by default for strong consistency and 99.999% availability; choose regional for lower cost and latency.

Bigtable scales linearly by adding nodes; replication is per-cluster and eventually consistent across clusters, with single-cluster routing for strong reads.

AlloyDB separates compute from storage; the primary plus read pool replicas share a regional log-processing storage layer, giving fast failover and read scale.

Memorystore for Redis uses a standard tier with automatic failover across zones; basic tier has no replica and no SLA for failover.

Use Datastream for CDC from Cloud SQL, AlloyDB, or Spanner into BigQuery or Cloud Storage; Datastream does not write back into OLTP systems.

Federated queries in BigQuery (EXTERNAL_QUERY) read Cloud SQL or Spanner live without copying; they are read-only and latency-bound by the source.

BigQuery Omni runs analytics on data in AWS S3 or Azure Blob without moving data; results can be joined with BigQuery-native tables.

Use Cloud SQL or Spanner as the system of record and BigQuery as the analytical layer; avoid dual writes by making the OLTP store authoritative and streaming changes out.

Spanner-to-BigQuery change streams require a change stream read with the Data Change Records API, typically via Dataflow, not a native BigQuery sink.

Database Migration Service supports homogeneous and heterogeneous migrations into Cloud SQL, AlloyDB, and Spanner; heterogeneous Oracle/SQL Server paths use SCT for schema conversion.

Use VPC Service Controls perimeters to keep multi-database data flows inside a boundary; Private Service Connect gives private endpoints to managed databases.

Cross-database consistency is not transactional; design idempotent consumers and use outbox or change-stream patterns to reconcile state.

Cloud SQL and AlloyDB export to Cloud Storage as SQL dumps or CSV; BigQuery can query GCS external tables directly before loading.

Database Migration Service continuous migration uses a dump plus CDC replication; the promotion step makes the target primary and should be a planned cutover.

Homogeneous migrations keep the engine (MySQL to Cloud SQL MySQL, PostgreSQL to Cloud SQL or AlloyDB); heterogeneous migrations change engine and require schema and code conversion.

Use the Google Cloud Database Migration Program tools: ora2pg-style conversion for Oracle to PostgreSQL, and the SQL Server to PostgreSQL conversion workflow.

For large offline transfers, gsutil or Storage Transfer Service into Cloud Storage, then load into BigQuery; BigQuery load jobs are free but storage and queries are billed.

Validate migrations with row counts, checksums, and a dual-run comparison window before cutting over; DMS reports replication lag and errors.

Spanner migrations from MySQL/PostgreSQL use HarbourBridge for schema and data; HarbourBridge does not migrate stored procedures or triggers.

BigQuery Data Transfer Service schedules recurring loads from SaaS sources and Cloud Storage; it is not a one-time migration tool for OLTP databases.

Terraform or the gcloud CLI can script database creation; schema migration tools (Flyway, Liquibase) manage DDL versioning after cutover.

Cloud SQL instances are created with gcloud sql instances create; set --availability-type=REGIONAL for HA and --database-version for the engine.

Cloud SQL storage auto-increases when free space runs low; enable it with --storage-auto-increase and set --storage-auto-increase-limit.

Spanner instances are provisioned in processing units (1 node = 1000 PU); regional instances need at least 1 node, multi-region need at least 3 nodes or equivalent PUs.

Bigtable instances contain clusters; each cluster lives in one zone, and app profiles route traffic; multi-cluster routing needs at least two clusters.

AlloyDB clusters require a primary instance; read pools are added separately and can be scaled with the number of nodes.

Memorystore Redis standard tier is created with --tier=STANDARD and --redis-version; it provisions a replica in another zone automatically.

Use Private IP (--network) plus Private Service Connect or private services access for databases that must not have public endpoints.

Backups: Cloud SQL automated backups with point-in-time recovery via binary/transaction logs; Spanner backup schedules and BigQuery table snapshots/ clones.

Deploy with Terraform google_sql_database_instance, google_spanner_instance, and google_bigtable_instance resources for repeatable infrastructure.