0Pricing
SQL Academy · Lektion

Stern- und Schneeflockenschemata

Modellieren Sie Daten für schnelle Analysen

Stern- und Schneeflockenschemata ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was ist ein Data-Warehouse-Schema?

In einer transaktionalen (OLTP-)Datenbank normalisieren Sie Daten, um Redundanz zu vermeiden. In einem Data Warehouse denormalisieren Sie Daten dagegen häufig bewusst und tauschen Speicherplatz gegen Abfragegeschwindigkeit. Zwei klassische Muster zur Organisation von Data-Warehouse-Tabellen sind das Star-Schema und das Snowflake-Schema.

Beide basieren auf einer zentralen Faktentabelle, die von Dimensionstabellen umgeben ist. Der Unterschied liegt darin, wie stark diese Dimensionen normalisiert sind.

Faktentabellen und Dimensionstabellen

Eine Faktentabelle speichert messbare Ereignisse – Verkäufe, Klicks und Lieferungen. Sie ist umfangreich (viele Zeilen) und enthält numerische Kennzahlen sowie Fremdschlüssel zu Dimensionen.

Eine Dimensionstabelle beschreibt den Kontext jedes Ereignisses: wer, was, wann, wo. Dimensionen sind kleiner (weniger Zeilen), enthalten aber mehr beschreibende Spalten.

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

Das Star-Schema

In einem Star-Schema ist jede Dimensionstabelle direkt mit der Faktentabelle verbunden. Zeichnen Sie die Beziehungen auf Papier, sieht das Ergebnis wie ein Stern aus – die Faktentabelle bildet das Zentrum, die Dimensionen die Spitzen.

Dimensionstabellen sind vollständig denormalisiert: Alle beschreibenden Attribute befinden sich in einer einzigen Tabelle, selbst wenn sich einige Attribute in mehreren Zeilen wiederholen.

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

Abfrage im Star-Schema

Die flachen Dimensionstabellen machen Abfragen einfach. Sie verknüpfen die Faktentabelle mit einer oder mehreren Dimensionen und aggregieren die Daten. Es gibt keine zusätzlichen Joins über Ketten normalisierter Tabellen.

Deshalb liefern Star-Schemata schnelle analytische Abfragen – der Join-Graph ist flach.

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;

Das Snowflake-Schema

Ein Snowflake-Schema normalisiert Dimensionstabellen weiter, indem es sie in Unterdimensionen aufteilt. Statt beispielsweise category_name und brand_name in dim_product zu speichern, erstellen Sie separate Tabellen dim_category und dim_brand.

Das resultierende Diagramm sieht wie eine Schneeflocke aus – mit verzweigten Ästen verbundener Tabellen.

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

Abfrage eines Snowflake-Schemas

Für Abfragen eines Snowflake-Schemas sind mehr Joins erforderlich, um die auf mehrere Tabellen verteilten Dimensionsdaten wieder zusammenzuführen. Der Abfrageoptimierer muss die zusätzlichen Ebenen durchlaufen, was im Vergleich zu einem Star-Schema zu höherer Latenz führen kann.

Die normalisierten Dimensionen sind jedoch kleiner und konsistent: Wenn Sie einen Markennamen in einer Zeile von dim_brand aktualisieren, wird die Änderung automatisch überall übernommen.

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;

Surrogate Keys vs. natürliche Schlüssel

Dimensionstabellen verwenden typischerweise einen Surrogate Key – eine vom Data-Warehouse generierte Ganzzahl (z. B. SERIAL) – statt eines natürlichen Schlüssels aus dem Quellsystem.

Surrogate Keys bleiben auch bei Änderungen im Quellsystem stabil, sind für große Faktentabellen kompakt und unterstützen langsam veränderliche Dimensionen, bei denen die Historie nachverfolgt werden muss.

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

Die Datumsdimension

Die Datumsdimension ist etwas Besonderes: Sie ist fast immer vorhanden und wird normalerweise für viele Jahre im Voraus mit Datumswerten befüllt. Wenn Sie abgeleitete Attribute (Jahr, Quartal, Monatsname, Geschäftsperiode, Feiertagskennzeichen) in der Dimensionstabelle speichern, müssen diese zur Abfragezeit nicht neu berechnet werden.

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

Langsam veränderliche Dimensionen (SCD Type 2)

Was passiert, wenn ein Kunde die Stadt wechselt oder ein Produkt einer anderen Kategorie zugeordnet wird? Sie müssen die Historie nachverfolgen. SCD Type 2 fügt bei jeder Änderung eine neue Dimensionszeile ein und schließt die vorherige mit einem Enddatum. Die Zeile der Faktentabelle verweist weiterhin auf den alten Dimensionsschlüssel, wodurch die historische Genauigkeit erhalten bleibt.

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

Star-Schema vs. Snowflake-Schema – Abwägungen

Keines der beiden Schemata ist grundsätzlich besser. Treffen Sie Ihre Wahl anhand Ihrer Prioritäten:

  • Star-Schema – weniger Joins, schnellere Abfragen, einfacheres ETL, höhere Speicherkosten. Am besten für leseintensive Analysewerkzeuge wie Tableau und Power BI geeignet.
  • Snowflake-Schema – normalisierte Dimensionen, weniger Redundanz und einfachere Dimensionsaktualisierungen, aber mehr Joins. Besser geeignet, wenn Dimensionen groß sind oder von mehreren Faktentabellen gemeinsam verwendet werden.
-- 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;

Galaxy-Schema (Faktenkonstellation)

Wenn ein Data-Warehouse mehrere Faktentabellen enthält, die Dimensionstabellen gemeinsam verwenden, wird das Ergebnis als Galaxy-Schema (oder Faktenkonstellation) bezeichnet. Ein Data-Warehouse für den Einzelhandel könnte beispielsweise separate Faktentabellen für Verkäufe und Retouren enthalten, die beide auf dim_product und dim_date verweisen.

Gemeinsam verwendete Dimensionen sorgen für konsistente Filterung und erleichtern Vergleiche über mehrere Faktentabellen hinweg.

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;

Star-Schema vs. Snowflake-Schema

Testen Sie Ihr Verständnis von Star- und Snowflake-Schemata.

Zusammenfassung der Lektion

In dieser Lektion haben Sie zwei grundlegende Entwurfsmuster für Data-Warehouses kennengelernt:

  • Star-Schema – eine zentrale Faktentabelle, umgeben von flachen, denormalisierten Dimensionstabellen. Weniger Joins, schnellere Abfragen und etwas mehr Speicherbedarf.
  • Snowflake-Schema – Dimensionstabellen werden weiter in Unterdimensionen normalisiert. Weniger Redundanz und einfachere Aktualisierungen, aber mehr erforderliche Joins.
  • Faktentabellen enthalten messbare Ereignisse; Dimensionstabellen liefern den Kontext (wer, was, wann, wo).
  • Surrogate Keys schützen die historische Genauigkeit und entkoppeln das Data-Warehouse von Änderungen im Quellsystem.
  • SCD Type 2 verfolgt die Historie von Dimensionen, indem neue Zeilen mit Gültigkeitsdaten hinzugefügt werden, anstatt alte zu überschreiben.
  • Wenn mehrere Faktentabellen Dimensionen gemeinsam verwenden, wird das Design zu einem Galaxy-Schema (Faktenkonstellation).

Wählen Sie das Star-Schema für Einfachheit und Geschwindigkeit; wählen Sie das Snowflake-Schema, wenn Dimensionen groß, häufig aktualisiert oder in vielen Faktentabellen gemeinsam verwendet werden.

Häufig gestellte Fragen

Ist die Lektion „Stern- und Schneeflockenschemata“ kostenlos?

Ja — der vollständige Text von „Stern- und Schneeflockenschemata“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Stern- und Schneeflockenschemata“?

Modellieren Sie Daten für schnelle Analysen Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „Stern- und Schneeflockenschemata“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. OLTP vs. OLAP
  2. Fakten- und Dimensionstabellen
  3. Stern- und Schneeflockenschemata
  4. Analytische Abfragen schreiben
← Zurück zu SQL Academy