Schema a stella e progettazione del data warehouse
Tabelle dei fatti e delle dimensioni, compromessi della denormalizzazione e modellazione OLAP.
Schema a stella e progettazione del data warehouse è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 3 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 Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.
OLTP vs OLAP
Le domande sui data warehouse iniziano con una distinzione che gli intervistatori si aspettano Lei sappia padroneggiare: OLTP vs OLAP.
- OLTP (transazionale): molte operazioni di lettura/scrittura di piccole dimensioni, con una forte normalizzazione per garantire l'integrità. Alimenta l'applicazione.
- OLAP (analitico): poche letture di grandi dimensioni che aggregano dati storici, con una denormalizzazione intenzionale per ottenere velocità. Alimenta report e dashboard.
Gli schemi a stella sono una soluzione di progettazione OLAP. L'obiettivo è ottenere query analitiche rapide, accettando la ridondanza come compromesso.
Fatti e dimensioni
Uno schema a stella divide i dati in due tipi di tabelle:
- Tabella dei fatti: gli eventi o le transazioni misurabili (una vendita, un clic). Contiene misure numeriche e chiavi esterne verso le dimensioni.
- Tabelle delle dimensioni: il contesto descrittivo in base al quale si applicano filtri e segmentazioni (data, prodotto, cliente, negozio).
La tabella dei fatti si trova al centro; le dimensioni la circondano come le punte di una stella, da cui deriva il nome.
Struttura di una tabella dei fatti
Una tabella dei fatti contiene soprattutto chiavi esterne e misure numeriche. È lunga e stretta e cresce continuamente.
Le misure sono numeri additivi da aggregare: quantità, ricavi, costi. La granularità (una riga = un ?) deve essere dichiarata chiaramente; in questo caso una riga rappresenta una riga di prodotto di una vendita.
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL, -- FK to dim_date
product_key INT NOT NULL, -- FK to dim_product
customer_key INT NOT NULL, -- FK to dim_customer
store_key INT NOT NULL, -- FK to dim_store
quantity INT, -- measure
revenue DECIMAL(12,2), -- measure
cost DECIMAL(12,2) -- measure
);Struttura di una tabella delle dimensioni
Le dimensioni sono corte e ampie: contengono molte colonne descrittive su cui applicare filtri e raggruppamenti. Sono denormalizzate intenzionalmente, così una query richiede un solo join per dimensione.
Si noti che dim_product mantiene categoria e marca nella stessa riga invece di usare tabelle separate. Questa ridondanza è voluta: evita join aggiuntivi al momento della query.
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_id INT, -- natural/business key
product_name VARCHAR(100),
category VARCHAR(50), -- denormalized
brand VARCHAR(50), -- denormalized
unit_price DECIMAL(10,2)
);Una query su uno schema a stella
Questo è il vantaggio offerto dalla progettazione. Una tipica query analitica esegue il join tra la tabella dei fatti e alcune dimensioni, applica filtri ed esegue aggregazioni. Un join per dimensione, senza catene profonde.
Gli intervistatori Le chiederanno di scrivere esattamente questo tipo di query su uno schema a stella.
SELECT d.category,
t.year,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date t ON t.date_key = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;Chiavi surrogate
Le dimensioni usano una chiave surrogata: una chiave primaria intera priva di significato (come product_key) generata dal data warehouse, separata dalla chiave naturale del sistema sorgente.
Perché è importante nei colloqui:
- Scollega il data warehouse dalle chiavi aziendali soggette a cambiamento.
- Mantiene compatte le tabelle dei fatti (i join tra interi sono veloci).
- È necessaria per mantenere lo storico con le dimensioni a variazione lenta (nella scena successiva).
Dimensioni a variazione lenta
È un argomento molto frequente nei colloqui sui data warehouse: quando cambia un attributo di una dimensione (un cliente cambia città), come lo gestisce? Si tratta delle dimensioni a variazione lenta (SCD):
- Type 1: sovrascrivere il vecchio valore. Nessuno storico.
- Type 2: aggiungere una nuova riga con date di validità e un flag che indica quella corrente. Storico completo; richiede chiavi surrogate.
- Type 3: mantenere una colonna "valore precedente". Storico limitato.
La Type 2 è la risposta più comunemente attesa per tenere traccia dei cambiamenti nel tempo.
-- SCD Type 2 dimension
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, -- surrogate
customer_id INT, -- natural key
city VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current BOOLEAN
);Schema a stella e schema snowflake
Si aspetti una domanda di confronto. Uno schema snowflake normalizza le dimensioni in sottotabelle (product -> category -> department), mentre uno schema a stella le mantiene piatte.
- Stella: meno join, letture più rapide, una certa ridondanza. Preferibile per le prestazioni delle query.
- Snowflake: minor spazio di archiviazione e manutenzione più semplice delle dimensioni, ma più join per ogni query.
Dica: "Per impostazione predefinita scelga lo schema a stella per la velocità delle query; usi snowflake solo quando le dimensioni sono grandi e riutilizzate."
La dimensione data
Quasi ogni schema a stella ha una dimensione data dedicata invece di una semplice colonna data. Precalcola anno, trimestre, mese, giorno della settimana, indicatori dei giorni festivi e periodi fiscali.
Questo permette agli analisti di raggruppare per "trimestre fiscale" o "is_weekend" con un semplice join, invece di distribuire funzioni sulle date in vari punti. Citare spontaneamente una dimensione data è un segnale forte del fatto che Lei abbia già realizzato data warehouse.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20250131
full_date DATE,
year INT,
quarter INT,
month INT,
day_of_week VARCHAR(10),
is_weekend BOOLEAN,
fiscal_qtr VARCHAR(6)
);Scegliere la granularità
La decisione più importante relativa alla tabella dei fatti è la granularità: che cosa rappresenta una riga. La dichiari prima di ogni altra cosa.
- Troppo grossolana (una riga per giorno e per negozio) e si perdono dettagli.
- Troppo fine (una riga per articolo scansionato) e la tabella esplode.
Una dichiarazione chiara della granularità, come "una riga per prodotto per riga d'ordine", determina quali dimensioni e misure appartengono allo schema. Gli intervistatori prestano attenzione a questa disciplina.
Quando denormalizzare
Riporti il discorso alla normalizzazione. I sistemi OLTP vengono normalizzati fino alla 3NF per garantire l'integrità; i data warehouse denormalizzano intenzionalmente le dimensioni per aumentare la velocità di lettura.
Il compromesso che deve saper spiegare:
- La ridondanza dei dati nelle dimensioni è accettabile perché il data warehouse viene caricato tramite ETL controllati, non con scritture applicative ad hoc.
- Meno join significa aggregazioni più rapide su miliardi di righe della tabella dei fatti.
Qui è la capacità di valutazione, non la regola, a distinguere le risposte di livello senior.
Verifica rapida
Sta progettando un data warehouse per i dati delle vendite e deve mantenere lo storico completo della città di un cliente quando si trasferisce.
Riepilogo: schema a stella e progettazione di un data warehouse
Ora è in grado di affrontare le domande sulla modellazione dei data warehouse:
- OLTP normalizza per garantire l'integrità; OLAP denormalizza per aumentare la velocità di lettura.
- Uno schema a stella ha una tabella dei fatti centrale (chiavi esterne e misure numeriche), circondata da dimensioni piatte.
- Usi chiavi surrogate e una dimensione data dedicata.
- Tracci i cambiamenti con SCD Type 2; dichiari prima la granularità della tabella dei fatti.
- Preferisca lo schema a stella allo schema snowflake per le prestazioni delle query.
Domande Frequenti
La lezione «Schema a stella e progettazione del data warehouse» è gratuita?
Sì — il testo completo di «Schema a stella e progettazione del data warehouse» è 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 Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.
Cosa imparerò in «Schema a stella e progettazione del data warehouse»?
Tabelle dei fatti e delle dimensioni, compromessi della denormalizzazione e modellazione OLAP. Eserciti SQL Interview Prep 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 Interview Prep?
Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.
Quanto tempo richiede la lezione «Schema a stella e progettazione del data warehouse»?
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 Interview Prep?
Sì. Ogni lezione SQL Interview Prep 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
- Normalizzazione fino alla 3NF
- Modellazione ER e cardinalità delle relazioni
- Schema a stella e progettazione del data warehouse
- Serie completa di problemi per una simulazione di colloquio