Planning BigQuery Warehouse Migrations

Assessing current processes and the technological landscape is the technical inventory that decides how hard a warehouse migration will be and which Google Cloud services fit. The work maps how data moves today to find bottlenecks, catalogs systems and schemas to size the effort, and scores compatibility to choose a target architecture.

1.1Mapping Data Workflows, Dependencies, and Integration Points

Data workflows are modeled as directed acyclic graphs (DAGs) where data flows 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.

Pipelines divide by timing and procedure. BigQuery (opens in a new tab) 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 and integration points 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, Schemas, and Infrastructure

Inventorying source systems, schemas, and infrastructure builds an Existing System snapshot from analyzed logs for a defined time period. This 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. Apache Hive (opens in a new tab) warehouse detail, data pipelines (opens in a new tab) behavior, and schema and data transfer (opens in a new tab) scope all feed into that sequencing decision.

1.3Determining Migration Complexity and Compatibility with GCP Services

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.

The choice between offload and full migration sets pipeline scope and decommissioning. 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. Compatibility then follows a lift-and-shift or rearchitect decision, where lift and shift keeps the existing ETL/ELT tool and changes storage stages to BigQuery, and rearchitecting uses managed services such as Cloud Data Fusion, Dataflow, and Cloud Composer. Oracle (opens in a new tab) capture, JDBC extraction, and Airflow orchestration are common patterns in that rearchitected target.

Conducting stakeholder and user-centric analysis turns interviews, workflows, and system evidence into a migration plan that reaches a defined future state on Google Cloud. The analysis documents what stakeholders need the system to do and how well it must do it, maps who interacts with data and how, and converts those findings into measurable checks for a BigQuery migration.

2.1Documenting Functional and Non-Functional Requirements

Functional and non-functional requirements describe required data movement and required operating qualities. 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.

BigQuery (opens in a new tab) planning must account for where transformation runs. ETL technologies run outside the data warehouse, so warehouse resources stay available for concurrent querying instead of data preparation. The tradeoff is that transformations expressed outside the warehouse require additional tooling and languages beyond SQL. Stakeholder interviews must therefore capture both the business rules that must be preserved and the performance and operability limits that determine whether an offload or a full rebuild is acceptable.

2.2Mapping User Personas to Interaction Patterns

User personas for a warehouse migration are the upstream producers, downstream consumers, and pipeline builders who interact with existing data systems. Upstream producers own transactional systems such as relational databases and OLTP databases and write data through ingestion pipelines. Downstream consumers run scripts, queries, dashboards, and business applications that read from warehouse tables. Pipeline builders and operators create and monitor the DAGs that move and coordinate data.

Interaction patterns separate into two DAG roles with different dependencies. A data flow DAG moves data from source systems through transformations to a sink, where sources are usually transactional systems and the sink connects to the data warehouse. An orchestration or control flow DAG coordinates movement between pipelines and other systems rather than moving row data itself. Sources within a DAG typically pull data from source systems, so data flows from the source system to the source node, then through each transformation in order, then from the sink to the data warehouse.

Batch and streaming patterns further distinguish interaction needs:

• Batch data pipelines run on data collected over a period of time, for example once a day.
• Streaming data pipelines handle real-time events, for example CDC row changes generated by OLTP databases.
• ETL pipelines pull from as few source systems as possible to reduce failures from unavailable systems.
• Multi-pipeline designs separate copying data from the source system from later transformation stages.
• Downstream processes that must be inventoried and validated include scripts, queries, dashboards, and business applications.

2.3Translating Findings Into Measurable Success Criteria

Measurable success criteria turn stakeholder needs into counts, times, and usage thresholds drawn from the migration assessment. The Existing System assessment provides CPU utilization heatmaps by hour, queries by hour and day with CPU utilization, queries by type with CPU utilization, query duration histograms, application details with reporting versus ETL breakdown, and storage views for databases by volume, tables by access rate, and application IP addresses. The BigQuery steady state assessment provides transformation suggestions such as materialized views, partitioning and clustering candidates, low-latency queries for BigQuery BI Engine, and tables with no usage or no writes. The Migration Plan assessment provides automatically translated query counts, queries requiring human intervention for lexical violations or reserved keywords, user-defined functions and procedures needing review, table update schedules, and estimated time to migrate each table with the BigQuery Data Transfer Service.

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. Heavily used tables signal many dependencies and need extra planning, while large-volume tables signal longer extraction time and earlier sequencing. Translation buckets that group similar query patterns show whether the approach holds across users, applications, and affected tables. Validation (opens in a new tab) then confirms that each target was met before cutover.

2.4Selecting the Migration Path From Stakeholder Constraints

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 from the existing warehouse to BigQuery, an incremental copy keeps the warehouses synchronized, and downstream processes are migrated and validated while upstream pipelines keep writing to the old warehouse. When a use case is fully migrated, its upstream pipelines move to Google Cloud and data is ingested directly into BigQuery, which allows the corresponding legacy tables to be deprecated. Full migration requires offloading first, and an iteration can offload only, fully migrate a previously offloaded use case, or fully migrate from scratch by offloading first in the same iteration.

Lift and shift preserves existing integration tooling when that tooling already supports BigQuery. Teams keep the ETL/ELT platform as is and change storage stages to BigQuery, or install the vendor tool on Compute Engine or from Google Cloud Marketplace (opens in a new tab) 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:

Data pipelines (opens in a new tab) for Oracle (opens in a new tab) sources commonly use the first two options for movement and the third option for scheduling.

2.5Evolving the Schema Without Breaking Stakeholder Workflows

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. This abstraction layer isolates systems from change until the new schema is ready to surface.

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.

A target state migration plan is a phased roadmap that moves each analytics use case from its current warehouse and pipelines to a desired state where upstream processes write directly to BigQuery and downstream processes read from BigQuery. The plan specifies the target Google Cloud architecture for each pipeline, the required data and schema transformations, the order in which use cases move, and how risk is controlled until the legacy warehouse can be switched off.

3.1Phased Roadmap With Three Coexisting Flows

The phased roadmap organizes migration by use case, where a use case is a bounded set of tables plus its upstream ingestion and downstream consumption. During execution, three data flows can coexist in the same environment while different use cases sit at different stages of maturity. Data flows from upstream producers either to the legacy warehouse or to BigQuery, and downstream consumers read from either system depending on how far that use case has progressed.

• Existing flow: Upstream processes still write to the legacy warehouse and downstream processes still read from the legacy warehouse, with no BigQuery involvement.
• Offloaded flow: Upstream processes still write to the legacy warehouse, data is incrementally copied from the legacy warehouse to BigQuery to keep them synchronized, and downstream processes read from BigQuery.
• Fully migrated flow: Upstream processes write directly to BigQuery and downstream processes read directly from BigQuery, with no reads or writes to the legacy tables for that use case.

An execution iteration moves one or more use cases forward, then repeats until every use case is fully migrated. First, transfer the use case schema and data as is to BigQuery (opens in a new tab) and establish incremental synchronization. Then, configure test upstream processes to write to BigQuery instead of or in addition to the legacy warehouse, validate, and cut over production upstream and downstream processes to the BigQuery API. Only after all use cases reach the fully migrated flow can the team deprecate legacy tables and switch off the old warehouse to reduce overhead and cost.

3.2Target Architectures for Pipelines

The target architecture decision for each pipeline is whether to lift and shift the existing integration tool or to rearchitect onto Google Cloud managed services. Lift and shift keeps the current ETL or extract, load, and transform (ELT) platform and only changes storage stages to BigQuery, or reinstalls the same vendor tool on Compute Engine or from Google Cloud Marketplace if the license allows it. Lift and shift preserves skills and behavior but preserves legacy limits, while rearchitecting trades rewrite effort for autoscaling, serverless operation, and native BigQuery integration.

Rearchitected data pipelines (opens in a new tab) divide work among three managed services by the shape of the work. Cloud Data Fusion provides visual, plugin-based batch and streaming replication, Dataflow provides coded batch and streaming processing, and Managed Service for Apache Airflow provides orchestration of multi-system workflows through DAGs.

Oracle (opens in a new tab) replication and JDBC extraction are representative source patterns for the first two rows, while cross-system scheduling belongs in the third row.

3.3Controlled Schema Evolution After As-Is Transfer

Controlled schema evolution after as-is transfer is the sequence that improves table design without breaking producers or consumers. The schema sits at the core because it defines entity relationships and affects every producer and consumer, so the plan evolves one use case at a time and can stop at an intermediate step. Facade views provide the key isolation boundary: the views present a stable, debt-free schema to consumers while tables and view definitions change underneath.

1. Transfer as is: Confirm upstream writers and downstream readers for the use case already use BigQuery tables, or start from downstream-reads-only and apply only downstream guidance.
2. Apply light optimizations: Re-create tables from query results with partitioning and clustering, then redirect upstream and downstream processes to the new tables.
3. Create facade views: Publish views that mask table complexity and always return the same results while the underlying implementation evolves.
4. Transform downstream processes: Move selected dashboards, scripts, and applications to read from facade views first, because visible business value appears faster than with upstream-only changes.
5. Transform upstream processes: Move selected ingestion processes to write into the new schema, completing the evolution for that use case.

3.4Pipeline-Specific Transformation Requirements

Pipeline transformation requirements depend on the source technology, because each legacy pattern has a different path to Cloud Storage and BigQuery. Cloud Storage acts as the landing zone that replaces Hadoop Distributed File System (HDFS) for files, while the hive.metastore.warehouse.dir property can point Hive warehouse storage to a Cloud Storage bucket. Sources in a data flow DAG typically pull from transactional systems such as a relational database, and sinks connect to the warehouse, so the plan must minimize the number of sources per pipeline to reduce failures from unavailable systems.

• Sqoop ingestion: Modify jobs to import into Cloud Storage by copying Hadoop DistCp output files to Cloud Storage, by writing directly with the Cloud Storage connector for Hadoop or Spark, or by submitting Sqoop jobs through Managed Service for Apache Spark without managing a Hadoop cluster.
• Apache Spark SQL and HiveQL logic: Automatically translate to GoogleSQL with the batch SQL translator or interactive SQL translator, or keep the logic and run it with Managed Service for Apache Spark plus the BigQuery connector for Spark.
• Hive ETL jobs: Convert to BigQuery jobs with the batch SQL translator, read and write BigQuery from Spark with ephemeral clusters, rewrite with the Apache Beam software development kit (SDK) for Dataflow, or rewrite with Apache Beam SQL.
• Orchestration: Manage the resulting ETL pipeline with Managed Service for Apache Airflow and Managed Service for Apache Spark Workflow Templates, including converting Oozie workflows to Airflow with the Oozie-to-Airflow tool.

Batch pipelines run on data collected over time, such as once per day, while streaming pipelines handle real-time events such as CDC row changes from OLTP databases. ETL runs transformation outside the warehouse so warehouse resources stay focused on concurrent querying, at the cost of requiring non-SQL tooling, whereas ELT loads first and transforms inside BigQuery. Standard data movement without custom code can use Dataflow templates, and schema-drift tolerance can use select * from in the source plugin with no fixed output schema on source and sink plugins.

3.5Risk Mitigation and Validation of the Desired State

Risk mitigation in the target state plan keeps the legacy environment running while each change is proven in BigQuery. Incremental copy keeps offloaded data synchronized, test upstream writers run in parallel before production cutover, and facade views isolate downstream systems from table changes until the new schema is ready to surface. The dependency order matters: validate downstream reads from BigQuery before migrating upstream ingestion, deprecate a legacy table only after its use case ingests directly into BigQuery, and switch off the old warehouse only after every use case is fully migrated.

Validation (opens in a new tab) confirms that the desired state holds for each use case and for the program as a whole. A use case is complete when production upstream processes write to BigQuery tables, downstream scripts, queries, dashboards, and applications read from BigQuery and return correct results, and the corresponding legacy tables are no longer needed. The program is complete when no existing or offloaded flows remain, all selected use cases show fully migrated flow, and ongoing evolution such as partitioning, clustering, and facade-view refinement continues inside BigQuery.

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.
• Facade views isolate downstream readers from table changes by presenting a stable schema while underlying BigQuery tables evolve.
• Lift and shift keeps the existing ETL/ELT tool and changes storage stages to BigQuery, while rearchitecting uses Cloud Data Fusion for visual replication, Dataflow for coded batch and streaming, and Managed Service for Apache Airflow for orchestration.