Ein föderiertes Data Warehouse: Skalierbare Multi-Mandanten-Architektur mit Automation & Templates

Projektanforderungen – Komplexe Strukturen und Data Governance

Es war von Anfang an klar, dass dieses Projekt etwas Besonders ist.

Ein großes Unternehmen beauftragte uns damit, eine Marketing-Dashboard-Suite aufzubauen, mit der sich der Erfolg der diversen Werbekampagnen messen lässt. Alle Informationen standen schon zur Verfügung, jedoch waren sie in den Datensilos der einzelnen Plattformen. Für Analysen mussten diese Daten manuell zusammengeführt und ausgewertet werden.
Um diesen Prozess zu vereinfachen und zu automatisieren sowie um manche Analysen überhaupt möglich zu machen, sollten die Daten aus Social-Media-Plattformen, Adwords-Kampagnen und Besucherströmen auf der Website in einem Snowflake-Data-Warehouse zusammengeführt und aufbereitet werden.
Eine Besonderheit des Unternehmens ist, dass es in verschiedene regionale Betriebe aufgeteilt ist. Jeder dieser Betriebe hat große Freiheiten in seinen Marketingstrategien, was sich unter anderem darin zeigt, dass jeder Betrieb über eigene Social-Media-Kanäle und eigene Werbekampagnen verfügt. Aufgrund ihrer rechtlichen Struktur benötigen diese Betriebe eine strenge Isolierung ihrer Datenverarbeitung und Datennutzung – eine strikte Mandantentrennung war also notwendig. Dennoch sollte eine Analyseplattform geschaffen werden, die von allen Betrieben genutzt werden kann.

Daten-Architektur – Medallion-Modell

Als Datenarchitektur haben wir uns für ein Medallion-Modell entschieden. Dadurch hat jeder Bereich eine klare Aufgabe:

Bronze Layer: Rohdaten, die direkt aus den Datenquellen extrahiert wurden.

Silver Layer: Aufbereitete Daten aus dem Bronze-Layer: Deduplizierung, Filtern ungültiger Einträge, Maskierung wo nötig, und Normalisierung der Daten.

Gold Layer: Datenprodukte, die für die spezifischen Einsatzzwecke, also z.B. einzelne Dashboards, modelliert werden.

Im Gold Layer, unserem Data Warehouse, sind wir flexibler als auf den anderen Ebenen: Datenprodukte können entweder auf Entitäten aus der Ebene darunter, also dem Silver Layer, aufbauen, oder sie basieren als Spezialisierung auf anderen Datenprodukten aus demselben Layer.  Auch Kombinationen sind möglich.

Jeder Betrieb und die Dachorganisation müssen aus Gründen der Datensicherheit über ein eigenes Data Warehouse verfügen.

Gemeinsam genutzte Daten werden in einem weiteren Data Warehouse aufbewahrt und von dort – ggf. gefiltert und maskiert – an die Data Warehouses der Betriebe verteilt.

Die Datenquellen könnte man als homogene Sammlung heterogener Quellen beschreiben. Homogen, weil meistens mehrere Betriebe gleiche Plattformen nutzen. Heterogen, weil sich die einzelnen Plattformen zum Teil deutlich unterscheiden.

Dimension 

Stand September 2025 haben wir knapp 1200 Tabellen produktiv im Projekt: 511 im Bronze-, 556 im Silver- und 120 im Gold-Layer. Tendenz steigend. Angesichts dieser Dimension wird schnell klar, dass ein manuelles Einrichten der Transformationen für Silver- und Goldlayer kein sinnvoller Weg ist.

Design – Data Engineering Best Practices

Nachdem die Datenarchitektur entworfen war, stellte sich uns die Frage, wie wir sie am besten umsetzen. Es galt, ein paar wichtige Punkte zu beachten:

Mit diesen Aspekten im Kopf haben wir uns dazu entschieden, leichtgewichtige Python-Scripts zu schreiben, die uns mit Jinja2-Templates die SQL-Statements generieren, welche unsere Data Warehouses definieren. Die Konfiguration für alle Betriebe liegt in einer Konfigurationsdatei, die sowohl von den Scripts als auch von den Templates zur Feinsteuerung benutzt wird.
Das Script akzeptiert Argumente, die es ermöglichen, nur einen Teil der Datenstruktur neu aufzubauen. So können wir nach Änderungen beispielsweise die Daten für eine bestimmte Plattform in allen Betrieben oder alle Strukturen eines bestimmten Betriebs neu aufbauen lassen. Das ist wichtig, da neue Anforderungen und Korrekturen oft auch Änderungen an der Datenstruktur erfordern. Diese Änderungen sollen leicht und semi-automatisch umsetzbar sein. So vermeiden wir Fehler, die bei einer manuellen Ausführung entstehen können.

SQL Templates

Die Templates sind das Herzstück des Projekts. Hier werden sämtliche Transformationen definiert: Von der initialen Bereinigung über die Normalisierung bis hin zu den komplexeren Datenprodukten im Gold-Layer.

Um uns die Pflege zu erleichtern und möglichst viel Code wiederzuverwenden (anstatt ihn zu kopieren) haben wir ein System aus “Sub-Templates” (also Templates, die von anderen eingebunden werden) und Macros etabliert. Diese sind in Ordnern und Dateien organisiert, die grob der Struktur des Projekts entsprechen.

So sieht die Verzeichnisstruktur des Projekts aus:

Gruppen von Tabellen im Silver-Layer und Datenprodukte im Gold-Layer haben jeweils ein Haupt-Template, das sozusagen den Einstiegspunkt darstellt. Das sind im obigen Beispiel silver/facebook/fb_main.jj2.sql (für alles von Facebook) und gold/socialmedia.jj2.sql (für das Social-Media-Dashboard). Von dort aus werden die weiteren Templates und Macros eingebunden.

Die Startdatei für Facebook beschränkt sich beispielsweise darauf, die benötigten Entitäten einzuführen. Die genaue Definition dieser Entitäten liegt dann in den jeweils referenzierten Templates:

{% from 'macros.jj2.sql' import build_dyn_table %}
{% import 'silver/common/social_media_schemas.jj2.sql' as td %}

USE ROLE {{ role }};
USE DATABASE {{ database }};

{{ build_dyn_table(
  'SIL_FACEBOOK.FB_MEDIA',
  config.inputs.media,
  'silver/facebook/fb_media.jj2.sql',
  schema = td.media()
)}}

-- ...

{% for (platform, table_suffix) in [
  ("facebook", "_FB"),
  ("instagram", "_IG"),
  ("messenger", "_MSG")
] %}
  {{ build_dyn_table(
    'SIL_FACEBOOK.FB_ADS'~table_suffix,
    config.inputs.ads,
    'silver/facebook/fb_ads.jj2.sql',
    schema = td.ads(),
    context = {"platform": platform}
  )}}

  {{ build_dyn_table(
    'SIL_FACEBOOK.FB_ADS'~table_suffix~'_DAILY',
    config.inputs.ads,
    'silver/facebook/fb_ads_daily.jj2.sql',
    schema = td.ads_daily(),
    context = {"platform": platform}
  )}}
{% endfor %}

Kleinere Unterschiede zwischen den Betrieben können innerhalb der Templates durch die Verwendung von Werten aus der Konfiguration dargestellt werden. Entweder durch die direkte Nutzung des Wertes, oder durch eine Verzweigung mittels if-Block.
Durch diese Struktur bleibt jede einzelne Datei (relativ) klein und übersichtlich. Gleichzeitig ist es möglich, schnell zum passenden Ort zu navigieren, um eine Anpassung vorzunehmen.