How do I filter deleted Events from the Destination?

When replicating data, Hevo does not remove Events that have been deleted at the Source. Instead, it tracks the deletes through metadata columns. For log-based Pipelines, Hevo sets the __hevo__marked_deleted column to TRUE. For SaaS Sources that support delete tracking, Hevo uses the metadata column from the Source.

To exclude the deleted Events from your Destination tables, you can do one of the following:

• Filter the Events by creating a Model.

• Delete the Events permanently.

  Note: This step is irreversible.

Filter deleted Events from the Destination table

You can filter the deleted Events by creating an SQL Model and exporting the query result into a new Destination table.

To do this, use the following query in the SQL Model:

SELECT *
FROM <table_name>
WHERE <metadata_column> = FALSE;

In the query above, replace <table_name> with the Destination table containing the unfiltered Source data, and <metadata_column> with the column name used to track deletes.

Once the Model is run, the newly created Destination table contains only the Events that still exist in the Source table.

Delete the Events from the Destination table

You can delete the Events directly from the Destination table. To do this, run the following query on the Destination table that contains the deleted and undeleted Source Events:

-- Replace <table_name> with the actual Destination table name
-- and <metadata_column> with the column name used to track deletes

DELETE FROM <table_name>
WHERE <metadata_column> = TRUE;

Note: This step is irreversible and permanently removes the deleted Events from your Destination table.

Example

Consider the Customer table in the Destination for which Hevo tracks deleted Events with the __hevo__marked_deleted column. Based on your requirement, you can do one of the following:

• To filter the deleted Events by creating an SQL Model, use the following query:

  SELECT *
  FROM customer
  WHERE __hevo__marked_deleted = FALSE;
  

• To delete the Events directly, run the following query:

  DELETE from customer
  WHERE __hevo__marked_deleted = TRUE;