SkarpSkarp

Chapter 7 of 26

Planning Data and Storage: Cloud Storage, Cloud SQL, and BigQuery

Design storage solutions that balance performance, durability, and cost using Google Cloud’s flagship data services for both transactional and analytical workloads.

27 min readen

Big Picture: Planning Storage on Google Cloud

Why This Module Matters

You will often be asked to pick the right storage service. This module focuses on Cloud Storage, Cloud SQL, and BigQuery, and how to balance performance, durability, and cost.

Three Flagship Services

  • Cloud Storage: object storage for files.
  • Cloud SQL: managed relational databases.
  • BigQuery: serverless data warehouse for analytics.

Balancing Trade-offs

You must weigh latency, durability, and cost. Exam scenarios use keywords like "transactions" (Cloud SQL), "TBs of data" (BigQuery), and "unstructured files" (Cloud Storage).

Architectures Combine Services

Real designs mix services: app data in Cloud SQL, exports to Cloud Storage, then loaded to BigQuery. You will learn to design and justify these flows.

Cloud Storage: Buckets, Classes, and Durability

What is Cloud Storage?

Cloud Storage is object storage. You store immutable objects in buckets, each with metadata. Think images, logs, backups, and large data files.

Bucket Locations

Choose region for low-latency local access, dual-region for high availability in two regions, or multi-region for broad geographic replication.

Storage Classes

Standard for hot data, Nearline for monthly access, Coldline for rare access, Archive for almost-never access. Same durability; costs differ.

Common Exam Trap

Storage class is per object, but you set a bucket default. Do not use Cloud Storage as a database filesystem; use Cloud SQL or other DBs.

Cloud Storage Lifecycle and Cost Optimization

Why Lifecycle Rules?

Lifecycle management automates moving and deleting objects based on age, class, or metadata, helping control long-term storage costs.

Typical Rule Chain

Example: Standard for 30 days, then Nearline, then Coldline after 180 days, then delete after 2 years. Fresh data is fast; old data is cheap.

Per-Bucket Behavior

Rules are defined on buckets. You can filter by prefixes like `logs/` to target subsets, but you cannot partially apply rules without filters.

Minimum Storage Durations

Nearline, Coldline, Archive have minimum storage durations. Deleting early still incurs charges, so design lifecycles with this in mind.

Cloud SQL: Engines, Deployments, and HA

What is Cloud SQL?

Cloud SQL is a managed relational database for transactional workloads. It supports MySQL, PostgreSQL, and SQL Server with ACID and SQL.

Machine and Storage Choices

You pick CPU, RAM, and storage type. Storage can auto-grow to avoid downtime when disks fill, but you still design for expected load.

High Availability

HA instances have a primary and standby in different zones with automatic failover. Use when minimizing downtime is critical.

Read Replicas

Read replicas handle read-heavy traffic and reporting. They are asynchronous, so not for strongly consistent reads.

Cloud SQL Backup, Recovery, and Durability

Automated Backups

Cloud SQL can run daily automated backups with configurable retention. These are required for point-in-time recovery.

Point-in-Time Recovery

With PITR and binary logs, you can restore to a specific time to fix issues like accidental deletes at a known timestamp.

On-Demand Backups & DR

Take manual backups before risky changes. Use cross-region replicas for disaster recovery beyond a single region.

Common Traps

Backups off means no PITR. Restores create a new instance, not overwrite. HA handles outages; backups handle data loss.

BigQuery Fundamentals: Datasets, Tables, and Partitions

What is BigQuery?

BigQuery is a serverless data warehouse. You pay mainly for stored data and bytes scanned by queries, not for managing servers.

Datasets and Tables

Datasets are containers with access control. Tables hold rows and columns and can be native or external (e.g., over Cloud Storage).

Partitioning & Clustering

Partition tables by ingestion time, date, or integer ranges. Cluster within partitions by columns like user_id to speed up filtered queries.

Know When Not to Use It

BigQuery is not for high-QPS transactional workloads. Use it when you see analytics over large datasets and time-based queries.

Choosing Between Cloud Storage, Cloud SQL, and BigQuery

Scenario 1: E-commerce DB

Transactional orders, payments, and strong consistency point to Cloud SQL. Add HA and read replicas if uptime and read scale are important.

Scenario 2: 5 TB Analytics

Ad-hoc SQL over TBs of clickstream data fits BigQuery with date partitioning. Cloud SQL would struggle; Cloud Storage alone cannot run SQL.

Scenario 3: Image Storage

Millions of images for a global app should use Cloud Storage in multi- or dual-region, plus lifecycle rules. DBs are overkill here.

Fast Decision Heuristic

Files → Cloud Storage. Transactions → Cloud SQL. Large-scale analytics → BigQuery. Then tune details like HA, classes, and partitions.

Designing a Simple Data Flow: Thought Exercise

Work through this design in your head or on paper. Try to name specific Google Cloud services and key configuration choices.

Scenario

A mobile game sends gameplay events (score updates, item purchases) to your backend. Product managers want near-real-time dashboards for daily active users and revenue. You also need to keep raw event data for at least 2 years for future analysis, but you rarely look at data older than 90 days.

Task 1: Pick a storage service for raw event files

  • You will batch events into JSON files every 5 minutes.
  • Where do you store these files?
  • What storage class and location would you choose initially?

Task 2: Pick a service for analytics

  • Product managers want to run SQL queries, group by date, country, and in-game item, and build dashboards.
  • Which service is best for this analytical workload?
  • How would you organize the data (datasets, tables, partitioning)?

Task 3: Long-term cost optimization

  • You must retain raw events for 2 years, but you only query the last 90 days frequently.
  • How could you use Cloud Storage lifecycle rules to control costs?
  • How might partitioning in BigQuery help reduce query costs?

Pause here and sketch your answer. Then compare with the suggested design in the next step (mentally or by re-reading earlier steps). Focus on:

  • Clear separation between raw files vs analytical tables.
  • Matching storage classes and partitioning to access patterns.
  • Using automation (lifecycle, scheduled loads) instead of manual management.

Quick Check: Picking the Right Service

Test your ability to choose between Cloud Storage, Cloud SQL, and BigQuery in exam-style questions.

Your company needs to store 50 TB of application logs for at least 1 year. Engineers mostly query the last 7 days of logs with SQL, but occasionally run investigations over the last 6 months. Cost is a concern. Which combination is the most appropriate?

  1. Store all logs in Cloud SQL; use read replicas for analytics.
  2. Store raw logs in Cloud Storage; load recent logs into a partitioned BigQuery table for analytics; use lifecycle rules to move older objects to colder storage classes.
  3. Store all logs only in BigQuery as an unpartitioned table; rely on query filters to limit scanned data.
  4. Store logs in a multi-region Cloud Storage Standard bucket only; download logs locally for analysis when needed.
Show Answer

Answer: B) Store raw logs in Cloud Storage; load recent logs into a partitioned BigQuery table for analytics; use lifecycle rules to move older objects to colder storage classes.

Option 2 is correct: Cloud Storage is ideal for raw log files and cheap long-term storage, while BigQuery is ideal for SQL analytics on recent data. Using partitioned tables and lifecycle rules balances performance and cost. Option 1 is not suited for 50 TB of logs and analytics in Cloud SQL. Option 3 wastes money by not partitioning. Option 4 lacks scalable SQL analytics.

Quick Check: Cloud SQL and BigQuery Details

Another short quiz to reinforce key configuration concepts.

A financial reporting team needs to run daily batch queries over 3 TB of transactional data. The application currently uses Cloud SQL PostgreSQL. They complain that long analytical queries are slowing down the production database. What is the best approach?

  1. Increase the CPU and RAM of the Cloud SQL instance and accept longer maintenance windows.
  2. Enable Cloud SQL high availability; this will offload analytical queries to the standby instance.
  3. Create a read replica of the Cloud SQL instance for analytics, or better, export data regularly to BigQuery and run analytical queries there.
  4. Move the entire application database from Cloud SQL to BigQuery and point the application directly at BigQuery for OLTP and analytics.
Show Answer

Answer: C) Create a read replica of the Cloud SQL instance for analytics, or better, export data regularly to BigQuery and run analytical queries there.

Option 3 is correct: separating OLTP and OLAP workloads is best. A Cloud SQL read replica can handle reporting, and exporting to BigQuery provides scalable analytics. Option 1 may help temporarily but does not separate workloads. Option 2 misunderstands HA; standby is not for reads. Option 4 misuses BigQuery for OLTP.

Key Term Review: Storage Services

Flip through these flashcards to reinforce core concepts and exam-ready definitions.

Cloud Storage
Google Cloud's object storage service for unstructured data such as images, backups, and log files. Data is stored as immutable objects in buckets, with multiple storage classes and locations for balancing performance and cost.
Cloud Storage Standard class
A Cloud Storage class optimized for frequently accessed (hot) data, offering low latency and high throughput. Recommended for active content, web assets, and data being processed.
Cloud Storage lifecycle rule
A bucket-level configuration that automatically performs actions such as changing storage class or deleting objects based on conditions like object age, current class, or name prefix.
Cloud SQL
A fully managed relational database service on Google Cloud that supports MySQL, PostgreSQL, and SQL Server, suitable for transactional (OLTP) workloads requiring SQL and ACID properties.
Cloud SQL high availability (HA)
A configuration where Cloud SQL maintains a primary and standby instance in different zones within a region, using synchronous replication and automatic failover to reduce downtime during zonal failures.
Cloud SQL read replica
An asynchronous copy of a Cloud SQL primary instance used to offload read-only queries and reporting workloads, improving read scalability but not providing strongly consistent reads.
Point-in-time recovery (PITR) in Cloud SQL
A feature that, when enabled with automated backups and binary logging, lets you restore a Cloud SQL instance to an exact time within a retention window to recover from accidental changes or deletions.
BigQuery dataset
A logical container in BigQuery that holds tables and views and acts as a boundary for organizing data and applying access controls.
BigQuery partitioned table
A BigQuery table whose data is divided into partitions based on ingestion time, a date/timestamp column, or an integer range, enabling queries to scan only relevant partitions and reduce cost.
BigQuery clustering
A feature that organizes data within a table or partition based on one or more columns (such as user_id or country) to improve query performance and reduce bytes scanned when filtering on those columns.

Key Terms

BigQuery
Google Cloud’s serverless data warehouse for large-scale analytical SQL queries over structured and semi-structured data.
Cloud SQL
A fully managed relational database service on Google Cloud that supports MySQL, PostgreSQL, and SQL Server, suitable for transactional (OLTP) workloads requiring SQL and ACID properties.
Cloud Storage
Google Cloud's object storage service for unstructured data such as images, backups, and log files. Data is stored as immutable objects in buckets, with multiple storage classes and locations for balancing performance and cost.
BigQuery dataset
A logical container in BigQuery that holds tables and views and acts as a boundary for organizing data and applying access controls.
BigQuery clustering
A feature that organizes data within a table or partition based on one or more columns to improve query performance and reduce bytes scanned when filtering on those columns.
Cloud SQL read replica
An asynchronous copy of a Cloud SQL primary instance used to offload read-only queries and reporting workloads, improving read scalability but not providing strongly consistent reads.
BigQuery partitioned table
A BigQuery table whose data is divided into partitions based on ingestion time, a date/timestamp column, or an integer range, enabling queries to scan only relevant partitions and reduce cost.
Cloud Storage lifecycle rule
A bucket-level configuration that automatically performs actions such as changing storage class or deleting objects based on conditions like object age, current class, or name prefix.
Point-in-time recovery (PITR)
A feature that, when enabled with automated backups and binary logging, lets you restore a Cloud SQL instance to an exact time within a retention window to recover from accidental changes or deletions.
Cloud SQL high availability (HA)
A configuration where Cloud SQL maintains a primary and standby instance in different zones within a region, using synchronous replication and automatic failover to reduce downtime during zonal failures.

Finished reading?

Test your understanding with a custom practice exam on this chapter.

Test yourself