Search docs.molo17.com
    Configuration guide Pipelines

    Filter rows with a snapshot WHERE clause and target filters

    Gluesync gives you two ways to replicate only some rows of a table: a snapshot WHERE clause, which limits what is read from the source during a snapshot, and target filters, which decide which rows reach the target during the snapshot and during live changes. This guide shows how to set both, how to combine them, and how each one treats inserts, updates and deletes.

    In this guide

    You will learn how to open the entity settings, limit a snapshot with a SQL WHERE clause, add target filters with each operator (including in), combine several conditions, run a snapshot and compare the target with the source using SQL counts, and check what a filter does when a row is inserted, updated or deleted after the snapshot. The Frequently asked questions answers the questions that come up most: how to write the in list, what to do when values are stored with blanks, and how decimals, dates and the IBM i relative record number behave.

    Quick information

    Goal

    Copy only a subset of the rows of a table, for the initial snapshot and for live changes

    Audience

    Gluesync users who build and maintain pipelines

    Level

    Intermediate

    Time required

    About 30 minutes

    Area

    Control Plane → Pipelines → entity Options → Edit settings

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You can sign in to the Gluesync Control Plane with a role that can edit pipelines;

    2. You have a pipeline with a source and a target agent and an entity for each table (this guide uses a pipeline named Filters demo with the entities customers and orders);

    3. The target tables already exist, with the same columns as the source tables;

    4. You can run SQL against the source and the target database, for example with Query Studio.

    The examples use the Gluesync sample shop data, copied into a schema that is used only for this exercise (shop_filters on the source, shop_filtered on the target), so that you can change rows freely:

    Table Content

    customers

    200 rows. Columns id, full_name, email, country, tier, status, credit_limit, created_at. Eight countries (25 rows each), four tiers (50 rows each), and the statuses active (156 rows), inactive (28) and suspended (16). email is empty on 20 rows and credit_limit is empty on 8 rows.

    orders

    1,000 rows. Columns id, customer_id, amount, currency, status, order_date. Currencies EUR (333), USD (334) and GBP (333), five statuses (200 rows each).
    Optional: add empty values to your test data

    If your test data has no empty values and you want to see how filters treat NULL, add some. The examples in this guide were created on the copy with UPDATE shop_filters.customers SET email = NULL WHERE id % 10 = 0 and UPDATE shop_filters.customers SET credit_limit = NULL WHERE id % 25 = 0. Skip this if your data already has empty values in the columns you filter on.

    Do not try filters for the first time on production tables. Use a copy, or a target schema you can empty, because some of the steps below delete rows from the target.

    Guide steps

    Open the settings of the entity

    UI

    1. Open Data pipelines and select your pipeline;

    2. In the row of the customers entity, select Options (the three-dot icon);

    3. Select Edit settings.

    Pipeline entity table with the entity menu open and Edit settings marked by annotation 1
    Figure 1. Open the entity menu and select Edit settings.
    Expected result

    The Settings for customers dialog opens with four tabs: Source, Target, Entity and Fine tuning. The snapshot WHERE clause is on Source. The target filters are on Target.

    Limit the snapshot with a WHERE clause

    UI

    1. In the dialog, select the Source tab;

    2. In the SQL Snapshot where clause filter (without initial WHERE) editor, enter the condition, for example country = 'IT';

    3. Keep the dialog open to add the target filters in the next steps.

    Settings for customers dialog on the Source tab
    Figure 2. Enter the condition in the snapshot WHERE clause editor of the Source tab.
    Expected result

    The editor shows country = 'IT'. The next snapshot reads only the 25 customers of Italy from the source database.

    The text is added to the snapshot SELECT as it is, so it is written in the SQL dialect of the source database and uses the source column names. Typing the keyword WHERE at the beginning is optional: WHERE country = 'IT' gives the same result. Because the clause runs inside the source database, it can use OR, LIKE, functions, ranges and subqueries (see Combinations and their effect).

    The WHERE clause is used only when a snapshot reads the table. It does not filter live changes: see What happens to live changes.
    Choose the fields for a target filter

    UI

    1. In the dialog, select the Target tab;

    2. In the Filter out incoming data section, open the Fields list;

    3. Select the target fields that you want to filter on, for example status and credit_limit.

    Settings for customers dialog on the Target tab with the Target tab marked by annotation 1 and the open Fields list marked by annotation 2
    Figure 3. Open the Fields list in Filter out incoming data and select the fields to filter on.
    Expected result

    For every selected field a row appears with three controls: Field (read only), Operator and Value. The text No filters defined disappears.

    Each field can be selected once, so a field carries one condition. The list shows the fields of the target table. A trash icon (Remove filter) at the end of a row removes that condition.
    Pick an operator and a value

    UI

    1. In the row of status, open the Operator list;

    2. Scroll the list and select in;

    3. In Value, enter active, suspended.

    Operator list open on the status row with the Operator control marked by annotation 1 and the in operator marked by annotation 2
    Figure 4. Open the Operator list and select in. The list scrolls: the operators = > < >= <= != in, Not in, Is null, Not null and Regex are available.
    Expected result

    The row shows status, in and active, suspended. The new rows are kept in the target only when status is active or suspended.

    The Operator list holds 11 entries and shows five at a time, so scroll it to reach in, Not in, Is null, Not null and Regex. For Is null and Not null the Value box is hidden because those operators need no value. The full list with examples is in Operators reference.
    Combine conditions

    UI

    1. In the row of credit_limit, select the operator >;

    2. In Value, enter 5000;

    3. Close the dialog with Close, then select Confirm and continue to save the entity.

    Target tab with two filter rows: status in active
    Figure 5. Two conditions on two fields: status in (active, suspended) and credit_limit > 5000.
    Expected result

    Gluesync saves the settings and returns to the pipeline. A row is replicated only if it passes every condition (logical AND). In the entity table, a filter icon appears in the Features column of the entity.

    Conditions on different fields are always combined with AND. The Control Plane has no OR between conditions and no group of conditions. To keep rows that match one value or another on the same field, use in (for example EUR, GBP) or Regex (for example (EUR|GBP)). To combine two different fields with OR during a snapshot, write the OR in the snapshot WHERE clause.
    Run the snapshot and empty the target first

    UI

    1. In the row of customers, select Run one-time snapshot;

    2. In Data already in the target, select Empty the table first;

    3. Select the checkbox I understand that rows in the target will be deleted;

    4. Select Start snapshot.

    Snapshot (copy-only) dialog with Empty the table first marked by annotation 1
    Figure 6. Run a snapshot that empties the target table first.
    Expected result

    The snapshot reads the 25 customers of Italy (the WHERE clause) and writes only the ones that pass both filters: 11 rows.

    Keep and merge, the default, overwrites rows with the same key and leaves every other row where it is. After you tighten a filter or a WHERE clause, rows copied by an earlier run stay in the target. We ran the snapshot with only country = 'IT' (25 rows in the target), added the two filters and ran it again with Keep and merge: the target still had 25 rows. Empty the table first gave the expected 11 rows. If you only want to remove part of the target, Delete only some rows first deletes the rows selected by the Selective deletion ahead of snapshot section of the Target tab. See Selective deletion ahead of snapshot.

    After this run the Last rows column of the entity shows 25, the rows read from the source after the WHERE clause, while the target holds 11. The WHERE clause reduces what Gluesync reads. A target filter does not: it discards rows after they are read.
    Filter the orders with in and a numeric condition

    UI

    1. In the row of the orders entity, select Options, then Edit settings;

    2. On the Target tab, select the fields currency and amount;

    3. For currency, select the operator in and enter EUR, GBP;

    4. For amount, select the operator >= and enter 500;

    5. Close the dialog and select Confirm and continue;

    6. Run a one-time snapshot with Empty the table first, as in the previous step.

    Target tab for the orders entity with currency in EUR
    Figure 7. Orders: currency in (EUR, GBP) and amount >= 500.
    Expected result

    Only the orders in euros or pounds with an amount of at least 500 reach the target: 501 of the 1,000 orders.

    Prove the result with SQL counts

    SQL

    1. Open Query Studio, select your pipeline’s source agent and run the expected counts;

    2. Select the target agent and run the actual counts;

    3. Compare the two.

    Expected rows, computed on the source with the same conditions written as SQL:

    SELECT count(*), md5(string_agg(id::text, ',' ORDER BY id))
FROM shop_filters.customers
WHERE country = 'IT' AND status IN ('active', 'suspended') AND credit_limit > 5000;

SELECT count(*), md5(string_agg(id::text, ',' ORDER BY id))
FROM shop_filters.orders
WHERE currency IN ('EUR', 'GBP') AND amount >= 500;

    Actual rows, read on the target:

    SELECT 'customers' AS target_table, count(*) AS rows FROM shop_filtered.customers
UNION ALL
SELECT 'orders', count(*) FROM shop_filtered.orders;

SELECT md5(string_agg(id::text, ',' ORDER BY id)) FROM shop_filtered.customers;
SELECT md5(string_agg(id::text, ',' ORDER BY id)) FROM shop_filtered.orders;
    Query Studio on the target agent returning 11 rows for customers and 501 rows for orders
    Figure 8. Row counts on the target agent in Query Studio.
    Expected result

    Source and target agree. In our run both sides returned 11 and 73aa142b0f4fd6989a79ec50d2794c71 for customers, and 501 and 3122c122bc4b66e6f45ab95c6c11c58c for orders: the same number of rows and the same set of keys.

    Start live changes and see what the filter does

    UI + SQL

    1. In the row of customers, select Start sync;

    2. In Start from, select Full copy, and keep Keep and merge under Data already in the target;

    3. Select Start entity;

    4. Run the statements of the table below on the source with your SQL client (Query Studio opens in read-only mode), one at a time, and read the target after each one.

    Sync changes (CDC) dialog with Full copy marked by annotation 1 and Start entity marked by annotation 2
    Figure 9. Start the entity with a full copy, then follow live changes.

    The entity here still has the snapshot WHERE clause country = 'IT' and the filters status in (active, suspended) and credit_limit > 5000. The target started with 11 rows. We observed:

    # Statement on the source Result on the target Rows

    1

    INSERT customer 9001: IT, active, credit_limit 7000

    The row is written (it passes every filter).

    12

    2

    INSERT customer 9002: IT, inactive, 7000

    Nothing is written (status fails the filter).

    12

    3

    INSERT customer 9003: DE, active, 7000

    The row is written. The WHERE clause country = 'IT' does not apply to live changes.

    13

    4

    UPDATE 9001 set status = 'inactive'

    The row is deleted from the target: it no longer passes the filter.

    12

    5

    UPDATE 9002 set status = 'suspended'

    The row appears in the target: it now passes the filter.

    13

    6

    UPDATE 9002 set credit_limit = 100

    The row is deleted: credit_limit > 5000 fails.

    12

    7

    UPDATE 9003 set credit_limit = 8000

    The row is updated and stays.

    12

    8

    DELETE customer 9003

    The row is deleted from the target.

    11

    9

    DELETE customer 9002 (not in the target)

    Nothing to remove, no error.

    11
    Expected result

    The target always holds exactly the rows that pass the filters. After the last statement it is back to 11 rows, the same as the source query of the previous step.

    When you finish, stop the entity, or select Run one-time snapshot instead of Start sync when you only need a copy.

    Operators reference

    The operators of Filter out incoming data are the ones in the Operator list. The examples were run on the shop data; the last column is the number of rows that reached the target.

    Operator Keeps the row when Value to enter Example Rows kept

    =

    The field equals the value.

    One value.

    country = IT

    25 of 200

    !=

    The field has a value that is different from the given one.

    One value.

    status != active

    44 of 200

    >

    The field is greater than the value.

    A number, date or text.

    credit_limit > 1137

    188 of 200

    >=

    The field is greater than or equal to the value.

    A number, date or text.

    credit_limit >= 1137

    189 of 200

    <

    The field is less than the value.

    A number, date or text.

    credit_limit < 1137

    3 of 200

    <=

    The field is less than or equal to the value.

    A number, date or text.

    credit_limit <= 1137

    4 of 200

    in

    The field equals one of the listed values.

    Values separated by commas. Spaces around a comma are ignored.

    status in active, suspended

    172 of 200

    Not in

    The field has a value that is not in the list.

    Values separated by commas.

    country Not in IT, DE

    150 of 200

    Is null

    The field is empty.

    None (no Value box).

    email Is null

    20 of 200

    Not null

    The field is not empty.

    None (no Value box).

    email Not null

    180 of 200

    Regex

    The whole value matches the regular expression.

    A regular expression. It must match the entire value, not a part.

    tier Regex (gold|platinum)

    100 of 200

    More examples on orders: currency in EUR, GBP keeps 666 of 1,000; amount >= 500 keeps 751; status Not in cancelled, new keeps 600; order_date > 2026-06-30 keeps 357; customer_id in 1, 2, 3, 8, 15 keeps 25; status Regex (paid|shipped) keeps 400.

    How values are read

    The value is typed as plain text, without quotes, and Gluesync converts it to the type of the target field before it compares.

    Field type What to type Observed

    Text

    The text, with no quotes. The comparison is case-sensitive.

    currency = eur keeps 0 rows, and so does in eur, gbp. country = 'IT' (with quotes) keeps 0 rows.

    Integer and decimal

    A plain number, with a dot for decimals.

    amount > 1000.50 keeps 496 of 1,000.

    Date

    yyyy-MM-dd

    order_date > 2026-06-30 keeps 357 of 1,000.

    Timestamp (no time zone)

    yyyy-MM-ddTHH:mm:ss, with the letter T

    created_at >= 2025-06-01T00:00:00 keeps 50 of 200. 2025-06-01 alone stops the entity with Text '2025-06-01' could not be parsed at index 10.

    Empty values (NULL)

    Use Is null or Not null.

    No other operator keeps a row whose field is empty: credit_limit != 5000 keeps 192 of 200, not 200, because 8 rows are empty; email Not in customer1@example.com keeps 179, not 199.

    The Control Plane does not check the value when you save. A value that cannot be read, such as abc on a number field, stops the entity at the first row with Character a is neither a decimal digit number, decimal point, nor "e" notation exponential mark.

    Combinations and their effect

    Each line was run as a snapshot into an empty target and the target was compared with the SQL result on the source (same rows, not only the same count).

    Setting What you enter Effect Rows in target

    Snapshot WHERE only

    WHERE: country = 'IT'

    The source returns only Italian customers.

    25

    Snapshot WHERE with OR

    WHERE: country = 'IT' OR tier = 'platinum'

    Any SQL the source accepts. OR is possible here and not in target filters.

    75

    Snapshot WHERE with LIKE, a function and a subquery

    WHERE: full_name LIKE 'A%'
    lower(email) LIKE 'customer1%'
    id IN (SELECT customer_id FROM shop_filters.orders WHERE amount > 1900)

    Each of the three on its own.

    20 / 100 / 53

    Snapshot WHERE on dates

    WHERE: created_at >= '2025-06-01'

    The value follows the SQL rules of the source database.

    50

    One target filter

    country = IT

    Same rows as the WHERE in a snapshot. The difference is on live changes.

    25

    Two target filters (AND)

    country = IT and status = active

    The row must pass both.

    20

    Three target filters (AND)

    country in IT, DE, FR and tier != bronze and email Not null

    The row must pass all three.

    50

    Two filters, in and a number

    status in active, suspended and credit_limit > 5000

    AND of the two.

    91

    WHERE and a target filter

    WHERE: country = 'IT'; filter: status in active, suspended

    The snapshot reads the WHERE rows and the filter keeps part of them: the intersection.

    22

    WHERE and two target filters

    WHERE: country = 'IT'; filters: status in active, suspended, credit_limit > 5000

    Intersection of all three conditions.

    11

    WHERE with OR, and a filter

    WHERE: country = 'IT' OR tier = 'platinum'; filter: credit_limit > 5000

    OR inside the WHERE, then AND with the filter.

    41

    A range on one column

    WHERE: credit_limit BETWEEN 3000 AND 8000; filter: credit_limit > 5000

    One field has one filter condition, so put the second bound in the WHERE clause.

    63

    Orders: in and a number

    currency in EUR, GBP and amount >= 500

    The example of this guide.

    501

    Orders: WHERE and two filters

    WHERE: order_date >= '2026-06-01'; filters: currency in EUR, GBP, amount >= 500

    WHERE reduces the read, the filters decide what is written.

    224

    What happens to live changes

    Change on the source With a target filter With only a snapshot WHERE clause

    INSERT

    Written if the new row passes every condition, ignored otherwise.

    Always written: the WHERE clause is not applied. Inserting a DE customer while the WHERE clause was country = 'IT' wrote a DE row to the target.

    UPDATE that keeps the row inside the condition

    The target row is updated.

    The target row is updated.

    UPDATE that moves the row out of the condition

    The row is deleted from the target.

    The row is updated and stays. We changed the country of an Italian customer to DE and the row stayed in the target with DE.

    UPDATE that moves the row into the condition

    The row is added to the target, even if it was never copied by the snapshot.

    The row is written: every update is applied.

    UPDATE of a row the snapshot skipped

    If it passes the filter the row is written, otherwise ignored.

    The row is written. We updated a DE customer that the IT snapshot had skipped and it appeared in the target.

    DELETE

    The row is deleted. Deleting a row that is not in the target does nothing and raises no error.

    The row is deleted.

    A snapshot WHERE clause never removes anything and never blocks anything after the snapshot. If a rule must hold for live changes too, express it as a target filter.

    Snapshot WHERE or target filter: which to use

    You want to Snapshot WHERE clause Target filter

    Read fewer rows from the source and put less load on it

    Yes. The condition runs in the source database.

    No. The rows are read and then discarded.

    Apply the rule to live changes as well as to the snapshot

    No.

    Yes.

    Keep the target consistent when a row stops matching

    No.

    Yes: the row is deleted.

    Write a condition with OR, LIKE, a function, a range or a subquery

    Yes, any SQL of the source database.

    No. Only the 11 operators, joined with AND, one per field.

    Filter on a field with a list of values

    Yes: IN ('IT', 'DE').

    Yes: in with a comma-separated list.

    Select rows with an empty value

    Yes: IS NULL.

    Yes: Is null.

    Load a one-off subset, for example the last year, once

    Yes.

    Works, but the rule stays active for live changes.

    Both: read little and keep the rule forever

    Combine them. The WHERE clause limits the read, the filter is the rule that always holds.
    Verify the result

    You have successfully filtered rows if:

    • The snapshot WHERE clause is saved on the Source tab and the target filters are on the Target tab;

    • After a snapshot into an empty target, the row count and the md5 of the keys on the target equal the SQL result on the source;

    • A new row that fails a filter is not written to the target;

    • A row updated so that it no longer passes the filter is deleted from the target;

    • The Features column of the entity shows the filter icon.

    Frequently asked questions

    The answers below were run on an extra table, codes, with a CHAR(12) column that holds codes with blanks, as fixed-length columns do on IBM i. Each answer gives the number of rows that reached the target, compared with the same condition written as SQL on the source.

    Optional: create the codes table

    Skip this if you have your own data with the same traits. Create the table on the source (shop_filters) and an empty copy on the target (shop_filtered), add an entity for it, and insert the rows:

    CREATE TABLE shop_filters.codes (
  id integer PRIMARY KEY,
  code char(12) NOT NULL,       -- fixed length, padded with blanks
  code_text varchar(12) NOT NULL,
  price numeric(10,3),
  ship_date date,
  updated_at timestamp,
  rrn bigint
);

INSERT INTO shop_filters.codes VALUES
(1, '         LI0', '         LI0',    1.500, '2026-05-31', '2026-05-31 23:59:59',    1),
(2, '         LI1', '         LI1',    2.250, '2026-06-01', '2026-06-01 00:00:00',    2),
(3, '         LTG', '         LTG',   10.000, '2026-06-15', '2026-06-15 10:30:00.123', 3),
(4, '         LFM', '         LFM',    7.125, '2026-06-30', '2026-06-30 12:00:00', 1500),
(5, '         LK9', '         LK9',    0.500, '2026-07-01', '2026-07-01 08:00:00', 1501),
(6, 'LI0', 'LI0',                   1000.000, '2026-07-15', '2026-07-15 00:00:00', 1502),
(7, 'LI1', 'LI1',                     99.999, '2026-08-01', '2026-08-01 00:00:01', 2000),
(8, 'LTG', 'LTG',                       NULL,        NULL,                  NULL, 2001);

    How do I write the list of the in operator?

    Type the values separated by commas, without quotes, for example LI0, LI1, LTG.

    • Quotes are not syntax. They become part of the value, so 'LI0' is looked for with its quotes and never found;

    • Gluesync removes the spaces around each item, so LI0, LI1, LI0,LI1 and ` LI0 , LI1 ` are the same list;

    • The comparison is exact and case-sensitive;

    • The items are converted to the type of the field before they are compared, so the same rule holds for numbers and dates.

    On the code_text column (VARCHAR), whose rows 6, 7 and 8 hold LI0, LI1 and LTG with no blanks:

    Value of in Result Rows

    LI0, LI1, LTG

    Matches rows 6, 7 and 8.

    3

    LI0,LI1,LTG and ` LI0 ,LI1 , LTG `

    Same as above: spaces around the items are ignored.

    3

    'LI0', 'LI1', 'LTG' and "LI0", "LI1", "LTG"

    The quotes are part of the values.

    0

    li0, li1, ltg

    Case-sensitive.

    0

    My values have blanks before or after them. Why does in match nothing?

    A fixed-length column, such as an IBM i CHAR(12), stores the value padded with blanks, and Gluesync reads those blanks as part of the value: a code of LI0 in a CHAR(12) column is + LI0+ (nine blanks first) or +LI0 +. Because Gluesync trims every item of the in list, the list can never contain a value that begins or ends with a blank, so in finds nothing, and no error is shown. The quotes do not help, and neither does typing the blanks into the list.

    Use Regex, which sees the whole value, and let the expression accept the blanks:

    ^\s*(LI0|LI1|LTG|LFM)\s*$
    Settings for codes dialog on the Target tab with a Regex condition on the code field marked by annotation 1
    Figure 10. A Regex condition on the fixed-length code column. The value is longer than the box.

    On the code column (CHAR(12), rows 1 to 5 padded on the left, rows 6 to 8 padded on the right):

    Condition Result Rows

    code in LI0, LI1, LTG, LFM

    Nothing matches.

    0

    code in 'LI0', 'LI1', 'LTG', 'LFM', or the same list with the blanks typed in

    Nothing matches.

    0

    code Regex ^\s*(LI0|LI1|LTG|LFM)\s*$

    Rows 1, 2, 3, 4, 6, 7 and 8, the same rows as trim(code) IN ('LI0','LI1','LTG','LFM') on the source.

    7

    code Regex (?i)\s*(li0)\s*

    (?i) ignores the case: rows 1 and 6. Without it the expression matches nothing.

    2

    The expression must match the entire value, so ^ and $ are optional, and \s* stands for the blanks. To list many codes, put them all in one group separated by |.

    The filter also works on live changes. With the Regex above, an INSERT of a padded LI1 and of LFM was written to the target, an INSERT of a padded LK9 was not, and an UPDATE that changed a written row to LK9 deleted it from the target.

    Rows that are already in the target stay there when you change a filter. Run a snapshot with Empty the table first to align the target.

    How do decimal values compare?

    Type the number in plain form, with a dot as the decimal separator and without a thousands separator. Gluesync compares it as a number, so trailing zeros do not matter, on the field and in the value. On price (NUMERIC(10,3)):

    Condition Result Rows

    price = 10 or 10.0

    Matches the row that holds 10.000.

    1

    price = 1.5 or 1.500

    Matches the row that holds 1.500.

    1

    price in 1.5, 2.25

    Matches 1.500 and 2.250.

    2

    price > 7.125, and >= 7.125

    Compared as numbers.

    3, and 4

    price != 10

    Rows with an empty price never pass.

    6 of 8

    price = 1,5

    The entity stops with Character , is neither a decimal digit number, decimal point, nor "e" notation exponential mark.

    error

    Regex is the exception: it sees the number as text, with the number of decimals of the column. 10.000 is not matched by 10; use 10(\.0+)?. For numbers, prefer =, in and the comparison operators.

    How do I filter dates and timestamps?

    Use the ISO format: yyyy-MM-dd for a date, yyyy-MM-ddTHH:mm:ss for a timestamp, with the letter T between date and time and optional decimals of a second. Every operator but Regex compares dates and timestamps as points in time, so the comparison operators and in work as they do for numbers. On ship_date (DATE) and updated_at (TIMESTAMP):

    Condition Result Rows

    ship_date >= 2026-06-15

    From the 15th of June onwards.

    5

    ship_date in 2026-06-01, 2026-06-15

    Two dates.

    2

    updated_at >= 2026-06-01T00:00:00

    The timestamp of row 2 is exactly this value, so it is included.

    6

    updated_at = 2026-06-15T10:30:00.123

    A fraction of a second is accepted.

    1

    updated_at > 2026-06-15T10:30:00

    The row at 10:30:00.123 is later than 10:30:00.

    5

    ship_date Is null

    Rows with an empty date. No other operator keeps them.

    1

    A value in another format stops the entity at the first row, with a message that quotes the value:

    Value Message

    ship_date = 06/30/2026

    Text '06/30/2026' could not be parsed at index 0

    updated_at >= 2026-06-01 00:00:00 (a space instead of T)

    Text '2026-06-01 00:00:00' could not be parsed at index 10

    updated_at >= 2026-06-01 (no time)

    Text '2026-06-01' could not be parsed at index 10

    ship_date >= 2026-06-01T00:00:00 (time on a date field)

    Text '2026-06-01T00:00:00' could not be parsed, unparsed text found at index 10

    Fix the value in Edit settings and start the entity again.

    Can I filter on the relative record number (RRN) of IBM i?

    The relative record number is the position of a row in the physical file. It is not a column, so it is not in the Fields list of Filter out incoming data until you expose it:

    1. In the settings of the IBM i source agent, select _RRN in Technical fields (see Source Agent Advanced Settings);

    2. Map _RRN to a BIGINT column of the target table;

    3. In Edit settings, on the Target tab, select that column in Fields and filter it like any integer.

    An integer column accepts =, !=, >, >=, <, <= and in. On the rrn column (BIGINT) of the example table: >= 1500 kept 5 rows, in 1, 2, 3 kept 3 rows, and a snapshot WHERE clause rrn < 2000 with the filter > 1000 kept 3 rows (1500, 1501 and 1502), because one field carries one condition and the second bound goes in the WHERE clause.

    For a snapshot you can also limit the read in the Source tab with the WHERE clause of the IBM i agent, where the table is named T, for example RRN(T) >= 1000000.

    A relative record number changes when the file is reorganized (RGZPFM), so a filter on an RRN range selects other rows afterwards. Use an RRN filter for a one-off subset of a snapshot, not as a rule that must hold for live changes.

    Troubleshooting

    Problem Possible cause What to do

    The target has more rows than the filter should allow after you changed it

    The snapshot was run with Keep and merge, which leaves rows from earlier runs

    Run the snapshot again with Empty the table first, or with Delete only some rows first and a Selective deletion ahead of snapshot rule

    Rows outside the WHERE clause appear in the target later

    The WHERE clause is not applied to live changes

    Add a target filter with the same rule

    Nothing is written to the target

    The value does not match: wrong case, quotes around the value, or a regular expression that matches only part of the text

    Type the value without quotes and in the same case as the data. For Regex, match the entire value, for example A.* and not A

    Rows with an empty value disappear when you use != or Not in

    Rows with NULL never pass a comparison

    Use Not null or Is null explicitly, or write the rule in the snapshot WHERE clause (for example status <> 'active' OR status IS NULL)

    The entity shows an error such as Text '2025-06-01' could not be parsed at index 10

    A timestamp value without the T, or a date with another format

    Open Edit settings, use yyyy-MM-ddTHH:mm:ss for timestamps and yyyy-MM-dd for dates, save, and start the entity again. The entity retries until you stop it

    The snapshot fails with column "countri" does not exist (or another SQL error)

    The WHERE clause is run by the source database and uses source column names and syntax

    Test the condition with SELECT count(*) FROM source_table WHERE ... in Query Studio on the source agent, then fix the clause

    You need two conditions on the same field

    The Fields list offers each field once

    Use in or Regex for alternatives, or move one bound to the snapshot WHERE clause

    Related guides

    Contact MOLO17 Support if you need assistance with filters or snapshots.