Why do engineers break each metric into a separate CTE?
I have a strong BI background with a lot of experience in writing SQL for analytics, but much less experience in writing SQL for data engineering. Whenever I get involved in the engineering team's code, it seems like everything is broken out into a series of CTEs for every individual calculation and transformation. As far as I know this doesn't impact the efficiency of the query, so is it just a convention for readability or is there something else going on here?
If it is just a standard convention, where do people learn these conventions? Are there courses or books that would break down best practice readability conventions for me?
As an example, why would the transformation look like this:
with product_details as (
select
product_id,
date,
sum(sales)
as total_sales,
sum(units_sold)
as total_units,
from
sales_details
group by 1, 2
),
add_price as (
select
*,
safe_divide(total_sales,total_units)
as avg_sales_price
from
product_details
),
select
product_id,
date,
total_sales,
total_units,
avg_sales_price,
from
add_price
where
total_units > 0
;
Rather than the more compact
select
product_id,
date,
sum(sales)
as total_sales,
sum(units_sold)
as total_units,
safe_divide(sum(sales),sum(units_sold))
as avg_sales_price,
from
sales_details
group by 1, 2
having
sum(units_sold) > 0
;
Thanks!