There is no on size fits all tool for good ETL processes IMO.
We just built a pretty involved ETL process over the past couple of months that had two key components, a true batch ETL process and then a real-time synchronization between two Postgres databases on different servers. In our case, real-time is basically less than 2 seconds for this application so nothing insane, we routinely see it working in about 300-500ms.
We could've solved it all using just SQL to be fair, but found that it was better even for the batch ETL process to utilize SQL only for the data CRUD and leave the business logic to the application code we wrote (it is in nodejs). This let us take advantage of some caching which reduced the database server load dramatically during the processing, id lookups and other known values being the core area we reduced with cache. It also let us keep our business rules in code which is where we have them for data validation etc, so we could reuse existing code sections to maintain consistency and not try to replicate some pretty sophisticated validations and transforms in SQL.
I have also worked on projects in the past where the entire process was done using SQL and it is doable but I'd say it was less than ideal and definitely can cause significant overhead on the DB servers which is unnecessary most of the time.
I had one project where the entire ETL process was in MSSQL with stored procs and SSIS, spread across multiple servers. That system worked really well overall (integrity was super good and reasonably maintainable for SQL people), but again strained the DB servers a lot and was fairly slow. We moved most of that ETL process to C# at the time given it was a MS house, and the difference was dramatic. Load on the DB servers dropped and ETL that would take hours to run on SSIS with stored procs etc took minutes with C# and the utilization of memcached. In fact on that one we even wrote extensions for SQL Server using .NET and had SSIS call our external code which used the caching engine to reduce DB lookups amongst a bunch of other things. Pretty damn cool solution but definitely wasn't simple, lots of smart people worked on that project to make it super reliable and fast.
Comments
There is no on size fits all tool for good ETL processes IMO.
We just built a pretty involved ETL process over the past couple of months that had two key components, a true batch ETL process and then a real-time synchronization between two Postgres databases on different servers. In our case, real-time is basically less than 2 seconds for this application so nothing insane, we routinely see it working in about 300-500ms.
We could've solved it all using just SQL to be fair, but found that it was better even for the batch ETL process to utilize SQL only for the data CRUD and leave the business logic to the application code we wrote (it is in nodejs). This let us take advantage of some caching which reduced the database server load dramatically during the processing, id lookups and other known values being the core area we reduced with cache. It also let us keep our business rules in code which is where we have them for data validation etc, so we could reuse existing code sections to maintain consistency and not try to replicate some pretty sophisticated validations and transforms in SQL.
I have also worked on projects in the past where the entire process was done using SQL and it is doable but I'd say it was less than ideal and definitely can cause significant overhead on the DB servers which is unnecessary most of the time.
I had one project where the entire ETL process was in MSSQL with stored procs and SSIS, spread across multiple servers. That system worked really well overall (integrity was super good and reasonably maintainable for SQL people), but again strained the DB servers a lot and was fairly slow. We moved most of that ETL process to C# at the time given it was a MS house, and the difference was dramatic. Load on the DB servers dropped and ETL that would take hours to run on SSIS with stored procs etc took minutes with C# and the utilization of memcached. In fact on that one we even wrote extensions for SQL Server using .NET and had SSIS call our external code which used the caching engine to reduce DB lookups amongst a bunch of other things. Pretty damn cool solution but definitely wasn't simple, lots of smart people worked on that project to make it super reliable and fast.