SQL Academy · Lektion

Stjerne- og snefnugsskemaer

Modellér data til hurtig analyse

Lektion 3 af 413 trin

Stjerne- og snefnugsskemaer er en gratis SQL Academy-lektion på CoddyKit. Dette er lektion 3 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i SQL Academy, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. SQL Academy-kurset indeholder 4 lektioner i alt.

Hvad er et datavarehusskema?

I en transaktionsdatabase (OLTP) normaliserer du data for at undgå redundans. I et datavarehus denormaliserer du ofte data med vilje — du bytter lagerplads for forespørgselshastighed. To klassiske mønstre til organisering af datavarehustabeller er stjerneskemaet og snefnugsskemaet.

Begge tager udgangspunkt i en central faktatabel omgivet af dimensionstabeller. Forskellen er, hvor langt du normaliserer disse dimensioner.

Faktatabeller og dimensionstabeller

En faktatabel gemmer målbare hændelser — salg, klik og forsendelser. Den har mange rækker og indeholder numeriske mål samt fremmednøgler til dimensioner.

En dimensionstabel beskriver konteksten for hver hændelse: hvem, hvad, hvornår og hvor. Dimensioner har færre rækker, men er rigere på beskrivende kolonner.

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

Stjerneskemaet

I et stjerneskema er alle dimensionstabeller forbundet direkte med faktatabellen. Tegn relationerne på papir, så ligner det en stjerne — faktatabellen er centrum, og dimensionerne er spidserne.

Dimensionstabeller er fuldt denormaliserede: Alle beskrivende attributter ligger i en enkelt tabel, selv hvis nogle attributter gentages på tværs af rækker.

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

Forespørgsel i stjerneskemaet

De flade dimensionstabeller gør forespørgsler enkle. Du kobler faktatabellen til én eller flere dimensioner og aggregerer. Der er ingen sekundære sammenkoblinger via kæder af normaliserede tabeller.

Det er derfor, stjerneskemaer leverer hurtige analytiske forespørgsler — sammenkoblingsgrafen har få niveauer.

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;

Snefnugskemaet

Et snefnugskema normaliserer dimensionstabeller yderligere ved at opdele dem i underdimensioner. I stedet for for eksempel at gemme category_name og brand_name i dim_product opretter du separate tabeller, dim_category og dim_brand.

Det resulterende diagram ligner et snefnug – forgrenede arme af relaterede tabeller.

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

Forespørgsel på snefnugskemaet

Forespørgsler på et snefnugskema kræver flere joins for at samle dimensionsdata, der er opdelt på tværs af tabeller. Forespørgselsoptimeringen skal gennemløbe de ekstra niveauer, hvilket kan øge latenstiden sammenlignet med et stjerneskema.

De normaliserede dimensioner er dog mindre og konsistente – hvis du opdaterer et mærkenavn i én række i dim_brand, gælder ændringen automatisk overalt.

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;

Surrogatnøgler kontra naturlige nøgler

Dimensionstabeller bruger typisk en surrogatnøgle – et heltal genereret af datalageret (f.eks. SERIAL) – i stedet for en naturlig nøgle fra kildesystemet.

Surrogatnøgler er stabile, selv når kilden ændres, de fylder kun lidt i store faktatabeller, og de understøtter langsomt ændrende dimensioner, hvor historikken skal spores.

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

Datodimensionen

Datodimensionen er særlig – den findes næsten altid og er normalt udfyldt på forhånd med datoer for mange år. Når du gemmer afledte attributter (år, kvartal, månedsnavn, regnskabsperiode, helligdagsflag) i dimensionstabellen, undgår du at beregne dem igen på forespørgselstidspunktet.

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

Langsomt ændrende dimensioner (SCD Type 2)

Hvad sker der, når en kunde flytter til en anden by, eller et produkt skifter kategori? Du skal spore historikken. SCD Type 2 indsætter en ny dimensionsrække for hver ændring, samtidig med at den forrige lukkes med en slutdato. Rækken i faktatabellen peger stadig på den gamle dimensionsnøgle, så den historiske nøjagtighed bevares.

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

Stjerneskema kontra snefnugskema – afvejninger

Ingen af skemaerne er universelt bedre end det andet. Vælg ud fra dine prioriteter:

Stjerneskema – færre joins, hurtigere forespørgsler, enklere ETL og højere lageromkostninger. Bedst til analyseværktøjer med mange læsninger (Tableau, Power BI).
Snefnugskema – normaliserede dimensioner, mindre redundans og nemmere opdatering af dimensioner, men flere joins. Bedre, når dimensionerne er store eller deles af flere faktatabeller.

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

Galakseskema (faktakonstellation)

Når et datalager har flere faktatabeller, der deler dimensionstabeller, kaldes resultatet et galakseskema (eller en faktakonstellation). Et detailhandelsdatalager kan for eksempel have separate faktatabeller for salg og returneringer, som begge refererer til de samme dim_product og dim_date.

Delte dimensioner sikrer ensartet filtrering og gør sammenligninger på tværs af faktatabeller enkle.

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;

Stjerneskema kontra snefnugskema

Test din forståelse af stjerne- og snefnugskemaer.

Besvar den i den interaktive lektion

Opsummering af lektionen

I denne lektion udforskede du to grundlæggende designmønstre for datalagre:

Stjerneskema – en central faktatabel omgivet af flade, denormaliserede dimensionstabeller. Færre joins, hurtigere forespørgsler og lidt mere lagerplads.
Snefnugskema – dimensionstabeller normaliseres yderligere til underdimensioner. Mindre redundans og nemmere opdateringer, men flere nødvendige joins.
Faktatabeller indeholder målbare hændelser; dimensionstabeller leverer kontekst (hvem, hvad, hvornår og hvor).
Surrogatnøgler beskytter den historiske nøjagtighed og afkobler datalageret fra ændringer i kildesystemet.
SCD Type 2 sporer dimensionshistorik ved at tilføje nye rækker med gyldighedsdatoer i stedet for at overskrive gamle rækker.
Når flere faktatabeller deler dimensioner, bliver designet til et galakseskema (faktakonstellation).

Vælg stjerneskema for enkelhed og hastighed; vælg snefnugskema, når dimensionerne er store, opdateres ofte eller deles af mange faktatabeller.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 46
Lektioner: 183

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Stjerne- og snefnugsskemaer” gratis?

Ja — hele teksten til “Stjerne- og snefnugsskemaer” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af SQL Academy-kurset, skal du opgradere til CoddyKit PRO. SQL Academy-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Stjerne- og snefnugsskemaer”?

Modellér data til hurtig analyse Du øver dig i SQL Academy med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på SQL Academy?

Der kræves ingen tidligere erfaring. SQL Academy på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 3 af 4.

Hvor lang tid tager lektionen “Stjerne- og snefnugsskemaer”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne SQL Academy-lektion?

Ja. Alle SQL Academy-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

← Tilbage til SQL Academy