I am working on a project to update a legacy ETL infrastructure that supports a number of clients, each with a slightly different setup.
Constraints that cannot be changed:
- Source data can come from flat files over sftp and then to S3, from MSSQL databases, or events published through webhooks.
- Source data ranges in size from ~60GB to ~1TB in size. Though we are grabbing diffs that are usually >5GB in total size. And the final databases for our clients are sized roughly the same.
- The current destination for all of our data is two-fold: a MySQL database (one for each client, may or may not be on a shared server) and second, a filtered set of data stored in a sql file on S3.
The existing system is designed to only load data from files so even when we have the data in a MSSQL RDS instance, we first extract the data to a series of files to be processed. Data is then loaded in through the MySQL
LOAD DATA INFILE command, tranforms performed to get the raw data from the source system linked to the data in our system, and finally the data is moved from staging tables to the production tables. We are not doing any aggregations and so all of the data stays very close to its raw form.
One large problem we have with these existing systems is that as the data grows we are forced to increase the amount of RAM specifically because query performance degrades in MySQL as data is no longer able to be stored in memory.
In researching possible solutions, I've come to believe that parallelizing the ETL tasks using micro-batches would be a better approach as it should allow us to process increasingly large datasets without a linear increase in cost and/or processing time.
Our devops team is pretty invested in the AWS ecosystem so I've been concentrating on using AWS Lambda for parallel processing. I've come across some posts talking about using the MapReduce technique with AWS Lambda to perform the data processing.
What I'm wondering is whether Lambda with a MapReduce-like solution is really the correct solution when I'm really just moving, filtering, and tranforming data and not building any sort of aggregate indexes?