Target-Snowflake PipelineWise Variant KeyError: 'type'

[drewipson]

Using Tap-Clickup with the target-snowflake pipelinewise variant loader throws a KeyError on load.

Traceback goes to the adjust_timestamps_in_record function in stream_utils.py in the target-snowflake library.

Offending Record:

{"type": "RECORD", "stream": "folder_customfield", "record": {"id": "f510d927-7fab-4622-96d1-07193fc577cc", "name": "Focus Area", "type": "drop_down", "type_config": {"default": 0, "placeholder": null, "new_drop_down": true, "options": [{"id": "97a53bcc-3fa7-4160-b59a-267a1d7d9cd1", "name": "Package Analytics", "color": "#800000", "orderindex": 0}, {"id": "6fc0e3b2-5a38-405a-88cf-4676252ada82", "name": "Custom Package Analytics", "color": "#bf55ec", "orderindex": 1}, {"id": "adf3f2b0-707a-4dde-bbfa-8aa3fd9516d2", "name": "Internal Analytics", "color": "#0231E8", "orderindex": 2}, {"id": "6f7c7717-6f5c-4f02-b788-bbace80da55c", "name": "Implementation Support", "color": "#AF7E2E", "orderindex": 3}, {"id": "c1bb571b-f66a-4ea5-9ec0-2d57f0bb4f33", "name": "Workato", "color": "#e50000", "orderindex": 4}, {"id": "a0e778a2-3b71-4ff2-8d9c-10917d53edc0", "name": "Demo Development", "color": "#f9d900", "orderindex": 5}]}, "date_created": "1628720868248", "hide_from_guests": false, "required": false}, "time_extracted": "2022-01-04T17:30:44.178089Z"} 

Traceback:

2022-01-04T17:30:44.746439Z [info     ] Traceback (most recent call last): cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.748067Z [info     ]   File "/workspaces/hs-meltano/.meltano/loaders/target-snowflake/venv/bin/target-snowflake", line 8, in <module> cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.750433Z [info     ]     sys.exit(main())           cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.753501Z [info     ]   File "/workspaces/hs-meltano/.meltano/loaders/target-snowflake/venv/lib/python3.7/site-packages/target_snowflake/__init__.py", line 520, in main cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.756279Z [info     ]     persist_lines(config, singer_messages, table_cache, file_format_type) cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.759311Z [info     ]   File "/workspaces/hs-meltano/.meltano/loaders/target-snowflake/venv/lib/python3.7/site-packages/target_snowflake/__init__.py", line 143, in persist_lines cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.761874Z [info     ]     stream_utils.adjust_timestamps_in_record(o['record'], schemas[stream]) cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.766532Z [info     ]   File "/workspaces/hs-meltano/.meltano/loaders/target-snowflake/venv/lib/python3.7/site-packages/target_snowflake/stream_utils.py", line 69, in adjust_timestamps_in_record cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.769285Z [info     ]     if 'string' in schema['properties'][key]['type'] and \ cmd_type=loader job_id=clickup-to-snowflake-dev name=target-snowflake run_id=4f348796-2671-4a08-bcf7-0bdfce839b4a stdio=stderr
2022-01-04T17:30:44.773071Z [info     ] KeyError: 'type'  

[visch]

@drewipson Sent over data on which stream this was failing at it seems to be "folder_customfield"

@drewipson could you make a reproducable file here. Ideally it'd be 2 lines first line , SCHEMA etc etc with the schema for that stream second line an offending record, RECORD etc etc with the record data that's failing for you from clickup (any sensitive data scrambled of course)

Idea is we should be able to run cat testdata | target-snowflake and reproduce the error

I can't see how we'd be failing at this, I guess I could run a test with my test data and target-snowflake as well

[visch]

Found it it's not in the custom field stream.

It's in folder_list (and folderless_list)

https://gitlab.com/meltano/sdk/-/issues/228#note_801273924 , validate on the SDK should be able to catch this I'd think.

[visch]

Spent way more time than I wanted on this today, but I did find the next fix here. https://gitlab.com/meltano/sdk/-/issues/299 I put into the SDK.

Refs are not supported. I have no idea how this wouldn't have been caught it our testing, but I guess those fields weren't needed for anything we were using.

Fix is to resolve all of these references, and to put in a PR without using any Refs. I tried to find a script that would do this for us , and tested a few, but I haven't been so lucky yet.