Thursday, 10 December 2015

SQL Override Pros and Cons

This is one of most frequently asked interview question for a informatica developer "what are the pros and cons of using SQL Overide ? " 




Pros 


  1. We can perform complex join conditions and filter conditions easily and the same is directly pushed to the database level without Informatica having to write the queries.
  2. We can override queries to make them use indexes and to get better execution plan.We can also use hints in SQL query
  3. There are few things which can be achieved easily in sql query using advanced sql feature like Top/Pivot/CTE etc.To achieve same in informatica you have to write sql logic
  4. In few cases when we are migrating from some legacy system to Informatica we can directly use old SQL queries in sql override which will save lot of efforts

Cons

  1. The no of columns connected in the downstream transformation  should match with the datatype and length with the overridden column name even if it does not necessarily need the same column names
  2. We lose the ability to perform source side push down optimization the moment we perform an override
  3. By writing complex queries on the override, we complicate the code which is against the fundamental rules of using ETL packages 



No comments:

Post a Comment