BigQuery Migration Services, Networking and Validation

Evaluating and selecting migration services means matching the source type, data volume, usable bandwidth, and transfer deadline to the Google Cloud service that can move the data and land it ready for BigQuery. Cloud Storage staging (opens in a new tab) is the central pattern in most warehouse migrations, so data flows from the source system to a Cloud Storage bucket and then from the bucket into BigQuery. The choice depends on whether the source is a data warehouse, cloud object store, or on-premises file system, and whether the network can meet the deadline or an offline path is required.

1.1Assess Source Volume and Transfer Window

Assessment of a transfer starts with source location, dataset size, usable bandwidth, and the time window that avoids disrupting production work. Transfer time grows quickly with volume because even at an ideal 1 Gbps, 1 GB takes eight seconds and 100 TB takes about 12 days before overhead, retries, and efficiency losses. Cost and connectivity shape the same decision because ingress to Cloud Storage is free, but egress from another cloud, added bandwidth to Google Cloud, bucket storage and operations, appliance fees, and staffing all add cost. Estimates for large dataset transfer (opens in a new tab) must therefore include:

• The size of the dataset being moved.
• The bandwidth available for the transfer.
• A percentage of management time for approvals, setup, and unanticipated issues.
• Bandwidth efficiency and contention with production traffic during peak hours.

When private data center bandwidth is insufficient, connectivity can be increased through public internet with a public API, Direct Peering with a public API, or Cloud Interconnect with a private API. Cloud-to-cloud transfers require no customer network setup because Google provisions the connection between cloud vendor data centers. For private data centers, the long-term need for Google Cloud connectivity often justifies an upgrade, but public internet transfers must be checked against security policy and production impact.

1.2BigQuery Data Transfer Service for Warehouse and Scheduled Ingestion

BigQuery Data Transfer Service (DTS) is the managed service that automates movement of schema and data into BigQuery on a scheduled basis without writing code. A transfer configuration is created from the Google Cloud console, bq command-line tool, or BigQuery Data Transfer Service API, and the service then loads data automatically on a schedule and supports backfills to recover from outages or gaps. The transfer configuration inherits the location of the destination BigQuery dataset, and the service processes and stages data in that same location. The service cannot transfer data out of BigQuery, and it is also used for BigQuery dataset copies and scheduled queries.

BigQuery Data Transfer Service (opens in a new tab) is the direct choice for initial warehouse migration from Amazon Redshift (opens in a new tab) and Teradata (opens in a new tab), where it copies table schemas and data into BigQuery while still using Cloud Storage to stage data as part of the migration. For Teradata, a special migration agent runs in the on-premises environment alongside the transfer configuration to extract and hand off data. For any warehouse, the alternative pattern is to extract files, upload them to Cloud Storage, and then load them with BigQuery Data Transfer Service, the BigQuery Storage Write API, or batch load. To reduce complexity, extraction should use Apache AVRO, ORC, or Parquet where schema is embedded with the data, while CSV requires separate schema handling through load-time specification or auto-detection.

After the initial load, BigQuery Data Transfer Service supports recurring ingestion from Cloud Storage, Amazon Simple Storage Service (Amazon S3), Azure Blob Storage, and many SaaS, marketing, payment, database, and Google services. Choosing the Cloud Storage bucket in the same location as the BigQuery dataset minimizes latency for both one-time and scheduled loads. If transformation is needed before or during loading, data flows from the staging bucket through Cloud Data Fusion, Dataflow with the Apache Beam model, or Managed Service for Apache Spark before landing in BigQuery.

1.3Online Object Transfer With gcloud and Storage Transfer Service

Online object transfer moves files as objects into Cloud Storage over the network, and the choice between tools depends on scale, operability, and control needs. The gcloud storage command is the standard tool for small- to medium-sized transfers from a private data center or another cloud over a typical enterprise network. It supports multi-threaded parallel uploads, resumes after errors, and encrypts traffic in transit, but large-object transfers are more likely to fail than short-running transfers. The command fits specific operational patterns:

• Transfers executed on an as-needed basis or in interactive command-line sessions.
• Moves involving only a few files or very large files.
• Streaming the output of a program directly to Cloud Storage.
• Watching a directory with a moderate number of files and syncing updates with very low latency.

Storage Transfer Service is the fully managed service for large-scale transfers up to petabytes of data and billions of files from another cloud, between Cloud Storage buckets, or from on-premises network file system (NFS) storage to Cloud Storage. The on-premises variant supports full copies or incremental copies and recurring scheduled transfers through a managed graphical interface. Data flows from the source NFS directory through on-premises agents to the destination bucket, where Storage Transfer Service recursively crawls subdirectories and creates objects with corresponding names. The service automatically retries transient errors, exposes file counts, throughput, and error samples during the run, and on completion generates a tab-delimited file with a full record of files touched and error messages.

Agent deployment and governance determine whether Storage Transfer Service meets its throughput and reliability targets. All agents must see the same NFS mounts with identical relative paths, and throughput scales because transfers parallelize across agents, so more agents use more of the available bandwidth. Bandwidth caps protect other data center workloads and service-level agreements, while Cloud Monitoring alerts on agent health and throughput are critical for transfers lasting days or weeks. Large transfers should plan time for error review because source files in active use or permission problems can leave errors even after retries, and the full error record can be loaded to BigQuery for analysis.

1.4Transfer Appliance for Offline Large-Scale Transfer

Transfer Appliance is the offline hardware option for large-scale transfers when network bandwidth cannot meet the deadline or cannot be acquired in time. Data is captured to Google-owned appliances at the customer site and shipped back to Google, so latency into Google Cloud is typically higher than online transfer but avoids weeks or months on a constrained link. With reasonable connectivity such as 1 Gbps, 100 TB takes over 10 days online, while the same 100 TB takes over 100 days at 100 Mbps, which is the point where offline transfer becomes worth evaluating. Transfer Appliance fits constrained environments:

• Data centers in remote locations with limited or no bandwidth access.
• Bandwidth that exists but cannot be provisioned in time to meet the deadline.
• Sites with logistical resources to receive, connect, and return appliance hardware.

Transfer Appliance depends on physical logistics and availability because the team must request capacity in the Google Cloud console, receive one or more appliances, complete data capture within the allowed window, and ship the hardware back. Transfer Appliance is available only in certain countries. The decision therefore trades appliance product and handling cost against the cost and delay of acquiring bandwidth, especially when production traffic cannot tolerate sustained saturation.

1.5Choosing the Optimal Service for Data Type and Volume

The optimal service decision compares source location, data type, volume, and whether the deadline allows an online window. The following comparison summarizes the decision rules supported by the migration guidance.

After files arrive in Cloud Storage, downstream processing can start immediately with tools such as Dataflow, and validation should confirm row counts and aggregations before expanding the migration footprint. The recommended iteration is to transfer schema unchanged first so existing pipelines, staff training, and proofs of concept continue in parallel, then expand data coverage and reroute upstream feeds to BigQuery.

Secure data migration networking is the design of private, high-throughput paths from a source environment into Google Cloud that control latency, throughput, and egress cost. Traffic flows from on-premises data centers or other clouds across a chosen connectivity service into a Virtual Private Cloud (VPC) and then to migration services such as Storage Transfer Service and Cloud Storage. Because network design affects how every workload moves, connectivity must be built during the foundation phase before data transfer jobs or cutover begin.

2.1Build Foundation Connectivity Before Migration

Foundation connectivity is the set of landing-zone tasks that must exist before any large dataset moves. The foundation sequence for workload assessment (opens in a new tab) establishes a resource hierarchy, configures Identity and Access Management (IAM), sets up billing, sets up network connectivity, hardens security, and sets up logging, monitoring, and alerting. Network connectivity in this sequence creates the VPCs, subnets, firewall rules, and hybrid links that later carry replication traffic, validation checks, and rollback traffic. Building this foundation first prevents insecure public paths and avoids rework when subnets or routes must change mid-migration.

2.2Design VPCs and Subnets for Regional Moves

Virtual Private Cloud (VPC) design controls where migration traffic can flow and how easily workloads can shift regions. VPCs are global resources, while each single subnet is always bound to one region and subnets cannot use overlapping IP addresses. To maintain the same addresses after a move, a new VPC is created rather than reusing the same subnet in the target region. Cloud DNS helps decouple internal subnet IPs from virtual machines (VMs), and DNS peering routes traffic for migrated workloads before the old region is dismissed.

Prefer network designs that are easily replicable and loosely coupled from specific topology, as described in guidance for architecting workloads across regions (opens in a new tab). A design that hardcodes subnet IPs, VM names with region prefixes, or region-specific endpoints breaks pipelines when regions change. Global or multi-region configurations and managed cross-region replicas reduce this risk because the service does not have to be fully rebuilt in the new region.

2.3Choose High-Throughput Hybrid Pathways

Hybrid connectivity pathways carry migration data from the source network into Google Cloud with different performance, security, and reconfiguration tradeoffs. Cloud Interconnect provides dedicated capacity through a VLAN attachment that connects to Google Cloud in a specific region. Partner Interconnect provides the same VLAN attachment model through a partner physical location. Cloud VPN provides an encrypted connection over the public internet and depends on routers that must reflect the current subnet addresses.

The pathway choice also affects continuous replication and cutover, and Cloud Router (opens in a new tab) manages the dynamic routing for these hybrid attachments. Replication traffic from the source must stay on the provisioned path while validation runs, and only after the destination is synchronized are source workloads stopped and clients pointed to the new buckets or databases. Keeping the source data for a period after cutover preserves a rollback path if post-migration monitoring finds errors that testing missed. The available migration tools (opens in a new tab) help inventory which workloads depend on each path before cutover.

2.4Control Egress Cost Latency and Security Perimeter

Migration network cost is usage-based in Google Cloud, which changes the economics compared with an on-premises data center. In a data center, routers, switches, and cabling are one-time costs that allow use of full network capacity, while in Google Cloud many services bill for network utilization, including egress traffic that leaves the environment and regional and zonal traffic that crosses region or zone boundaries. For data-intensive workloads, new architectures and network flows are often needed to lower networking costs, and cross-regional replica traffic for disaster recovery and high availability must be included in total cost of ownership (TCO) estimates calculated with the price calculator.

Latency and egress cost are reduced by keeping storage and computation close together and by selecting the transfer egress route deliberately. Storing data in the same zone that processes the data reduces communication latency, outbound data transfer cost, and input/output operations between the storage layer and computation layer. For transfers such as Amazon S3 to Cloud Storage (opens in a new tab), the documented egress routes are:

• Across the public internet to Storage Transfer Service
• By using an interconnect link between AWS and Google Cloud
• By using Amazon CloudFront as the egress front end

The selected route affects AWS network egress charges, Amazon S3 request charges, and the effort and resources needed to provision and configure infrastructure. VPC Service Controls adds a security boundary around this flow by building a perimeter that explicitly denies communication between Google Cloud services unless the services are authorized, which constrains migrated data within the VPC during transfer and validation.

Continuous data validation and replication is the practice of keeping a source system and BigQuery synchronized throughout migration while proving that every row, transformation, and downstream result remains correct. Replication moves data continuously from transactional sources to the target, and validation checks integrity during transformation and reconciles downstream processes such as scripts, queries, dashboards, and business applications. The two work together across the migration lifecycle, from initial offload with incremental copy to full migration with direct ingestion.

3.1Change Data Capture and Continuous Replication Flows

Change data capture (CDC) is a streaming replication pattern where row changes generated by Online Transaction Processing (OLTP) databases flow continuously into data pipelines instead of waiting for a periodic batch window. Batch data pipelines run on data collected over a period of time, such as once a day, while streaming data pipelines handle real-time events from operational systems. In a data flow directed acyclic graph (DAG), data flows from one or more source systems through transformations to a sink, which is the connection to the destination data system, and then into the data warehouse.

Sources in the DAG typically pull their data from the source systems, so the pipeline depends on source availability and transactional load. A typical extract, transform, and load (ETL) pipeline pulls from as few source systems as possible to avoid failures caused by unavailable systems, then performs a series of transformations before loading to the sink. ETL technologies run outside the data warehouse, which preserves warehouse resources for concurrent querying but requires additional tooling and languages beyond SQL to express transformations. It is common to split work into multiple data pipelines (opens in a new tab), where the first pipeline focuses on copying data from the source system to the warehouse and later pipelines apply business logic.

3.2Incremental Synchronization Across Migration Stages

Incremental synchronization is the replication stage that keeps BigQuery consistent with the legacy warehouse after the initial schema and data transfer. When a use case is offloaded, upstream pipelines are unchanged and still write to the existing warehouse, so an incremental copy must flow from the old warehouse to the new BigQuery tables to keep data synchronized. Full migration ends that dependency by moving upstream pipelines to Google Cloud so data is ingested directly into BigQuery and the corresponding legacy tables can be deprecated. This staged pattern applies to Oracle (opens in a new tab), Apache Hive (opens in a new tab), and IBM Netezza (opens in a new tab) migrations that use the same offload-then-migrate model.

The offload-to-validation sequence establishes replication before downstream cutover in this order:

1. Migrate use-case schema and data from the existing warehouse into BigQuery as the baseline target.
2. Establish incremental copy from the old warehouse to BigQuery so ongoing upstream writes continue to replicate to the target.
3. Migrate and validate downstream processes including scripts, queries, dashboards, and business applications against BigQuery data.

Only after the incremental copy is running can downstream validation be trusted, because validation compares a moving source against a target that must reflect the same changes. During an iteration a team can offload only a use case, fully migrate a previously offloaded use case, or fully migrate from scratch by offloading first in the same iteration.

3.3Coexisting Replication Paths During Migration

The migration lifecycle maintains three replication paths at once while use cases move from legacy to target. The existing path keeps data and processes centered on the legacy warehouse, the offloaded path feeds the legacy warehouse from upstream processes and then replicates to BigQuery for downstream processes, and the fully migrated path no longer writes to or reads from the legacy warehouse. Test upstream processes can be configured to write to BigQuery tables instead of, or in addition to, the existing warehouse before production upstream and downstream processes are redirected through the BigQuery API.

The three paths differ by where writes land and whether ongoing synchronization is required:

Revisiting offloaded use cases in later iterations moves them to fully migrated, and when all use cases are fully migrated the old warehouse can be switched off to reduce overhead and costs. Full migration requires offload first, so every use case passes through a validated synchronized state before legacy dependencies are removed.

3.4Validation Integrity Checks and Orchestration

A validation framework is a set of transformation checks, downstream reconciliations, and orchestration controls that prove consistency between source and target throughout migration. Transformation stages apply business rules and integrity logic before data reaches the sink, while downstream validation confirms that migrated queries, dashboards, and applications return correct results from BigQuery. Schema evolution work, such as re-creating tables with partitioning and clustering and redirecting upstream and downstream processes to the new tables, must also be revalidated because it changes physical layout and access paths. Schema and data transfer planning (opens in a new tab) provides the baseline that these checks reconcile against.

Integrity and reconciliation checks during ETL include:

• Cleaning data to remove malformed or duplicate records before loading
• Applying business rules that define correct values and relationships
• Checking for data integrity between source extracts and transformed outputs
• Creating aggregates or disaggregates and reconciling them against source totals

An orchestration or control flow DAG coordinates data movement between pipelines and other systems, which is distinct from a data flow DAG that moves row data itself. Managed Service for Apache Airflow (Managed Airflow) authors, schedules, and monitors pipelines that span cloud environments and on-premises data centers, so replication, transformation, and validation steps run in order and failures are visible across boundaries. That orchestration boundary matters because sources, sinks, warehouses, and downstream consumers sit on different systems, and consistency depends on the control plane managing order, retries, and monitoring rather than on any single pipeline step succeeding in isolation.

Key Facts

• BigQuery Data Transfer Service copies schema and data from Amazon Redshift and Teradata into BigQuery with Cloud Storage staging, supports schedules and backfills, and never moves data out of BigQuery.
• Storage Transfer Service manages large-scale transfers up to petabytes and billions of files, parallelizes work across on-premises agents, and generates a tab-delimited audit record of files touched and errors.
• Transfer Appliance is the offline hardware option when bandwidth cannot meet the deadline, such as 100 TB requiring over 10 days at 1 Gbps and over 100 days at 100 Mbps, and it is available only in certain countries.
• Virtual Private Clouds are global while subnets and Cloud Interconnect VLAN attachments are region-specific, so region migration requires new subnets and a new attachment region.
• Offloaded migration keeps upstream writes in the legacy warehouse with incremental copy to BigQuery, while fully migrated migration writes and reads directly in BigQuery with no legacy dependency.