Skip to content

// automation and data tools

Via API

Snowflake email verification with pingutil

Snowflake is where signups, leads and customer records end up, including every disposable and mistyped address the front end let through, and those rows skew activation metrics and get synced back out to email tools. A Python UDF behind an external access integration can call pingutil for new addresses and store category, spam verdict and verification status in a table your models join to.

Where it connects: External access integration with a network rule, a secret, and a Python UDF that calls pingutil

What goes wrong without it

  • Our activation dashboards count thousands of temp-mail signups as real users.
  • Reverse ETL syncs warehouse audiences to the email tool, bounces and all.
  • Data engineering keeps a hand-maintained disposable domain table that is always out of date.

What you can do with Snowflake and pingutil

  • Classify new signups daily

    A scheduled task runs the UDF on yesterday's new addresses only and inserts category and spam flags into an email_classification table.

  • Exclude fake users from metrics

    Join models to the classification table and filter is_spam = true out of activation, retention and trial-conversion numbers.

  • Clean audiences before reverse ETL

    Verify the addresses in an audience before it syncs to the email platform, and drop rows whose status is invalid.

Set it up

  1. 1

    Create an egress network rule for pingutil.com:443 and a GENERIC_STRING secret holding your pingutil key.

  2. 2

    Create an external access integration that allows that network rule and secret, and grant usage to the role that will own the UDF.

  3. 3

    Create the Python UDF below with the integration, the secret and the requests package.

  4. 4

    Call it from an INSERT INTO ... SELECT over addresses not yet in your results table, never from a view that recalculates.

  5. 5

    Schedule the insert with a task, and keep each run under the account's daily lookup budget.

Snowflake SQL: network rule, secret, integration and UDF
CREATE OR REPLACE NETWORK RULE pingutil_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('pingutil.com:443');

CREATE OR REPLACE SECRET pingutil_key
  TYPE = GENERIC_STRING
  SECRET_STRING = 'pk_live_REPLACE_ME';

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION pingutil_access
  ALLOWED_NETWORK_RULES = (pingutil_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (pingutil_key)
  ENABLED = TRUE;

CREATE OR REPLACE FUNCTION pingutil_classify(email STRING)
  RETURNS VARIANT
  LANGUAGE PYTHON
  RUNTIME_VERSION = 3.12
  HANDLER = 'classify'
  EXTERNAL_ACCESS_INTEGRATIONS = (pingutil_access)
  PACKAGES = ('requests')
  SECRETS = ('cred' = pingutil_key)
AS $$
import _snowflake
import requests

session = requests.Session()

def classify(email):
    key = _snowflake.get_generic_secret_string('cred')
    try:
        r = session.post(
            'https://pingutil.com/v1/lookup',
            json={'input': email, 'skip_enrichment': True},
            headers={'Authorization': 'Bearer ' + key},
            timeout=10,
        )
    except requests.RequestException:
        return None
    if r.status_code != 200:
        return None  # 429 or 5xx: leave unclassified, retry next run
    d = r.json()
    return {
        'category': d['category'],
        'is_spam': d['verdict']['is_spam'],
        'reason': d['verdict']['reason'],
    }
$$;

-- Classify only addresses not seen before, a bounded batch per run
INSERT INTO email_classification (email, result, checked_at)
SELECT email, pingutil_classify(email), CURRENT_TIMESTAMP()
FROM (
  SELECT DISTINCT u.email
  FROM users u
  LEFT JOIN email_classification c ON c.email = u.email
  WHERE c.email IS NULL
  LIMIT 500
);

What to do with each result

pingutil resultAction in Snowflake
categoryresult:category column for joins and metric filters
verdict.is_spam = trueExclude from activation metrics and reverse ETL audiences
status = "invalid" (if you call /v1/verify instead)Drop from email audiences before the sync
NULL result (error or 429)Delete the row or leave it for the next scheduled run

Limits to plan for

  • Snowflake runs UDFs in parallel, which can burst past pingutil's 60 requests a minute; keep batches small and treat NULL results as retry-later.
  • pingutil budgets are 2,000 lookups and 300 verified addresses per account per day (30,000 and 3,000 a month), so warehouse-scale backfills take several days. Those are Free plan limits; Enterprise has custom limits.
  • There is no bulk lookup endpoint; only /v1/verify/batch accepts up to 50 addresses per call.

Snowflake documentation

Snowflake is a trademark of its owner. This integration uses Snowflake's documented extension points; it is not an official Snowflake app.

Common questions

Can I call the UDF from a dbt model or view?
Avoid it. Views and full-refresh models recompute and re-spend your daily budget. Store results in a table and only classify new addresses.
Does this check whether mailboxes exist?
Not in this form. It classifies the address. Point the UDF at /v1/verify for mailbox checks, but expect up to about 25 seconds per address and a 300 a day budget (3,000 a month).

Stop bad addresses in Snowflake today

Sign in with your email, create an API key and paste it into the step above. Test on a few real addresses first: the live demo shows exactly what your Snowflake setup will receive.