データリネージの例：SQLと図を用いた6つの実例

あ データ系統の例 具体的な SQL の例で、どのソース テーブルと列がどの出力列にデータを供給し、データが途中でどのような変換を経るかを示します。以下の 6 つの例では、データ エンジニアが実際に遭遇する状況を取り上げています。 INSERT-SELECTマルチCTE変換、ビューチェーン、動的SQLを使用したストアドプロシージャ、dbtモデルチェーン、およびクロスデータベースETLフローなど、さまざまな例を示します。各例ではSQL文を示し、それに対応するリネージグラフがどのように生成されるかを正確に説明します。

これらの例文の読み方

各系統図には2種類の辺が含まれています。 直接 エッジとは、データが物理的にソース列からターゲット列へ流れることを意味し、場合によっては関数や集計関数を介して流れる。 間接的 エッジとは、列が結果に着地することなく結果を形成することを意味します。列は、 どこ, 参加する、 と グループ分け 条項。間接的なエッジを見落とすと、影響分析がうまくいかなくなります。フィルター列を削除すると、選択した列を削除するのと同様にレポートが壊れてしまうからです。これらの用語が初めての場合は、 データリネージとは何か と 列レベルの系統がどのように機能するかそして、戻ってきてください。

例1：単純なINSERT-SELECTデータリネージの例

系統に関する議論の出発点は、集計データを要約表に書き込む1つの記述です。

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;

系統図には 3 つのテーブルがあります (注文, 顧客, 売上概要）そして、これらの列レベルのエッジ：

対象列	ソース列	エッジタイプ	経由
売上概要地域	顧客地域	直接	プレーンコピー
売上概要.合計金額	注文数	直接	和（）
売上概要。*	注文状況	間接的	どこ フィルター
売上概要。*	注文の顧客ID, 顧客ID	間接的	参加する 状態

グラフが既に示していることで、表レベルのビューでは示されない点に注目してください。 顧客メール そして、手つかずの他のすべてのコラムはここでは何の役割も果たさず、 注文状況 出力には表示されなくても、それは重要な意味を持ちます。多くの系統追跡ツールは最後の段階を完全に省略しますが、SQLFlowは直接的な系統と間接的な系統を、切り替え可能な別々の関係タイプとしてモデル化します。

例2：複数CTE変換

CTEは、手描きの系統図が適用される部分です。なぜなら、各CTEは、グラフが元のソースを見失うことなく通過しなければならない一時的な関係だからです。

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 ranking WHERE rn = 1;

系統図は 毎日 と ランキング 中間ノードとして扱われるため、対象となる列の完全なパスは次のようになります。 注文数 → 和（） → 日別合計 → ランク付けされた日数 → ベストデイ収益ウィンドウ関数は、より繊細な連鎖を作り出します。 rn は以下から計算されます 顧客ID （分割）と 日合計 （注文）そして ここで、rn = 1 作る rn すべての出力列への間接的な入力です。 注文数 影響 ベストデイ収益 2つの経路があります。1つは合計値による直接的な経路、もう1つはどの行が残るかを決定するランキングによる間接的な経路です。列名のみをパターンマッチングするリゾルバでは、この2番目の経路を認識できません。

例3：一連の見解

ビューは積み重ねられる。成熟したデータウェアハウスでは、レポートは物理的なテーブルから3つか4つのビュー定義を隔てた場所に存在することが多く、「この数値は実際にはどこから来たのか？」という疑問を解明するには、データチェーン全体を辿る必要がある。

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;

グラフは v_customer_revenue.revenue ビュー境界をたどって戻る 注文数、 と 顧客ステータス 最初のビューのフィルターから継承された間接的なエッジとして接続されています。また、反対方向にも有用な情報を提供します。 v_active_customers.email 最初のビューで選択されますが、下流では何も消費されません。このようなデッドカラムは、クリーンアップや移行の前に見つけておきたいものであり、テーブルレベルでは見えません。ビュー定義でこれを使用する場合 選択 *SQLFlow はスキーマに対してスターを展開するため、継承されたすべての列は物理的なソースに解決されます。

例4：動的SQLを使用したストアドプロシージャ

これは、対象テーブル名がコード内にテキストとして存在しないため、リネージツールを区別する例です。以下は、一時テーブルと動的に組み立てられたテーブルを使用して月次スナップショットを作成する SQL Server プロシージャです。 入れる:

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

正しい系統図は、以下の2つの障害を乗り越えます。 注文数 → 和（） → #月間.月間合計 → snapshot_monthly.month_total、 と 注文日 間接的なエッジとして どこ 条項と @月 そのフィルターへの入力として追跡されるパラメーター。そこに到達するには、実際の T-SQL プロシージャ パーサー (ステートメント スプリッターではない)、ステートメント間の一時テーブルの追跡、および渡された SQL 文字列の解決が必要です。 sp_executesql. SQLFlow has dedicated procedural parsers for SQL Server T-SQL and Oracle PL/SQL, resolves dynamic SQL inside procedures, and additionally renders a call graph of which procedures invoke which, so procedure-to-procedure lineage is visible too. Dynamic SQL is the most common blind spot in lineage tooling; if you evaluate any tool, test it with this pattern first.

例5：dbtモデルチェーン

dbt は、モデルレベルの系統情報をすぐに提供します ref()しかし、興味深いのは列レベルの問題です。どのソースフィールドがどのマート列に供給され、どのステージングリネームを経由するのか？

-- 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

プロジェクトのdbtマニフェストをSQLFlowにインポートすると、グラフがテンプレート化された参照を具体的な列チェーンに解決します。 生の注文数 → stg_orders.amount → 和（） → fct_顧客収益.生涯収益名前変更により ID → 注文ID ステージングホップに保存され、 状態 マートのフィルターから間接的なエッジとして渡されます。マニフェストはモデルをそれらが構築するウェアハウスオブジェクトに結び付けるため、同じグラフがdbtで管理されるリネージと、dbtの外部でそれらのテーブルに触れる他のすべてのものを調和させます。

例6：クロスデータベースETL

不動産は複数のエンジンにまたがって存在します。典型的な流れは次のとおりです。運用中のSQL Serverデータベースがクラウドストレージに抽出され、Snowflakeステージングにロードされ、分析スキーマに変換されます。ロードと変換の部分は、次のようになります。

-- 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;

Snowflake側では、グラフは 金額（米ドル） 2 つの直接の親を持つ計算列として、 orders_raw.amount と fx_rates.rate算術式で結合され、プラス 通貨 結合条件からの間接的なエッジとして。パイプライン全体をカバーするために、SQLFlow には、それぞれ独自のダイアレクトで両側を供給します。T-SQL 抽出ロジックは SQL Server パーサーで解析され、Snowflake スクリプトは Snowflake パーサーで解析されます。ダイアレクト固有のパーサーは合計 39 個あります。エンタープライズ展開では、結果は永続的なリネージ リポジトリに格納され、100 を超えるデータベースと 100 万を超える列の環境をバッチ スキャンするため、エンドツーエンドの SQL Server から Snowflake までの状況が 1 つのグラフに表示され、DataHub、Microsoft Purview、または OpenMetadata にエクスポートできます。

これら6つの例に共通するもの

上記のグラフはすべて、SQLテキストとスキーマメタデータのみから生成されています。これが、この手法の根幹です。 SQLベースのデータリネージ変換ロジックは既にSQLに存在するため、そのSQLの静的解析により、エージェントもクエリログへのアクセスもデータの行を読み取ることもなく、データ系列が再構築されます。例の違いは、単一のSQLから解析がどれだけ難しくなるかという点だけです。 INSERT-SELECT プロシージャ内で動的SQLを実行する。

例	それが何を鍛えるか	難しいところ
1. INSERT-SELECT	直接エッジと間接エッジ	モデリング どこ/参加する 列を系統として
2. マルチCTE	中間関係	ウィンドウ関数の入力; ランクに基づくフィルタパス
3. ビューチェーン	入れ子になった定義	星の膨張; デッドコラムの発見
4. ストアドプロシージャ	手続きコード	一時テーブルと動的SQL解決
5. dbtチェーン	テンプレート化されたSQL	解決する ref()/ソース（） 実物
6. クロスデータベースETL	複数の方言	エンジンごとのグラフを1つのリポジトリに統合する

よくある質問

データ系統図とは何ですか？

データリネージ図は、ノードがテーブル、ビュー、または列を表し、エッジがSQL変換を通じてそれらの間でデータがどのように流れるかを示す有向グラフです。このページで説明されているような列レベルの図では、ソース列とターゲット列の関係ごとに1つのエッジが描画され、その関係を作成した関数または句が注釈として付けられます。

これらの例を自分で再現してもいいですか？

はい。上記のSQLスニペットのいずれかを貼り付けてください。 無料のSQLFlowビジュアライザー該当する方言を選択すると、数秒でインタラクティブな図が表示されます。クラウド版には無料プランがあります。

直接的な血縁関係と間接的な血縁関係の違いは何ですか？

直接的なデータ系列とは、例えば、ソース列からターゲット列へデータが流れることを意味します。 SUM(orders.amount) 給餌 合計金額間接的な系統とは、列が結果には現れないが結果に影響を与えることを意味します。 どこ フィルターまたは 参加する 重要な点として、SQLFlowは両方を個別の切り替え可能なエッジタイプとして追跡しますが、ほとんどのツールは直接フローのみを捕捉します。

ストアドプロシージャや動的SQLから系統情報を抽出できますか？

はい、ただしクエリパーサーだけでなく、プロシージャパーサーが必要です。SQLFlowにはOracle PL/SQLとSQL Server T-SQL専用のパーサーがあり、パラメータと一時テーブルを介したデータ系列の追跡、プロシージャ内で構築されたSQL文字列の解決、プロシージャ間の呼び出しグラフの描画が可能です。

オープンソースツールはこれらの例に対応できますか？

部分的に。図書館は SQL Lineage と sqlglot 個々のクエリの解析に非常に優れており、例1と例2も適切に処理します。コミットする前にテストする価値のある例は、例4～6です。動的SQLを使用した手続き型コード、別のエッジタイプとしての間接的なデータ系列、複数の方言からなる環境を1つのインタラクティブなグラフに統合する例です。評価対象のツールで、最も複雑なストアドプロシージャを実行し、出力結果を比較してください。

これらの例はどのSQL方言に対応していますか？

SQLFlowには、Snowflake、BigQuery、Redshift、Databricks、Oracle、SQL Server、PostgreSQL、MySQL、Teradata、Hive、Spark SQL、Trinoなど、39種類の言語固有のパーサーが同梱されています。上記の例では、汎用SQL、T-SQL、dbtテンプレートSQL、Snowflake構文を使用しており、それぞれ汎用ANSI文法ではなく、独自の文法で解析されます。