JINJA/dbt macro unneeded whitespace?-CodePudding

I am trying to develop a dbt macro using jinja-sql to create a template for my base hub models. How do I get my hardcoded fields directly underneath my generated code? I have fooled around with all of the -'s trying to limit the whitespace to no avail.

 {{hub_table}} AS (
        SELECT
            {%- if not leading_commas -%}
            {%- for column in column_names %}
            {% if column not in hub_default_columns %}{{column | lower~","}}{%- endif -%}
            {%- endfor -%}
            {%- endif -%}
            dvloaddatetime                                      AS dvLoadDateTime,
            dvlastseendate                                      AS dvLastSeenDate,
            dvsourceid                                          AS dvSourceID
    
        FROM source

    )

    SELECT * 

    FROM {{hub_table}}

{% endset %}

{% if execute %}

{{ log(hub_model_sql, info=True) }}
{% do return(hub_model_sql) %}

{% endif %}
{% endmacro %}

My output that I receive is shown below.

WITH source AS (
    SELECT *

    FROM {{ source('-----', 'h_CLIENT') }}

    ),

    h_CLIENT AS (
        SELECT
            h_client_hashkey,
            clientsid,


            dvloaddatetime                                      AS dvLoadDateTime,
            dvlastseendate                                      AS dvLastSeenDate,
            dvsourceid                                          AS dvSourceID

        FROM source

    )

    SELECT *

    FROM h_CLIENT

The output I am wanting/hoping to receive is shown below.

WITH source AS (
    SELECT *

    FROM {{ source('-----', 'h_CLIENT') }}

    ),

    h_CLIENT AS (
        SELECT
            h_client_hashkey,
            clientsid,
            dvloaddatetime                                      AS dvLoadDateTime,
            dvlastseendate                                      AS dvLastSeenDate,
            dvsourceid                                          AS dvSourceID

        FROM source

    )

    SELECT *

    FROM h_CLIENT

CodePudding user response：

Whitespace control just impacts the space/newlines between that tag and the tags immediately before or after it. With loops, you need to imagine the contents of the loops repeated; for if statements, the whitespace control of the if block will be applied, even if the contents are empty. If two tags are either ends of whitespace, either tag's - will remove that whitespace.

In your case, you're getting extra newlines when if column not in hub_default_columns evaluates to false. Since there's no whitespace control on the beginning of that tag, you're getting a newline for each loop, even if the contents of the if block are empty.

To fix this, you want to allow whitespace inside the if block, but have the block itself remove surrounding whitespace. Similarly, you need to tweak the first if statement after select to make sure there is a newline after select, even when leading_commas is true.

This:

{%- set column_names = ["a", "b", "c", "d"]-%}
{%- set hub_default_columns = ["c", "d"]-%}
{%- set leading_commas = false %}
        SELECT
            {% if not leading_commas -%}
            {%- for column in column_names -%}
            {%- if column not in hub_default_columns -%}
            {{column | lower~","}}
            {% endif -%}
            {%- endfor -%}
            {%- endif -%}
            dvloaddatetime                                      AS dvLoadDateTime,
            dvlastseendate                                      AS dvLastSeenDate,
            dvsourceid                                          AS dvSourceID

Compiles to:


        SELECT
            a,
            b,
            dvloaddatetime                                      AS dvLoadDateTime,
            dvlastseendate                                      AS dvLastSeenDate,
            dvsourceid                                          AS dvSourceID