Connect Snowflake to Conversational Intelligence

Overview

This guide explains how engineering teams can build a pipeline that extracts conversation data from Snowflake and sends it to Contentsquare Conversation 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 Contentsquare Conversation Intelligence setup wizard to generate API credentials.

1. Authenticate your Contentsquare user. You'll receive an email inviting you to activate your Contentsquare user account. Open the email, follow the link, and create your credentials. After verification, you're redirected to the Conversation 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 Conversation 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 Conversation 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 Contentsquare 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. Contentsquare 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

Snowflake source	CI API field	Notes
ticket_id, plus a channel and index suffix when one ticket maps to multiple conversations	source_id	Must be unique per client and no more than 63 characters. Use {ticket_id}_{channel}_{index} for one-to-many mappings.
customer_id	crm_user_id	Required.
ticket_created_at, converted to UTC ISO 8601	created	Required. Include a timezone indicator.
channel, mapped to an allowed enum	channel	Map source values to EMAIL, CHAT, SMS, WHATSAPP, and other allowed values.
parent_ticket_id	related_ticket_id	Required only when one ticket maps to multiple conversations.
csat_score	csat	Requires CSAT normalization to be configured during onboarding.
brand	brand_name	The brand is created automatically if it is new.
priority, category, and similar attributes	tags	Use {tag_name: [values]}. Values must always be arrays, including for one value.
Other filterable attributes	custom_fields	Values must be flat strings, integers, or floats. Nested objects aren't supported.
csq_user_id, optional	csq_user_id, optional	Contentsquare-side user identifier, if available.

Message-level fields

Snowflake source	Message field	Notes
message_direction	message_type	Map to IN, OUT-HUMAN, OUT-BOT, or OUT-AUTO-REPLY.
message_text	message_body	Attachments aren't supported. Represent an attachment with the [FILE_UPLOAD] marker in the text.
message_created_at, converted to UTC ISO 8601	created	Must be non-decreasing across the message array. Sort before sending.
agent_id	author_id	Required for all OUT-* message types.
agent_display_name	author_name	Required for all OUT-* message types.
agent_email	author_email	Required for all OUT-* message types.

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.

Setting	Value
Token endpoint	POST https://live.loris.ai/ci/api/oauth/token/
Grant type	client_credentials only. The refresh_token grant is rejected.
Token lifetime	24 hours. No refresh token is issued.
Renewal	Send another POST request with the same Client ID and Client secret when the token expires or when the API returns a 401 response.

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.

Limit	Requirement
Non-English conversation text	No more than 100 KB in UTF-8 format.
Individual message	No more than 5 KB.
Total request	No more than 2.5 MB.
Messages per conversation	Between 1 and 300 messages.

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

Status	Condition	Meaning	Response body
200	Success	The request was accepted.	Empty body.
400	Schema validation failure	The request doesn't match the schema.	{"errors": [{"loc": [...], "msg": "...", "type": "..."}]}
400	CSAT is not configured	The request includes a csat value, but CSAT normalization hasn't been configured.	{"error": "<message>"}
400	Missing source_id	The status request doesn't include the required query parameter.	{"success": false, "error_message": "source_id query parameter is required"}
401	Authentication failure	The bearer token is invalid, missing, or expired.	Request a new token from the token endpoint.
404	Conversation not found	No conversation exists for the supplied source_id.	{"success": false, "error_message": "No conversation found for source_id <id>"}
429	Rate limit exceeded	The request rate is above the API limit.	Reduce the request rate and try again.
500	Processing failure	Translation or PII redaction failed while processing an otherwise valid request.	{"error": "<message>"}

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.

Status	Pipeline action
pending	Continue polling.
complete	Mark the conversation as complete in the tracking table.
failed	Capture the error field and route the conversation to a dead-letter table.

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 Contentsquare.
• 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.