Snowflake python connector cannot parse null timestamps

[osipovartem]

Initial create query (empty table with defined column types)

CREATE OR REPLACE TABLE example AS (WITH prep AS (SELECT CAST(NULL AS TEXT) AS model, CAST('1970-01-01' AS TIMESTAMP) AS last_success) SELECT * FROM prep WHERE false)

Select query

 SELECT min(last_success) AS min_last_success, max(last_success) AS max_last_success, coalesce(count(*), 0) AS models FROM example WHERE model IN ('snowplow_web_page_views', 'snowplow_web_pv_scroll_depth', 'snowplow_web_pv_engaged_time', 'snowplow_web_page_views_this_run', 'snowplow_web_user_mapping', 'snowplow_web_sessions', 'snowplow_web_sessions_this_run', 'snowplow_web_users', 'snowplow_web_users_sessions_this_run', 'snowplow_web_users_this_run', 'snowplow_web_users_aggs', 'snowplow_web_users_lasts') 

The table is empty before the select query. Response contains rowset and rowtype.

{"data": {"rowtype": [{"name": "min_last_success", "database": "", "schema": "", "table": "", "nullable": true, "type": "timestamp_ntz", "byteLength": null, "length": null, "scale": 9, "precision": 0, "collation": null}, {"name": "max_last_success", "database": "", "schema": "", "table": "", "nullable": true, "type": "timestamp_ntz", "byteLength": null, "length": null, "scale": 9, "precision": 0, "collation": null}, {"name": "models", "database": "", "schema": "", "table": "", "nullable": false, "type": "fixed", "byteLength": null, "length": null, "scale": 0, "precision": 38, "collation": null}], "rowsetBase64": null, "rowset": [{"min_last_success": null, "max_last_success": null, "models": 0}], "total": 1, "queryResultFormat": "json", "sqlState": "ok"}, "success": true, "message": "successfully executed", "code": "000200"}

Snowflake dbt response for the json above

13:40:43    252005: Failed to convert: field min_last_success: TIMESTAMP_NTZ::min_last_success, Error: invalid literal for int() with base 10: 'min_last_success'

Possible issue here that we use JSON type instead of ARROW IPC. For JSON type snowflake connector contains logic to convert data from strings to correct type.

[DanCodedThis]

Closed by #280