Row filtering

Replicate only the rows that match a condition, with worked examples of common filters, and see how changes apply when rows move in or out.

A row filter keeps only the rows that match a condition, such as one region or the active customers. Set it in the Keep rows where field of the Configure dialog, for one table or for All tables.

Before you begin

• Every filtered table needs a primary key or a unique key on the source.
• A SQL Server source needs ALLOW_SNAPSHOT_ISOLATION on. See Validation and limits.

Write a condition

1. Open Configure for the target.
2. Select a table, or All tables.
3. Type the condition in Keep rows where (for All tables, Keep rows where (all tables)). A blank field keeps every row.

status = 'active'

region_id IN (1, 2, 3) AND deleted_at IS NULL

Use unqualified column names of the table. A row is kept only when the condition is true. When it is false or NULL (unknown, for example status = 'active' on a row whose status is NULL), the row isn't kept.

Examples

The examples are written in T-SQL for a SQL Server source and use a table dbo.Orders with these columns:

Match one value

status = 'shipped'

region_id = 3

Ranges

Write a range as two comparisons. This keeps the orders with 10 to 99 items:

quantity >= 10 AND quantity < 100

Arithmetic works on numbers. This keeps the orders worth at least 1,000:

quantity * unit_price >= 1000

A number compared with a decimal column must fit the column's precision and scale. On the decimal(10,2) column unit_price, unit_price > 9.995 is refused with processing_source_predicate_unsupported.

Lists of values

region_id IN (1, 2, 3)

region_id NOT IN (4, 5)

A list holds 1 to 128 literals. Don't put NULL in a NOT IN list: it makes every row that isn't in the list unknown, so no row is kept.

Missing values

Keep the rows that aren't soft-deleted, or the orders that haven't shipped yet. IS NULL and IS NOT NULL work on a column of any type except binary:

deleted_at IS NULL

shipped_at IS NULL

A comparison with a NULL value is unknown, so status <> 'closed' drops the rows whose status is NULL. Add the NULL case to keep them:

status <> 'closed' OR status IS NULL

COALESCE replaces a NULL before the comparison. This keeps the orders with a discount, and treats a missing discount as 0:

COALESCE(discount, 0) > 0

Boolean columns

A bit column is a condition on its own. Keep the real orders and drop the test orders:

NOT is_test

A NULL in a bit column is unknown, so NOT is_deleted drops it. Keep the rows where is_deleted is false or NULL:

is_deleted IS NULL OR NOT is_deleted

Don't compare a bit column with a number: is_test = 0 is refused with incompatible-expression-types.

Text matching

Text compares exactly, as described in Text comparisons are exact. To match several spellings, list them:

status IN ('active', 'Active', 'ACTIVE')

A char(n) column holds its value padded with spaces to n characters, so warehouse_code = 'LON' never matches the char(5) value 'LON '. Remove the spaces first:

TRIM(warehouse_code) = 'LON'

There is no LIKE. To keep the values that start with a prefix, compare with the prefix and with the prefix whose last character is the next one. This keeps every sku that starts with AB, such as 'AB-100', but not 'ab-100' (lowercase letters sort after uppercase ones) or 'AC-200':

sku >= 'AB' AND sku < 'AC'

Date windows

Row conditions can't compare date and time values: shipped_at >= '2024-01-01' is refused with incompatible-expression-types, and a comparison with a cast date with processing_source_predicate_unsupported.

What works:

• IS NULL and IS NOT NULL on a date column, as in shipped_at IS NOT NULL.

• A range on a numeric column that carries the date. This keeps the orders of 2024:

  order_date_key >= 20240101 AND order_date_key < 20250101

• A range on an increasing key, such as order_id >= 5000000.

Combine conditions

AND binds tighter than OR, so use parentheses. This keeps the orders of regions 1 and 2 and every priority 1 order, when the order isn't a test order and isn't deleted:

(region_id IN (1, 2) OR priority = 1) AND NOT is_test AND deleted_at IS NULL

CASE sets a different threshold per value. This keeps the EU orders worth at least 100 and the other orders worth at least 500:

quantity * unit_price >= CASE region WHEN 'EU' THEN 100 ELSE 500 END

Conditions for all tables

Under All tables, a condition filters every table that has all the columns it names, joined with AND to the table's own condition. A tenant filter is a typical example:

tenant_id IN (1, 2, 3)

With that condition under All tables and status = 'active' on dbo.Orders:

A table that has the columns with a different type isn't skipped: if tenant_id is a text column in one table, the start is refused with incompatible-expression-types.

Conditions that are refused

A condition that SQL Flow can't apply exactly is refused before the start. Test rule shows the code, and the start reports it followed by where it is, such as incompatible-expression-types at expression.

Besides the refusals in the examples above:

LIKE, BETWEEN and functions that aren't in the Expression reference, such as ISNULL or YEAR, are refused too.

How each change is applied

SQL Flow checks the condition on the row as it is after each change, then writes the target by key.

The target therefore always holds exactly the source rows that currently match. For example, with the condition status = 'active':

The initial load

The initial load filters rows in the query that reads the source table, so every condition must translate into the source database's SQL. That is why conditions accept fewer forms than output columns. A condition that can't run in the source query is refused before the start with processing_source_predicate_unsupported.

A text column in a condition needs a declared length of at most 349,525 characters, so (max), text and ntext columns can't be compared. On SQL Server, a varchar or char column in a condition needs a Latin1_General, SQL_Latin1_General_CP1 or UTF-8 (_UTF8) collation; nvarchar and nchar columns work with any collation.

Text comparisons are exact

Text compares as binary UTF-16, whatever the column's collation. Comparisons are case-sensitive and accent-sensitive, and trailing spaces count: status = 'active' doesn't match 'Active' or 'active '. The initial load compares the same way, so the load and streaming always agree.

Tables related by foreign keys

When a filtered table has foreign keys to or from other selected tables, SQL Flow keeps the target referentially intact:

• A parent table without its own condition receives the rows that the kept child rows reference. If another selected child table copies every row, the parent keeps every row too.
• A parent table with its own condition must keep every row that its kept children reference. Where SQL Flow can prove a mismatch before the start, it refuses it with processing_filter_foreign_key_closure_unproved.

For example, with region_id IN (1, 2) on dbo.Orders and no condition on its parent dbo.Customers, the target receives the customers that the kept orders reference.

These relationships must meet the following conditions, or the start is refused with the code shown:

Check how many rows match

In the wizard, select Test rule under the table's rule. The result lists, for each table, whether the rule applies and which columns it filters on. It warns when a filtered column has no index that starts with it: "Some filtering columns have no usable leading index. Testing may scan source rows."

To count matches, choose Sample rows (1 to 100,000 rows per table) or Exact full scan, then select Test ALL matches. On SQL Server, counting needs READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION on the source database, because the count never takes locks.

See also

• Output and computed columns
• Expression reference
• Validation and limits
• Compare