Businesses migrate their data warehouses for many reasons. Some are looking for improved performance around data loading, complex queries, and ELT. Others are focused on minimizing the cost of storage, reporting, and batch ELT processing. Sometimes it’s a combination of both these motivators, plus the need to reduce admin tasks and boost automation. Whatever the reasons may be, data warehouse migration can meet your desired goals when done correctly.
Setting Yourself up for Success
Just like any other data project, a data warehouse migration is most successful when the right team members play their part. These include the business owner, project manager, cloud engineer, developers, business analysts, and other individuals whose input adds value to the project.
With the right team and proper planning, the last outlier for a successful project relies on the tools and technologies used to implement it. ETL solutions like StarfishETL can prepare data, handle complicated use cases, extract data mappings and transformation logic, and test data quality as you migrate. A specialized data warehouse automation tool may also be helpful to structure data for BI processes in the new warehouse. The right solution(s) speeds up the migration process and minimizes the risk.
It’s also important to go into the project with clear goals, a budget, and a thoughtful approach. These help both you and your potential vendor understand the ability of a solution to meet your needs.
You have several options for how to approach your data warehouse migration:
1.) Move your existing data warehouse as-is
2.) Simplify the existing warehouse and then migrate it
3.) Do a complete redesign of the data warehouse and then migrate
Whichever data warehouse migration approach you choose, here are a few must-do tasks to prepare.
#1 Assess the Existing Warehouse
Going into the data warehouse migration project, it’s important to have a full picture of what currently exists. Detailing the current architecture, data stores, schema, business logic, data flows, warehouse operations, dependencies, and DBMS functionality not only gives your team solid ground to stand on for assessing what should come next, but it also highlights the strengths and weaknesses of your setup.
The whole point of migrating the data warehouse is to improve something (or everything!) about how that warehouse functions for your business, so a clear understanding of where the current system is succeeding and failing is a must for informing the strategy to do that.
Teams that initially thought they were going to migrate the data warehouse as-is might be surprised to realize it needs some improvements and simplifications, or even a complete overhaul. How might those changes affect your proposed budget and timelines? Now—before you actually start the project—is the time to figure that out.
#2 Detail Your Data Discovery
Just as you want the full picture of your current data warehouse setup, you also need a full picture of the data that lives within it. Profiling and auditing source data will identify anomalies early, so you can ensure you’re choosing an ideal migration method and mapping clean data.
Not only does this make the data warehouse migration process less stressful, but it reduces the risk of added costs. Some estimates reveal that the cost of code amendments during testing can be reduced by up to 80% when proper data discovery and auditing is performed.
#3 Define the Scope and Mapping Specifications
The scope of your data warehouse migration should not only define what will be migrated but how much, and when. Are you migrating incrementally, or all at once? Some businesses choose to focus on the high-priority areas first, like migrating their data marts individually and then looking to the data warehouse itself. What are the pros and cons of this approach for your project goals? You’ll want to speak with your migration partner to determine what’s best.
In addition, the mapping specifications are a must-have for ensuring your source data fits the target system. Mapping specs get converted into migration code that is then verified in the test environments to find errors. This is where your ETL tool will be handy. It can clean the data, restructure it for the destination system, and match functions to your mapping specs.
#4 Plan for ETL and Version Upgrades
ETL and database version upgrades are part of your data warehouse migration must-haves. ETL version upgrades will happen in the existing IT environment. Database version upgrades will happen in either the new environment, or in-place. The ETL vendor should support the ETL upgrade process and make sure you can restore the environment if something goes wrong with that process.
If you’re upgrading your database version in-place, the database vendor will provide that support. The database version upgrade could be seamless, or complications and installation failure could mean you end up installing a completely new database. In any case, be prepared for potential downtime.
#5 Establish a Proof-of-Concept Lab
Mitigate risks by letting your team conduct tests, find and fix issues, and validate migration approaches in a safe environment before you forge ahead with the data warehouse migration.
Trying things out to see what works will reveal better ways to automate tasks and implement best practices. By streamlining everything in this proof-of-concept lab first, you’ll improve the performance of your new data warehouse and keep costs to a minimum. It’s also a great way to keep tabs on how well you’re living up to your defined migration strategy. Assign owners to your tests to create accountability for achieving the targets you outlined in your strategy.
Prepare and Plan for Success
An informed, connected internal team, and a knowledgeable and efficient migration partner will join together to help you prepare, plan, and ultimately succeed in migrating your data warehouse. StarfishETL can help you prepare your data for this undertaking by querying data sets, cleaning and validating what you have, and automating many of the tasks crucial to a successful migration. Reach out to our team at firstname.lastname@example.org today for a free consultation on your upcoming project.