Process Integration Using Salesforce Apex and Snowflake API.

Audience: Salesforce & Snowflake Architects and Developers

Why: Inspire them to adopt intelligence in their business automation processes.

“ 1Billion+ queries a day ” and “44 Billion automation executed in 24 hours” — are jaw-dropping metrics provided by Snowflake and Salesforce respectively.

These numbers don’t just show the scalability of these platforms, but the huge value customers are deriving as evidenced by how quickly these platforms have become leaders in their categories. Having worked at both Salesforce and Snowflake as an Architect, I have come to appreciate how many of the common customers want to integrate these two cloud solutions and solve new and interesting use cases and drive value to the business.

There is a myriad number of ETL solutions that can move data and integrate at the data layer, but here, I want to focus on process-level integration.

This integration pattern can be used across a number of Customer 360 use cases like determining an individual’s life-time-value to present the next best offer to the customer; verifying a consumer’s creditworthiness by adding macro-economics dataset through the Snowflake Marketplace to predict a loan risk during a loan approval flow in Salesforce, or many more.”

The rest of the blog will detail how we can leverage Salesforce Apex to call Snowflake API and extract LTV and store it in a custom object.

Steps Involved:

We will leverage Salesforce Apex callout to Snowflake SQL REST API here, but we could also integrate Salesforce flows if you are comfortable with declarative tools instead of code. Look out for a blog in the near future that will discuss Salesforce Flows integration with Snowflake REST API.

On Snowflake Instance:

Create a Security Integration for a custom client as documented here. Make note of client_id, account_name, and integration_name as we will need them for the next steps.

CREATE SECURITY INTEGRATION oauth_kp_we
  type = oauth
  enabled = true
  oauth_client = custom
  oauth_client_type = 'CONFIDENTIAL'
  oauth_redirect_uri = 'https://<salesforce-url>.com'
  oauth_issue_refresh_tokens = true
  oauth_refresh_token_validity = 86400
  blocked_roles_list = ('SYSADMIN');
GRANT USAGE ON INTEGRATION oauth_kp_web TO ROLE sqlapirole;
-- EXTRACT client_id, client_secret 
select system$show_oauth_client_secrets('oauth_kp_web');        

On Salesforce Org:

Auth provider: You traverse to Setup -> Auth. Provider and create a new Auth with Provider Type = SnowflakeOAuth. Enter the details captured in the step above.

Named Credential: Traverse to Setup -> Named Credential and create a named credential with a name but remember to select Auth Protocol as OAuth 2.0 and AuthProvider should be the name you assigned while creating Auth provider above.

Now, we are ready to call Snowflake API from Salesforce Apex code. Check out the sample working code below

Apex code

Follow this link to generate a Snowflake access token to be used below.

public class Snowflake_Callout {

public void oauth(){
Http http = new Http();
HttpRequest req = new HttpRequest();
req.setMethod('POST');
req.setHeader('Content-Type', 'application/json');
req.setHeader('Accept','application/json');
req.setHeader('Snowflake-Account','<xxxxxx>');
// Choose Bearer token or Named Credential, comment the other.

String token=<oath-token>;
req.setEndpoint('https://sfsenorthamerica-cnayak.snowflakecomputing.com/api/v2/statements');
req.setHeader('Authorization','Bearer ' + token);
// if using Named Credential, set endpoint 
req.setEndpoint('callout:/api/v2/statements');
req.setBody('{
 "statement": "select lifetime_value from customer where customer_id=101;", 
 "timeout": 60, 
 "database":"C360DB", 
 "warehouse":"C360_WH", 
 "role":"SQLAPIROLE", 
 "resultSetMetaData": 
    {"format":"json"} 
  }');
HttpResponse response = http.send(req);
String respBody = response.getBody();
// store the query response in a custom object
(Snow_Response__c)JSON.deserialize(respBody,Snow_Response__c.class);
JSONParser parser = JSON.createParser(respBody);
JSONToken key_value = JSONToken.VALUE_STRING;
String ltv = '';
while (parser.nextToken() != null) {
 if (
     (parser.getCurrentToken() == JSONToken.FIELD_NAME) &&    
     (parser.getText() == 'data')
    )
  { 
   //code to parse through the array until a value is found 
   //  do {  parser.nextValue();
   //  } while (parser.getText() != key_value);
   parser.nextValue();
   parser.nextValue();
   parser.nextValue();
   ltv = parser.getText();
  }
 }
System.debug(ltv); 
Database.insert(Snow_Response);// save in Snow_Respose custom object
}        

Snowflake response payload which was parsed in the code above

"resultSetMetaData" : {
"numRows" : 1,
"format" : "jsonv2",
"partitionInfo" : [ {"rowCount" : 1,"uncompressedSize" : 21} ],
"rowType" : [ {"name" : "LIFETIME_VALUE","database" : "","schema" : "","table" : "","byteLength" : null,"type" : "real","scale" : null,"precision" : null,"nullable" : true,"collation" : null,"length" : null} ]},
"data" : [ ["100000"] ],
"code" : "090001",
"statementStatusUrl" : "/api/v2/statements/01a5c392-0401-a8cd-0045-e803002e3272?requestId=1a16e8e1-dcaa-4ded-8db6-6cd765791674",
"requestId" : "1a16e8e1-dcaa-4ded-8db6-6cd765791674",
"sqlState" : "00000",
"statementHandle" : "01a5c392-0401-a8cd-0045-e803002e3272",
"message" : "Statement executed successfully.",
"createdOn" : 1658443372925
}