Zum Inhalt springen
DuckDBDuckDBDuckDB
Deutsch

MERGE-Anweisung für SCD Typ 2

Dies ist eine praxisnahe Schritt-für-Schritt-Anleitung zur Verwendung der DuckDB-MERGE-Anweisung (eingeführt in v1.4.0) für Upserts und den Aufbau von Slowly Changing Dimension Type 2 (SCD Typ 2)-Tabellen. SCD vom Typ 2 ermöglichen es, vollständige historische Versionen von Datensätzen zu behalten und gleichzeitig die aktuelle Version eindeutig zu kennzeichnen – ideal für Audit-Trails, Data Warehousing und analytische Workloads. SCD vom Typ 2 sind praktisch, wenn Sie frühere Werte Ihrer Primärschlüsseldaten kennen möchten, wann sie sich geändert haben und wie lange sie in einem bestimmten Zustand waren.

Warum MERGE in DuckDB verwenden?

  • Eine einzige SQL-Anweisung für INSERT, UPDATE und weiches DELETE (Upsert und Ablaufen).
  • Deutlich übersichtlicher und schneller als die entsprechende Python/Pandas-Logik.
  • Vollständige Historienverfolgung ohne hartes Löschen.
  • Funktioniert dank der Konnektivität von DuckDB direkt auf Parquet, CSV und Datenbanken!

Voraussetzungen

  • Grundlegende SQL-Kenntnisse

Wichtige Begriffe

Begriff Bedeutung
Zieltabelle Die Haupt-/Mastertabelle, die Sie aktualisieren (z. B. master_ducks)
Quelltabelle Die eingehenden/neuen Daten (z. B. incoming_ducks)
MERGE INTO Gibt die Zieltabelle an
USING Gibt die Quelltabelle bzw. -abfrage an
ON Join-Bedingung (üblicherweise Primär-/Geschäftsschlüssel + aktuelles Flag)
WHEN MATCHED Zeile existiert in beiden → typischerweise UPDATE (oder DELETE)
WHEN NOT MATCHED BY TARGET Neue Zeile (Insert)
WHEN NOT MATCHED BY SOURCE Zeile verschwunden → weiches Löschen/Ablaufen der alten Version
RETURNING merge_action Optional: zeigt, was mit jeder Zeile geschehen ist (INSERT/UPDATE/DELETE)

Eine SCD-Typ-2-Dimensionstabelle aufbauen

Wir verfolgen Enten und bewahren die Historie, wann immer sich Name, Rasse oder Standort ändern.

DuckDB hat eine Notebook-Oberfläche; das eignet sich gut, um mehrere SQL-Anweisungen zu verwalten und den Code zu segmentieren. Die UI wird mit der DuckDB-CLI ausgeliefert. Wenn Sie die CLI installiert haben, können Sie das Frontend nutzen. Um das Notebook-Frontend zu starten, führen Sie einfach duckdb -ui aus und navigieren Sie zu http://localhost:4213/, um Ihren SQL-Code in den Notebooks zu schreiben. Kopieren Sie die folgenden Codeblöcke, um dieser Anleitung zu folgen.

Schritt 1: Die eingehende (Quell-)Tabelle erstellen

Diese Tabelle repräsentiert die heutigen Transaktionsdaten.

CREATE TABLE IF NOT EXISTS incoming_ducks (
    duck_id     INTEGER,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE,
    end_date    DATE,
    is_current  BOOLEAN
);


INSERT INTO incoming_ducks VALUES
    (101, 'Quackers',   'Mallard',       'Pond B',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (102, 'Waddles',    'Pekin',         'Pond A',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (104, 'Splash',     'Muscovy',       'Pond C',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (105, 'Puddles',    'Indian Runner', 'Relocated',   CURRENT_DATE - INTERVAL '1 day', NULL, true);

Schritt 2: Die Master- (Ziel-)Tabelle erstellen

Diese Tabelle repräsentiert die SCD-Daten vom Typ 2 (d. h. Transaktionsdaten mit Historie).

CREATE TABLE IF NOT EXISTS master_ducks (
    record_id   INTEGER PRIMARY KEY,
    duck_id     INTEGER NOT NULL,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE NOT NULL,
    end_date    DATE,
    is_current  BOOLEAN NOT NULL DEFAULT true
);


CREATE SEQUENCE IF NOT EXISTS duck_record_seq START 1;


INSERT INTO master_ducks VALUES
    (nextval('duck_record_seq'), 101, 'Quackers', 'Mallard',       'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 102, 'Waddles',  'Pekin',         'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 103, 'Feathers', 'Rouen',         'Pond B', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 105, 'Puddles',  'Indian Runner', 'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true);

Schritt 3: Die MERGE-Anweisung ausführen

Diese Anweisung führt den Merge aus. Sie prüft Unterschiede zwischen den Daten von Ziel und Quelle und folgt der angegebenen Logik WHEN MATCHED bzw. WHEN NOT MATCHED.

MERGE INTO master_ducks AS target
USING incoming_ducks AS source
ON target.duck_id = source.duck_id AND target.is_current = true


WHEN MATCHED AND (
       target.duck_name <> source.duck_name OR
       target.breed     <> source.breed     OR
       target.location  <> source.location
) THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false


WHEN NOT MATCHED BY SOURCE AND target.is_current = true THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false


WHEN NOT MATCHED BY TARGET THEN INSERT (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
) VALUES (
    nextval('duck_record_seq'),
    source.duck_id, source.duck_name, source.breed, source.location,
    source.begin_date, source.end_date, source.is_current
)


RETURNING merge_action, *;

Schritt 4: Neue aktuelle Versionen für geänderte Datensätze einfügen

Diese Anweisung fügt die neuen aktuellen Datensätze in die Mastertabelle ein. Dasselbe Ergebnis lässt sich zwar über die RETURNING-Klausel der MERGE-Anweisung erzielen, dieser zweistufige Ansatz ist jedoch direkter und leichter nachvollziehbar.

INSERT INTO master_ducks (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
)
SELECT
    nextval('duck_record_seq'),
    source.duck_id,
    source.duck_name,
    source.breed,
    source.location,
    CURRENT_DATE AS begin_date,
    NULL AS end_date,
    true AS is_current
FROM incoming_ducks AS source
INNER JOIN master_ducks AS target
    ON source.duck_id = target.duck_id
WHERE target.is_current = false
  AND target.end_date = CURRENT_DATE - INTERVAL '1 day';

Schritt 5: Die Ergebnisse abfragen

Die folgenden Abfragen können verwendet werden, um die aus der MERGE-Anweisung resultierenden Daten zu prüfen.

-- All history
SELECT * FROM master_ducks ORDER BY duck_id, begin_date DESC;


-- Only current records
SELECT * FROM master_ducks WHERE is_current = true;


-- Only expired historical records
SELECT * FROM master_ducks WHERE is_current = false ORDER BY duck_id, begin_date DESC;

Schritt 6: Eine einzelne Ente untersuchen

Um das Konzept besser zu veranschaulichen, betrachten wir eine einzelne Ente und verdeutlichen so den Mehrwert von SCD Typ 2. Wenn wir nach dem Ausführen der MERGE-Anweisung und des anschließenden INSERT aus der Mastertabelle selektieren, sehen wir die einzelnen Zeilen für Quackers.

Um die ursprüngliche, historische Datenzeile anzuzeigen:

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = false;

Ergebnis:

record_id duck_id duck_name breed location begin_date end_date is_current
1 101 Quackers Mallard Pond A 2025-11-24 2025-11-25 false

Hinweis:

  • Das end date ist NOT NULL; es enthält das Datum, an dem die Daten dieser Ente aktualisiert wurden.
  • is_current ist false und kennzeichnet damit einen historischen Datensatz.
  • Das Feld, das sich ändert, ist location; es ist derzeit Pond A und wird auf Pond B aktualisiert.

Um die aktuelle Datenzeile anzuzeigen:

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = true;
record_id duck_id duck_name breed location begin_date end_date is_current
10 101 Quackers Mallard Pond B 2025-11-26 NULL true

Hinweis:

  • Das end date ist NULL; NULL bedeutet in diesem Zusammenhang, dass dies der neueste Datensatz für diese duck_id ist.
  • is_current ist true und kennzeichnet damit ebenfalls einen aktuellen Datensatz.
  • Die location ist nun Pond B.

Um alle Daten von Quackers anzuzeigen, also sowohl aktuelle als auch nicht aktuelle Zeilen:

SELECT * FROM master_ducks where duck_name = 'Quackers';

| record_id | duck_id | duck_name | breed | location | begin_date | end_date | is_current | | 1 | 101 | Quackers | Mallard | Pond A | 2025-11-24 | 2025-11-25 | false | | 10 | 101 | Quackers | Mallard | Pond B | 2025-11-26 | NULL | true |

Häufige Muster und Varianten

Anwendungsfall Zu verwendende Klausel
Einfacher Upsert (ohne Historie) WHEN MATCHED THEN UPDATE und WHEN NOT MATCHED BY TARGET THEN INSERT
Upsert und fehlende Zeilen löschen WHEN NOT MATCHED BY SOURCE THEN DELETE hinzufügen
Nur neue einfügen, nie aktualisieren WHEN MATCHED weglassen
Betroffene Zeilen zurückgeben RETURNING merge_action, * hinzufügen

Best Practices

  • Denken Sie daran: TARGET ist die Mastertabelle und SOURCE die eingehende Tabelle oder Abfrage.
  • Halten Sie end_date für aktuelle Zeilen NULL (macht Abfragen schneller).
  • Verpacken Sie MERGE- und INSERT-Anweisungen bei Bedarf in eine Transaktion.
  • Verwenden Sie einen Primärschlüssel oder einen Surrogatschlüssel für die Eindeutigkeit.
  • Testen Sie zuerst mit RETURNING.

Dies ist eine inoffizielle Website und nicht mit DuckDB verbunden. Offizielle Seite:duckdb.org.duckdb.ubitools.com · Übersetzt und erstellt mit Astro und daisyUI