Configure Data Blocking and Column Hashing

You can manually block and hash single columns in your connector's schema either when setting up a new connector or when changing the schema of an existing connector.

Additionally, when you set up a new connector, we ask you to define how Fivetran should handle new tables and columns that are created in the source in the future. These Schema Change Handling settings are applied to all tables and columns in the connector schema. For existing connectors, you can change the Schema Change Handling settings at any time.

You can choose from the following Schema Change Handling options:

• Allow all – New schemas, tables, and new columns for existing tables are synced into the destination.
• Allow columns – Only new columns for existing tables are synced into the destination. New schemas and configurable tables* are blocked from being synced to the destination.
• Block all – New schemas, configurable tables*, and new columns for existing tables are not synced into the destination.

You can select Send notifications under New Data Notifications Settings if you want us to still send you a notification email whenever we detect new data objects in the source. In that case, if you have disabled notifications for the connector, you must click Activate in the popup that appears to enable them.

*NOTE: In some connectors with a defined schema, including many of our application connectors, some tables are not configurable. Non-configurable tables cannot be completely blocked. If you select the Block All option, we only sync the primary keys of the non-configurable tables.

Configure existing connectors

You can change the Data Blocking, Column Hashing, and Schema Change Handling settings for any existing connectors, except Magic Folder connectors. Magic Folder connectors don't support these features.

Configure individual columns

Block columns

To block columns, do the following:

1. Go to the Schema tab on your connector details page and uncheck the columns you would like to omit from syncing.

   

2. Click Save changes in the top right corner of the dashboard.

3. A pop-up window opens asking whether you want to Drop blocked columns from the destination. If you don't choose this option, we replace the existing values of the blocked columns in your destination with NULL.

4. Click OK.

Unblock columns

To unblock blocked columns, do the following:

1. Go to the Schema tab on your connector details page and check the columns you would like to sync.
2. Click Save changes in the top right corner of the dashboard.
3. A pop-up window opens asking whether you want to Re-sync to populate columns for existing records,or to keep the NULL values for existing records by choosing Do not re-sync.
4. Click OK. If you chose Re-sync to populate columns for existing records, a re-sync starts.

Hash columns

To hash columns, do the following:

1. Go to the Schema tab on your connector details page and open the Hashed/Unhashed drop-down menu on the columns you'd like to hash.

   

2. Select Hashed.

3. Click Save Changes in the top right corner of the dashboard.

4. A pop-up window opens asking whether you want to replace the existing values of the hashed columns in your destination with NULL by choosing Do not re-sync or to Re-sync to populate columns for existing records with hashed values.

5. Click OK. If you chose Re-sync to populate columns for existing records, a re-sync starts.

Unhash columns

NOTE: Hashing is a one-way operation. You can only unhash the hashed columns if you have access to the original values.

To unhash hashed columns, do the following:

1. Go to the Schema tab on your connector details page and open the Hashed/Unhashed drop-down menu on the columns you'd like to unhash.
2. Select Unhashed.
3. Click Save Changes in the top right corner of the dashboard.
4. A pop-up window opens asking whether you want to keep the NULL values for existing records by choosing Do not re-sync or to Re-sync to overwrite existing hashed data with unhashed values.
5. Click OK. If you chose Re-sync to overwrite existing hashed data, a re-sync starts.

Configure schema change settings for existing connectors

To define the Schema Change Handling settings for your existing connector, do the following:

1. On the Schema tab of your connector's details page, click the gear icon.

   

2. Select one of the provided options from the displayed menu.

3. (Optional) Select Send notifications under New Data Notifications Settings if you want us to still send you a notification email whenever we detect new data objects in the source.

   NOTE: If you have disabled notifications for the connector, you must click Activate in the popup that appears to enable them.

4. Click Save Changes in the top right corner of the dashboard.

Configure new connectors during setup

When you are setting up a new connector, you configure both the Data Blocking and Column Hashing and the Schema Change Handling settings. You can do it either in your Fivetran dashboard as described below or use our REST API, as described in our Connector Management Troubleshooting documentation.

NOTE: Magic Folder connectors don't support these features.

Use data blocking and column hashing with databases and similar connectors

Overview

For the connector types listed below, we fetch the connector schema upon successful completion of setup tests. This allows you to review the schema before the initial sync and block or hash the data columns you do not want to send to the destination.

We fetch the schema after setup tests for the following database connectors:

• MySQL
• Oracle
• PostgreSQL
• SQL Server

We also fetch the schema after setup tests for the following connectors, which behave similarly to databases:

• Marketo
• Microsoft Dynamics 365 CRM
• NetSuite SuiteAnalytics
• Salesforce
• ServiceNow
• Zuora

Instructions

After your newly-created connector's setup tests have succeeded, a Connection test successful! message appears in your dashboard. Follow these steps to review your schema:

1. Click Select your data. Fivetran starts fetching the schema.

2. Review your schema. By default, all tables and columns are checked to be synced into your destination. You can do either of the following:

   • Uncheck the tables and table columns that you don't want to be synced to your destination ( in other words, blocked).
   • Select and hash table columns.
   • Keep the schema unchanged.

   

3. Click Save & Continue.

4. A pop-up asking How would you like to handle changes? appears. To choose how Fivetran should handle new data in the source in the future, select one of the options:

   • Allow all – New schemas, tables, and new columns for existing tables are synced into the destination.
   • Allow columns – Only new columns for existing tables are synced into the destination. New schemas and configurable tables* are blocked from being synced to the destination.
   • Block all – New schemas, configurable tables*, and columns for existing tables are not synced into the destination.

   *NOTE: In some connectors with a defined schema, including many of our application connectors, some tables are not configurable. Non-configurable tables cannot be completely blocked. If you select the Block All option, we only sync the primary keys of the non-configurable tables.

5. (Optional) Select Send notifications under New Data Notifications Settings if you want us to still send you a notification email whenever we detect new data objects in the source. In that case, you must Activate notifications for the connector in the popup that appears.

6. Click Start Initial Sync.

After the initial sync, you can change the blocking and hashing settings in the Schema tab of the connector details page at any time.

Use data blocking and column hashing with application, event, file, and function connectors

Overview

For these connector types as well as the Amazon DynamoDB and MongoDB database connectors, Fivetran must start extracting data from your source to uncover the full schema (tables and columns) that is to be delivered to the destination. If you choose to use data blocking or column hashing for these connector types, we start the extract and present the schema to you once we’ve fetched it. This can take some time for certain connectors.

Some ad reporting connectors such as Facebook Ads and LinkedIn Ads do not offer blocking or hashing before the first sync because you already choose which fields to sync during connector setup.

Instructions

After your newly created application, event, file, or function connector's setup tests have succeeded, the Almost ready to sync pop-up appears.

1. Select one of the displayed options:

   • Block or hash columns first and click Review your data. Fivetran starts an extract to fetch the schema from your source, which enables you to review it and block or hash selected columns before sending data to your destination.

   • Sync all source data and click Start initial sync. All data is synced to your destination right away.

2. If you chose Sync all source data, Fivetran syncs all your data to your destination. Proceed to Step 7.

3. If you chose Block or hash columns first, Fivetran starts fetching the schema.

4. If the schema can be fetched within 60 seconds, the Review your schema pop-up appears. Proceed to Step 7.

5. If fetching the schema takes longer than 60 seconds, a pop-up stating We'll email you when your schema is ready appears.

   NOTE: In some cases, fetching the schema may take up to several days.

   While the schema is being fetched, the connector's status is Incomplete on the Status tab of the connector details page. The Schema tab of the connector details page displays We are still fetching your data schema.

   

6. Once fetching the schema is complete, we send you an email. Click Review your data in the email to navigate to the Schema tab of the connector details page and review your schema.

7. Review your schema. By default, all tables and columns are checked to be synced into your destination. You can do either of the following:

   • Uncheck the tables and table columns that you don't want to be synced to your destination (in other words, blocked)
   • Select and hash table columns
   • Keep the schema unchanged

   

8. Click Save & Continue.

9. The How would you like to handle the changes? pop-up appears requesting you to choose how Fivetran should handle new tables and columns during the syncs that may appear in the source in the future. Select one of the provided options:

   • Allow all – New schemas, tables, and new columns for existing tables are synced into the destination.
   • Allow columns – Only new columns for existing tables are synced into the destination. New schemas and configurable tables* are blocked from being synced to the destination.
   • Block all – New schemas, configurable tables*, and new columns for existing tables are not synced into the destination.

   *NOTE: In some connectors with a defined schema, including many of our application connectors, some tables are not configurable. Non-configurable tables cannot be completely blocked. If you select the Block All option, we only sync the primary keys of the non-configurable tables.

10. (Optional) Select Send notifications under New Data Notifications Settings if you want us to still send you a notification email whenever we detect new data objects in the source.

    NOTE: You must click Activate in the popup that appears to enable notifications for the connector.

    NOTE: We do not send New Data Detected notification emails for connectors that don't support data blocking or column hashing due to limitations in how we track schema changes.

11. Click Start Initial Sync.

After the initial sync, you can change the blocking and hashing settings on the Schema tab of the connector details page at any time.