Salesforce to snowflake ingestion converting datetime to PST on TMC cloud

[RFAM1]

Hi,

I have a job to load data from salesforce to snowflake which is working as expected in local machine.

However when I deploy the job in TMC, we can only run on cloud (not on remote engine) due to firewall issue.

On TMC when i run the job, all the date columns which are in UTC in salesorce are converted to PST. I dont have any conversions in my job.

Using dynamic schema in the job. Any pointers would be appreciated. The job is already in production and needs to be fixed asap.

[Anonymous · Accepted solution]

I've experienced this issue. It's a pain, but it is caused by the timestamp type of the Snowflake column. I'm going to guess that your column timestamp type is TIMESTAMP_NTZ. This is "wall clock time" and essentially stores the time without considering timezone. I suspect you are also using a US based Cloud engine (which will be set to PST time). Salesforce always returns the time in UTC. What happens is that is read by Talend and sent to Snowflake as "wall clock time". This results in the incorrect time being kept in Snowflake. There are a couple of ways to deal with this. The first (and easiest) is to change your column type to TIMESTAMP_LTZ. This should be all you need to do and it should just work. Just in case you aren't aware of the default Snowflake timezone, you should test this by querying your timestamp column using "convert_timezone" to convert it to UTC like this....

convert_timezone(‘UTC’,TIMESTAMPCOL) 

The Snowflake timestamp types are described below....

https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#timestamp-ltz-timestamp-ntz-timestamp-tz

The other way to mitigate for this is to change the JVM timezone to UTC before you read the data and then alter the JVM timezone to the default timezone of your Snowflake. This is clumsy and not ideal. But it does work. You can do that like below in tJava....

//Change JVM timezone to UTC

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("UTC"));

//Change JVM timezone to PST

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("America/Los_Angeles"));

Have a play with this and see which way works best for you.