Data Retention – Archiving or Purging

What does data retention mean?

Data retention means defining how long the data need to be available in the database.
Why data retention policy is required?
  1. Performance will be impacted due to increase of data
  2. The cost will be high if database accumulates data
  3. The old record may not be useful for end user over a certain period of time
What is purging?
It means deleting data from a database which crosses the defined retention time.
 What is archiving?
Archiving means moving the data which crosses the defined retention time to another database (archival database).
 What to select Archiving or Purging?
If the old records won’t be required at any point of time then purging will be applied, else the data will be archived.
A retail shop maintains a data warehouse where all the sales data will be loaded at the month level, as business is growing day by day still  more data will be getting loaded.
The shop has been running for the past 10 years now the data warehouse database size has got increased tremendously.
Also, the shop management says they do not want to view the report at month level for 10-year-old data. Hence they are planning to remove the data older than 10 years.
At the same time, they want to keep the data at year level instead of month level.

So the requirement would be, roll up all month data’s into year level for data older than 10 years and delete the month level data.