Snowflake

​

Purpose and Capabilities

​

Prerequisites

• Snowflake Account: An active Snowflake account.
• User Credentials & Permissions:
  • A Snowflake user with appropriate permissions to access the required databases, schemas, tables, and warehouses.
  • Permissions to execute the types of SQL statements intended (SELECT, INSERT, CREATE, etc.).
  • Permissions to perform COPY INTO table (for loading) and COPY INTO @stage (for unloading) operations if using those tools.
• Authentication Method:
  • Key Pair Authentication is recommended for API access. This involves generating a public/private key pair, assigning the public key to the Snowflake user, and using the private key for signing JWTs.
  • Alternatively, OAuth can be configured if supported by your ACP setup for Snowflake.
• Network Access: Ensure that the environment running the ACP connector can reach your Snowflake account URL (e.g., youraccount.snowflakecomputing.com).
• Warehouse: An active and appropriately sized Snowflake warehouse for query execution.

​

Setup Instructions

1. Configure Snowflake User and Permissions:
   • Create or identify a Snowflake user for ACP integration.
   • Grant necessary roles and privileges to this user. For example:
     Copy

     -- Example: Role creation and basic grants
     CREATE ROLE acp_integration_role;
     GRANT USAGE ON WAREHOUSE <your_warehouse> TO ROLE acp_integration_role;
     GRANT USAGE ON DATABASE <your_database> TO ROLE acp_integration_role;
     GRANT USAGE ON SCHEMA <your_database>.<your_schema> TO ROLE acp_integration_role;
     GRANT SELECT ON ALL TABLES IN SCHEMA <your_database>.<your_schema> TO ROLE acp_integration_role;
     -- Add grants for INSERT, UPDATE, DELETE, CREATE TABLE, COPY INTO etc. as needed
     GRANT ROLE acp_integration_role TO USER <acp_user>;
     ALTER USER <acp_user> SET DEFAULT_ROLE = acp_integration_role;
     ALTER USER <acp_user> SET DEFAULT_WAREHOUSE = '<your_warehouse>';
     

2. Set up Key Pair Authentication (Recommended):
   • Generate an RSA key pair (e.g., using OpenSSL):
     Copy

     # Generate private key (PKCS#8 format)
     openssl genrsa -out rsa_key.p8 2048
     # Generate public key from private key
     openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
     

   • Assign the public key to the Snowflake user. Copy the content of rsa_key.pub (excluding headers/footers) and set it for the user in Snowflake:
     Copy

     ALTER USER <acp_user> SET RSA_PUBLIC_KEY_FP = '<fingerprint_of_public_key>'; -- Optional, for tracking
     ALTER USER <acp_user> SET RSA_PUBLIC_KEY = '<public_key_string>';
     

   • Securely store the private key (rsa_key.p8). This key will be used by the ACP connector to generate JWTs for authentication.
3. Configure ACP Connector:
   • The ACP administrator will configure the Snowflake Connector settings within ACP.
   • This typically involves providing:
     • Snowflake account identifier (e.g., xy12345.us-west-2.aws).
     • The Snowflake user for integration (e.g., acp_user).
     • The private key (if using Key Pair Authentication) and passphrase (if any).
     • Default warehouse, database, and schema (can often be overridden by tool inputs).
4. Verify Connection:
   • Use a simple tool like snowflake_execute_sql_query with a basic query (e.g., SELECT CURRENT_VERSION(); or SELECT COUNT(*) FROM your_table;) to verify the connection and permissions.

​

Common Use Cases

• Data Segmentation: Agentforce agents execute complex SQL queries against Snowflake to identify customer segments for marketing campaigns or targeted actions. Example: Querying for customers in a specific region who purchased a particular product in the last 6 months.
• Campaign List Management: Agents retrieve or update lists in Snowflake used for campaign suppression or inclusion. Example: Adding newly unsubscribed users to a suppression table, or fetching a list of eligible leads for a new campaign.
• Real-time Data Retrieval: Agents fetch specific data points or aggregated metrics from Snowflake to inform decision-making or provide context in user interactions. Example: Retrieving a customer’s lifetime value or recent order history.
• Data Enrichment: Agents use data from Snowflake to enrich records in other systems (e.g., Salesforce Sales Cloud).
• Automated Reporting: Agents trigger the execution of queries that generate data for reports, potentially unloading results to a stage for consumption by other tools.
• Data Validation: Agents execute queries to validate data consistency or quality within Snowflake tables.

​

Troubleshooting Tips

• Authentication Errors (e.g., Invalid JWT token, User not found or not authorized):
  • Verify the Snowflake account identifier, username, and authentication details (private key, OAuth token) are correct in the ACP configuration.
  • If using Key Pair, ensure the public key is correctly assigned to the Snowflake user and the private key is valid.
  • Check if the user’s password has expired or if the account is locked.
• SQL Execution Errors (e.g., SQL compilation error, Object does not exist):
  • Validate the SQL syntax. Test the query directly in Snowflake’s UI first.
  • Ensure the specified warehouse, database, schema, and table/object names are correct and the user has USAGE and relevant DML/DDL permissions on them.
  • Check if the default warehouse for the user or session has sufficient credits and is running.
• Permission Denied Errors (Access control error: Insufficient privileges):
  • The Snowflake user configured for ACP lacks the necessary permissions for the attempted operation (e.g., SELECT on a table, INSERT into a table, USAGE on a warehouse/schema).
  • Review and grant the required privileges to the user’s role in Snowflake.
• Timeout Errors:
  • Long-running queries might exceed default timeout settings in ACP or Snowflake. Consider optimizing the query or increasing timeout parameters if configurable.
  • For very long operations, use asynchronous execution if supported by the tool and check status later.
• Data Loading/Unloading Issues (COPY command error):
  • Verify stage configurations (URL, credentials, encryption keys for external stages).
  • Ensure file paths are correct and accessible from Snowflake.
  • Check file format options and copy options for compatibility with the data files.
  • The user needs USAGE on the stage and INSERT on the target table (for loading) or SELECT on the source table (for unloading).
• Rate Limits:
  • Snowflake may impose rate limits on API requests or concurrent queries. If encountering issues, check Snowflake documentation for limits and consider strategies like batching or spacing out requests.