DBT ingesting the same model name in set_sql_header

Question

I have a model test1_scd2.sql that runs a stored procedure in set_sql_header. The adapter is bigquery and I do not own the stored procedure, I just need to call it. the SP has been battle tested and works fine. I am currently migrating some of our pipelines to DBT.

 -- depends on: {{ ref('ingest_table_temp') }}
 -- depends on: {{ ref('test1_target_table') }}
 {{ config(hours_to_expiration = 1)
 }}
 

{% call set_sql_header(config) %}
call `{{target.schema}}`.scd2( 
        "{{ ref('ingest_table_temp') }}",
        "{{ ref('test1_target_table') }}",
        'is_latest_flag',
        ['submission_id'],
        ['last_reported_dt'])
{%- endcall %}


select * from {{ ref('ingest_table_temp') }} limit 1

but in the compiled version you can see that {{ ref('ingest_table_temp') }} and {{ ref('test1_target_table') }} has been replaced by the model's name

call `sp`.scd2( 
        "`proj`.`datasn_sp`.`test1_scd2`",
        "`proj`.`datasn_sp`.`test1_scd2`",
        'is_latest_flag',
        ['submission_id'],
        ['last_reported_dt'])

  create or replace table `proj`.`sp`.`test1_scd2`
  
  
  OPTIONS(
      expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 hour)
    )
  as (
   

select * from `proj`.`sp`.`ingest_table_temp` limit 1
  );

-- ingest_table_temp.sql
with ingest_table_temp
  AS (
    SELECT             DATETIME(2020, 01, 15, 00, 00, 00) ingest_ts, DATE(2020,1,15) last_reported_dt, "001" submission_id, "XXX" journal, 100 views
    UNION ALL SELECT   DATETIME(2020, 01, 15, 00, 00, 00),           DATE(2020,1,15),                  "002",            "XXX",         150
    UNION ALL SELECT   DATETIME(2020, 01, 15, 00, 00, 00),           DATE(2020,1,15),                  "003",            "XXX",         500
    UNION ALL SELECT   DATETIME(2020, 01, 15, 00, 00, 00),           DATE(2020,1,15),                  "004",            "XXX",         200
  ) select * from ingest_table_temp

-- test1_target_table.sql

with test1_target_table as (
    select true as is_latest_flag, 
        * from {{ ref('ingest_table_temp')}} 
        where 1=2
        ) select * from test1_target_table

Error I get in log is:

Syntax error: Expected end of input but got keyword CREATE at [10:3]

I have done tons of research and asked in dbt slack channel but no luck.

any ideas?

Answer 1

I ran into the same issue, and you can declare the referenced table name before calling the set_sql_header macro.

{% set target_relation = api.Relation.create(database=database, schema=schema, identifier='table_referenced') -%}

{% call set_sql_header(config) %}
 select * from {{ target_relation }}
{%- endcall %}

{{ config(materialized = 'incremental') }}

select * from source('sources', 'table') 
where date = latest_date

Also more issues related to this one on the following thread here