Accessing Cloud Storage, Databases and BigQuery

Optimizing data access performance and security means choosing how applications read and write data to lower latency and cost while allowing only trusted identities to reach the data. Performance depends on the connection path, bucket location, and storage class, while security depends on Identity and Access Management (IAM), service accounts, and network perimeters. The handover pattern (opens in a new tab) connects a private computing environment to Google Cloud projects through shared storage protected by those controls.

1.1Handover Pattern for Reading and Writing Data

The handover pattern is an architecture where workloads in a private computing environment or another cloud upload data to shared storage for Google Cloud workloads to consume. Data flows from the private environment to Cloud Storage buckets or Pub/Sub topics, and then from that shared storage to workloads in an application Virtual Private Cloud (VPC) for processing, analytics, and frontend use. Frontend applications in the VPC are exposed to users through Cloud Load Balancing or API Gateway, so virtual machine (VM) instances are not directly reachable from the internet.

Communication with Cloud Storage or Pub/Sub travels over public networks or over private connectivity with VPN, Cloud Interconnect, or Cross-Cloud Interconnect. The choice of connection depends on:

• Expected traffic volume
• Whether the setup is temporary or permanent
• Security and compliance requirements

Private connectivity is preferred for sustained or sensitive transfers to reduce latency and avoid moving high volumes over the public internet, while public paths may fit temporary or low-volume use. The gated ingress variation uses Private Service Connect endpoints for Google APIs, which provide private IP addressing to Cloud Storage, BigQuery, and other Google service APIs over VPN or Interconnect.

1.2Optimizing Performance and Transfer Cost

Performance optimization for data access starts with selecting the transfer path and storage location that match latency, availability, and cost goals. Cloud Interconnect or Cross-Cloud Interconnect reduces latency for large transfers and for access to Private Service Connect endpoints inside the VPC. When evaluating transfer options, assess cost, expected transfer time, and security to balance speed against expense and compliance needs.

Cloud Storage bucket location types control how data is replicated for resilience and speed:

For the bucket used to load an ingestion subsystem, choose a storage class based on retention and access frequency so frequently read data stays performant and infrequently read data stays low-cost. For block and shared-file needs, Google Cloud Hyperdisk allows separate and dynamic provisioning of IOPS and throughput with Hyperdisk Balanced, while Filestore regional instances replicate data synchronously across three zones for configuration, tools, and logs mounted on multiple VMs. Regional Persistent Disk volumes provide synchronous replication across two zones in a region for durable VM-attached storage.

1.3Enforcing Fine-Grained Access with IAM and Service Accounts

Fine-grained access control is enforced with Identity and Access Management (IAM) (opens in a new tab) roles that grant permissions at the bucket, project, folder, or organization level. Cloud Storage supports IAM and access control lists (ACLs), and in most cases IAM is recommended because it centralizes who has which access across buckets and projects. Using IAM policies, access to Cloud Storage buckets and Pub/Sub topics can be restricted to trusted workloads, and Cloud SQL IAM database authentication lets administrators configure the instance and add IAM users as database users so login is monitored through one interface.

Applications authenticate to data sources with service accounts rather than sharing user credentials, and privileged access is managed by placing users in Google Groups and granting roles to the group. This group-based approach keeps policies correct during join, move, and leave events because membership changes update access without rewriting IAM policies. Least privilege is maintained with the IAM recommender, which uses machine learning to recommend removal of unneeded roles and to identify unused service accounts and permissions. Time-bound access uses conditional role bindings and group membership expiration, while organization policy constraints limit where resources can be created to prevent storing data in the wrong region.

1.4Preventing Exfiltration and Protecting Sensitive Data

Data protection for reading and writing combines network perimeters, residency controls, and encryption to keep data inside selected boundaries. VPC Service Controls restricts access to services and reduces exfiltration risk by creating a service perimeter at the project or VPC network level that defines the virtual boundaries from which a service can be accessed. The perimeter can extend to a hybrid environment over an authorized VPN or Cloud Interconnect, and it can restrict Cloud SQL import and export through the Cloud SQL Admin API or Cloud Storage API so data stays in chosen network locations.

Message and object data residency (opens in a new tab) is controlled with Pub/Sub message storage policies and by storing or replicating Cloud Storage data within the specified region. Encryption is automatic for customer content at rest and in transit, with Pub/Sub encrypting messages by default using Google-owned and Google-managed keys. For sensitive data, customer-managed encryption keys (CMEK) add control where Cloud SQL uses customer keys on API calls to access data at rest and Pub/Sub supports CMEK for message encryption. Keys can live in Cloud Key Management Service (Cloud KMS) in a chosen location, in Cloud HSM inside a hardware security module in a chosen region, or outside Google infrastructure with Cloud External Key Manager (Cloud EKM). Data loaded through Cloud Storage that may contain sensitive fields can be discovered, classified, and de-identified with Sensitive Data Protection before further processing.

Managing data operations in relational and NoSQL databases means choosing the right access pattern for each workload and keeping reads, writes, and recovery consistent and scalable. Cloud SQL and Spanner provide fully managed relational operations with full SQL support, while Firestore in Datastore mode provides a schemaless NoSQL document model built for automatic scaling. Applications exchange data with these services through client libraries, SQL statements, and managed export and import paths that move data between the database and Cloud Storage.

2.1Relational Operations with Cloud SQL and Spanner

Relational operations with Cloud SQL and Spanner use standard SQL to create tables, link related data without duplication, and run queries, inserts, updates, and deletes. Cloud SQL is a fully managed relational service offering MySQL, PostgreSQL, and SQL Server engines for online transaction processing (OLTP) workloads. Cloud SQL Enterprise Plus edition adds a 99.99% availability SLA with near-zero downtime maintenance, optimized hardware and software, intelligent data caching for read-intensive workloads, and 35 days of log retention, with advanced disaster recovery for MySQL that supports orchestrated failover and switchback with zero data loss after cross-regional failover.

Spanner is a globally distributed relational database that uses GoogleSQL, an ANSI-compliant dialect formerly called Google Standard SQL. GoogleSQL in Spanner divides operations into statement types that flow from the application through the Spanner client to tables, views, and roles:

• Query statements, also called Data Query Language (DQL), scan tables or expressions and return result rows
• Data Definition Language (DDL) statements create and modify tables, views, and database roles
• Data Manipulation Language (DML) statements insert, update, and delete rows
• Data Access Control statements grant and revoke privileges at the table and column level
• Transaction Control statements manage transactions for data modifications

Spanner applies strong consistency across distributed clusters, so a read after a committed write sees current data. The Spanner commit timestamp records when each row was committed, which lets applications select only rows added or modified since the last full backup for incremental backup.

2.2NoSQL Operations with Firestore in Datastore Mode

NoSQL operations with Firestore in Datastore mode (opens in a new tab), often called Datastore, use a schemaless document model built for automatic scaling and high performance. Datastore organizes data as kinds containing entities, where each entity has properties and a unique key, which maps naturally to objects in object-oriented and scripting languages. Applications access Datastore through multiple clients and a SQL-like query language, and all queries are served by previously built indexes so query performance scales with the size of the result set rather than the size of the dataset.

Datastore is schemaless and does not enforce a uniform set of properties across entities of the same kind. Entities of the same kind can have different properties, and the same property name can hold different value types in different entities, so scaling is achieved by distributing writes automatically and restricting queries to index-supported patterns. Datastore does not support:

• Join operations
• Inequality filtering on multiple properties
• Filtering based on results of a subquery

Applications that need joins and subqueries should use a Firestore Native or MongoDB-compatible API instead. The object mapping across models is:

Datastore provides atomic transactions where operations either all succeed or none occur, strong consistency for all queries, high availability through redundancy across Google data centers, and automatic encryption at rest. These properties fit product catalogs with real-time inventory, user profiles based on past activity, and transactions that require ACID properties such as transferring funds between bank accounts.

2.3Transaction Consistency and Availability

Transaction consistency in Cloud SQL, Spanner, and Firestore centers on atomicity, strong consistency, and controlled recovery from bad writes. Firestore and Datastore guarantee atomic transactions and strongly consistent queries, while Spanner adds Transaction Control statements and point-in-time recovery (PITR) that can restore data from a specific point in time up to 7 days in the past. PITR addresses operator errors or application rollouts that corrupt data by returning the database to a state before the bad write, and the same 7-day window applies to BigQuery snapshot decorators for recovering corrupted tables.

Availability depends on how each service replicates data and handles failure. Cloud SQL supports a high-availability (HA) configuration within a region and cross-region replicas to keep serving reads and writes during zonal or regional failure. Spanner throughput and recovery time depend on configured compute capacity, because the speed at which data can be read from Spanner or written back depends on the number of nodes, which directly affects recovery time objective (RTO) values. For small RTO values, a warm standby Spanner instance can be kept at the minimum nodes needed for storage and read and write throughput.

2.4Backup, Export, and Recovery Operations

Backup and recovery operations move data from the live database to durable storage in Cloud Storage and then back to a new table, dataset, or instance during restore. Managed Airflow, a managed version of Apache Airflow, can schedule these operations with a directed acyclic graph (DAG) that runs daily to copy data to another project, dataset, or table or to export data to Cloud Storage using operators such as:

• BigQueryToCloudStorageOperator
• DatastoreExportOperator
• MySqlToGoogleCloudStorageOperator
• PostgresToGoogleCloudStorageOperator

The backup paths and restore behavior differ by service:

Cloud SQL recovery starts from an automated backup, then binary logging replays changes to reach the desired point in time on a fresh instance. Spanner full export writes the database to Avro files in a Cloud Storage bucket, and a separate template re-imports those files into a new Spanner database, while the Dataflow connector supports more controlled copy-out and copy-back pipelines. BigQuery follows the same staged pattern with long-term storage that drops storage price by 50 percent after a table is unedited for 90 consecutive days, replication to two zones in a single region, and recovery by querying a past snapshot, exporting uncorrupted data to a new table, keeping time-partitioned tables, copying datasets across regions, or reloading original data kept in Cloud Storage.

2.5Choosing the Right Service for Data Operations

Choosing the right service for data operations depends on structure, transaction needs, and query patterns rather than size alone. An optimal storage strategy (opens in a new tab) matches each workload to the service whose consistency and query model fit the access pattern. The decision balances relational joins, transaction guarantees, analytical queries, and blob handling against scaling and operational cost.

• Choose Cloud SQL when the system needs OLTP with full SQL support and relational joins across tables
• Choose Spanner when the system needs globally distributed relational data with strong consistency and disaster recovery (opens in a new tab) across regions
• Choose Firestore in Datastore mode when the system needs highly available structured data at scale with automatic distribution of writes and index-driven reads
• Choose BigQuery when the workload is interactive querying for online analytical processing (OLAP)
• Choose Cloud Storage when the data consists of large immutable blobs such as images and movies

Firestore in Datastore mode is not an effective replacement for relational analytics or blob storage, just as Bigtable is a fit only for workloads that do not require ACID transactions or highly structured data.

Integrating object storage and analytical data warehouses follows a staged flow where data moves from a source system to Cloud Storage and then from Cloud Storage to BigQuery. Cloud Storage is the object storage service that holds staging files as objects in buckets, and BigQuery is the analytical warehouse that stores those files as queryable tables in datasets. Cloud Storage acts as the durable landing zone while BigQuery provides high-performance ingestion and SQL analytics, so choosing the right transfer and load pattern controls latency, cost, and throughput.

3.1Uploading Source Files to Cloud Storage

Uploading source files to Cloud Storage is the process of copying extracted data files from on-premises systems or other clouds into a bucket for staging. Files should first be extracted to a vendor-neutral format, with Apache Avro, ORC, or Parquet preferred because schema information is embedded with the data. If CSV or another delimited format is used, the schema must be supplied separately at load time or through schema auto-detection. After extraction, files are copied to staging storage and then uploaded to Cloud Storage.

The upload method depends on data location, data size, and available network bandwidth:

The Google Cloud CLI supports multi-threaded parallel uploads, resumes after errors, and encrypts traffic in transit. When transferring over the network, latency is minimized by creating the Cloud Storage bucket in the location closest to the data center. If possible, the bucket location should match the location of the BigQuery dataset that will receive the data.

3.2Loading Cloud Storage Data into BigQuery

Loading Cloud Storage data into BigQuery is the batch ingestion step that reads objects from a bucket and writes them into destination tables. The BigQuery Data Transfer Service for Cloud Storage supports CSV, newline-delimited JSON, Avro, Parquet, and ORC, including compressed data supported by BigQuery load jobs. A recurring transfer requires the destination table and its schema to exist before the transfer is created, and all files matching the source URI must share that same schema. Only live Cloud Storage objects can be transferred, and archived versioned objects are not supported.

A controlled schema and data transfer (opens in a new tab) moves data through an ordered sequence:

1. Extract: Export source data to files in Cloud Storage staging format and copy the files to staging storage.
2. Copy: Copy data from the source, such as Amazon S3, to a Cloud Storage bucket using Storage Transfer Service, the Google Cloud CLI, or Cloud Storage client libraries.
3. Load: Load Cloud Storage data into BigQuery using BigQuery Data Transfer Service, the bq command-line tool, or BigQuery client libraries.

For one-time loads, batch load from Cloud Storage is used, while scheduled loads at regular intervals use Cloud Storage transfers in BigQuery Data Transfer Service. The source path and destination table can be parameterized to load date-organized buckets on a schedule. After loading, results can be checked with the open source Data Validation Tool, which compares source and BigQuery target data using aggregations such as count, sum, and average.

3.3Controlling Recurring Ingestion with Write Preferences

Recurring ingestion with write preferences controls whether each scheduled run adds new rows or replaces table contents. A transfer with an APPEND or WRITE_APPEND preference performs an incremental transfer that appends only data changed since the previous successful run. The BigQuery Data Transfer Service detects changes by comparing each file's updated timestamp metadata against the timestamp of the last successful transfer. A transfer with a truncated-transfer preference replaces data instead of appending it.

Incremental behavior follows a clear order where first the service records the last successful run time, then it filters source files for a later updated time, and only then it loads the matching files. For example, a file created at 00:00Z is loaded by the 03:00Z run, the next day's run loads nothing when no updated time is newer, and a new file created later is picked up by the following run. If files are modified while a transfer is running, the service does not guarantee that every file transfers exactly once. Table schema changes between runs also cause the transfer to fail.

3.4Transforming Data with ETL and ELT Patterns

Transforming data with ETL and ELT patterns determines whether business logic runs before BigQuery or inside BigQuery itself. Transformation can run in the source environment, but limited compute capacity and extra transfer time for enriched data can reduce throughput. Transformation on Google Cloud uses a managed processing service that reads from the Cloud Storage bucket and writes directly to BigQuery. ELT instead loads raw files into a BigQuery staging table first and then runs SQL queries that write output into the final production table.

Managed transformation services divide the work by complexity and operating model:

• Cloud Data Fusion graphically builds fully managed ETL and ELT pipelines using preconfigured connectors and transformations
• Dataflow defines and runs complex transformation and enrichment graphs with the Apache Beam model on serverless capacity
• Managed Service for Apache Spark runs Apache Spark and Apache Hadoop clusters as a fully managed service, including connectors that read from Cloud Storage and write to BigQuery
• Third-party partner tools externalize pipeline building when a managed Google service is not used

Data flows from the staging bucket to the transformation service and then from the transformation service to the BigQuery destination table. This separation lets Cloud Storage absorb large, parallel uploads while BigQuery scales ingestion and analytical queries independently, which supports an optimal storage strategy (opens in a new tab) for data analytics (opens in a new tab).

Key Facts

• The handover pattern moves data from a private environment to Cloud Storage buckets or Pub/Sub topics and then to workloads in an application Virtual Private Cloud (VPC), with frontend access exposed through Cloud Load Balancing or API Gateway.
• Dual-region Cloud Storage buckets with turbo replication provide a 15-minute recovery point objective (RPO), while Spanner point-in-time recovery covers up to 7 days and Spanner Backup and Restore retains backups up to 1 year.
• Cloud SQL Enterprise Plus edition provides a 99.99% availability SLA and 35 days of log retention, and BigQuery long-term storage drops storage price by 50 percent after a table is unedited for 90 consecutive days.
• A recurring Cloud Storage transfer to BigQuery defaults to APPEND and detects new files by comparing each file's updated timestamp to the last successful run, and only live objects can be transferred.