OpenRefine vs Talend

I'm looking for an open-source/free/cheap tool to clean messy data coming from various travel APIs. We use many different APIs and save the info in our DB. However, many duplicates cannot be easily recognized as such.

We would either write an algorithm or use smart technology/tools with ML to help with product management.

While there are many things to be considered, this is one feature that it should have:

"To avoid confusion, we need to merge the suppliers & products accordingly. Products and suppliers must be able to be merged and assigned separately.

Reason: It may happen that one supplier offers different products. E.g., 1 tour operator offers 3 products via 1 API, but only 1 product with 3 (or a different amount of) variations via a different API. Also, the commission may differ for products, which we need to consider. Very often, products that are live (are bookable in real-time) on via 1 API, but are not live on the other. E.g., Supplier product 1 & 2 of API1 are live, product 3 not. For the same supplier, API2 provides live availability for products 1, 2, and 3.

Summing up, when merging the suppliers (tour operators) we need to consider:

• Are the products the same for all APIs?
• Which booking system API gives a better commission? Note: Some APIs charge us 1-5% depending on the monthly sale, which needs to be considered
• Which booking system provides live availability
• Is it the same supplier, or is the name only similar?

Most of the time, the supplier names differ even if they are the same (e.g., API1 often names them XX Pty Ltd, while API2 leaves "Pty Ltd" out). Additionally, the product title, description, etc. differ.

We need to write logic and create an algorithm to find the duplicates & to merge, assign, or (de)activate the respective supplier or product. My previous developer started a module to merge the suppliers, which does not seem to work correctly. Also, it is way too time taking considering the high amount of products that we have.

I would recommend merging, assigning etc. products and suppliers only if our algorithm says it's 90- 100% the matching supplier/product. Otherwise, admins need to be able to check & modify this. E.g. everything with a lower possibility of matching will be matched automatically, but can be undone or modified.

The next time the cron job runs, this needs to be considered to avoid recreating duplicates & creating a mess."

I am not sure in what way OpenRefine can help to achieve this and what ML tool can be connected to learn from the decisions the product management team makes. Maybe you have an idea of how other travel portals deal with messy data, duplicates, etc.?

I'm looking for the cheapest solution for a start-up, but it should do the work properly.

I am trying to build a data lake by pulling data from multiple data sources ( custom-built tools, excel files, CSV files, etc) and use the data lake to generate dashboards.

My question is which is the best tool to do the following:

1. Create pipelines to ingest the data from multiple sources into the data lake
2. Help me in aggregating and filtering data available in the data lake.
3. Create new reports by combining different data elements from the data lake.

I need to use only open-source tools for this activity.

I appreciate your valuable inputs and suggestions. Thanks in Advance.