HubSpot syncs failing normalization with Snowflake destination

[remisalmon]

Environment

• Airbyte version: 0.39.19-alpha
• OS Version / Instance: AWS EC2
• Deployment: Docker
• Source Connector and version: HubSpot 0.1.70
• Destination Connector and version: Snowflake 0.4.28
• Step where error happened: Sync job

Current Behavior

HubSpot syncs are failing during normalization "Failure Origin: normalization, Message: Something went wrong during normalization". Same issue happens when going back to HubSpot 0.1.69.

Expected Behavior

Syncs not to fail when normalization is enabled.

Logs

Unknown function JSON_EXTRACT_ARRAY seems to be the issue. This function does not exist in Snowflake: https://docs.snowflake.com/en/sql-reference/functions-all.html

2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.098489 [info ] [MainThread]: �[31mCompleted with 1 error and 0 warnings:�[0m
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.098967 [info ] [MainThread]: 
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.099661 [error] [MainThread]: �[33mDatabase Error in model EMAIL_EVENTS (models/generated/airbyte_tables/HUBSPOT/EMAIL_EVENTS.sql)�[0m
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.100110 [error] [MainThread]:   002140 (42601): SQL compilation error:
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.100924 [error] [MainThread]:   Unknown function JSON_EXTRACT_ARRAY
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.101368 [error] [MainThread]:   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/HUBSPOT/EMAIL_EVENTS.sql
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.102390 [info ] [MainThread]: 
2022-06-17 14:57:33 �[42mnormalization�[0m > 14:57:29.103189 [info ] [MainThread]: Done. PASS=0 WARN=0 ERROR=1 SKIP=5 TOTAL=6
2022-06-17 14:57:33 �[32mINFO�[m i.a.w.t.TemporalAttemptExecution(lambda$getWorkerThread$2):161 - Completing future exceptionally...
io.airbyte.workers.exception.WorkerException: Normalization Failed.
	at io.airbyte.workers.general.DefaultNormalizationWorker.run(DefaultNormalizationWorker.java:63) ~[io.airbyte-airbyte-workers-0.39.19-alpha.jar:?]
	at io.airbyte.workers.general.DefaultNormalizationWorker.run(DefaultNormalizationWorker.java:21) ~[io.airbyte-airbyte-workers-0.39.19-alpha.jar:?]
	at io.airbyte.workers.temporal.TemporalAttemptExecution.lambda$getWorkerThread$2(TemporalAttemptExecution.java:158) ~[io.airbyte-airbyte-workers-0.39.19-alpha.jar:?]
	at java.lang.Thread.run(Thread.java:833) [?:?]
Caused by: io.airbyte.workers.exception.WorkerException: Normalization Failed.

Steps to Reproduce

1. Setup HubSpot 0.1.70 source and Snowflake 0.4.28 with incremental sync + normalization
2. Sync

Are you willing to submit a PR?

No.

[Tomperez98]

I have exactly the same problem moving data from Shopify to Snowflake.

[error] [MainThread]:   002140 (42601): SQL compilation error:
[error] [MainThread]:   Unknown functions JSON_EXTRACT_ARRAY