🐛 Shopify => Snowflake: fails during normalization on table `ABANDONED_CHECKOUTS_CUSTOMER`

[philippeboyd]

Environment

• Airbyte version: example is 0.35.30-alpha
• OS Version / Instance: GCP e2
• Deployment: docker
• Source Connector and version: Shopify 0.1.31
• Destination Connector and version: Snowflake 0.4.12
• Severity: High
• Step where error happened: Sync job (normalization)

Current Behavior

We're syncing 2 Shopify sources (2 different clients) to Snowflake and we get the same error on the same table during normalization.

Boolean value '[]' is not recognized on the table ABANDONED_CHECKOUTS_CUSTOMER

In _AIRBYTE_RAW_ABANDONED_CHECKOUTS table we can see that customers have

{
  ...

  "customer": {
    ...
    "state": "enabled",
    "tags": "newsletter, prospect",
    "tax_exempt": false,
    "tax_exemptions": [], <- this seems to be problematic
    "total_spent": 6283.66,
    "updated_at": "2022-01-16T10:00:56-05:00",
    "verified_email": true
  },

  ...
}

However in the normalization SQL generated by Airbyte, it tries to cast that column into a boolean

...
) as TOTAL_SPENT,
    cast(ORDERS_COUNT as
    bigint
) as ORDERS_COUNT,
    cast(LAST_ORDER_ID as
    bigint
) as LAST_ORDER_ID,
    cast(TAX_EXEMPTIONS as boolean) as TAX_EXEMPTIONS,  # <- this seems to be problematic
    cast(VERIFIED_EMAIL as boolean) as VERIFIED_EMAIL,
    cast(DEFAULT_ADDRESS as
    variant
) as DEFAULT_ADDRESS,
    cast(LAST_ORDER_NAME as
    varchar
) as LAST_ORDER_NAME,
    cast(ACCEPTS_MARKETING as boolean) as ACCEPTS_MARKETING,
    cast(ADMIN_GRAPHQL_API_ID as
    varchar
) as ADMIN_GRAPHQL_API_ID,
    cast(MULTIPASS_IDENTIFIER as
    varchar
) as MULTIPASS_IDENTIFIER,
    ACCEPTS_MARKETING_UPDATED_AT,
    _AIRBYTE_AB_ID,
    _AIRBYTE_EMITTED_AT,
    convert_timezone('UTC', current_timestamp()) as _AIRBYTE_NORMALIZED_AT
from __dbt__cte__ABANDONED_CHECKOUTS_CUSTOMER_AB1
...

Expected Behavior

SQL column should probably not be cast as a boolean

Logs

If applicable, please upload the logs from the failing operation.
For sync jobs, you can download the full logs from the UI by going to the sync attempt page and
clicking the download logs button at the top right of the logs display window.

LOG

...

2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.783167 (MainThread): Completed with 1 error and 0 warnings:
2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.783457 (MainThread): 
2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.783754 (MainThread): Database Error in model ABANDONED_CHECKOUTS_CUSTOMER (models/generated/airbyte_tables/RAW/ABANDONED_CHECKOUTS_CUSTOMER.sql)
2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.784064 (MainThread):   100037 (22018): Boolean value '[]' is not recognized
2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.784302 (MainThread):   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/RAW/ABANDONED_CHECKOUTS_CUSTOMER.sql
2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.785231 (MainThread): 
2022-02-17 09:43:30 normalization > Done. PASS=253 WARN=0 ERROR=1 SKIP=2 TOTAL=256
2022-02-17 09:43:30 normalization > 2022-02-17 09:43:23.785633 (MainThread): Flushing usage events
2022-02-17 09:43:30 INFO i.a.w.t.TemporalAttemptExecution(lambda$getWorkerThread$2):158 - Completing future exceptionally...
io.airbyte.workers.WorkerException: Normalization Failed.
	at io.airbyte.workers.DefaultNormalizationWorker.run(DefaultNormalizationWorker.java:60) ~[io.airbyte-airbyte-workers-0.35.30-alpha.jar:?]
	at io.airbyte.workers.DefaultNormalizationWorker.run(DefaultNormalizationWorker.java:18) ~[io.airbyte-airbyte-workers-0.35.30-alpha.jar:?]
	at io.airbyte.workers.temporal.TemporalAttemptExecution.lambda$getWorkerThread$2(TemporalAttemptExecution.java:155) ~[io.airbyte-airbyte-workers-0.35.30-alpha.jar:?]
	at java.lang.Thread.run(Thread.java:833) [?:?]

...

Steps to Reproduce

1. Setup Shopify source
2. Setup Snowflake destination
3. Sync data

[philippeboyd]

I'm working on the fix, PR coming soon