[CT-1749] [Feature] Let Snowpark telemetry know that dbt is creating the sproc

[dataders]

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-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Snowflake is requesting that the below line of code be added to the body of user-created python functions used to create python UDFs on behalf of users behind the scenes and have PartnerName_ToolName="dbt"

sys._xoptions['snowflake_partner_attribution'].append("PartnerName_ToolName")

Below is where our template of sproc creation is implemented. It seems the implementation would be to somehow modify the function definition in compiled_code to also include the sys._xoptions call?

dbt-snowflake/dbt/adapters/snowflake/impl.py

Lines 199 to 210 in e18c501

	common_procedure_code = f"""
	RETURNS STRING
	LANGUAGE PYTHON
	RUNTIME_VERSION = '{python_version}'
	PACKAGES = ('{packages}')
	{imports}
	HANDLER = 'main'
	EXECUTE AS CALLER
	AS
	$$
	{compiled_code}
	$$"""

This is an example provide by the Snowflake team

create function python_udf_usage_tracking(c1 int, c2 int)
returns double
language python
runtime_version='3.8'
handler = 'PartnerTrackingTest'
as $$$$
import sys
 
def sum(a,b):
    sum=a+b
    sys._xoptions['snowflake_partner_attribution'].append("PartnerName_ToolName")
    return sum
$$$$;

Describe alternatives you've considered

We do nothing

This is one idea

We add a session parameter to connection creation?

Not sure if this would be helpful for the Snowpark team, that wants to track consumption...

https://github.com/dbt-labs/dbt-snowflake/blob/main/dbt/adapters/snowflake/connections.py#L284-L285

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[ChenyuLInx]

Do we have to call sys._xoptions inside user's function? can we just do it at the very end or very beginning right after import sys?

[lostmygithubaccount]

agreed we should move it if possible to below the import instead of in user code

I don't see any reason we couldn't/shouldn't do this, would definitely check w/ @jtcohen6 to be sure

[sfc-gh-ripu]

@dataders can you please expand on the alternate solution linked? Are you suggesting dbt python models will be able to add a specific query tag, say "snowpark_python" ?

[jtcohen6]

@sfc-gh-ripu If I understand right, we're awaiting confirmation from folks on your side that it's possible to do something similar to sys._xoptions['snowflake_partner_attribution'] (which works in UDFs) in stored procedures as well, since that's how we're currently creating + calling dbt Python models on Snowpark.

@dataders My hesitation with the query_tag approach is, that's something we've opened up fully to user-space configuration. Users can add/remove whatever query_tag they want, and it's fully visible within their own query histories. Whereas application or snowflake_partner_attribution are just for use by Snowflake + partners.

[sfc-gh-ripu]

@dataders I confirmed with Snowpark team, the sys._xoptions['snowflake_partner_attribution'].append("PartnerName_ToolName")
statement can be outside user function, and will work directly from the body of a sproc as well, right after import sys statement.

[dataders]

@sfc-gh-ripu amazing! just opened up #383 as a draft. @ChenyuLInx @colin-rogers-dbt wanna take a look?