Star-Schema und Data-Warehouse-Design
Fakten- und Dimensionstabellen, Kompromisse bei der Denormalisierung und OLAP-Modellierung.
Star-Schema und Data-Warehouse-Design ist eine kostenlose SQL Interview Prep-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 Interview Prep-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.
OLTP vs. OLAP
Fragen zu Data Warehouses beginnen mit einer Unterscheidung, die Interviewer von Ihnen sicher beherrscht sehen wollen: OLTP vs. OLAP.
- OLTP (transaktionsorientiert): viele kleine Lese-/Schreibvorgänge, zur Wahrung der Integrität stark normalisiert. Betreibt die Anwendung.
- OLAP (analytisch): wenige große aggregierende Lesevorgänge über historische Daten, für hohe Geschwindigkeit bewusst denormalisiert. Betreibt Berichte und Dashboards.
Sternschemas sind ein OLAP-Entwurf. Der entscheidende Zweck sind schnelle analytische Abfragen, wobei Redundanz als Gegenleistung akzeptiert wird.
Fakten und Dimensionen
Ein Sternschema teilt Daten in zwei Arten von Tabellen auf:
- Faktentabelle: die messbaren Ereignisse oder Transaktionen (ein Verkauf, ein Klick). Sie enthält numerische Kennzahlen und Fremdschlüssel zu Dimensionen.
- Dimensionstabellen: der beschreibende Kontext, nach dem Sie Daten aufschlüsseln (Datum, Produkt, Kunde, Filiale).
Die Faktentabelle befindet sich in der Mitte; die Dimensionen umgeben sie wie die Spitzen eines Sterns – daher der Name.
Aufbau einer Faktentabelle
Eine Faktentabelle besteht größtenteils aus Fremdschlüsseln und numerischen Kennzahlen. Sie ist lang und schmal und wächst kontinuierlich.
Kennzahlen sind additive Zahlen, die Sie aggregieren: Menge, Umsatz, Kosten. Die Granularität (eine Zeile = ein ?) muss klar angegeben werden; hier entspricht eine Zeile einer Produktposition in einem Verkauf.
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
);Aufbau einer Dimensionstabelle
Dimensionen sind kurz und breit: Sie enthalten viele beschreibende Spalten, nach denen Sie filtern und gruppieren. Sie sind absichtlich denormalisiert, sodass eine Abfrage nur einen Join pro Dimension benötigt.
Beachten Sie, dass dim_product Kategorie und Marke in derselben Zeile speichert und nicht in separaten Tabellen. Genau das ist der Zweck dieser Redundanz: Sie vermeidet zusätzliche Joins zum Abfragezeitpunkt.
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)
);Eine Abfrage auf einem Sternschema
Das ist der Vorteil dieses Entwurfs. Eine typische Analyseabfrage verknüpft die Faktentabelle mit einigen Dimensionen, filtert und aggregiert. Ein Join pro Dimension, keine tiefen Join-Ketten.
In Interviews werden Sie aufgefordert, genau diese Art von Abfrage für ein Sternschema zu schreiben.
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;Surrogatschlüssel
Dimensionen verwenden einen Surrogatschlüssel: einen bedeutungslosen ganzzahligen Primärschlüssel (wie product_key), der vom Data Warehouse generiert wird und vom natürlichen Schlüssel des Quellsystems getrennt ist.
Warum das für Interviewer wichtig ist:
- Das Data Warehouse wird von sich ändernden Geschäftsschlüsseln entkoppelt.
- Faktentabellen bleiben schmal (Joins über Ganzzahlen sind schnell).
- Er wird benötigt, um die Historie mit langsam veränderlichen Dimensionen zu erfassen (nächste Szene).
Langsam veränderliche Dimensionen
Ein beliebtes Thema in Data-Warehouse-Interviews: Wie gehen Sie damit um, wenn sich ein Dimensionsattribut ändert (etwa wenn ein Kunde die Stadt wechselt)? Das sind langsam veränderliche Dimensionen (SCD):
- Type 1: Den alten Wert überschreiben. Keine Historie.
- Type 2: Eine neue Zeile mit Gültigkeitsdaten und einem Kennzeichen für den aktuellen Datensatz hinzufügen. Vollständige Historie; dafür werden Surrogatschlüssel benötigt.
- Type 3: Eine Spalte für den „vorherigen Wert“ behalten. Begrenzte Historie.
Type 2 ist die Antwort, die am häufigsten erwartet wird, wenn Änderungen über die Zeit verfolgt werden sollen.
-- 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
);Sternschema vs. Snowflake-Schema
Rechnen Sie mit dieser Vergleichsfrage. Ein Snowflake-Schema normalisiert Dimensionen in Untertabellen (Produkt -> Kategorie -> Abteilung), während ein Sternschema sie flach hält.
- Sternschema: weniger Joins, schnellere Lesevorgänge, etwas Redundanz. Bevorzugt für eine hohe Abfrageleistung.
- Snowflake-Schema: weniger Speicherbedarf und einfachere Pflege der Dimensionen, aber mehr Joins pro Abfrage.
Sagen Sie: „Verwenden Sie standardmäßig ein Sternschema für schnelle Abfragen; ein Snowflake-Schema nur, wenn Dimensionen groß und mehrfach verwendet werden.“
Die Datumsdimension
Nahezu jedes Sternschema verfügt über eine eigene Datumsdimension statt über eine reine Datumsspalte. Sie berechnet Jahr, Quartal, Monat, Wochentag, Feiertagskennzeichen und Geschäftsjahresperioden vor.
Dadurch können Analysten mit einem einfachen Join nach „Geschäftsquartal“ oder „is_weekend“ gruppieren, statt verstreute Datumsfunktionen zu verwenden. Wenn Sie eine Datumsdimension von sich aus erwähnen, ist das ein starkes Signal dafür, dass Sie Data Warehouses bereits aufgebaut haben.
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)
);Die Granularität festlegen
Die wichtigste Entscheidung bei einer Faktentabelle betrifft die Granularität: Was repräsentiert eine Zeile? Legen Sie dies vor allem anderen fest.
- Zu grob (eine Zeile pro Tag und Filiale), und Sie verlieren Details.
- Zu fein (eine Zeile pro gescanntem Artikel), und die Tabelle wächst explosionsartig.
Eine klare Aussage zur Granularität, etwa „eine Zeile pro Produkt und Bestellposition“, bestimmt, welche Dimensionen und Kennzahlen dazugehören. Interviewer achten auf diese methodische Disziplin.
Wann Sie denormalisieren sollten
Beziehen Sie das auf die Normalisierung. OLTP-Systeme werden für die Integrität bis zur 3NF normalisiert; Data Warehouses denormalisieren Dimensionen bewusst, um die Lesegeschwindigkeit zu erhöhen.
Die Abwägung, die Sie erläutern müssen:
- Redundante Dimensionsdaten sind akzeptabel, weil das Data Warehouse durch kontrolliertes ETL und nicht durch unkontrollierte Schreibvorgänge der Anwendung geladen wird.
- Weniger Joins bedeuten schnellere Aggregationen über Milliarden von Faktzeilen.
Die Abwägung und nicht die bloße Regel zeichnet hier Antworten auf Senior-Niveau aus.
Kurzer Test
Sie entwerfen ein Data Warehouse für Verkaufsdaten und müssen die vollständige Historie der Stadt eines Kunden bewahren, wenn dieser umzieht.
Zusammenfassung: Sternschema und Data-Warehouse-Entwurf
Sie können nun Fragen zur Modellierung von Data Warehouses beantworten:
- OLTP normalisiert für Integrität; OLAP denormalisiert für schnelle Lesevorgänge.
- Ein Sternschema besitzt eine zentrale Faktentabelle (Fremdschlüssel und numerische Kennzahlen), die von flachen Dimensionen umgeben ist.
- Verwenden Sie Surrogatschlüssel und eine eigene Datumsdimension.
- Verfolgen Sie Änderungen mit SCD Type 2; legen Sie zuerst die Granularität der Faktentabelle fest.
- Bevorzugen Sie für eine hohe Abfrageleistung ein Sternschema gegenüber einem Snowflake-Schema.
Häufig gestellte Fragen
Ist die Lektion „Star-Schema und Data-Warehouse-Design“ kostenlos?
Ja — der vollständige Text von „Star-Schema und Data-Warehouse-Design“ 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 Interview Prep-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.
Was lerne ich in „Star-Schema und Data-Warehouse-Design“?
Fakten- und Dimensionstabellen, Kompromisse bei der Denormalisierung und OLAP-Modellierung. Du übst SQL Interview Prep 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 Interview Prep zu starten?
Keine Vorkenntnisse erforderlich. SQL Interview Prep 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 „Star-Schema und Data-Warehouse-Design“?
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 Interview Prep-Lektion Code schreiben und ausführen?
Ja. Jede SQL Interview Prep-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
- Normalisierung bis zur 3NF
- ER-Modellierung und Kardinalität von Beziehungen
- Star-Schema und Data-Warehouse-Design
- Kompletter Satz von Probeinterview-Aufgaben