Pisano - Snowflake Integration

What You Will Find Here

This article covers:

• Prerequisites
• Integration Options
• Platform Capabilities Used in the Integration
• Snowflake to Pisano Event Driven Integration
• Scheduled Snowflake Audience
• Pisano Feedback to Snowflake
• File Based Integration
• Data Mapping and Identity
• Security, Rate Limits, and Operations
• Testing and Acceptance
• Decisions Before Implementation

Prerequisites

Before implementation, the following requirements should be defined and confirmed:

• Define a stable event or invitation key and the business rule that determines when a customer is eligible for a survey.
• Agree on the customer or person ID, transaction or employment ID, survey purpose, due time, and repeat invitation rules.
• Confirm whether the Snowflake source is an immutable event table or a mutable business table.
• Provide the required source, outbox, and result tables.
• Define the Snowflake compute model, such as a warehouse or serverless task.
• Assign the required roles and permissions for streams, tasks, procedures, secrets, network rules, and External Access Integration.
• Provide the Pisano environment host, integration credential, survey flow or channel, and exact API contract for the selected endpoint.
• For feedback ingestion, agree on the Pisano account and flow scope, collection method, either webhook or API, and target Snowflake schema.

Integration Options

The integration can use four main patterns:

The appropriate option depends on the required direction, latency, data volume, and operating model.

Integration Flow

For an event driven outbound flow, the data path is:

For feedback ingestion, the data path is:

Platform Capabilities Used in the Integration

Snowflake

The integration can use the following Snowflake capabilities:

• Streams expose table changes between transactional offsets and can capture new or changed business data.
• Tasks can run on a schedule or based on a stream condition.
• Stored procedures can call approved external HTTPS services through External Access Integration. Credentials can be stored in Snowflake Secrets.
• SQL API allows external systems to execute approved SQL statements for feedback loading and related integration work.
• Openflow provides HTTP and SFTP processors in a separately deployed runtime when this operating model is selected.

Pisano

Pisano provides several capabilities that can support the integration:

• Email Sharing API starts survey email delivery through the configured provider.
• SMS Sharing API starts survey SMS delivery through the configured provider.
• Link Sharing API returns one personalised survey link per customer for delivery by an external sender.
• Webhook can push feedback events to a configured receiver.
• Feedback Retrieval API can collect feedback on a schedule or support backfill.
• File export and workflow capabilities can support batch integration when a file based model is preferred.

Snowflake to Pisano Event Driven Integration

Event driven survey initiation separates event capture from API dispatch. The capture task first stores eligible work in a durable outbox. A separate dispatcher then sends due requests to Pisano.

This separation prevents a temporary HTTP problem from being tied to the Snowflake stream offset. It also allows retries to continue when no new source event arrives.

Capture and Dispatch

The integration follows these steps:

• The producer writes a stable event ID and required request data to the agreed Snowflake source or event table.
• A Stream exposes new changes.
• A triggered task captures eligible events and stores them in an outbox table.
• The outbox stores the payload, processing state, attempt information, and next retry time.
• A scheduled dispatcher selects due PENDING or RETRY records.
• The dispatcher validates the record and calls the selected Pisano endpoint.
• The result is stored as ACCEPTED, RETRY, UNKNOWN, FAILED, or SUPPRESSED.

These states provide controlled replay and recovery.

Notes

• When every business event must be preserved, an append only event table with an immutable event ID is preferred.
• For mutable source tables, define which post update state qualifies for an invitation.
• Define how cancellations affect pending invitations.

Secure External Access

A Snowflake stored procedure can act as the dispatcher. External Access Integration restricts outbound network access to the approved Pisano host, while the Pisano credential is stored in a Snowflake Secret.

The procedure must bind the External Access Integration and Secret and use the exact Authorization format verified for the selected Pisano endpoint.

The resulting flow is:

Outbox → Stored procedure or dispatcher → External Access Integration → Secret → Pisano API

Pisano Target APIs

Email Sharing API

Endpoint:

POST https://<pisanoURL>/v1/email_campaigns/<campaign_id>/email_sharings/

Use this endpoint when Pisano should initiate survey email delivery. Provider delivery is tracked separately from API acceptance.

curl --location 'https://<pisanoURL>/v1/email_campaigns/<campaign_id>/email_sharings/' \
--header 'Authorization: <token>' \
--header 'Content-Type: application/json' \
--data '{
  "emails": ["<email>"],
  "custom_attributes": {
    "<email>": {
      "name": "<name>",
      "external_id": "<external_id>"
    }
  },
  "transactional_data": {
    "<email>": {
      "EventType": "<event_type>",
      "TransactionId": "<transaction_id>"
    }
  }
}'

SMS Sharing API

Endpoint:

POST https://<pisanoURL>/v1/sms_campaigns/<campaign_id>/sms_sharings/

Use this endpoint when Pisano should initiate survey SMS delivery. The customer phone number and SMS provider or channel configuration are required.

curl --location --request POST 'https://<pisanoURL>/v1/sms_campaigns/<campaign_id>/sms_sharings/' \
--header 'Authorization: <token>' \
--header 'Content-Type: application/json' \
--data-raw '{
  "phone_numbers": ["<phone_number>"],
  "custom_attributes": {
    "<phone_number>": {
      "name": "<name>",
      "external_id": "<external_id>",
      "email": "<email>"
    }
  },
  "built_in_responses": {
    "<phone_number>": {
      "EventType": "<event_type>",
      "TransactionId": "<transaction_id>"
    }
  }
}'

Link Sharing API

Endpoint:

POST https://<pisanoURL>/external/v1/link_sharings/<link_channel_id>/generate_link

Use this endpoint when an external system will deliver the invitation. Pisano returns a personalised survey URL.

Example request body:

{
  "customer": {
    "email": "customer@example.com",
    "external_id": "CUST-12345"
  },
  "built_in_responses": {
    "EventType": "Purchase Completed",
    "TransactionId": "TRX-98765"
  },
  "options": {
    "shorten_url": true
  }
}

Scheduled Snowflake Audience

A scheduled Snowflake task can select an hourly, daily, or weekly audience and write due invitations into the same outbox used by the event driven flow.

The same dispatcher, rate control, and recovery rules can then be reused.

Notes

• For a complete audience snapshot, suppress invitations that were already accepted during the current programme period.
• For a delta audience, define the selection watermark and handling of late records.
• Do not advance the business checkpoint until invitation work has been stored durably.
• Use an explicit time zone for schedules and agree on daylight saving behavior.

Pisano Feedback to Snowflake

Webhook Based Feedback Ingestion

Pisano can send feedback events as JSON to a configured receiver.

The receiver should:

• Authenticate the request.
• Validate the payload.
• Persist the feedback event.
• Return success only after the event has been persisted.
• Load the persisted record into Snowflake.

A stable feedback ID and source or account context should be retained so duplicate records can be reconciled.

The webhook flow is:

Pisano feedback → Webhook receiver → Durable queue or store → Snowflake load → Analytics table

Feedback Retrieval API

The Feedback Retrieval API can be used for scheduled collection and backfill.

Flow discovery endpoint:

GET https://<pisanoURL>/external/v1/flows/?node_id=<node_id>

Use this endpoint to discover the relevant flow scope for the account or node.

Feedback endpoint:

GET https://<pisanoURL>/external/v1/feedback?flow_id=<flow_id>&from=<timestamp>&to=<timestamp>&page=<n>

Use this endpoint to collect feedback pages for a fixed time window.

The documented limits are:

• Maximum 20 feedback records per response
• Maximum 10 requests per 5 seconds

Notes

• Advance the collector checkpoint only after all pages in the selected window have been safely persisted and loaded.
• Use the same feedback key for webhook ingestion and API backfill so the two paths do not create duplicates.

Loading Feedback into Snowflake

The receiver or collector can load Snowflake through the SQL API or an agreed batch loading route.

For SQL API usage:

• Authenticate with an approved Snowflake method.
• Use reviewed SQL with bound values.
• Check statement completion before marking the feedback as loaded.

Snowflake SQL API endpoint:

POST https://<account_identifier>[.snowflakecomputing.com/api/v2/statements/](https://.snowflakecomputing.com/api/v2/statements/)

This endpoint is used for approved statement execution.

For larger sustained volumes, a batch or staged file load may be more appropriate than row by row SQL statements.

File Based Integration

A file based integration can be used when batch processing is preferred.

Pisano can export feedback files and can also read configured file sources through Workflow.

Snowflake external stages are intended for supported cloud storage backends rather than generic SFTP. When SFTP is required, use a managed transfer process or an Openflow runtime.

For any file based integration, define:

• Exact transfer protocol
• File format
• Completion marker or final rename
• Row counts
• Replay key
• Archive policy
• Complete loading and delivery chain

A file transfer alone does not constitute the complete integration.

Data Mapping and Identity

The following data should have defined integration rules:

Security, Rate Limits, and Operations

The integration should follow these security and operational requirements:

• Store Pisano credentials in a Snowflake Secret or the selected integration platform secret store.
• Restrict external access to the approved host.
• Verify TLS.
• Use stable invitation and feedback keys.
• Maintain persistent processing states so replays and retries do not silently create duplicates.
• Treat an uncertain timeout or connection loss separately from a confirmed failure that is safe to retry. A request may already have reached Pisano.
• Do not log credentials, unrestricted HR or customer data, or personalised survey URLs.

Published Rate Limits

Effective limits should be confirmed in the target environment for Email Sharing.

Notes

• API acceptance does not confirm email or SMS delivery.
• API acceptance does not confirm completion of feedback processing.
• These outcomes should be monitored separately.

Testing and Acceptance

Before production use, test the integration against representative scenarios.

Event Driven Flow

Test:

• New event
• Replayed event
• Duplicate ID
• Cancelled or updated source state
• Empty stream condition

Recovery

Confirm that a Pisano dispatch failure is retried even when no new Snowflake source event arrives.

API Contract

Verify:

• Correct host
• Correct credential header
• Correct channel
• Correct schema codes and data types
• Returned survey link
• Accepted Email or SMS request

Uncertain Outcome

Simulate a remote acceptance followed by a lost response. The item should be held for reconciliation rather than automatically resent.

Feedback Ingestion

Test:

• Pagination beyond 20 records
• Failure on a later page
• Replay and backfill
• Duplicate and update reconciliation

Volume

Test with a representative peak audience and measure:

• Rate windows
• Queue age
• Processing duration
• Source to outcome counts

Decisions Before Implementation

The following decisions should be confirmed before implementation begins:

• Direction: Snowflake to Pisano, Pisano to Snowflake, or both.
• Latency and volume: Required latency and expected daily and peak volume.
• Source semantics: Source event behavior, stable identity keys, and survey eligibility rules.
• Pisano configuration: Pisano environment, Sharing API or channel, feedback method, and effective limits.
• Dispatcher ownership: Snowflake stored procedure with External Access Integration or an existing customer integration platform.
• Feedback loading method: SQL API or batch or staged load based on expected volume.
• File integration: Exact transfer protocol, runtime, owner, and complete loading and delivery process.