Is it possible to add CTE Ref Snippet in Cloud?

Help
1

Can a CTE ref be added as a snippet in the cloud IDE? I find myself copying the code since I don’t want to type it all out.

sales as (select * from {{ ref(“stg_sales”) }})

1 Like
2

In the past I’ve used a macro to reduce the boilerplate for this:

{% macro import_ctes(ctes_and_aliases) -%}

    with
    {% for cte_ref in ctes_and_aliases %} 
        {{- cte_ref[1] if cte_ref[1] else cte_ref[0] -}} 
        {{- ' ' }} as (
            select * from {{ ref(cte_ref[0]) }}
        )
        {%- if not loop.last -%}
        ,
        {% endif -%}
    {%- endfor -%}

{%- endmacro %}

Which can be used like this:

{{ 
    import_ctes([
        ['first_model', 'orgs'],
        ['second_model']
    ])
}},

where second element of each list is the optional alias. (It’s probably better to use a dictionary than lists, but doing it with lists makes it quicker/easier to type, which is the whole point).

In action it looks like this:

image
image660×569 29.3 KB

3

Is that what __ref is? A Macro? I was seeking something similar to __ref which I just figured out.

4

Oh I get you! you want to type a snippet like __import_cte that expands to

[placeholder] as (select * from {{ ref(“[placeholder]”) }})

That should be achievable :+1:

What would you call it?

5

Yea, that’s correct. That would be awesome. I like “__cte”

1 Like