Beispiele zur Datenherkunft: 6 reale Szenarien mit SQL und Diagrammen

A Beispiel für Datenherkunft Es zeigt anhand eines konkreten SQL-Abschnitts, welche Quelltabellen und -spalten welche Ausgabespalten speisen und welche Transformationen die Daten dabei durchlaufen. Die sechs unten aufgeführten Beispiele decken Situationen ab, denen Dateningenieure tatsächlich begegnen: ein einfacher INSERT-SELECTEs handelt sich um eine Multi-CTE-Transformation, eine Kette von Sichten, eine gespeicherte Prozedur mit dynamischem SQL, eine dbt-Modellkette und einen datenbankübergreifenden ETL-Workflow. Jedes Beispiel enthält das SQL und beschreibt anschließend detailliert den zugehörigen Datenherkunftsgraphen.

Wie man diese Beispiele liest

Jeder Abstammungsgraph enthält zwei Arten von Kanten. A direkt Edge bedeutet, dass Daten physisch von einer Quellspalte in eine Zielspalte fließen, möglicherweise über eine Funktion oder Aggregation. indirekt Kante bedeutet, dass eine Spalte das Ergebnis formt, ohne darin zu landen: Spalten, die verwendet werden in WO, VERBINDEN, Und GRUPPE NACH Klauseln. Das Übersehen indirekter Auswirkungen ist der Grund, warum Wirkungsanalysen fehlschlagen, denn das Entfernen einer Filterspalte führt genauso sicher zu einem fehlerhaften Bericht wie das Entfernen einer ausgewählten Spalte. Falls Ihnen diese Begriffe neu sind, beginnen Sie mit Was ist Datenherkunft? Und wie die Herkunftsanalyse auf Spaltenebene funktioniertDann komm zurück.

Beispiel 1: ein einfaches Beispiel zur Datenherkunft bei einer INSERT-SELECT-Anweisung

Ausgangspunkt jeder Abstammungsdiskussion: eine Anweisung, die aggregierte Daten in eine Übersichtstabelle schreibt.

INSERT INTO sales_summary (region, total_amount) SELECT c.region, SUM(o.amount) FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'completed' GROUP BY c.region;

Der Stammbaum besteht aus drei Tabellen (Bestellungen, Kunden, Verkaufsübersichtund diese Kanten auf Spaltenebene:

Zielspalte	Quelle Spalte	Kantentyp	Über
Vertriebsübersicht Region	Kundenregion	Direkt	einfache Kopie
Gesamtbetrag der Verkäufe	Bestellungen.Betrag	Direkt	SUMME()
Verkaufsübersicht.*	Bestellungen.Status	Indirekt	WO Filter
Verkaufsübersicht.*	orders.customer_id, Kunden-ID	Indirekt	VERBINDEN Zustand

Beachten Sie, was Ihnen das Diagramm bereits zeigt, was eine Tabellenansicht nicht zeigen würde: Kunden-E-Mail und alle anderen unberührten Spalten spielen hier keine Rolle, Bestellungen.Status Es ist relevant, auch wenn es nie in der Ausgabe erscheint. Viele Lineage-Tools überspringen diesen letzten Schritt vollständig; SQLFlow modelliert direkte und indirekte Lineage als separate, umschaltbare Beziehungstypen.

Beispiel 2: Multi-CTE-Transformation

Bei CTEs (Common Table Expressions) beginnt die Geschichte der handgezeichneten Stammbaumdiagramme, denn jede CTE ist eine temporäre Relation, die der Graph durchlaufen muss, ohne die ursprünglichen Quellen aus den Augen zu verlieren.

WITH daily AS ( SELECT order_date, customer_id, SUM(amount) AS day_total FROM orders GROUP BY order_date, customer_id ), ranked AS ( SELECT customer_id, day_total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY day_total DESC) AS rn FROM daily ) SELECT customer_id, day_total AS best_day_revenue FROM ranked WHERE rn = 1;

Der Stammbaum wird dargestellt täglich Und Rangliste als Zwischenknoten, sodass der vollständige Pfad der relevanten Spalte lautet: Bestellungen.Betrag → SUMME() → täglich.Tagesgesamt → Rangliste.Tagesgesamt → bester TagesumsatzDie Fensterfunktion erzeugt eine subtilere Kette: rn wird berechnet aus Kunden-ID (Partition) und Tagesgesamt (Reihenfolge), und dann WHERE rn = 1 macht rn ein indirekter Input für jede Ausgabespalte. Bestellungen.Betrag Einflüsse bester Tagesumsatz Zweimal: direkt über die Summe und indirekt über die Rangfolge, die entscheidet, welche Zeile erhalten bleibt. Ein Resolver, der nur Spaltennamen nach Mustern abgleicht, kann diesen zweiten Pfad nicht erkennen.

Beispiel 3: eine Kette von Ansichten

Ansichten stapeln sich. In einem ausgereiften Data Warehouse befindet sich ein Bericht oft drei oder vier Ansichtsdefinitionen von den physischen Tabellen entfernt, und die Frage „Woher kommt diese Zahl eigentlich?“ erfordert das Durchlaufen der gesamten Kette.

CREATE VIEW v_active_customers AS SELECT id, name, email FROM customers WHERE status = 'active'; CREATE VIEW v_customer_revenue AS SELECT a.id, a.name, SUM(o.amount) AS revenue FROM v_active_customers a JOIN orders o ON o.customer_id = a.id GROUP BY a.id, a.name;

Die Grafik zeigt v_customer_revenue.revenue durch die Sichtgrenze zurückverfolgen zu Bestellungen.Betrag, mit Kundenstatus Es ist als indirekte Kante angehängt, die vom Filter der ersten Ansicht geerbt wurde. Es offenbart auch etwas Nützliches in der anderen Richtung: v_active_customers.email wird von der ersten Ansicht ausgewählt, aber von keiner nachfolgenden Ansicht verwendet. Solche toten Spalten sind genau das, was Sie vor einer Bereinigung oder Migration finden möchten, und sie sind auf Tabellenebene unsichtbar. Wenn Ansichtsdefinitionen verwenden WÄHLEN *SQLFlow erweitert den Stern anhand des Schemas, sodass jede geerbte Spalte weiterhin auf ihre physische Quelle aufgelöst wird.

Beispiel 4: Gespeicherte Prozedur mit dynamischem SQL

Dieses Beispiel verdeutlicht die Unterschiede zwischen Lineage-Tools, da der Name der Zieltabelle im Code nicht einmal als Literaltext existiert. Hier ist eine SQL-Server-Prozedur, die mithilfe einer temporären Tabelle und einer dynamisch zusammengestellten Tabelle einen monatlichen Snapshot erstellt. EINFÜGEN:

CREATE PROCEDURE dbo.load_monthly_snapshot @month VARCHAR(7) AS BEGIN SELECT o.customer_id, SUM(o.amount) AS month_total INTO #monthly FROM dbo.orders o WHERE CONVERT(VARCHAR(7), o.order_date, 126) = @month GROUP BY o.customer_id; DECLARE @sql NVARCHAR(MAX) = N'INSERT INTO dbo.snapshot_monthly (customer_id, month_total) ' + N'SELECT customer_id, month_total FROM #monthly'; EXEC sp_executesql @sql; END

Der korrekte Abstammungsgraph überwindet beide Hindernisse: Bestellungen.Betrag → SUMME() → #monthly.month_total → snapshot_monthly.month_total, mit orders.order_date als indirekte Kante durch die WO Klausel und die @Monat Der Parameter wird als Eingabe für diesen Filter verfolgt. Um dies zu erreichen, benötigt man einen echten T-SQL-Prozedurparser (keinen Statement-Splitter), die Verfolgung temporärer Tabellen über Anweisungen hinweg und die Auflösung der übergebenen SQL-Zeichenkette. sp_executesqlSQLFlow verfügt über spezielle Prozedurparser für SQL Server T-SQL und Oracle PL/SQL, löst dynamisches SQL innerhalb von Prozeduren auf und erstellt zusätzlich einen Aufrufgraphen, der die Aufrufreihenfolge der einzelnen Prozeduren darstellt. Dadurch wird auch die Herkunft der Prozeduren sichtbar. Dynamisches SQL ist die häufigste Schwachstelle von Tools zur Herkunftsanalyse; testen Sie daher jedes Tool zunächst anhand dieses Musters.

Beispiel 5: dbt-Modellkette

dbt bietet Ihnen standardmäßig Modell-Lineage über ref()Die interessante Frage ist jedoch die auf Spaltenebene: Welches Quellfeld speist welche Mart-Spalte, und zwar über welche Staging-Umbenennungen?

-- models/staging/stg_orders.sql SELECT id AS order_id, customer_id, amount, status FROM {{ source('raw', 'orders') }} -- models/marts/fct_customer_revenue.sql SELECT customer_id, SUM(amount) AS lifetime_revenue FROM {{ ref('stg_orders') }} WHERE status = 'completed' GROUP BY customer_id

Importieren Sie das dbt-Manifest des Projekts in SQLFlow, und der Graph löst die Vorlagenreferenzen in eine konkrete Spaltenkette auf: Rohbestellungen → stg_orders.amount → SUMME() → fct_customer_revenue.lifetime_revenue, mit der Umbenennung Ausweis → Bestellnummer erhalten im Zwischenstopp und Status Die Daten werden als indirekte Kante vom Filter des Datenmarktes übertragen. Da das Manifest Modelle mit den von ihnen erstellten Warehouse-Objekten verknüpft, gleicht derselbe Graph die von dbt verwaltete Datenherkunft mit allen anderen Daten ab, die außerhalb von dbt auf diese Tabellen zugreifen.

Beispiel 6: Datenbankübergreifendes ETL

Immobilien-Engines sind eng miteinander verknüpft. Ein typischer Ablauf: Eine operative SQL-Server-Datenbank wird in den Cloud-Speicher extrahiert, in Snowflake Staging geladen und anschließend in ein Analyseschema transformiert. Der Lade- und Transformationsprozess sieht folgendermaßen aus:

-- Snowflake COPY INTO staging.orders_raw FROM @s3_extract/orders/; INSERT INTO analytics.fct_orders (order_id, customer_id, amount_usd) SELECT r.order_id, r.customer_id, r.amount * fx.rate FROM staging.orders_raw r JOIN staging.fx_rates fx ON fx.currency = r.currency;

Auf der Snowflake-Seite zeigt das Diagramm Folgendes: Betrag_USD als berechnete Spalte mit zwei direkten übergeordneten Spalten, orders_raw.amount Und fx_rates.rate, verbunden durch den arithmetischen Ausdruck, plus Währung Als indirekte Kante aus der Join-Bedingung. Um die gesamte Pipeline abzudecken, werden beide Seiten mit SQLFlow in ihren jeweiligen Dialekten gespeist: Die T-SQL-Extraktionslogik wird mit dem SQL-Server-Parser und die Snowflake-Skripte mit dem Snowflake-Parser analysiert – insgesamt stehen 39 dialektspezifische Parser zur Verfügung. In Unternehmensumgebungen werden die Ergebnisse in einem persistenten Lineage-Repository gespeichert, das Datenbanken mit über 100 Datenbanken und mehr als einer Million Spalten im Batch-Verfahren durchsucht. So wird die gesamte SQL-Server-zu-Snowflake-Pipeline in einem einzigen Graphen abgebildet und kann nach DataHub, Microsoft Purview oder OpenMetadata exportiert werden.

Was alle sechs Beispiele gemeinsam haben

Alle obigen Diagramme wurden ausschließlich aus SQL-Text und Schema-Metadaten abgeleitet. Das ist die Kernmethode dahinter. SQL-basierte DatenherkunftDa die Transformationslogik bereits in SQL implementiert ist, rekonstruiert die statische Analyse dieses SQL-Codes die Datenherkunft ohne zusätzliche Agenten, ohne Zugriff auf das Abfrageprotokoll und ohne jemals eine Zeile Ihrer Daten zu lesen. Die Unterschiede zwischen den Beispielen liegen lediglich im Aufwand der Analyse, ausgehend von einer einzigen Abfrage. INSERT-SELECT zu dynamischem SQL innerhalb einer Prozedur.

Beispiel	Was es trainiert	Schwieriger Teil
1. INSERT-SELECT	Direkte vs. indirekte Kanten	Modellieren WO/VERBINDEN Spalten als Abstammung
2. Multi-CTE	Zwischenbeziehungen	Eingaben für Fensterfunktionen; Pfad des Filters auf Rangebene
3. Kette ansehen	Verschachtelte Definitionen	Sternenexpansion; Auffinden toter Säulen
4. Gespeicherte Prozedur	Verfahrensordnung	Temporäre Tabellen und dynamische SQL-Auflösung
5. DBT-Kette	SQL-Vorlage	Lösung ref()/Quelle() zu realen Objekten
6. Datenbankübergreifendes ETL	Mehrere Dialekte	Zusammenführen von Diagrammen pro Motor in einem Repository

Häufig gestellte Fragen

Was ist ein Datenherkunftsdiagramm?

Ein Datenherkunftsdiagramm ist ein gerichteter Graph, dessen Knoten Tabellen, Sichten oder Spalten darstellen und dessen Kanten den Datenfluss zwischen ihnen durch SQL-Transformationen zeigen. Spaltendiagramme, wie die auf dieser Seite beschriebenen, zeichnen für jede Quellspalten-Zielspalten-Beziehung eine Kante, die mit der Funktion oder Klausel beschriftet ist, die diese Beziehung erzeugt hat.

Kann ich diese Beispiele selbst nachstellen?

Ja. Fügen Sie einen der oben stehenden SQL-Codeausschnitte in die folgende Datei ein: kostenloser SQLFlow-VisualisiererWählen Sie den passenden Dialekt aus, und das interaktive Diagramm erscheint in Sekundenschnelle. Die Cloud-Version bietet eine kostenlose Basisversion.

Worin besteht der Unterschied zwischen direkter und indirekter Abstammung?

Direkte Datenherkunft bedeutet, dass Daten von einer Quellspalte in eine Zielspalte fließen, zum Beispiel SUMME(orders.amount) Fütterung GesamtbetragIndirekte Abstammung bedeutet, dass eine Spalte das Ergebnis beeinflusst, ohne selbst darin aufzutreten, wie zum Beispiel eine WO Filter oder VERBINDEN Wichtig. SQLFlow verfolgt beides als separate, umschaltbare Kantentypen; die meisten Tools erfassen nur den direkten Datenfluss.

Lässt sich die Herkunft aus gespeicherten Prozeduren und dynamischem SQL extrahieren?

Ja, aber es benötigt einen Prozedurparser, nicht nur einen Abfrageparser. SQLFlow verfügt über dedizierte Parser für Oracle PL/SQL und SQL Server T-SQL, verfolgt die Befehlskette über Parameter und temporäre Tabellen, löst SQL-Zeichenketten innerhalb von Prozeduren auf und erstellt den Aufrufgraphen zwischen den Prozeduren.

Können Open-Source-Tools diese Beispiele verarbeiten?

Teilweise. Bibliotheken wie sqllineage Und sqlglot Sie sind wirklich gut darin, einzelne Abfragen zu analysieren, und sie bewältigen die Beispiele 1 und 2 problemlos. Die Beispiele 4 bis 6 sollten Sie vor dem Commit testen: prozeduraler Code mit dynamischem SQL, indirekte Herkunft als separater Kantentyp und das Zusammenführen eines mehrsprachigen Gebiets zu einem interaktiven Graphen. Führen Sie Ihre komplexeste gespeicherte Prozedur mit einem beliebigen Tool Ihrer Wahl aus und vergleichen Sie die Ausgabe.

Mit welchen SQL-Dialekten funktionieren diese Beispiele?

SQLFlow enthält 39 dialektspezifische Parser, darunter für Snowflake, BigQuery, Redshift, Databricks, Oracle, SQL Server, PostgreSQL, MySQL, Teradata, Hive, Spark SQL und Trino. Die obigen Beispiele verwenden generisches SQL, T-SQL, dbt-templatisiertes SQL und Snowflake-Syntax. Jede Syntax wird anhand ihrer eigenen Grammatik und nicht anhand einer generischen ANSI-Grammatik analysiert.