What is the required language knowledge to use Informatica effectively?

Question

In the next few weeks, my company will be engaging multiple vendors to establish a choice for a common global ETL tool - not necessarily one that can't be broken from, but just where our license investment will go to consolidate those costs. Two of the major players are Talend and Informatica, with others that are unimportant for the sake of this question.

My experience is in Talend's Enterprise ETL tool, Talend Integration Suite, which also has many standard integration capabilities. Aside from all of the obvious pros and cons between the two such as cost, availability of knowledgeable resources, etc. my question is specific to the development language knowledge required.

I'd like some feedback on the Informatica option. I can state from experience that to use Talend effectively, and reach beyond it's pre-programmed bounds (which happens pretty often), you need to have a decent background in Java. And in this case, I mean Java directly - you can literally use Java if you need to add-on functionality, not a "special" version of Java. What is the situation with Informatica? Is there a particular extension language you can use? Is it proprietary, but perhaps closely related to a mainstream language? What is the likelihood of needing to use it for any jobs of consequence?

This is only one factor I'm going to use in evaluating the space, but I need to get a little more up to speed on this aspect of Informatica before I feel fully informed. Thanks for your answers!

Answer 1

I have seen custom transformaions written in 'C'. For what it was used for, it really wasn't worth the effort. This answer suggests you can also use Java. A few years back, I saw that Perl was supported as an extension language, but I can find no such references today. They probably dropped support for that.

Answer 2

Informatica has its own transformation langugage that you can use in your expressions, filter and such. It is quite versatile and not at all difficult to learn, if you're familiar with any of the most popular programming languages of today. SQL is probably the one it resembles the most. You will definitely need to learn at least the basics of this langugage to be able to use Informatica efficiently.

(Good understanding of SQL is pretty much a requirement anyway if you're going to use Informatica (or any ETL tool) - of course this depends on what your source and target systems are.)

It is also possible to create Java transformations, although those should be fairly rarely needed. Usually the built-in transformations combined with Informatica's own transformation language will provide more than enough options.