Calling External APIs from Snowflake: External Access Integrations, Network Rules, and Secrets

Snowflake is a closed network by default. Nothing running inside it — a UDF, a stored procedure, a Snowpark job, a Streamlit app — can open an outbound connection to the public internet unless an account administrator has explicitly allowed it. That default is a feature: it is why data teams can run Python in Snowflake without a security review for every library.

But real pipelines need the outside world. You want to geocode addresses, validate emails, pull an FX rate, call a vendor's REST API, hit an internal microservice, or use a model endpoint Cortex does not host. The supported way to do that is an External Access Integration: a named, grantable object that binds together where you may call and which credentials you may use.

This tutorial builds one end to end, then covers the operational and security details that separate a demo from something you can put in production.

The four objects

An external access setup is always the same four pieces, created in this order:

1. NETWORK RULE — the egress allowlist: hosts and ports this code may reach.
2. SECRET — the credential, stored in Snowflake, never in your code.
3. EXTERNAL ACCESS INTEGRATION — an account-level object that combines one or more network rules with the secrets they are allowed to use. Only ACCOUNTADMIN (or a role with CREATE INTEGRATION) can create it.
4. The function or procedure — which names the integration in EXTERNAL_ACCESS_INTEGRATIONS and the secrets in SECRETS.

The separation matters: the security team owns steps 1–3, the data team owns step 4. A developer can write any Python they like and still cannot reach a host nobody approved.

Step 1 — the network rule

USE ROLE securityadmin;
CREATE SCHEMA IF NOT EXISTS governance.integrations;

CREATE OR REPLACE NETWORK RULE governance.integrations.exchange_api_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.exchangeratesapi.example.com');

Notes that save time later:

• MODE = EGRESS is what makes this an outbound rule. INGRESS rules are for network policies restricting who may log in — a different feature entirely, and a common mix-up.
• TYPE = HOST_PORT with no port defaults to 443. Add :port in the value if you need something else ('internal-svc.acme.corp:8443').
• Wildcard support is limited to a leading subdomain wildcard; do not plan on regex. List the exact hosts. If a vendor's API redirects to a CDN host for downloads, you must list that host too — this is the number-one cause of "it works in Postman, fails in Snowflake".

Step 2 — the secret

CREATE OR REPLACE SECRET governance.integrations.exchange_api_key
  TYPE = GENERIC_STRING
  SECRET_STRING = 'sk_live_replace_me';

Secret types available: GENERIC_STRING (API keys, tokens), PASSWORD (username plus password), OAUTH2 (client credentials, or an authorization-code flow backed by a SECURITY INTEGRATION), and SYMMETRIC_KEY. Use the typed variants when they fit — the OAuth2 type lets Snowflake handle token refresh for you, which is a whole class of bug you never write.

Secret values are readable only through the Python/Java API inside a function that has been granted them. SELECT never returns the value, and it does not appear in query history.

Step 3 — the integration

USE ROLE accountadmin;

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION exchange_api_access
  ALLOWED_NETWORK_RULES = (governance.integrations.exchange_api_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (governance.integrations.exchange_api_key)
  ENABLED = TRUE
  COMMENT = 'FX rates API - owner: data-platform@acme.com';

GRANT USAGE ON INTEGRATION exchange_api_access TO ROLE data_engineer;
GRANT READ ON SECRET governance.integrations.exchange_api_key TO ROLE data_engineer;
GRANT USAGE ON SCHEMA governance.integrations TO ROLE data_engineer;

ENABLED = FALSE is your kill switch: flipping it instantly stops every function that depends on it, without dropping anything. Put that in your incident runbook.

Step 4 — the UDF

USE ROLE data_engineer;

CREATE OR REPLACE FUNCTION analytics.fx_rate(base STRING, quote STRING, on_date DATE)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.11
HANDLER = 'get_rate'
EXTERNAL_ACCESS_INTEGRATIONS = (exchange_api_access)
SECRETS = ('cred' = governance.integrations.exchange_api_key)
PACKAGES = ('requests')
AS
$$
import _snowflake
import requests

session = requests.Session()

def get_rate(base, quote, on_date):
    key = _snowflake.get_generic_secret_string('cred')
    resp = session.get(
        f"https://api.exchangeratesapi.example.com/{on_date}",
        params={"base": base, "symbols": quote},
        headers={"Authorization": f"Bearer {key}"},
        timeout=10,
    )
    resp.raise_for_status()
    return resp.json()["rates"][quote]
$$;

SELECT analytics.fx_rate('USD', 'EUR', '2026-01-15');

_snowflake.get_generic_secret_string('cred') resolves the alias declared in SECRETS. For other secret types use get_username_password('cred') (returns an object with .username and .password) or get_oauth_access_token('cred').

Creating the Session at module scope, outside the handler, matters: the module is imported once per Python process and the handler is called per row, so a module-level session reuses the TCP/TLS connection instead of renegotiating for every row.

Making it not melt under a million rows

A scalar UDF that makes one HTTP call per row is fine for a lookup table and a disaster for a fact table. Three fixes, in order of preference:

1. Do not call per row — call per distinct value. Materialize a small dimension and join to it.

CREATE OR REPLACE TABLE analytics.fx_rates_daily AS
SELECT d.rate_date, c.quote_ccy,
       analytics.fx_rate('USD', c.quote_ccy, d.rate_date) AS rate
FROM analytics.date_spine d
CROSS JOIN analytics.currencies c
WHERE d.rate_date BETWEEN '2026-01-01' AND CURRENT_DATE();

Three currencies over a year is about a thousand calls, not fifty million. Then join fx_rates_daily to the fact table with ordinary SQL.

2. Use a vectorized UDF so each invocation receives a pandas batch and you can issue concurrent requests:

import _snowflake, pandas as pd, requests
from concurrent.futures import ThreadPoolExecutor

def validate(df: pd.DataFrame) -> pd.Series:
    key = _snowflake.get_generic_secret_string('cred')
    s = requests.Session()

    def one(addr):
        r = s.get("https://api.vendor.example.com/validate",
                  params={"email": addr}, headers={"X-Key": key}, timeout=5)
        return r.json().get("valid") if r.ok else None

    with ThreadPoolExecutor(max_workers=8) as pool:
        return pd.Series(list(pool.map(one, df[0])))

validate._sf_vectorized_input = pd.DataFrame
validate._sf_max_batch_size = 500

Cap the batch size so a batch comfortably fits inside the UDF timeout, and keep worker counts modest — you are also rate-limiting yourself against the vendor, and a 429 storm is slower than politeness.

3. Use a stored procedure for orchestration, not a UDF, when the shape is "fetch a payload, load a table":

CREATE OR REPLACE PROCEDURE raw.load_vendor_orders()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.11
PACKAGES = ('snowflake-snowpark-python','requests')
HANDLER = 'run'
EXTERNAL_ACCESS_INTEGRATIONS = (vendor_api_access)
SECRETS = ('cred' = governance.integrations.vendor_token)
AS
$$
import _snowflake, requests

def run(session):
    token = _snowflake.get_oauth_access_token('cred')
    rows, page = [], 1
    while True:
        r = requests.get("https://api.vendor.example.com/orders",
                         params={"page": page},
                         headers={"Authorization": f"Bearer {token}"},
                         timeout=30)
        r.raise_for_status()
        batch = r.json()["data"]
        if not batch:
            break
        rows.extend(batch)
        page += 1
    session.create_dataframe([[r] for r in rows], schema=["payload"]) \
           .write.mode("append").save_as_table("raw.vendor_orders_json")
    return f"loaded {len(rows)} rows"
$$;

CREATE OR REPLACE TASK raw.load_vendor_orders_task
  WAREHOUSE = etl_wh
  SCHEDULE = 'USING CRON 15 * * * * UTC'
AS CALL raw.load_vendor_orders();

One call per page, one write, one task, and a retry story. This is the right default for API ingestion.

Idempotency, retries, and failure

External calls fail. Build for it:

• Set a timeout on every request. requests has no default timeout; without one a hung vendor pins your warehouse until the statement timeout fires.
• Retry with backoff on 429 and 5xx only. Never retry other 4xx responses — you will just burn credits confirming the same rejection.
• Return None rather than raising in a UDF used across a big scan, and record the failure. One bad row should not kill a 40-minute query; reconcile the nulls on a second pass.
• Make loaders restartable with a watermark or cursor in a control table, so a failed task run resumes instead of duplicating.
• Set STATEMENT_TIMEOUT_IN_SECONDS on the warehouse running these jobs so a stuck integration cannot bill indefinitely.

Security review checklist

• One integration per external system, never a catch-all with a broad VALUE_LIST. The integration is the unit of granting, so it should be the unit of trust.
• Grant USAGE ON INTEGRATION to functional roles, not to PUBLIC, and grant READ ON SECRET separately — a role that can use the network path still cannot read a credential it was not given.
• Audit regularly: SHOW EXTERNAL ACCESS INTEGRATIONS for what exists, and SNOWFLAKE.ACCOUNT_USAGE.EXTERNAL_ACCESS_HISTORY for which functions actually called which hosts. Review it the way you review LOGIN_HISTORY.
• Rotate secrets with ALTER SECRET ... SET SECRET_STRING = '...'; functions pick up the new value on the next call, so rotation needs no code deploy. Schedule it.
• Put the owning team in the integration's COMMENT. In two years somebody will ask why vendor_api_access exists, and the answer should not require archaeology.
• If egress must leave from a known IP range because the vendor allowlists on their side, that is an outbound private connectivity conversation with Snowflake, not something a network rule solves. Raise it early; it has lead time.

Where external access fits versus the alternatives

Need	Better tool
LLM inference over governed data	Cortex AISQL — no egress at all
CDC or SaaS ingestion with an existing connector	Openflow — managed, no code
High-volume application events	Snowpipe Streaming from the producing app
Long-running services or custom containers	Snowpark Container Services
Occasional API enrichment and modest API-based ingestion	External access integrations — this article

External access is the right tool for enrichment, validation, and modest API ingestion. It is the wrong tool for moving terabytes or for anything that wants to be a long-lived service; reach for Openflow or Snowpark Container Services there.

Teardown

ALTER EXTERNAL ACCESS INTEGRATION exchange_api_access SET ENABLED = FALSE;   -- stop first
DROP FUNCTION IF EXISTS analytics.fx_rate(STRING, STRING, DATE);
DROP INTEGRATION IF EXISTS exchange_api_access;
DROP SECRET IF EXISTS governance.integrations.exchange_api_key;
DROP NETWORK RULE IF EXISTS governance.integrations.exchange_api_rule;

Disable before dropping, so dependent code fails loudly with a clear error rather than mysteriously at 3am.

PowderInsights builds and reviews Snowflake integration architecture — external access, secret management, API ingestion, and the governance around them. Contact us to talk through your build, or see our Snowflake development services.