Connect Snowflake to Conversation Intelligence

Overview

This guide explains how engineering teams can build a pipeline that extracts conversation data from Snowflake and sends it to Heap Conversational Intelligence through the CI API.

Snowflake remains the source of truth for raw conversation data. The integration sends data from Snowflake to the CI API in a one-way, idempotent flow. It doesn't read conversation data back into Snowflake. Status polling is used to monitor pipeline health, not to synchronize data.

Architecture overview

Key design principle: The pipeline is a one-way, idempotent push from Snowflake into the CI API. No data is read back into Snowflake by this integration.

Step 1: Complete the onboarding wizard

Before you build the pipeline, complete the Heap Conversational Intelligence setup wizard to generate API credentials.

1. Authenticate your Heap user. You'll receive an email inviting you to activate your Heap user account. Open the email, follow the link, and create your credentials. After verification, you're redirected to the Conversational Intelligence setup wizard.
2. Review and accept the Terms and Conditions. The welcome screen explains the setup. Review and accept the Terms and Conditions, select the acceptance checkbox, and click Let's get started.
3. Connect using the Conversational Intelligence API. Because this integration sends conversation data from Snowflake instead of using a supported CRM connector, select Use our API on the platform selection screen.

1. Generate credentials. On the Connect via Conversational Intelligence API screen, review the setup notes, open View API documentation so your engineering team can prepare, and click Generate credentials.
2. Save the credentials. On the Save your credentials screen, copy the API endpoint, Client ID, and Client secret from the Digital channels section. Select the acknowledgment checkbox and click Done.
3. Share the credentials securely. Share the credentials with your engineering team through an approved secrets manager, not through chat or email.

Connection status: This setup path doesn't include a live connection check in the wizard. The platform shows an In progress status until your engineering team starts sending data. Data is typically visible in the Heap UI within a few hours of the first successful submission.

Additional configuration

• CSAT normalization, optional: If you plan to populate the csat field, configure normalization rules through the CI API's CSAT settings endpoint before sending conversations. Otherwise, CSAT values won't be normalized correctly. Endpoint: POST https://live.loris.ai/ci/api/settings/csat/.
• Enrichment model volume: Client-specific enrichment models require ingestion of 40,000 conversations. Heap is notified automatically when that threshold is reached.
• Credential storage: Store the Client ID and Client secret in your organization's secrets manager, such as Snowflake external secrets integration, Vault, or AWS Secrets Manager. Never hard-code credentials or store them in plaintext configuration.

Step 2: Identify and prepare source data in Snowflake

Extract only conversations that are closed or resolved in the source system.

Important: The API doesn't support partial updates. Resending a source_id completely replaces the previous analysis. Filter on a closed or resolved status field, not on last-modified timestamp alone, to avoid resending in-progress conversations and losing existing enrichment.

Use an incremental watermark for extraction. The table name and column names below are illustrative placeholders. Replace them with your actual Snowflake schema.

SELECT
    ticket_id,
    customer_id,
    channel,
    ticket_created_at,
    csat_score,
    brand,
    priority,
    category
FROM support_tickets
WHERE status = 'CLOSED'
  AND closed_at > :last_successful_watermark
  AND closed_at <= :batch_cutoff_time
ORDER BY closed_at ASC;

This query illustrates conversation-level extraction, with one row per ticket. Each conversation also requires its messages, which are typically stored in a separate message-level table such as ticket_messages and joined using the ticket or conversation ID.

• Pull conversation-level fields and associated messages before assembling the final API payload.
• Sort messages by their timestamp before sending them to the API.
• Because resending a conversation replaces it completely, an occasional duplicate send after a failure is safe. Missed sends are the main risk to avoid.

Step 3: Map Snowflake fields to the CI API schema

Conversation-level fields

Message-level fields

Agent identity fields: author_name and author_email are set on the first send only and cannot be updated through this API. Make sure your extraction returns the correct current display name and email before the first time an author_id is sent.

Step 4: Authenticate your pipeline

The CI API uses the OAuth 2.0 client credentials flow.

Implementation guidance

• Cache the bearer token in memory or a short-lived cache, such as Redis with a 23-hour TTL, instead of requesting a new token for every API call.
• Build a wrapper around your API client that checks token expiry before each request.
• If a request returns 401, re-authenticate once and retry the original request.
• Alert if re-authentication fails, because this may indicate rotated or revoked credentials.
• Token revocation: When decommissioning an environment or rotating credentials, revoke the old token through POST https://live.loris.ai/ci/api/oauth/revoke_token/. Send the token, client_id, and client_secret as form-encoded values.

Step 5: Build and validate the request payload

• Endpoint:POST https://live.loris.ai/ci/api/conversation/
• Sort messages by created in ascending order before building the payload. The API rejects out-of-order messages with a 400 response. Timestamps may be tied, and array order breaks ties.
• Validate size limits on the client before sending. This allows the pipeline to fail before making a request that will return 400.

Step 6: Send requests within rate limits

• Rate limit: 100 requests per minute.
• Implement client-side throttling, such as a token-bucket limiter, instead of relying only on 429 responses. This keeps the pipeline predictable at scale.
• When a request returns 429, apply exponential backoff and retry. Track rate-limit hits as a metric so you can identify pipelines that are under-provisioned for their conversation volume.
• For large backlogs, including an initial load for the 40,000-conversation enrichment threshold, throttle deliberately over time instead of sending a burst. At 100 requests per minute, 40,000 records require a multi-hour job.

Step 7: Handle responses and errors

Capture source_id, HTTP status, the raw error payload, and the timestamp. This makes validation failures queryable and prevents errors from disappearing silently from the pipeline.

Step 8: Poll for processing status

• Endpoint:GET https://live.loris.ai/ci/api/conversation-status/?source_id={source_id}
• Poll asynchronously after submission. Do not block the ingestion pipeline while waiting for processing status.
• Expected processing time: Approximately 2 to 3 hours.
• Recommended polling cadence: Every 30 to 60 minutes for each pending source_id. Avoid tight polling to stay within rate limits.

Step 9: Test with dry-run before go-live

Use the following header for all pre-production validation:

Dry-Run: true

Dry-run requests run the full pipeline, including schema validation, translation, and PII redaction, without persisting data. Use them to validate Snowflake-to-API mapping against realistic API feedback before go-live.

Step 10: Secure your credentials

• Store the Client ID, Client secret, and bearer tokens in a secrets manager. Do not store them in Snowflake tables, source code, or plaintext orchestration variables.
• Client secret: The client_secret is shown only once during onboarding. The create_client_oauth_credentials operation returns it in plaintext, and only a hash is stored afterward.
• Bearer tokens: Bearer tokens are short-lived, expire after 24 hours, and are created on demand from the client_id and client_secret through the client_credentials grant. They are not one-time credentials.
• Revoke tokens immediately when decommissioning an environment, rotating credentials, or responding to a suspected compromise. Do not wait for natural expiry.

Step 11: Monitor the pipeline in production

Track the following metrics to monitor pipeline health:

• Submission success rate, including 200 responses compared with 400 and 500 responses per batch.
• Rate-limit hit rate, including the number of 429 responses.
• Re-authentication frequency. Unexpected increases may indicate token-caching issues.
• Status distribution over time, including pending, complete, and failed conversations.
• Time to complete. This should remain close to the documented 2 to 3 hour processing time. Sustained deviation should be escalated to Heap.
• Dead-letter table growth rate, including validation error volume.

Revoke a token

If a bearer token is compromised, or you're decommissioning an integration, revoke the token instead of waiting for it to expire.

Send a POST request to https://live.loris.ai/ci/api/oauth/revoke_token/.

Send token, client_id, and client_secret as application/x-www-form-urlencoded fields in the request body. The endpoint always returns 200, whether or not the token existed. An application can only revoke tokens issued to itself.