Did adding the $ at the beginning of your expression help?
Allow me to explain a bit more about validation vs execution. This will be generally useful in your use of SnapLogic.
When you validate a pipeline, the SnapLogic platform will cache the preview output of each snap. On the next “regular” validation, it considers which snaps have been modified since the last validation, and will reuse the cached output of all snaps up until the first snap that was modified. It does this as an optimization to avoid re-executing potentially costly or time-consuming operations like Snowflake Execute. So when you’re modifying the Mapper and a new validation occurs, it’s operating on the cached preview data from the Snowflake Execute.
Usually, that invisible use of cached data during validation is a good thing. It makes designing pipelines faster by re-running only the modified snaps and the snaps downstream from those. Usually the results are consistent whether you’re validating with cached data or executing.
However, as you’ve witnessed, there can be subtle issues that arise from the use of cached data. This is because the caching mechanism is based on serializing the preview data to JSON format and then deserializing it when the cached data is used. JSON only has a few basic data types, which don’t include types like dates and timestamps. So if an output document uses objects of types that don’t correspond to native JSON types, SnapLogic will represent those objects using a custom JSON representation. For well-supported types like Joda DateTime, it will use a representation like this where the type key begins with “snaptype”:
{"_snaptype_datetime": "2022-03-14T15:24:24.974 -06:00"}
When the platform deserializes the JSON representation of the output document and encounters that object, it’s able to correctly restore a Joda DateTime object identical to the one that was serialized, so the downstream snaps will behave consistently regardless of whether cached data is used.
Unfortunately, some object types like java.sql.Timestamp are not well-supported by the serialization/deserialization of the caching mechanism, resulting in inconsistent behavior. This is the type used for timestamps when the Snowflake Execute’s “Handle…” setting is “Default Date Time Format…”. The serialized representation looks like this:
{"Timestamp": "2022-03-14T21:24:24.974+0000"}
Unfortunately, the platform does not know how to deserialize this cached representation to restore an identical java.sql.Timestamp object that was serialized. Instead, it will deserialize this as a Map containing a single key/value pair, exactly as shown in the JSON. So your transformations that depended on the presence of “Timestamp=” in the toString() representation or on the .Timestamp sub-object were relying on the mis-deserialized cached preview data and would only work in that context.
You can force all snaps to re-run during a validation (avoiding any cached data) by either holding Shift as you click the validate icon, or by clicking the Retry link next to “Validation completed” if that link is displayed. But it’s better to avoid the issue by configuring the snap to use the data types that are well-supported for caching.
Hope that helps.
