LinkedIn to Snowflake Integration Setup Procedure

1. Purpose

This SETUP PROCEDURE documents the approved and proven process for integrating LinkedIn APIs with Snowflake using:

• Snowflake External Access Integrations
• Network Rules for outbound traffic
• Snowflake Secrets for OAuth credentials
• Python Snowpark stored procedures
• OAuth 2.0 (3-legged) refresh-token flow

This document exists to prevent:

• Repeated trial-and-error with LinkedIn permissions
• Misconfigured Snowflake secrets
• CSP/OAuth browser issues
• Improper use of deprecated LinkedIn endpoints

---

2. Architecture Overview

LinkedIn API
    ↑
OAuth 2.0 (Refresh Token)
    ↑
Snowflake Secret (JSON)
    ↑
External Access Integration
    ↑
Python Stored Procedure (Snowpark Runtime)

Key Principle:
Snowflake is the only system that ever handles the refresh token. No client-side refresh or embedded credentials are allowed.

---

3. LinkedIn Developer App Setup

3.1 App Type

OAuth 2.0 – 3-Legged (Member Authentication)

The authenticating LinkedIn user must be:

• Page Admin (for org data)
• Event Admin (for events)
• Ad Account Admin (for ads)

3.2 Scopes Confirmed Working

Scope	Purpose
r_liteprofile	Basic member identity
r_profile_basicinfo	Member profile metadata
r_verify	Verified on LinkedIn
r_events	Read organization events
rw_events	Create/update events
r_ads	Read advertising accounts
rw_conversions	Conversion tracking
w_member_social	Post as a member only

Organization posts require the Community Management API, which must be the only product on the app. This requires a separate LinkedIn application.

4. OAuth Consent & Token Generation

4.1 Browser Requirement (Critical)

OAuth consent failed silently in Chrome due to:

• Content Security Policy (CSP)
• Ad/script blockers

Use Mozilla Firefox or disable all blockers during OAuth consent.

4.2 Token Strategy

• Obtain a long-lived refresh token
• Never store access tokens manually
• Refresh tokens are stored only in Snowflake Secrets

---

5. Snowflake Network Configuration

Snowflake blocks all outbound traffic by default. LinkedIn endpoints must be explicitly allowed.

5.1 Create Network Rule

CREATE OR REPLACE NETWORK RULE LINKEDIN_NETWORK_RULE
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = (
  'api.linkedin.com:443',
  'www.linkedin.com:443'
);

Notes:

• Network rules are account-level
• Required for all LinkedIn API calls

---

6. Snowflake Secret Configuration (Critical Pattern)

6.1 Why a Single JSON Secret Is Required

• ❌ Multiple OAuth secrets not supported
• ❌ No OAUTH_CLIENT_SECRET property
• ✅ Single allowed secret reference (mandatory)

6.2 Correct Secret Format (Validated)

CREATE OR REPLACE SECRET STAGE_DB_DEV.LINKEDIN.LINKEDIN_CLIENT_SECRET
TYPE = GENERIC_STRING
SECRET_STRING = '{
  "client_id": "<CLIENT_ID>",
  "client_secret": "<CLIENT_SECRET>",
  "refresh_token": "<REFRESH_TOKEN>"
}';

---

7. External Access Integration

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION LINKEDIN_EAI_STRSI
ALLOWED_NETWORK_RULES = (LINKEDIN_NETWORK_RULE)
ALLOWED_AUTHENTICATION_SECRETS = (
  {STAGE_DB}.LINKEDIN.LINKEDIN_CLIENT_SECRET
)
ENABLED = TRUE;

Common Errors Prevented:

• Secret not whitelisted
• Missing or misnamed network rule

---

8. Token Management Strategy (Required)

8.1 Token Storage

Table: STAGE_DB_DEV.LINKEDIN.LINKEDIN_ACCESS_TOKEN_LOG

Fields:

• RUN_TS
• TOKEN_JSON
• TOKEN_STATUS

8.2 Token Usage Pattern (Mandatory)

Every stored procedure must:

• Pull latest successful token
• Check expiration (expires_in)
• Refresh if expired
• Retry API call once on 401/403

---

9. Stored Procedure Requirements

CREATE PROCEDURE procedure_name()
LANGUAGE PYTHON
RUNTIME_VERSION = '3.10'
PACKAGES = ('snowflake-snowpark-python','requests')
EXTERNAL_ACCESS_INTEGRATIONS = (LINKEDIN_EAI_STRSI)
SECRETS = ('LI_OAUTH' = STAGE_DB_DEV.LINKEDIN.LINKEDIN_CLIENT_SECRET)
EXECUTE AS OWNER;

9.1 Secret Access (Python)

import _snowflake, json

raw = _snowflake.get_generic_secret_string("LI_OAUTH")
creds = json.loads(raw)

_snowflake is only available inside stored procedures.

---

10. Endpoints Confirmed Working

Scope	Endpoint	Status
r_liteprofile	/v2/me	✅
r_profile_basicinfo	/v2/me	✅
r_events	/rest/events?q=eventsByOrganizer	✅
Token Refresh	/oauth/v2/accessToken	✅

Required Headers for /rest/*:

• Authorization: Bearer <token>
• X-Restli-Protocol-Version: 2.0.0
• Linkedin-Version: 202505
• X-RestLi-Method: FINDER

---

Must be re-validated and documented.