CTEs in dbt

CTEs in dbt #

What are CTEs? #

Common Table Expresssions (CTEs). Using them is a best practice.

It makes the code a whole lot cleaner, AND it doesn’t add cost or lag to the operation as they operate as pass-throughs.

WITH <temporary table name> AS (
    SELECT ...
    FROM ...
    WHERE ...
    blah blah
) 
SELECT ...,
FROM <temporary table name>
WHERE ...

There can be many CTEs ^.

Structure #

The general *.sql design should follow:

  1. CTEs to import
  2. CTEs to transform
  3. a final CTE
  4. End with select * from final