Invalidate Omni's cache with dbt or SQL

Modeling
1

We’re going to create a UDF in Snowflake that you can call from dbt or anywhere else. <something> means you’ll need to replace that with the relevant value for your environment before running the SQL.

Caution: Omni API keys have a LOT of power beyond just invalidating the cache. Snowflake stores them securely and this code will only use the API key to do cache invalidation, but make sure you don’t leave it hanging around in your IDE or anywhere else.

CREATE OR REPLACE NETWORK RULE OMNI_NETWORK_RULE
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('<yourorg>.omniapp.co');

CREATE OR REPLACE SECRET OMNI_API_KEY
TYPE = GENERIC_STRING
SECRET_STRING='omni_osk_<redacted>';

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION OMNI_INTEGRATION
ALLOWED_NETWORK_RULES = (OMNI_NETWORK_RULE)
ALLOWED_AUTHENTICATION_SECRETS = (OMNI_API_KEY)
ENABLED=TRUE;

CREATE OR REPLACE FUNCTION INVALIDATE_OMNI_CACHE(POLICY_NAME STRING)
returns variant
language python
runtime_version=3.11
handler = 'invalidate_omni_cache'
external_access_integrations=(OMNI_INTEGRATION)
packages = ('requests')
SECRETS = ('cred' = OMNI_API_KEY)
as
$$
import requests
import _snowflake

session = requests.Session()
def invalidate_omni_cache(policy_name):
    api_key = _snowflake.get_generic_secret_string('cred')
    r = requests.post(f"https://<yourorg>.omniapp.co/api/unstable/model/<model_id>/cache_reset/{policy_name}", headers={"Authorization": f"Bearer {api_key}"}, json={})
    return r.json()
$$;

GRANT USAGE ON FUNCTION INVALIDATE_OMNI_CACHE(STRING) TO ROLE <ROLE>;

SELECT INVALIDATE_OMNI_CACHE('model_name');