Do not merge lines with more than N segments

[tconbeer]

We aggressively merge lines if they fit, even if those lines are very complex:

select a, b, c, case when a > b then c else a end as d from e where a > 1 and b > 2

I'm not sure this is a good idea. The example above has 3 top-level segments (select, from, where) and the select segment has 4 segments. Maybe we should check the number of segments (either after we split into segments or before merging them) and skip merging across segments if there are more than N (where N is probably 3-4).

[dave-connors-3]

I think segments is key, but I think the complexity of each segment matters too

select * from table where x is not null

feels like a query I'd be ok with one-lining, and it also has 3 top-level segments. the fact that your example the select statement has 4 components (and case when itself is an extra complex type of component!) and the where clause has 2 components pushes me towards multilining

[nbsmo4]

Reviving this thread: Being contrarian on this 8)
I would like the option to not merge too many stuff, as there is some benefits readability-wise in having things segmented and consistent formatted when working with larger queries:

For CTEs:
After sqlfmt:

with
    -- import datasets
    orders as (
        select * 
        from {{ source("db", "orders") }}
        where deleted = 'N' and {{ var("from_date") }} <= order_date
    ),
    blocked_orders as (select * from {{ source("db", "blocked_orders") }}),
    instrument as (
        select *
        from {{ source("db", "instrument") }}
        where current_row = 'Y' and deleted = 'N'
    )
...

Preferred:

with
    -- import datasets
    orders as (
        select * 
        from {{ source("db", "orders") }}
        where deleted = 'N' 
            and {{ var("from_date") }} <= order_date   -- indent for 2:+ filter
    ),
    blocked_orders as (                        -- same structure as the rest of other CTEs
        select * 
        from {{ source("db", "blocked_orders") }}
    ),
    instrument as (
        select *
        from {{ source("db", "instrument") }}
        where current_row = 'Y' 
            and deleted = 'N'
    )
...

For joins:
After sqlfmt:

...
    consolidate_data as (
        select *
        from orders o
        join blocked_orders bo on bo.blocked_orders_ek = o.blocked_orders_ekr
        join
            instrument i
            on o.instrument_ekr = i.instrument_ek
            and i.start_date <= o.order_date
            and o.order_date < i.end_date

Preferred:

...
    consolidate_data as (
        select *
        from orders o
        join blocked_orders bo 
            on bo.blocked_orders_ek = o.blocked_orders_ekr    -- join keys on next line 
        join instrument i                                     -- always have joining table next to "join" code
            on o.instrument_ekr = i.instrument_ek
            and i.start_date <= o.order_date
            and o.order_date < i.end_date

Tech stack: Snowflake and dbt Cloud

Would be great to discuss this further on and finding a balance in the segmenting approach.

[tconbeer]

I think I would write this rule as "don't merge segments if other segments at the same depth cannot be merged". That has pretty broad implications, though -- I think you want a more specific subset of that -- can you try to generalize the rule you're looking for?

[nbsmo4]

Good question. In my simple mind I just thought that all CTEs should be segmented in the precise same matter for the maximized readability, as I prefer simplicity. Consequently, I would propose merging CTEs is not preferable, as it easily becomes cluttered when merging.

At the most extreme end of this approach this would mean that every line should only have one purpose hence segment (or even splitting segment based on "or"/and".

Trying to generalize this I would actually propose: if more than 3-4 CTEs, don't merge segments at all.