Question
Fivetran is syncing Personally Identifiable Information(PII) to the warehouse, how can I remove this data when it is deleted in the source?
Answer
One way to remove data is by making use of the Fivetran transformations and DBT feature.
You can write a post-load transformation to delete or mask data in the warehouse where the "_fivetran_deleted" column is "TRUE". These transformations can be triggered on a set time schedule or based on a table trigger from the Fivetran Dashboard.
Steps to do this:
- Navigate to the "transformation" tab on the left side of you dashboard
-
Select "+ Transformation" in the top right corner of the screen
- Write a SQL query to purge the soft deleted data. An pseudo example of this would be:
Delete from table where _fivetran_deleted is True - Set the schedule to run the query at the bottom of the screen.
Based on your transformation, data that has been deleted from the source will now be removed from the warehouse.
Cause
Fivetran does not hard delete any data from customer warehouses. Fivetran only soft deletes data with the "_fivetran_deleted" column set to "TRUE".