Delete Recent Days is useful when the values in your data are updated (mutable) as time goes on.
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:
- Delete all the data in that transformation from the last 10 days
DELETE .... where ts > DATE_ADD('day', -10, SYSDATE)
- Then, complete a normal update to reinsert all the data until today
- 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_atthat gives the timestamp of when the record was deleted, you should do a time_diff on the
deleted_atcolumn 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
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