4.3 Choosing a Data Storage Product: Cloud SQL, BigQuery, Firestore, Spanner & Bigtable

Why This Section Matters

The exam guide's third planning bullet is data storage product choice, and it is arguably the single most-tested "which service?" decision on the entire ACE exam because Google Cloud offers so many overlapping-sounding database options. The exam expects you to sort a scenario along two axes almost instantly: transactional versus analytical, and relational (SQL) versus non-relational (NoSQL). Once those two questions are answered, the correct product is usually the only one left standing.

The Two Questions That Decide Everything

1. Is this workload transactional (OLTP) — many small, fast reads/writes supporting an application — or analytical (OLAP) — scanning large volumes of historical data to find patterns and produce reports?
2. If transactional, does it need a fixed relational schema and SQL joins, or is it flexible/document/key-value shaped?

The Five Core Products

Cloud SQL is a fully managed relational database running MySQL, PostgreSQL, or SQL Server. It automates provisioning, patching, backups, and failover, but it scales vertically within a single region — there is a ceiling on how large a single instance can grow. It is the default choice for general-purpose web apps, CRM/ERP systems, and any workload that just needs "a normal SQL database, but managed."

Cloud Spanner is Google's globally distributed, horizontally scalable relational database with SQL support and strong (external) consistency, backed by a 99.999% availability SLA on multi-region configurations. Choose Spanner over Cloud SQL exactly when a scenario needs relational/SQL semantics at a scale or geographic distribution Cloud SQL cannot reach — global financial ledgers, worldwide inventory systems, or any workload where a single-region vertical-scaling ceiling would eventually be hit.

AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible database built for the most demanding enterprise workloads, offering significantly higher transactional throughput and much faster analytical queries than standard PostgreSQL while remaining wire-compatible with the open-source ecosystem. Think of it as the answer when a scenario specifically calls for PostgreSQL compatibility at higher performance than Cloud SQL for PostgreSQL provides, without needing Spanner's global distribution.

Firestore is a serverless, NoSQL document database optimized for application and mobile/web backends: it offers real-time data synchronization, offline support for mobile clients, and flexible JSON-like documents rather than fixed schemas/tables. It is the default pick for mobile app backends, user profiles, and content that naturally nests as documents.

Bigtable is a wide-column NoSQL database built for massive scale, high throughput, and low latency — think terabytes to petabytes of data with millions of operations per second, such as IoT sensor streams, time-series data, and ad-tech clickstreams. Bigtable does not support SQL joins or complex ad-hoc queries; it is optimized around fast lookups by row key.

BigQuery, though it also appears in this ecosystem, is fundamentally different from the other four: it is a serverless data warehouse for OLAP — large-scale SQL analytics over historical/aggregated data — priced primarily by data stored plus data scanned per query (or flat-rate/reservation pricing for predictable heavy workloads). If a scenario describes "running ad-hoc SQL reports across years of business data," BigQuery is almost always the answer, regardless of what other database is mentioned in the same sentence.

Comparison Table

Realistic Exam Scenario

A logistics company needs a single source of truth for shipment records that must remain strongly consistent across data centers in the US, Europe, and Asia, using standard SQL for application queries, with no acceptable window of inconsistency. Cloud SQL cannot span regions with strong consistency, so the answer is Cloud Spanner — the combination of "global," "SQL," and "strongly consistent" is Spanner's exact niche.

Now contrast: a mobile fitness app needs to sync workout data across a user's phone and the cloud in real time, and must keep working (queueing writes) when the phone loses connectivity. That combination of real-time sync and offline support points to Firestore, not Cloud SQL or Bigtable, since neither of those offers native mobile offline persistence.

Common Traps

• Picking Bigtable for a workload that needs SQL joins or complex queries — Bigtable intentionally trades query flexibility for raw throughput, so if the scenario needs relational queries, Bigtable is wrong even if the data volume sounds "big."
• Picking BigQuery for an OLTP workload just because it says "big" data — BigQuery is not designed for row-level transactional updates at low latency; that is Cloud SQL, Spanner, AlloyDB, or Firestore territory.
• Defaulting to Cloud SQL whenever "relational" appears in a scenario without checking for the words "global," "multi-region," or "unlimited horizontal scale" — those words signal Spanner, not Cloud SQL.