Feature Request: support for sql headers so we can test temp user-defined functions

[darist]

Hey there!

Just ran into this situation today: we are using sql_header to define a temp user-defined function in BigQuery so we can then call it from the SQL code in the model.

It goes something like this:

my_model.sql

{{ config(sql_header=create_my_udf()) }}
SELECT *, my_udf(1, 2, 3) as udf_result
FROM other_model

The create_my_udf macro is something like this:

{% macro create_my_udf() %}
CREATE TEMP FUNCTION my_udf(x INT, y INT, z INT)
RETURNS INT
LANGUAGE js
AS """
 ... js code here ...
"""
{% endmacro %}

I want to be able to test my_model.sql but currently the query that dbt_unit_testing builds does not include the sql_header, so the UDF is never defined and we get a database error.

I would like to have dbt_unit_testing include the sql_header in the query (queries?) that it executes during the test.

Some ideas:

• Look at the config of the model under test and if it has a sql_header, grab it and put it at the top of the query generated by dbt_unit_testing
• Expose new sql_header option as one of the options that some of the macros take.
• Provide a new macro, eg dbt_unit_testing.set_sql_header that we can call during the test, eg:

{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test('my_model', '...') %}

  {% call dbt_unit_testing.set_sql_header() %}
    {{ create_my_udf() }}
  {% endcall %}
  
  {% call dbt_unit_testing.mock_ref ('other_model') %}
     ...
  {% endcall %}
  
  {% call dbt_unit_testing.expect() %}
    ...
  {% endcall %}
{% endcall %}