Storing Data with BigQuery and Cloud Storage

1.1Analyze Access Patterns and Choose the Storage Engine

Analyzing data access patterns means matching how data is written, read, and retained to the storage layout (opens in a new tab) 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 many clients read or write at the same time. Read-write behavior falls into frequent writes and reads, frequent writes with occasional reads, occasional writes and reads, or occasional writes with frequent reads.

BigQuery stores data in columnar Capacitor format in Colossus and executes queries with Dremel workers, so it scans large datasets and aggregations without traditional indexes. Bigtable fits fast key-based access to a small set of rows, while Spanner or Cloud SQL fit transaction processing that needs strong relational consistency. BigQuery separates storage and compute so workers scan in parallel, but moving an online transaction processing (OLTP) workload into BigQuery performs poorly and that workload belongs in Cloud SQL, Spanner, or Firestore instead.

1.2Design BigQuery Tables with Partitioning, Clustering, and Nesting

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 most common filter, such as event date for daily reports or customer ID range for ID-filtered queries. 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, EXCHANGE PARTITION, SPLIT PARTITION, or converting a nonpartitioned table to a partitioned one, so use sharding when those limits block a design.

BigQuery clustering sorts data on disk by one or more columns that are often filtered or aggregated together, so related values sit together and queries skip unneeded blocks. Use clustering after partitioning for columns used together, especially for strings because partitioning supports only dates and integers. BigQuery nested and repeated columns store records and arrays inside one row, such as orders with line items, which reduces joins and fits columnar storage. BigQuery materialized views periodically cache view results for write-once, read-many reports, and Smart Tuning materialized views return current data and rewrite queries to use the view even when the query names the base table.

1.3Choose Managed Databases for Transactions, Streaming, and Search

Transactional managed services handle live operational reads and writes where latency and recovery matter more than bulk scans. Cloud SQL is fully managed relational service for MySQL, PostgreSQL, and SQL Server, while AlloyDB for PostgreSQL supports demanding hybrid transactional and analytical processing (HTAP) and direct federated queries from BigQuery. Cloud Spanner replicates across zones in multiple regions, with deletion protection, scheduled backups with cross-region copies, and point-in-time recovery (PITR) for up to seven days. Firestore is a serverless document database for flexible semi-structured documents, and Memorystore for Valkey and Redis caches hot keys and sessions in memory so repeated reads hit memory first and fall through to the system of record only on a miss.

Multicloud database options (opens in a new tab) include Cloud Bigtable as the managed NoSQL wide-column service for streaming, time-series, and real-time analytics. Bigtable separates durable storage from compute nodes, so adding nodes improves throughput within minutes. SSD storage supports low-latency high queries-per-second (QPS), while HDD storage fits write-intensive workloads where reads are range scans for batch processing. Traffic flows from producers into Bigtable tables keyed for point reads and range scans, then out to analytics through scans, exports, or change streams to Dataflow.

Standard app profiles use provisioned nodes with single-cluster routing to one cluster or multi-cluster routing to the nearest cluster, while Data Boost app profiles use serverless compute with single-cluster routing only. Use single-cluster routing when strong read-your-write consistency or the in-memory tier is needed, multi-cluster routing for geographic proximity and availability, and Data Boost to isolate heavy scans from serving traffic. The in-memory tier serves eligible point reads from memory and can be enabled only with single-cluster routing. Bigtable automatically encrypts data at rest and in transit, supports customer-managed encryption keys (CMEK) and VPC Service Controls, but does not support row-level, column-level, or cell-level security.

1.4Balance Cost, Performance, Location, and Lifecycle

Balancing cost and performance means placing hot data near compute, tiering cold data automatically, and replicating only essential data. Compute Engine virtual machines (opens in a new tab) attach to Persistent Disk for persistent block storage and to Google Cloud Hyperdisk when input/output operations per second (IOPS) and throughput must be provisioned separately. Zonal volumes stay in one zone, while regional Persistent Disk and Hyperdisk Balanced High Availability replicate synchronously between two zones in the same region. Container storage (opens in a new tab) and parallel file sharing (opens in a new tab) follow the same rule: Filestore regional instances replicate synchronously across three zones for shared files mounted on multiple virtual machines (VMs).

Cloud Storage classes separate objects by access frequency, where Standard is for high-frequency reads with no penalty and Nearline, Coldline, and Archive lower storage price but add retrieval fees and minimum-duration charges. Object Lifecycle Management (OLM) automatically transitions class with SetStorageClass or deletes with Delete when age or class conditions are met, while Autoclass manages transitions from observed access. For versioned buckets, use isLive = False so the rule targets only noncurrent versions. Retention is enforced with retention policies and Bucket Lock for permanent compliance, Soft Delete for recovery from accidental deletes, object holds that block deletion until released, and object versioning that keeps prior versions.

BigQuery expiration deletes data automatically, where dataset default expiration applies to new tables, table expiration overrides the dataset default, and partition expiration drops old partitions while keeping the table. Data flows cheapest when producer, bucket, and consumer sit in the same region; cross-region reads and disaster recovery copies add egress. Regional buckets replicate synchronously across zones, dual-region buckets can use turbo replication with a 15-minute recovery point objective (RPO), and Storage Transfer Service copies selected prefixes to another bucket for controlled disaster recovery.

2.1Partition and Cluster Physical Storage to Reduce Scans

Partitioning and clustering control physical layout in BigQuery (opens in a new tab) 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 share the same blocks. Together they let the optimizer first prune whole partitions and then skip blocks inside the partitions that remain. Storage allocates as data arrives in Colossus and deallocates when partitions are removed, so retention can operate on partitions rather than the whole table.

Choose the partition key that matches the most frequent and selective filter, such as time-unit partitioning on a DATE column for orders by order date, ingestion-time partitioning on arrival time, or integer-range partitioning on customer ID ranges. Clustering works best after partitioning for columns frequently filtered or aggregated together, such as customer ID, region, or status. Slots bundle central processing unit (CPU) and random access memory (RAM) under fair scheduling, and projects are limited by default to 2,000 slots, so uncontrolled full-table scans raise both bytes billed and slot queuing.

2.2Model History with Temporal Tables and Append-Only Loads

Temporal models preserve how business records evolve so facts reference the dimension state true at event time rather than the current overwritten value. In a star schema, a central fact table holds metrics such as order amount plus keys to denormalized dimensions such as customer and region. In a snowflake schema, dimensions are normalized into more tables with more joins. BigQuery supports both, but its native form 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, fed by change data capture (CDC) from source systems. Incremental loads flow 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 logic can deduplicate, apply 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.

2.3Decide Normalization from Transactional Versus Analytical Needs

Deciding normalization is the choice between protecting writes and speeding reads. Migration from Oracle (opens in a new tab) shows transactional systems need normalized schemas with one table per entity linked by primary key to foreign key JOINs, so an update changes one row. Row-oriented online transaction processing (OLTP) engines store rows in blocks, depend on indexes and statistics, and require pre-provisioned capacity through segments and tablespaces. Analytical systems favor denormalized flat wide tables so a query reads pre-joined data in one scan instead of rejoining on every query.

Storage, compute, and cost trade-offs in BigQuery reverse the normalization rule because storage is cheaper than compute and effectively unlimited. Normalized star and especially snowflake models force every analytic query to re-resolve joins across workers, raising slot-time and latency. Flat wide tables with STRUCT and ARRAY consolidate dimension attributes into one enriched fact record, then partitioning prunes segments and clustering skips blocks. Partitioned and clustered flat tables therefore lower both billed bytes under on-demand pricing and slot-hours under capacity pricing with reservations starting at a minimum of 100 slots.

Snowflake migration (opens in a new tab) follows an iterative order:

1. Transfer initial schema unchanged: Move the normalized schema so pipelines keep working.
2. Apply light optimizations: Add partitioning and clustering for top queries.
3. Evolve to nested structures: Convert high-join relationships into STRUCT and ARRAY fields.
4. Validate history and key strategy: Replace updates with daily snapshots and surrogate keys, then retest cost and performance.

2.4Map Billing, Recovery, and Security Requirements

Mapping business requirements translates cost, uptime, and compliance rules into BigQuery storage, compute, and security choices. On-demand pricing charges per tebibyte (TiB) of bytes processed and fits variable ad hoc queries, while capacity-based pricing charges for slots in reservations and fits concurrent reporting with predictable throughput. Streaming inserts and load jobs do not consume reservation capacity. Storage bills per second with no provisioning, and unmodified data stored for more than 90 days costs half as much to store. Labels as key-value pairs on datasets and tables support chargeback because storage, query, and streaming usage bill separately.

Disaster recovery (opens in a new tab) converts recovery time objective (RTO) and recovery point objective (RPO) into storage and recovery designs. RTO is allowed time to restore service, while RPO is maximum age of acceptable data loss. A nightly export to Cloud Storage in another region gives about 24-hour RPO and potentially days of RTO because data must transfer and reload and recovery compute must exist. A regional dataset stores data in one region, while a multi-region dataset stores data in a single region within that large area and does not by itself provide cross-region replication. Time travel queries data from any point in the last seven days even for deleted tables, while snapshots are same-region metadata operations that do not protect against regional loss.

Security maps governance rules to residency, grants, and masking. FedRAMP High requires the Assured Workloads Data Boundary and data residency control SA-9(5), while Moderate can use any High-authorized service inside its boundary. Dataset access can be granted to a Google Account, Google group, Google domain, All Authenticated Users, Project Owners, Viewers, Editors, or an Authorized View. Column-level masking obscures values for groups while still allowing the column, where the policy applies to the column and the rule decides true versus masked value.

3.1Manage Discovery, Access, and Cost Controls

Managing the lake means enforcing unified discovery, access, and cost controls across Cloud Storage and BigQuery from one governance plane. Identity and Access Management (IAM) sets the coarse boundary for who can act, while query-time controls, encryption, catalog tagging, and network perimeters decide what data is visible. Cloud Storage holds raw objects and open-format files, BigQuery provides structured query language (SQL) compute and fine-grained enforcement, and the Lakehouse runtime catalog centralizes metadata so engines share one source of truth.

Least-privilege access gives every user, group, and service account only minimum permissions such as no-access, read-only, or write, and separates permission to run a job from permission to read the data. In Cloud Storage, IAM policies apply first at the bucket and then at managed folders before reaching objects. In BigQuery, IAM applies at datasets and refines to tables, views, and jobs. Virtual Private Cloud Service Controls (VPC Service Controls) adds a network trust boundary around both services, so data cannot move outside the perimeter even when IAM would allow it.

Query-time controls in BigQuery restrict what a query returns after IAM allows the job, so one physical table serves many audiences:

3.2Govern Metadata with Catalogs, Lineage, and Encryption

Policy tagging connects classification metadata to enforcement across policy-compliant lakehouse access (opens in a new tab) with Knowledge Catalog. Knowledge Catalog centralizes discovery, lineage, data-quality checks, data profiles, business glossary, and feature and model metadata. Tags carry sensitivity, encryption, retention, cost, and provider-agreement information, and an automated Tag Engine can apply tags from a BigQuery policy table. Sensitive Data Protection scans BigQuery data for personally identifiable information (PII), financial, and health data and sends results to the catalog as aspects.

Encryption protects lake data at rest by default and in transit, with stronger controls when required. Google default encryption needs no customer action, customer-managed encryption keys (CMEK) through Cloud Key Management Service add control over rotation and destruction, and Cloud External Key Manager keeps keys outside Google infrastructure while analytics still run. Unified monitoring ties decisions to accountability through Cloud Logging for real-time logs, Cloud Monitoring for metrics and alerts, and Cloud Audit Logs for application programming interface (API) trails. Storage cost control matches each dataset to the cheapest class, where Storage Intelligence advisor flags cold data accessed frequently, accumulation above trend, and cross-region egress such as Compute Engine in us-central1 reading a bucket in us-east1.

Central discovery with the borderless Lakehouse (opens in a new tab) lets Spark, Trino, BigQuery, Flink, and Hive query one metadata truth without syncing repositories. Discovery follows a fixed order: first the user submits SQL to an engine, then the engine requests metadata from the catalog, then the catalog validates IAM and returns metadata plus an optional short-lived token, then the engine reads files directly from Cloud Storage and returns results. Organization follows Project.Catalog.Namespace.Table, where a catalog is a regional metastore, a namespace groups tables and enforces IAM, nested sub-namespaces are not supported, and only Iceberg V2 and V3 tables are supported.

3.3Process and Monitor Data Flows Across the Lake

Processing data builds continuous paths where events flow from ingestion through transformation to storage. Data products in a data mesh (opens in a new tab) use Pub/Sub as the durable ingestion layer where publishers write to a topic and subscribers read from subscriptions. A Dataflow job consumes an unbounded stream from a subscription and runs Apache Beam logic across Compute Engine workers, where the same transform can run in batch and streaming over PCollection data. The typical order is fixed: events flow from producers to a Pub/Sub topic, from the subscription to Dataflow workers, and from Dataflow to Bigtable rows or BigQuery tables.

Bigtable serves pipelines as a sink for output rows and as a lookup for per-key state joined inside Dataflow before flowing onward. Throughput quotas for Pub/Sub are project-scoped, so deploying pipelines that share one topic into separate projects gives independent quotas. For batch, extract, transform, and load (ETL) transforms before loading, while extract, load, and transform (ELT) loads raw data into a BigQuery staging table and then transforms with SQL. Scalability limits outside code include external system partitions, file formats where Avro with parallel reads is usually faster than comma-separated values (CSV), and network distance between workers, sources, and sinks.

Monitoring the lake collects logs, metrics, and audit trails to prove access and expose slow queries and stale data. How borderless Lakehouse works (opens in a new tab) spans metadata lookup, authorization, storage reads, and execution, so delay can originate in any stage. Admin Activity audit logs record configuration changes, are enabled by default, and are retained 400 days. Data Access audit logs record data reads and writes, must be explicitly enabled except in BigQuery where they are on by default, and are retained 30 days by default. Data insights for Lakehouse tables (opens in a new tab) and Knowledge Catalog quality checks validate formats, ranges, and distributions, while lineage traces a gold aggregate back through silver to bronze to find upstream delay.

4.1Build Storage, Processing, and Ingestion Tiers

Building a data platform means selecting storage, processing, and governance tools to match cost, performance, freshness, and compliance. Self-service data platform design (opens in a new tab) separates low-cost object storage from high-performance serving. Cloud Storage holds raw, high-throughput, and archival data in open formats decoupled from compute, such as clickstream logs as Parquet files in Iceberg tables. BigQuery consolidates processed results into governed tables for analysis, such as a unified customer profile with denormalized types. AlloyDB for PostgreSQL holds live transactional data queried in place through BigQuery AlloyDB federation, which eliminates change data capture latency.

Managed Service for Apache Spark is the distributed processing engine between raw storage and serving, using Lightning Engine for massive joins and aggregations. Data flows from source systems into Spark for transformation, then from Spark output into BigQuery tables. Location controls determine redundancy: regional buckets replicate synchronously across zones, dual-region and multi-region buckets replicate asynchronously across regions, and BigQuery data is stored synchronously in two zones within its region. Querying over the public internet between clouds can incur high egress and latency, so Cross-Cloud Interconnect provides private connectivity.

Ingestion preserves confidentiality through encryption, private transfer, buffering, and separated keys. Traffic flows from on-premises over Dedicated Interconnect or from another cloud over Cross-Cloud Interconnect into a Virtual Private Cloud protected by VPC Service Controls and deny-by-default firewalls, with credentials from Secret Manager verified before bytes move. The hardened sequence runs in order: first encrypt outside Google Cloud with Tink and Cloud Key Management Service keys, then transfer over private connectivity, then land objects in Cloud Storage and messages in Pub/Sub, then load into BigQuery staging for de-identification. Transformation uses Sensitive Data Protection for discovery and de-identification, where teams choose Dataflow for custom row logic, Cloud Data Fusion for low-code plugins, Spark for massive joins, or BigQuery SQL for load-first transform.

4.2Federate Governance Across Domains and Locations

Building a federated governance model means defining ownership, security, and compliance centrally while letting domains manage their own data products. Discovering and consuming data products (opens in a new tab) depends on knowing where each partition lives, which direction data flows, and whether reads return the latest state. Dataplex Knowledge Catalog sits above BigQuery, Cloud Storage, and BigLake and grants access to trusted users while maintaining logs, so discovery, lineage, quality, and policy apply consistently without moving data into one store. This supports a data mesh where domain teams own products but share vocabulary, aspects, and security principles.

Federated discovery records system scope because scope decides availability during an outage: a zonal system is inaccessible if that zone fails, a regional system remains accessible when at least one zone is accessible, and a cross-regional system functions when at least one region is available. Multicloud open lakehouse patterns (opens in a new tab) compare replication flows:

Change data capture reads the transaction log continuously in order without loss, while differential querying polls by timestamp and cannot detect deletes without a logical delete flag. Migration must be complete, duplicate-free, and ordered. Choose bidirectional replication only when concurrent writes to the same key are rare, and active-active only when every location must read the latest state without lag.

Central policy enforcement defines rules once and applies them automatically through annotate, enforce, verify, and alert stages. AI security controls (opens in a new tab) restrict public access to datasets and buckets, require uniform bucket-level access with IAM as the single plane, block public IPs for AlloyDB, restrict external IPs, and require transport layer security (TLS) 1.2 or higher. Encryption governance requires customer-managed keys and restricts which projects may supply keys. Lifecycle controls enforce retention, locked policies, versioning, and secure disposal, while residency uses allowedRegions to define permitted regions. Controls operate in Audit and Detective modes and report to Security Command Center with evidence from Cloud Asset Inventory and logging.

Key Facts

• BigQuery separates storage in Colossus using Capacitor columnar format from compute in Dremel slots, with a default limit of 2,000 slots per project and reservations starting at 100 slots.
• Partitioning by ingestion time, DATE or TIMESTAMP column, or integer range prunes whole segments, while clustering sorts rows inside segments and is required for string filters.
• Cloud Bigtable separates durable storage from compute nodes, uses SSD for low-latency high-QPS reads and HDD for write-intensive batch scans, and enables the in-memory tier only with single-cluster routing.
• Cloud Storage regional buckets replicate synchronously across zones, dual-region buckets support turbo replication with 15-minute RPO, and Nearline, Coldline, and Archive add retrieval and minimum-duration charges.
• Admin Activity audit logs are enabled by default and retained 400 days, while Data Access audit logs must be explicitly enabled except in BigQuery and are retained 30 days by default.