There are many ways to do ETL (extract, transform, load). Probably if your transform function is a no-op, you are doing it wrong though (see below for why). Extract and load are the easy parts, generally.
Your database has cursors. You can use those to dump data efficiently. Dump it to a file. Put it somewhere. Use Kafka or something similarly fancy if you need to juice up your resume. But otherwise, files actually go a long way. Maybe put them in S3. Anyway, that's extract covered. If this code is in any way complicated, you are doing it wrong.
Now process that stuff item by item by line. Chunk it up. Use a nice framework to make this concurrent and fast if you must. There are loads of options for this. That's where your transform logic lives. This is where you do all the clever stuff you need to do to sure querying is fast. This bit can be expensive and complex. This is why you don't want to run this on your database server while it is serving traffic, typically. Bad idea.
The output of transform can be another file.
The load it into wherever it needs to go. This code too should be simple simple. Use batch/bulk inserts. Whatever works fast. I've seen systems load data by the GB per second. That only works if it does absolutely nothing smart whatsoever.
Here's the key advice: don't do your ETL in one function. Separate those concerns from day 1. Long term they are probably not going to run on the same hardware.
Data transformations are what will make the difference. Indexing exactly what you store in your database is rarely optimal.
Things you can do during data transformation
- merge in other data from elsewhere to make it easier to search on that data.
- calculate expensive things that are hard to calculate at query time, things like page rank, quality scores, embeddings, etc.
- denormalize things from various database tables into your search index (most search engines don't really do joins, storage is cheap)
- filter out stuff you don't actually search for
- etc.
The reason ETL is important is that things change. Your data model might change (new fields, tables, whatever). Your transformation logic might change (new features, bug fixes, etc.). Your business logic might change. Etc. When stuff changes, you probably should reindex all of your data. And your ETL pipeline needs to be ready for that. Recreating indices from scratch needs to be a completely routine thing. Quick and easy. If it's not, you are never going to do it and it's always going to be inconvenient. It will block all progress in your team.
Your ETL needs to have two modes: incremental and full reindex.
I consult clients on this stuff, well over 90% of my clients do this wrong and then get blocked on not being able to evolve their indexing strategy or do rapid iteration or experiment with how their search works.
I had a client recently that was complaining it took 24 hours to rebuild their index. And it wasn't because they had a lot of data. But because they were doing a lot of work against their production database because their ETL strategy was tightly coupled with that. We're talking stored procedures here. Probably seemed like a good idea at the time. Their ETL speed was limited by their database speed. While it was serving traffic.
A few potential issues with the approach suggested:
- it tackles incremental but not full reindex, that's a mistake
- it sounds like it combines Extract and Transform into one step; I wouldn't do that.
- Probably doing a lot of joins is going to complicate things; do you even want to be doing that on your production cluster? Especially when doing a full reindex. Do all those tables change all the time? This blurs the line between Extract and Transform and lets the database do a lot of the work.
Not saying that it's all wrong but it probably isn't optimal and might become a problem if you scale.