Matillion performance question - Transformation job vs sql script component in orchestration job

Matillion ETL
1

I need to join 19 tables and volume of data is expected to be 5 millions & there are 100 fields to be populated in one target table. Should we use the sql script components in orchestration jobs or should we use transformation jobs? Which one wd be faster

2

Hi @2068303 and that’s a great question!

I checked in with some of the team and they had this to say:

  • using transformation jobs makes it very fast to create and amend the job - it will take a little while to validate, depending on how many components are used, but for that volume of data, the running time will be massively greater than the validation time, so not so noticeable.

  • using ‘SQL in orchestration’ will take longer to write and longer to amend - usually greatly so. The validation time will be very quick (basically, we check the syntax in that case) and the run time will probably be about the same. If the person writing the SQL has great insight into the problem at hand such that they can wring some optimisations from it then it may be faster, of course.

Let me know if that helps or if you have any further questions.
Many thanks - Claire