Consider solutions that span multiple database technologies (e.g. federation, exports, hybrid deployments)

Cross-technology data movement is the practice of extracting data from source databases, moving it through a central migration system, and loading it into target databases that may use different engines. The sources and targets do not have to be collocated and can sit on-premises, in Google Cloud, or in other clouds. The data migration process (opens in a new tab) names the sources, targets, and in-transit logic, while hybrid deployments (opens in a new tab) use the same process to bridge environments. The design supports both one-time exports and continuous synchronization between transactional and analytical systems.

1.1Data Migration Process Across Hybrid and Multicloud Environments

The data migration process is the developer-defined unit that names the source databases, the target databases, and any modification logic applied during transit. Data flows from each source to the migration system for extraction, then from the migration system to each target for insertion, with optional transformation in between. One or more processes can be defined and run sequentially or concurrently, so independent databases can move in parallel to reduce total time. Because each source and target can live in a different environment, the same architecture supports hybrid operation, movement between clouds, and consolidation from multiple sites at once.

1.2Change Capture with CDC and Differential Querying

Change capture is how a migration system detects insertions, updates, and deletions in a source database. Log-based change data capture (CDC), such as reading the binary log in MySQL, continuously reads the transaction log that already records every change in correct order. Differential querying instead adds a timestamp, sequence number, or status field to each row and polls for rows changed since the last poll, which adds query load, requires schema changes, and needs special handling for deletes through a logical delete flag or separate delete tables. CDC places the least load on the source because it is built into the database and separate from the query interface, while polling competes with normal queries.

1.3Migration Consistency and Write Access Models

Migration consistency means the target ends in a state equivalent to the intended source state, whether applied through replayed transactions or through backup, export, and restore. A consistent migration meets three related requirements that together prevent missing, repeated, or out-of-order data:

• Complete: all specified data, whether a full database or a defined subset, is actually migrated to the target.
• Duplicate free: each piece of data is migrated once and only once, so no duplicates appear in the target.
• Ordered: changes from the source are applied to the target in the same order they occurred, which preserves transactional meaning.

The write model determines where consistency risk comes from. In an active-passive migration, clients write to the source databases while targets allow only read-only access, so ordering and completeness are the main concerns. In an active-active migration, clients write to both source and target databases during the move, so the same item can change in two places at once and every conflict must be resolved by conflict resolution rules.

1.4Migration Cardinality for Redesign

Migration cardinality describes how many sources map to how many targets, and it lets a move redesign the architecture rather than copy it exactly. A direct mapping uses 1:1 cardinality, where one source moves without reshaping to one target. Other cardinalities change the shape to meet scale, management, or locality needs, with data flowing from the source set through the migration processes to the target set in the defined grouping. Independent source groups can use parallel processes, while dependent groups must be ordered to preserve referential and transactional relationships.

• Consolidation (n:1): data flows from several source databases into a smaller number of targets, often one target, to simplify management or use a more scalable engine.
• Distribution (1:n): data flows from one centralized source into several targets, such as splitting regional data into regional databases.
• Re-distribution (n:m): data flows from several sources into several targets, such as rebalancing uneven shards evenly across new shards.

1.5Deployment Patterns Linking Transactional and Analytical Systems

Deployment patterns define how databases in different locations stay connected, from fully separate to fully synchronized. In a best-in-class design, business-logic processing runs on a transactional primary in one cloud while analytics runs on a different engine in another cloud, with the primary feeding the secondary. Partitioned data leaves databases unconnected and forces the application to write twice, while replication and synchronization automate the flow at different consistency and cost trade-offs.

Choose asynchronous unidirectional replication when a transactional system must feed an analytical system without slowing transactions, and accept replication lag on reads. Choose bidirectional replication only when write-write conflicts on the same keys are rare and a clear resolution rule exists, because manual repair is sometimes required.

Hybrid and multi-cloud database architectures distribute data and processing across more than one environment instead of keeping everything in a single cloud. A hybrid deployment bridges an on-premises private environment with Google Cloud, while multi-cloud integration connects Google Cloud with another provider and with live operational databases. Both patterns use secure private networking, governed federation, and replicated storage to meet data residency rules and to stay available during zone or region outages.

2.1Tiered Hybrid Pattern for On-Premises and Google Cloud

The tiered hybrid pattern is a split where the frontend runs in Google Cloud while the backend stays on-premises. Frontend components are directly exposed to users, are often performance sensitive and stateless, and receive frequent updates, while backend components store and manage data, handle large request and data volumes, and change less often. Client requests flow first to the application frontend hosted in Google Cloud, then data flows from that frontend back to the on-premises backend, ideally through an API gateway. The three tiers are the web frontend and presentation tier, the application tier, and the data access or backend tier, and each tier operates independently but functions together with the others. Putting the tiers into containers separates needs such as scaling requirements and supports phased migration to portable, auto-managed platforms such as Cloud Run (opens in a new tab) or Google Kubernetes Engine (GKE), with Cloud SQL as an example of a Google Cloud-managed database for the backend layer.

2.2Secure Connectivity Across Hybrid and Multi-Cloud Boundaries

Secure connectivity means traffic moves between environments over private, governed paths rather than the public internet. Virtual Private Cloud (VPC) provides a private, secure, isolated network for all Google Cloud resources in the architecture. Cloud Router provides Border Gateway Protocol (BGP) speaker and responder capabilities and works with Cloud Interconnect, Cloud VPN, and Router appliances to create dynamic routes in VPC networks. Cloud NAT lets resources inside the Google Cloud network securely access external cloud data without exposing those resources to the public internet. For Google Cloud to Amazon Web Services (AWS) database access, Cross-Cloud Interconnect (opens in a new tab) establishes a private connection between the clouds to provide reliable performance and minimize data transfer costs. Credential checks add another boundary: Secret Manager securely stores service principal credentials, such as client ID and secret, for borderless authentication, and Cloud Domain Name System (DNS) routing policies can route traffic to an external load balancer in a different region when a passive replica sits in another region.

2.3Data Residency Compliance and Region Selection

Data residency compliance means storing and replicating data only inside the regions or locations required by regulation and organizational policy. Region choice for Google Cloud deployments balances competing factors, since the most cost-efficient region might not have the lowest carbon footprint or the required services. Consider the following factors when choosing a region:

• Availability of Google Cloud services in each region
• Availability of Compute Engine machine types in each region
• End-user latency requirements
• Cost of Google Cloud resources and cross-regional data transfer costs
• Regulatory requirements
• Sustainability requirements such as low-carbon regions

Storage and messaging services enforce residency by location controls. Cloud Storage helps meet residency because data is stored or replicated within the specified region, and buckets can be regional, dual-region, or multi-region. Pub/Sub can enforce location with message storage policies that keep message data in specific locations, and Cloud Logging can be configured to store log data in a specified region through regionalized logs.

Filestore follows the same residency logic for file data. Files stored in a Filestore Regional instance are replicated synchronously across three zones within the region, which protects against zone outages, and an instance can be replicated to a different region for robustness against region outages.

2.4Multi-Cloud Federation Without Data Movement

Multi-cloud federation means analytical engines query data where it resides instead of copying it through fragile extract, transform, and load (ETL) pipelines. This approach avoids the operational overhead, storage costs, and latency of physical data movement and creates a borderless unified profile where analysts see consistent, up-to-date business entities. The open Apache Iceberg table format with Apache Parquet files decouples data from specific compute services so multiple engines can access the same objects in Cloud Storage and Amazon Simple Storage Service (Amazon S3). AlloyDB for PostgreSQL is the high-performance database for hybrid transactional and analytical processing (HTAP), such as real-time inventory or live customer profiles. BigQuery AlloyDB federation sends a query from BigQuery directly to live transactional data stored in AlloyDB through BigQuery connections, which eliminates the latency and overhead of CDC pipelines. Managed Service for Apache Spark with Lightning Engine then executes distributed joins and behavioral aggregations by ingesting data from Parquet files in Amazon S3, event logs in Cloud Storage, and live AlloyDB data. Access to external metadata is governed before any data flows. Borderless Lakehouse (opens in a new tab) acts as the central metadata and governance system and establishes its connection in order:

1. Connect to catalog: Lakehouse connects to Databricks Unity Catalog to resolve schema and routing information for borderless Iceberg data.
2. Verify credentials: Lakehouse sends a request to Secret Manager to verify the Databricks service principal credentials.
3. Enforce decision: If credentials are approved, Lakehouse establishes a direct connection to lakehouse metadata; if credentials are denied, Lakehouse returns an error message and aborts the operation.

2.5High Availability and Disaster Recovery Across Heterogeneous Stacks

High availability and disaster recovery in heterogeneous stacks combine synchronous replication inside a region with asynchronous copies and backups across regions and providers. Spanner (opens in a new tab) replicates data in multiple zones across multiple regions through multi-region configurations and adds database deletion protection, scheduled backups with cross-region copies, and point-in-time recovery (PITR) for up to seven days. Cloud Storage regional buckets replicate synchronously across zones, while dual-region and multi-region buckets replicate asynchronously across regions. File, virtual machine (VM), and Oracle database layers add their own durability controls. Filestore Regional replication protects against zone loss and cross-region instance replication protects against region loss. Backup and DR Service creates, stores, and manages backups of Compute Engine VMs in original, application-readable format so workloads can be restored directly from long-term backup storage without data-movement preparation. By default, backups of databases in Exadata Database Service on Dedicated Infrastructure (opens in a new tab) are stored in OCI Object Storage, and Oracle Autonomous Recovery Service can be used to achieve a lower recovery point objective (RPO).

Database federation and external data integration allow BigQuery (opens in a new tab) to analyze data that remains in its original storage engine without migrating or copying it first. Federation sends a query to an operational database and returns the result as a temporary table, while external tables expose outside files or databases as queryable tables inside a BigQuery dataset. Together these approaches support real-time analysis across Cloud SQL, Spanner, AlloyDB, Bigtable, Cloud Storage, and multi-cloud storage on AWS and Azure.

3.1Federated Queries to Operational Databases

Federated queries send a query statement from BigQuery to an AlloyDB, Spanner, or Cloud SQL database and return the result as a temporary table in BigQuery. The query uses the BigQuery Connection API (opens in a new tab) to establish a named connection to the external database, and the EXTERNAL_QUERY function carries a query written in that database's own SQL dialect. Results flow from the external database back to BigQuery, where data types are converted to GoogleSQL types for joining with native BigQuery tables. A federated query depends on a connection resource that controls access to the source database. The setup follows an ordered workflow:

1. Identify source project: Locate the Google Cloud project that contains the AlloyDB, Spanner, or Cloud SQL data source.
2. Create connection: A user with the bigquery.admin role creates a connection resource in BigQuery.
3. Share connection: The admin grants permission to use the connection resource to another user, unless the admin and the querying user are the same person.
4. Run EXTERNAL_QUERY: The authorized user writes a BigQuery query that calls EXTERNAL_QUERY with the connection name and the source-database SQL statement.

Federated queries are best for joining small or frequently changing operational data with warehouse data, because the source is read live on every execution and does not need to be reloaded into BigQuery storage.

3.2External Tables for Analysis Without Data Migration

External data source tables (opens in a new tab) are BigQuery tables whose metadata and schema are stored in BigQuery storage while the data itself remains in an external source. The table lives inside a BigQuery dataset and is managed like a standard table for viewing properties and setting access controls, and in most cases it can be joined directly to other BigQuery tables using BigQuery syntax. This model supports extract-load-transform workloads where a CREATE TABLE ... AS SELECT query loads, cleans, and writes external data into BigQuery storage in one pass, and live joins where changing source data is queried directly to avoid repeated reloads. There are three kinds of external data source tables with different security models:

• BigLake tables: (opens in a new tab) Query structured data in external stores with access delegation through a service account on an external connection. Users need access only to the BigLake table, which enables table-level, row-level, column-level security, and dynamic data masking for Cloud Storage data.
• Object tables: Analyze unstructured data in Cloud Storage with access delegation through a service account. Rows represent file metadata, and results from remote functions or BigQuery ML inference can be joined to structured BigQuery data with row-level security.
• External tables: Query structured data in stores such as Cloud Storage, Bigtable, and Google Drive without access delegation. The querying user must have permission to both the external table and the underlying source, for example bigquery.tables.getData, bigquery.jobs.create, storage.buckets.get, and storage.objects.get for a Cloud Storage source.

Unlike federated queries, external tables and datasets do not use EXTERNAL_QUERY and do not require writing in the source database dialect. Bigtable is accessed through external tables, while Spanner and Cloud SQL can be accessed through federated queries.

3.3BigQuery Omni for Multi-Cloud Analytics

BigQuery Omni (opens in a new tab) runs BigQuery analytics on data stored in Amazon S3 or Azure Blob Storage using BigLake tables. Compute runs locally to the storage in the AWS or Azure region, which breaks down data silos for organizations that store data in multiple public clouds behind one unified interface. Data flows from S3 or Blob Storage through a connection into a BigLake table reference, and then through BigQuery execution without decentralized governance overhead. To use BigQuery Omni, first create a connection to Amazon S3 or Blob Storage, then create a BigLake table that references the external files. Four tools move or combine that data:

• BigQuery Omni joins: Run a query from a BigQuery region that joins data from a BigQuery Omni region, including joins between two Omni regions such as AWS and Azure.
• BigQuery Omni materialized views: Use materialized view replicas to continuously and incrementally replicate data from Omni regions with support for filtering.
• BigQuery Omni transfer using SELECT: Run CREATE TABLE AS SELECT or INSERT INTO SELECT in an Omni region and move the filtered result to a BigQuery region.
• BigQuery Omni transfer using LOAD: Use LOAD DATA statements to load data directly from S3 or Blob Storage into BigQuery.

Choose Omni joins for one-time analysis of smaller data without caching needs, materialized views for repeated or scheduled transfers such as dashboards where caching matters, transfer using SELECT for one-time complex queries that need manual optimization, and transfer using LOAD to migrate large raw datasets as-is.

3.4Performance, Security, and Location Constraints

Performance, security, and location rules determine whether federation or external access will meet latency and governance needs. A federated query is typically slower than querying native BigQuery storage because BigQuery must wait for the source database to execute the external statement and temporarily move the result into BigQuery, and the source database is often not optimized for complex analytical queries. Performance also varies with proximity between the BigQuery processing location and the external source, so closer pairings respond faster. Security depends on the connection model chosen for each method. Federated access depends on the BigQuery connection resource plus a database user account for Cloud SQL, BigLake and object tables depend on a service-account connection that decouples table access from storage access, and standard external tables require the user to hold permissions on both the table and the source system. For workload isolation on the source, query a database read replica rather than the primary instance, since there is no separate quota control for federated querying inside BigQuery. Location and quota limits enforce regional boundaries. A BigQuery single region can only query an AlloyDB or Cloud SQL resource in the same region, while a BigQuery US or EU multi-region can query any single-region source in the same large geographic area, except that sources in southamerica-east1 cannot be queried from the US multi-region. The query processing location must match the location of the connection resource, so a query running in the US multi-region must use a connection located in the US multi-region. Spanner supports both regional and multi-regional configurations, and a BigQuery single region or multi-region can query a Spanner instance in any supported Spanner region. Cross-region federated queries are limited to 1 TB per project per day, a single federated query can reference at most 10 unique connections, and the maximum-bytes-billed control is not supported because billed bytes cannot be estimated before execution.

3.5Choosing an Integration Method

The integration methods differ by how data is addressed, how access is granted, and which stores they reach. External tables expose persistent metadata that can be browsed like normal tables, while federated queries return only a temporary result for that execution.

Choose federated queries when live operational rows must be filtered in the source engine and joined ad hoc, choose BigLake tables when centralized governance with row-level or column-level control is required, choose standard external tables when direct permission checks on the source are acceptable, and choose BigQuery Omni with BigLake tables when the data resides in Amazon S3 or Azure Blob Storage.

Key Facts

• Log-based change data capture (CDC) reads the source transaction log to migrate changes in order without loss, while differential querying polls timestamp or sequence fields and adds query load and schema changes.
• Asynchronous unidirectional replication feeds an analytical secondary from a transactional primary with replication lag, while a fully synchronized distributed system makes every commit immediately visible in all locations.
• Cross-Cloud Interconnect provides a private connection between Google Cloud and AWS that avoids high egress fees and unpredictable latency of public internet queries.
• Federated queries use EXTERNAL_QUERY in the source database dialect and return a temporary table, while external tables and BigLake tables use standard BigQuery syntax against a persistent table reference.
• A BigQuery single region can only query an AlloyDB or Cloud SQL resource in the same region, and cross-region federated queries are limited to 1 TB per project per day with at most 10 unique connections per query.