Snowflake (Data Synchronization)

Last updated:
8 min read

Prisma Campaigns can integrate with Snowflake so that financial institutions that keep their core data in a Snowflake data warehouse can sync it directly, without deploying an agent, a middleware layer, or an SFTP server.

Overview

Two Snowpark Python stored procedures call the Prisma Campaigns DataSync REST API: SEND_TO_PRISMA pushes a Snowflake table into an import data sync, and GET_FROM_PRISMA pulls the latest export back into a Snowflake table. You configure the Snowflake side; your Prisma Campaigns representative configures the customer schema and column mappings, then hands you the DataSync ID and API token each procedure needs.

Complete the steps below with your Snowflake / data engineering team. In parallel, work with your Prisma Campaigns representative so the data syncs and credentials are ready before you test the procedures.

Set Up Network Access in Snowflake

Snowflake blocks all external network access by default, so the stored procedures cannot reach Prisma Campaigns until you create a network rule and an external access integration. This requires the ACCOUNTADMIN role (or a role granted CREATE INTEGRATION).

  1. Create a network rule that allows egress to your Prisma Campaigns instance, replacing the domain below with yours. You may list more than one host, for example a sandbox and a production instance:

    USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE NETWORK RULE PRISMACUSTOMERS.PUBLIC.PRISMA_NETWORK_RULE
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('yourinstitution.prismacampaigns.com:443');

  2. Create an external access integration named HTTP_EXT_INT. Both stored procedures reference this exact name in their EXTERNAL_ACCESS_INTEGRATIONS clause, so if you use a different name you will need to update the procedure definitions accordingly:

    CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION HTTP_EXT_INT
  ALLOWED_NETWORK_RULES = (PRISMACUSTOMERS.PUBLIC.PRISMA_NETWORK_RULE)
  ENABLED = TRUE;

GRANT USAGE ON INTEGRATION HTTP_EXT_INT TO ROLE DATA_ENGINEER;

Create the Stored Procedures

  1. Grant the deploying role permission to create procedures in the target database and schema. The examples in this guide use PRISMACUSTOMERS.PUBLIC:

    GRANT USAGE ON DATABASE PRISMACUSTOMERS TO ROLE DATA_ENGINEER;
GRANT USAGE, CREATE PROCEDURE ON SCHEMA PRISMACUSTOMERS.PUBLIC TO ROLE DATA_ENGINEER;

  2. Before creating the procedures, confirm your account has accepted the Anaconda terms under Admin → Billing & Terms → Anaconda. The procedures run on Python 3.9 with the snowflake-snowpark-python, requests, and pandas packages from the Snowflake Anaconda channel.

  3. Ask your Prisma Campaigns representative for the CREATE OR REPLACE PROCEDURE statements for SEND_TO_PRISMA and GET_FROM_PRISMA, and run them against that database and schema.

  4. Grant execution to the role that will run the sync:

    GRANT USAGE ON PROCEDURE PRISMACUSTOMERS.PUBLIC.SEND_TO_PRISMA(
  VARCHAR, VARCHAR, NUMBER, VARCHAR, VARCHAR, VARCHAR) TO ROLE MARKETING_OPS;

GRANT USAGE ON PROCEDURE PRISMACUSTOMERS.PUBLIC.GET_FROM_PRISMA(
  VARCHAR, VARCHAR, NUMBER, VARCHAR, VARCHAR, VARCHAR) TO ROLE MARKETING_OPS;

Keep the following in mind while granting access:

  • Both procedures run EXECUTE AS OWNER, so the owner role needs SELECT on any table passed to SEND_TO_PRISMA and CREATE TABLE / INSERT on the schema where GET_FROM_PRISMA writes. Callers only need USAGE on the procedure itself.
  • Any caller also needs USAGE on an active warehouse. CSV serialization happens inside the Python sandbox, so an XS warehouse is enough for typical files.

The reference procedures accept the API token as a plain argument, which means it can appear in query history. For production, store the token as a Snowflake SECRET or wrap the calls in per-data-sync procedures that embed the credentials and grant only those wrappers to operating roles. At minimum, restrict who can view query history for the roles that run these calls.

Prepare Staging Tables

Prisma expects a member-level table for profile and flag data and, if you sync products, a separate account-level table. Because SEND_TO_PRISMA serializes data with pandas.to_csv (comma-delimited, header row, no index), build your staging views with these rules:

  • Name columns to match Prisma field names (for example MemberNumber, FirstName), or ask your Prisma representative to map them explicitly.
  • Include the member unique identifier first (MemberNumber or equivalent) in every file.
  • Cast dates to YYYY-MM-DD strings, for example TO_VARCHAR(dob, 'YYYY-MM-DD'). Avoid raw DATE / TIMESTAMP columns.
  • Use 1 / 0 for flags, not TRUE / FALSE.
  • Remove pipe characters (|) from text fields in the accounts table: REPLACE(descr, '|', '/').
  • Treat NULLs carefully — they serialize as empty strings, which is fine for recommended fields but not for required ones.
  • Stay within Prisma’s import limits (100 MB and 100,000 rows or fewer are recommended). Partition larger member bases and upload in batches.

Member Table

Create a view with one row per member:

CREATE OR REPLACE VIEW MEMBERS_STAGING AS
SELECT
    member_number                                 AS "MemberNumber",       -- required, unique key
    first_name                                    AS "FirstName",
    last_name                                     AS "LastName",
    email                                          AS "Email",
    cell_phone                                     AS "CellPhone",
    TO_VARCHAR(date_of_birth,'YYYY-MM-DD')         AS "DateOfBirth",
    city                                           AS "City",
    state                                          AS "State",
    zip                                            AS "Zip",
    TO_VARCHAR(membership_open_dt,'YYYY-MM-DD')    AS "MembershipOpenDate",
    IFF(dormant, 1, 0)                             AS "DormantFlag",
    IFF(deceased, 1, 0)                            AS "DeceasedFlag",
    IFF(opt_out, 1, 0)                             AS "OptOutFlag",
    IFF(has_checking, 1, 0)                        AS "Has_Checking",
    IFF(has_savings, 1, 0)                         AS "Has_Savings",
    IFF(has_auto_loan, 1, 0)                       AS "Has_AutoLoan",
    IFF(has_credit_card, 1, 0)                     AS "CreditCardFlag"
FROM core_member_extract;

Always include suppression fields such as OptOutFlag and DeceasedFlag — they drive campaign exclusions.

Accounts / Products Table

Keep products in a separate account-level table. Prisma assembles the AcctInfo composite during import, so send individual columns rather than a pre-built pipe-delimited string. Put MemberNumber first as the record key:

CREATE OR REPLACE VIEW MEMBER_ACCOUNTS_STAGING AS
SELECT
    member_number                                   AS "MemberNumber",        -- record key
    account_id                                      AS "AccountID",           -- required
    account_code                                    AS "AccountCode",         -- required
    REPLACE(account_descr, '|', '/')                AS "AccountDescription",
    account_group                                   AS "AccountGroup",        -- LOAN/DEPOSIT/CARD
    TO_VARCHAR(current_balance, 'FM999999999.00')   AS "CurrentBalance",      -- required
    TO_VARCHAR(current_rate,    'FM990.00')         AS "CurrentRate",
    TO_VARCHAR(payment_due_dt,  'YYYY-MM-DD')       AS "PaymentDueDate",
    delinquency_days                                AS "DelinquencyDays",
    TO_VARCHAR(account_open_dt, 'YYYY-MM-DD')       AS "AccountOpenDate",     -- required
    IFF(account_closed, 1, 0)                       AS "AccountClosedFlag",
    TO_VARCHAR(maturity_dt,     'YYYY-MM-DD')       AS "MaturityDate",
    TO_VARCHAR(last_txn_dt,     'YYYY-MM-DD')       AS "LastTransactionDate"
FROM core_account_extract
WHERE account_closed = FALSE OR account_closed_dt >= DATEADD(month, -13, CURRENT_DATE);
  • Load the member table and the accounts table through two separate import data syncs. Both key on MemberNumber and merge into the same member record.
  • Strip or replace pipe characters in any account text field before upload.
  • Coordinate column order and required fields with your Prisma representative so the import mapping matches your view.

Destination Table for Exports

GET_FROM_PRISMA creates the destination table on the first run if it does not exist, inferring column types from the CSV. Review those types before using the table downstream. Because the procedure appends rows on every call, truncate the destination first or land into a raw table and MERGE into a curated table keyed on the export’s natural key (for example MemberNumber plus campaign and event timestamp).

Get Your DataSync Credentials

Once your staging views are ready:

  1. Send a small sample file (10 rows or fewer, under 1 MB) for the member table and, if applicable, the accounts table to your Prisma Campaigns representative.
  2. Wait for them to configure the data syncs (with frequency set to Manual, since the API upload queues processing) and to send you, through a secure channel (never plaintext email or a shared document):
    • Your Prisma Campaigns domain, for example yourinstitution.prismacampaigns.com.
    • A DataSync ID and API token pair for each data sync (member import, accounts import, and export if configured).
  3. Store the tokens as described in Create the Stored Procedures.

Tokens are generated per data sync, so revoking one never affects the others.

Test the Procedures

With the DataSync IDs and tokens in hand, call the stored procedures from a worksheet:

-- Push a Snowflake table into a Prisma import data sync
CALL SEND_TO_PRISMA(
    'MEMBERS_STAGING',                        -- source table
    'yourinstitution.prismacampaigns.com',    -- Prisma domain
    443,                                       -- port
    '6908f6f8-8bd0-4b95-9e36-7667d0b21ea6',   -- data sync ID (from Prisma)
    'api',                                     -- username (always "api")
    'key-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'    -- data sync API token (from Prisma)
);

-- Pull the latest file from a Prisma export data sync
CALL GET_FROM_PRISMA(
    'PRISMA_CAMPAIGN_RESULTS',                -- destination table (created if missing)
    'yourinstitution.prismacampaigns.com',
    443,
    '6908f8e9-6843-427a-b33c-109087880239',
    'api',
    'key-yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'
);

Keep these behaviors in mind:

  • SEND_TO_PRISMA sends the entire table (SELECT *), so your staging table or view should contain exactly the columns and rows Prisma should receive.
  • A successful upload returns File queued — the file is processed asynchronously. Confirm the launch finished with the status APIs described in Using Datasyncs via APIs.
  • GET_FROM_PRISMA appends rows on every call. For a clean snapshot, truncate the destination table first or land into a staging table and merge.
  • GET_FROM_PRISMA always returns the file from the latest successful launch of the export data sync. Calling it twice without a new launch in between imports the same rows twice.

Schedule the Sync

Once a manual call works as expected, wrap it in a Snowflake Task:

CREATE OR REPLACE TASK NIGHTLY_MEMBER_SYNC
  WAREHOUSE = XS_WH
  SCHEDULE = 'USING CRON 0 5 * * * America/New_York'
AS
  CALL SEND_TO_PRISMA('MEMBERS_STAGING', 'yourinstitution.prismacampaigns.com',
                      443, '<datasync-id>', 'api', '<token>');

ALTER TASK NIGHTLY_MEMBER_SYNC RESUME;

Because imports are queued asynchronously, verify each run with the data sync status APIs (same Basic Auth). See Using Datasyncs via APIs for the status, launch history, and download endpoints.

Expected Result

Before enabling the scheduled tasks, confirm the following with your Prisma Campaigns representative:

  1. A small sample upload through SEND_TO_PRISMA to the member import data sync returns File queued, and the launch status reaches finished.
  2. A sample member in Prisma Campaigns shows the expected profile fields, flags, and date formats.
  3. If you sync accounts, a small accounts sample loads correctly and segment conditions that depend on products or balances resolve as expected.
  4. If you configured an export, GET_FROM_PRISMA populates the destination table with the expected columns and types.
  5. Once everything checks out, enable the scheduled Snowflake tasks.

Troubleshooting

Symptom Likely cause Fix
Procedure fails to compile, referencing HTTP_EXT_INT The external access integration is missing or not granted Create the integration and grant USAGE to the owner role
requests.exceptions.ConnectionError at runtime The network rule doesn’t include the Prisma host and port Add the host to the VALUE_LIST of the network rule
HTTP 403 from Prisma Wrong token, or a token from a different data sync Tokens are per data sync — verify the ID and token pair
HTTP 404 from Prisma Wrong DATASYNC_ID or wrong domain Confirm the ID with your Prisma representative
Upload succeeds but data never appears The file was queued but the launch failed Check the launch status-message via the status APIs and share it with your Prisma representative (often a header mismatch against the column mapping)
Duplicate rows in the export destination table GET_FROM_PRISMA appends, and the export wasn’t re-launched before the last call Use the truncate/merge pattern and check launch timestamps before importing
Dates land as full timestamps Raw DATE / TIMESTAMP columns were serialized by pandas Cast to TO_VARCHAR(..., 'YYYY-MM-DD') in the staging view

If segment conditions on products or accounts look wrong after a successful import, contact your Prisma representative with a sample of the uploaded headers and rows — mapping and composite-field assembly are configured on the Prisma side.

Was this article helpful?