Slowly Changing Dimensions: Storicizzare i Cambiamenti Senza Perdere la Verità

Il problema: la storia che sparisce

In un data warehouse la tabella dei clienti sembra innocua. Ha un identificativo, un nome, una città, forse un segmento commerciale. Poi un cliente si trasferisce, un prodotto cambia categoria, un negozio cambia responsabile. Se l'aggiornamento sovrascrive il valore precedente, il report di domani racconterà una storia diversa anche per gli ordini di due anni fa. Non è un dettaglio tecnico: è un problema di significato dei dati.

Le Slowly Changing Dimensions (SCD) sono il modo canonico per affrontare questo problema. Una dimensione cambia lentamente rispetto ai fatti: gli ordini arrivano ogni giorno, il cliente cambia città ogni tanto. Il tema è quindi come rappresentare nel data warehouse l'evoluzione di un attributo senza perdere la possibilità di rispondere a domande tipo: «quel cliente, al momento dell'ordine, in quale città risultava?».

Questo articolo mostra un modello concreto di SCD di tipo 2 in SQL, i controlli minimi da mettere in piedi e gli errori che rendono la storicizzazione più complicata del necessario.

Cosa sono le Slowly Changing Dimensions

Una dimensione è una tabella di contesto: clienti, prodotti, negozi, fornitori. Una fact table contiene eventi o misure: vendite, ordini, giacenze, pagamenti. La dimensione risponde a «chi, cosa, dove»; la fact a «quanto, quando».

Quando un attributo della dimensione cambia, abbiamo più opzioni.

Nel retail e nei sistemi gestionali il tipo 2 è spesso il default per clienti, prodotti e punti vendita. Non perché sia sempre la scelta giusta, ma perché permette di rispondere a domande storiche senza duplicare l'intera dimensione ogni giorno.

Un esempio: se un cliente cambia città il 10 marzo, con una SCD di tipo 2 avremo due righe. La prima valida fino al 9 marzo incluso, la seconda dal 10 marzo in avanti. Un ordine del 5 marzo si collega alla prima; un ordine dell'11 marzo alla seconda. Con una sovrascrittura entrambi gli ordini finirebbero nella città nuova, falsando un'analisi per area geografica.

Il modello minimo di tipo 2

Un modello di partenza può essere questo:

CREATE TABLE dim_customer (
  customer_sk bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id text NOT NULL,
  name text NOT NULL,
  city text,
  segment text,
  valid_from date NOT NULL,
  valid_to date NOT NULL DEFAULT DATE '9999-12-31',
  is_current boolean NOT NULL DEFAULT true,
  CONSTRAINT valid_range CHECK (valid_to > valid_from)
);

CREATE UNIQUE INDEX one_current_customer
  ON dim_customer (customer_id)
  WHERE is_current;

Alcune scelte importanti:

• Chiave naturale (customer_id): identifica l'entità nel sistema di origine. Non è unica nella dimensione, perché lo stesso cliente ha più versioni.
• Chiave surrogata (customer_sk): identifica la singola versione. È utile per i join e per evitare ambiguità.
• Intervallo semiaperto: [valid_from, valid_to). La data di inizio è inclusa, quella di fine è esclusa. L'ultima versione usa una data sentinella, ad esempio 9999-12-31.
• Flag is_current: comodo per le query operative, ma non va usato per i report storici. La verità sta nelle date.
• Vincolo di unicità parziale: impedisce due versioni correnti per lo stesso cliente. È un controllo economico e prezioso.

La formula dell'intervallo, scritta a parole, è: una riga è valida per un evento se valid_from <= data_evento e data_evento < valid_to. In codice:

valid_from <= data_evento < valid_to

Rilevare i cambiamenti

Il caricamento parte da uno staging con una fotografia corrente del cliente. Supponiamo che contenga una riga per cliente e una data di efficacia business, non la data di caricamento.

CREATE TEMP TABLE staged_customer AS
SELECT customer_id, name, city, segment, effective_date
FROM staging_customer;

Per capire quali clienti sono cambiati, confrontiamo gli attributi tracciati con la versione corrente. In PostgreSQL il confronto riga per riga con IS DISTINCT FROM gestisce anche i NULL senza sorprese.

CREATE TEMP TABLE changed_customer AS
SELECT s.*
FROM staged_customer s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current
WHERE (d.name, d.city, d.segment)
      IS DISTINCT FROM (s.name, s.city, s.segment);

I clienti nuovi sono quelli che non hanno alcuna versione in dimensione:

CREATE TEMP TABLE new_customer AS
SELECT s.*
FROM staged_customer s
LEFT JOIN dim_customer d
  ON d.customer_id = s.customer_id
WHERE d.customer_id IS NULL;

Questo approccio funziona se lo staging contiene una sola riga per cliente. Se ce ne sono più di una, bisogna prima decidere quale sia la fotografia valida: l'ultima per data di aggiornamento, o quella con la data di efficacia più recente. Non lasciare che sia il caso a deciderlo.

Chiudere la versione vecchia e inserire la nuova

L'aggiornamento va fatto in transazione. Prima si chiude la versione corrente, poi si inserisce la nuova. Se la data di efficacia è successiva alla data di inizio della versione corrente, possiamo aggiornare valid_to e is_current.

BEGIN;

UPDATE dim_customer d
SET valid_to = s.effective_date,
    is_current = false
FROM changed_customer s
WHERE d.customer_id = s.customer_id
  AND d.is_current
  AND s.effective_date > d.valid_from;

INSERT INTO dim_customer (
  customer_id, name, city, segment,
  valid_from, valid_to, is_current
)
SELECT customer_id, name, city, segment,
       effective_date, DATE '9999-12-31', true
FROM changed_customer
WHERE effective_date > (
  SELECT d.valid_from
  FROM dim_customer d
  WHERE d.customer_id = changed_customer.customer_id
    AND d.is_current = false
  ORDER BY d.valid_from DESC
  LIMIT 1
)
UNION ALL
SELECT customer_id, name, city, segment,
       effective_date, DATE '9999-12-31', true
FROM new_customer;

COMMIT;

La sottoquery nell'INSERT è solo un esempio didattico: in produzione è meglio portarsi dietro la data di inizio della versione chiusa in una tabella temporanea, per evitare condizioni fragili. La logica da rispettare è semplice: la nuova riga deve iniziare esattamente quando finisce la precedente, senza buchi e senza sovrapposizioni.

Attenzione al caso in cui effective_date coincida con valid_from della versione corrente. Non è un cambio di versione: è una correzione sulla stessa data. Se gli attributi sono sbagliati, si aggiorna la riga esistente (di fatto un tipo 1); se si inserisse una nuova riga, il vincolo valid_to > valid_from fallirebbe o si creerebbe un intervallo vuoto. Serve una regola esplicita nel processo, non un UPDATE che passa per caso.

Late arriving data: quando il passato arriva dopo

Il caso difficile è la correzione con data di efficacia passata. Esempio: il 20 marzo scopriamo che il cliente aveva cambiato città il 1 febbraio. La versione corrente parte dal 10 marzo. Non basta chiudere la riga corrente: bisogna inserire una versione valida dal 1 febbraio al 9 marzo e spostare l'inizio della versione corrente al 10 marzo, oppure spezzare l'intervallo esistente.

La procedura corretta è:

1. individuare la versione che contiene la data di efficacia;
2. se la data coincide con valid_from, aggiornare gli attributi di quella versione;
3. se la data è interna all'intervallo, spezzare la riga in due: una che termina alla data di efficacia e una che riparte dalla stessa data con i nuovi attributi;
4. verificare che non si creino sovrapposizioni.

Questo è il motivo per cui conviene avere un controllo automatico sugli intervalli, non solo un vincolo sulla singola riga. Un modello SCD senza test sugli intervalli è un modello che sembra funzionare fino alla prima correzione retroattiva.

Query point-in-time

La query storica non deve filtrare su is_current. Deve collegare il fatto alla versione valida in quel momento.

SELECT
  f.order_id,
  d.city,
  sum(f.amount) AS totale
FROM fact_orders f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.valid_from
 AND f.order_date < d.valid_to
GROUP BY f.order_id, d.city;

Se la fact table ha una data e la dimensione ha intervalli di date, il join è corretto solo se le due granularità coincidono. Se la fact ha un timestamp e la dimensione date, bisogna decidere se confrontare la data dell'evento o il timestamp normalizzato. La scelta va documentata: un ordine delle 23:30 del 9 marzo appartiene al 9 marzo o al 10? Dipende dal fuso orario e dalla regola di business. Non lasciare che sia il database a improvvisare.

Controlli di qualità

Alcuni test SQL da eseguire dopo ogni caricamento:

-- Più di una versione corrente per cliente
SELECT customer_id, count(*)
FROM dim_customer
WHERE is_current
GROUP BY customer_id
HAVING count(*) > 1;

-- Intervalli sovrapposti
SELECT a.customer_id
FROM dim_customer a
JOIN dim_customer b
  ON a.customer_id = b.customer_id
 AND a.customer_sk < b.customer_sk
 AND a.valid_from < b.valid_to
 AND b.valid_from < a.valid_to;

-- Buchi tra versioni consecutive
SELECT customer_id, valid_to, next_from
FROM (
  SELECT customer_id, valid_to,
         lead(valid_from) OVER (
           PARTITION BY customer_id ORDER BY valid_from
         ) AS next_from
  FROM dim_customer
) t
WHERE next_from IS NOT NULL
  AND valid_to <> next_from;

Il controllo sui buchi presuppone che ogni cliente abbia una copertura continua dalla prima all'ultima versione. Non è sempre vero: un cliente può essere cancellato e ricreato, oppure può esistere un periodo in cui non era attivo. In quei casi il test va adattato, ma la domanda resta: il buco è un dato mancante o un evento di business?

Errori comuni

• Usare la data di caricamento come data di validità. Il data warehouse sa quando ha ricevuto il dato, non quando è cambiato nella realtà. Sono due cose diverse.
• Sovrascrivere gli attributi senza tracciare la storia. Va bene per le correzioni, non per i cambiamenti reali.
• Mischiare tipo 1 e tipo 2 senza regole. Se alcuni attributi si sovrascrivono e altri no, il significato della riga diventa ambiguo.
• Dimenticare le cancellazioni logiche. Un cliente disattivato è una versione con una data di fine, non una riga che sparisce.
• Joinare sulla chiave naturale senza le date. Due versioni dello stesso cliente producono duplicazioni nei report.
• Non gestire i NULL nel confronto. Un NULL che diventa stringa vuota non è un cambiamento, ma un problema di normalizzazione.

Quando il tipo 2 non serve

La storicizzazione ha un costo: più righe, join più complessi, caricamenti più delicati. Se un attributo non viene mai usato per analisi storiche, il tipo 1 è più semplice. Se il reporting è solo per periodo chiuso, una snapshot periodica può essere sufficiente. Il tipo 3 ha senso solo per un singolo confronto «prima/dopo» e raramente vale la complessità.

La domanda da farsi è concreta: qualcuno userà mai questo attributo per rispondere a una domanda sul passato? Se la risposta è no, non serve una SCD di tipo 2. Se la risposta è sì, allora vanno messi i controlli, non solo le colonne.

Conclusione

Le Slowly Changing Dimensions di tipo 2 non sono un esercizio teorico: sono il modo per evitare che un aggiornamento cancelli il contesto storico di un fatto. I punti chiave:

• usa intervalli semiaperti [valid_from, valid_to) e una data sentinella per la versione corrente;
• distingui chiave naturale e chiave surrogata;
• confronta gli attributi con una logica che gestisca i NULL;
• chiudi la versione vecchia e inserisci la nuova nella stessa transazione;
• gestisci esplicitamente le correzioni retroattive;
• aggiungi test per versioni correnti duplicate, sovrapposizioni e buchi;
• non usare is_current per i report storici: usa le date.

Un modello SCD è affidabile quando le sue date raccontano una storia coerente. Il resto sono solo righe in più.

Foto di copertina di Domaintechnik su Unsplash.