CTE to Lineage

This can help debug complex SQL scripts and help you identify dependencies for each CTE.

Quick example

Read the blog: SQL Diagram (Part 1): Building DAG from SQL CTEs

Learn how to create a Directed Acyclic Graph (DAG) from SQL CTEs. This can be useful for visualizing dependencies between CTEs and understanding transformation flow.

---

CTEs are often used to simplify complex subqueries or break down a large query into more manageable pieces. They are defined using the WITH keyword followed by the CTE name and its definition enclosed in parentheses.

WITH green_trips as (
    SELECT vendor_id 
    FROM `myproject.data_lineage.nyc_green_trips_2022`
    UNION ALL
    SELECT vendor_id
    FROM `myproject.data_lineage.nyc_green_trips_2021`
),
total_green_trips as (
    SELECT vendor_id, COUNT(*) AS number_of_trips
    FROM green_trips
    GROUP BY vendor_id
)
SELECT vendor_id, number_of_trips FROM total_green_trips;

---

Copy the code above and visualize it in https://datascale.cloud/diagram, you'll see the lineage in the SQL.