Advantages of Informatica ETL tool over Teradata
First up, Informatica is a data integration tool, while Teradata is a MPP database with some scripting (BTEQ) and fast data movement (mLoad, FastLoad, Parallel Transporter, etc) capabilities.
Informatica over Teradata
1) Metadata repository for the organization’s ETL ecosystem
Informatica jobs (sessions) can be arranged logically into worklets and workflows in folders in informatica is best option over teradata.
Leads to an ecosystem which is easier to maintain and quicker for architects and analysts to analyze and enhance.
2) Job monitoring and recovery –
Easy to monitor jobs using Informatica Workflow Monitor.
Easier to identify and recover in case of failed jobs or slow running jobs.
Ability to restart from failure row / step.
3) Informatica MarketPlace – one stop shop for lots of tools and accelerators to make the SDLC faster, and improve application support.
4) Plenty of developers in the market with varying skill levels and expertise
5) Lots of connectors to various databases, including support for Teradata mLoad, tPump, FastLoad and Parallel Transporter in addition to the regular (and slow) ODBC drivers.
Some ‘exotic’ connectors may need to be procured and hence could cost extra.
Examples – PowerExchange for FaceBook, Twitter, etc which source data from such social media sources.
6) Surrogate key generation through shared sequence generators inside Informatica could be faster than generating them inside the database.
7) If the company decides to move away from Teradata to another solution, then vendors like Infosys can execute migration projects to move the data, and change the ETL code to work with the new database quickly, accurately and efficiently using automated solutions.
8) Pushdown optimization can be used to process the data in the database
9) Ability to code ETL such that processing load is balanced between ETL server and the database box – useful if the database box is ageing and/or in case the ETL server has a fast disk/ large enough memory & CPU to outperform the database in certain tasks.
10) Ability to publish processes as web services
Teradata over Informatica
1) Cheaper (initially) – No initial ETL tool license costs (which can be significant), and lower OPEX costs as one doesn’t need to pay for yearly support from Informatica Corp.
2) Great choice if all the data to be loaded are available as structured files – which can then be processed inside the database after an initial stage load.
3) Good choice for a lower complexity ecosystem
4) Only Teradata developers or resources with good ANSI/Teradata SQL / BTEQ knowledge required to build and enhance the system.
Teradata does have native export and load utilities, and with scripting you can do ETL within Teradata.
Operationally, however, its the concept of using the right tool for the right job. Informatica is a data integration tool and as such provides native functionality for the export transform and loading of data. Teradata is a database, pure and simple. I wouldn’t use a screwdiver to hammer a nail, I wouldn’t use Teradata for ETL.
Scalability would be a concern. Teradata is either a noded or appliance architecture, so the processing power and data storage within have a hard cap. The other operational concern I would point out is recoverability. If your using Teradata to do both and you lose your TD system, you lose the ability to perform ETL. With a separate system you could set up an active active or active passive failover and still be able to load the data.
I am currently running a TD system with Informatica as the ETL integration, and it works very well.
Attend to the best informatica online training.