Planning BigQuery Data Migration and Validation

Analyzing current needs and creating a migration plan is the assessment that connects what stakeholders want to what the current warehouse can do. The work inventories data flows, systems, users, and compatibility, then defines a phased path to a desired state where upstream processes write directly to BigQuery (opens in a new tab) and downstream processes read from BigQuery. Every use case moves through offload before full migration, with validation before legacy tables are deprecated.

1.1Mapping Data Workflows and Pipeline Dependencies

Data workflows are directed acyclic graphs (DAGs) that define the ordered path data follows from sources through transformations to sinks. A data flow DAG carries row data from transactional source systems such as a relational database management system (RDBMS) to a sink connected to the data warehouse, while an orchestration or control flow DAG coordinates movement between pipelines and other systems without moving row data itself. Sources inside the graph typically pull from the source system, so traffic flows from the source system to the source node, then through each transformation in order, then from the sink to the warehouse.

BigQuery migration planning distinguishes batch pipelines that run on data collected over a period, such as once per day, from streaming pipelines that handle real-time events such as change data capture (CDC) row changes from Online Transaction Processing (OLTP) databases. In an extract, transform, and load (ETL) procedure, data flows from one or more source systems through cleaning, business rules, integrity checks, and aggregation outside the warehouse, then loads into the warehouse. Running transformation outside the warehouse preserves warehouse resources for concurrent querying but requires extra tooling and languages beyond SQL, and pulling from many sources increases failure risk when any source system is unavailable.

Dependencies show where delay and migration risk concentrate. Upstream processes write data into warehouse tables and downstream processes such as scripts, queries, dashboards, and business applications read from those tables, often over JDBC connections identified by application name, user, and IP address. The Database Coupling view reveals which databases and tables are accessed together in a single query, so tables with heavy shared access and many dependents must be planned and moved together.

1.2Inventorying Source Systems and Usage to Sequence the Move

Inventorying source systems builds an Existing System snapshot from analyzed logs for a defined time period. The snapshot quantifies compute, storage, applications, and schema objects to expose utilization, waste, and sequencing constraints. Large tables take longer to extract and affect order, heavily used tables often have many dependencies, and query histograms reveal low-utilization windows for transfer.

The snapshot organizes findings into related views:

• System Overview: high-level volume metrics of key components to gauge overall warehouse utilization for planning.
• Table Volume: statistics on the largest tables and databases that shape extraction time and sequencing.
• Table Usage: statistics on heavily used tables that often need extra dependency planning.
• Queries: breakdown of SQL statement types, histogram of query type and time, plus frequently executed queries and invoking users.
• Databases: metrics on size and counts of tables, views, and procedures that must be migrated.
• Applications: access rates over time, reporting versus ETL breakdown, and client IP addresses that visualize ingestion and consumption dependencies.
• Compute and Queries detail: heatmap of hourly average CPU utilization, queries by hour and day, queries by type with CPU utilization, applications with CPU utilization, overlays of CPU with query and application performance, and queueing and waiting analysis for Hive queues.

Storage hygiene further shapes complexity. The Tables With No Usage view lists tables with no observed reads during the log period, and the Tables With No Writes view lists tables with no updates, both signaling potential waste that can be omitted to lower storage cost. Discovery from the discovery client or uploaded database scripts reports detected software, suggested Compute Engine machine type with cores and memory, simultaneous multithreading (SMT) guidance, and compatibility issues for each feasible Google Cloud product.

1.3Scoring Fit and Translation Effort for Google Cloud Targets

Migration complexity is scored by technical fit and translation effort. Fit rules from Google Cloud specifications and tool limitations aggregate into a server-level or database-level fit score that decides whether a workload can move as is, needs remediation, or is blocked:

SQL and schema compatibility refines the estimate. The SQL Translation view counts queries automatically converted without manual intervention, while the Offline Effort view captures user-defined functions (UDFs), procedures, and lexical violations needing manual action. The Warnings To Review view captures mostly translated queries needing inspection, and the Reserved Keywords view flags GoogleSQL keywords that must be enclosed in backticks when used as identifiers. The Table Updates Schedule shows when and how frequently tables are updated so read and write schedules can group tables for movement, while steady-state suggestions such as converting join indexes to materialized views, adding partitioning and clustering candidates, routing low-latency queries to BigQuery BI Engine, and using staging tables with MERGE to enforce uniqueness define the target shape.

1.4Turning Stakeholder Needs into Measurable Migration Targets

Stakeholder analysis turns interviews, workflows, and system evidence into measurable checks for migration. Functional requirements include sources and sinks, batch or streaming delivery, and the ETL logic for cleaning data, applying business rules, checking integrity, and building aggregates or disaggregates. Non-functional requirements include cost limits, migration timelines, concurrent query performance, reliability when source systems are unavailable, and the skills needed to operate new tooling.

User personas separate into upstream producers, downstream consumers, and pipeline builders. Upstream producers own transactional systems such as relational and OLTP databases and write data through ingestion pipelines, downstream consumers run scripts, queries, dashboards, and business applications that read from warehouse tables, and pipeline builders create and monitor the DAGs. These views let planners set explicit targets such as downstream dashboards returning identical results, high-value tables migrated during low-utilization windows identified from query histograms, and unused tables excluded after validation for quarterly or half-yearly use. Translation buckets that group similar query patterns show whether the approach holds across users, applications, and affected tables.

1.5Choosing Offload, Full Migration, and Target Pipeline Architecture

The migration path for each use case is selected from stakeholder tolerance for change, skills, and cost and time limits. When a use case is offloaded, its schema and data move to BigQuery, an incremental copy keeps the old and new warehouses synchronized, and downstream processes are migrated and validated while upstream pipelines still write to the legacy warehouse for later full migration. When a use case is fully migrated, which requires offloading first in the same or a prior iteration, upstream pipelines are migrated to Google Cloud so data ingests directly into BigQuery and legacy tables can be deprecated. When all use cases are fully migrated, the old warehouse can be switched off to reduce overhead and cost.

Lift and shift preserves existing integration tooling when that tooling already supports BigQuery. Teams keep the ETL or extract, load, and transform (ELT) platform as is and change storage stages to BigQuery, or install the vendor tool on Compute Engine or from Google Cloud Marketplace when the vendor licenses it on Google Cloud. Rearchitecting replaces pipelines with Google Cloud services when stakeholders need managed scaling, unified batch and streaming code, visual development, or cross-cloud orchestration.

The three rearchitecting services divide work by builder skill and coordination need:

1.6Evolving the Schema Without Breaking Producers and Consumers

Schema evolution in BigQuery is the controlled sequence that improves the migrated design while upstream writers and downstream readers keep working. Light optimizations recreate tables with partitioning and clustering by creating a table from a query result, then redirect upstream and downstream processes to the new tables. Facade views then mask underlying tables to hide change complexity, so the view definition and tables can change while views always return the same results.

The documented order prioritizes visible business value by changing consumers before producers:

1. Transfer use case as is: Confirm upstream and downstream processes already write to and read from BigQuery tables, or apply only downstream guidelines when only downstream reads from BigQuery.
2. Apply light optimizations: Re-create tables with partitioning and clustering, then redirect upstream and downstream processes to the new tables.
3. Create facade views: Define views that present a new schema free from technical debt for future ingestion and consumption scenarios.
4. Transform downstream processes: Move selected downstream processes to read from facade views, so migrated dashboards and reports show value first even though views still source from the original BigQuery schema.
5. Transform upstream processes: Move selected upstream processes to write into the new schema once downstream validation is complete.

Teams may stop at an intermediate step or continue until fully evolved, depending on stakeholder needs. Transforming downstream first is preferred when non-technical stakeholders need early proof, while starting upstream first remains valid when ingestion correctness is the binding constraint.

Planning migration and validation matches source type, volume, bandwidth, and deadline to the Google Cloud service that moves data and proves correctness. Cloud Storage staging 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 (opens in a new tab). Connectivity must be built during the foundation phase before transfer jobs or cutover begin.

2.1Sizing the Transfer and Choosing the Ingestion Service

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 must therefore include:

• Dataset size: the total volume being moved, which sets baseline duration and staging needs.
• Available bandwidth: the usable throughput after contention with production traffic during peak hours.
• Management overhead: a percentage of time for approvals, setup, and unanticipated issues.
• Efficiency losses: bandwidth efficiency, retries, and protocol overhead that extend the ideal transfer time.

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.

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 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. 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, the service supports recurring ingestion from Cloud Storage, Amazon Simple Storage Service (Amazon S3), Azure Blob Storage, and many SaaS and Google services, with the bucket in the same location as the dataset to minimize latency.

2.2Moving Objects Online with gcloud storage 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. The command 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 gcloud storage command fits specific operational patterns:

• Ad hoc execution: transfers executed on an as-needed basis or in interactive command-line sessions.
• Few or very large files: moves involving only a few files or very large files that need manual control.
• Streaming output: streaming the output of a program directly to Cloud Storage without staging files.
• Directory sync: 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. 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 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.

2.3Using Transfer Appliance for Offline Moves

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 such as data centers in remote locations with limited or no bandwidth access, bandwidth that exists but cannot be provisioned in time to meet the deadline, and 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.

The optimal service decision compares source location, data type, volume, and whether the deadline allows an online window:

2.4Building VPC Connectivity Before Data Moves

Secure 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. Foundation connectivity must exist before any large dataset moves, including resource hierarchy, Identity and Access Management (IAM), billing, VPCs, subnets, firewall rules, hybrid links, security hardening, and logging and monitoring.

Virtual Private Cloud 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 designs that are easily replicable and loosely coupled from specific topology, because hardcoding subnet IPs or region-specific endpoints breaks pipelines when regions change.

Hybrid connectivity pathways carry migration data into Google Cloud with different performance and reconfiguration tradeoffs:

Cloud Router manages dynamic routing for these hybrid attachments. Replication traffic 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.

2.5Controlling Cost, Latency, and Security Perimeter During Transfer

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, the documented egress routes are across the public internet to Storage Transfer Service, by using an interconnect link between AWS and Google Cloud, and by using Amazon CloudFront as the egress front end. The selected route affects AWS network egress charges, Amazon S3 request charges, and the effort needed to provision 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.

2.6Keeping Source and BigQuery Synchronized with Validation

Continuous validation and replication keeps a source system and BigQuery synchronized while proving that every row, transformation, and downstream result remains correct. Change data capture (CDC) is a streaming replication pattern where row changes generated by OLTP databases flow continuously into pipelines instead of waiting for a periodic batch window. Sources in the DAG typically pull their data from the source systems, so the pipeline depends on source availability, and a typical ETL pipeline pulls from as few source systems as possible to avoid failures. It is common to split work into multiple pipelines, where the first pipeline copies data from the source system and later pipelines apply business logic.

Incremental synchronization keeps BigQuery consistent with the legacy warehouse after the initial transfer. When a use case is offloaded, upstream pipelines still write to the existing warehouse, so an incremental copy must flow from the old warehouse to the new BigQuery tables. Full migration ends that dependency by moving upstream pipelines to Google Cloud so data is ingested directly into BigQuery. The offload-to-validation sequence runs in strict order: first, migrate use-case schema and data into BigQuery as the baseline; then, establish incremental copy so ongoing writes replicate; then, migrate and validate downstream scripts, queries, dashboards, and business applications against BigQuery data.

The migration (opens in a new tab) lifecycle maintains three replication paths at once while use cases move from legacy to target:

A validation framework proves consistency through transformation checks, downstream reconciliations, and orchestration controls. Integrity checks during ETL include cleaning data to remove malformed or duplicate records, applying business rules that define correct values, checking integrity between source extracts and transformed outputs, and creating aggregates or disaggregates and reconciling them against source totals. Managed Service for Apache Airflow (Managed Airflow) authors, schedules, and monitors pipelines that span cloud and on-premises data centers, so replication, transformation, and validation steps run in order and failures are visible. Schema evolution work, such as re-creating tables with partitioning and clustering, must also be revalidated because it changes physical layout and access paths.

Key Facts

• Offloaded use cases keep upstream writes in the legacy warehouse with incremental copy to BigQuery, while fully migrated use cases ingest directly into BigQuery and allow legacy tables to be deprecated.
• Full migration always requires offloading first, either in a prior iteration or in the same iteration.
• 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.
• 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.
• 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.