Informatica Powercenter – SQL Overrides

I did not really find any super good resource on this topic, but the overall idea of SQL override is to override a transformation default behavior by writing a SQL query instead.

Of course, SQL overrides works only on relational data. There is no SQL Overrides for flat file.

For instance, take a source qualifier transformation. If we edit our Source Qualifier transformation we will find there a tab for Transformations, Ports, Properties, Sources and Metadata Extensions.

source qualifier transformation’s ports tab
source qualifier transformation’s properties tab

In SQ’s properties tab we have “SQL QUERY” option, its selected in the image above. See that it says “user-defined SQL Statement”. So, if you write a query there you need to make sure that:

  • The SELECT statement must list the port names in the order in which they appear in the transformation.
  • The number of columns in the SELECT statement in the query must match the number of ports in the Source Qualifier transformation.
  • If you enter an SQL query, the Integration Service uses only the defined SQL statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation.

All those yellow highlighted properties in the imabe above will be overridden by you SQL QUERY.

The Default Query

We know that using SQL Query we are overriding our default query, but, what is our default query?

To check your default query simply open your SQL Query editor within your transformation and hit generate query.

example of default query

Why should we use SQL Override?

We can make use of SQL Override every time that we need a more complex scenario that would be easier and simpler to construct via SQL instead of via Informatica Powercenter transformations. For instance, if you have multiple joins, CTEs, window functions, subqueries, etc. For those cases, SQL Query override sounds like a better option.

We can also overwrite our SQL Queries if Powercenter default query is not using the best indexes, so we can tweak our query to make sure we are using best indexes.

Also, if you already have a report or a dataset ready in your database and you need to migrate it to Powercenter, you can just grab the query that is producing your dataset and set in Powercenter via SQL Query override, that way you will not need to recreate the whole thing using Powercenter transformations; that can speed up your migration process.

What is the difference between SQL override and lookup override in Informatica?

The role of SQL Override is to limit the number of incoming rows entering the mapping pipeline, whereas Lookup Override is used to limit the number of lookup rows to avoid the whole table scan by saving the lookup time and the cache it uses.


Publicado por Pedro Carvalho

Apaixonado por análise de dados e Power BI

Um comentário em “Informatica Powercenter – SQL Overrides

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do

Você está comentando utilizando sua conta Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: