Planning BigQuery Data Warehouse Design

Designing the data model in BigQuery (opens in a new tab) is the combined choice of logical schema shape, history strategy, and physical storage layout. Storage is separated from compute: data is stored compressed in columnar format in Colossus, execution is performed by Dremel workers, and Capacitor is the compressed columnar format that lets queries read only needed columns. The optimizer gathers column statistics during load, persists them during Capacitor encoding, and uses them to prune data and pick efficient runtime algorithms. Compute is allocated as slots, units of CPU and RAM shared by fair scheduling, so reducing bytes scanned lowers both on-demand cost and slot contention.

1.1Partitioning and Clustering to Reduce Scans and Slots

Partitioning and clustering control physical layout so queries scan fewer bytes and hold slots for less time. Partitioning divides a large table into independent segments by a partition key, while clustering sorts rows inside those segments so similar values sit together in the same blocks. Together they let the optimizer first prune whole partitions and then skip blocks inside the partitions that remain.

Choose the partition key that matches the most frequent and selective filter, because only queries that filter on that key benefit:

• Time-unit partitioning on a DATE, TIMESTAMP, or DATETIME column for append-only event tables such as orders by order date
• Ingestion-time partitioning on arrival time using automatic pseudocolumns when arrival time is the filter
• Integer-range partitioning on an integer column for numeric ranges such as customer ID ranges

Clustering works best after partitioning for columns frequently used for filtering or aggregation, such as customer ID, region, or status. Because Capacitor operates directly on compressed data without full decompression, colocated values compress better and scan faster across the petabit Jupiter network with in-memory execution. By default Google Cloud projects are limited to 2,000 slots, so uncontrolled full-table scans can queue concurrent work, while pruning lowers both bytes billed and slot time.

Partitioning also supports lifecycle management because storage allocates as data arrives and deallocates when partitions are removed. Older partitions are often static, so retention, export, and deletion can operate on partitions rather than the whole table. When a recent window can still change, the backup must include that full window, such as exporting the current-date partition plus the prior 7 days when returns can update orders within 7 days. Recovery for an append-only table flows from source partitions to a new staging table to Cloud Storage in an ordered workflow:

1. Copy required partitions: Create a copy of new partitions plus any mutable recent window in a new table from a query result.
2. Run export job: Run a BigQuery export job on the new table to Avro, JSON, Parquet, or ORC files in Cloud Storage.
3. Transfer files: Transfer the exported files with gcloud storage to backup storage, accounting for egress charges and export limits.

1.2Temporal Models and Slowly Changing Dimensions with Append-Only History

Temporal models preserve how business records evolve so historical analysis stays accurate after source values change. Facts continue to reference the dimension state that was true at the time of the event, rather than the current overwritten value. The design combines a logical schema for facts and dimensions, an append-only strategy for history with change data capture (CDC), incremental loads through staging, and a time-optimized physical layout.

Logical models define where metrics live and where changing descriptions live. In a star schema, a central fact table holds metrics such as order amount, discount, and quantity plus keys to denormalized dimensions such as customer, supplier, and region. In a snowflake schema, the same fact-to-dimension pattern is used but dimensions are normalized into more tables. BigQuery supports both star and snowflake schemas, but its native representation uses nested and repeated fields that embed dimension attributes inside the fact row and reduce joins.

Append-only history adds new records for changes rather than overwriting older data, which matches how warehouses keep prior states of customer, product, or region queryable. Change data capture collates changes from source systems and feeds them into the warehouse so evolution is captured rather than lost. Warehouses are typically updated with extract, transform, and load (ETL) or extract, load, and transform (ELT) patterns, which provide controlled update paths where recent events can be reproduced from alternative sources.

Incremental loads apply source changes in controlled batches instead of reloading full history. Data flows first from the source system into a staging table in BigQuery using extract and load options, and only after validation from the staging table into the final production table. The staging table isolates raw arrivals so transformation logic can deduplicate arrivals, apply business rules, and append only validated versions. Early pipelines should move the initial schema with light optimizations and test key use cases before evolving toward nested fields and final time-based optimizations.

1.3Denormalization with Nested Fields and Physical Layout

Denormalization for BigQuery is the shift from normalized models built for row storage to flat, columnar-friendly structures that reduce joins and bytes scanned. Keeping a migrated star or snowflake schema as-is avoids rework during initial iterations and still removes index rebuilds, vacuuming, and upgrade downtime. Keeping that as-is model long term preserves technical debt, limits query optimization, and blocks native features such as nested fields, partitioning, and clustering. Long-term design should therefore be based on reads and business needs rather than minimizing redundancy.

Columnar storage reverses the normalization tradeoff because redundant values cost little to store but joins cost much to compute. Row stores save each table row in data blocks, so analytic queries that need a few columns still fetch unneeded columns in the block and rely on star, snowflake, and vault modeling to work around fixed memory and storage. BigQuery stores data by column with no indexes and no fixed storage and memory limits, adds machines to scan and aggregate concurrently, and reads only referenced columns where repeated values compress well. A flat fact record that consolidates employee, location, and customer attributes therefore reads the order and its attributes in one scan instead of joining four tables on every query.

Nested and repeated fields are the BigQuery-native way to represent one-to-many relationships inside one row. Changing to nested fields aligns the schema with the internal Dremel model and Capacitor storage and eliminates expensive joins that dominate cost and latency. Partitioning and clustering improve that layout without forcing a remodel, because partitioning divides data by TIMESTAMP, DATE, or ingestion-time pseudocolumns without changing table structure, and clustering sorts and colocates rows to skip blocks and speed aggregations.

Deciding the degree of data normalization is the choice between normalized schemas that protect writes and denormalized schemas that speed reads. Online transaction processing (OLTP) systems such as Cloud Spanner handle many small concurrent writes and point lookups where correctness matters most, so they enforce relational normalization. Online analytical processing (OLAP) systems such as BigQuery (opens in a new tab) handle read-heavy scans over billions of rows where speed and scan cost matter most, so they favor denormalization. The decision starts with what entities are stored and what queries rank highest by frequency and importance, then iterates after testing with realistic load.

2.1Transactional Versus Analytical Workloads and Schema Shape

Transactional workloads require normalized schemas to maintain write integrity under concurrent updates. Normalization creates one table per entity and links related tables at query time with a primary key to foreign key relationship resolved by JOINs. This structure avoids storing the same fact in multiple places, so an update to a customer, order, or location changes one row instead of many duplicated copies. Row-oriented OLTP engines store data in row format in blocks organized in segments, depend on indexes and optimizer statistics for fast lookups, and require capacity planning because storage is pre-provisioned through segments, datafiles, and tablespaces.

Analytical workloads favor denormalized schemas to optimize read-heavy queries that aggregate large datasets. Denormalization combines related entities into fewer, wider tables so a query reads pre-joined data in one scan instead of joining many tables at query time. This trades extra storage and more expensive updates for faster reads, lower slot consumption, and simpler queries. The tradeoff reverses in BigQuery because storage is much cheaper than compute and effectively infinitely scalable, so redundancy is encouraged when it produces faster queries, including loading full snapshots into new daily partitions to track slowly changing dimensions instead of in-place updates.

2.2Storage, Compute, and Cost Trade-Offs of Normalized and Denormalized Designs

Storage, compute, and financial implications in BigQuery are governed by how schema shape changes bytes stored, slots consumed, and billing. BigQuery separates storage in Colossus using the Capacitor columnar format from compute allocated per query as slots that bundle CPU and RAM. Datasets are the highest-level abstraction for organizing access, with schemas, labels, partitioning, and clustering used to organize tables and prune scans. Coordinated optimizations include in-memory execution, multilevel Dremel tree execution, automatic storage optimization with column statistics, 1 petabit per second Jupiter bisection bandwidth, and autoscaling resource management.

Normalized star and snowflake models preserve joins at the cost of extra compute. A star schema centers metrics with keys to surrounding dimensions, while a snowflake schema further normalizes dimensions into more tables and more joins. The dependency runs from the fact table to the dimension tables at query time, so every analytic query must re-resolve those joins across workers. Keeping that model beyond initial migration leaves technical debt and forces optimizations to be redone if the schema later changes.

Flat wide tables with nested and repeated fields consolidate dimension attributes into one enriched fact record, so co-accessed parent and child values are scanned together without joins. Partitioning divides the table into segments without changing table structure, and queries over smaller partitions scan only a subset of data and reduce billed bytes. Clustering sorts data into optimally sized blocks, eliminates scans of unnecessary data, and calculates aggregates quicker because similar values sit together. Denormalized tables therefore use fewer join stages, lower slot-time per query, and lower latency under concurrency.

Financial impact comes from bytes read per TiB under on-demand pricing, slot-hour consumption under capacity pricing, and bytes retained for storage. Partitioned and clustered flat tables lower both compute dimensions by scanning fewer bytes and holding slots for less time. Capacity is managed with reservations, buckets of slots assigned to projects, folders, or organizations, starting at a minimum of 100 slots with flex, monthly, or yearly commitments, where queries in one reservation can use idle slots from others. Slot pressure is observed through Cloud Monitoring, audit logs, INFORMATION_SCHEMA data, and system tables, where high wait times plus high utilization signal that more slots are needed, as when 4,000 slots running 100 medium-complexity queries begin to queue.

2.3Nested and Repeated Fields Using STRUCT and ARRAY

Semi-normalized design preserves relational logic inside a single BigQuery table by using nested and repeated fields instead of many joined tables. STRUCT groups related columns into a single parent field, and ARRAY allows that field to hold multiple values per row. Together STRUCT and ARRAY preserve the one-to-many relationship that a normalized design would express with a primary key, a foreign key, and a JOIN. Because this layout matches the Dremel model and Capacitor storage and operates directly on compressed data, reading a nested STRUCT avoids shuffling separate tables across workers.

Conversion from flat relational models groups dimension attributes into STRUCTs while leaving unique fact attributes at the top level. In a documented order example, CustomerID, FName, and LName nest into a Customer struct, salesperson fields nest into a Salesperson struct, and LocationID, city, state, and zip code nest into a Location struct, while OrderNum and amount remain top-level because they are unique to the order fact. Marking Customer as repeated allows one order row to hold an ARRAY of STRUCT values for a primary and secondary customer without a bridge table.

The migration path toward nesting follows an iterative order:

1. Transfer initial schema unchanged: Move the existing normalized schema so pipelines and tools keep working.
2. Apply light optimizations: Add partitioning and clustering for highest-ranked queries and validate with key use cases.
3. Evolve to nested structures: Convert high-join fact-to-dimension relationships into STRUCT and ARRAY fields.
4. Validate history and key strategy: Replace update-heavy logic with daily snapshots and surrogate keys built with row_number and partition by, then retest performance and cost.

Choose nesting when child entities are almost always queried with their parent, when join-heavy queries dominate slot usage, and when the hierarchy is stable enough to manage as one table. Nesting does not always improve performance, because highly selective queries that need only a small dimension slice, frequently updated dimensions that force rewriting large rows, and limits on nested columns can erase join savings. Keep star or snowflake form during initial migration, then evolve high-value use cases once key queries are tested.

Mapping business requirements translates financial objectives, uptime promises, and compliance rules into concrete data warehousing requirements for BigQuery storage, compute, and security. Compute is billed either by bytes scanned per query or by reserved capacity, while storage is billed per dataset with discounts for long-lived data. Recovery time objective (RTO) is the allowed time to restore service, while recovery point objective (RPO) is the maximum age of acceptable data loss. Governance with labels, perimeters, and masking then assigns cost and restricts access to the team that should see it.

3.1Query and Storage Billing Models with Reservations and Labels

Query and storage billing models separate BigQuery compute charges from storage charges so each can match workload predictability. On-demand pricing charges per TiB of bytes processed, so cost rises and falls with data scanned by each query. Capacity-based pricing charges for slots purchased as reservations with a minimum of 100 slots, where slots used for streaming inserts and loads do not count against reservation capacity. By default projects are limited to 2,000 slots.

Reservations are pools of slots that isolate capacity by team, project, or workload type. A reservation lives in one administration project and can be shared across projects, so concurrent queries from different projects draw from their assigned pool. BigQuery allocates resources dynamically by query type and complexity with an eventual fairness scheduler, so brief uneven sharing is corrected over time but sustained high utilization signals under-provisioning. Size reservations from observed use with yearly or three-year commitments in the Google Cloud console or with the bq command-line tool:

1. Monitor slot allocation: Collect slot utilization, bytes scanned, and bytes stored with Cloud Monitoring and system tables across jobs and reservations.
2. Analyze consumption patterns: Analyze audit logs in BigQuery and visualize slot use across queries and projects with Data Studio, Looker, or Tableau.
3. Adjust reservation size: Add slots when execution plans show high wait times and dashboards show sustained high use.

The dataset storage model allocates storage dynamically as data is consumed and releases it as data is removed, with per-second billing and no provisioning. Unmodified data stored for more than 90 days automatically costs half as much to store, so stable historical partitions become cheaper to retain. Labels, which are key-value pairs on datasets, tables, and views, support chargeback by cost center because storage, query, and streaming-insert usage are billed separately.

3.2High Availability and Disaster Recovery from RTO and RPO

High availability and disaster recovery architectures convert allowed downtime and data loss into BigQuery storage and recovery designs. RTO and RPO are driven by warehouse size, availability needs, and nature of the data. Size matters because warehouses often hold terabytes to petabytes, so restoring that volume affects whether RTO can be met. Availability needs depend on whether the failure is machine, zonal, or regional, because regional loss is longer and more expensive to recover. Nature of the data matters because warehouse data is often historic, static, and append-only, so RTO can sometimes be met by restoring only new appends.

A nightly backup pattern shows how the objectives are calculated. If BigQuery data in us-east4 is exported nightly with extract jobs to Cloud Storage in Archive Storage class in us-central1, RPO is 24 hours because the last backup can be up to 24 hours old. RTO is potentially days, because data must be transferred to the recovery region if needed and then restored from Cloud Storage into BigQuery in us-central1. Additional delay occurs unless redundant slots were purchased in advance in the recovery region, since restore and backfill queries need compute before they can run.

A BigQuery dataset location defines failure isolation and does not by itself create a disaster recovery copy. A regional location stores data in one specific region such as us-central1, while a multi-region location covers a large area such as United States (US) or European Union (EU) that contains two or more places. Selection of EU multi-region, for example, excludes certain places such as Zurich and London from that configuration.

Cross-region protection depends on creating a usable data copy and usable compute in a second failure domain. Recovery flows from primary BigQuery tables to Cloud Storage backup objects to restored BigQuery tables in the recovery region, with an added transfer step when the restore region differs from the backup region. For batch workloads with strict deadlines, continuous dual-region execution gives stronger RTO than backup-and-restore alone. Running two separate instances of the entire pipeline in two regions, for example us-west1 and us-east4, creates two independent failure domains, where both pipelines write results independently and exactly-once delivery is reconciled by picking the first finisher, for example by notifying a Pub/Sub topic on success, or by overwriting the result and re-versioning the Cloud Storage object.

BigQuery time travel provides self-service recovery from accidental deletion or corruption within a short window. The capability comes from multiversion concurrency control architecture, which retains history so data can be queried from any point in time over the last seven days, even on tables that have been deleted. BigQuery table snapshots extend protection against logical errors beyond seven days without copying bytes at creation time, because a snapshot is purely a metadata operation within the same region. Snapshots do not increase durability because they remain in the same region and do not protect against regional loss.

Resilience cost must match real-time and batch service-level agreements (SLAs). In real-time analytics, log events flow from endpoints to Pub/Sub to a streaming Dataflow pipeline to BigQuery through the Storage Write API, then serve dashboards where ingestion and query latency matter. That ingestion path is already resilient because Pub/Sub subscriptions can buffer unacknowledged messages for up to seven days and Dataflow can resume backed-up events with low RPO, so full dual-region redundancy is often not worth the cost and nightly extract with 24-hour RPO is accepted. In batch regulatory reporting, the SLA is a fixed nightly deadline where missing the deadline carries high compliance cost, so extra cost of dual-region pipelines is justified to achieve low RTO.

3.3Security, Privacy, and FedRAMP Compliance Controls

Security, privacy, and regulatory compliance controls translate governance policies into residency boundaries, access grants, masking rules, and encryption settings. The FedRAMP authorization path is the Rev. 5 Agency Authorization to Operate (ATO), completed jointly by the customer, Google, and a third-party assessment organization (3PAO). The customer must satisfy continuous monitoring for provisioned infrastructure, while Google provides compliance documentation under a non-disclosure agreement (NDA) through the sales team or, for federal agencies, through the FedRAMP package access request form.

FedRAMP Moderate and FedRAMP High share the same NIST SP 800-53 control family but differ in scope and enforcement:

Data residency enforcement for FedRAMP is a High control baseline requirement mandated by control SA-9(5), not a Moderate requirement. The technical implementation is the Assured Workloads Data Boundary for FedRAMP High, which constrains where protected data is stored and processed. When a warehouse such as BigQuery must meet High obligations, the project and data must operate inside that boundary, which determines eligible services, regions, and APIs.

Shared responsibility is documented in the Customer Responsibility Matrix (CRM) and System Security Plan (SSP). The Customer Responsibility Matrix describes customer responsibilities for implementing High baseline controls, while the System Security Plan describes the authorization boundary, system architecture, and how Google implements applicable controls. Service-specific configuration is guided by control mapping guides (CMGs) and High implementation guides for services including BigQuery, Cloud Logging, Compute Engine, Identity and Access Management (IAM), Cloud Key Management Service, Google Kubernetes Engine (GKE), and Cloud Storage. Those configurations are not enforced by default and must be managed by the customer.

Dataset access maps governance rules to explicit grants. Access can be granted to an individual Google Account by user email, to all members of a Google group by group email, to all users and groups in a Google domain, and to all account holders through All Authenticated Users, which makes the dataset public. Access can also be granted to Project Owners, Project Viewers, Project Editors, and to an Authorized View that receives access on behalf of its users. Column-level masking extends dataset access by obscuring column data for groups of users while still allowing access to the column itself, where the policy applies to the column and the masking rule determines whether the user receives the true or masked value.

Google infrastructure controls provide prevention, detection, and auditability that warehouse controls depend on. Prevention includes strong authentication where production jobs prove identity with cryptographic credentials and communicate with application layer transport security (ALTS), context-aware access for zero-trust decisions based on identity, location, and device, and Binary Authorization for Borg to ensure production software is reviewed before deployment. Auditability flows from infrastructure logs to customer-visible Cloud Audit Logs and Access Transparency for Google personnel access, while incident response combines expert teams with automated controls using machine learning, threat intelligence, and offensive security exercises.

Defining architecture to support data access patterns matches analytical, operational, and real-time workloads to the right storage engine, sharing topology, and compute boundary. BigQuery (opens in a new tab) separates storage and compute so each scales independently based on demand. When a query runs, the query engine distributes work in parallel across multiple workers, the workers scan relevant tables in storage, process the query completely in memory, and gather results over a petabit network. Operational workloads emphasize transactional data, high input/output operations per second (IOPS), simultaneous clients, and predictable throughput, while real-time workloads add low-latency caching, scratch space, and fast failover.

4.1Analytical Storage with Partitioning, Clustering, and Materialized Views

Analytical storage in BigQuery defines how data is physically stored, pruned, and precomputed for complex queries. Storage uses the efficient columnar Capacitor format optimized for analytics, allocates capacity when data is loaded and releases it when data is removed, and bills compute and storage separately with no provisioning. All data is automatically encrypted before it is written to disk, and the service is designed for 99.999999999% annual durability by replicating data across multiple availability zones.

Table resources have different storage behavior:

• Standard tables contain structured data with a schema where every column has a data type, stored in columnar format.
• Table clones are lightweight, writable copies where BigQuery only stores the delta between the clone and its base table.
• Table snapshots are read-only, point-in-time copies for restore where BigQuery only stores the delta between the snapshot and its base table.
• Materialized views are precomputed views that periodically cache view query results in BigQuery storage.

Partitioned tables divide a large table so queries with predicate filters on the partitioning column scan less data, which lowers cost on on-demand pricing and reduces slot consumption on capacity pricing. Partition choice should follow historical query patterns, so the column most often used in date or integer filters becomes the partition key. BigQuery implements ingestion-time partitioning by arrival time, column partitioning by TIMESTAMP or DATE column, and integer-range partitioning by integer column. BigQuery does not support hierarchical partitioning, and it does not support EXCHANGE PARTITION, SPLIT PARTITION, or converting a nonpartitioned table to a partitioned one. If partitioning limits affect the design, consider sharding instead.

Clustered organization stores rows ordered on disk based on columns often accessed together and is the recommended replacement for explicit indexes. Clustering helps when clustered columns are used as filter or aggregation predicates, especially for string columns and equivalents of list-partitioned tables from other warehouses. BigQuery stores data in columnar format, so no indexes are needed for batch analytics, but Search Indexes and Vector Indexes support specialized lookups and unenforced foreign keys help query optimization. When the workload needs single small row-based access, Bigtable can be a better alternative, while transaction processing with strong consistency belongs in Spanner or Cloud SQL.

Precomputed results with materialized views improve report rendering time for write-once, read-many reports. BigQuery leverages precomputed results and, whenever possible, reads only delta changes from the base table to compute up-to-date results. Smart Tuning materialized views always return current data and provide automatic query rewrite even when the query refers to the base table. This differs from standard views, which allow custom views but do not support data manipulation language (DML) statements. Caching in Looker Studio or other business intelligence tools can further improve performance by eliminating reruns.

Slot capacity, monitoring, and BI Engine acceleration control how compute is obtained and sped up. Slots are units of query compute capacity allocated by query type and complexity under an eventual fairness scheduler, where some queries may briefly receive a higher share before balance is corrected. On-demand pricing charges for bytes processed and suits variable volume where pruning controls cost, while capacity-based pricing purchases reservations with a minimum of 100 slots through yearly or three-year commitments for enterprise reporting and ELT workloads. By default projects are limited to 2,000 slots. Historical patterns guide sizing through Cloud Monitoring, audit logs visualized in Looker Studio, Looker, or Tableau, INFORMATION_SCHEMA.JOBS history for the last 6 months, and system tables for slot use. BigQuery BI Engine accelerates dashboards by running fast in-memory analysis backed by BI reservations and integrates with Looker Studio, Looker, Google Sheets, Tableau, and Power BI.

4.2Secure Multi-Tenant Access with Identity, Perimeters, and Governed Sharing

Secure multi-tenant architectures let separate teams produce and consume shared data without seeing restricted data or moving data outside approved boundaries. The design combines an identity layer that decides who can access what, a perimeter layer that decides where data can move, and a sharing topology that moves data from producers to consumers through governed storage. In Google Cloud, Identity and Access Management (IAM), Virtual Private Cloud (VPC) Service Controls, and shared services such as Cloud Storage and Pub/Sub form those three layers.

Identity controls and perimeter trust boundaries decide which identities can act and where requests can travel. Cloud Storage supports IAM and access control lists (ACLs), where IAM at bucket and project levels is recommended in most cases. Cloud SQL IAM database authentication adds IAM users as database users so logins use the same IAM identity that governs cloud resources. Organizational policy constraints combined with IAM policies can enable or disable services for sets of users, which prevents storing data in the wrong region. VPC Service Controls create service perimeters at the project or VPC network level, where traffic from a trusted workload inside the perimeter can reach Cloud Storage, BigQuery, and other APIs, while requests from outside are denied even when the caller has IAM permission. Perimeters can extend to hybrid environments over VPN or Cloud Interconnect and restrict Cloud SQL import and export so data stays within selected locations. Access Approval requires explicit customer approval before Google personnel access data.

Encryption ownership and sensitive-data protection define who controls decryption and how fields are cleaned before sharing. Shared data starts with automatic Google-owned encryption for content at rest and in transit with no customer action. Pub/Sub encrypts messages by default at the application layer and supports customer-managed encryption keys (CMEK), while Cloud SQL also supports CMEK where the customer key is used on every API call that accesses data. Cloud Key Management Service (Cloud KMS) stores keys in the location set at creation, Cloud HSM stores keys inside a hardware module in a chosen region, and Cloud External Key Manager (Cloud EKM) stores keys outside Google infrastructure. Data loaded through Cloud Storage that might include sensitive fields can be discovered, classified, and de-identified with Sensitive Data Protection before sharing.

Governed handover connects a private computing environment to Google Cloud projects through intermediate storage. Data flows from workloads in the private environment to shared Cloud Storage buckets or Pub/Sub queues, then from those buckets or queues to processing, analytics, and frontend workloads in an application VPC. IAM policies on buckets and topics restrict access to trusted workloads, while VPC Service Controls minimize exfiltration risk. The handover moves in order:

1. Upload: Producer workloads upload data in bulk or increments to Cloud Storage buckets or Pub/Sub queues.
2. Processing: Consumer workloads in the application VPC read from buckets or queues and process data in streaming or batch fashion.
3. Exposure: Frontend applications expose results through Cloud Load Balancing or API Gateway, so VM instances are not directly reachable from the internet.

Connectivity can travel over public networks or private connectivity using VPN, Cloud Interconnect, or Cross-Cloud Interconnect. A gated-ingress variation uses Private Service Connect endpoints for Google APIs inside the VPC, which provides private IP addressing to Cloud Storage, BigQuery, and other APIs over the hybrid connection.

Storage locations determine replication and residency tradeoffs. Region selection balances service availability, machine types, end-user latency, resource and transfer cost, regulatory requirements, and sustainability such as low-carbon regions. Cloud Storage regional buckets replicate synchronously across zones for low-latency access with zone resilience, dual-region buckets write metadata synchronously and replicate data asynchronously with turbo replication for 15-minute recovery point objective, and multi-region buckets store redundantly in at least two locations with asynchronous replication for broad geographic reads. Pub/Sub message storage policies restrict where message data is stored, Cloud SQL cross-region read replicas copy the primary in near real time and must sit in a compliant region to preserve residency after promotion, Filestore regional instances replicate synchronously across three zones, regional Persistent Disk volumes replicate synchronously across two zones, and Hyperdisk Balanced allows separate provisioning of IOPS and throughput.

Shared compute boundaries separate flexibility from management effort. Compute Engine virtual machines and Google Kubernetes Engine (GKE) containers provide more configuration flexibility but require team management, while Cloud Run serverless containers run on a preconfigured platform where jobs run regionally across zones and survive zone outages with retries and checkpointing. Aggregate availability falls as tiers are chained, calculated as tier1 availability x tier2 availability x tierN availability, while parallelized services run independently with higher combined availability. Managed instance groups recreate failed VMs, load balancing distributes traffic, high-availability databases span zones, and managed multi-regional services such as Spanner and Cloud Storage provide built-in redundancy.

4.3Operational and Real-Time Storage Engines for IOPS and Latency

Operational and real-time storage engines match frequent read-write activity, low-latency needs, and consistency needs to block storage, managed databases, and object storage. The design starts with functional characteristics, security constraints, resilience needs, performance expectations, and cost goals, and it can iterate when a storage feature changes application behavior. Expected patterns include frequent writes and reads, frequent writes with occasional reads, occasional writes and reads, and occasional writes with frequent reads. Granular needs such as file protocol version, expansion to new regions, replication across two locations, lifecycle controls, and separate performance tiers for production and non-production also shape the choice.

Block storage divides data into chunks stored as separate blocks with unique addresses, where applications access data by block address. Block storage is optimized for high-IOPS workloads such as transaction processing and resembles storage area network and directly attached storage. In Google Cloud, block options attach to Compute Engine virtual machines (VMs) and Google Kubernetes Engine (GKE) clusters:

• Persistent Disk provides hard-disk drives and solid-state drives with 10 GiB to 64 TiB per disk and up to 257 TiB per VM.
• Google Cloud Hyperdisk provides fast redundant network storage with configurable performance and dynamic resizing, with 4 GiB to 64 TiB per disk and up to 512 TiB per VM.
• Local SSD provides ephemeral locally attached storage with 375 GiB per disk and up to 12 TiB per VM.

Traffic flows from the VM to the attached disk, and the disk depends on the host for availability. Persistent Disk and Hyperdisk are network storage that can be shared and scaled, while Local SSD sits inside the host for lowest latency but is ephemeral and not shareable. Choose Local SSD only when temporary scratch or cache can be lost, and choose Persistent Disk or Hyperdisk when transactional data must survive host failure.

Low-latency design with Cloud Bigtable centers on a managed database that stays available across zones in a region. A production instance has a minimum of two clusters in unique zones in the region, replication between clusters is automatically managed by Google, data flows from the primary cluster to the other cluster, and incoming traffic can fail over to another cluster when one zone has a problem. Bigtable also supports asynchronous replication across zones and backup flows that preserve schema and data, where a backup saves table schema and data and restores to a new table later. Tables can be exported as Hadoop sequence files to Cloud Storage and imported back into another instance. Storing indexes in random access memory (RAM) is essential, so machines must scale vertically with storage to account for the primary index, while a single node can handle several hundred terabytes or petabytes.

Transactional backup flows with Cloud Spanner preserve operational data outside the live database. Data flows from Spanner through Dataflow to Cloud Storage for durable backup storage. A Dataflow template performs a full export to Avro files in a Cloud Storage bucket and another template re-imports the files into a new Spanner database. For more controlled backups, the Dataflow connector lets code read and write Spanner data inside a pipeline to copy selective data into Cloud Storage. The live database serves operational reads and writes, while Cloud Storage holds the portable copy that can rebuild a new database after corruption or loss.

Object storage classes for read-heavy access define how Cloud Storage serves frequent reads at low cost. Cloud Storage is low-cost, highly durable object storage with no lower or upper limit, where data is stored as objects in a flat hierarchy of buckets, each object has a globally unique ID plus metadata, and applications access data by object ID through REST APIs or client libraries. Autoclass automatically transitions objects to appropriate classes when frequency varies, Standard serves frequently accessed analytics, data lakes, websites, streaming, and mobile apps with Cloud CDN caching and Rapid Cache for read-heavy workloads, while colder classes lower cost for less frequent access:

Key Facts

• BigQuery separates storage in Colossus from execution in Dremel and stores data compressed in the columnar Capacitor format, with compute allocated as slots that bundle CPU and RAM.
• Partitioning divides a table by time-unit, ingestion-time, or integer-range keys so filtered queries scan only matching segments, while clustering sorts rows inside those segments to skip blocks and speed aggregations.
• Google Cloud projects are limited by default to 2,000 BigQuery slots, slot reservations start at a minimum of 100 slots, and unmodified data stored for more than 90 days costs half as much to store.
• BigQuery time travel allows querying and restoring data from any point in time over the last seven days, even for deleted tables, while snapshots are same-region metadata operations that do not protect against regional loss.
• A BigQuery multi-region dataset stores data in a single region within that multi-region area and does not provide cross-region replication by itself.