[Feature] Make sql_header configuration available on tests

[epapineau]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

The sql_header model configuration is a convenient way to define a temporary BigQuery UDF, as described in the dbt docs. It would be helpful to have this configuration available on tests as well for the same purposes.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[dbeatty10]

@epapineau ! So good to see you 😄

Are you thinking primarily data tests or unit tests or both? If data tests is included, singular or generic or both?

Could you share a simplified code example in which you'd want to define a temporary BigQuery UDF and a test that would use it?

[epapineau]

Are you thinking primarily data tests or unit tests or both? If data tests is included, singular or generic or both?

Data tests! And probably both singular and generic - can't imagine having the ability in one and not the other

[epapineau]

Could you share a simplified code example in which you'd want to define a temporary BigQuery UDF and a test that would use it?

I've been working with the temporary UDF recommended here this week. I'm creating a temporary UDF using sql_headers in my model, but I'd like to be able to test the key values in a singular data test to confirm they can successfully cast to integers. However, I'm not able to create the temporary UDF in the test like I am in the model. LMK if you want something more explicit here 👍

[dbeatty10]

LMK if you want something more explicit here 👍

Something more explicit would be great! It would serve as a two-fer-one:

1. It will help us more tangibly consider this proposal 🧠
2. If we end up adopting this proposal, then we can drop a variation of the explicit example into pytest to make sure it works as expected 💪

[epapineau]

Okay, here's an example using the UDF from the article linked above to get keys and values from JSON. In this example, I want the test to fail where test is null. I currently can't define a temp UDF in a test w/o sql_header config on a test node.

{%- call set_sql_header(config) -%}
    CREATE TEMP FUNCTION EXTRACT_KV_PAIRS(json_str STRING)
    RETURNS ARRAY<STRUCT<key STRING, value STRING>>
    LANGUAGE js AS """
    try{ 
        const json_dict = JSON.parse(json_str); 
        const all_kv = Object.entries(json_dict).map(
            (r)=>Object.fromEntries([["key", r[0]],["value",  
                                    JSON.stringify(r[1])]]));
        return all_kv;
    } catch(e) { return [{"key": "error","value": e}];}
    """;
{%- endcall -%}

-- sample JSON requiring a UDF to extract keys
WITH
sample_json AS(
  SELECT '{"1":{"status":"success"},"2":{"status":"success"},"NaN":{"status":"unsuccess"}}' AS payload
),
-- use UDF
extracted_pairs as (
  SELECT EXTRACT_KV_PAIRS(payload) as kv_list FROM sample_json
)
SELECT
    items.key as id,
    SAFE_CAST(items.key AS INTEGER) as test  -- Test fails for rows where test is null
FROM extracted_pairs
CROSS JOIN UNNEST(kv_list) as items

[dbeatty10]

Thanks for that example @epapineau !

I tried out your example, and surprising it works if (and only if) store_failures_as="table" (or store_failures=true).

Regardless, it makes sense for us to do the following:

• Enable sql_header to apply to both singular and generic data tests regardless if they are storing failures or not.

I did a proof-of-concept in these draft PRs:

• Add sql_header to data tests dbt-adapters#145
• Enable sql_header within generic data tests #9854

Labeling this as help_wanted for someone in the community to bring across the finish line (adding test cases, etc.).

[jx2lee]

@dbeatty10 hi! Could i take your draft PRs?
I've worked on good_first_issue issue in dbt-core, but I didn't contribute to dbt-adapter.

• I'm wondering if I could do it,
• and if so, is it okay to �do on ref branch of draft-PR (e.g. dbeatty/9775-sql-header)?

[github-christophe-oudar]

@dbeatty10 sql_header is not working on unit tests too, maybe we could take care of that one as well?
Would dbt-labs/dbt-adapters#145 changes also affect them? I would need to rework my local setup to test if you don't know 👀