Reusable CTEs

Archived

From CTE to Lineage, it showed how to visualize CTEs into Lineage diagram.

Now, let's say we want to create snippet template from these CTEs.

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;

---

Creating views

How-to save query into snippets in Datascale. On the right, click properties

Click on Category drop down, and select type to View.

view: green_trips

SELECT vendor_id 
FROM `myproject.data_lineage.nyc_green_trips_2022`
UNION ALL
SELECT vendor_id
FROM `myproject.data_lineage.nyc_green_trips_2021`

Unlike dbt, you can reference green_trips view in another view by its name directly.

view: total_trips

SELECT vendor_id, COUNT(*) AS number_of_trips
FROM green_trips
GROUP BY vendor_id

The different here is, we use CTEs to reference views in the main query.

WIP: we are working on automatic import from just NAME. i.e., only need SELECT * from total_trips;

and no need WITH green_trips AS ( {{ ref("green_trips") }} ) to be referenced in CTE.

nyc_query.sql

WITH green_trips AS (
    {{ ref("green_trips") }}
),
total_trips AS (
    {{ ref("total_trips") }}
)
SELECT * from total_trips;

---

Open params menu on the right, you'll see referenced views in this query. This concept works like importing code snippets from templates.

Hit compile to see compiled code.

Open Diagram menu to see the lineage.