Are you considering migrating your data warehouse to Azure but unsure of the challenges you might face? With the rapid growth of cloud services, businesses are increasingly looking to leverage the benefits of improved scalability, cost efficiency, and cutting-edge features. However, a successful migration to Azure is far from a walk in the park.

After being part of migration projects where organizations have migrated their SQL Server -based data warehouses to Azure, I have collected some lessons learned to help you plan and execute data warehouse migration projects. In this blog post, we’ll dive into the common pitfalls and obstacles organizations face during data warehouse migration to Azure and provide you with valuable insights to overcome them and ensure a seamless transition.

1. Underestimating the complexity

One of the most common pitfalls in data warehouse migration to Azure is the failure to plan the scope of the migration. It’s easy to underestimate the complexity of the migration process, resulting in a lack of clarity about what needs to be migrated, how long it will take, and what resources will be required. This can lead to budget and time overruns.

The only way to prevent this is to critically assess the current situation of the data warehouse before deciding to migrate the data warehouse using a lift and shift approach.

2. Move technical debt to the cloud

For data warehouses, technical debt refers to the cost of poor-quality code, that will make the operation and further development more difficult. For example, hard-coded values, undocumented procedures, and complex processes can be examples of technical debt. It’s not once or twice that I have found hard-coded dates or other values that have been left in the ETL pipelines. Technical debt accumulates over time and can slow down development, increase maintenance costs, and reduce overall system quality if not addressed.

As data warehouses have been in development for years, this affects many data warehouses. Historically data warehouse development has not been on the leading edge when it comes to adopting modern development methodologies and processes. This means that in many data warehouses, the database and ETL procedures might not be under version control and the deployment process between development, testing, and production environments might be manual if exists at all.

When faced with this kind of situation, it’s important to determine whether it’s more efficient to rearchitect the platform or to perform a lift and shift migration. Rearchitecting involves analyzing the data warehouse and designing a new architecture that utilizes Azure’s capabilities. This can help to reduce costs in the long term but can be more time-consuming. Lift and shift migration involves moving the data warehouse to Azure without any significant changes to the architecture. This is a faster and less expensive option, but it may leave too much technical debt to slow down future development.

The right choice ultimately depends on the specific case at hand.

3. Overlook the unknown unknows

Unknown unknowns are things that we don’t know we don’t know. They can be potential risks, hidden assumptions, unexplored opportunities, or undiscovered knowledge that are difficult to anticipate, detect, and prepare for. While it’s important to fully prepare for things we don’t know, we can anticipate that unknown unknowns exist.

This can be one of the most overlooked pitfalls is the assumption that everything about the current system is known and well-documented. Documentation is essential for understanding the data warehouse and its dependencies. However, documentation can become outdated, inaccurate, or incomplete over time, leading to unknown unknowns in the migration process.

To avoid this you need to plan for unexpected things beforehand and allocate time for unexpected things. Also, reviewing the data lineage, data quality, and data transformation documentation with the actual data warehouse to ensure that they are accurate, up-to-date, and complete it’s important. Most critical is to identify any undocumented processes or dependencies and address them before starting the migration.

4. Managing integrations

Many data warehouses not only serve the purpose of data ingestion and reporting but also as an integration platform between systems. In many companies, a data warehouse has been an easy way to integrate data between operational systems, such as ERP, CRM, and HR, and these integrations might not have been documented and maintained in a way that is up to date. For example, the data warehouse could collect new won opportunities in CRM and send them to the ERP system for the creation of new orders.

When migrating the data warehouse to Azure this can become an issue if these requirements happen to pop up without prior knowledge. To avoid this pitfall, it is important to identify all the systems that are integrated with the data warehouse and plan the migration accordingly. This includes identifying any dependencies between the systems, determining the impact of the migration on the integrations, and developing a plan to mitigate any disruptions.

Also, careful consideration should be taken on whether to use data platform as a basis for these kinds of integrations.

5. Development processes and DataOps

Last topic is not really a pitfall rather more a recommendation to implement during the migration. As mentioned before, many current data warehouses don’t use modern development methodologies. Migrating to the cloud provides an excellent opportunity to adopt modern data operations methodologies, such as infrastructure as code and continuous integration/continuous deployment (CI/CD). Adopting these practices can streamline the development process, improve collaboration, and reduce risks associated with manual configuration and deployment. For more details on infrastructure as code, continuous integration, and continuous deployment refer to the blog post by Lauri Lehman about DataOps.

Summary

To conclude, in many cases, migrating an existing data warehouse to Azure without rearchitecting at least part of the current solution can be time-consuming and difficult. The most important part of decision-making should be the amount of technical debt in the data warehouse and whether the current data warehouse model meets all of the required business cases.

In cases when the data model has evolved into a large spider web-like structure, nobody seems to know how the integrations between systems are built and what are the business requirements for data warehouse datasets it’s probably better to start with a greenfield data platform development project in Azure.

Zure can help to assess the state of your current data warehouse with our Analytics Assessment to decide whether it’s better to migrate or rebuild.

Read more about our Data Solutions and let’s get in touch!