Running macro that calls macro with CREATE FUNCTION on-run-start

Help
, ,
1

Hi everyone,

I have created a new macro file with the content:

{% macro sub_macro() %}
    CREATE OR REPLACE FUNCTION sub_macro_function(param STRING)
    RETURNS TABLE(col1 VARCHAR)
    AS
    $$
        SELECT param as col1
    $$;
{% endmacro %}

{% macro main_macro() %}
    {{ sub_macro() }};
{% endmacro %}

Then I added:

on-run-start:
  - "{{ main_macro() }}"

To dbt_project.yml

And I get the following error:

09:22:53  Running with dbt=1.4.5
09:22:53  Found 104 models, 17 tests, 0 snapshots, 0 analyses, 555 macros, 7 operations, 1 seed file, 47 sources, 0 exposures, 0 metrics
09:22:53  
09:22:56  
09:22:56  Running 1 on-run-start hook
09:22:56  1 of 1 START hook: axonius.on-run-start.0 ...................................... [RUN]
09:22:57  
09:22:57  Finished running  in 0 hours 0 minutes and 4.78 seconds (4.78s).
09:22:58  Encountered an error:
cannot unpack non-iterable NoneType object
09:22:58  Traceback (most recent call last):
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/main.py", line 136, in main
    results, succeeded = handle_and_check(args)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/main.py", line 206, in handle_and_check
    task, res = run_from_args(parsed)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/main.py", line 253, in run_from_args
    results = task.run()
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/task/runnable.py", line 472, in run
    result = self.execute_with_hooks(selected_uids)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/task/runnable.py", line 434, in execute_with_hooks
    self.before_run(adapter, selected_uids)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/task/run.py", line 435, in before_run
    self.safe_run_hooks(adapter, RunHookType.Start, {})
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/task/run.py", line 397, in safe_run_hooks
    self.run_hooks(adapter, hook_type, extra_context)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/task/run.py", line 365, in run_hooks
    response, _ = adapter.execute(sql, auto_begin=False, fetch=False)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/adapters/base/impl.py", line 270, in execute
    return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/adapters/snowflake/connections.py", line 420, in execute
    _, cursor = self.add_query(sql, auto_begin)
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/adapters/snowflake/connections.py", line 455, in add_query
    connection, cursor = self._add_standard_queries(
  File "/Users/shimon.klebanov/PycharmProjects/dbt_analytics_sa/venv/lib/python3.10/site-packages/dbt/adapters/snowflake/connections.py", line 511, in _add_standard_queries
    connection, cursor = self.add_standard_query(query, **kwargs)
TypeError: cannot unpack non-iterable NoneType object

It can be solved by writing the sub_macro directly in the on-run-start
Or by changing sub_macro to not create a function, for example:

{% macro sub_macro() %}
    select 1
{% endmacro %}

Works.

I have a workaround, but I’m wondering if I can use several sub-macros that create a function under the main_macro that will be used in on-run-start and call them.

Thanks,
Shimon

2

The issue was resolved by removing the semicolon from main_macro as follows:

{% macro main_macro() %}
    {{ sub_macro() }}
{% endmacro %}