A Modern Approach to Data Migration
A Modern Approach to Data Migration
The ETL portion of most data projects takes up as much as 70% of the overall time.
Join the DZone community and get the full member experience.
Join For FreeRead the 2019 State of Database DevOps Report for the very latest insights
Many data migration projects seem easy at first. Just take the data from your database and put it into a data warehouse. Simple! But once you start, you realize that there's so much more to it than that. You likely have more data than you thought, in both volume and types of sources. And then getting that data all matched up and into a common schema so it's useful is no easy task. Add in security concerns, resource constraints, and scalability to really see the larger picture. In the end, we're finding that for the companies we've spoken with, the ETL portion of most data projects was taking up as much as 70% of the overall time.
That means there's a lot of room for improvement, for a modern approach.
Old vs. New: A Fresh Look at Data Pipelines
We shouldn't settle for "at least it works most of the time" or even for "good enough." Today's goal should be to spend less time in the ETL part of the process so that you have better data, more data, available sooner.
Having access to better data can have a material impact on your business and even change its culture. Better data leads to:
- Better decision making
- Less arguing (if you know your data is correct and up to date, misinterpretation is less likely, for example)
- More confidence in decisions and direction
The question is, how do we get there? What are some ways we can optimize the ETL portion of data migration?
Data Sources: Legacy and Modern
To help streamline the process, let's first take a look at the types of data sources involved in today's data migration projects.
On the one hand, we have legacy data sources. These are complex, often on-premise databases and systems, with limited data accessibility. Often these systems are being sunsetted and that can even be part of the reason for the migration project.
Example legacy data sources:
- Netezza
- Teradata
- Oracle Exadata
And then there are modern data sources. These include streaming data, applications, SaaS services, modern databases, files, etc. If you perform a data census as part of your data migration plan (which we recommend), you may be surprised at the number of these data sources your company has.
Example modern data sources:
- Salesforce
- MySQL
- MongoDB
- S3/Azure Blob Storage
Now that we have a good understanding of the data sources involved, we see that they don't have a common set of challenges, and there likely isn't a single approach that will work with all of them. The key is treating the two types of data sources differently. We can make substantial improvements to the ETL process based on some helpful strategies for dealing with each type of data source.
Strategies for Migrating Different Data Sources
Legacy and modern data sources have different inherent challenges, and knowing which approach to use for each is key.
Legacy Data Sources
| Challenge | Modern Approach | Traditional Approach |
|---|---|---|
| Migrating to a cloud data warehouse | Phased migration | All at once migration (time consuming) |
| Data Modeling | Use your cloud data warehouse to denormalize the data | Normalize in stage, then migrate (time consuming) |
| Migrating schemas | Automatically map A to B | Manually map A to B |
Modern Data Sources
| Challenge | Modern Approach | Traditional Approach |
|---|---|---|
| Capture all the changes | Reduce overhead by only capturing changes | Copy over all the data, every time (time consuming) |
| Handling semi-structured data sources | Leverage semi-structured data types in your modern cloud data warehouse | Stringify and extract (very error prone) |
| Migrating schemas | Automatically map A to B | Manually map A to B |
The winning strategy is to spend less time on the ETL phase of your data migration project. To do that, pick a tool designed to handle the different data sources properly.
Read the 2019 State of Database DevOps Report for latest insights into DevOps adoption among SQL Server professionals, and the benefits and challenges of including the database in DevOps initiatives
Like This Article? Read More From DZone
Published at DZone with permission of Garrett Alley , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}