Google PCA: Data Storage, Databases and Analytics Architecture — Study Guide

By Laurynas Brazys · 5 September 2026 · #google #PCA #certification #exam-prep #study-guide

Part of the Google Professional Cloud Architect — Study Guide. Practice with verified answers in the Google exam hub, or take timed practice tests on ExamRoll.io.

Overview

Designing data storage, databases, and analytics on Google Cloud requires matching workload patterns to services while planning for durability, availability, access control, cost, and operational resilience. This section covers object storage and lifecycle governance; operational databases and caches; analytics warehousing and processing; ingestion architectures; and governance, protection, and performance practices. It calls out design choices, operational reasoning, and common failure modes or trade-offs.

Storage and Object Architecture

Cloud Storage bucket design

Location: choose region for low-latency, cost-sensitive workloads; dual-region for business continuity with predictable failover; multi-region for global read access. Dual-region offers optional turbo replication for an SLA-backed, low RPO; otherwise replication is asynchronous.
Namespace and separation: use separate buckets for data domains, environments, and sensitivity levels. Employ uniform bucket-level access and public access prevention for consistent permissions.
Storage classes: Standard for hot data; Nearline for infrequently accessed (monthly); Coldline for quarterly access; Archive for long-term retention. Autoclass can optimize class placement automatically with minimal operational work.
Lifecycle policies: automate transition and deletion by age, storage class, or object prefix. Example to delete objects older than 90 days: { “rule”: [ { “action”: {“type”: “Delete”}, “condition”: {“age”: 90} } ] } Apply with: gsutil lifecycle set lifecycle.json gs://my-bucket
Retention and legal holds: configure bucket retention policies and optionally lock them to prevent reduction (compliance). Event-based holds and object versioning enable recovery from accidental deletes or overwrites.
Replication: choose dual-region for synchronous consistency semantics at the API with background replication across two regions; use bucket-to-bucket replication (for cross-project or cross-location copies) to meet specialized RPO/RTO or separation of duties.

Failure modes and trade-offs

Class mismatch inflates cost and latency. Autoclass reduces this but adds per-object management overhead.
Retention lock is irreversible; test policies in non-production.
Replication improves durability but can increase write latency and cost; design read/write paths for targeted locations.

Patterns

Data lake: raw and curated zones on Cloud Storage; governance via Dataplex; externalized schemas in Data Catalog.
Archival: Archive class with retention lock for compliance, with BigQuery external tables or on-demand restore for rare analysis.
Lakehouse: use BigLake to unify access across Cloud Storage and BigQuery with consistent security.

Operational Datastores and Caching

Cloud SQL

High availability: regional HA with synchronous replication to a standby; automatic failover typically within seconds to a couple of minutes. The instance endpoint stays the same, minimizing app changes.
Read replicas: within-region or cross-region, asynchronous; good for read scale-out and DR. Monitor lag; stale reads can affect correctness.
Backups and PITR: scheduled backups plus point-in-time recovery using transaction logs (generally up to 7 days window, depending on engine). Test restore regularly.
Private connectivity: private IP via VPC peering reduces exposure and latency; plan IP ranges to avoid overlap.
Migration: Database Migration Service supports low-downtime moves from on-prem or other clouds; for high-volume links, prefer Dedicated or Partner Interconnect over VPN to reduce packet loss and latency.

Trade-offs and failure modes

HA failovers reset connections; applications must retry with backoff. Maintenance windows can briefly degrade performance.
Long-running transactions increase replication lag and PITR recovery time.
Over-provisioned storage is cheap insurance; under-provisioned IOPS cause latent failures under peak.

Cloud Spanner

Global scale and consistency: strongly consistent reads/writes across regions using TrueTime and two-phase commit. Choose regional for lowest write latency; multi-region for higher availability and global reads.
Transactions: external consistency and fully ACID across rows and tables; read-only transactions scale across replicas.
Schema design: pick primary keys that avoid hotspots; use interleaved tables for locality; consider secondary indexes’ write amplification and backfill behavior.
Regionality: leader region placement drives write latency; multi-region adds quorum costs and commit latency.

Trade-offs

Write latency grows with geographic footprint; avoid multi-region unless availability and global distribution justify it.
Baseline cost is higher than single-node VM databases; capacity planning must align with SLOs and growth.

Firestore, Bigtable, Memorystore, and selection

Firestore: document database for mobile/web backends; strong consistency for single documents; fine-grained security; automatic indexing. Beware contention on frequently updated documents; use distributed counters and batched writes.
Bigtable: wide-column, petabyte-scale, ultra-low-latency time-series and IoT; design row keys to avoid hotspots (e.g., hash or salting); scale by nodes and clusters; multi-cluster routing for availability. Replication is asynchronous; strong consistency is per-cluster.
Memorystore for Redis: in-memory cache; Basic tier is single instance (no HA); Standard tier provides replica and automatic failover (brief disconnects possible). Treat as cache, not source of truth; persistence features reduce volatility but do not replace database backups.

Workload-driven selection

Relational with joins/ACID and moderate scale: Cloud SQL.
Global relational with horizontal scale and external consistency: Cloud Spanner.
High-throughput time-series/telemetry or very large key-value: Bigtable.
App-centric document models with hierarchical queries: Firestore.
Ephemeral acceleration and rate limiting: Memorystore.

Analytics, Ingestion, and Processing

BigQuery

Datasets: logical security and billing boundaries; adopt naming conventions by domain and lifecycle stage.
Partitioning: ingestion-time or column-based for temporal filtering; also integer range partitioning. Use time-unit partitioning for event_ts to prune scans.
Clustering: up to four columns to co-locate related data on storage; improves selective queries’ performance and cost.
Reservations: manage dedicated slots via reservations and assignments; use flex slots for bursty experiments; isolate critical workloads to avoid starvation.
Access control: IAM at project and dataset; table/column control with row-level policies and policy tags; share curated data via authorized views and routines.

Useful example: bq mk –dataset myproj:analytics bq mk –table –time_partitioning_field=event_ts –clustering_fields=user_id,device_type myproj:analytics.events ./schema.json

Trade-offs and failure modes

Poor partitioning leads to full-table scans and runaway costs.
Clustering only helps when filters or joins include clustered columns; frequent reshuffles can reduce benefit.
Slot under-provisioning queues jobs; over-provisioning raises cost. Monitor slot utilization and spilled shuffle.

Data ingestion and processing

Pub/Sub: global, durable, at-least-once delivery; ordering keys enforce per-key ordering with throughput trade-offs. Design idempotent consumers.
Dataflow: unified batch and streaming with autoscaling, exactly-once stateful processing, windowing, and triggers; Streaming Engine offloads state. Use dead-letter topics and replayable sources.
Dataproc: managed Spark/Hadoop for existing code and ecosystems; ephemeral clusters or autoscaling; use for ML libraries or when porting is costly.

Batch vs streaming trade-offs

Streaming cuts latency and supports real time, but increases complexity (state, watermarking, late data) and ongoing costs.
Batch simplifies correctness and cost control; acceptable when SLAs tolerate delay.
Hybrid patterns: land raw events in Cloud Storage, stream aggregated KPIs to BigQuery, and run nightly batch re-computations for accuracy.

Warehouse patterns

Warehouse: BigQuery as the system of analysis; materialized views and scheduled queries for serving BI.
Lakehouse: manage data in Cloud Storage with open formats; expose through BigLake to BigQuery with consistent security.

Governance, Protection, and Performance

Data governance and security

Metadata and lineage: use Data Catalog for technical and business metadata; enable lineage capture from Dataflow, BigQuery, and Dataproc to trace dependencies.
Quality: enforce rules with Dataplex data quality and orchestrate checks in Composer or Dataform; quarantine bad records.
Access boundaries: VPC Service Controls around BigQuery and Cloud Storage to reduce exfiltration risk; IAM Conditions for context-aware access; CMEK for cryptographic control; policy tags for column-level restrictions.
Retention: align Cloud Storage bucket retention, BigQuery table time travel (configurable up to 7 days), and dataset/table expiration with legal requirements.

Backup, PITR, and deletion protection

Validate backups: periodically restore Cloud SQL and Spanner backups into isolated environments and run checksums and application-level validations.
Bigtable: enable PITR to recover to a timestamp within a configured retention; test table- or cluster-level restores.
Spanner: use backups for disaster recovery; leverage stale reads within version retention for audit queries.
Cloud Storage: enable object versioning and bucket retention lock to guard against accidental deletions; replicate to a separate project for operator error isolation.
BigQuery: use time travel and table snapshots; avoid drops on production datasets with required approvals and dataset-level deletion protection.

Data performance and cost control

Avoid hot keys: distribute Bigtable row keys (hash prefixes), choose Spanner primary keys that randomize leadership elections, shard Firestore counters.
Indexes: maintain necessary SQL and NoSQL indexes; in BigQuery, cluster on common filters; in Cloud SQL, monitor slow queries and vacuum/analyze Postgres regularly.
Capacity planning: baseline with load tests; set SLOs and error budgets; monitor BigQuery slot utilization, Bigtable CPU/read-modify-write latencies, Cloud SQL CPU/IOPS, and Pub/Sub backlog.
Cost controls: use BigQuery slot commitments for steady workloads, Autoclass for Cloud Storage, compact Bigtable tables and tune Bloom filters, expire old partitions and datasets, and implement per-team budgets and alerts.

Practical Problem Scenario

Acme Retail Group needs a unified, low-latency clickstream and order analytics platform. Requirements: real-time KPIs within 10 seconds, historical analysis over five years with SQL, sub-hour recovery objectives, strict data residency in the US, and prevention of accidental data loss.

Land raw events and implement durable ingestion

Create dual-region us-central1/us-east1 Cloud Storage buckets for raw and curated zones; enable Autoclass and object versioning on raw.
Rationale: dual-region meets durability and residency; versioning protects against bad backfills; Autoclass optimizes storage cost automatically.

Stream events reliably with Pub/Sub and Dataflow

Publish clickstream and order events to Pub/Sub topics with ordering keys per user_id; implement a Dataflow streaming pipeline to validate, deduplicate, enrich, and branch output to BigQuery (hot KPIs) and Cloud Storage (parquet in curated).
Rationale: Pub/Sub provides global, durable at-least-once delivery; Dataflow offers exactly-once state and autoscaling; branching maintains a lakehouse pattern for reprocessing.

Serve real-time features in Bigtable and Redis

Write a subset of enriched events to Bigtable with a salted user_id#timestamp row key; use Memorystore for Redis as a front cache for the most recent sessions.
Rationale: Bigtable delivers low-latency, high-throughput writes for time-series; salting avoids hotspots; Redis reduces tail latency for live personalization.

Warehouse and optimize queries in BigQuery

Create datasets partitioned by event_date and clustered by user_id, channel. Use materialized views for KPIs and scheduled compactions for small-file loads; purchase a baseline slot reservation with a small flex slot buffer for bursts.
Rationale: partitioning and clustering prune scans and reduce cost; materialized views accelerate dashboards; reservations bound cost and protect critical workloads from queuing.

Govern access and prevent exfiltration

Apply dataset-level IAM for analyst groups; use policy tags to restrict PII columns and authorized views for vendor access. Enforce VPC Service Controls around BigQuery and Cloud Storage; use CMEK for regulated datasets.
Rationale: least-privilege at dataset and column levels; VPC SC reduces data exfiltration risk; CMEK satisfies cryptographic control requirements.

Implement backups, PITR, and restore tests

Enable Bigtable PITR for 7–14 days; create weekly Spanner or Cloud SQL backups for transactional order stores; snapshot BigQuery critical tables daily and rely on time travel for mistakes. Run quarterly restore drills into an isolated project.
Rationale: layered recovery options address logical errors and disasters; regular drills validate RPO/RTO and runbooks.

Control data retention and lifecycle

Apply a lifecycle rule to purge raw objects after 30 days and curated after five years; lock a bucket-level retention policy that meets compliance. Set BigQuery dataset/table default expiration for transient datasets.
Rationale: automatic enforcement reduces operational risk; retention lock prevents accidental or unauthorized policy weakening.

Monitor performance and cost, and mitigate hotspots

Track BigQuery slot utilization, bytes scanned, and BI query concurrency; monitor Bigtable CPU and read-modify-write latency; alert on Pub/Sub backlog. If user_id hotspots emerge, increase salt width and backfill keys via Dataflow.
Rationale: continuous telemetry finds bottlenecks early; proactive key strategy changes preserve SLOs without wholesale rearchitecture.

Provide private connectivity and isolation

For hybrid dependencies, use Partner or Dedicated Interconnect with Cloud Router; ensure non-overlapping IP ranges; use Private Service Connect for managed service endpoints.
Rationale: private paths reduce latency and packet loss; clear IP planning and PSC enforce isolation and predictable routing.

Operationalize reliability

Enable canary Dataflow pipelines and blue/green BigQuery views; enforce schema evolution via Data Catalog approvals; integrate dead-letter queues with incident response.
Rationale: controlled rollouts limit blast radius; governed schema changes maintain data quality; DLQs ensure no data is lost during incidents.

← Compute · All domains · Networking →

Practice these questions → · Timed practice on ExamRoll.io →