Why Keep History
Audit, undo and analytics from the past.
Why Keep History is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Problem With Overwriting
Every time you run an UPDATE or DELETE, the old data disappears forever. That sounds efficient, but it creates real problems: you cannot answer questions like what was the price last Tuesday? or who changed this record and when?
Keeping history means storing every version of a row, not just the latest one. This lesson explores why that matters and how SQL helps you do it.
Three Reasons to Keep History
There are three classic reasons to preserve historical data in a database:
1. Audit — prove that a change happened, who made it, and when.
2. Undo — roll back a mistake without restoring the whole database.
3. Analytics — answer questions about the past, spot trends, and compare periods.
A well-designed history strategy satisfies all three needs without duplicating too much storage.
A Simple Audit Table
The simplest approach is a separate audit table that records every change. Each row captures the old value, the new value, who made the change, and when.
Below is an audit table for a products table. The operation column stores INSERT, UPDATE, or DELETE.
CREATE TABLE products_audit (
audit_id SERIAL PRIMARY KEY,
product_id INT NOT NULL,
operation VARCHAR(6) NOT NULL, -- INSERT / UPDATE / DELETE
old_price NUMERIC(10,2),
new_price NUMERIC(10,2),
changed_by TEXT NOT NULL,
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);Populating the Audit Table
You can write to an audit table manually, but the most reliable approach is a database trigger that fires automatically whenever data changes. This way no application code can bypass the log.
Here we insert one audit row directly to illustrate the structure before covering triggers.
INSERT INTO products_audit (product_id, operation, old_price, new_price, changed_by)
VALUES (42, 'UPDATE', 9.99, 12.49, 'alice');
SELECT * FROM products_audit ORDER BY changed_at DESC LIMIT 5;Reading the Audit Trail
Once rows accumulate in the audit table you can query them to answer audit questions. The query below shows the full price history for a single product, most recent first.
SELECT
changed_at,
changed_by,
operation,
old_price,
new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;Effective Dates: Valid-Time History
An audit table records when you made the change (transaction time). Sometimes you also need to track when something was true in the real world — called valid time.
Adding valid_from and valid_to columns to the main table creates a valid-time history, sometimes called a slowly-changing dimension (SCD Type 2).
CREATE TABLE employee_history (
id SERIAL PRIMARY KEY,
employee_id INT NOT NULL,
department TEXT NOT NULL,
salary NUMERIC(10,2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE -- NULL means current record
);
-- Current record for employee 7
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Engineering', 85000, '2023-01-01');Updating a Slowly-Changing Record
When an employee moves departments you do not UPDATE their row. Instead you close the old row by setting valid_to and insert a new open-ended row. This preserves the full history.
-- Step 1: close the current record
UPDATE employee_history
SET valid_to = '2024-06-01'
WHERE employee_id = 7 AND valid_to IS NULL;
-- Step 2: insert the new record
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Product', 90000, '2024-06-01');
-- Verify history
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
ORDER BY valid_from;Querying a Point in Time
With valid-time columns you can ask what was true on a specific date — a query that would be impossible with a plain UPDATE model.
The WHERE clause checks that the target date falls inside the row's validity window.
-- What department and salary did employee 7 have on 2023-09-15?
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
AND valid_from <= '2023-09-15'
AND (valid_to > '2023-09-15' OR valid_to IS NULL);System-Versioned Temporal Tables
Modern SQL databases (PostgreSQL 16+, SQL Server, MySQL 8) support system-versioned temporal tables. The database automatically tracks transaction time in hidden columns, and you can query past states with a special syntax.
SQL Server example — the concept is the same across engines:
-- SQL Server / MariaDB style (illustrative)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
status VARCHAR(20),
total NUMERIC(10,2),
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);
-- Query historical state
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-15 12:00:00'
WHERE order_id = 100;Using History for Undo
History tables are not just for reading — you can use them to undo mistakes. If a batch job corrupted 500 price records, you can restore them from the audit table without touching a backup.
-- Undo all price changes made by the bad batch job at a specific time
UPDATE products p
SET price = a.old_price
FROM products_audit a
WHERE p.id = a.product_id
AND a.operation = 'UPDATE'
AND a.changed_by = 'batch_job'
AND a.changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';
-- Confirm affected rows
SELECT COUNT(*) AS rows_restored FROM products_audit
WHERE changed_by = 'batch_job'
AND changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';Analytics Over Time
History data unlocks time-series analytics. You can track how a metric evolved, compare month-over-month figures, or detect anomalies — all without touching a separate data warehouse.
This query shows the average price of a product in each calendar month using the audit table.
SELECT
DATE_TRUNC('month', changed_at) AS month,
ROUND(AVG(new_price), 2) AS avg_price
FROM products_audit
WHERE product_id = 42
AND operation IN ('INSERT', 'UPDATE')
GROUP BY 1
ORDER BY 1;Knowledge Check
Test your understanding of historical data storage in SQL.
Recap: Why Keep History
In this lesson you learned why overwriting data is risky and how SQL patterns preserve history for audit, undo, and analytics.
Key takeaways:
- Audit tables log every INSERT, UPDATE, and DELETE with who did it and when.
- Valid-time (SCD Type 2) rows use
valid_from/valid_tocolumns to record real-world timelines. - Point-in-time queries answer historical questions by filtering on those date columns.
- System-versioned temporal tables automate transaction-time tracking at the database level.
- History data enables surgical undo and rich time-series analytics without needing backups.
Preserving the past is not overhead — it is the foundation for trustworthy, auditable systems.
Frequently asked questions
Is the “Why Keep History” lesson free?
Yes — the full text of “Why Keep History” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Why Keep History”?
Audit, undo and analytics from the past. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Why Keep History” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.