ServiceNow Snowflake Integration Guide

var request = new sn_ws.RESTMessageV2('Snowflake SQL API', 'executeQuery');
request.setStringParameterNoEscape('account', 'your-account-name');
request.setStringParameterNoEscape('token', 'your-oauth-token');
var requestBody = {
  'statement': 'SELECT CURRENT_TIMESTAMP()',
  'warehouse': 'SERVICENOW_WH',
  'database': 'SERVICENOW_DATA',
  'schema': 'OPERATIONAL_DATA',
  'bindings': {}
};
request.setRequestBody(JSON.stringify(requestBody));
var response = request.execute();
gs.info('Snowflake API Response: ' + response.getBody());

// Flow Designer Script Step for data transformation
(function process(inputs, outputs) {
  var records = inputs.source_records;
  var transformedData = [];
  
  records.forEach(function(record) {
    var snowflakeRecord = {
      sys_id: record.sys_id,
      number: record.number,
      state: record.state.toString(),
      priority: record.priority.toString(),
      created_on: record.sys_created_on,
      updated_on: record.sys_updated_on
    };
    transformedData.push(snowflakeRecord);
  });
  
  outputs.transformed_records = transformedData;
})(inputs, outputs);

// Integration Hub REST Step configuration
var sqlStatement = "MERGE INTO incident_data AS target USING (" +
  "SELECT * FROM VALUES " + 
  inputs.records.map(function(r) {
    return "('" + r.sys_id + "','" + r.number + "','" + r.state + "')";
  }).join(',') +
  ") AS source(sys_id, number, state) ON target.sys_id = source.sys_id " +
  "WHEN MATCHED THEN UPDATE SET number = source.number, state = source.state " +
  "WHEN NOT MATCHED THEN INSERT (sys_id, number, state) VALUES (source.sys_id, source.number, source.state)";

var requestPayload = {
  statement: sqlStatement,
  warehouse: 'SERVICENOW_WH',
  database: 'SERVICENOW_DATA',
  schema: 'OPERATIONAL_DATA'
};

outputs.sql_payload = requestPayload;

// Script to manage incremental sync state
var syncStateGR = new GlideRecord('u_snowflake_sync_state');
syncStateGR.addQuery('table_name', 'incident');
syncStateGR.query();

var lastSyncTime = new GlideDateTime();
if (syncStateGR.next()) {
  lastSyncTime.setValue(syncStateGR.last_sync_time.toString());
} else {
  // First run - go back 24 hours
  lastSyncTime.addDaysUTC(-1);
}

// Query incidents modified since last sync
var incidentGR = new GlideRecord('incident');
incidentGR.addQuery('sys_updated_on', '>', lastSyncTime);
incidentGR.orderBy('sys_updated_on');
incidentGR.query();

var records = [];
while (incidentGR.next()) {
  records.push({
    sys_id: incidentGR.sys_id.toString(),
    number: incidentGR.number.toString(),
    state: incidentGR.state.toString()
  });
}

return records;

// Error handling and monitoring script
try {
  var response = snowflakeAPI.execute();
  var responseBody = JSON.parse(response.getBody());
  
  if (response.getStatusCode() != 200) {
    throw new Error('Snowflake API error: ' + responseBody.message);
  }
  
  // Update success metrics
  var metricsGR = new GlideRecord('u_integration_metrics');
  metricsGR.initialize();
  metricsGR.integration_name = 'Snowflake Sync';
  metricsGR.records_processed = recordCount;
  metricsGR.execution_time = executionTime;
  metricsGR.status = 'success';
  metricsGR.insert();
  
} catch (error) {
  gs.error('Snowflake integration error: ' + error.message);
  
  // Log error for monitoring
  gs.eventQueue('snowflake.sync.error', null, error.message, gs.getUserID());
  
  // Update error metrics
  var errorGR = new GlideRecord('u_integration_errors');
  errorGR.initialize();
  errorGR.error_message = error.message;
  errorGR.integration_name = 'Snowflake Sync';
  errorGR.insert();
}

// Data validation script for Snowflake sync
var validationResults = {
  servicenow_count: 0,
  snowflake_count: 0,
  discrepancies: []
};

// Count records in ServiceNow
var snGR = new GlideRecord('incident');
snGR.addQuery('sys_created_on', '>=', yesterday);
snGR.query();
validationResults.servicenow_count = snGR.getRowCount();

// Query Snowflake for count (pseudo-code for REST call)
var countQuery = "SELECT COUNT(*) FROM incident_data WHERE created_on >= '" + yesterday + "'";
var snowflakeCount = executeSnowflakeQuery(countQuery);
validationResults.snowflake_count = snowflakeCount;

// Log discrepancies
if (validationResults.servicenow_count != validationResults.snowflake_count) {
  gs.warn('Data count mismatch: SN=' + validationResults.servicenow_count + ', SF=' + validationResults.snowflake_count);
  
  var discrepancyGR = new GlideRecord('u_data_validation_log');
  discrepancyGR.initialize();
  discrepancyGR.validation_date = new GlideDateTime();
  discrepancyGR.servicenow_count = validationResults.servicenow_count;
  discrepancyGR.snowflake_count = validationResults.snowflake_count;
  discrepancyGR.insert();
}