Choosing Storage Systems for Analytical and Operational Data

Analyzing data access patterns (opens in a new tab) means matching how data is written, read, and retained to the storage layout that scans the least data and spreads load evenly. A workload is described by data type, current and future capacity, whether capacity scales automatically, whether data is accessed from outside Google Cloud, and whether multiple clients read or write at the same time. Read-write behavior typically falls into frequent writes and reads, frequent writes with occasional reads, occasional writes and reads, or occasional writes with frequent reads.

1.1Choose the Storage Engine by Access Pattern

The storage engine choice is the first schema decision because columnar analytics and row-based operations need different layouts. BigQuery stores data in a columnar format that scans large datasets and runs aggregations without traditional indexes. Bigtable fits fast access to a single, small set of rows, while Spanner or Cloud SQL fit transaction processing that needs strong relational consistency.

BigQuery separates storage and compute so query workers scan relevant tables in parallel and scale independently with demand. Because of that separation, moving an online transaction processing (OLTP) workload into BigQuery creates poor performance, so that workload should move to Cloud SQL, Spanner, or Firestore instead.

1.2Design BigQuery Partitioning and Clustering Around Filters

BigQuery partitioning divides a large table into segments so queries with a filter on the partitioning column scan only matching partitions. Partition design must match the filter queries use most often, such as event date for daily reporting or customer ID range for ID-filtered queries. BigQuery does not support hierarchical partitioning, and it does not support EXCHANGE PARTITION, SPLIT PARTITION, or converting a nonpartitioned table to a partitioned one, so use sharding instead when those limits block a design.

BigQuery implements three partitioning types for different filter columns:

• Tables partitioned by ingestion time, where partitions are based on data ingestion time.
• Tables partitioned by column, where partitions are based on a TIMESTAMP or DATE column.
• Tables partitioned by integer range, where partitions are based on an integer column.

BigQuery clustering organizes data on disk by the values in one or more columns that are often filtered or aggregated together. Clustering stores related values together, which helps retrieval when clustered columns are used as predicates. Use clustering when a table is commonly filtered and aggregated with specific columns, especially when those columns are accessed together. For strings, use clustering because partitioning supports only dates and integers.

1.3Use Nested Schemas and Materialized Views for Repeated Reads

BigQuery nested and repeated columns let one row contain structured records and arrays instead of spreading related data across joined tables. A nested column holds a record structure, while a repeated column holds multiple values for the same row. This design matches access paths that always fetch a parent and its children together, such as orders with line items or events with attributes. Denormalization reduces joins and fits the columnar layout because each nested field is still stored by column.

BigQuery materialized views are precomputed views that periodically cache view query results in BigQuery storage. The cached results serve write-once, read-many reports without recomputing a full base-table scan each time. BigQuery leverages precomputed results and whenever possible reads only delta changes from the base table to return current results. Smart Tuning materialized views always return current data and automatically rewrite a query to use the materialized view even when the query refers to the base table. Choose materialized views when many queries repeat the same aggregations, filters, or joins over slowly changing data.

1.4Diagnose Retrieval Versus Ingestion Bottlenecks

Diagnosis of bottlenecks starts from the architecture because BigQuery stores data in Colossus in a compressed columnar format called Capacitor and executes queries with Dremel over a multilevel tree. Storage resources are allocated as data is consumed and deallocated when data is removed, so storage scales without resizing compute. Because compute, memory, and storage are treated separately, adding stored bytes does not add query power, and slow queries with small scans point to compute or query shape rather than storage size.

Data flows from source extracts into Cloud Storage staging, then from staging into BigQuery tables through batch load, Storage Write API, or Data Transfer Service, and only then flows from Colossus storage through Dremel workers to query results. If load jobs are slow but queries on loaded partitions are fast, the constraint sits in extraction, network bandwidth, or staging. If loads complete normally but queries scan excessive data or consume disproportionate slots, the constraint sits in table design or SQL structure. Column statistics gathered at load time improve later plans, so fragmented or frequently rewritten loads can degrade both paths.

Slots, which are units of computation that include CPU and RAM, are the primary signal for retrieval limits. BigQuery gathers column statistics while loading data and exposes query plan and timing information for each query. A plan that shows large bytes scanned, late-stage aggregation, or skewed stages with high slot-time points to query shape or data layout, while queuing under concurrent load points to slot quota limits. Separation of execution lets different projects query datasets in other projects with separate slot quotas and billing, so diagnosis must isolate whether contention comes from the project running the query or the project hosting the dataset.

1.5Evaluate Latency, Throughput, and Sharing for Storage Choice

Evaluation of operational storage separates block, file, and object options by latency, throughput, sharing, and replication. Google Cloud Hyperdisk provides better performance, flexibility, and efficiency than Persistent Disk, and Hyperdisk Balanced lets you provision IOPS and throughput separately for read-heavy, write-heavy, or mixed workloads. Persistent Disk in zonal form serves single-zone tiers, while regional Persistent Disk and Hyperdisk Balanced High Availability replicate synchronously between two zones in the same region to stay available during a single-zone outage. Filestore regional instances store data shared across multiple virtual machines (VMs) in a region, such as configuration files and centralized logs, with data replicated synchronously across three zones.

Capacity planning validates the choice before selection by forecasting users, request rate, seasonality, spikes, and geographic growth. Estimation then sizes managed instance groups (MIGs), machine types, and disk count, type, and size, with cost estimated through the Pricing Calculator. Controls include N+1 redundancy, load benchmarking under normal and peak conditions, quota review for resource and rate limits, and reservations that assure capacity in a specific zone.

Choosing managed services means selecting the Google Cloud database that fits data shape, access pattern, scale, and operational trade-offs for analytical and transactional workloads. Operational systems with frequent small reads and writes and strict latency point to relational, document, or in-memory stores, while bulk analytical reads point to warehouse and lake stores. The decision weighs structure, consistency, throughput, replication scope, and cost.

2.1Serve Streaming and Time-Series Data with Cloud Bigtable

Cloud Bigtable (opens in a new tab) is the managed NoSQL wide-column service for high-velocity streaming, time-series, and real-time analytics that need high throughput and low-latency reads and writes at scale. Cloud Bigtable separates durable storage from compute nodes, so throughput scales by adding nodes while durability is handled in a separate transparent tier. Data flows from producers into Bigtable tables keyed for fast point reads and range scans, then flows out to analytics through scans, exports, or change streams. Bigtable is not a traditional relational database even though it supports SQL queries.

Storage and cluster sizing directly determine sustained throughput, latency, and cost. SSD storage supports low-latency results and high queries-per-second (QPS), while HDD storage is preferred for write-intensive workloads where reads are range scans for batch processing. Size a single-cluster instance by taking the larger of storage need and compute need:

1. Estimate unreplicated size with nodetool tablestats by dividing total flushed storage size by the keyspace replication factor, then divide that total by 3.5 TB to get SSD nodes for storage alone.
2. Estimate minimum SSD nodes for compute by dividing sustained reads plus writes by 10,000, with more nodes likely for low-latency results.
3. Take the larger of the storage-based and compute-based node counts as the starting size, then adjust manually or with the Cloud Bigtable API.

For a multi-cluster instance, create one cluster for every Cassandra data center, where each Bigtable cluster must store all data and handle the total insert rate. Start each cluster with the capacity of the busiest data center so the instance handles all writes and reads to at least two data centers during an outage, then scale clusters individually with zero downtime.

2.2Route Bigtable Traffic with App Profiles and Performance Tiers

App profiles control how traffic flows from applications to clusters in a Bigtable instance. Standard app profiles use provisioned cluster nodes and support single-cluster routing, which sends all requests to one cluster, and multi-cluster routing, which sends requests to the nearest available cluster among all clusters or a specified group. Data Boost app profiles use serverless compute instead of cluster nodes and are restricted to single-cluster routing. Use single-cluster routing when strong read-your-write consistency or the in-memory tier is needed, use multi-cluster routing when geographic proximity and availability matter, and use Data Boost when high-throughput reads must be isolated from core traffic.

The in-memory tier accelerates eligible point-read traffic by serving it from memory instead of provisioned nodes and disk. You can enable the in-memory tier only for app profiles that use single-cluster routing, and when configured on a cluster, all eligible point-read traffic uses the tier. Data Boost isolates heavy scans so batch analytics do not contend with latency-sensitive serving traffic on cluster nodes. Because Bigtable separates storage and compute, adding nodes can yield noticeable improvements within minutes. Performance tuning uses console dashboards for throughput and utilization at instance, cluster, and table levels, with custom views and alerts in Cloud Monitoring, while Key Visualizer visualizes access across keys to find hotspots.

2.3Protect Bigtable Data with Replication, Backups, and Security

Protection for Bigtable combines replication for availability, backups for restore points, and exports for portability. Bigtable replication across clusters provides higher availability than a single cluster, additional read throughput, and higher durability in zonal or regional failures. Change streams provide change data capture by capturing table changes as they happen, where data flows from the table into the change stream, then to a reader such as Dataflow, then to downstream analytics, audits, or archiving without repeated scans.

The Bigtable service handles OS patching, node recovery, node repair, storage compaction monitoring, and SSL certificate rotation. Authorization is integrated into Identity and Access Management (IAM) with granular roles for users and service accounts. Bigtable automatically encrypts all data at rest and in transit with no option to disable it, and customer-managed encryption keys (CMEK) can control keys at rest. VPC Service Controls controls access from outside approved networks, and authorized views expose a subset of table data without granting table permission. Bigtable does not support row-level, column-level, or cell-level security.

2.4Choose Relational and Document Databases for Transactions

Transactional managed services handle live operational reads and writes where availability, recovery, and latency matter more than bulk analytics. Cloud SQL is a fully managed relational service offered as Cloud SQL for MySQL, Cloud SQL for PostgreSQL, and Cloud SQL for SQL Server, and it supports serverless GraphQL APIs through Firebase Data Connect. AlloyDB for PostgreSQL is a PostgreSQL-compatible database for demanding workloads, including hybrid transactional and analytical processing (HTAP) such as real-time inventory and live customer profiles on the same live data.

Cloud Spanner is a managed relational database for high availability and global scale that replicates data across multiple zones in multiple regions through multi-region configurations. Spanner provides deletion protection against accidental deletion, scheduled backups with cross-region copies against regional loss, and point-in-time recovery (PITR) for up to seven days against logical corruption. Firestore is a fully managed serverless document database that stores semi-structured documents where fields can vary, so traffic flows directly from clients to documents without provisioning servers. Memorystore for Valkey and Redis holds hot keys, sessions, counters, and cached results in memory, so repeated access flows from the application to Memorystore first and only falls through to the persistent system of record on a miss.

AlloyDB clusters provide high availability with automatic failover by default, where the primary uses redundant nodes in two zones with one active node and one standby ready to take over. BigQuery AlloyDB federation sends a query from BigQuery directly to live AlloyDB rows in place, which eliminates change data capture latency and overhead.

2.5Combine Warehouse and Data Lake with Federated Queries

The lakehouse pattern combines BigQuery as the warehouse with Cloud Storage as the object store. BigQuery is the serverless analytical warehouse that stores datasets, tables, and views with automatic replication across locations for high availability. A dataset is the top-level container for tables and views, and storage supports ACID properties. Centralized administration covers data and compute through jobs that load, export, query, or copy data, and through reservations that switch between on-demand and capacity-based pricing.

Data enters BigQuery through the BigQuery Data Transfer Service on a schedule, streaming ingestion with the Storage Write API, and batch loading from local files or Cloud Storage in Avro, Parquet, ORC, CSV, JSON, Datastore export, and Firestore export formats. Once stored, data supports business intelligence, ad hoc analysis, geospatial analytics, and machine learning with ANSI SQL, nested fields, BigQuery ML, and BigQuery Studio notebooks. Cloud Storage holds diverse objects accessible inside and outside Google Cloud, where regional buckets replicate synchronously across zones and dual-region or multi-region buckets store redundantly in at least two locations with synchronous metadata writes and asynchronous data replication.

External tables and federated queries let BigQuery query data where it lives instead of loading every byte. A query starts in the BigQuery engine and reads source data from Cloud Storage, Bigtable, Spanner, or Google Sheets, then returns results without a copy step. Data flows from Cloud Storage to BigQuery managed storage when repeated high-performance querying is needed, while queries flow from BigQuery to external sources when avoiding duplication matters more. Access combines IAM at bucket and project levels with access control lists (ACLs) for exceptions, and Sensitive Data Protection can discover and de-identify sensitive data before warehouse queries.

Planning for storage costs and performance means placing data where it is read most cheaply and quickly while meeting availability, recovery, and budget goals. In Google Cloud, Cloud Storage (opens in a new tab) cost follows access frequency and location, while BigQuery cost follows bytes scanned or reserved compute because storage and compute scale independently. An optimal design colocates hot data with compute, tiers or deletes cold data automatically, and replicates only essential data across regions.

3.1Balance Cost and Performance in Analytical Databases

Decoupled storage and compute in BigQuery means data storage in Colossus is independent from query execution in Dremel with resources scheduled by Borg. Data flows from Colossus storage into Dremel execution slots over the petabit network, so adding stored bytes does not require adding query servers. Storage resources are allocated as data is consumed and deallocated when data is removed or tables are dropped, which makes cost follow actual usage. Oracle and Exadata follow the opposite model, where capacity must be pre-provisioned and expanded through segments, datafiles, and tablespaces.

Columnar storage in Capacitor, a compressed columnar format in Colossus, lets BigQuery operate directly on compressed data without full decompression and apply automatic storage optimization. While columns are encoded, BigQuery persists statistics and later uses those statistics to compile optimal plans and choose runtime algorithms. Each query uses some number of slots that include CPU and RAM, with in-memory execution, multilevel Dremel trees, Jupiter networking at 1 petabit per second bisection bandwidth, and autoscaling to handle petabyte queries.

Slots determine billing and capacity planning because slots measure computational throughput. By default, projects are limited to 2,000 slots.

Separation of execution lets teams query datasets in different projects without affecting each other by separating slot quotas and billing from the hosting project. Because storage and compute bills are independent, growing stored data does not force purchase of more compute unless concurrency or speed requires it. Partitioning and clustering prune scanned data to improve both speed and cost, and deallocation when partitions age out directly lowers the storage bill.

3.2Align Cloud Storage Access Frequency to Cost

Aligning access frequency to cost means matching how often objects are read and written to the right storage class and lifecycle behavior. Cloud Storage charges differently by class for stored bytes and operations, so frequently accessed data belongs in Standard storage or Nearline storage, while rarely accessed data belongs in colder classes. A Spike in Class A or B operations on Coldline or Archive data finding from Storage Intelligence advisor (opens in a new tab) signals a mismatch where cold data is accessed frequently and would be cheaper and faster in Standard or Nearline. Object Lifecycle Management resolves drift by downgrading objects or deleting objects and noncurrent versions, while Autoclass automatically manages class transitions from observed access.

Retrieval and minimum-duration rules trade cheaper storage for commitments and access fees. Objects in Nearline, Coldline, and Archive incur minimum-duration charges, and retrieving data or metadata adds cost even when access happens early. Deleting, overwriting, or moving an object before its minimum duration expires does not avoid the remaining charge and can add retrieval cost. Frequent access can also cause a Spike in 429 errors where Cloud Storage returns 429 Too Many Requests, so traffic from workers to the bucket must use exponential backoff and gradual ramp-up rather than added replication.

3.3Control Egress and Design Geographic Distribution

Controlling egress means reducing transfer cost and latency when data flows from a bucket in one region to compute in another region or outside Google Cloud. Storage Intelligence advisor reports this as a Spike in cross-region egress, for example when a Compute Engine instance in us-central1 frequently accesses a bucket in us-east1. The same cost applies to disaster recovery copies, where copying backups to different regions incurs egress on every replicated byte. Data flows cheapest when the producer, the bucket, and the consumer sit in the same region and network boundary.

Reduce egress by moving less data across boundaries and colocating what interacts frequently:

• Colocate compute and storage in the same region, using bucket relocation when a bucket is distant from consumers.
• Copy only essential backups to another region for disaster recovery or compliance instead of replicating full datasets.
• Keep large derived datasets on the cloud and process them with cloud workstations rather than downloading them.
• Write temporary output to the worker local file system first, then copy only final frames back to on-premises storage.

Designing distribution balances read locality, replication latency, and survival of regional failure. Buckets fix where object data is permanently stored, and a dual-region bucket can enable turbo replication for faster replication between its two regions. Cross-region copies depend on explicit replication, which adds egress and destination storage cost.

Storage Transfer Service replication is configured in order:

1. Select destination: In the Bucket menu, select the destination bucket for replication.
2. Open replication settings: In Replication settings, click Configure to open cross-bucket replication.
3. Filter scope: Enter an object name prefix to include or exclude objects, then add the prefix to replicate only essential subsets.
4. Set destination class: Select a storage class for replicated objects; if skipped, objects use the destination default class.
5. Confirm: Click Done to create the replication relationship from source to destination.

Lifecycle management of data is the practice of moving each dataset to the lowest-cost storage that still meets access speed, residency, and durability needs. Hot data that is read often stays in high-performance storage, while cooler data moves to cheaper infrequent-access classes or is deleted when business value expires. Total cost includes storage price plus retrieval fees and minimum-duration charges, so frequent reads favor expensive storage and rare reads favor cheap storage with retrieval penalties.

4.1Tier Cloud Storage Objects by Access Frequency

Cloud Storage (opens in a new tab) classes separate objects by how often they are accessed and how long they must be kept. Standard storage is for high-frequency, latency-sensitive reads with no infrequent-access penalty. Nearline, Coldline, and Archive storage lower per-byte price but add fees for retrieving data or metadata and charge for minimum durations even if an object is deleted early. Data flows from the application to the bucket in the selected class, and every later read or early deletion triggers an additional fee from the infrequent-access class back to the requester.

Bucket location controls where Cloud Storage stores and replicates object data. Regional buckets replicate synchronously across multiple zones within one region, so a single-zone outage does not lose data. Dual-region and multi-region buckets store redundantly in at least two geographic locations, where metadata is written synchronously and object data is replicated asynchronously. Dual-region buckets can use turbo replication with a recovery point objective (RPO) of 15 minutes. Cloud Storage stores or replicates data within the specified bucket region to support residency, so choose regional for single-region residency and lower cost and dual-region or multi-region when cross-location availability justifies higher cost.

4.2Automate Tier Transitions with Object Lifecycle Management

Object Lifecycle Management (OLM) is bucket-level automation that transitions objects between classes or deletes them when conditions are met. A bucket holds a lifecycle configuration made of rules, and each rule pairs a condition such as age or storage class with an action. When object metadata satisfies the condition, Cloud Storage automatically applies the action in that bucket. Best practice is to enable lifecycle management rather than relying on manual cleanup, and to track changes with Cloud Asset Inventory and object versioning.

The SetStorageClass action changes the class of less-often accessed objects to reduce cost while keeping the data, and the Delete action removes objects for cost, hygiene, and retention limits. With a default class, the owner chooses the initial class and OLM transitions objects as they age. With Autoclass enabled, Cloud Storage automatically manages transitions. Choose the deletion tool by scale and selectivity:

• OLM deletion: Use when objects should expire by age or class without manual work.
• gcloud storage rm --recursive: Use for prefix-based cleanup under a path.
• Multi-object delete: Use for up to 1,000 objects in one programmatic request.
• storage batch operations: Use for millions or billions of objects with tracking at scale.

For versioned buckets, deletion rules should use the isLive condition set to False so the rule targets noncurrent versions while preserving the live version. The lifecycle policy depends on the bucket, and the bucket depends on correct IAM permissions at bucket and project levels.

4.3Enforce Retention with Holds, Versioning, and Bucket Lock

Retention and deletion protection keep automated tiering from violating compliance or destroying data that must be preserved. Tiered storage still carries the duty to retain personal information only as long as necessary and to prevent erasure during a required retention period. Locked retention policies, holds, and soft deletion sit between the lifecycle engine and the stored object to block premature deletes.

• Retention policy with Bucket Lock: The retention policy prevents deletion or replacement until the period ends, and Bucket Lock makes that policy permanent for compliance.
• Soft Delete: Soft Delete preserves deleted and overwritten objects for a specified period so accidental deletion can be recovered.
• Object holds: Object holds on specific objects prevent deletion or update until explicitly released, including temporary holds and event-based holds with optional default holds on new objects.
• Object versioning: Object versioning preserves prior versions as noncurrent objects so system changes remain trackable and recoverable.

Use locked retention when law requires provable immutability, and use Soft Delete and holds when operational safety requires recovery from human error. A bucket retention policy must be unlocked to be removed, while a locked policy is permanent for the duration, and object-level retention allows different expiration times for different regulated records. Storage Transfer Service moves data from external sources into Cloud Storage, after which destination lifecycle, location, and retention controls determine long-term cost and compliance.

4.4Expire BigQuery Data and Automate Backups at Scale

BigQuery expiration is automatic lifecycle control that deletes data when it is no longer needed for active use. Dataset-level default expiration is the fallback rule that applies to new tables created in a dataset, so every table without its own expiration inherits the dataset lifetime. Table-level expiration is an explicit lifetime on an individual table that overrides the dataset default, which allows critical tables to be retained longer and temporary tables to be removed sooner. Partition-level expiration controls how long individual partitions remain in active storage before they are dropped, while the table definition itself remains.

Partition expiration is designed for time-series and event data where recent partitions are queried often and older partitions become inactive. Use partition expiration to keep a table continuously available for new data while trimming old partitions to control cost and storage limits. For data that must be kept longer, use BigQuery snapshots inside BigQuery or data exports to Cloud Storage as lower-cost tiers. A snapshot keeps the backup inside the BigQuery boundary for direct restore, while an export moves table data to the Cloud Storage boundary for long-term retention and cross-regional recovery.

Scalable backup automation is the Cloud Run-based framework that enforces table-level and fallback policies across folders, projects, datasets, and tables. A table-level policy defined by data owners applies first, and the fallback policy defined by the governance officer applies only when a table has no defined policy. The automated run moves data through an ordered workflow where each service depends on the previous service:

1. Schedule trigger: Cloud Scheduler sends a Pub/Sub message to the dispatcher on a cron schedule with included and excluded scope.
2. Discovery: The dispatcher on Cloud Run calls the BigQuery API to list tables in scope.
3. Fan-out: The dispatcher submits one Pub/Sub message per table to the configurator.
4. Policy resolution: The configurator computes the effective policy from the table-level policy or the fallback policy.
5. Routing: The configurator submits one request per table to the next service based on the policy.
6. Backup execution: A custom Cloud Run service calls the BigQuery API to create a snapshot or run an export to Cloud Storage.
7. Completion detection: For exports, a Cloud Logging sink listens for job completion to enable async next steps.
8. Result handling: After completion, Pub/Sub triggers the tagger service.
9. State recording: The tagger logs the result and updates backup state in the Cloud Storage metadata layer, which depends on Cloud Storage and Datastore.

Key Facts

• BigQuery stores data in columnar Capacitor format without traditional B-tree indexes and uses partitioning by ingestion time, DATE or TIMESTAMP column, or integer range plus clustering to reduce bytes scanned.
• BigQuery on-demand pricing charges by bytes processed per query, while capacity-based pricing charges by slots reserved in reservations with a default limit of 2,000 slots per project.
• Cloud Bigtable separates durable storage from compute nodes, uses SSD storage for low-latency high-QPS reads and HDD storage for write-intensive batch scans, and routes traffic through standard app profiles or serverless Data Boost app profiles.
• Cloud Storage regional buckets replicate synchronously across zones and dual-region buckets support turbo replication with a 15-minute recovery point objective, while Nearline, Coldline, and Archive add retrieval and minimum-duration charges.
• A lifecycle deletion rule with isLive = False targets only noncurrent object versions, while BigQuery table expiration overrides the dataset default and partition expiration drops old partitions while keeping the table.