When should I use "Delete Recent Days"?

When to use it?

Delete Recent Days is useful when the values in your data are updated (mutable) as time goes on.

Common Example: Ad Data

The most common application is on advertising data.

Why is advertising data mutable?
Typically metrics are reported on a daily basis and thus the last 2 days will be changing as more data is collected.
Ex. CPC on 8/2 will be changing as more data is collected during that day.

How to use "Delete Recent Days"?
In this case you may want to delete the last 10 days (to be safe) on every update.

This means that every time the Activity Stream updates it will:

1. Delete all the data in that transformation from the last 10 days
   DELETE .... where ts > DATE_ADD('day', -10, SYSDATE)
2. Then, complete a normal update to reinsert all the data until today

Other times to use "Delete Recent Days"

• Your source data is a daily aggregation
  If your transformation uses a table that is an aggregation, the last row is often changing every couple of hours with the most recent updates.

• The source data has records that are sometimes deleted soon after being created (they were created by mistake)
  For example, if you have a column called deleted_at that gives the timestamp of when the record was deleted, you should do a time_diff on the created_at and deleted_at column to filter out any records that were deleted within a few hours of being created. This indicates a data entry error. To prevent this data from being added to your activity stream, use "Delete Recent Days" to ensure that records that were erroneously added are not inserted into your Activity Stream.

• Your source data has delayed processing
  If you use another data source (ex. Clearbit) to enrich all the leads in your warehouse. And you want the enrichment to occur before the data is inserted into the Activity Stream. Use delete recent days to account for the fact that the data is still changing for recent records.
  Note: For lead enrichment, Narrator recommends putting all relevant enrichment columns in the customer table instead

Still have questions?

Our data team is here to help! Here are a couple ways to get in touch...

💬 Chat us from within Narrator

💌 Email us at [email protected]

🗓 Or schedule a 15 minute meeting with our data team