Use Fivetran Transformations to Hash Confidential Data

Use Case/Scenario:

You have confidential data in your datasource that should be hashed/masked when it is transferred to your destination. Column hashing and column blocking is currently unavailable for this specific datasource.

Environment:

This is relevant for any connector which does not have column hashing or column blocking available.

Fivetran Recommendation:

You can use Fivetran Transformations to implement column hashing on your confidential data. The following steps allow you to do this;

1. Open Fivetran UI
2. Click Transformations 
3. Click + Transformation
4. Name the Transformation
5. Write the SQL code in the SQL Script box that will allow your data to be hashed when it arrives in the destination. The code below is an example of how this works for the Xero connector

You can use the ‘sha256’ or the ‘md5‘ function to complete this transformation.

update xero.account

set tax_type = md5(tax_type),

       code = md5(code),

        class = md5(class)

where account_id = '1111';

    6. Choose how you would like this Transformation to be implemented, either when new data arrives      (New Data) or at a specific time each day (Time Schedule). 

Considerations:

It will not be possible to decrypt the hashed data in the destination using this method. Therefore if it is necessary to be able to decrypt the data, consider another method of hashing the data. Implement this code for one account at the outset to ensure the result in your destination is as required.