0Pricing
SQL Academy · Lezione

Perché conservare la cronologia

Audit, annullamenti e analisi del passato.

Perché conservare la cronologia è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Il problema della sovrascrittura

Ogni volta che si esegue un UPDATE o un DELETE, i dati precedenti scompaiono per sempre. Può sembrare efficiente, ma crea problemi concreti: non è possibile rispondere a domande come qual era il prezzo martedì scorso? oppure chi ha modificato questo record e quando?

Conservare la cronologia significa memorizzare ogni versione di una riga, non solo quella più recente. Questa lezione illustra perché è importante e come SQL consente di farlo.

Tre motivi per conservare la cronologia

Esistono tre motivi classici per conservare i dati storici in un database:

1. Audit — dimostrare che una modifica è avvenuta, chi l'ha effettuata e quando.
2. Annullamento — annullare un errore senza ripristinare l'intero database.
3. Analisi — rispondere a domande sul passato, individuare tendenze e confrontare periodi.

Una strategia di conservazione della cronologia ben progettata soddisfa tutte e tre le esigenze senza duplicare una quantità eccessiva di dati.

Una semplice tabella di audit

L'approccio più semplice consiste in una tabella di audit separata che registra ogni modifica. Ogni riga memorizza il valore precedente, il nuovo valore, chi ha effettuato la modifica e quando.

Di seguito è riportata una tabella di audit per una tabella products. La colonna operation memorizza 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()
);

Popolamento della tabella di audit

È possibile scrivere manualmente in una tabella di audit, ma l'approccio più affidabile consiste nell'utilizzare un trigger del database che si attiva automaticamente ogni volta che cambiano i dati. In questo modo nessun codice dell'applicazione può eludere il registro.

Qui si inserisce direttamente una riga di audit per illustrarne la struttura prima di occuparsi dei trigger.

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;

Lettura della traccia di audit

Una volta accumulate le righe nella tabella di audit, è possibile interrogarla per rispondere alle domande di audit. La query seguente mostra la cronologia completa dei prezzi di un singolo prodotto, dalla più recente alla meno recente.

SELECT
  changed_at,
  changed_by,
  operation,
  old_price,
  new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;

Date di validità: cronologia del tempo valido

Una tabella di audit registra quando è stata effettuata la modifica (tempo della transazione). A volte è inoltre necessario tenere traccia di quando qualcosa era vero nel mondo reale: questo è chiamato tempo di validità.

L'aggiunta delle colonne valid_from e valid_to alla tabella principale crea una cronologia basata sul tempo di validità, talvolta chiamata dimensione a variazione 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');

Aggiornare un record a variazione lenta

Quando un dipendente cambia reparto, non si aggiorna la sua riga con UPDATE. Si chiude invece la riga precedente impostando valid_to e si inserisce una nuova riga priva di una data di fine. In questo modo si conserva l'intera cronologia.

-- 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;

Eseguire query a un dato momento

Con le colonne del tempo di validità è possibile chiedere che cosa fosse vero in una data specifica, una query impossibile con un semplice modello basato su UPDATE.

La clausola WHERE verifica che la data di riferimento rientri nell'intervallo di validità della riga.

-- 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);

Tabelle temporali con versione di sistema

I moderni database SQL (PostgreSQL 16+, SQL Server, MySQL 8) supportano le tabelle temporali con versione di sistema. Il database tiene automaticamente traccia del tempo della transazione in colonne nascoste e consente di interrogare gli stati passati con una sintassi speciale.

Esempio con SQL Server: il concetto è lo stesso in tutti i motori:

-- 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;

Utilizzare la cronologia per annullare modifiche

Le tabelle di cronologia non servono solo per leggere i dati: possono essere utilizzate anche per annullare gli errori. Se un processo batch ha danneggiato 500 record di prezzi, è possibile ripristinarli dalla tabella di audit senza ricorrere a un 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';

Analisi nel tempo

I dati storici permettono di eseguire analisi di serie temporali. È possibile monitorare l'evoluzione di una metrica, confrontare i valori mese su mese o rilevare anomalie, il tutto senza utilizzare un data warehouse separato.

Questa query mostra il prezzo medio di un prodotto per ogni mese di calendario utilizzando la tabella di 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;

Verifica delle conoscenze

Verificate la vostra comprensione dell'archiviazione dei dati storici in SQL.

Riepilogo: perché conservare la cronologia

In questa lezione avete imparato perché sovrascrivere i dati è rischioso e come i pattern SQL consentono di conservare la cronologia per l'audit, l'annullamento e l'analisi.

Punti chiave:

  • Tabelle di audit registrano ogni INSERT, UPDATE e DELETE, indicando chi ha eseguito l'operazione e quando.
  • Le righe con tempo di validità (SCD Type 2) utilizzano le colonne valid_from / valid_to per registrare le sequenze temporali del mondo reale.
  • Le query a un istante specifico rispondono a domande storiche filtrando in base a queste colonne di data.
  • Le tabelle temporali con versione di sistema automatizzano il monitoraggio del tempo delle transazioni a livello di database.
  • I dati storici permettono un annullamento mirato e analisi approfondite di serie temporali senza dover ricorrere a backup.

Conservare il passato non è un onere aggiuntivo: è il fondamento di sistemi affidabili e verificabili.

Domande Frequenti

La lezione «Perché conservare la cronologia» è gratuita?

Sì — il testo completo di «Perché conservare la cronologia» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Perché conservare la cronologia»?

Audit, annullamenti e analisi del passato. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «Perché conservare la cronologia»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Perché conservare la cronologia
  2. Tabelle di eventi append-only
  3. Righe temporali e versionate
  4. Ricostruire lo stato dagli eventi
← Torna a SQL Academy