Choosing Storage and Databases: Cloud SQL, BigQuery, Spanner, and More

Ask three questions in order and the answer usually falls out. First, what shape is the data — relational rows with joins and transactions, JSON-like documents, wide-column key-value at huge scale, or analytical tables you scan in aggregate? Second, what scale and where — one region or global, gigabytes or petabytes? Third, what consistency and latency — strict transactions, or eventual freshness with sub-millisecond reads? Relational plus regional scale means Cloud SQL (managed MySQL, PostgreSQL, and SQL Server) or, for demanding PostgreSQL performance, AlloyDB. Relational plus horizontal, global scale with strong consistency means Spanner — it is the only option that combines all three. Flexible documents with mobile and web sync means Firestore. Massive-throughput, low-latency wide-column data — time series, IoT, personalization — means Bigtable. Scanning terabytes for analytics with SQL means BigQuery, which is a serverless data warehouse, not an operational database.

Scenario: a global ticketing platform needs strongly consistent seat inventory across three continents — Spanner, because Cloud SQL cannot scale writes globally and Firestore's model does not fit relational inventory. Its analytics team then loads sales history for dashboarding — BigQuery. Its mobile app stores user preferences with offline sync — Firestore. The exam nearly always includes one requirement (global strong consistency, document sync, petabyte scans) that eliminates all but one service.

Cloud SQL is the default relational answer, and you deploy it in one command: gcloud sql instances create app-db --database-version=POSTGRES_16 --region=us-central1 --tier=db-custom-2-8192. For availability, enable regional HA at creation with --availability-type=REGIONAL, which maintains a synchronous standby in a second zone and fails over automatically — note this is zonal fault tolerance within one region, not multi-region. Read scale comes from read replicas, which can live in the same region or a different one; cross-region replicas are also Cloud SQL's disaster-recovery story, because you can promote one to a standalone primary.

AlloyDB is the step up for PostgreSQL: a Google-built, PostgreSQL-compatible engine that separates compute from storage and adds a columnar engine for fast analytical queries over operational data. Choose it over Cloud SQL when the question stresses demanding PostgreSQL performance, hybrid transactional-and-analytical queries, or migrating a heavyweight PostgreSQL estate that has outgrown Cloud SQL — while staying wire-compatible with PostgreSQL tools.

Spanner is deployed as an instance with a configuration that fixes its geography: gcloud spanner instances create orders --config=regional-us-central1 --processing-units=100 --description=Orders. The config is the redundancy decision — regional-* configs replicate across zones in one region, while multi-region configs replicate across regions for global strong consistency and higher availability. Capacity is sized in processing units or nodes and scales horizontally without downtime. The trade-off to remember: Spanner costs more at small scale, so if a scenario fits comfortably in Cloud SQL, Spanner is the over-engineered wrong answer.

One gotcha across all three: these are regional-or-wider managed services — you never SSH into them, and machine-level tuning questions about them are distractors.

Firestore is created once per database as a project-level resource with a location that cannot be changed later: gcloud firestore databases create --location=nam5 creates a multi-region database (nam5 spans multiple regions for the highest availability), while a regional location like us-central1 costs less. Firestore gives you strongly consistent document reads and queries, real-time listeners, offline sync for mobile and web clients, and automatic scaling with no capacity planning — which is why mobile app backend is its exam cue. Choose Native mode for new applications; Datastore mode exists for server-side workloads built on the older Datastore API.

Bigtable is provisioned as an instance containing one or more clusters: gcloud bigtable instances create metrics --display-name=Metrics --cluster-config=id=metrics-c1,zone=us-central1-b,nodes=3. It is a wide-column store designed for very high read and write throughput at consistently low latency over huge datasets — time series, IoT telemetry, ad serving, personalization. Scale by adding nodes; add clusters in other zones or regions for replication (covered under redundancy below). Its limits are the selection signal: no SQL joins in the operational path, single-row transactions only, and it is not cost-effective for small datasets — if the scenario is small or needs multi-row transactions, Bigtable is wrong.

Memorystore is the managed in-memory layer — Redis-compatible and Valkey offerings plus Memcached — used as a cache or session store in front of a database: gcloud redis instances create session-cache --size=5 --region=us-central1 --tier=STANDARD_HA creates a Redis instance with a replica and automatic failover (BASIC tier has no replication, so a failure loses the cache). Exam cue: reduce read latency for repeated lookups or store user sessions outside the app means Memorystore, not a bigger database.

BigQuery is the serverless data warehouse: you create datasets and tables, load or stream data in, and query with standard SQL while Google handles all capacity — bq mk --location=US sales_ds then bq load --source_format=CSV sales_ds.orders gs://sales-bucket/orders.csv ./schema.json. There is no instance to size, which is precisely why it is the analytics answer and never the operational-database answer: it is optimized for scanning columns across billions of rows, not for single-row lookups behind an application.

The streaming trio divides by job. Pub/Sub is the global messaging backbone — publishers send to topics, subscribers pull or receive pushes, delivery is at-least-once with automatic scaling — the answer for decoupling services and ingesting event streams: gcloud pubsub topics create clickstream and gcloud pubsub subscriptions create clickstream-sub --topic=clickstream. Dataflow is the managed processing engine (Apache Beam) that transforms data in motion or at rest — the answer whenever a question says transform, enrich, deduplicate, or window a stream, and Google provides prebuilt templates for common paths such as Pub/Sub to BigQuery. Managed Service for Apache Kafka runs real Kafka clusters as a managed service — the answer only when the scenario explicitly requires Kafka compatibility, usually migrating an existing Kafka estate without rewriting producers and consumers; for greenfield messaging, Pub/Sub is the simpler, serverless default.

Scenario: clickstream events publish to a Pub/Sub topic; a Dataflow streaming job windows and enriches them; results land in BigQuery for dashboards. That three-step pipeline — Pub/Sub ingests, Dataflow processes, BigQuery analyzes — is the canonical pattern the exam expects you to assemble from a description.

Cloud Storage prices the same durable object storage four ways, trading lower at-rest cost for retrieval fees and minimum storage durations. All classes share the same 11-nines durability design, the same APIs, and millisecond first-byte access — colder classes are not slower to read, they just charge for retrieval and early deletion. Pick by how often you touch the data:

The minimum duration is the classic trap: deleting or rewriting an Archive object after two months still bills the full 365 days. Create a bucket with a default class and location: gcloud storage buckets create gs://acme-backups --location=us --default-storage-class=NEARLINE --uniform-bucket-level-access. Location is chosen at creation and is permanent: a region for lowest latency next to your compute, a dual-region for two named regions with active-active access, or a multi-region like us for the broadest geo-redundancy. Autoclass can manage class transitions automatically, and Object Lifecycle Management rules (a Section 3 topic) downgrade or delete objects on a schedule you define.

Object storage is not a file system, so when a workload needs a shared POSIX file system mounted by many clients at once, you reach for the file family. Filestore is the default: managed NFS file shares that Compute Engine VMs and GKE Pods mount concurrently — the answer for lift-and-shift apps expecting an NFS mount, shared home directories, and content-management workloads. Create one with gcloud filestore instances create shared-fs --zone=us-central1-a --tier=BASIC_SSD --file-share=name=share1,capacity=2560GB --network=name=default, then mount it over NFS from any client in the VPC. Higher tiers add capacity, performance, and availability options.

Google Cloud NetApp Volumes is the enterprise option: fully managed NetApp ONTAP file storage supporting both NFS and SMB, with enterprise data-management features such as snapshots and replication. Its exam cue is explicit — SMB shares for Windows-based workloads or an organization standardized on NetApp tooling migrating to Google Cloud. Managed Lustre serves the opposite extreme: a managed parallel file system for high-performance computing and AI training, where thousands of clients need massive aggregate throughput to shared scratch data. If the scenario says HPC cluster or feeding a large training job from a shared file system, Managed Lustre is the answer; for everything ordinary, it is over-engineered.

Selection in one line: general NFS means Filestore, SMB or NetApp enterprise features mean NetApp Volumes, HPC-scale parallel throughput means Managed Lustre, and if no client actually needs a mounted file system, step back to Cloud Storage.

Choose a loading tool by size, source, and cadence. For ad hoc uploads from a workstation or VM, use the gcloud storage commands (successor to the legacy gsutil): gcloud storage cp ./export.csv gs://acme-ingest/ copies a file, and gcloud storage rsync ./data gs://acme-ingest/data --recursive synchronizes a directory, transferring only differences — the right answer for scripted, repeatable pushes of moderate size. Large single files are uploaded as parallel composite chunks automatically, but the tool is still bounded by your machine and network, which is the limitation that motivates the next option.

For large-scale or recurring movement, use Storage Transfer Service: a managed service that transfers data into Cloud Storage from other cloud object stores, HTTP endpoints, other buckets, and — via agents — on-premises file systems, with scheduling, filtering, checksumming, and retries handled for you. Exam cues: terabytes from another cloud provider's object storage, nightly sync from a data center, or move objects between buckets on a schedule all mean Storage Transfer Service, not a scripted copy. For truly massive offline datasets where the network is the bottleneck, Google offers a physical appliance option, but the scheduled-network-transfer scenarios on the exam belong to Storage Transfer Service.

Loading into databases follows a two-step pattern: stage in Cloud Storage, then load. BigQuery loads directly from a bucket — bq load --source_format=NEWLINE_DELIMITED_JSON app_ds.events gs://acme-ingest/events/*.json — with load jobs free of query cost. Cloud SQL imports SQL dumps or CSV from a bucket with gcloud sql import sql app-db gs://acme-ingest/dump.sql --database=app; grant the instance's service account read access to the bucket first, the most common failure the exam probes. Firestore and Spanner similarly import from Cloud Storage exports, and Dataflow templates cover streaming and transform-heavy loads.

Multi-region redundancy is configured differently in every service, and the exam tests that you know which knob each one exposes. In Cloud Storage, it is the bucket location: multi-region and dual-region buckets store data redundantly across regions automatically, and dual-region buckets can add turbo replication for a faster replication objective — nothing to operate, just choose the location at creation. In Spanner, it is the instance configuration: pick a multi-region config at creation and Spanner synchronously replicates with global strong consistency and automatic failover. In Firestore, it is the database location: multi-region locations such as nam5 or eur3 replicate across regions with strong consistency and the highest availability tier.

Services without a single switch use replicas. Cloud SQL achieves cross-region redundancy with cross-region read replicas that you promote during a regional outage — an explicit, operator-driven failover, distinct from the automatic zonal failover of regional HA; remember that pairing: HA is zonal, replicas are regional DR. Bigtable replicates by adding clusters in other regions to an instance; replication is asynchronous and eventually consistent, and app profiles route traffic between clusters for failover or multi-cluster serving. BigQuery datasets placed in a multi-region location such as US or EU are stored redundantly within that geography, and cross-region dataset replication can copy datasets between regions for disaster recovery. Memorystore offers replicas and, in its cluster offerings, cross-zone distribution — but a cache should be rebuildable by design.

Scenario: a health-tech company must survive a full regional outage. It stores documents in a dual-region bucket with turbo replication, runs Spanner in a multi-region config for patient-facing transactions, keeps a cross-region Cloud SQL replica for a legacy reporting app, and places BigQuery datasets in the US multi-region. Four services, four different mechanisms — knowing which is automatic (Storage, Spanner, Firestore, BigQuery within a multi-region) and which is operator-driven (Cloud SQL promotion, Bigtable app-profile failover) is exactly what the question will probe.