What factors make one ETL tool better than another?

I have used SSIS as it is a Microsoft-developed ETL tool tightly integrated with the Microsoft SQL server ecosystem, offering seamless integration because my company had invested heavily in Microsoft technologies. As the company migrated to AWS (Redshift), we started using Pentaho (open-source), which supports broader cross-platform sources.

When choosing an ETL tool, do not only look for an ETL supporting a wide range of sources and destinations, but more importantly, does it support YOUR own sources and destinations. Also, sometimes, it can be tricky to define what "support" means, you need to go one step deeper and understand all the parameters required, the exact version, the page size, the timeout options, the memory limits and so on, so you are clear it will support your own use cases. There is no perfect ETL tool, there is the perfect ETL tool for your use case.

While selecting an ETL tool, one thing we should pay attention to is, on how the pipeline processing is happening, is it pulling data to your system for processing or pushing your transformation logic to data layer. Since the rise of data cloud platforms like Snowflake, Databricks etc it is a good idea to push the transformations to the data stores

Several factors determine the superiority of one ETL (Extract, Transform, Load) tool over another. Selecting the best ETL tool involves evaluating factors based on specific project requirements and business objectives. - Performance - Scalability - Connectivity - Transformation Capabilities - Error Handling - Cost - Security - Integration: Compatibility

Data quality should be addressed upfront. The ETL process can help identify some gaps, but if you are using the ETL tool to fill any data quality gaps, I believe this is not the best practice. The data should be clean in the source system. If it is not the case, go (or ask the business to go) and fix it within the source system before running your ETL. If you run your ETL process over data not having the right quality level, you are just propagating bad data to the rest of your information system, up to your analytics layer.

For the Transformation feature, we should also consider the scalability options. With ETL, you might need to transport and transform huge volume of data which might become resource-intensive. iPaaS tools (Integration Platform as a Service) with cloud deployment can be good options to consider in this case.

Transparency and readability! Without these business adaptation will be lacking, trust will be low and your initiative won’t be an enabler for data literacy in your organization.

I totally agree with this point, I am using SAP BODS ETL tool, It is very convenient to use and easy to build application/objects even if you are not pro in SQL. The documentation about this ETL tool is also very good. You will find lots of resources online from the SAP in SAP help portal, Designer guide etc. and also from the professional working in SAP BODS. I would say this tool requires less maintenance and we do get software patch every 6 months.

Many organizations are adopting a multi-tool approach to benefit from best of breed features for each functional capability -- particularly separating tooling for data replication and transformation. Adopting a single ETL tool has the benefit of tight integration of capabilities but can constrain organizations when features lag the composable stack options. Secondly, organizations need to consider the price/performance constraints of using tools that push-down transformation to the data storage platform (or data warehouse) compared to those that process the data separate from the data platform.