Enrich a Snowflake Table With LeadOcean

Q: How do I enrich by name and company?

You cannot. Enrich takes one of linkedin_url, email, phone or person_id. Search by domain first, then enrich by the person_id it returns.

You enrich a Snowflake table with a Python stored procedure that calls LeadOcean once per pending row and updates that row. A Snowflake task runs the procedure on a schedule, so new rows are enriched without anyone touching them.

Snowflake code can only reach the internet through an external access integration (Snowflake docs, September 2026). Steps 2 and 3 set that up. Your API key lives in a Snowflake secret, never in the procedure text.

TL;DR

It takes about 40 minutes: 10 for the table, 15 for the integration and procedure, 15 to test on a small batch.
It spends 1 record per matched row. A batch of 20 costs up to 20 of your 1,000 free records. A row we hold nothing for is a 404 and costs 0.
Store linkedin_url or email on every person row, and domain on every company row. There is no name plus domain lookup.

Prerequisites

A Snowflake account where you can use a role with CREATE NETWORK RULE, CREATE SECRET, CREATE PROCEDURE and CREATE TASK on one schema, plus CREATE INTEGRATION on the account (an admin role).
A virtual warehouse for the task.
A LeadOcean API key from app.leadocean.io. The free plan has no card and 1,000 records, one-off.
The key in your shell as $LEADOCEAN_API_KEY. Test it before you paste it into Snowflake.

bash

export LEADOCEAN_API_KEY="paste-your-key-here"
curl -s https://api.leadocean.io/v1/account -H "x-api-key: $LEADOCEAN_API_KEY"

Step 1: Add the columns the job writes to

Keep the raw answer in a VARIANT column and pull out the fields you query. Rows start with ENRICHED_AT empty, which is how the job finds its work.

sql

CREATE TABLE IF NOT EXISTS leads (
  id            NUMBER AUTOINCREMENT,
  linkedin_url  STRING,
  email         STRING,
  enriched_at   TIMESTAMP_NTZ,
  enrich_status STRING,        -- matched | not_found
  work_email    STRING,
  email_status  STRING,
  phone         STRING,
  profile       VARIANT
);

CREATE TABLE IF NOT EXISTS companies (
  id          NUMBER AUTOINCREMENT,
  domain      STRING NOT NULL,
  enriched_at TIMESTAMP_NTZ,
  company     VARIANT
);

INSERT INTO leads (linkedin_url) VALUES ('https://www.linkedin.com/in/janedoe-example');
INSERT INTO companies (domain) VALUES ('acme.com');

Both rows are placeholders. Swap in your own data.

Step 2: Create the network rule, secret and integration

The network rule allows one host. The secret holds the key. The integration ties them together. Snowflake documents each object in its external access guide, September 2026.

sql

CREATE OR REPLACE NETWORK RULE leadocean_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.leadocean.io');

CREATE OR REPLACE SECRET leadocean_key
  TYPE = GENERIC_STRING
  SECRET_STRING = 'paste-your-key-here';

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION leadocean_integration
  ALLOWED_NETWORK_RULES = (leadocean_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (leadocean_key)
  ENABLED = TRUE;

Run the SECRET_STRING line once in a worksheet and do not commit it to Git. The procedure reads the key at run time, so it never appears in the procedure body.

Step 3: Write the stored procedure

The procedure takes pending rows, calls POST /v1/people/enrich with reveal_email true, and writes each result back. Snowflake's secret API returns the key inside the handler (September 2026).

sql

CREATE OR REPLACE PROCEDURE enrich_pending(batch_size NUMBER)
  RETURNS VARIANT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python', 'requests')
  HANDLER = 'run'
  EXTERNAL_ACCESS_INTEGRATIONS = (leadocean_integration)
  SECRETS = ('cred' = leadocean_key)
AS
$$
import json, time, requests, _snowflake

API = "https://api.leadocean.io"

def call(path, body, key):
    for _ in range(3):
        r = requests.post(API + path, json=body, timeout=30,
                          headers={"x-api-key": key})
        if r.status_code != 429:
            break
        time.sleep(int(r.headers.get("Retry-After", "1")))
    if r.status_code == 404:
        return None
    if r.status_code in (401, 402, 429):
        raise Exception(f"stop:{r.status_code}")
    r.raise_for_status()
    return r.json()["data"]

def run(session, batch_size):
    key = _snowflake.get_generic_secret_string("cred")
    people = companies = 0

    for row in session.sql(
        "SELECT id, linkedin_url, email FROM leads "
        "WHERE enriched_at IS NULL LIMIT " + str(int(batch_size))
    ).collect():
        ident = ({"linkedin_url": row["LINKEDIN_URL"]} if row["LINKEDIN_URL"]
                 else {"email": row["EMAIL"]})
        d = call("/v1/people/enrich", {**ident, "reveal_email": True}, key)
        if d is None:
            session.sql("UPDATE leads SET enriched_at = CURRENT_TIMESTAMP(), "
                        "enrich_status = 'not_found' WHERE id = ?",
                        params=[row["ID"]]).collect()
        else:
            c = d.get("contact_data") or {}
            emails = c.get("contact_emails") or [{}]
            phones = c.get("contact_phones") or [{}]
            session.sql(
                "UPDATE leads SET enriched_at = CURRENT_TIMESTAMP(), "
                "enrich_status = 'matched', work_email = ?, email_status = ?, "
                "phone = ?, profile = PARSE_JSON(?) WHERE id = ?",
                params=[emails[0].get("email"), c.get("email_status"),
                        phones[0].get("phone"),
                        json.dumps(d.get("profile_data")), row["ID"]]).collect()
            people += 1
        time.sleep(0.25)

    for row in session.sql(
        "SELECT id, domain FROM companies WHERE enriched_at IS NULL LIMIT "
        + str(int(batch_size))
    ).collect():
        d = call("/v1/companies/enrich", {"domain": row["DOMAIN"]}, key)
        session.sql("UPDATE companies SET enriched_at = CURRENT_TIMESTAMP(), "
                    "company = PARSE_JSON(?) WHERE id = ?",
                    params=[json.dumps(d), row["ID"]]).collect()
        if d is not None:
            companies += 1
        time.sleep(0.25)

    return {"people": people, "companies": companies}
$$;

The sleep keeps you under 4 requests a second, below the 5 a second on Free. A 429 waits the Retry-After seconds and tries again, up to 3 times. A 401, 402 or a third 429 raises and ends the run, so a bad key or spent records never loops. Rows already updated stay updated, and untouched rows are picked up next time.

Step 4: Test on a small batch

Call the procedure by hand on a few rows before you schedule it.

sql

CALL enrich_pending(3);
SELECT id, enrich_status, work_email, email_status FROM leads;

Step 5: Schedule it with a task

A task runs a SQL statement on a schedule. The CREATE TASK reference (September 2026) notes that new tasks start suspended, so resume it.

sql

CREATE OR REPLACE TASK enrich_leads_hourly
  WAREHOUSE = my_wh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  CALL enrich_pending(100);

ALTER TASK enrich_leads_hourly RESUME;

At 100 rows an hour, the job spends at most 100 records an hour. Lower the batch size to cap spend on the free plan.

What you get

After a run, each matched row has the best email, its status and a phone if we hold one. This is the response shape from the API reference, with placeholder values.

json

{
  "success": true,
  "data": {
    "profile_data": { "profile_full_name": "Jane Doe", "profile_headline": "VP Sales at Acme" },
    "contact_data": {
      "has_email": true,
      "email_status": "verified",
      "contact_emails": [{ "email": "jane.doe@acme.com", "type": "work", "status": "verified" }],
      "contact_phones": [{ "phone": "+1 415 555 0133", "type": "mobile" }],
      "contact_current_experiences": [{ "company_name": "Acme", "job_title": "VP Sales" }]
    },
    "resume_data": { "experiences": [], "educations": [] }
  }
}

Person enrich also returns career history in resume_data: past roles, education and certifications, each when held. Past employers carry no company_id yet, so join them to your own tables by name.

What it costs

One record is one person or one company you receive. A matched people enrich is 1 record, and a matched company enrich is 1 record. A 404, a count and an empty page cost nothing, and reveal_email adds no extra record.

On Free, the 1,000 records are one-off, so the hourly task stops with stop:402 once they are spent. Paid plans have no credits. Solo is $97 a month and Agency is $497 a month, each with a fair-use pace. Reaching the pace slows the key until the window resets; nothing stops. See pricing.

Only mail rows whose email_status is verified or catch_all_valid. LeadOcean gives no refund for bounced emails.

Troubleshooting

Error	Cause	Fix
`stop:401`	The key in the secret is wrong or revoked.	Recreate the secret with a key from app.leadocean.io.
`stop:429`	You went above your plan's rate limit: 5 a second on Free, 200 on Agency.	Raise the `sleep`, or move to a plan with a higher limit.
`stop:402`	The plan is out of records.	Check usage with `GET /v1/usage`, then start a paid plan.
Request blocked or host not allowed	The integration does not list `api.leadocean.io`, or is disabled.	Check the network rule `VALUE_LIST` and `ENABLED = TRUE`.
Task never runs	New tasks start suspended.	Run `ALTER TASK enrich_leads_hourly RESUME`.

FAQ

Does this cost warehouse credits?

The task uses the warehouse you name while it runs, so Snowflake bills that time. LeadOcean bills nothing extra: you spend records only on matched rows.

How do I enrich by name and company?

You cannot. Enrich takes one of linkedin_url, email, phone or person_id. Search by domain first, then enrich by the person_id it returns.

Can I enrich millions of rows this way?

Not on the free plan. The procedure calls one row at a time, so its speed is set by the sleep and each call's round trip. Agency allows 1M enrichments a day and 200 requests a second. See pricing before you size a large backfill.

Where are the other warehouses?

The same pattern works elsewhere. See the guides for Postgres, Databricks and BigQuery. More recipes are in the blog hub.

Enrich your Snowflake table with a free LeadOcean key

Free to start. No credit card. 1,000 records to spend whenever you like.

Get your free API key →