SQL Academy · Lezione

Schemi a stella e a fiocco di neve

Modelli i dati per analisi rapide.

Lezione 3 di 413 passaggi

Schemi a stella e a fiocco di neve è una lezione SQL Academy 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 Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è lo schema di un data warehouse?

In un database transazionale (OLTP) si normalizzano i dati per evitare la ridondanza. In un data warehouse spesso si denormalizzano intenzionalmente i dati, scambiando spazio di archiviazione per velocità delle query. Due pattern classici per organizzare le tabelle di un data warehouse sono lo schema a stella e lo schema a fiocco di neve.

Entrambi ruotano attorno a una tabella dei fatti centrale circondata da tabelle delle dimensioni. La differenza consiste nel livello di normalizzazione applicato a tali dimensioni.

Tabelle dei fatti e tabelle delle dimensioni

Una tabella dei fatti archivia eventi misurabili, come vendite, clic e spedizioni. È estesa (molte righe) e contiene misure numeriche oltre alle chiavi esterne delle dimensioni.

Una tabella delle dimensioni descrive il contesto di ogni evento: chi, cosa, quando e dove. Le dimensioni sono più strette (meno righe), ma più ricche di colonne descrittive.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  revenue      NUMERIC(12, 2) NOT NULL
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_price   NUMERIC(10, 2)
);

Lo schema a stella

In uno schema a stella, ogni tabella delle dimensioni è collegata direttamente alla tabella dei fatti. Se si disegnano le relazioni su carta, il risultato ricorda una stella: la tabella dei fatti è al centro e le dimensioni sono le punte.

Le tabelle delle dimensioni sono completamente denormalizzate: tutti gli attributi descrittivi risiedono in un'unica tabella, anche se alcuni attributi si ripetono tra le righe.

-- Star schema: all product info in one flat dimension table
CREATE TABLE dim_product (
  product_key    SERIAL PRIMARY KEY,
  product_name   VARCHAR(200),
  category_name  VARCHAR(100),   -- denormalized
  subcategory    VARCHAR(100),   -- denormalized
  brand_name     VARCHAR(100),   -- denormalized
  brand_country  VARCHAR(100),   -- denormalized
  unit_price     NUMERIC(10, 2)
);

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20240315
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  month_name VARCHAR(20),
  week       INT,
  day_of_week VARCHAR(10)
);

Query su uno schema a stella

Le tabelle delle dimensioni piatte rendono le query semplici. Si collega la tabella dei fatti a una o più dimensioni e si esegue l'aggregazione. Non sono necessari join secondari attraverso catene di tabelle normalizzate.

È per questo che gli schemi a stella consentono query analitiche rapide: il grafo dei join è poco profondo.

SELECT
  d.year,
  d.quarter,
  p.category_name,
  SUM(f.revenue)   AS total_revenue,
  SUM(f.quantity)  AS units_sold
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category_name
ORDER BY d.quarter, total_revenue DESC;

Lo schema a fiocco di neve

Uno schema a fiocco di neve normalizza ulteriormente le tabelle delle dimensioni suddividendole in sottodimensioni. Ad esempio, invece di memorizzare category_name e brand_name all'interno di dim_product, si creano tabelle separate dim_category e dim_brand.

Il diagramma risultante ricorda un fiocco di neve: rami che si diramano da tabelle correlate.

-- Snowflake schema: product dimension is normalized
CREATE TABLE dim_brand (
  brand_key     SERIAL PRIMARY KEY,
  brand_name    VARCHAR(100),
  brand_country VARCHAR(100)
);

CREATE TABLE dim_category (
  category_key   SERIAL PRIMARY KEY,
  category_name  VARCHAR(100),
  subcategory    VARCHAR(100)
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category_key INT REFERENCES dim_category(category_key),
  brand_key    INT REFERENCES dim_brand(brand_key),
  unit_price   NUMERIC(10, 2)
);

Query dello schema a fiocco di neve

Eseguire query su uno schema a fiocco di neve richiede più join per ricomporre i dati delle dimensioni suddivisi tra le tabelle. L'ottimizzatore delle query deve attraversare livelli aggiuntivi, che possono aumentare la latenza rispetto a uno schema a stella.

Tuttavia, le dimensioni normalizzate sono più piccole e coerenti: l'aggiornamento del nome di un brand in una riga di dim_brand viene applicato automaticamente ovunque.

SELECT
  d.year,
  c.category_name,
  b.brand_name,
  SUM(f.revenue) AS total_revenue
FROM fact_sales    f
JOIN dim_date      d ON d.date_key    = f.date_key
JOIN dim_product   p ON p.product_key = f.product_key
JOIN dim_category  c ON c.category_key = p.category_key
JOIN dim_brand     b ON b.brand_key    = p.brand_key
WHERE d.year = 2024
GROUP BY d.year, c.category_name, b.brand_name
ORDER BY total_revenue DESC;

Chiavi surrogate e chiavi naturali

Le tabelle delle dimensioni utilizzano in genere una chiave surrogata — un intero generato dal data warehouse, ad esempio SERIAL — invece di una chiave naturale proveniente dal sistema sorgente.

Le chiavi surrogate rimangono stabili anche quando il sistema sorgente cambia, occupano poco spazio nelle tabelle dei fatti di grandi dimensioni e supportano le dimensioni a variazione lenta, nelle quali è necessario conservare la cronologia.

-- Surrogate key (product_key) vs natural key (sku)
INSERT INTO dim_product (product_name, category_key, brand_key, unit_price)
VALUES ('Wireless Headphones', 3, 7, 89.99);
-- product_key is assigned by SERIAL -- the natural key (SKU) lives elsewhere

-- Natural key would be:
-- INSERT INTO dim_product (sku, product_name, ...)
-- VALUES ('WH-1000XM5', 'Wireless Headphones', ...);
-- Risky: SKU can be reused or reassigned by the source system

La dimensione data

La dimensione data è speciale: è quasi sempre presente e in genere viene prepopolata con date relative a molti anni. Memorizzare nella tabella delle dimensioni gli attributi derivati (anno, trimestre, nome del mese, periodo fiscale, indicatore di festività) evita di ricalcolarli al momento della query.

-- Populate dim_date for one year using generate_series
INSERT INTO dim_date (date_key, full_date, year, quarter, month, month_name, week, day_of_week)
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT  AS date_key,
  d                             AS full_date,
  EXTRACT(YEAR    FROM d)::INT  AS year,
  EXTRACT(QUARTER FROM d)::INT  AS quarter,
  EXTRACT(MONTH   FROM d)::INT  AS month,
  TO_CHAR(d, 'Month')           AS month_name,
  EXTRACT(WEEK    FROM d)::INT  AS week,
  TO_CHAR(d, 'Day')             AS day_of_week
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

Dimensioni a variazione lenta (SCD Type 2)

Che cosa succede quando un cliente si trasferisce in un'altra città o un prodotto cambia categoria? È necessario conservare la cronologia. SCD Type 2 inserisce una nuova riga nella dimensione per ogni modifica e chiude quella precedente con una data di fine. La riga della tabella dei fatti continua a puntare alla vecchia chiave della dimensione, preservando la correttezza storica.

-- SCD Type 2 customer dimension
CREATE TABLE dim_customer (
  customer_key  SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  customer_name VARCHAR(200),
  city          VARCHAR(100),
  country       VARCHAR(100),
  valid_from    DATE NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN DEFAULT TRUE
);

-- When a customer moves, close old row and insert new one:
UPDATE dim_customer
   SET valid_to = CURRENT_DATE - 1, is_current = FALSE
 WHERE customer_id = 42 AND is_current = TRUE;

INSERT INTO dim_customer (customer_id, customer_name, city, country, valid_from, is_current)
VALUES (42, 'Alice Muller', 'Berlin', 'Germany', CURRENT_DATE, TRUE);

Schema a stella e a fiocco di neve — compromessi

Nessuno dei due schemi è universalmente migliore. Scelga in base alle proprie priorità:

Stella — meno join, query più veloci, ETL più semplice, costi di archiviazione maggiori. Ideale per strumenti analitici con carichi prevalentemente di lettura (Tableau, Power BI).
Fiocco di neve — dimensioni normalizzate, meno ridondanza, aggiornamento più semplice delle dimensioni, ma più join. Preferibile quando le dimensioni sono grandi o condivise tra più tabelle dei fatti.

-- Checking how much storage the denormalized category column costs
-- in a large dim_product (star schema) vs a separate dim_category (snowflake)
SELECT
  COUNT(*)                               AS total_products,
  COUNT(DISTINCT category_name)          AS unique_categories,
  pg_size_pretty(
    SUM(pg_column_size(category_name))
  )                                      AS category_storage
FROM dim_product;

Schema a galassia (costellazione di fatti)

Quando un data warehouse contiene più tabelle dei fatti che condividono tabelle delle dimensioni, il risultato viene chiamato schema a galassia (o costellazione di fatti). Ad esempio, un data warehouse per la vendita al dettaglio potrebbe avere tabelle dei fatti separate per le vendite e i resi, entrambe con riferimenti a dim_product e dim_date.

Le dimensioni condivise garantiscono filtri coerenti e rendono semplici i confronti tra fatti diversi.

CREATE TABLE fact_returns (
  return_id     SERIAL PRIMARY KEY,
  date_key      INT NOT NULL REFERENCES dim_date(date_key),
  product_key   INT NOT NULL REFERENCES dim_product(product_key),
  customer_key  INT NOT NULL,
  quantity      INT NOT NULL,
  refund_amount NUMERIC(12, 2) NOT NULL
);

-- Cross-fact query: net revenue = sales - refunds
SELECT
  d.year,
  d.month,
  SUM(s.revenue)       AS gross_revenue,
  SUM(r.refund_amount) AS total_refunds,
  SUM(s.revenue) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM dim_date d
LEFT JOIN fact_sales   s ON s.date_key = d.date_key
LEFT JOIN fact_returns r ON r.date_key = d.date_key
WHERE d.year = 2024
GROUP BY d.year, d.month
ORDER BY d.month;

Schema a stella e schema a fiocco di neve

Metta alla prova la propria comprensione degli schemi a stella e a fiocco di neve.

Rispondi nella lezione interattiva

Riepilogo della lezione

In questa lezione ha esplorato due modelli fondamentali di progettazione dei data warehouse:

Schema a stella — una tabella dei fatti centrale circondata da tabelle delle dimensioni piatte e denormalizzate. Meno join, query più veloci, leggermente più spazio di archiviazione.
Schema a fiocco di neve — le tabelle delle dimensioni sono ulteriormente normalizzate in sottodimensioni. Meno ridondanza, aggiornamenti più semplici, ma sono necessari più join.
Le tabelle dei fatti contengono eventi misurabili; le tabelle delle dimensioni forniscono il contesto (chi, che cosa, quando, dove).
Le chiavi surrogate proteggono la correttezza storica e disaccoppiano il data warehouse dalle modifiche del sistema sorgente.
SCD Type 2 tiene traccia della cronologia delle dimensioni aggiungendo nuove righe con date di validità invece di sovrascrivere quelle precedenti.
Quando più tabelle dei fatti condividono le dimensioni, la progettazione diventa uno schema a galassia (costellazione di fatti).

Scelga lo schema a stella per semplicità e velocità; scelga lo schema a fiocco di neve quando le dimensioni sono grandi, vengono aggiornate frequentemente o sono condivise tra molte tabelle dei fatti.

Gratis per iniziare

Impara SQL con un tutor IA — gratis

Scrivi ed esegui vero codice nel tuo browser, ricevi aiuto istantaneo da un tutor IA disponibile 24/7, e riprendi da dove hai lasciato sul web o nell'app.

Corsi: 46
Lezioni: 183

Inizia a imparare gratis Visualizza il corso completo

Domande Frequenti

La lezione «Schemi a stella e a fiocco di neve» è gratuita?

Sì — il testo completo di «Schemi a stella e a fiocco di neve» è 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 «Schemi a stella e a fiocco di neve»?

Modelli i dati per analisi rapide. 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 3 di 4.

Quanto tempo richiede la lezione «Schemi a stella e a fiocco di neve»?

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

← Torna a SQL Academy