SELECT lastName,firstName FROM "DB1"."table2" WHERE invoiceAmt > 10000
Selecting time zone data returns null values in profiling and fails in publishing
When you import a column from Snowflake that contains time zone information, you may see the following behavior:
- Sampled data appears to import correctly into the Transformer page for the TIMESTAMP-based column.
- When a job is run, the visual profile for the output column based on this data indicates null values.
- When the data is published back to Snowflake, the publishing job fails.
The above issue is caused by the following:
- When data is imported into the Transformer page, it is automatically converted to UTC timezone during the JDBC ingestion step for displaying the sample in the application. This ingestion process is called by the application and outside of the application's control.
- During this ingestion process, some auto-recognition and conversion to UTC of Datetime values is applied to the sample for display.
- Example: You design a recipe step to parse the following Datetime format:
2020-10-11 12:13:14., which has been auto-converted to UTC.
- When a job is run:
- The application instructs Snowflake to unload the entire dataset from Snowflake and write it the target location, bypassing this automatic conversion process.
- The recipe that was created to handle the data in the sample does not properly handle the data that is directly unloaded from Snowflake.
- In the previous example: The Datetime parsing in your recipe may receive an input that looks very different from what you parsed in the displayed sample:
2020-10-11 14:13:14 CEST.
For a time stamp with a time zone, you must wrap your reference to it like the following:
Suppose your query was the following:
SELECT *, CURRENT_TIMESTAMP() AS current_time FROM MY_TABLE;
To address this issue, the query needs to be rewritten as follows:
SELECT *, TO_TIMESTAMP(CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())) AS current_time FROM MY_TABLE;
When the above wrapper function is applied, the data is imported normally and validated and published as expected.