There are three ways to ‘filter’ data in Replicate and all three are handled differently.

1) Filter Conditions – Anything you place in this section is pushed to the source during full load as part of the where predicate and is also used during CDC to determine what is passed through.

2) Record Selection Condition – This is where you can build an expression that is used against rows as they pass through Replicate. All the rows from the source are read during full load, passed to the replicate server, and the expression is used against all changes being processed. If you want to have the expression run only on CDC records then you can use a case statement to test what phase the task is in, Full Load or CDC.

3) Use the Passtrough special version of the Table Settings Screen. You get to this screen by holding down the ctrl key when clicking Table Settings or double-clicking on a table. From this special screen the Filter Screen now has a third box on the right-hand side where you enter the Full Load Passthru where clause.
NOTE: Make sure to press ctrl key when clicking on table settings to get the full load pass thru option.

USE CASE:
ISSUE: Using Records Selection Condition (RSC) section to do a source lookup for filter was very slow during full load.
REASON: Each row of a 22 million row source table was passed down to the replicate server for the task to determine if row should be applied to target.
SOLUTION:
Use the Fullload Passthru Filter (FPT) section to apply a source side SQL statement to filter rows before they were sent to the replicate server. This (FPT) SQL ran very fast and filtered 453k rows out of the 22 million source records and reduced the full load time to 23 seconds.
EXAMPLE (FPT) SQL sub query where c_custkey is your key field:
NOTE: The syntax used in the passtrough is in the form of source database syntax - not replicate SQLite syntax
NOTE: The WHERE is implied and added automatically
c_custkey >= 0 and c_custkey <= 10

What the task does behind the scene:
This is the default select generated during full load with out a filter:
SELECT TOP 1000 [c_custkey]
,[c_name]
,[c_address]
,[c_nationkey]
,[c_phone]
,[c_acctbal]
,[c_mktsegment]
,[c_comment]
,[c_myDate]
,[myTimestamp]
FROM [Rep_Source_DEMO].[dbo].[CUSTOMER]

This is the select with the filter:
SELECT TOP 1000 [c_custkey]
,[c_name]
,[c_address]
,[c_nationkey]
,[c_phone]
,[c_acctbal]
,[c_mktsegment]
,[c_comment]
,[c_myDate]
,[myTimestamp]
FROM [Rep_Source_DEMO].[dbo].[CUSTOMER] WHERE c_custkey >= 0 and c_custkey <= 10

To do the filtering on both the full load and the CDC you will need to put an expression in two places in the GUI.

For full load:
NOTE: This is in source database syntax.
As of Replicate 5.1.2.143:
The “--FILTER: “keyword in the Record Selection Condition was replaced with a special version of the Table Settings Screen.
Note: You get to this screen by holding down the ctrl key when clicking Table Settings or double-clicking on a table. From this special screen the Filter Screen now has a third box on the right-hand side where you enter the Full Load Passthru WHERE predicate.
• Anything in the section is used only during the Full Load phase of the task and it is passed back up to the source database to be evaluated, resulting in only records that are “good” are sent down to the replicate server/task

For the CDC portion:
NOTE: This is in task SQLite syntax.
Record Selection Condition:
This is where you can build an expression that is used against rows as they pass through Replicate. All the rows from the source are read during full load, passed to the replicate server, and the expression is used against all changes being processed.
NOTE: If you want to have the expression run only on CDC records then you can use a case statement to test what phase the task is in, Full Load or CDC. Check the value of

$AR_H_STREAM_POSITION if it is not equal to ‘NULL’ then task is in CDC mode.
CASE
WHEN $AR_H_STREAM_POSITION != ''
THEN
CASE WHEN
$c_custkey >= 0 and $c_custkey <= 10
THEN 1
END
ELSE 1
END