Designing data migrations

Analyzing current stakeholder needs, users, processes, and technologies, and creating a plan to get to desired state

Conduct Stakeholder and User-Centric Analysis

Stakeholder and user-centric analysis is the first step in designing a data migration to Google Cloud Platform (GCP). This process examines the current needs, workflows, and technologies of all users to create a clear plan for the future system. The goal is to ensure the migration strategy supports business objectives, not just technical requirements.

A critical part of this analysis is identifying both functional requirements, which define what the system must do, and non-functional requirements, which define how the system performs in terms of scalability, latency, security, and cost. Analysts must also map user personas and their interaction patterns with the existing data systems. This involves understanding different user roles, their daily tasks, and their pain points with the current system.

The findings from this analysis are then translated into measurable success criteria for the migration. These criteria become the benchmarks for evaluating the project's success, such as achieving a specific reduction in report generation time or enabling new types of analytical queries. This approach directly informs later decisions, like whether to start with an offload or pursue a full migration, and guides the evolution of the data schema in BigQuery.

Assess Current Processes and Technological Landscape

Assessing the current technological landscape involves documenting what data you have, where it lives, and how it is used. You must document your existing data warehouse schema, identify all source systems, and map the data flows between different components. This technical inventory determines migration complexity and ensures compatibility with GCP services like BigQuery.

There are two main approaches to migrating data workloads. An offload approach moves the schema and data to BigQuery while keeping the original upstream pipelines active, using incremental copies to synchronize data. A full migration moves both the data and the upstream pipelines to Google Cloud, allowing the legacy system to be retired. Organizations often start with an offload for specific use cases and then fully migrate them in later phases to reduce risk.

A critical part of this assessment is examining existing data pipelines. These can be batch (scheduled) or streaming (real-time). You must identify dependencies between pipelines, including fan-in (multiple pipelines merging) and fan-out (one pipeline triggering multiple others) relationships. Understanding these dependencies helps identify bottlenecks and inefficiencies, such as dependencies that exist only due to old resource limitations rather than true data relationships.

You must also document current ETL (Extract, Transform, Load) processes, which often run outside the data warehouse, and ELT (Extract, Load, Transform) processes, where data is loaded first and transformed inside the warehouse using SQL. Assessing whether CDC (Change Data Capture) patterns are used to track data changes is also important. Each approach has different implications for migration, as cloud-native services like BigQuery offer serverless, massively parallel processing that can handle both ELT and concurrent queries more efficiently.

Formulate a Target State Migration Plan

Formulating the target state plan involves creating a phased migration roadmap. You must choose between starting with an offload or a full migration for each use case. An iterative approach ensures zero downtime and minimizes operational risk. During an offload, incremental synchronization keeps the cloud and legacy environments aligned, while a full migration redirects upstream pipelines directly to Google Cloud, allowing legacy tables to be retired.

Migrating data pipelines requires choosing between transformation patterns. ETL transforms data using external compute engines before loading. ELT leverages the parallel processing power of BigQuery to run transformations using SQL after loading. Implementing Change Data Capture (CDC) helps feed clean, historical versions of data directly into target systems in real-time. Selecting the right pattern optimizes performance and reduces operational overhead.

The plan must also address pipeline dependencies by analyzing the orchestration DAG (Directed Acyclic Graph), which represents task workflows. To mitigate the risk of cascading failures from legacy dependencies, teams should optimize using a phased approach:

1. Migrate the legacy orchestration structure as-is to minimize initial risk.
2. Analyze and parallelize tasks to maximize cloud scalability.
3. Extract common tasks into their own DAGs to simplify maintenance.

Once workloads are in BigQuery, you can gradually evolve table schemas. Using facade views—a design pattern that masks underlying structures—protects downstream analytics tools from these structural changes. To deploy these cloud resources securely and repeatedly, teams should adopt Infrastructure as Code (IaC), managing infrastructure through machine-readable definition files. Implementing Terraform prevents configuration drift and automates compliance across environments.

Planning migration and validation to Google Cloud (e.g., BigQuery Data Transfer Service, Database Migration Service, Transfer Appliance, Google Cloud networking, Datastream)

Implement Continuous Data Validation and Replication Strategies

Continuous data validation and replication ensure data remains consistent, accurate, and available throughout the migration to Google Cloud. This involves implementing robust validation frameworks and choosing replication technologies that match business requirements and data characteristics.

Change Data Capture (CDC) is a software design pattern used to track data changes from source systems in real-time. Google Cloud’s Datastream service enables seamless CDC implementation, continuously replicating data from on-premises or cloud databases to GCP services like BigQuery, Cloud Storage, and Cloud SQL. Key benefits include minimal downtime, as changes are captured continuously; data consistency, ensuring both historical and current data are properly migrated; and real-time synchronization, keeping target systems up-to-date throughout the migration lifecycle.

A comprehensive validation framework includes automated monitoring and reconciliation processes to verify data integrity. This involves automated checks that compare record counts, validate schema consistency, and verify data values between source and target systems. Organizations should implement reconciliation queries to identify discrepancies like missing records or data corruption that may occur during transfer.

When planning migration, organizations choose between an offload approach, which migrates schema and data first and keeps incremental copies synchronized, and a full migration approach, which migrates both the use case and its upstream pipelines to deprecate the legacy system after validation. For continuous replication, data pipeline patterns must be selected based on data volume and transformation needs:

• EL (Extract and Load): Extracts and loads data without transformation.
• ELT (Extract, Load, and Transform): Loads data first, then transforms it using SQL in the warehouse.
• ETL (Extract, Transform, and Load): Transforms data outside the warehouse before loading.

Design Network Architecture and Connectivity for Secure Data Migration

Network architecture is foundational, impacting the security, cost, and performance of moving data to Google Cloud. A well-designed network establishes secure, high-throughput pathways while minimizing latency and egress costs, accounting for the data's source, volume, and business uptime requirements.

To build these pathways, you configure core Google Cloud networking services. A Virtual Private Cloud (VPC) provides the isolated network foundation. For connectivity to on-premises environments, you choose between Cloud VPN for encrypted tunnels over the public internet and Cloud Interconnect (Direct or Partner) for dedicated, high-bandwidth, low-latency connections.

The network design must facilitate migration tools and ensure data integrity. Key principles include minimizing latency and egress costs by preferring Cloud Interconnect over VPN for large-volume migrations, and establishing secure pathways with correct VPC firewall rules, IAM policies, and potentially VPC Service Controls to prevent data exfiltration. The network must also support the chosen migration service, such as Database Migration Service, which requires specific configurations like VPC peering between source and Cloud SQL instances.

Before migration, the network requires thorough preparation. This involves re-evaluating any existing Cloud Interconnect or VPN connections and potentially redirecting them to the new target region. Teams must test connectivity and performance to set realistic timelines and identify bottlenecks. The design should also support a rollback strategy, maintaining or quickly re-establishing connectivity to the source environment if needed to ensure business continuity.

Evaluate and Select Migration Services Based on Data Type and Volume

Selecting the right migration service depends on data volume, network bandwidth, and available transfer windows. Google Cloud offers services optimized for specific source types and transfer methods.

For automated scheduled data ingestion from SaaS applications or legacy warehouses like Teradata and Redshift, use BigQuery Data Transfer Service (DTS). For online data transfers from other cloud providers or on-premises filesystems to Cloud Storage, use Storage Transfer Service (STS). When network bandwidth is limited and datasets exceed 20 TB, Transfer Appliance provides secure, physical hardware for offline data transfers.

For online transfers, Storage Transfer Service (STS) or the Google Cloud CLI (using gcloud storage for transfers under 1 TB) are used. STS is designed for large-scale, multi-threaded parallel transfers up to petabytes. These online transfers can be controlled by a bandwidth cap to prevent impact on live workloads. To minimize latency and costs, the target Cloud Storage bucket should be in the same region as the destination BigQuery dataset.

Migrating structured data warehouses requires special configurations. Migrating from Teradata uses BigQuery DTS combined with an on-premises migration agent for partition-by-partition extraction. For Amazon Redshift, BigQuery DTS initiates an unload to an intermediate Amazon S3 bucket before loading into BigQuery. These managed workflows support automatic schema mapping to native BigQuery types.

After transfer, validating the migration's success is critical. Engineers can use the Data Validation Tool (DVT), an open-source Python CLI tool, to automate multi-level comparison of source and target tables. To optimize query performance in BigQuery, teams should evaluate denormalization strategies, such as using nested and repeated fields instead of traditional star schemas.

Key Facts

• Stakeholder and user-centric analysis identifies functional and non-functional requirements to ensure a GCP data migration aligns with business objectives and user needs.
• An offload migration copies schemas and data to BigQuery while keeping source pipelines active, whereas a full migration redirects upstream pipelines to Google Cloud to retire legacy systems.
• Datastream enables continuous change data capture (CDC) for real-time replication from source databases to Google Cloud services like BigQuery and Cloud Storage.
• Cloud Interconnect provides dedicated, high-bandwidth connections preferred for large-volume migrations to reduce latency and egress costs compared to Cloud VPN.
• Transfer Appliance is the recommended service for offline data transfers when network bandwidth is limited and datasets exceed 20 TB.