Data-Warehouse-Design: Dimensionale Modellierung vom Star-Schema bis zur PartitionierungData Warehouse Design: Dimensional Modeling from Star Schema to Partitioning

Prolog: Die Spurensuche im Datenozean

Es begann mit einer simplen Frage, die mich nicht mehr losließ: Warum dauert diese verdammte Sales-Abfrage 47 Sekunden? Mein Linux-Server ratterte, PostgreSQL schluckte CPU-Zyklen wie ein durstiger Marathon-Läufer Wasser, und ich starrte auf den EXPLAIN ANALYZE-Output wie auf eine Landkarte, deren Legende jemand vergessen hatte. Sieben Tabellen, zwölf Joins, ein Full Table Scan über 100 Millionen Zeilen. Das war kein Query — das war ein Hilferuf.

Also tat ich, was jeder neugierige Datenarchitekt tun würde: Ich löschte die Produktionsdatenbank. Nein, natürlich nicht. Ich holte mir einen Kaffee, öffnete ein leeres Terminal und begann, das Fundament neu zu denken. Data Warehouse Design — nicht als akademisches Konzept aus einem Lehrbuch, sondern als praktisches Werkzeug, das ich auf meinem eigenen Server mit echten Daten testen konnte.

Was folgt, ist die Geschichte dieser Expedition: Vom chaotischen Datensumpf zum strukturierten Warehouse, vom naiven Schema-Entwurf zur hochoptimierten Analytical Engine. Sechs Kapitel, sechs Visualisierungen, und die Erkenntnis, dass gutes Datenbank-Design weniger Wissenschaft und mehr Detektivarbeit ist.

Kapitel 1: Das Star-Schema — Eleganz durch Einfachheit

Jede gute Architektur beginnt mit einer fundamentalen Entscheidung: Wie organisiere ich meine Daten so, dass sie Fragen beantworten, die ich noch gar nicht gestellt habe? Die Antwort, die Ralph Kimball in den 1990ern formulierte, ist bestechend simpel: Trenne Fakten von Dimensionen.

Fakten sind Messungen — Dinge, die man zählen, summieren, mitteln kann. Revenue, Quantity, Discount. Dimensionen sind der Kontext — wer kaufte was, wann, wo? Ein Star-Schema stellt die Faktentabelle ins Zentrum und umgibt sie mit Dimensionstabellen wie Planeten um eine Sonne.

Auf meinem Server setzte ich das mit PostgreSQL um:

-- Dimensionstabellen
CREATE TABLE dim_date (
    date_key    SERIAL PRIMARY KEY,
    full_date   DATE NOT NULL,
    day_of_week VARCHAR(10),
    month       INTEGER,
    quarter     INTEGER,
    year        INTEGER,
    is_weekend  BOOLEAN
);

CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_id  VARCHAR(20) NOT NULL,
    name        VARCHAR(200),
    category    VARCHAR(100),
    brand       VARCHAR(100),
    unit_price  DECIMAL(10,2)
);

CREATE TABLE dim_customer (
    customer_key SERIAL PRIMARY KEY,
    customer_id  VARCHAR(20) NOT NULL,
    name         VARCHAR(200),
    segment      VARCHAR(50),
    region       VARCHAR(100),
    loyalty_tier VARCHAR(20)
);

CREATE TABLE dim_store (
    store_key   SERIAL PRIMARY KEY,
    store_id    VARCHAR(20) NOT NULL,
    name        VARCHAR(200),
    city        VARCHAR(100),
    country     VARCHAR(50),
    size_sqm    INTEGER
);

-- Faktentabelle im Zentrum
CREATE TABLE fct_sales (
    sale_id      SERIAL PRIMARY KEY,
    date_key     INTEGER REFERENCES dim_date(date_key),
    product_key  INTEGER REFERENCES dim_product(product_key),
    customer_key INTEGER REFERENCES dim_customer(customer_key),
    store_key    INTEGER REFERENCES dim_store(store_key),
    quantity     INTEGER NOT NULL,
    revenue      DECIMAL(12,2) NOT NULL,
    discount     DECIMAL(5,2) DEFAULT 0
);

-- Indizes für performante Joins
CREATE INDEX idx_fct_sales_date ON fct_sales(date_key);
CREATE INDEX idx_fct_sales_product ON fct_sales(product_key);
CREATE INDEX idx_fct_sales_customer ON fct_sales(customer_key);
CREATE INDEX idx_fct_sales_store ON fct_sales(store_key);

Die Schönheit dieses Ansatzes offenbarte sich, als ich die erste analytische Abfrage schrieb:

SELECT
    d.year,
    d.quarter,
    p.category,
    c.segment,
    SUM(f.revenue) AS total_revenue,
    AVG(f.discount) AS avg_discount,
    COUNT(DISTINCT c.customer_key) AS unique_customers
FROM fct_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.year, d.quarter, p.category, c.segment
ORDER BY total_revenue DESC;

Vier Joins, aber jeder kristallklar. Kein Rätselraten, welche Tabelle welche Bedeutung hat. Das Star-Schema ist selbstdokumentierend — eine Eigenschaft, deren Wert man erst schätzt, wenn man um 2 Uhr morgens einen Bug in einer 200-Zeilen-SQL-Abfrage sucht.

Kapitel 2: Star vs. Snowflake — Die Normalisierungsdebatte

Kaum hatte ich mein Star-Schema aufgebaut, flüsterte mir mein innerer Datenbank-Purist zu: Aber die Redundanz! Die Anomalien! Du wiederholst Category-Informationen in jeder Product-Zeile! Und er hatte nicht unrecht. In dim_product stand „Electronics" hundertmal — einmal für jedes Produkt dieser Kategorie.

Die Antwort des Puristen heißt Snowflake-Schema: Man normalisiert die Dimensionstabellen weiter, extrahiert Sub-Dimensionen. Aus einer flachen dim_product-Tabelle werden drei: dim_product, dim_category, dim_brand.

-- Snowflake: Normalisierte Dimensionen
CREATE TABLE dim_category (
    category_key SERIAL PRIMARY KEY,
    name         VARCHAR(100),
    department   VARCHAR(100)
);

CREATE TABLE dim_brand (
    brand_key    SERIAL PRIMARY KEY,
    name         VARCHAR(100),
    manufacturer VARCHAR(200),
    country      VARCHAR(50)
);

CREATE TABLE dim_product_sf (
    product_key  SERIAL PRIMARY KEY,
    product_id   VARCHAR(20) NOT NULL,
    name         VARCHAR(200),
    category_key INTEGER REFERENCES dim_category(category_key),
    brand_key    INTEGER REFERENCES dim_brand(brand_key),
    unit_price   DECIMAL(10,2)
);

Klingt sauber, oder? Bis man die analytische Abfrage schreibt und plötzlich statt 4 Joins 8 braucht. Bis der Query Planner anfängt, Hash Joins zu kaskadieren und die Ausführungszeit sich verdoppelt. Bis das BI-Team fragt, warum das Dashboard seit dem Refactoring langsamer ist.

Ich testete beide Varianten auf meinem Server mit einem synthetischen Dataset von 10 Millionen Sales-Records und maß die Unterschiede. Das Ergebnis war eindeutig: Für analytische Workloads — aggregieren, gruppieren, filtern — gewinnt das Star-Schema fast immer. Der Grund ist simpel: Weniger Joins = weniger I/O = schnellere Queries.

Das Snowflake-Schema hat seinen Platz — bei sehr großen Dimensionstabellen, wo die Redundanz tatsächlich zu Speicherproblemen führt, oder bei Compliance-Anforderungen an Datenkonsistenz. Aber für die meisten Data Warehouses auf einem Linux-Server gilt: Denormalisierung ist dein Freund.

Kapitel 3: Query-Performance — Wo die Millisekunden sterben

Theorie ist schön, aber mein Server hat begrenzte Ressourcen. 32 GB RAM, 8 Kerne, eine SSD, die schneller altert als mir lieb ist. Also benchmarkte ich systematisch, was passiert, wenn verschiedene Schema-Designs auf reale analytische Queries treffen.

Ich erstellte drei identische Datasets mit 50 Millionen Zeilen — einmal als Star-Schema, einmal als Snowflake, einmal als vollnormalisierte 3NF-Struktur (die typische OLTP-Datenbank, aus der die Daten ursprünglich kommen). Dann feuerte ich fünf Abfragetypen ab:

-- Typ 1: Simple Aggregate
SELECT SUM(revenue) FROM fct_sales WHERE date_key BETWEEN 1000 AND 1365;

-- Typ 2: Join 2 Tables
SELECT p.category, SUM(f.revenue)
FROM fct_sales f JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category;

-- Typ 3: Join 4 Tables
SELECT d.year, p.category, c.segment, s.country, SUM(f.revenue)
FROM fct_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_store s ON f.store_key = s.store_key
GROUP BY d.year, p.category, c.segment, s.country;

-- Typ 4: Complex Subquery
SELECT category, revenue_rank FROM (
    SELECT p.category, SUM(f.revenue) as total,
           RANK() OVER (ORDER BY SUM(f.revenue) DESC) as revenue_rank
    FROM fct_sales f JOIN dim_product p ON f.product_key = p.product_key
    GROUP BY p.category
) ranked WHERE revenue_rank <= 10;

-- Typ 5: Full Scan mit Aggregation
SELECT COUNT(*), SUM(revenue), AVG(discount) FROM fct_sales;

Die Ergebnisse sprachen Bände. Bei einfachen Aggregationen war der Unterschied marginal — alle drei Schemas lagen unter 2 Sekunden. Aber bei Multi-Table-Joins explodierte die 3NF-Variante: 8,5 Sekunden für einen 4-Table-Join, verglichen mit 2,1 Sekunden im Star-Schema. Der Snowflake lag dazwischen.

Der entscheidende Faktor war nicht die Rechenleistung, sondern der I/O-Overhead. Jeder zusätzliche Join bedeutet: Hash-Tabellen aufbauen, Zwischenergebnisse materialisieren, Speicher allozieren. Auf einem dedizierten Data-Warehouse-Server mit 256 GB RAM mag das irrelevant sein. Auf meinem Linux-Server war es der Unterschied zwischen „brauchbar" und „Coffee-Break-Query".

Kapitel 4: Slowly Changing Dimensions — Die Zeitreise-Maschine

Das nächste Rätsel lauerte in den Daten selbst: Was passiert, wenn sich eine Dimension ändert? Anna Müller zieht von Berlin nach Hamburg. Max Schmidt wird vom Standard- zum Gold-Kunden hochgestuft. Der Store in der Friedrichstraße verdoppelt seine Fläche.

In einer OLTP-Datenbank ist die Antwort trivial: UPDATE dim_customer SET city = 'Hamburg' WHERE customer_id = '1001'. Fertig. Aber in einem Data Warehouse ist diese Antwort katastrophal — denn damit verlieren wir die Geschichte. Alle historischen Sales von Anna Müller erscheinen plötzlich in Hamburg, obwohl sie zum Zeitpunkt des Kaufs in Berlin lebte.

Ralph Kimball definierte sechs Typen von Slowly Changing Dimensions (SCDs), aber in der Praxis begegnen einem hauptsächlich drei:

SCD Typ 1 — Überschreiben: Die einfachste und verlustreichste Methode. Man überschreibt den alten Wert. Keine Geschichte, kein Audit-Trail. Geeignet für Korrekturen (Tippfehler im Namen) oder Attribute, deren Historie irrelevant ist.

-- SCD Typ 1: Einfach überschreiben
UPDATE dim_customer
SET city = 'Hamburg'
WHERE customer_id = '1001';

SCD Typ 2 — Neue Zeile einfügen: Die mächtigste Methode. Man fügt eine neue Zeile ein, markiert die alte als historisch und die neue als aktuell. Dafür braucht man einen Surrogate Key (unabhängig vom Business Key), Gültigkeitsdaten und ein is_current-Flag.

-- SCD Typ 2: Neue Zeile für die Geschichte
-- Schritt 1: Alte Zeile schließen
UPDATE dim_customer
SET valid_to = CURRENT_DATE,
    is_current = FALSE
WHERE customer_id = '1001'
  AND is_current = TRUE;

-- Schritt 2: Neue Zeile einfügen
INSERT INTO dim_customer (customer_id, name, city, segment, region,
                          loyalty_tier, valid_from, valid_to, is_current)
VALUES ('1001', 'Anna Müller', 'Hamburg', 'Premium', 'Nord',
        'Gold', CURRENT_DATE, '9999-12-31', TRUE);

SCD Typ 3 — Vorheriger Wert als Spalte: Ein Kompromiss. Man fügt eine zusätzliche Spalte für den vorherigen Wert hinzu. Begrenzt auf eine Änderungsebene, aber einfach zu implementieren und zu querien.

-- SCD Typ 3: Previous-Value Spalte
ALTER TABLE dim_customer ADD COLUMN previous_city VARCHAR(100);

UPDATE dim_customer
SET previous_city = city,
    city = 'Hamburg'
WHERE customer_id = '1001';

Ich implementierte SCD Typ 2 für meine Customer-Dimension und lud historische Daten. Der Effekt war verblüffend: Plötzlich konnte ich analysieren, wie sich der Umsatz von Kunden vor und nach einem Segment-Wechsel entwickelt. Die Zeitreise-Maschine funktionierte.

Kapitel 5: Die Schichtenarchitektur — Vom Rohdaten-Chaos zum Analyse-Gold

Ein Star-Schema allein macht noch kein Data Warehouse. Die wahre Architektur-Entscheidung liegt in der Frage: Wie transformiere ich Rohdaten in analyse-taugliche Strukturen? Die Antwort ist ein Schichtenmodell — manchmal „Medallion Architecture" genannt (Bronze → Silver → Gold), manchmal nach Funktion (Raw → Staging → Business → Mart).

Auf meinem Server implementierte ich vier Schichten:

1. Raw / Staging Layer: Hier landen die Daten exakt so, wie sie aus der Quelle kommen. Kein Filtern, kein Transformieren, kein Interpretieren. Append-Only. Das ist die „Single Source of Truth" — wenn downstream etwas schiefgeht, kann man hierhin zurückgehen.

CREATE SCHEMA raw_layer;

CREATE TABLE raw_layer.orders (
    _loaded_at    TIMESTAMP DEFAULT NOW(),
    _source       VARCHAR(50),
    _raw_json     JSONB,
    order_id      VARCHAR(50),
    customer_id   VARCHAR(50),
    order_date    VARCHAR(50),  -- Noch VARCHAR! Kein Casting in der Raw-Schicht.
    total_amount  VARCHAR(50),
    status        VARCHAR(50)
);

2. Cleaned / Intermediate Layer: Hier wird geputzt, gecastet, dedupliziert. Type-Casting, NULL-Handling, Standardisierung von Formaten. Aus dem String '2025-03-15' wird ein echtes DATE. Aus 'EUR 1.234,56' wird 1234.56.

CREATE SCHEMA clean_layer;

CREATE TABLE clean_layer.stg_orders AS
SELECT
    order_id,
    customer_id,
    CAST(order_date AS DATE) AS order_date,
    CAST(REPLACE(REPLACE(total_amount, 'EUR ', ''), ',', '.') AS DECIMAL(12,2))
        AS total_amount,
    UPPER(TRIM(status)) AS status,
    NOW() AS _processed_at
FROM raw_layer.orders
WHERE order_id IS NOT NULL
  AND order_date ~ '^\d{4}-\d{2}-\d{2}$';

3. Business / Core Layer: Hier entstehen die dimensionalen Modelle — Star- oder Snowflake-Schemas mit Fakten- und Dimensionstabellen. Business-Logik wird einmal definiert und dann konsistent wiederverwendet.

4. Presentation / Mart Layer: Zielgruppen-spezifische Views oder materialisierte Tabellen. Der Marketing-Mart zeigt Kunden-Segmentierung, der Finance-Mart zeigt Revenue-Breakdown, das ML-Team bekommt Feature-Tables.

CREATE SCHEMA mart_layer;

-- Materialized View für das Marketing-Team
CREATE MATERIALIZED VIEW mart_layer.marketing_customer_360 AS
SELECT
    c.customer_key,
    c.name,
    c.segment,
    c.loyalty_tier,
    COUNT(DISTINCT f.sale_id) AS total_orders,
    SUM(f.revenue) AS lifetime_value,
    AVG(f.revenue) AS avg_order_value,
    MAX(d.full_date) AS last_purchase_date,
    MIN(d.full_date) AS first_purchase_date
FROM fct_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE c.is_current = TRUE
GROUP BY c.customer_key, c.name, c.segment, c.loyalty_tier;

-- Refresh-Schedule als Cron-Job
-- 0 3 * * * psql -d warehouse -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mart_layer.marketing_customer_360;"

Diese Schichtentrennung klingt nach Overhead, aber sie ist ein Sicherheitsnetz. Wenn sich die Quell-API ändert, betrifft das nur die Raw- und Clean-Schicht. Wenn das Business eine neue Metrik braucht, ändert man nur den Mart. Separation of Concerns — aber für Daten.

Kapitel 6: Partitionierung — Die Kunst des Teilens

Mit wachsenden Datenmengen stieß ich auf das nächste Performance-Plateau: Selbst mein optimiertes Star-Schema mit Indizes wurde langsam, als die Faktentabelle 100 Millionen Zeilen überschritt. Der EXPLAIN ANALYZE zeigte: Full Index Scan auf idx_fct_sales_date — der Index war so groß geworden, dass er selbst zum Bottleneck wurde.

Die Lösung: Table Partitioning. PostgreSQL unterstützt seit Version 10 deklarative Partitionierung — man teilt eine große Tabelle in physisch getrennte Subtabellen, die logisch als eine erscheinen.

-- Partitionierte Faktentabelle
CREATE TABLE fct_sales_partitioned (
    sale_id      SERIAL,
    date_key     INTEGER NOT NULL,
    product_key  INTEGER,
    customer_key INTEGER,
    store_key    INTEGER,
    quantity     INTEGER NOT NULL,
    revenue      DECIMAL(12,2) NOT NULL,
    discount     DECIMAL(5,2) DEFAULT 0,
    sale_date    DATE NOT NULL
) PARTITION BY RANGE (sale_date);

-- Monatliche Partitionen
CREATE TABLE fct_sales_2025_01 PARTITION OF fct_sales_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE fct_sales_2025_02 PARTITION OF fct_sales_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE fct_sales_2025_03 PARTITION OF fct_sales_partitioned
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Automatische Partition-Erstellung via Funktion
CREATE OR REPLACE FUNCTION create_monthly_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := DATE_TRUNC('month', target_date);
    end_date := start_date + INTERVAL '1 month';
    partition_name := 'fct_sales_' || TO_CHAR(start_date, 'YYYY_MM');

    EXECUTE FORMAT(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF fct_sales_partitioned
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

Der Effekt war dramatisch. Eine Abfrage auf März 2025 scannte jetzt nur noch die Partition fct_sales_2025_03 mit 2 Millionen Zeilen statt die gesamte Tabelle mit 100 Millionen. Die Query-Zeit fiel von 45 Sekunden auf 1,5 Sekunden — eine 97 % Reduktion.

Aber Partitionierung allein war nur die halbe Miete. Das Speicherformat spielte eine ebenso große Rolle. Für meine auf dem Server liegenden Export-Dateien testete ich verschiedene Formate:

#!/bin/bash
# Benchmark: CSV vs. Parquet mit DuckDB
# DuckDB kann direkt Parquet lesen — perfekt für analytische Queries

# CSV Export (100M Zeilen)
time psql -d warehouse -c "\COPY fct_sales TO '/data/exports/sales.csv' CSV HEADER"
# → 100 GB, 12 MB/s Lesegeschwindigkeit

# Parquet Export mit DuckDB
duckdb << 'SQL'
COPY (SELECT * FROM read_csv_auto('/data/exports/sales.csv'))
TO '/data/exports/sales.parquet' (FORMAT PARQUET, COMPRESSION SNAPPY);
SQL
# → 15 GB (85% Kompression!), 78 MB/s Lesegeschwindigkeit

# Analytische Abfrage direkt auf Parquet
duckdb << 'SQL'
SELECT
    EXTRACT(YEAR FROM sale_date) AS year,
    SUM(revenue) AS total_revenue
FROM read_parquet('/data/exports/sales.parquet')
WHERE sale_date >= '2025-01-01'
GROUP BY year;
SQL
# → 0.3 Sekunden (vs. 5.2 Sekunden auf CSV)

Die Kombination aus partitionierter PostgreSQL-Datenbank für den operativen Betrieb und Parquet-Exporten für ad-hoc Analysen mit DuckDB erwies sich als ideales Setup für meinen Linux-Server. Enterprise-Level Architektur, Zero-Budget Edition.

Epilog: Das Warehouse als lebender Organismus

Was als Frustration über einen langsamen Query begann, wurde zu einer fundamentalen Lektion über Datenarchitektur. Ein Data Warehouse ist kein statisches Konstrukt — es ist ein lebender Organismus, der mit den Anforderungen wächst und sich anpasst.

Die wichtigsten Erkenntnisse aus meiner Server-Expedition:

• Star-Schema für analytische Workloads: Weniger Joins, schnellere Queries, selbstdokumentierender Code. Snowflake nur bei echten Speicherproblemen.
• SCD Typ 2 für alles, was Geschichte hat: Die Investition in Surrogate Keys und Gültigkeitsdaten zahlt sich bei jeder historischen Analyse aus.
• Schichtentrennung ist nicht optional: Raw → Clean → Business → Mart. Jede Schicht hat eine Verantwortung. Keine Shortcuts.
• Partitionierung ab Tag 1: Nicht warten, bis die Tabelle 100 Millionen Zeilen hat. Den Partition Key frühzeitig wählen und konsequent nutzen.
• Columnar Formate für Analysen: Parquet/ORC für alles, was aggregiert wird. CSV ist ein Transport-Format, kein Analyse-Format.

Mein Query läuft jetzt in 0,3 Sekunden statt 47. Der Server schnurrt zufrieden vor sich hin. Und ich habe gelernt, dass die beste Architektur nicht die komplexeste ist — sondern die, die man versteht, warten und weiterentwickeln kann.

Zitationen & Quellen

1. Kimball, R. & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. 3rd Edition. Wiley.
2. Inmon, W. H. (2005). Building the Data Warehouse. 4th Edition. Wiley.
3. PostgreSQL Documentation: Table Partitioning. postgresql.org/docs/current/ddl-partitioning.html
4. Apache Parquet Format Specification. parquet.apache.org/documentation/latest/
5. DuckDB Documentation: Parquet Import/Export. duckdb.org/docs/data/parquet/overview
6. Kimball Group: Slowly Changing Dimensions. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
7. Databricks: Medallion Architecture. docs.databricks.com/en/lakehouse/medallion.html
8. Linstedt, D. & Olschimke, M. (2015). Building a Scalable Data Warehouse with Data Vault 2.0. Morgan Kaufmann.

Fazit

Data-Warehouse-Design ist keine Raketenwissenschaft — es ist Handwerk. Mit PostgreSQL auf einem Linux-Server, den richtigen Schema-Entscheidungen und einem klaren Schichtenmodell kann man analytische Systeme bauen, die mit kommerziellen Lösungen mithalten. Die Tools sind frei verfügbar, die Dokumentation exzellent, und das Beste: Man kann alles selbst testen, Fehler machen und daraus lernen. Das ist der wahre Wert einer selbstgestellten Aufgabe — nicht das Ergebnis, sondern der Weg dorthin.

Technische Dokumentation

Aspekt	Detail
Datenbank	PostgreSQL 16 auf Ubuntu Linux
Schema-Typ	Star-Schema (primär), Snowflake (Vergleich)
SCD-Strategie	Typ 2 mit Surrogate Keys & Gültigkeitsdaten
Partitionierung	Range Partitioning nach Datum (monatlich/täglich)
Export-Format	Parquet + Snappy Compression
Analyse-Engine	DuckDB für ad-hoc Queries auf Parquet
Testdaten	50-100 Millionen synthetische Sales-Records
Visualisierung	matplotlib 3.8+, Python 3.11

-- Dimension tables
CREATE TABLE dim_date (
    date_key    SERIAL PRIMARY KEY,
    full_date   DATE NOT NULL,
    day_of_week VARCHAR(10),
    month       INTEGER,
    quarter     INTEGER,
    year        INTEGER,
    is_weekend  BOOLEAN
);

CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_id  VARCHAR(20) NOT NULL,
    name        VARCHAR(200),
    category    VARCHAR(100),
    brand       VARCHAR(100),
    unit_price  DECIMAL(10,2)
);

CREATE TABLE dim_customer (
    customer_key SERIAL PRIMARY KEY,
    customer_id  VARCHAR(20) NOT NULL,
    name         VARCHAR(200),
    segment      VARCHAR(50),
    region       VARCHAR(100),
    loyalty_tier VARCHAR(20)
);

CREATE TABLE dim_store (
    store_key   SERIAL PRIMARY KEY,
    store_id    VARCHAR(20) NOT NULL,
    name        VARCHAR(200),
    city        VARCHAR(100),
    country     VARCHAR(50),
    size_sqm    INTEGER
);

-- Fact table at the center
CREATE TABLE fct_sales (
    sale_id      SERIAL PRIMARY KEY,
    date_key     INTEGER REFERENCES dim_date(date_key),
    product_key  INTEGER REFERENCES dim_product(product_key),
    customer_key INTEGER REFERENCES dim_customer(customer_key),
    store_key    INTEGER REFERENCES dim_store(store_key),
    quantity     INTEGER NOT NULL,
    revenue      DECIMAL(12,2) NOT NULL,
    discount     DECIMAL(5,2) DEFAULT 0
);

-- Indexes for performant joins
CREATE INDEX idx_fct_sales_date ON fct_sales(date_key);
CREATE INDEX idx_fct_sales_product ON fct_sales(product_key);
CREATE INDEX idx_fct_sales_customer ON fct_sales(customer_key);
CREATE INDEX idx_fct_sales_store ON fct_sales(store_key);

SELECT
    d.year,
    d.quarter,
    p.category,
    c.segment,
    SUM(f.revenue) AS total_revenue,
    AVG(f.discount) AS avg_discount,
    COUNT(DISTINCT c.customer_key) AS unique_customers
FROM fct_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.year, d.quarter, p.category, c.segment
ORDER BY total_revenue DESC;

-- Snowflake: Normalized dimensions
CREATE TABLE dim_category (
    category_key SERIAL PRIMARY KEY,
    name         VARCHAR(100),
    department   VARCHAR(100)
);

CREATE TABLE dim_brand (
    brand_key    SERIAL PRIMARY KEY,
    name         VARCHAR(100),
    manufacturer VARCHAR(200),
    country      VARCHAR(50)
);

CREATE TABLE dim_product_sf (
    product_key  SERIAL PRIMARY KEY,
    product_id   VARCHAR(20) NOT NULL,
    name         VARCHAR(200),
    category_key INTEGER REFERENCES dim_category(category_key),
    brand_key    INTEGER REFERENCES dim_brand(brand_key),
    unit_price   DECIMAL(10,2)
);

-- Type 1: Simple Aggregate
SELECT SUM(revenue) FROM fct_sales WHERE date_key BETWEEN 1000 AND 1365;

-- Type 2: Join 2 Tables
SELECT p.category, SUM(f.revenue)
FROM fct_sales f JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category;

-- Type 3: Join 4 Tables
SELECT d.year, p.category, c.segment, s.country, SUM(f.revenue)
FROM fct_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_store s ON f.store_key = s.store_key
GROUP BY d.year, p.category, c.segment, s.country;

-- Type 4: Complex Subquery
SELECT category, revenue_rank FROM (
    SELECT p.category, SUM(f.revenue) as total,
           RANK() OVER (ORDER BY SUM(f.revenue) DESC) as revenue_rank
    FROM fct_sales f JOIN dim_product p ON f.product_key = p.product_key
    GROUP BY p.category
) ranked WHERE revenue_rank <= 10;

-- Type 5: Full Scan with Aggregation
SELECT COUNT(*), SUM(revenue), AVG(discount) FROM fct_sales;

-- SCD Type 1: Simply overwrite
UPDATE dim_customer
SET city = 'Hamburg'
WHERE customer_id = '1001';

-- SCD Type 2: New row for history
-- Step 1: Close old row
UPDATE dim_customer
SET valid_to = CURRENT_DATE,
    is_current = FALSE
WHERE customer_id = '1001'
  AND is_current = TRUE;

-- Step 2: Insert new row
INSERT INTO dim_customer (customer_id, name, city, segment, region,
                          loyalty_tier, valid_from, valid_to, is_current)
VALUES ('1001', 'Anna Müller', 'Hamburg', 'Premium', 'Nord',
        'Gold', CURRENT_DATE, '9999-12-31', TRUE);

-- SCD Type 3: Previous-Value Column
ALTER TABLE dim_customer ADD COLUMN previous_city VARCHAR(100);

UPDATE dim_customer
SET previous_city = city,
    city = 'Hamburg'
WHERE customer_id = '1001';

CREATE SCHEMA raw_layer;

CREATE TABLE raw_layer.orders (
    _loaded_at    TIMESTAMP DEFAULT NOW(),
    _source       VARCHAR(50),
    _raw_json     JSONB,
    order_id      VARCHAR(50),
    customer_id   VARCHAR(50),
    order_date    VARCHAR(50),  -- Still VARCHAR! No casting in the raw layer.
    total_amount  VARCHAR(50),
    status        VARCHAR(50)
);

CREATE SCHEMA clean_layer;

CREATE TABLE clean_layer.stg_orders AS
SELECT
    order_id,
    customer_id,
    CAST(order_date AS DATE) AS order_date,
    CAST(REPLACE(REPLACE(total_amount, 'EUR ', ''), ',', '.') AS DECIMAL(12,2))
        AS total_amount,
    UPPER(TRIM(status)) AS status,
    NOW() AS _processed_at
FROM raw_layer.orders
WHERE order_id IS NOT NULL
  AND order_date ~ '^\d{4}-\d{2}-\d{2}$';

CREATE SCHEMA mart_layer;

-- Materialized View for the marketing team
CREATE MATERIALIZED VIEW mart_layer.marketing_customer_360 AS
SELECT
    c.customer_key,
    c.name,
    c.segment,
    c.loyalty_tier,
    COUNT(DISTINCT f.sale_id) AS total_orders,
    SUM(f.revenue) AS lifetime_value,
    AVG(f.revenue) AS avg_order_value,
    MAX(d.full_date) AS last_purchase_date,
    MIN(d.full_date) AS first_purchase_date
FROM fct_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE c.is_current = TRUE
GROUP BY c.customer_key, c.name, c.segment, c.loyalty_tier;

-- Refresh schedule as cron job
-- 0 3 * * * psql -d warehouse -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mart_layer.marketing_customer_360;"

-- Partitioned fact table
CREATE TABLE fct_sales_partitioned (
    sale_id      SERIAL,
    date_key     INTEGER NOT NULL,
    product_key  INTEGER,
    customer_key INTEGER,
    store_key    INTEGER,
    quantity     INTEGER NOT NULL,
    revenue      DECIMAL(12,2) NOT NULL,
    discount     DECIMAL(5,2) DEFAULT 0,
    sale_date    DATE NOT NULL
) PARTITION BY RANGE (sale_date);

-- Monthly partitions
CREATE TABLE fct_sales_2025_01 PARTITION OF fct_sales_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE fct_sales_2025_02 PARTITION OF fct_sales_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE fct_sales_2025_03 PARTITION OF fct_sales_partitioned
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Automatic partition creation via function
CREATE OR REPLACE FUNCTION create_monthly_partition(target_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := DATE_TRUNC('month', target_date);
    end_date := start_date + INTERVAL '1 month';
    partition_name := 'fct_sales_' || TO_CHAR(start_date, 'YYYY_MM');

    EXECUTE FORMAT(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF fct_sales_partitioned
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

#!/bin/bash
# Benchmark: CSV vs. Parquet with DuckDB
# DuckDB can read Parquet directly — perfect for analytical queries

# CSV Export (100M rows)
time psql -d warehouse -c "\COPY fct_sales TO '/data/exports/sales.csv' CSV HEADER"
# → 100 GB, 12 MB/s read speed

# Parquet Export with DuckDB
duckdb << 'SQL'
COPY (SELECT * FROM read_csv_auto('/data/exports/sales.csv'))
TO '/data/exports/sales.parquet' (FORMAT PARQUET, COMPRESSION SNAPPY);
SQL
# → 15 GB (85% compression!), 78 MB/s read speed

# Analytical query directly on Parquet
duckdb << 'SQL'
SELECT
    EXTRACT(YEAR FROM sale_date) AS year,
    SUM(revenue) AS total_revenue
FROM read_parquet('/data/exports/sales.parquet')
WHERE sale_date >= '2025-01-01'
GROUP BY year;
SQL
# → 0.3 seconds (vs. 5.2 seconds on CSV)