We are looking to move a suite of ETLs from Talend to Fivetran. Here is the current setup in Talend:

Source - file uploads from s3 

Destination - MS SQL RDS instance. This instance has 1 DB that is a config database that holds info pertaining to each client. Each client has at least one DB  

Extract -  

• Multiple clients load from same source 

• csv, tsv, xls/xlsx, fixed length files 

Transform -  

• Based on file derive the client ID 

• Query config database on the MS SQL instance for client specific info 

• Use this to determine destination DB and file format info 

• Perform data validation and transformation 

Load -  

• DB is passed in from previous step and data is loaded accordingly 

We completed a POC in Fivetran which proved it is doable with DBT core transformations. However, due to Fivetran’s current Extract > Load > Transform workflow with hard coded destinations, this would look like upwards of 500 connections, destinations, and transformations. Managing that would be cumbersome to say the least. Version upgrades from dev > test > prod would require applying changes to hundreds of objects. 
 
Additionally, in our existing Talend process, files are loaded from S3 only after all required validations and transformations have completed successfully. This validation-first approach helps ensure data quality before any data reaches the target environment. 
 
We are hoping for functionality similar to what exists in Talend with dynamic load configs based on transformation results.  
 
The stats in Talend show about 60m rows per month in prod another 40m for dev and test envs for a total of 100m per month estimate.