What We Were Trying to Do

My team had a need to call a third party encryption API directly from Snowflake. The requirements were simple:

• Call a REST API to decrypt some tokens (such as encrypted card numbers that needed to be resolved for downstream analytics)
• Authenticate with OAuth 2 client credentials
• Route all traffic through AWS PrivateLink securely

Snowflake’s feature: External Access Integration lets us do exactly this, to call external APIs from a Python UDF.

Getting the Snowflake side working is ~30% of the effort. The remaining 70% is coordinating with the infrastructure team, getting the AWS plumbing right, and resolving a certificate chain issue.

Architecture (Big Picture)

The setup has two separate network paths:

┌─────────────────────────────────────────────────────────────┐
│  SNOWFLAKE ENVIRONMENT                                      │
│                                                             │
│  ┌───────────────────────────────────────────────────────┐  │
│  │  External Access Integration                          │  │
│  │  (bundles network rules + secrets together)           │  │
│  │                                                       │  │
│  │  ┌──────────────────┐    ┌─────────────────────────┐  │  │
│  │  │ OAUTH_PUBLIC_RULE│    │  API_PRIVATE_RULE       │  │  │
│  │  │ TYPE: HOST_PORT  │    │  TYPE: PRIVATE_HOST_PORT│  │  │
│  │  └────────┬─────────┘    └────────────┬────────────┘  │  │
│  └───────────┼───────────────────────────┼───────────────┘  │
│              │                           │                  │
│  ┌───────────▼───────────────────────────▼───────────────┐  │
│  │  Python UDF / Stored Procedure                        │  │
│  │  1. Get OAuth token  ──►  2. Call API with token      │  │
│  └───────────────────────────────────────────────────────┘  │
└──────────────┬───────────────────────────┬──────────────────┘
               │                           │
       Public Internet              AWS PrivateLink
       (outbound HTTPS)            (private network)
               │                           │
               ▼                           ▼
  ┌────────────────────┐     ┌──────────────────────────┐
  │  Identity Provider │     │  VPC Endpoint Service    │
  │  (OAuth 2.0 token  │     │         │                │
  │   endpoint)        │     │    NLB / ALB             │
  └────────────────────┘     │         │                │
                             │    Your API Service      │
                             └──────────────────────────┘

We have two paths because our OAuth provider wasn’t reachable over PrivateLink. The Token requests traverse over public internet (HOST_PORT rule), and the actual API calls stay private (PRIVATE_HOST_PORT rule). There are two network rules, one integration and an UDF that relies on both.

Step 1: Check the PrivateLink Setup

Verify what PrivateLink looks like from your Snowflake account:

SELECT key, value 
FROM TABLE(FLATTEN(INPUT => PARSE_JSON(SYSTEM$GET_PRIVATELINK_CONFIG())));

Document the VPC endpoint ID, PrivateLink account URL, and the AWS principal that needs to be whitelisted on the target service.

Step 2: Provision the PrivateLink Endpoint

SELECT SYSTEM$PROVISION_PRIVATELINK_ENDPOINT(
    'com.amazonaws.vpce.eu-west-2.vpce-svc-xxxxxxxxxxxx',
    'api.your-internal-service.com'
);

Two things to know:

• Snowflake supports cross-region connectivity through AWS VPC Endpoint Services, provided the endpoint service is available and supported in the target Snowflake region. However, AWS PaaS services such as Amazon S3 and AWS KMS must reside in the same region as the consuming resources, as cross-region access is not supported for these services.
• Admin on the AWS side should accept this connection request, or the endpoint sits in “pending” forever/

Step 2b: AWS Side

This is the section that’s important.

What Your Infra or Platform Team Needs to Set Up

VPC Endpoint Service — Before Snowflake can connect, we need to create the service on AWS side. In Terraform it looks like:

resource "aws_vpc_endpoint_service" "encryption_api" {
  acceptance_required        = true
  network_load_balancer_arns = [aws_lb.api_nlb.arn]
  
  allowed_principals = [
    "arn:aws:iam::${var.snowflake_aws_account_id}:root"
  ]
}

allowed_principals value comes from the SYSTEM$GET_PRIVATELINK_CONFIG() output from previous step. Else, the connection gets rejected.

Accept the connection : After Snowflake provisions its endpoint, one who has AWS console access needs to go to VPC > Endpoint Services > Endpoint Connections and click Accept.

DNS name should match exactly : This one cost us a lot of time. The private DNS name configured on the AWS side must be in sync with what you gave Snowflake. A subtle difference between api-service.example.com and api-service-us.example.com will give connection errors.

TLS certificates: The load balancer in front of your service must present a complete certificate chain. Server cert, intermediate CA, root CA — the entire thing. If the intermediate cert is not present, Python’s SSL library (which Snowflake uses under the hood) will reject the connection.

Talk to Your SRE Team Early

Our infrastructure team flagged an item I hadn’t considered: the backend service sometimes had capacity issues in the past under heavy load. These are the things to consider

• Can the backend handle expected volume?
• Are there rate limits?
• Can we monitor if the service is struggling?
• What is the rollback plan if you overwhelm it?

We built a checklist for deployment day that covered pre-deployment (a week out), deployment day steps, and post-deployment monitoring. This is essential if you’re doing it across teams.

Step 3: Create Network Rules

On the Snowflake side, we need two rules — one for the API over PrivateLink, one for OAuth over the public internet:

-- API traffic goes through PrivateLink
CREATE NETWORK RULE API_PRIVATE_RULE
    MODE = EGRESS
    TYPE = PRIVATE_HOST_PORT
    VALUE_LIST = ('api.your-internal-service.com:443');

-- OAuth token endpoint goes over public internet
CREATE NETWORK RULE OAUTH_PUBLIC_RULE
    MODE = EGRESS
    TYPE = HOST_PORT
    VALUE_LIST = ('auth.your-idp.com:443');

PRIVATE_HOST_PORT is necessary for PrivateLink traffic, HOST_PORT is for regular internet.

Step 4: Create the External Access Integration

This bundles the network rules together and the reference in your UDF:

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION MY_API_INTEGRATION
    ALLOWED_NETWORK_RULES = (API_PRIVATE_RULE, OAUTH_PUBLIC_RULE)
    ENABLED = TRUE;

GRANT USAGE ON INTEGRATION MY_API_INTEGRATION TO ROLE YOUR_ROLE;

Step 5: The Python UDF

Here’s the basic UDF with OAuth token caching and retry logic:

CREATE OR REPLACE FUNCTION decrypt_token(encrypted_token STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.10
PACKAGES = ('requests')
EXTERNAL_ACCESS_INTEGRATIONS = (MY_API_INTEGRATION)
HANDLER = 'decrypt'
AS
$$
import json
import requests
import uuid
import time
import random

API_URL = "https://api.your-internal-service.com/v1/decrypt"
TOKEN_URL = "https://auth.your-idp.com/oauth/token"
CLIENT_ID = "your-client-id"
CLIENT_SECRET = "your-client-secret"

# Token cache - survives across rows within the same warehouse session
_token_cache = {
    "access_token": None,
    "expires_at": 0
}

def _get_oauth_token(max_retries=3):
    current_time = time.time()
    
    if _token_cache["access_token"] and _token_cache["expires_at"] > (current_time + 60):
        return _token_cache["access_token"]
    
    last_exception = None
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                time.sleep((2 ** attempt) + random.uniform(0, 1))
            
            resp = requests.post(
                TOKEN_URL,
                data={
                    "grant_type": "client_credentials",
                    "client_id": CLIENT_ID,
                    "client_secret": CLIENT_SECRET
                },
                headers={"Content-Type": "application/x-www-form-urlencoded"},
                timeout=15
            )
            
            if not resp.ok:
                if 500 <= resp.status_code < 600 and attempt < max_retries - 1:
                    continue
                raise Exception(f"OAuth failed: HTTP {resp.status_code}")
            
            token_data = resp.json()
            access_token = token_data.get("access_token")
            expires_in = token_data.get("expires_in", 3600)
            
            if not access_token:
                raise Exception("No access_token in response")
            
            _token_cache["access_token"] = access_token
            _token_cache["expires_at"] = current_time + int(expires_in)
            return access_token
            
        except requests.exceptions.RequestException as e:
            last_exception = e
            if attempt == max_retries - 1:
                raise Exception(f"OAuth failed after {max_retries} attempts: {e}")
    
    raise Exception(f"OAuth failed after {max_retries} attempts: {last_exception}")

def decrypt(encrypted_token: str) -> str:
    if encrypted_token is None or not str(encrypted_token).strip():
        return None
    
    trace_id = str(uuid.uuid4())
    
    try:
        access_token = _get_oauth_token()
        
        response = requests.post(
            API_URL,
            headers={
                "Content-Type": "application/json",
                "Authorization": f"Bearer {access_token}",
                "X-Trace-Id": trace_id
            },
            data=json.dumps({"token": str(encrypted_token)}),
            timeout=15
        )
        
        if not response.ok:
            raise Exception(f"API error: HTTP {response.status_code}")
        
        result = response.json().get("decrypted_value")
        if result is None:
            raise Exception(f"Missing 'decrypted_value' in response")
        
        return str(result)
    
    except requests.exceptions.SSLError as e:
        raise Exception(f"TLS/SSL error: {e}")
    except requests.exceptions.Timeout:
        raise Exception(f"API timeout (trace_id={trace_id})")
    except requests.exceptions.RequestException as e:
        raise Exception(f"Network error: {e}")
$$;

A few things worth noting:

• The _token_cache dictionary persists across rows in the same query. So you're not re-authenticating for every single row - just once (or whenever the token expires).
• The retry logic backs off exponentially on server errors but fails immediately on client errors (4xx). There is no point in retrying a bad request.
• The trace ID on every request helps to match Snowflake errors to the API’s logs.
• I’m using hardcoded credentials here for simplicity. In production, Snowflake Secrets must be used.

Making It Fast: Vectorized Batch Processing

The single-row UDF worked fine for testing, but calling the API once per row doesn’t scale. For example 200,000 tokens, that’s 200,000 HTTP requests. That’s slow.

We rebuilt it as a batch UDF that takes an array of tokens and processes them concurrently:

CREATE OR REPLACE FUNCTION decrypt_token_batch(
    token_array ARRAY,
    access_token VARCHAR
)
RETURNS ARRAY
LANGUAGE PYTHON
RUNTIME_VERSION = '3.10'
PACKAGES = ('requests')
HANDLER = 'decrypt_batch'
EXTERNAL_ACCESS_INTEGRATIONS = (MY_API_INTEGRATION)
AS $$
import json
import requests
import uuid
from concurrent.futures import ThreadPoolExecutor, as_completed

API_URL = "https://api.your-internal-service.com/v1/decrypt"

# Reuse TCP connections across calls
session = requests.Session()
adapter = requests.adapters.HTTPAdapter(
    pool_connections=20,
    pool_maxsize=20,
    max_retries=2
)
session.mount('https://', adapter)

def decrypt_single(token: str, access_token: str) -> str:
    if not token or not str(token).strip():
        return "ERROR: Empty token"
    
    trace_id = str(uuid.uuid4())
    
    try:
        resp = session.post(
            API_URL,
            headers={
                "Content-Type": "application/json",
                "Authorization": f"Bearer {access_token}",
                "X-Trace-Id": trace_id
            },
            json={"token": str(token)},
            timeout=10
        )
        
        if not resp.ok:
            return f"ERROR: HTTP {resp.status_code}"
        
        result = resp.json().get("decrypted_value")
        if result is None:
            return f"ERROR: No result in response"
        
        return str(result)
    
    except requests.exceptions.Timeout:
        return f"ERROR: Timeout"
    except Exception as e:
        return f"ERROR: {str(e)[:200]}"

def decrypt_batch(token_array, access_token: str):
    if not token_array:
        return []
    
    results = [None] * len(token_array)
    
    with ThreadPoolExecutor(max_workers=20) as executor:
        future_to_index = {
            executor.submit(decrypt_single, t, access_token): i
            for i, t in enumerate(token_array)
        }
        
        for future in as_completed(future_to_index):
            idx = future_to_index[future]
            try:
                results[idx] = future.result()
            except Exception as e:
                results[idx] = f"ERROR: {str(e)[:200]}"
    
    return results
$$;

The two key tricks here: connection pooling (via requests.Session with an HTTPAdapter) so you're not making a new TCP connection for every call, and threading (via ThreadPoolExecutor) so 20 API calls run at the same time instead of one after another.

This took us from 45 minutes for 100K tokens down to a few minutes!

The Real Win: A Cache Table

Even with concurrent processing, we were still making redundant calls. The same encrypted tokens appear over and over in the data. So we added a cache table:

CREATE TABLE IF NOT EXISTS TOKEN_DECRYPT_CACHE (
    encrypted_token VARCHAR(256) PRIMARY KEY,
    decrypted_value VARCHAR(256),
    first_seen TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    last_used TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    use_count NUMBER DEFAULT 1
)
CLUSTER BY (encrypted_token)
DATA_RETENTION_TIME_IN_DAYS = 15;

The processing logic checks the cache table first, and only calls the API for tokens we haven’t seen before, then updates the cache with new results.

-- 1. Check what's already cached
CREATE TEMPORARY TABLE temp_with_cache AS
SELECT
    src.encrypted_token,
    cache.decrypted_value as cached_result,
    CASE WHEN cache.encrypted_token IS NOT NULL THEN TRUE ELSE FALSE END as is_cached
FROM source_data src
LEFT JOIN TOKEN_DECRYPT_CACHE cache 
    ON src.encrypted_token = cache.encrypted_token;

-- 2. Only make an API call for distinct uncached tokens
-- (batch them into arrays of 500 for vectorized UDF)

-- 3. MERGE new results back into the cache

In production, our cache hit rate sits at 90–95%. That means for a single batch of 100,000 tokens, we’re only making 5,000–10,000 API calls instead of all 100,000. The processing time dropped from 45 minutes to 3–5 minutes.

Persisting the OAuth Token

Our in-memory token cache in the UDF resets every time the warehouse restarts. For scheduled jobs, that means a fresh OAuth request on every run. We added a table to persist tokens for all sessions:

CREATE TABLE IF NOT EXISTS OAUTH_TOKEN_CACHE (
    token_type VARCHAR(50) PRIMARY KEY,
    access_token VARCHAR(5000),
    expires_at TIMESTAMP_NTZ,
    last_updated TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

And a stored procedure refreshes the token and loads it to this table. The batch UDF selects from the table. This removes unnecessary OAuth round-trips.

Logging and Monitoring

We track every batch run in a log table:

CREATE TABLE IF NOT EXISTS BATCH_PROCESSING_LOG (
    batch_id NUMBER AUTOINCREMENT PRIMARY KEY,
    batch_start_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    batch_end_time TIMESTAMP_NTZ,
    records_processed NUMBER,
    records_successful NUMBER,
    records_failed NUMBER,
    cache_hits NUMBER DEFAULT 0,
    cache_misses NUMBER DEFAULT 0,
    api_calls_made NUMBER DEFAULT 0,
    cache_hit_rate_pct FLOAT
)
CLUSTER BY (batch_start_time);

A typical log entry looks like:

Batch 1247 completed. Processed: 85,432 | Cache Hits: 78,921 (92.4%) | 
API Calls: 6,511

When any issues occurs, we have the trail log to figure out what happened.

Automating with Tasks

We automated the entire thing with a Snowflake Task that runs after our upstream data load:

CREATE OR REPLACE TASK PROCESS_DECRYPT_BATCH_TASK
  WAREHOUSE = MY_WAREHOUSE
  USER_TASK_TIMEOUT_MS = 7200000  -- 2 hour timeout
  AFTER UPSTREAM_DATA_LOAD_TASK
AS
  EXECUTE IMMEDIATE $$
  BEGIN
    CALL PROCESS_DECRYPTION_BATCH(0.25);
  EXCEPTION
    WHEN OTHER THEN
      CALL SYSTEM$SEND_EMAIL(
        'MY_EMAIL_INTEGRATION',
        'team@example.com',
        'Decryption Batch Failed',
        'Failed at ' || CURRENT_TIMESTAMP()::VARCHAR || 
        ' Error: ' || SQLERRM
      );
      RAISE;
  END;
  $$;

ALTER TASK PROCESS_DECRYPT_BATCH_TASK RESUME;

If the batch fails, team gets an email.

The SSL Certificate Problem (a.k.a. This Part nearly Broke Us)

This was our challenge before our go-live:

[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: 
Unable to get local issuer certificate

Code worked in our test environment, but not in Production.

After working with Snowflake support, we ran:

openssl s_client -connect api.your-internal-service.com:443 -showcerts

We realized that our production load balancer was only serving the server certificate — no intermediate CA. Python’s SSL library (which Snowflake also uses internally) couldn’t build a trust chain back to a root CA, so it rejected the connection.

The fix was on the AWS Platform side. The infrastructure team made an update to the load balancer to present full certificate chain: server cert + intermediate CA + root CA. Once that was done, it worked.

If you hit SSL errors with PrivateLink, first check the certificate chain on the endpoint. Snowflake uses Python SSL verification. If the endpoint presents a complete chain, it works.

Bonus: The same pattern works for SFTP

After the REST API integration was working, we realized that we could pull files from an external SFTP server the same way. External Access Integration handles this as well — and it’s actually simpler than the API case because there’s no OAuth involved.

Our use-case was ingesting daily CSV files from an external third-party SFTP server into Snowflake. Traditionally, this was a multi-step process: syncing the SFTP files to cloud storage (Amazon S3 or Azure Blob) using intermediate orchestrators like Control-M, AWS Lambda, or cron jobs on EC2, and then loading them into the data warehouse. By using Snowflake External Access Integration, we eliminated this middle tier. Snowflake can natively and securely connect to the SFTP server.

SFTP Architecture

┌─────────────────────────────────────────────────────────┐
│  SNOWFLAKE ENVIRONMENT                                  │
│                                                         │
│  ┌───────────────────────────────────────────────────┐  │
│  │  External Access Integration                      │  │
│  │                                                   │  │
│  │  ┌──────────────────┐  ┌───────────────────────┐  │  │
│  │  │ SFTP_NETWORK_RULE│  │  SFTP_CREDENTIAL      │  │  │
│  │  │ TYPE: HOST_PORT  │  │  TYPE: PASSWORD       │  │  │
│  │  │ host:22          │  │  (username + password)│  │  │
│  │  └────────┬─────────┘  └───────────┬───────────┘  │  │
│  └───────────┼────────────────────────┼──────────────┘  │
│              │                        │                 │
│  ┌───────────▼────────────────────────▼──────────────┐  │
│  │  Python Stored Procedure                          │  │
│  │  Connect → List files → Download → Parse → Load   │  │
│  └───────────────────────────────────────────────────┘  │
│              │                                          │
│              ▼                                          │
│  ┌──────────────────────┐                               │
│  │  Target Table        │                               │
│  └──────────────────────┘                               │
└──────────────┬──────────────────────────────────────────┘
               │
        Public Internet
          (port 22)
               │
               ▼
     ┌──────────────────┐
     │  Vendor's SFTP   │
     │  Server          │
     └──────────────────┘

SetUp

The building blocks are the same as mentioned in API example — network rule, secret, integration — just configured for SFTP instead of HTTPS.

Network rule — allow outbound traffic to the SFTP server on port 22:

CREATE NETWORK RULE SFTP_ACCESS_RULE
    TYPE = HOST_PORT
    MODE = EGRESS
    VALUE_LIST = ('sftp.vendor-partner.com:22');

Secret — store the SFTP credentials securely (no hardcoding passwords in your code):

CREATE SECRET SFTP_CREDENTIALS
    TYPE = PASSWORD
    USERNAME = 'sftp_user'
    PASSWORD = '********';

External Access Integration — bind them together:

CREATE EXTERNAL ACCESS INTEGRATION SFTP_INTEGRATION
    ALLOWED_NETWORK_RULES = (SFTP_ACCESS_RULE)
    ALLOWED_AUTHENTICATION_SECRETS = (SFTP_CREDENTIALS)
    ENABLED = TRUE;

Stored procedure — connect to SFTP, pull the files, parse them, and load into table:

CREATE OR REPLACE PROCEDURE INGEST_FROM_SFTP()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.10
PACKAGES = ('snowflake-snowpark-python', 'paramiko')
EXTERNAL_ACCESS_INTEGRATIONS = (SFTP_INTEGRATION)
SECRETS = ('cred' = SFTP_CREDENTIALS)
HANDLER = 'run'
AS
$$
import paramiko
import csv
import io
import _snowflake

def run(session):
    # Get credentials from Snowflake secret
    username = _snowflake.get_username_password('cred').username
    password = _snowflake.get_username_password('cred').password
    
    # Connect to SFTP
    transport = paramiko.Transport(('sftp.vendor-partner.com', 22))
    transport.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(transport)
    
    try:
        # List files in the drop directory
        files = sftp.listdir('/outbound/daily/')
        csv_files = [f for f in files if f.endswith('.csv')]
        
        total_rows = 0
        for filename in csv_files:
            # Download file into memory
            with sftp.open(f'/outbound/daily/{filename}', 'r') as remote_file:
                content = remote_file.read().decode('utf-8')
            
            # Parse CSV
            reader = csv.DictReader(io.StringIO(content))
            rows = []
            for row in reader:
                rows.append(row)
            
            if rows:
                # Load into Snowflake table
                df = session.create_dataframe(rows)
                df.write.mode("append").save_as_table("VENDOR_DAILY_DATA")
                total_rows += len(rows)
            
            # Move processed file to archive
            sftp.rename(
                f'/outbound/daily/{filename}',
                f'/outbound/archive/{filename}'
            )
        
        return f"Load is complete. {total_rows} rows from {len(csv_files)} files"
    
    finally:
        sftp.close()
        transport.close()
$$;

What’s unique from the API Example

The SFTP version is simpler in a few ways:

• No OAuth — credentials are handled by a Snowflake Secret
• No PrivateLink — Usually SFTP servers are reachable over the internet, so a regular HOST_PORT rule is enough
• No caching needed — you’re not making the same call repeatedly
• Uses paramiko - Python's SFTP library, available in Snowflake's package repo

But the core pattern stays same: network rule controls where you can connect, secret stores credentials safely, integration bundles them, and a Python procedure does the actual work. Once you’ve set up one External Access Integration, the setup stays same with different end points.

When to Use This vs. the Traditional Approach

Pulling SFTP files directly into Snowflake makes sense when:

• Less hops (no Lambda, no EC2 cron job, no intermediate S3 bucket)
• The file volumes are not huge (daily drops of CSVs in MBs or GBs but not TBs)
• You want everything governed in single place — network rules, credentials, and processing logic all local to Snowflake

If you’re dealing with extra large files or need streaming ingestion, the traditional SFTP -> cloud-storage -> Snowflake path is probably still better. But for the typical “vendor drops a file once a day” scenario, this simplifies the infrastructure.

What I’d Do Differently

Start the cross-team conversation earlier.

There’s always a need for coordination with the infrastructure team

We underestimated how much coordination was needed with the infrastructure team. DNS names, Terraform changes, TLS configuration, capacity reviews — give them at least two weeks lead time.

Keep a shared doc with all the environment-specific details. URLs, DNS names, AWS account IDs, endpoint IDs — all of it. We had bits scattered across email threads and Teams messages. That was a mistake.

Don’t assume production will work just because the test environment does. DNS names, certificate chains, and backend capacity can all differ between environments. Test the full chain in a non-prod or pre-prod environment that mirrors production as closely as possible.

Cache early, cache often. The token cache was an afterthought for us, but it ended up being the biggest performance win. If your data has any amount of repetition, a cache table will pay for itself.

Build in logging from the start. Batch processing logs with cache hit rates and error counts made troubleshooting much easier. Without them, we’d have been flying blind.

The Bottom Line

External Access Integration is one of those Snowflake features that’s more capable than most people realize. Whether you are calling a REST API over PrivateLink with OAuth, or pulling files off a SFTP server, the pattern is the same: network rule, secret (optional), integration, Python code.

The REST API and PrivateLink setup has the AWS-side coordination. But once it’s setup and running, it’s low-maintenance. Our batch job processes hundreds of thousands of tokens on a daily basis, the cache keeps API costs low, and it runs on a schedule.

The SFTP version is much simpler to set up and a good place to start if you want to try External Access Integration without the complexity of PrivateLink.

Either way, the payoff is the same: your data stays in Snowflake; the external calls are governed and auditable, and you don’t need to maintain extra infrastructure to move data around.

Breaking Out of Snowflake: Calling External APIs and SFTP Servers from SQL Queries was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.

We migrated a high-volume merchant analytics platform to Snowflake. We went from 500ms point-lookup latency down to 20ms. A 25x improvement. We didn’t get there by turning on fancy features. 98% of what worked was just standard Snowflake tables, engineered properly — time-based sharding, clustering on the right keys, and Search Optimization on columns that actually needed it.

I’m sharing this to provide a complete perspective. When we started, there wasn’t a single resource that connected the full picture. Most articles cover clustering or Search Optimization in isolation, but our scenario was a production system handling 2 TB of organic data growth per month.

What you’ll learn:

• How to design hot/warm/cold data tiers that match real query patterns
• When clustering alone isn’t enough and Search Optimization becomes essential
• Why we chose split tables over a single consolidated surface
• Connection pooling optimizations that eliminated 90% of reconnect overhead
• The exact setup pattern any technical team can follow

The Problem We Were Solving

This wasn’t a typical lift-and-shift. We were replacing a legacy accelerator powering a merchant-facing analytics portal. Sub-second response times, millions of daily queries, zero tolerance for regression. If the dashboard got slower after migration, we’d hear about it immediately from merchants, from product, from everyone.

The platform covers multiple transaction domains: payment authorizations, credit cards, debit cards, gift cards. Data comes in from mainframe-originated source files, transforms through several layers, and ends up serving a real-time dashboard.

Two numbers shaped every decision we made:

• ~2 TB of new transaction data per month: the tables never stop growing
• 95%+ of queries hit the most recent 90 days: extreme recency bias

That second number changed everything for us. We couldn’t just throw the entire dataset behind a single table and hope Snowflake’s pruning would handle it. At 30 TB per transaction type, even good pruning takes seconds just at the compilation stage. We needed to physically separate hot data from cold data.

Let’s get into the implementation.

Our Three-Layer Architecture: RAW → CURATED → APPLICATION

After a few weeks of back-and-forth with the team, we landed on three layers. Nothing groundbreaking in concept but, the devil is in how you implement each one.

RAW: Just Land the Data, Don’t Touch It

RAW is append-only. We load source files from S3, preserve everything as-is. No transformations, no business logic. This gives us replayability — when something breaks downstream, we can always go back to source.

CREATE OR REPLACE TABLE RAW.TXN_AUTH (
  RAW_LINE              VARCHAR,    -- entire fixed-width record stored as-is
  METADATA_FILENAME     VARCHAR,
  METADATA_FILE_ROW_NUMBER NUMBER,
  LOAD_TS               TIMESTAMP_NTZ
);

 -- Load data from S3 into RAW
COPY INTO RAW.TXN_AUTH (RAW_LINE, METADATA_FILENAME, METADATA_FILE_ROW_NUMBER, LOAD_TS)
  FROM (
    SELECT
      $1,
      METADATA$FILENAME,
      METADATA$FILE_ROW_NUMBER,
      CURRENT_TIMESTAMP()
    FROM @RAW.S3_TXN_STAGE/txn_auth/
  )
  FILE_FORMAT = (
    TYPE            = 'CSV'
    FIELD_DELIMITER = NONE      -- no delimiter; treat entire line as one field
    RECORD_DELIMITER = '\n'
    TRIM_SPACE      = FALSE     -- preserve exact byte positions
  )
  ON_ERROR = 'ABORT_STATEMENT';

CREATE OR REPLACE STREAM RAW.STR_TXN_AUTH 
  ON TABLE RAW.TXN_AUTH 
  APPEND_ONLY = TRUE;

We store the entire mainframe record as a single `VARCHAR` line . Mainframe positional files are brittle; if the upstream team shifts a column, we don’t want RAW to break. All positional logic lives in the CUR stored procedure. We fail the entire load on any error (`ABORT_STATEMENT`). For error handling, we use SYSTEM$SEND_EMAIL inside the stored procedure's EXCEPTION block (shown later) to notify the support team so they can coordinate a fix with the upstream mainframe team before re-triggering.

CURATED: Where the Real Work Happens

CUR is where we flatten the raw data into proper columns, apply business logic, and *this is key* split it into time-windowed tables. The physical split happens here, not at the serving layer.

Each table gets its own clustering and Search Optimization. We cluster by merchant prefix + date (because that’s how queries come in), and apply Search Optimization on `CARD_NUMBER_HASH` a high-cardinality column that’s always used in equality lookups but would be wasteful to cluster on.

CREATE OR REPLACE TABLE CUR.TXN_01_90D (
TXN_ID VARCHAR,
MERCHANT_ID VARCHAR,
CARD_NUMBER_HASH VARCHAR,
TXN_DATE DATE,
TXN_AMOUNT NUMBER(38,2),
LOAD_TS TIMESTAMP_NTZ
)
CLUSTER BY (SUBSTRING(MERCHANT_ID, 1, 6), TXN_DATE);
ALTER TABLE CUR.TXN_01_90D ADD SEARCH OPTIMIZATION
ON EQUALITY(CARD_NUMBER_HASH);

One thing we got wrong initially — we tried applying Search Optimization on the same columns we were clustering by. For columns already used in range-based clustering predicates, SOS typically adds storage cost without meaningful latency benefit. For equality-only lookups on a clustering key column, SOS can still provide value — measure before deciding. In our case, the clustering key columns didn’t need it.

Before we get to the tasks, you’ll need a Notification Integration for the stored procedure email alerts. This is an account-level object, you’ll need ACCOUNTADMIN or equivalent privileges to create it:

CREATE OR REPLACE NOTIFICATION INTEGRATION email_integration
TYPE = EMAIL
ENABLED = TRUE
ALLOWED_RECIPIENTS = ('your-team-dl@company.com');

The stream-to-CUR pipeline runs every 5 minutes via a Task. We use SYSTEM$SEND_EMAIL in the stored procedure's EXCEPTION block to send error notifications. This is the right pattern for email-based alerting at the SQL layer. (Note: Task-level ERROR_INTEGRATION requires a cloud messaging integration like SNS, Azure Event Grid, or Google Pub/Sub)

CREATE OR REPLACE TASK TASK_ADMIN.TASK_TXN_AUTH_TO_CUR
WAREHOUSE = ETL_WH
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('RAW.STR_TXN_AUTH')
AS
CALL SP_PROCESS_TXN_AUTH();

Here’s a simplified version of what `SP_PROCESS_TXN_AUTH` does. The real one is longer(handles more edge cases), but this shows the core pattern extract fields by byte position, derive business columns, and insert into the hot tier:

CREATE OR REPLACE PROCEDURE SP_PROCESS_TXN_AUTH()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
-- Field positions are defined by the mainframe file layout specification.
-- All positional logic is centralised here — RAW stores records verbatim.
-- If the upstream layout changes, only this procedure needs updating.
INSERT INTO CUR.TXN_01_90D (
TXN_ID, MERCHANT_ID, CARD_NUMBER_HASH, TXN_DATE, TXN_AMOUNT, LOAD_TS
)
SELECT
TRIM(SUBSTRING(RAW_LINE,  1, 20)),                      -- pos  1-20:  TXN_ID
TRIM(SUBSTRING(RAW_LINE, 21, 15)),                      -- pos 21-35:  MERCHANT_ID
SHA2(TRIM(SUBSTRING(RAW_LINE, 36, 16)))::VARCHAR,       -- pos 36-51:  CARD_NUMBER (hashed; raw PAN never lands in CUR)
TO_DATE(TRIM(SUBSTRING(RAW_LINE, 52, 8)), 'YYYYMMDD'), -- pos 52-59:  TXN_DATE
TO_NUMBER(TRIM(SUBSTRING(RAW_LINE, 60, 12)), 10, 2),   -- pos 60-71:  TXN_AMOUNT 
CURRENT_TIMESTAMP()
FROM RAW.STR_TXN_AUTH
WHERE METADATA$ACTION = 'INSERT';
RETURN 'Processed ' || SQLROWCOUNT || ' rows';

EXCEPTION
  WHEN OTHER THEN
    CALL SYSTEM$SEND_EMAIL(
      'email_integration',
      'your-team-dl@company.com',
      'ERROR: SP_PROCESS_TXN_AUTH Failed',
      'SQLCODE: ' || SQLCODE || '\nSQLERRM: ' || SQLERRM || '\nSQLSTATE: ' || SQLSTATE
    );
    RETURN 'FAILURE';
END;
$$;

The positional offsets above are illustrative, replace them with your actual mainframe layout spec. We hash the card number at ingestion (`SHA2`) so the raw PAN never lands in CUR. Note that SHA2 is a one-way cryptographic hash(it’s irreversible), which is different from tokenization (which uses a reversible token vault). For our purposes, we only need a stable high-cardinality key for Search Optimization, not the ability to recover the original value.

APPLICATION: Views Only and Interactive tables

APP contains zero standard tables (with the exception of Interactive tables) Just views that UNION ALL across the CUR split tables. The benefit of this setup is that when a query comes in with a date filter, Snowflake’s optimizer prunes aggressively within each table. A query for “last 30 days” will scan CUR.TXN_01_90D fully, while the other three tables will have virtually all their partitions pruned (resulting in minimal or zero actual data scan from those older tiers).

CREATE OR REPLACE VIEW APP.VW_TXN_AUTH AS
SELECT * FROM CUR.TXN_01_90D
UNION ALL
SELECT * FROM CUR.TXN_91_180D
UNION ALL
SELECT * FROM CUR.TXN_181_360D
UNION ALL
SELECT * FROM CUR.TXN_361_480D;

From the application’s perspective, it’s just one view. The complexity is completely hidden.

The Weekly Rollover (And Why Atomicity Matters)

Every Saturday at 2 AM, a task kicks off to move data between tiers. Records older than 90 days move from `TXN_01_90D` to `TXN_91_180D`, and so on down the chain.

We learned the hard way that this needs to be atomic. During our first few test runs, we had a failure halfway through. Data was inserted into the warm tier, but not yet deleted from hot. Suddenly we had duplicate records showing up in the dashboard.

The fix was wrapping everything in a transaction with proper exception handling. If anything fails, we roll back the entire batch and fire off an email so someone can investigate before Monday morning.

CREATE OR REPLACE PROCEDURE SP_ROLLOVER_TXN_TIERS()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
BEGIN TRANSACTION;
DELETE FROM CUR.TXN_361_480D WHERE TXN_DATE < DATEADD(day, -480, CURRENT_DATE());
INSERT INTO CUR.TXN_361_480D
SELECT * FROM CUR.TXN_181_360D WHERE TXN_DATE < DATEADD(day, -360, CURRENT_DATE());
DELETE FROM CUR.TXN_181_360D WHERE TXN_DATE < DATEADD(day, -360, CURRENT_DATE());
INSERT INTO CUR.TXN_181_360D
SELECT * FROM CUR.TXN_91_180D WHERE TXN_DATE < DATEADD(day, -180, CURRENT_DATE());
DELETE FROM CUR.TXN_91_180D WHERE TXN_DATE < DATEADD(day, -180, CURRENT_DATE());
INSERT INTO CUR.TXN_91_180D
SELECT * FROM CUR.TXN_01_90D WHERE TXN_DATE < DATEADD(day, -90, CURRENT_DATE());
DELETE FROM CUR.TXN_01_90D WHERE TXN_DATE < DATEADD(day, -90, CURRENT_DATE());
COMMIT;
RETURN 'SUCCESS';
EXCEPTION
WHEN OTHER THEN
ROLLBACK;
CALL SYSTEM$SEND_EMAIL(
'email_integration',
'youremail@example.com',
'ERROR: SP_ROLLOVER_TXN_TIERS Failed',
'SQLCODE: ' || SQLCODE || '\nSQLERRM: ' || SQLERRM || '\nSQLSTATE: ' || SQLSTATE
);
RETURN 'FAILURE';
END;
$$;

CREATE OR REPLACE TASK TASK_ADMIN.TXN_ROLLOVER_ALL_TIERS
WAREHOUSE = ETL_WH
SCHEDULE = 'USING CRON 0 2 * * SAT America/New_York'
AS
CALL SP_ROLLOVER_TXN_TIERS();

Why Split Tables Instead of Just Relying on Pruning?

The question came up: “Snowflake already has micro-partition pruning, why bother with manual sharding?”

Here’s what we observed: at 30 TB per transaction type, even with perfect clustering, the query compiler itself takes 2-3 seconds just to identify which partitions to scan. That’s before any actual data is read. By splitting into smaller tables (each roughly 4 TB instead of 30 TB), we cut that compilation overhead dramatically. Then clustering does its thing *within* the smaller table.

The combination of physical sharding + clustering + Search Optimization is what got us to 20ms. No single technique alone would have done it.

Clustering + Search Optimization: How We Chose What Goes Where

We spent two weeks analyzing six months of query logs from our ACCOUNT_USAGE views. Not glamorous work, but it told us exactly what predicates our users were actually filtering on.

The pattern was clear:

• Almost every query filters by merchant (first 6 chars of `MERCHANT_ID`)
• Date range is always present
• `CARD_TYPE` shows up in about 60% of queries

So that became our clustering key. For Search Optimization, we picked `CARD_NUMBER_HASH` a fully unique identifier that users search by exact match. Clustering can’t help with high-cardinality equality lookups like that. SOS can.

Results after applying both:

• Point-lookup latency: 500ms → 20-50ms
• Storage overhead from SOS: ~20% (we accepted this tradeoff)

One lesson: don’t apply SOS everywhere. We initially enabled it on every column that appeared in a WHERE clause. Storage costs shot up 40% with marginal latency improvement. Be surgical about it only high-cardinality equality predicates where you’re doing needle-in-haystack lookups.

Interactive Tables: Useful, But Not for Everything

My team was excited about Interactive Tables when Snowflake announced them. We tried using them for the main transaction path. It didn’t work for us. The 5-second query timeout was too restrictive for our edge cases, and the DML limitations made our rollover process impossible.

But they were perfect for one specific use case: merchant hierarchy/demographics. This is pre-joined reference data that our dashboard queries on nearly every request. It changes multiple times a day as merchants update their profiles.

Here’s how we set it up:

- Interactive Warehouse (required - IT tables can only be queried by these)
CREATE OR REPLACE INTERACTIVE WAREHOUSE APP_INTERACTIVE_WH
WAREHOUSE_SIZE = 'XSMALL'
COMMENT = 'Interactive warehouse for merchant hierarchy';


- The Interactive Table itself - auto-refreshes from source every 5 minutes
CREATE OR REPLACE INTERACTIVE TABLE APP.TMD_MERCHANT_HIERARCHY
CLUSTER BY (REGION, MERCHANT_ID)
TARGET_LAG = '5 minutes'
WAREHOUSE = MY_REFRESH_WH -- Standard WH handles the refresh compute
AS
SELECT
MERCHANT_ID,
PARENT_MERCHANT_ID,
MERCHANT_NAME,
REGION
FROM SOURCE_MERCHANT_TABLE;

- Bind and resume
ALTER WAREHOUSE APP_INTERACTIVE_WH ADD TABLES (APP.TMD_MERCHANT_HIERARCHY);
ALTER WAREHOUSE APP_INTERACTIVE_WH RESUME;

Important detail: the `WAREHOUSE` parameter in the Interactive Table DDL is for the *refresh* process. It must be a standard warehouse, not the interactive one.

We also tried static Interactive Tables first, but with data changing multiple times daily, we’d need to rebuild them via ETL each time. That gets expensive fast. The auto-refresh with `TARGET_LAG` was the right call.

APP Connection Pooling: The Bug That Wasn’t a Bug

This was a technical blocker before our final load testing. We had queries completing in 20ms on the Snowflake side (confirmed via query history), but the application was reporting 200-400ms end-to-end. Where was the extra latency?

Turns out it was connection establishment. Our Spring Boot app uses HikariCP, and the default settings are designed for steady-state traffic. Our traffic is bursty, merchants check their dashboards in waves, with 5-10 minute gaps of silence between bursts.

During those gaps, HikariCP was closing idle connections (default `idleTimeout` is 10 minutes). So when the next burst came in, every request had to re-establish a JDBC connection to Snowflake. That’s 100-300ms of pure overhead per request.

The fix was straightforward once we understood the problem:

HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setJdbcUrl("jdbc:snowflake://<account>.snowflakecomputing.com/…");
hikariConfig.setUsername("<user>");
hikariConfig.setPassword("<password>");
int maxPoolSize = 10;
hikariConfig.setMaximumPoolSize(maxPoolSize);
// Keep the pool full at all times - no connection spin-up during bursts
hikariConfig.setMinimumIdle(maxPoolSize);
// Don't let connections die between bursts
hikariConfig.setMaxLifetime(86400000); // 24 hours
// Still validate periodically to catch dead connections
hikariConfig.setIdleTimeout(600000); // 10 minutes
hikariConfig.setConnectionTimeout(30000);
DataSource dataSource = new HikariDataSource(hikariConfig);

Setting `minimumIdle = maximumPoolSize` means the pool never shrinks. Connections stay warm. End-to-end latency dropped from 300ms+ to under 50ms consistently.

Problems That Cost Us Sleep

Queries still slow despite good pruning: We had cases where scan time was 10ms but total query time was 2+ seconds. Compilation and queueing were the bottleneck. We worked with our Snowflake account team to get dedicated Global Services cluster allocation. This reduced noisy-neighbor effects significantly.

TRUNCATE + INSERT killed our Interactive Tables: Our upstream team was doing TRUNCATE + INSERT to refresh hierarchy data. This completely invalidated the Interactive Table’s incremental refresh. We switched them to MERGE (SCD Type 1 style) and everything started working properly.

New source columns broke everything: Someone added a column to the mainframe extract. Our stored procedures assumed positional column ordering. Lesson: always use explicit column names in INSERT and SELECT statements. Never trust positional order.

Mainframe data quality: Embedded newlines in VARCHAR fields, inconsistent quoting, intermittent PGP decryption failures. We fail the load entirely (`ON_ERROR = ‘ABORT_STATEMENT’`), alert the support team via email notification, and they coordinate with the upstream mainframe team to fix and resend the file.

Snowflake Partnership was crucial

1. Snowflake provisioned a dedicated Global Services (GS) Cluster for the warehouses that serve our users (which are used to query the APP layer) — eliminating cross customer interference
2. Snowflake scaled the GS Cluster capacity to align with our workload profile
3. Snowflake reduced the cluster spin up time to ~ 5 seconds to accommodate the query bursts
4. Optimized masking policies to lower query compilation time and improve overall efficiency

Learnings

After living with this system in production for months, here’s what I’d say if you’re building something similar:

1. Don’t reach for specialized features first. Standard tables with proper clustering and Search Optimization handled 98% of our performance needs. Start simple, measure, then optimize.
2. Look at your query logs before you design anything. We spent few weeks on ACCOUNT_USAGE analysis. It felt slow at the time. It saved us months of rework.
3. Physical sharding still matters in the cloud. At 30 TB, splitting tables by time window gave us a 7-8x reduction in search surface before Snowflake even starts pruning. That’s not theoretical, we measured it.
4. Interactive Tables are great for reference data, not for fact tables. The constraints (5s timeout, limited DML, required binding) make them unsuitable for high-volume transaction serving. But for pre-joined hierarchies that refresh throughout the day? Perfect.
5. Check your connection layer. We almost missed this completely. Table-level optimizations don’t matter if your app is spending 300ms re-establishing connections between queries.

Final Thoughts

This was the final state after a lot of iterations. It’s just disciplined engineering. Understand your workload, measure where time is actually spent, and optimize the hot path aggressively while leaving the rest alone.

The 25x improvement came from stacking multiple optimizations: physical sharding reduces the search surface, clustering accelerates pruning within that reduced surface, Search Optimization handles the needle-in-haystack lookups, views abstract the complexity away from consumers, and connection pooling eliminates application-layer overhead.

If you’re facing a similar migration: especially from a legacy accelerator to Snowflake, don’t try to replicate what the old system did. Instead, look at how your users actually query the data and build a serving architecture around that reality.

How We Got 25x Faster Queries on Snowflake (And What Actually Worked) was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.