# Macro isn't working -\> unknown function

**URL:** <https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938>\
**Category:** Help\
**Tags:** jinja, snowflake, dbt-cloud, macros\
**Created:** [February 7, 2024, 10:50am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938 "2024-02-07T10:50:29Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![grizbull](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/grizbull/32/4621_2.png) [@grizbull](https://discourse.getdbt.com/u/grizbull)\
**Post date:** [February 7, 2024, 10:50am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938/1 "2024-02-07T10:50:29Z")

</div>

Hello everyone, I’d love to get some help. I reckon, I just have some gaps in understanding how the jinja works under the hood. many thanks in advance!

## The problem I’m having

I’m tring to create a macro in order to generate all columns for staging tables by using a source table where $1 is a variant column (parquet file). however when i run the macro within a model it gives the following exception: `SQL compilation error: Unknown function INFER_TABLE_SCHEMA`

## The context of why I’m trying to do this

I’d like to create an automated way to generate all columns with its matching types from a parquet file. I’m using snowflake as a dwh, and the source tables are as following:

- $1: content
- $2: filename
- $3: date

## What I’ve already tried

I’m using the infer\_schema function to get all the rows i need. Afterwards, i’m trying to get all the rows as one string, so when i call the macro within a model, it spits out all rows as one string. (please see script below)  
_please don’t mind the log and print, i just wanna know what is the difference between the two_

## Some example code or error messages

##### macro (str, str, int):

```auto
{% macro infer_table_schema(X__file_stage_path, file_format_path, max_file_count) %}
    
    {{ print("Running infer_table_schema: stage_path: " ~ arg1 ~ ", file_format_path " ~ arg2) }}
    {{ log("Running infer_table_schema: stage_path: " ~ arg1 ~ ", file_format_path " ~ arg2, info=True) }}

    {% set query = """
                      select expression || ' AS ' || column_name
                      from table(infer_schema(
                                  LOCATION => \'""" ~ X__file_stage_path ~ """\'
                                , FILE_FORMAT => \'""" ~ file_format_path ~ """\'
                                , IGNORE_CASE => TRUE
                                , MAX_FILE_COUNT => """ ~ max_file_count ~ """
                              )
                            )

                  """
      %}

    {{ print("Query: " ~ query) }}

    {% set res_schema = run_query(query) %}
    
    {# Fetching the definition for each column in the parquet file #}
    {% set schema = res_schema.columns[0].values() %}

    {% set schema_str = schema|join(", ") %}
    
    {{ return(schema_str) }}

{% endmacro %}

```

##### model:

```auto
select

infer_table_schema('@<X_file_stage_path>', '<file_format_path>', 5)
  
from {{ source('X', 'TABLE_X') }}

```

Any help is highly appreciated!

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [February 7, 2024, 10:54am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938/2 "2024-02-07T10:54:03Z")

</div>

Macros have to be called inside {{ }}

So

```auto
select

{{ infer_table_schema('@<X_file_stage_path>', '<file_format_path>', 5) }}
  
from {{ source('X', 'TABLE_X') }}

```

And just another point, if you want the result of the macro to be written as part of the sql, you should not use return, you should make the macro like

```auto
...
    {% set schema_str = schema|join(", ") %}
    
    {{ schema_str }}

{% endmacro %}

```

---

<div class="post-metadata">

**Author:** ![grizbull](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/grizbull/32/4621_2.png) [@grizbull](https://discourse.getdbt.com/u/grizbull)\
**Post date:** [February 7, 2024, 10:59am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938/3 "2024-02-07T10:59:49Z")

</div>

Hi Bruno,  
thanks for the reply!

Grrrr, can’t believe i missed the `{{ }}`. however now, i’m getting another issue:

also, many thanks for the return remark. noted!

```auto
Compilation Error in model STG_TABLE_X (models/STG/SAP/STG_TABLE_X.sql)
  'None' has no attribute 'table'

```

any idea?

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [February 7, 2024, 11:19am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938/4 "2024-02-07T11:19:42Z")

</div>

Yeah, give this page a read

> **[About execute variable | dbt Developer Hub](https://docs.getdbt.com/reference/dbt-jinja-functions/execute)**
>
> Use \`execute\` to return True when dbt is in 'execute' mode.

It is exactly about your issue

---

<div class="post-metadata">

**Author:** ![grizbull](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/grizbull/32/4621_2.png) [@grizbull](https://discourse.getdbt.com/u/grizbull)\
**Post date:** [February 7, 2024, 11:22am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938/5 "2024-02-07T11:22:05Z")

</div>

will do, thanks again for the quick replies!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [February 14, 2024, 11:22am UTC](https://discourse.getdbt.com/t/macro-isnt-working-unknown-function/11938/6 "2024-02-14T11:22:44Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.