dbt python model - EXTERNAL_ACCESS_INTEGRATIONS and SECRETS

The problem I’m having

I would like to create a python model (which turns into stored procedure in Snowflake) and in that model access external integration and uses secrets.

The context of why I’m trying to do this

Trying to setup a python model what will use external data. Following External Access: Securely Connect to External Endpoints from Snowpark

What I’ve already tried

This is the definition of procedure - and it works:

CREATE OR REPLACE PROCEDURE test_salesforce(table_name STRING)
returns STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.9
HANDLER = 'handler'
EXTERNAL_ACCESS_INTEGRATIONS = (SALESFORCE_TEST_INTEGRATION)
PACKAGES = ('snowflake-snowpark-python','simple-salesforce')
SECRETS = ('user_name'=SALESFORCE_TEST_USER_NAME, 'password'=SALESFORCE_TEST_PASSWORD)
AS
$$
import _snowflake
import logging
import sys
from snowflake.snowpark.functions import listagg
from simple_salesforce import SalesforceLogin
from simple_salesforce import Salesforce

def handler(session,table_name)->str:
    logging.basicConfig(level=logging.DEBUG)
    logger = logging.getLogger(__name__)
    df_table = session.table(table_name)
    user_name = _snowflake.get_generic_secret_string('user_name')
    password = _snowflake.get_generic_secret_string('password')
    ...

and this is the model I tried to use:

def model(dbt, session):
    dbt.config(
        packages=['simple-salesforce'],
        external_access_integrations=['SALESFORCE_TEST_INTEGRATION'],
        secrets={'user_name': 'UTIL.SALESFORCE_TEST_USER_NAME', 'password': 'UTIL.SALESFORCE_TEST_PASSWORD'}
    )
    import logging
    import sys
    from snowflake.snowpark.functions import listagg
    from simple_salesforce import SalesforceLogin
    from simple_salesforce import Salesforce
    import _snowflake
    import pandas as pd
    logging.basicConfig(level=logging.DEBUG)
   ...

But I cannot pass the secrets and it seems to me that also the external access integration is not passed as an argument to the procedure.

1 Like