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 features.
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
-
Navigate to the Transformation tab on the left side of your dashboard.
-
Select + Transformation in the top right corner of the screen
- Write a SQL query to purge the soft deleted data. A 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.