MySQL to Snowflake Out of range Time issue

[rdeleonf]

Subject of the issue

TIME data type range mismatch

Your environment

Pipelinewise version v0.38.0
Origin: MySQL database
Destination: Snowflake

Steps to reproduce

The TIME datatype in MYSQL may range from '-838:59:59' to '838:59:59', the TIME datatype in snowflake may range from 00:00:00 to 23:59:59.

If there is a value in the source database that is outside the snowflake data range, Snowflake will throw an error about the time not being recognized.

Expected behaviour

A min-max value validation in order to determine if the data-type will cuase conflict or not.

Actual behaviour

TIME '<some_time>' is not recognized

Further notes

A string data type could be used when there might be values that are out of Snowflake's TIME range.

[Samira-El]

Hey @rdeleonf, thanks for raising this!

Can you share some logs of this error? is this happening during FastSync or Singer replication?

[rdeleonf]

Hi @Samira-El This was during FastSync; however, I checked the sourcecode for the singer tap and the problem should also be present.
Here's the relevant part of the log.
out_of_range_log.txt

[Samira-El]

Thanks for the reply!

This query needs to be updated to check for out of range times and fallback to the max time value in Snowflake.

As for the tap, need to further investigate and try to reproduce this when I have time.

I can't promise an ETA for the fix but if you're open to submit a fix then we can review it :)