What comes first, the lookup or the filter?

[MoeyE]

Hi,

I am wondering when I have a source lookup on a column in a table, then I also have a filter on the table. What comes first? Are the records filtered then the source lookup occurs? or does the source lookup occur then the rows are filtered?

Regards,

Mo

[john_wang]

Hello @MoeyE ,

Good question 🙂 Please allow me some minutes to confirm for you.

Regards,

John.

[john_wang · Accepted solution]

Hello @MoeyE ,

It depends task settings. Maybe filter first, or maybe lookup first.

Let's see the filter "Filter Conditions" :

If the filter column is the source table's column eg PK , then in Full Load, filter take effective first then lookup. In CDC, filter first then lookup.

If the filter column is the "Calculated" column (eg the column value comes from lookup operation) then in both Full load and CDC, lookup first then filter.

In short, Replicate is smart to do filter first if possible to reduce the lookup operations.

There are another 2 filters, I ever summarized their difference in article Pass thru filter. In these 2 filter, probably lookup first then filter.

Hope this helps.

John.

[MoeyE]

Hi John,

Thanks for the help as always!

Regards,

Mohammed