[Feature] Make `sql_header` configuration available on tests about dbt-core HOT 8 OPEN

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:

• dbt-labs/dbt-adapters#145
• #9854

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

from dbt-core.

@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?

from dbt-core.

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 💪

from dbt-core.

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

from dbt-core.

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 👍

from dbt-core.

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

from dbt-core.

@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)?

from dbt-core.

@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 👀

from dbt-core.