We’ve all been there, where we’ve got a Data Extension and we need to know when a row was added to it because there’s an audit or there’s a question about the data in the DE. Especially around imported data, where rows could have been added at any point through an add or an update function. Commonly for this type of requirement, you would be expected to add a field to your data extension that defaults to the current system time when the row gets added.
Which is great if you know you’ll need it in the future.
But, what if you were to introduce a step to your Data Extension creation process that would take care of this for you, without adding an additional attribute to be worried about managing in the future?
Turns out that there’s a hidden attribute on each row in a Data Extension that has Retention Policy configured that can be included in a query and it can be sort of, retrofitted. Credit to Jonas Lamberty for sharing this on StackExchange.
If you have a Data Extension with a Retention Policy configured (which a lot of Data Extensions should be for compliance purposes with GDPR, CCPA and other similar legislations) you can query this hidden attribute and make it visible for analysis purposes.
SELECT PrimaryKey ,[_CreatedDate] as ‘DateCreated’ FROM DataExtension
This attribute appears to contain the date used by Retention Policies to determine when a row should be deleted from the Data Extension. It is specifically the date the record was created and if the record is modified, the date it was modified won’t replace the row. So for situations you need to track modifications on a data extension that isn’t fully overwritten, you may need to use the previous method to keep track.
What do we mean by “sort of retrofitted”? Well, as this date seems to only be of use with Data Extensions with retention policies and retention policies only start to count down when they are activated. By activating a retention policy on a Data Extension that hasn’t previously had it - the moment you apply the policy is the date that gets written to the hidden attribute.
So whilst this doesn’t solve the problem overall, by being thorough with your data retention and data compliance practices overall you’ll gain access to this spiffy little hidden feature. Besides… There’s nothing to stop you from doing this whenever you create a data extension.
Do you have any interesting tips and tricks that you use that don’t appear to be common knowledge? Get in touch and we’d love to share your insight with the wider community!