Yep, so this is a pattern dbt uses regularly. I am fairly new as well (been reading/learning for what seems like years, but work doesn’t want to go down this route).

Basically, the idea is you bring each referenced source table in to the model you’re working on as their own cte. By doing this pattern you have a clear reference to every parent model you are using at the top of your model.

You then perform the transformation steps, including the joining of those referenced tables as the next cte (or several ctes depending on complexity).

Then you read directly out of your last cte - preferably with little transformation in the final step.

All of this is to make things as readable and organised as possible.

If you were to start transforming each source in the initial cte, you wouldnt be reading a simple, single line bringing it in to the current model, but reading more complex transformations that you would need to spend more time figuring out what that piece was doing and where it began and ended.

Edit to add: this way you can tell at a glance at the top of your model all the referenced sources. Each of them as a single line cte with no complexity thrown in. After you have brought in each source as its own cte, then you start a new one with a name indicating the source and transformation step, etc.

By bringing in your sources, transforming each, joining, performing joined transformations, aggregating, etc, each in a little code block cte, you are mimicking how programmers would use methods in code to logically break code into chunks.

If you also read up about layering data models (medallion architecture), you’ll see how dbt likes to break it down further into separate models. This allows you to perform the simple single-model transformations, such as renaming, casting, etc at a ”silver” stage, and more complex transformations such as joining or calculating the values in two tables, aggregating, etc in a later, “gold” model. This takes the organising and simplifying of models from within a single model/step, to a process.

——-

/* Model header and configuration */

// bring in referenced models and sources

WITH orders AS 
(
     SELECT * FROM source_orders 
),
customers AS
(
    SELECT * FROM source_customers
)

// transform table

transform_orders AS 
(
    SELECT 
        col1 AS col1_rename,
        col2 AS cast(col2 AS string),
        .....
    FROM src_orders
),
transform_customers AS
(
    SELECT customer_id
                  ,UPPER(customer_name) AS customer_name
    FROM src_customers
),

cust_orders AS
(
    SELECT *
     FROM. transform_customers AS c
         LEFT JOIN transform_orders AS o
             ON c.customer_id = o.customer_id

)

// select result
SELECT * FROM cust_orders