Row Filtering Private Preview

Row filtering enables you to only sync rows that meet user-defined criteria based on a selected table, column, and operator.

Use cases

The primary use cases for row filtering are as follows:

Filtering historical data - For tables where you only want to sync recent data, you can define a filter to sync rows from a specific time period.
Syncing or excluding a specific ID value - If your tables are multitenant or you have a specific ID value you explicitly want to or don’t want to sync, you can use filters to select only relevant rows.
Syncing or excluding records containing a matching string - Similar to filtering by IDs, you can include or exclude rows where a column's string value matches a specified string.

How row filtering works

When a filter is set up, we modify the SELECT statements that run on the source database by appending a WHERE statement based on your filter. This ensures any SELECT queries filter the data based on your filter predicates.

Supported data types

Row filtering is supported for columns with the following data types:

Integer
Serial
Date and Datetime/Timestamp
String

Supported comparison operators

Row filtering supports the following comparison operators:

Equal To
Not Equal To
Greater Than (for Integers, Serial and Date and Timestamp only)
Less Than (for Integers, Serial and Date and Timestamp only)
Greater Than Or Equal To (for Integers, Serial and Date and Timestamp only)
Less Than Or Equal To (for Integers, Serial and Date and Timestamp only)
Contains (for String only)
Starts With (for String only)

Supported connectors

Fivetran supports row filtering for the following connectors:

Limitations

We support row filtering only for initial sync and re-syncs. For incremental syncs, we do not apply row filtering.
You can only create up to three conditions per filter.
You can only create one filter per table.
We store fixed-length STRING columns in MySQL and SQL Server with trailing spaces as padding. If you apply a filter with trailing spaces to these columns, we will ignore it.
In MySQL, incrementally updated data synced during a filtered initial sync will be synced unfiltered.
You can specify dates for filtering operations on DATE, DATETIME, and TIMESTAMP types only if the year falls within the range 1000 to 9999 (inclusive).
You can specify integer values for filtering operations on INTEGER and SERIAL types only if they fall within the range -9007199254740991 to 9007199254740991 (inclusive).

Enabling row filter

For existing connections

To create and apply a row filter, do the following:

On the Schema tab of the Connection Details page, hover over the table you want apply the row filter to.
NOTE: If a table you want to apply a filter to does not have columns of a supported type, No filterable data is displayed for the table.
Click Filter Data. The Create a new filter sidebar opens on the right side of the Connection Details page.
NOTE: If the table you want to apply the filter to does not have columns of supported types, the message No filterable data will be displayed in the table's row.
Select the Column you want to filter by.
Select the Operator. See the list of supported comparison operators.
Specify the Value as INTEGER, DATE(TIME), or STRING, depending on the column type.
(Optional) Click Add to add another condition. Repeat steps 3 to 5 for each condition.
NOTE: You can create up to three conditions per filter.
Click Save and Apply. The Apply Row Filter popup opens, notifying you that the table re-sync is required to apply the filter.
Click Apply filter and re-sync. A table re-sync starts.

TIP: A filtered table has a Filtered badge.

Filtered-table

For newly created connections

To create and apply a row filter, do the following:

On the Schema tab of the Connection Details page, hover over the table you want apply the row filter to.
On the Schema tab of the Connector Details page, hover over the table you want to apply the row filter to.
NOTE: If a table you want to apply a filter to does not have columns of supported type, No filterable data is displayed for the table.
Click Filter Data. The Create a new filter sidebar opens on the right side of the Connection Details page.
NOTE: If the table you want to apply the filter to does not have columns of supported types, the message No filterable data will be displayed in the table's row.
Select the Column you want to filter by.
Select the Operator. See the list of supported comparison operators.
Specify the Value as integer, date(time), or string depending on the column type.
(Optional) Click Add to add another condition. Repeat steps 3 to 5 for each condition.
NOTE: You can create up to three conditions per filter.
Click Save and Apply.
Click Save and Continue. You are navigated to the Schema Change Handling Strategy step of the setup guide. The filter is applied during the initial sync.

TIP: A filtered table has a Filtered badge.

Filtered-table

Editing row filter

To edit a row filter, do the following:

On the Schema tab of the Connection Details page, click Filtered in the relevant table's row. The Filter applied to this table box opens.
Click View filter. The Edit filter sidebar opens on the right side of the Connection Details page.
TIP: You can select to Show details or Close details for each condition.
(Optional) You can edit a condition by modifying one or more of the following parameters:
- (Optional) Select the new Column you want to filter by.
- (Optional) Select the new Operator. See the list of supported comparison operators.
- (Optional) Specify the new Value as integer or date, depending on the column type.
(Optional) You can click Add to add another condition. Follow the instructions in the previous step to define the table column, operator, and value for each added condition.
NOTE: You can create up to three conditions per filter.
(Optional) You can click Remove condition to remove an existing condition.
Click Save and Apply. The Apply Row Filter popup opens, notifying you that the table re-sync is required to apply the filter.
Click Apply filter and re-sync. The table re-sync starts.

Deleting row filter

To delete a row filter, do the following:

On the Schema tab of the Connection Details page, click Filtered in the relevant table's row. The Filter applied to this table box opens.
Click View filter. The Edit filter sidebar opens on the right side of the Connection Details page.
Click Delete filter.
In the Delete data filter and re-sync popup, type DELETE and click Delete filter and re-sync. The table re-sync starts.