Data Warehouse Migration Strategy: How to Plan SQL, Pipeline, and Reporting Changes

Moving tables is only one part of a data warehouse migration. Production analytics also depends on procedures, transformation rules, schedules, access policies, semantic models, exports, and people who know why a number is calculated a particular way. A table-level plan can therefore finish successfully while a critical report still fails.

This guide treats an analytical workload as the unit of planning. If you need a refresher on the broader components, see the enterprise data warehouse glossary. Identify the changes behind one business output, choose a migration sequence, and define the evidence required to complete the first workload without pretending that an initial assessment can provide a reliable program-wide schedule or budget.

Key takeaways

• Plan around reports and analytical jobs that people use, then trace their dependencies back to sources.
• Separate syntax conversion from changes to business logic and operating behavior.
• Choose a pilot that exercises meaningful shared dependencies, not merely the easiest tables to copy.
• Complete each wave by transferring ownership and retiring obsolete processing, not only by reconnecting a dashboard.

Define the migration outcome and constraints

A migration needs an outcome that is more specific than “move to the cloud.” State the current limitation, the result that justifies change, and the service levels that cannot regress. For example: move the daily finance reporting workload to the target warehouse while preserving its approved calculation rules, morning refresh deadline, access boundaries, and export process.

Also record the alternative. If tuning or resizing the current platform can solve the problem, migration is not automatically the best response. This keeps optional modernization separate from necessary compatibility work.

Define constraints:

• Which reports and decisions depend on the workload?
• What data freshness and processing windows must it meet?
• Where may data be stored and who may access it?
• Which source systems, network paths, and maintenance windows are fixed?
• Which skills and owners are available during investigation and rollout?

Keep cost as a set of categories at this stage: assessment and implementation, temporary parallel operation, compute, storage, transfer, monitoring, and support. Vendor prices alone do not show the cost of adapting custom logic or resolving unclear ownership.

Label target-platform assumptions as tested or untested. The outcome statement should specify what must remain true, what may change, and what needs proof. It is a planning boundary, not a platform commitment.

Map the workloads and dependencies that must move

Start inventory from a business output, such as a daily sales report or a churn-scoring job, rather than from database schemas. Trace that output through the BI model, views, transformations, procedures, ingestion jobs, and source systems that produce it. Metadata and query logs help reveal use, but interviews and operating records are still necessary. Google’s BigQuery migration assessment documentation uses both metadata and query logs, whose results are limited to the analyzed log period. Do not exclude an inactive object without an owner’s confirmation.

For each workload, capture:

• sources, connectors, ingestion mode, and credentials;
• tables, views, data types, transformations, and shared calculation logic;
• stored procedures, external scripts, orchestration, and job dependencies;
• schedules, refresh windows, rerun procedures, and manual interventions;
• semantic models, dashboards, extracts, file exports, APIs, and other consumers;
• owners of business logic, access, operation, and acceptance;
• apparently unused objects, with the evidence and owner approval needed before exclusion.

A compact workload map makes the scope discussable:

The first row shows why a table list is insufficient. Orders and returns may copy correctly, yet the report is wrong if the shared job applies adjustments after the copy window. That dependency changes design and sequence.

A published Datatonic case says localsearch engaged it to design and migrate warehouse systems to Google Cloud, covering priority use cases, data models, and ETL pipelines. It shows comparable commissioned scope beyond copying tables, not a universal result; no project date, architecture, or performance figures are inferred.

Choose the migration scope and sequence

Two decisions are often collapsed into the word “phased.” Keep them separate.

The first is how much the implementation changes. A minimally adapted move preserves logic where the target supports it. Replatforming changes the constructs required for compatibility, such as procedures, types, connectors, or scheduling. A redesign changes data models or business processing more deeply. The right depth can differ by workload. A stable regulatory calculation may favor limited change, while an unsupported monolithic procedure may justify decomposition.

The second decision is sequence. Common options include:

• Workload groups: move a complete report or analytical job with its dependencies. This creates a clear acceptance boundary but may duplicate a shared dependency temporarily.
• Pipeline foundation first: establish ingestion, transformations, and governed models before reconnecting consumers. This reduces temporary interfaces but delays visible user results.
• User scenario first: expose a report early through replication, federation, or another compatibility layer, then move its upstream processing. This demonstrates value sooner but creates interim dependencies that must later be removed.

Databricks describes ETL-first and BI-first migration strategies with these broad tradeoffs. Its federation and ingestion examples are Databricks capabilities, not guarantees that every warehouse offers equivalent paths.

Choose a sequence by asking: Which output needs value first? Which shared component constrains several outputs? How much temporary compatibility can the team operate safely? What evidence will show that an interim component can be retired?

Phasing describes order, not modernization depth. Make both choices explicit so stakeholders can see whether effort comes from compatibility, modernization, or temporary coexistence. Cloud adoption and a complete redesign remain options to test, not predetermined conclusions.

Plan SQL and pipeline changes before moving data

Convert every discovered incompatibility into an owned engineering task with a verification method. “Translate SQL” is too broad to estimate or accept.

SQL and data model

Compare source and target behavior for types, precision, time zones, NULL handling, collations, functions, DDL and DML, transactions, and procedures. Distinguish two results:

1. Syntactic conversion: the statement parses and runs on the target.
2. Business equivalence: given controlled inputs, it produces the approved result with the required edge-case behavior.

The second result cannot be inferred from the first. For a concrete SQL Server to Fabric example, Microsoft’s migration planning guidance maps datetime to datetime2. It also states that Fabric Data Warehouse does not currently support datetimeoffset; the offset must be extracted into a separate column. That is not a cosmetic substitution. If a report defines a business day using local time, losing the offset can move transactions across reporting boundaries.

Pipelines and orchestration

For each pipeline, document changes to connectors, authentication, schedules, dependencies, retries, alerts, and recovery ownership. If custom services or integrations must change, include that backend development work instead of hiding it under “configuration.”

Plan historical and incremental loads separately. Define stable keys, watermarks or change capture, and handling for late arrivals and corrections. Ask: Can a batch rerun without duplicates? How is a partial load detected? Can a backfill use the original transformation version?

Consumers

Beyond BI connections, check semantic models, permissions, refresh credentials, extracts, exports, API clients, and spreadsheets. Record who compares results and who may approve a changed definition.

Use a compatibility register to make these decisions executable:

Automation can accelerate individual tasks, but it does not close the register. Google presents BigQuery Migration Service as separate capabilities for assessment, SQL translation, and data transfer. Even when translation succeeds, teams must still verify calculation semantics, orchestration, security, consumers, and operations.

Prove the approach with one reporting workload

Consider a hypothetical daily sales report moving from a SQL Server warehouse to Fabric. It combines orders, returns, and exchange rates; applies SQL transformations in a scheduled job; and publishes a BI report. This is an instructional scenario, not a reconstruction of a Mad Devs or localsearch project.

First, define the boundary: the approved daily net-sales dataset and report. Dependencies include three feeds, the shared procedure, currency rates, the scheduler, the semantic model, and the finance owner. If another report uses the procedure, preserve a compatible path or move the shared component with both reports.

Second, adapt unsupported procedure constructs, map monetary precision deliberately, and replace datetimeoffset with UTC plus a retained offset where needed. Configure credentials, dependencies, retries, and alerts. The pilot tests a production-shaped chain, not only query execution.

Third, establish the data boundary. Load an agreed historical period, then start incremental orders, returns, and currency updates from recorded watermarks. Make loads idempotent so a failed run can be repeated. Preserve the transformation version used for a backfill.

One meaningful test is a return received after the original sale was reported. If the incremental process merely appends new orders, the prior day remains overstated. The design needs an explicit rule for locating and recalculating the affected sale, and the comparison set must include this case. A second boundary test checks transactions near local midnight so the new time representation does not change the reporting day.

Next, connect a test semantic model, apply representative permissions, and refresh it on schedule. Compare source and target outputs using identical frozen inputs, including totals, exceptions, late returns, exchange-rate corrections, and time-boundary cases. Detailed reconciliation and acceptance evidence belong in a dedicated data migration validation plan.

The pilot ends with a decision. If differences are understood and the operating path is supportable, reuse the pattern for related workloads. If shared procedures, correction handling, or time semantics require extensive exceptions, revise the target design before scaling the migration.

Turn the pilot into a migration project plan

A data warehouse migration project plan should order deliverables by dependency, not table count. The pilot reveals which adapters, test cases, deployment patterns, and monitoring rules are reusable, and which assumptions were unique to one workload.

Group workloads by shared transformations, sources, consumers, and owner availability. For example:

The weekly export follows net sales because it consumes that result. The quarterly extract remains explicit even if recent logs show no use. This is how a dependency map becomes sequence rather than another inventory.

Reuse validated type mappings, connector configuration, deployment automation, reconciliation cases, monitoring templates, and runbooks. A successful sales workload does not prove compatibility for different procedures or latency requirements.

Control source changes during the program. Route schema and calculation changes through the migration backlog, record which implementation receives them, and update comparison cases.

Assign responsibility across data engineering, BI, platform, and business owners. Technical teams prove repeatability; the calculation owner approves meaning. Identify necessary parallel processing, temporary views, replication, or switch windows. Handle live cutover requirements through a separate production database cutover process rather than implying zero downtime.

Estimate from undocumented rules, unsupported constructs, data exceptions, shared dependencies, and operating changes. Include parallel-run compute and support. Table counts help size transfer, but poorly represent engineering effort.

Retire the old workloads and establish ownership

A dashboard pointing to the target does not prove that the old workload can be removed. Retirement requires evidence that every active consumer has moved and that the replacement can be operated and recovered.

For each workload, verify that reports, exports, jobs, and APIs use the target implementation. Check old connection logs, scheduler runs, service accounts, and shared procedures for remaining dependencies. Then stop redundant schedules in a controlled order so both platforms do not continue processing the same data unnoticed.

Name owners for pipelines, data quality alerts, platform monitoring, access, cost review, incident response, and business acceptance. Update runbooks and diagrams to match the implemented system, including manual recovery steps discovered during the pilot. Confirm retention, audit, and restoration requirements before removing source data or infrastructure. Some legacy data may need to remain accessible even after its computation has stopped.

Define an exit condition for every temporary compatibility layer. Without an owner and removal condition, replication, bridge views, and dual reports become permanent complexity.

The final retirement decision should be workload-specific and auditable: all known consumers moved, monitoring is active, recovery is documented, required retention is satisfied, and an accountable owner approves shutdown. Platform-wide decommissioning follows only when no remaining workload depends on it.

A lesson from moving data into an analytical platform

Mad Devs previously described moving data into an analytical warehouse from 1C as part of building a recommender system. The team first transferred data for analysis and later separated product groups for model development. The account also notes that some data was lost during transfer and had to be restored manually.

This was a move into a warehouse for an analytical use case, not a warehouse-to-warehouse migration. It does not establish SQL conversion patterns, migration waves, or target architecture for the scenario in this guide. Its relevant lesson is narrower: exception handling and recovery are part of the migration scope, even when the visible goal is a new analytical capability.

For a warehouse migration, identify which records can be replayed, which require manual reconstruction, who decides that a recovery is complete, and how the exception is documented. If those answers are missing, the load process is not yet operationally complete.

Conclusion

Choose one report or analytical job and build three artifacts: its dependency map, its compatibility register, and an exit condition for the first migration wave. That work will expose whether the main uncertainty lies in SQL semantics, pipelines, consumers, ownership, or recovery. If the calculation owner is unknown or the source of an approved result cannot be identified, resolve that gap before estimating the workload. Teams that need help assessing the current platform, defining target architecture, adapting jobs and integrations, and planning a phased cloud rollout can bring those artifacts into an initial migration discussion.