How long should a company keep customer data?
A retention period is how long an organisation keeps a kind of data before it deletes it. The right period is as long as the purpose needs, and no longer.
Old data is a risk that grows every year. It can leak, be misused or be asked for, and it costs money to guard. Deleting on time is part of looking after data.
By the end of this lesson
Note
I can set how long data is kept and find the rows that are past it.
Key words
| Word | What it means | Example |
|---|---|---|
| Retention period | how long a kind of data is kept | |
| Retention schedule | a list of retention periods for all your data | |
| Hold | a reason to keep data past its date | an open dispute |
| Purge | delete a batch of old rows | |
| Backup | a spare copy of data, kept in case of loss |
The cells students fill in are left blank in this preview.
A retention period starts when the purpose ends
Each kind of data needs a start and a length. The clock often starts at an event: a ticket closed, an order delivered, an account ended.
Write the periods in a retention schedule, with the reason beside each. The reason is what lets you defend the length.
A hold pauses the clock for one record, for example while a complaint is open.
Worked example: a small retention schedule
| Data | Clock starts | Kept for | Reason |
|---|---|---|---|
| Support ticket | Ticket closed | 24 months | Answer repeat questions |
| Newsletter address | Reader signs up | Until they unsubscribe | They asked for it |
| Delivery address | Order delivered | 12 months | Handle late returns |
| Login record | The login | 90 days | Spot misuse of an account |
The cells students fill in are left blank in this preview.
Compare each date with a cutoff
To find rows past retention, work out a cutoff date: today minus the period. A row is past retention if its date is before the cutoff.
Dates written year, month, day compare correctly as text. A row with no date, such as a ticket still open, is never past retention. NULL is not less than anything.
In the lab, today is fixed at 2026-03-01, so every answer is the same each time.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Work out a cutoff date
sql "SELECT DATE_SUB('2026-03-01', INTERVAL 24 MONTH) AS cutoff"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Try it: chats past retention
-- Today is 2026-03-01. Keep chats for 12 months after the last message.
-- So any chat whose last message is before 2025-03-01 is past retention.
SELECT id, topic, last_message_on
FROM chats
WHERE last_message_on < '2025-03-01'
ORDER BY last_message_on;
-- Change the cutoff to 2025-07-01. Which chats are added?
Purge on a copy first
Deleting cannot be undone, so try it on a copy. Make a copy of the customer table, count the rows, purge, and count again.
The Runs below treat customers who joined before 2023-07-01 as past retention. That is only a teaching rule.
Make a copy of the customers
sql "CREATE TABLE customers_copy AS SELECT * FROM customers"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count the copy before the purge
sql "SELECT COUNT(*) AS before_purge FROM customers_copy"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Delete the rows past the date
sql "DELETE FROM customers_copy WHERE joined_on < '2023-07-01'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count the copy after the purge
sql "SELECT COUNT(*) AS after_purge FROM customers_copy"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Remove the copy when you finish
sql "DROP TABLE customers_copy"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The purge removed eight rows
The cutoff Run prints 2024-03-01. The count before the purge is 40 and the count after is 32, because eight customers joined before 2023-07-01.
Always count the rows you expect to remove, using a SELECT, before you run the DELETE.
A common mistake
Warning
Many people think setting a column such as deleted to 1 deletes the data. The row and the personal data are still there. Delete the row, then make sure copies and backups expire on their own schedule too.