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.

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 of the following subtypes of PostgreSQL data types:

• Integer
• Serial
• Date and Timestamp

Supported comparison operators

Row filter supports the following comparison operators:

• Equal To
• Not Equal To
• Greater Than
• Less Than
• Greater Than Or Equal To
• Less Than Or Equal To

Supported connectors

Fivetran supports row filtering for the following connectors:

• Generic PostgreSQL
• Amazon Aurora PostgreSQL
• Amazon Aurora Serverless V2
• Amazon RDS PostgreSQL
• Azure Database for PostgreSQL
• Google Cloud PostgreSQL
• Heroku PostgreSQL

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 one filter per table.

Enabling row filter

For existing connectors

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

1. On the Schema tab of the Connector Details page, hover over the table you want apply the row filter to.

   

2. Click Filter Data. The Create a new filter sidebar opens in the right part of the Connector Details page.

   

   NOTE: If the table you want to apply the filter to does not have columns of supported types, the message There are no columns for filtering will be displayed in the sidebar. Click X to close the sidebar.

3. Specify the Name of the filter.

4. Select the Column you want to filter by.

5. Select the Operator. See the list of supported comparison operators.

6. Specify the Value as integer or date, depending on the column type.

7. Click Save and Apply. The Apply Row Filter popup opens, notifying you about the table re-sync required to apply the filter.

   

8. Click Apply filter and re-sync. A table re-sync starts.

TIP: A filtered table has a Filtered badge.

For newly created connectors

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

1. On the Schema tab of the Connector Details page, hover over the table you want apply the row filter to.

   

2. Click Filter Data. The Create a new filter sidebar opens in the right part of the Connector Details page.

   

   NOTE: If the table you want to apply the filter to does not have columns of supported types, the message There are no columns for filtering will be displayed in the sidebar. Click X to close the sidebar.

3. Specify the Name of the filter.

4. Select the Column you want to filter by.

5. Select the Operator. See the list of supported comparison operators.

6. Specify the Value as integer or date, depending on the column type.

7. Click Save and Apply.

8. 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.

Editing row filter

To edit a row filter, do the following:

1. On the Schema tab of the Connector Details page, click Filtered in the relevant table's row. The Filter applied to this table box opens.

   

2. Click View or edit filter.

3. Select the Column you want to filter by.

4. Select the Operator. See the list of supported comparison operators.

5. Specify the Value as integer or date, depending on the column type.

6. Click Save and Apply. The Apply Row Filter popup opens, notifying you about the table re-sync required to apply the filter.

   

7. Click Apply filter and re-sync. The table re-sync starts.

Deleting row filter

To delete a row filter, do the following:

1. On the Schema tab of the Connector Details page, click Filtered in the relevant table's row. The Filter applied to this table box opens.

   

2. You can delete a row filter either by:

   • Clicking Delete.
   • Clicking View or edit filter and selecting Delete filter in the sidebar.

3. In the Delete data filter and re-sync popup, type DELETE and click DELETE. The table re-sync starts.