Pourquoi conserver l’historique
Auditez, annulez et analysez les données passées.
Pourquoi conserver l’historique est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.
Le problème du remplacement des données
Chaque fois que vous exécutez un UPDATE ou un DELETE, les anciennes données disparaissent pour toujours. Cela semble efficace, mais crée de vrais problèmes : vous ne pouvez pas répondre à des questions comme quel était le prix mardi dernier ? ou qui a modifié cet enregistrement et quand ?
Conserver l’historique signifie stocker chaque version d’une ligne, pas seulement la plus récente. Cette leçon explique pourquoi c’est important et comment SQL vous aide à le faire.
Trois raisons de conserver l’historique
Il existe trois raisons classiques de conserver les données historiques dans une base de données :
1. Audit — prouver qu’une modification a eu lieu, qui l’a effectuée et quand.
2. Annulation — annuler une erreur sans restaurer toute la base de données.
3. Analyse — répondre à des questions sur le passé, repérer les tendances et comparer les périodes.
Une stratégie d’historisation bien conçue satisfait ces trois besoins sans dupliquer une quantité excessive de données.
Une table d’audit simple
L’approche la plus simple consiste en une table d’audit distincte qui enregistre chaque modification. Chaque ligne capture l’ancienne valeur, la nouvelle valeur, l’auteur de la modification et le moment où elle a été effectuée.
Voici une table d’audit pour une table products. La colonne operation stocke INSERT, UPDATE ou 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()
);Alimenter la table d’audit
Vous pouvez écrire manuellement dans une table d’audit, mais l’approche la plus fiable consiste à utiliser un déclencheur de base de données qui se déclenche automatiquement chaque fois que les données changent. Ainsi, aucun code applicatif ne peut contourner le journal.
Nous insérons ici directement une ligne d’audit pour illustrer la structure avant d’aborder les déclencheurs.
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;Lire la piste d’audit
Une fois que les lignes se sont accumulées dans la table d’audit, vous pouvez les interroger pour répondre aux questions d’audit. La requête ci-dessous affiche l’historique complet des prix d’un produit donné, du plus récent au plus ancien.
SELECT
changed_at,
changed_by,
operation,
old_price,
new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;Dates d’effet : historique selon le temps de validité
Une table d’audit enregistre le moment où vous avez effectué la modification (temps de transaction). Parfois, vous devez également suivre le moment où un fait était vrai dans le monde réel — ce que l’on appelle le temps de validité.
Ajouter les colonnes valid_from et valid_to à la table principale crée un historique selon le temps de validité, parfois appelé dimension à évolution lente (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');Mettre à jour un enregistrement à évolution lente
Lorsqu’un employé change de service, vous ne devez pas exécuter UPDATE sur sa ligne. À la place, vous clôturez l’ancienne ligne en définissant valid_to, puis vous insérez une nouvelle ligne ouverte sans date de fin. Cela préserve l’historique complet.
-- 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;Interroger un instant donné
Grâce aux colonnes de temps de validité, vous pouvez demander ce qui était vrai à une date précise — une requête qui serait impossible avec un modèle fondé sur un simple UPDATE.
La clause WHERE vérifie que la date cible se trouve dans la période de validité de la ligne.
-- 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);Tables temporelles versionnées par le système
Les bases de données SQL modernes (PostgreSQL 16+, SQL Server, MySQL 8) prennent en charge les tables temporelles versionnées par le système. La base de données suit automatiquement le temps de transaction dans des colonnes masquées, et vous pouvez interroger les états passés grâce à une syntaxe spéciale.
Exemple avec SQL Server — le concept est le même dans tous les moteurs :
-- 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;Utiliser l’historique pour annuler des actions
Les tables d’historique ne servent pas seulement à la lecture : vous pouvez les utiliser pour annuler des erreurs. Si un traitement par lots a corrompu 500 enregistrements de prix, vous pouvez les restaurer depuis la table d’audit sans toucher à une sauvegarde.
-- 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';Analyses au fil du temps
Les données d’historique permettent d’effectuer des analyses de séries temporelles. Vous pouvez suivre l’évolution d’une métrique, comparer les chiffres d’un mois sur l’autre ou détecter des anomalies, le tout sans utiliser un entrepôt de données distinct.
Cette requête affiche le prix moyen d’un produit pour chaque mois calendaire à l’aide de la table d’audit.
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;Vérification des connaissances
Vérifiez votre compréhension du stockage des données historiques en SQL.
Récapitulatif : pourquoi conserver l’historique
Dans cette leçon, vous avez appris pourquoi l’écrasement des données est risqué et comment les modèles SQL préservent l’historique à des fins d’audit, d’annulation et d’analyse.
Points essentiels :
- Les tables d’audit journalisent chaque INSERT, UPDATE et DELETE, en indiquant qui a effectué l’opération et quand.
- Les lignes à temps de validité (SCD de type 2) utilisent les colonnes
valid_from/valid_topour enregistrer les chronologies du monde réel. - Les requêtes à un instant donné répondent aux questions historiques en filtrant sur ces colonnes de date.
- Les tables temporelles versionnées par le système automatisent le suivi du temps de transaction au niveau de la base de données.
- Les données d’historique permettent une annulation ciblée et de riches analyses de séries temporelles sans nécessiter de sauvegardes.
Préserver le passé n’est pas une surcharge : c’est le fondement de systèmes fiables et auditables.
Questions Fréquemment Posées
La leçon « Pourquoi conserver l’historique » est-elle gratuite ?
Oui — le texte complet de « Pourquoi conserver l’historique » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Pourquoi conserver l’historique » ?
Auditez, annulez et analysez les données passées. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Academy ?
Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.
Combien de temps prend la leçon « Pourquoi conserver l’historique » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?
Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Pourquoi conserver l’historique
- Tables d’événements en ajout uniquement
- Lignes temporelles et versionnées
- Reconstituer l’état à partir des événements