ZrobDev/data-modeling

Datenmodellierung

Zielplattformen: PostgreSQL, BigQuery, Snowflake. Transformation: dbt. Lokales Prototyping: DuckDB 2.0. Für dbt-Mechanismen (Model-Konfiguration, Tests-YAML, Snapshots, Semantic Layer, v2-Migration) verwenden Sie die offiziellen dbt Labs Skills, die von der de-vendors-Skill dieses Repos eingebunden werden. Diese Skill entscheidet, was modelliert wird, nicht wie dbt es umsetzt.

Ablauf

1. Nennen Sie die geschäftliche Frage und den Konsumenten (Dashboard, ML-Feature, Finance-Abschluss).
2. Schreiben Sie die Granularität als einen Satz vor jeder Spalte: "Eine Zeile pro Auftragsposition pro Tag." Wenn der Satz "oder" benötigt, handelt es sich um zwei Tabellen.
3. Wählen Sie den Fakten-Typ (unten) und listen Sie dann die Dimensionen auf, die auf dieser Granularität korrekt sind.
4. Benennen Sie den Granularitätsschlüssel (natürliche Spalten oder ein Hash davon). Er erhält unique + not_null.
5. Prototypisieren Sie in DuckDB anhand einer Stichprobe, führen Sie die Fan-Out-Prüfungen durch und portieren Sie dann.
6. Fügen Sie die unter "Was zu testen" aufgeführten Tests hinzu, bevor Sie für fertig erklären.

Granularität

• Erst die Granularität, dann die Spalten. Jede Kennzahl und jeder Fremdschlüssel muss auf dieser Granularität eindeutig sein; eine Spalte, die das nicht ist, gehört in eine andere Tabelle, da sie ansonsten stillschweigend Zeilen multipliziert.
• Behalten Sie die niedrigste Granularität bei, die die Quelle unterstützt. Aggregate sind aus Details ableitbar, Details sind nicht aus Aggregaten ableitbar. Erstellen Sie Zusammenfassungen als separate Marts.
• Mischen Sie niemals Granularitäten in einer Tabelle (Auftragskopfsummen, die auf Auftragspositionszeilen wiederholt werden). Die Summierung von Kopfbeträgen über Positionszeilen führt zu Doppelzählungen; modellieren Sie Kopf- und Positionsfakten getrennt.

Fakten vs. Dimensionen

• Fakt: ein Ereignis oder eine Messung; numerisch, wenn möglich additiv, verschlüsselt mit Dimensionsschlüsseln plus degenerierten Dimensionen (Auftragsnummer).
• Dimension: beschreibender Kontext zum Filtern und Gruppieren. Breit und denormalisiert ist in Ordnung.
• Klassifizieren Sie jede Kennzahl: additiv (Summe über alle Dimensionen), semi-additiv (Salden: nicht über die Zeit), nicht-additiv (Verhältnisse: speichern Sie Zähler und Nenner, dividieren Sie nach der Aggregation).
• Fakten-Typen:
  • Transaktion: eine Zeile pro Ereignis. Standardwahl.
  • Periodischer Snapshot: eine Zeile pro Entität pro Periode (täglicher Saldo). Verwenden, wenn der Zustand wichtig ist und Ereignisse selten oder spät eintreffen.
  • Akkumulierender Snapshot: eine Zeile pro Prozessinstanz mit einer Datumsspalte pro Meilenstein, die vor Ort aktualisiert wird. Verwenden für Pipelines mit einem bekannten Lebenszyklus (Auftrag aufgegeben, versendet, zugestellt). Erwarten Sie Updates; machen Sie den Ladevorgang idempotent.
  • Faktenloser Fakt: Ereignisse oder Abdeckungen ohne Kennzahl (Anwesenheit, Berechtigung). Zählen Sie Zeilen.

Schema-Form

• Standardmäßig Stern-Schema: ein Fakt, flache Dimensionen. Schneeflocken-Schema (normalisierte Dimensionen) nur, wenn eine Unterdimension gemeinsam genutzt und groß oder von einem anderen Team gepflegt wird; die zusätzlichen Joins kosten Lesbarkeit und BI-Tool-Kompatibilität.
• Konforme Dimensionen: eine dim_customer, dim_date, die von jedem Fakt gemeinsam genutzt wird, mit denselben Schlüsseln und Attributbedeutungen. Bauen Sie sie einmal auf; eine zweite Kundentabelle mit anderen Regeln ist ein Fehler, da dann fachübergreifende Berichte voneinander abweichen.
• Rollenspiel-Dimensionen (order_date, ship_date) verwenden eine dim_date über separate FK-Spalten wieder.
• Junk-/Mini-Dimensionen für Flags mit niedriger Kardinalität; lassen Sie keine 8 Booleans auf dem Fakt.
• Unbekannter Member: Behalten Sie eine Zeile (Schlüssel -1 oder Hash von 'unknown') in jeder Dimension, damit spät eintreffende Fakten ihre Zeilen behalten. Inner Joins zu Dimensionen lassen Fakten stillschweigend verschwinden; verwenden Sie Left Joins und ein Coalesce auf den unbekannten Schlüssel.

Schlüssel

• Natürlicher Schlüssel: der Geschäftsbezeichner des Quellsystems. Behalten Sie ihn immer als Attribut.
• Surrogat-Schlüssel: Fügen Sie einen hinzu, wenn natürliche Schlüssel zusammengesetzt sind, sich ändern können, aus mehreren Quellen stammen oder die Dimension SCD2 ist (eine Entität hat viele Versionen). Bevorzugen Sie in dbt-ähnlichen Data Warehouses einen deterministischen Hash des natürlichen Schlüssels (plus valid_from für SCD2) gegenüber Sequenzen, damit Wiederholungsläufe und parallele Builds konsistent sind.
• Setzen Sie Surrogat-Schlüssel nicht als Identifikatoren für Geschäftsanwender ein; sie ändern sich bei einem Neubau.

Langsam veränderliche Dimensionen

• Typ 1 (überschreiben): wenn die Historie keinen analytischen Wert hat oder die Änderung eine Korrektur ist.
• Typ 2 (neue Zeile pro Version, valid_from / valid_to / is_current): wenn Fakten mit dem Attributwert zum Zeitpunkt des Ereignisses berichtet werden müssen (Kundenstufe zum Zeitpunkt des Kaufs).
• Snapshots reichen aus, wenn die Quelle keine Historie führt und Sie nur Änderungen ab jetzt erfassen müssen; sie sind der Erfassungsmechanismus, die SCD2-Dimension ist immer noch ein Modell darauf. Wenn die Quelle bereits eine Historie oder ein Ereignisprotokoll hat, leiten Sie Gültigkeitsbereiche daraus ab, anstatt Snapshots zu erstellen.
• Verbinden Sie Fakten mit SCD2 über Schlüssel plus event_ts >= valid_from AND event_ts < valid_to (halboffene Intervalle, keine Überlappungen). Testen Sie auf keine überlappenden oder lückenhaften Intervalle pro natürlichem Schlüssel.
• Typ 3 oder attributbezogener Typ 2: nur, wenn eine einzelne Spalte "vorheriger Wert" die Frage beantwortet; ansonsten überspringen.

Fan-Out und Doppelzählung

Ursache: Verbinden von Tabellen mit nicht übereinstimmender Granularität, sodass die "Eins"-Seite eigentlich eine "Viele"-Seite ist. Symptome: Summen größer als in der Quelle, count(*) wächst nach einem Left Join, Durchschnittswerte verschieben sich.

• Erkennung: Vergleichen Sie count(*) und count(distinct grain_key) vor und nach jedem Join; sie müssen nach einem Join mit einer Dimension gleich sein. Testen Sie die Eindeutigkeit des Join-Schlüssels auf der Dimensionsseite (select key from dim group by 1 having count(*) > 1).
• Abgleich: Die Summe der Kennzahl im Mart entspricht der Summe in der Quelle für denselben Filter.
• Behebung: Aggregieren Sie die Viele-Seite auf die Granularität des Fakts vor dem Join oder verbinden Sie über eine Brückentabelle mit Verteilungsgewichten; niemals distinct auf das Ergebnis anwenden, um es zu verbergen.
• Viele-zu-viele (Kunde zu Konto, Produkt zu Kategorie): Brückentabelle mit einem Gewichtungsfaktor, oder wählen Sie ein Primäres aus und benennen Sie es.
• SQL für diese Prüfungen: siehe references/fan-out-checks.md.

Schichtung

• raw/staging: ein Staging-Modell pro Quelltabelle; umbenennen, casten, deduplizieren, keine Joins, keine Geschäftslogik. Lassen Sie raw unveränderlich.
• intermediate: Joins, Umformungen, gemeinsame Logik, die von 2+ Marts wiederverwendet wird. Nicht für BI freigegeben.
• marts: Fakten und Dimensionen, ein Geschäftsprozess pro Mart, dokumentiert, getestet, vertraglich festgelegt. Nur Marts sind öffentlich.
• Abhängigkeiten fließen in eine Richtung; Marts lesen nicht aus raw, und Staging liest nie aus Staging.
• Materialisieren Sie Staging als Views, Marts als Tabellen (inkrementell, wenn groß und der Granularitätsschlüssel stabil ist).

Benennung

• stg_<quelle>__<entity>, int_<entity>_<verb>, fct_<prozess>, dim_<entity>; snake_case; einheitlich Plural oder Singular (entscheiden Sie sich für eines).
• Schlüssel <entity>_id (natürlich) und <entity>_key (Surrogat); Zeitstempel <ereignis>_at in UTC; Daten <ereignis>_date; Booleans is_ / has_; Beträge mit Einheit oder Währung (amount_usd).

Was zu testen

• Granularitätsschlüssel: unique + not_null, auf jedem Modell; für zusammengesetzte Granularitäten testen Sie die Kombination.
• Jeder FK auf einem Fakt: not_null (oder unbekannter Member) und ein Relationships-Test zur Dimension.
• Akzeptierte Werte für Status-/Typ-Spalten; ein neuer Wert führt zu einem lauten Fehler, anstatt falsch kategorisiert zu werden.
• Zeilenanzahl- und Summenabgleich jedes Marts mit seiner Quelle, mit einer angegebenen Toleranz (null für Finanzen).
• SCD2: keine überlappenden Bereiche, genau eine aktuelle Zeile pro natürlichem Schlüssel.
• Aktualität der Quellen. Brechen Sie jeden Test einmal mit Beispieldaten, um zu beweisen, dass er fehlschlagen kann.

OLTP-Schema-Review (PostgreSQL)

• Normalisieren Sie auf 3NF für Transaktionstabellen; denormalisieren Sie nur für einen gemessenen Lesepfad. Jede Tabelle hat einen Primärschlüssel.
• Indizieren Sie Foreign-Key-Spalten: PostgreSQL macht das nicht automatisch, und nicht indizierte FKs führen dazu, dass Parent-Deletes und Joins die Child-Tabelle scannen.
• Verwenden Sie timestamptz, numeric für Geld, text anstelle von willkürlichen varchar-Limits; setzen Sie Regeln mit NOT NULL, CHECK, UNIQUE und FK-Constraints durch, nicht allein durch Anwendungscode.
• Bevorzugen Sie bigint-Identität oder UUIDv7-Schlüssel gegenüber zufälligen UUIDv4 für große, einfügelastige Tabellen (Index-Lokalität).
• Multi-Tenant: Fügen Sie tenant_id zu jeder tenant-eigenen Tabelle hinzu, machen Sie es zur führenden Spalte von zusammengesetzten Schlüsseln und Indizes, nehmen Sie es in FKs auf (zusammengesetzter FK), sodass Cross-Tenant-Referenzen unmöglich sind, und hinterlegen Sie es mit Row-Level-Security. Standardmäßig gemeinsames Schema mit tenant_id; Schema- oder Datenbank-pro-Tenant nur für strikte Isolation oder tenant-spezifische Wiederherstellungsanforderungen.
• Soft Deletes: partielle eindeutige Indizes (where deleted_at is null), damit die Eindeutigkeit erhalten bleibt.

Fallstricke und beobachtete Fehlermodi: references/gotchas.md