Previewing the code generated inside of a macro

[adilrehman]

The problem I’m having

I am trying to include an insert statement inside a macro. But I am facing error so please guide me through this.

Some example code or error messages

{% macro centralize_test_failures(results) %}

  {%- set test_results = [] -%}
  {%- for result in results -%}
    {%- if result.node.resource_type == 'test' and result.status != 'skipped' and (
          result.node.config.get('store_failures') or flags.STORE_FAILURES
      )
    -%}
      {%- do test_results.append(result) -%}
    {%- endif -%}
  {%- endfor -%}
  
  {%- set central_tbl -%} {{ target.schema }}.test_failure_central {%- endset -%}
  {%- set monitoring_tbl -%} {{ target.schema }}.monitoring_table {%- endset -%}
  
  {{ log("Storing test failures in " + central_tbl, info = true) if execute }}

  create or replace table {{ central_tbl }} as (
  
  {% for result in test_results %}

    select distinct

        '{{result.node.unique_id}}'                     as unique_id,
        '{{ result.node.name }}'                        as test_performed,
        '{{result.node.resource_type}}'                 as resource_type,
        '{{ result.status }}'                           as status,
        '{{ target.schema }}'                           as schema,
        'test_failure_central'                          as target_table,
         object_construct_keep_null(*)                  as failed_records,
        '{{result.execution_time}}'                     as execution_time_sec,
         current_timestamp                              as date_time
      
    from {{ result.node.relation_name }}
    
    {{ "union all" if not loop.last }}
  
  {% endfor %}

  )

    insert into {{monitoring_tbl}}
    as 
    select * from {{ central_tbl }})

{% endmacro %}

Encountered an error:
Database Error
001003 (42000): SQL compilation error:
syntax error line 115 at position 3 unexpected ‘insert’.

[joellabes]

Have you looked at the compiled code in your target directory?

[adilrehman]

Hi,
The code was not compiled so that’s why I was not sure what is the issue. But now I have found the issue and resolved the error after putting a semi colon before insert.