Por qué conservar el historial
Auditoría, deshacer acciones y análisis del pasado.
Por qué conservar el historial es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 1 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.
El problema de sobrescribir datos
Cada vez que ejecuta un UPDATE o un DELETE, los datos antiguos desaparecen para siempre. Parece eficaz, pero crea problemas reales: no puede responder preguntas como ¿cuál era el precio el martes pasado? o ¿quién modificó este registro y cuándo?
Conservar el historial significa almacenar cada versión de una fila, no solo la más reciente. En esta lección explorará por qué es importante y cómo puede hacerlo con SQL.
Tres motivos para conservar el historial
Hay tres motivos clásicos para conservar los datos históricos en una base de datos:
1. Auditoría: demostrar que se produjo un cambio, quién lo realizó y cuándo.
2. Deshacer: revertir un error sin restaurar toda la base de datos.
3. Análisis: responder preguntas sobre el pasado, detectar tendencias y comparar períodos.
Una estrategia de historial bien diseñada satisface las tres necesidades sin duplicar demasiado el almacenamiento.
Una tabla de auditoría sencilla
El enfoque más sencillo consiste en utilizar una tabla de auditoría independiente que registre cada cambio. Cada fila captura el valor anterior, el nuevo valor, quién realizó el cambio y cuándo.
A continuación se muestra una tabla de auditoría para una tabla products. La columna operation almacena INSERT, UPDATE o 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()
);Rellenar la tabla de auditoría
Puede escribir manualmente en una tabla de auditoría, pero el enfoque más fiable es utilizar un trigger de base de datos que se active automáticamente cada vez que cambien los datos. De este modo, ningún código de la aplicación puede omitir el registro.
Aquí se inserta directamente una fila de auditoría para ilustrar la estructura antes de explicar los 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;Leer el registro de auditoría
Cuando se acumulan filas en la tabla de auditoría, puede consultarlas para responder preguntas de auditoría. La consulta siguiente muestra el historial completo de precios de un solo producto, empezando por el más reciente.
SELECT
changed_at,
changed_by,
operation,
old_price,
new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;Fechas de vigencia: historial basado en el tiempo válido
Una tabla de auditoría registra cuándo realizó el cambio (tiempo de transacción). A veces también necesita registrar cuándo era verdadero algo en el mundo real, lo que se denomina tiempo válido.
Añadir las columnas valid_from y valid_to a la tabla principal crea un historial basado en el tiempo válido, denominado en ocasiones dimensión de variación lenta (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');Actualizar un registro de variación lenta
Cuando un empleado cambia de departamento, no debe ejecutar un UPDATE sobre su fila. En su lugar, cierre la fila antigua estableciendo valid_to e inserte una nueva fila abierta. Así se conserva todo el historial.
-- 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;Consultar un momento concreto
Con las columnas de tiempo válido puede preguntar qué era cierto en una fecha concreta, algo imposible con un modelo basado únicamente en UPDATE.
La cláusula WHERE comprueba que la fecha indicada se encuentre dentro del período de validez de la fila.
-- 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);Tablas temporales versionadas por el sistema
Las bases de datos SQL modernas (PostgreSQL 16+, SQL Server y MySQL 8) admiten tablas temporales versionadas por el sistema. La base de datos registra automáticamente el tiempo de transacción en columnas ocultas y permite consultar estados anteriores con una sintaxis especial.
Ejemplo de SQL Server: el concepto es el mismo en los distintos motores:
-- 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;Uso del historial para deshacer cambios
Las tablas de historial no sirven solo para leer datos: también puede utilizarlas para deshacer errores. Si un trabajo por lotes dañara 500 registros de precios, podría restaurarlos desde la tabla de auditoría sin tocar una copia de seguridad.
-- 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';Análisis a lo largo del tiempo
Los datos históricos permiten realizar análisis de series temporales. Puede hacer un seguimiento de la evolución de una métrica, comparar cifras mes a mes o detectar anomalías, todo ello sin utilizar un almacén de datos independiente.
Esta consulta muestra el precio medio de un producto en cada mes natural mediante la tabla de auditoría.
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;Comprobación de conocimientos
Compruebe sus conocimientos sobre el almacenamiento de datos históricos en SQL.
Repaso: por qué conservar el historial
En esta lección ha aprendido por qué sobrescribir datos es arriesgado y cómo los patrones de SQL permiten conservar el historial para auditorías, deshacer cambios y realizar análisis.
Ideas clave:
- Las tablas de auditoría registran cada INSERT, UPDATE y DELETE, junto con quién lo realizó y cuándo.
- Las filas de tiempo de validez (SCD de tipo 2) utilizan las columnas
valid_fromyvalid_topara registrar las cronologías del mundo real. - Las consultas de un momento dado responden preguntas históricas filtrando por esas columnas de fecha.
- Las tablas temporales con versionado del sistema automatizan el seguimiento del tiempo de transacción en el nivel de la base de datos.
- Los datos históricos permiten deshacer cambios de forma selectiva y realizar análisis detallados de series temporales sin necesidad de copias de seguridad.
Conservar el pasado no es una carga adicional: es la base de unos sistemas confiables y auditables.
Preguntas frecuentes
¿La lección «Por qué conservar el historial» es gratis?
Sí — el texto completo de «Por qué conservar el historial» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué aprenderé en «Por qué conservar el historial»?
Auditoría, deshacer acciones y análisis del pasado. Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar SQL Academy?
No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 1 de 4.
¿Cuánto tiempo toma la lección «Por qué conservar el historial»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de SQL Academy?
Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- Por qué conservar el historial
- Tablas de eventos de solo anexado
- Filas temporales y versionadas
- Reconstrucción del estado a partir de eventos