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.