Preventing run_query Execution During Compilation

Help
,
1

Dynamically generate view query

I’m trying to dynamically generate the columns of a dbt view, let’s call it DYNAMIC_VIEW, by querying another table, VIEW_FIELDS.

The goal is to have a macro, get_fields, that gets invoked within a model like this:

    SELECT 
		{{get_fields('source_table'))}}
        FROM {{ref('source_table')}}

What I’ve already tried

I’ve written the following macro, and while I’ve made several attempts (even using get_column_values), I’m running into an issue: the run_query still executes every time the code is compiled.

{%- macro get_fields(relation) -%}

{%- set query -%}
        SELECT DISTINCT FIELD_NAME
            FROM {{ref('VIEW_FIELDS')}}
            WHERE UPPER(TABLE_NAME) = '{{relation | upper}}'
                --AND dbt_valid_to IS NULL
{%- endset -%}



{%- if execute -%}
    {%- set results = run_query(query) -%}
    {#- Return the first column -#}
    {%- set field_names = results.columns[0].values() -%}

        {% for field_name in field_names -%}
        {{field_name | upper }}{%- if not loop.last -%},
        {% endif -%}
        {% endfor -%}
{%- endif -%}


{%- endmacro -%}

Since I have many views that need this dynamic column generation, I really want to avoid running a query for each view every time the dbt project compiles.

Could anyone offer some guidance or suggest a more efficient approach? Thanks in advance for your help!

2

Give this a try:

  {%- if not var('_view_fields_cache', false) -%}
    {%- set query -%}
      SELECT TABLE_NAME, FIELD_NAME
      FROM 
      ORDER BY TABLE_NAME, FIELD_NAME
    {%- endset -%}
    
    {%- if execute -%}
      {%- set results = run_query(query) -%}
      {%- set field_dict = {} -%}
      
      {%- for row in results -%}
        {%- set table_name = row[0] | upper -%}
        {%- set field_name = row[1] -%}
        {%- if table_name not in field_dict -%}
          {%- do field_dict.update({table_name: []}) -%}
        {%- endif -%}
        {%- do field_dict[table_name].append(field_name) -%}
      {%- endfor -%}
      
      {%- do var.update({'_view_fields_cache': field_dict}) -%}
    {%- endif -%}
  {%- endif -%}
  
  {{ return(var('_view_fields_cache', {})) }}
{%- endmacro -%}

{%- macro get_fields(relation) -%}
  {%- set all_fields = get_all_view_fields() -%}
  {%- set field_names = all_fields.get(relation | upper, []) -%}
  
  {% for field_name in field_names -%}
    (SELECT value.string_value FROM UNNEST(event_params) AS p 
     WHERE UPPER(p.key) = UPPER('')) AS {{field_name | upper }}
    {%- if not loop.last -%},{%- endif -%}
  {% endfor -%}
{%- endmacro -%}```
This should enable you to store all of your table-field mapping results in the dbt variable cache one time and then the subsequent macro calls can hit the variable instead of re-running the lookup. You should be able to ignore worrying about refreshes as the dbt variable caching _should_ handle the lifecycle for you and should keep that variable populated _per_ command execution (i.e. dbt run, dbt build, etc.)

This is untested and was vibe-coded so you'll need to test to make sure it works how I think it does :)

<sub>Note: `@Abigail Green (CHG Healthcare)` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1752854236931129?thread_ts=1752829955.008409&cid=CBSQTAPLG). It might not have transferred perfectly.</sub>