CTEs to Lineage

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.

Last updated