---
title: "Additional features for DataSync for Snowflake"
canonical: "https://docs.perspectium.com/space/Iodine/1705047/Additional%20features%20for%20DataSync%20for%20Snowflake"
format: markdown
---
To enhance your DataSync integration for   Snowflake , you can configure the Snowflake Bulk Load  Meshlet  with the additional features available below! Add custom fields to Snowflake   You can add custom fields to Snowflake database tables to represent actions take by the meshlet as follows:  IO insert columns - Columns populated with the timestamp of the record insert into Snowflake IO update columns - Columns populated with the timestamp of the record insert into Snowflake and updated subsequently on record update into Snowflake key column - A string field populated with the ServiceNow instance's key where this record came from. This field is based on the  key  field of the Perspectium Message (which by default populates with your ServiceNow instance's name such as acmeqa, dev12345, etc.) that is sent from the ServiceNow app into the Integration Mesh and consumed by the meshlet. IO delete column - Column populated with the timestamp when the meshlet receives a message indicating a record is to be deleted (i.e. the record has been deleted from ServiceNow and this is being replicated over to Snowflake). The record  will not  be deleted from the Snowflake database table. See  Soft Deletes  for more information. To add your custom column names in your Snowflake tables, you can configure the  databaseConfig.json  file by following these steps: By default, IO columns will behave as they do in ServiceNow; for rows inserted into Snowflake tables with a custom IO update column, the IO update column will be populated with the insert timestamp. This setting can be turned off with updateTimeOnInsert yml configuration. If the table does not exist in Snowflake, the table will be created with the custom columns when the meshlet is run. If the table already exists, the table will be altered to add the custom columns into the table. ↑ Go to top of page Compare tables from ServiceNow to Snowflake  Table Compare  allows you to compare tables from one ServiceNow instance to Snowflake. This is useful because you can see each table's record count, as well as a list of record discrepancies by  sys_id  between the two tables you're comparing—in other words, you can see which records exist in one table but not the other. To get started with comparing your ServiceNow tables to Snowflake, see  Table Compare: ServiceNow to database table compare .     NOTE : This requires version Helium and above Perspectium Core update set.  ↑ Go to top of page Load Schema Files  This feature allows you to load your ServiceNow table schemas to the meshlet when a connection cannot be made to the originating ServiceNow instance.  Schema files are used to create and update tables in Snowflake. Enabling this feature will force the meshlet to read schemas via files exclusively from the local file storage and disable all calls to ServiceNow. Any new tables shared will require table schemas to be added to the local file storage.   NOTE : By default, this option will load all schemas stored in local file storage when the meshlet starts up to create tables in the Snowflake database immediately so as to improve performance when the meshlet is processing records. If you only want tables to be created when a record is received for a table, see the   loadSchemasOnStart  configuration. ↑ Go to top of page Azure External Storage To start using Microsoft Azure as an external stage for  bulk loading , follow these steps: ↑ Go to top of page Soft Deletes This feature allows you to add columns to a database table that gets updated when the meshlet receives a message indicating a record is to be deleted. The meshlet will update said columns appropriately and will not delete the record in Snowflake.   NOTE : At least one of the following table columns are required to achieve soft deletes, if neither column has been added to the configurations but  softDelete  is set to true, no delete will occur. This is to prevent deletion of records when there is an intention of using soft deletes. ↑ Go to top of page Timestamp Columns and Timezones Snowflake supports saving timestamp columns with different variations as described  here . For example, if you are using the  TIMESTAMP_NTZ  variation, records saved into a timestamp column will be saved with the timestamp value as is with no timezone offset possible. Thus when records are queried, Snowflake will always return the exact same timestamp value regardless of the timezone you specify in your session using the  TIMEZONE  parameter.  That is, if you save a value as  2021-01-01 00:14:30  into a column defined as  TIMESTAMP_NTZ(9) , Snowflake will always return the value as  2021-01-01 00:14:30   regardless of the timezone your current session is in. To mimic how data is saved in ServiceNow, by default t he meshlet uses the  TIMEZONE   session parameter in its JDBC connection string when connecting to Snowflake, specifying that timestamp values the meshlet saves will be in UTC ( TIMEZONE=UTC ). In addition to using this parameter,  glide_date_time  fields from ServiceNow (which is the default field type for datetime fields in ServiceNow) are mapped to the  TIMESTAMP_LTZ(9)  column type in the meshlet's  databaseConfig.json  configuration file.  This combination ensures that ServiceNow timestamp fields the meshlet pushes into Snowflake are saved in UTC time and then allows for querying of records to be returned in local timezone based on the TIMEZONE parameter a user sets in their  session .  That is, if you save a value as  2021-01-01 00:14:30  and specified that we were saving it in UTC time ( TIMEZONE=UTC ) into a column defined as  TIMESTAMP_LTZ(9) , Snowflake will return the value as  2021-01-01 00:06:30  -0800  if your current session is in the America/Los_Angeles (Pacific) timezone. If you prefer the meshlet to not save records in UTC time,  set the following configuration  in  the application-dev.yml meshlet configuration file:   NOTE : This is an advanced configuration and it is recommended you review how Snowflake handles  timestamp variations  before making these changes. perspectium:    
	snowflake:          
		useUTCTimeZone: false By default or if the configuration is not specified,  useUTCTimeZone  is set to  true  so records are saved in UTC time.  When setting this to false, the meshlet will save in the  session  timezone as defined in the session/account for the Snowflake account credentials entered in application-dev.yml for connecting to Snowflake.  After setting this configuration to false, you can then use the  connectionUrl  configuration in application-dev.yml to use a different timezone if desired i.e. perspectium:    
	snowflake:             
		connectionUrl: jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?TIMEZONE=America/Los_Angeles In this case, records will be saved into the database with the America/Los_Angeles (Pacific) timezone. You can modify the  glide_date_time  configuration  in the  databaseConfig.json  file if you want timestamp fields to be saved in a different  TIMESTAMP  variation and precision.   NOTE : This is an advanced configuration and it is recommended you review how Snowflake handles  timestamp variations  before making these changes. For example, if you wanted to change all ServiceNow datetime fields to be saved without timezone considerations with a precision of 0 (only seconds), you can update the  glide_date_time  configuration in  databaseConfig.json  as follows: "types":{
      "glide_date_time":"TIMESTAMP_NTZ(0)",
 } ↑ Go to top of page Error Logging Starting with the  Iodine 7.0.1 release , the meshlet will log details on when it fails with a record in the batch, listing the error message as returned by Snowflake. For example: 2022-05-20 20:25:36,197 ERROR [task-2] com.perspectium.meshlet.common.DataSyncReceiptService: Error processing record: {"sys_id":"0c611d5e1b27011003b30f26624bcbba","table":"ticket","message":"User character length limit (5000) exceeded by string 'Issue with my email, can you please take a look'"}
2022-05-20 20:25:39,225 INFO [scheduling-1] com.perspectium.meshlet.listener.SnowflakeListenerService: Processed 5 record(s) to table: ticket with 1 record(s) having errors

 ↑ Go to top of page