Change Data Capture: Sincronizzare i Dati dal Gestionale al Data Warehouse

Quando si porta un gestionale in un data warehouse, la prima versione che si scrive è quasi sempre la stessa: una query enorme che estrae tutto, tutti i giorni, e ricarica le tabelle da zero. Funziona, finché il gestionale è piccolo. Poi la tabella dei movimenti di magazzino arriva a decine di milioni di righe, la finestra notturna non basta più e ti accorgi che stai riscrivendo ogni volta gli stessi dati, di cui magari è cambiato lo 0,1%.

Il Change Data Capture (CDC) è la risposta a questo problema: invece di rileggere tutto, leggi solo ciò che è cambiato. Sembra banale, ma il come cambia molto le cose.

Cosa vuol dire catturare i cambiamenti

In un sistema transazionale i dati cambiano in continuazione: un articolo venduto, una giacenza aggiornata, un prezzo modificato, una riga di scontrino inserita. Il data warehouse non ha bisogno di vedere ogni singolo stato intermedio, ma ha bisogno di sapere che qualcosa è cambiato e di poter ricostruire lo stato finale.

Il CDC si occupa esattamente di questo: identificare le modifiche (insert, update, delete) nel sistema sorgente e propagarle alla destinazione in modo incrementale, con un ritardo che va da pochi secondi a qualche minuto.

Nel retail questo si traduce in domande molto concrete:

• Le vendite di oggi alle 15:00 sono già visibili nella dashboard?
• Se un negozio corregge uno scontrino, il data warehouse lo recepisce?
• Se la pipeline si rompe per due ore, riesco a recuperare i cambiamenti persi?

Una pipeline batch notturna risponde male a tutte e tre.

I tre approcci principali

Ci sono tre modi classici di fare CDC, in ordine crescente di robustezza (e di complicazione).

1. Polling su colonna di timestamp

È il più semplice. Si aggiunge a ogni tabella una colonna updated_at e ogni N minuti si legge tutto ciò che è cambiato dopo l'ultimo giro.

SELECT *
FROM ordini
WHERE updated_at > :ultimo_controllo
  AND updated_at <= now();

Vantaggi: non serve toccare il database, funziona con qualsiasi DB, facile da capire e da debuggare.

Svantaggi: non vedi le cancellazioni (una riga che non c'è più non ha updated_at), e se updated_at non è popolato in modo affidabile da tutte le applicazioni che scrivono, perdi pezzi. Inoltre il polling continuo su tabelle grandi pesa, soprattutto se la colonna non è indicizzata.

Per un gestionale chiuso, dove non controlli il codice applicativo, questo approccio è spesso l'unico praticabile. Ma va accompagnato da una strategia per le delete: di solito un is_deleted flag invece della cancellazione fisica.

2. Trigger sul database

Si crea un trigger che, a ogni modifica, scrive una riga in una tabella di log.

CREATE TABLE cdc_log (
  id           bigserial PRIMARY KEY,
  tabella      text NOT NULL,
  operazione   char(1) NOT NULL,  -- I, U, D
  chiave       text NOT NULL,
  payload      jsonb NOT NULL,
  ts           timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_cambiamento()
RETURNS trigger AS $$
BEGIN
  INSERT INTO cdc_log (tabella, operazione, chiave, payload)
  VALUES (
    TG_TABLE_NAME,
    left(TG_OP, 1),
    COALESCE(NEW.id, OLD.id)::text,
    to_jsonb(COALESCE(NEW, OLD))
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Vantaggi: catturi tutto, anche le delete, e hai un ordine cronologico delle modifiche.

Svantaggi: ogni scrittura applicativa paga un piccolo costo in più, la tabella di log cresce in fretta e va svuotata dopo la lettura, e se il trigger ha un bug rischi di bloccare l'applicazione. Su un gestionale di terze parti è raro poterlo fare.

3. Log-based CDC (logical replication)

Qui il CDC legge direttamente il write-ahead log del database, quello che il DBMS scrive già internamente per garantire durabilità e replicazione. Non serve modificare tabelle, non serve mettere trigger, e si catturano anche le delete.

In PostgreSQL si fa con la logical replication, usando la replica slot e le pubblicazioni.

-- nel postgresql.conf: wal_level = logical

-- nel database sorgente
CREATE PUBLICATION pub_retail
FOR TABLE articoli, ordini, righe_ordine, giacenze;

Il consumatore si connette e riceve uno stream di cambiamenti nel formato di output scelto. Un modo pratico per testarlo è il plugin pgoutput, oppure wal2json se vuoi JSON leggibile.

Vantaggi: impatto quasi nullo sull'applicazione, cattura tutto, ordine garantito, il meccanismo è quello che il database usa già per sé stesso.

Svantaggi: richiede permessi elevati, uno slot di replica consuma spazio su disco se non viene letto, e una gestione attenta dei LSN (log sequence number) per non perdere il punto. È più potente ma anche più delicato.

Un confronto rapido

Nella pratica aziendale mi sono trovato spesso in questa situazione:

• Sul gestionale non posso toccare lo schema → polling su timestamp con flag di cancellazione logica.
• Su database che controllo io (per esempio un servizio interno) → logical replication, perché il gioco vale la candela.
• Su tabelle piccole e poco volatili (anagrafiche, categorie) → full reload ogni notte va benissimo, non complichiamoci la vita.

La tentazione è sempre quella di scegliere l'approccio più elegante per principio. Il criterio giusto è invece: dove sta il collo di bottiglia e quanto costa sbagliare.

Leggere lo stream con Python

Se il database sorgente supporta la logical replication ma non hai voglia di installare Debezium o strumenti più pesanti, si può leggere lo stream direttamente. Con psycopg la cosa è sorprendentemente semplice.

import psycopg

DSN = "host=db-sorgente dbname=retail user=cdc_reader password=..."

with psycopg.connect(DSN) as conn:
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute(
        "CREATE_REPLICATION_SLOT slot_retail LOGICAL pgoutput"
    )
    cur.execute(
        "START_REPLICATION SLOT slot_retail LOGICAL 0/0 "
        "(proto_version '1', publication_names 'pub_retail')"
    )
    for msg in cur:
        # ogni msg contiene un blocco di cambiamenti
        elabora(msg)

Nota che in un vero progetto questo codice va irrobustito: gestione della riconnessione, salvataggio del LSN, ack dei messaggi consumati. Il pezzo sopra serve solo per capire il meccanismo. Ma una volta che lo vedi funzionare su una tabella di prova, tutto il resto diventa chiaro.

Le parti difficili che nessuno racconta

Il CDC è una di quelle cose che sulla carta sono semplici e in produzione hanno insidie. Le tre che mi hanno fatto perdere più tempo:

1. Ordinamento e idempotenza. I cambiamenti possono arrivare fuori ordine o duplicati, soprattutto se la pipeline si riconnette e riprende da un LSN precedente. Ogni riga destinazione dovrebbe essere aggiornata con un upsert idempotente, non con un insert cieco.

INSERT INTO dw.righe_ordine (id, ordine_id, articolo_id, qta, prezzo)
VALUES (:id, :ordine_id, :articolo_id, :qta, :prezzo)
ON CONFLICT (id) DO UPDATE SET
  ordine_id   = EXCLUDED.ordine_id,
  articolo_id = EXCLUDED.articolo_id,
  qta         = EXCLUDED.qta,
  prezzo      = EXCLUDED.prezzo;

2. Cambiamenti di schema. Se il gestionale aggiunge una colonna e tu non aggiorni il consumatore, la pipeline può rompersi a metà notte. Conviene un contratto esplicito: il tuo lettore CDC si aspetta un set di colonne, e un cambiamento di schema passa da una review, non da un aggiornamento silenzioso.

3. Cosa rappresenti nel data warehouse. Non basta applicare i cambiamenti uno a uno. Bisogna decidere se vuoi:

• lo stato corrente (una sola riga per chiave, con l'ultima versione);
• lo storico di tipo SCD Type 2, con una riga per ogni versione e le date di validità;
• oppure entrambi, con una tabella current e una history.

Nel retail questa scelta ha conseguenze enormi: se devi ricostruire le giacenze storiche per fare un'analisi di sell-through su 12 mesi, ti serve lo storico. Se devi solo mostrare il prezzo attuale dell'articolo, ti basta lo stato corrente.

Un criterio pratico per decidere

Prima di scrivere una riga di codice, mi faccio sempre tre domande.

• Quanto sono grandi le tabelle e quanto cambiano? Una tabella da 20 milioni di righe in cui cambiano 500 righe al giorno è un candidato ideale per il CDC; una tabella che cambia al 30% ogni notte non ne beneficia granché.
• Quanto è fresca la dashboard? Se nessuno guarda i dati prima delle 8:00 del mattino, il batch notturno è perfetto. Se serve il cruscotto in tempo reale durante la giornata, il CDC diventa utile davvero.
• Quanto mi costa sbagliare? Le vendite di oggi sbagliate generano decisioni sbagliate. Le anagrafiche di sei mesi fa, quasi mai. Il livello di investimento va calibrato sul danno potenziale, non sull'eleganza della soluzione.

Conclusioni

Il Change Data Capture è una di quelle tecniche che cambiano davvero il modo in cui si progetta un data warehouse: non più ricostruire tutto, ma seguire il flusso di ciò che accade. In sintesi:

• Polling su timestamp: semplice, universale, ma non vede le delete e pesa sul sorgente.
• Trigger: cattura tutto, ma va fatto con criterio e non sempre è possibile.
• Logical replication: la soluzione più pulita, impatto quasi nullo, ma richiede competenza e cura.

La regola d'oro è non innamorarsi dello strumento: caricare a mano una tabella di categorie da 200 righe ogni notte va benissimo. Il CDC serve dove i dati cambiano tanto, dove la freschezza conta, e dove il costo di rileggere tutto supera quello di gestire una pipeline incrementale. Quando quelle condizioni ci sono, la differenza si sente subito — e si smette di guardare l'orologio alle 3 di notte.

Foto di copertina di Albert Stoynov su Unsplash.