Salesforce- data type is invalid

Connector Questions & Issues
1

We have one table on our connection which is causing issues for the airbyte normalization, the table has one column (Missing_hours__C) which the airbyte transformation is casting to a float and should be varchar.

  • Is this your first time deploying Airbyte?: No
  • Airbyte Version: 0.39.37-alpha
  • Source name/version: Salesforce 1.0.14 (latest)
  • Destination name/version: Snowflake 0.4.34 (latest)
  • Step: During normalization of tables
  • Description: Our salesforce table is being cast as float and should be varchar. The normalization is failing at this step. Upon inspection of the dbt transformation it is indeed incorrect. How can we update this to be the correct data type?

logs error:

normalization >  [info ] [MainThread]: Completed with 1 error and 0 warnings:
normalization >  [error] [MainThread]: Database Error in model Case (models/generated/airbyte_tables/SALESFORCE/Case.sql)
normalization >  [error] [MainThread]:  100038 (22018): Numeric value '01/05/22' is not recognized
normalization >  [error] [MainThread]:  compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/SALESFORCE/Case.sql
normalization >  [info ] [MainThread]: 
normalization >  [info ] [MainThread]: Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Model transformation error:

    cast(MISSING_HOURS__C as 
    float
) as MISSING_HOURS__C

Any support and suggestions would be greatly appreciated!

2

Hi @kwebster, as a first - could you update your Airbyte instance to the latest version? And I think there’s a known issue with backslashes in Snowflake, I’m looking further into it and hope to have some ideas for you soon!

1 Like
3

Hi @natalyjazzviolin – Thank you for your quick response! We will update to the latest version. Any insight you might have would be greatly appreciated. While the example above was failing on values with backslashes, it seems to happen with other values in this column as well (example below). Upon inspection of the raw data, it seems to all be coming from one column which is cast as float even though in salesforce it is text. Thoughts?

normalization > [info ] [MainThread]: Completed with 1 error and 0 warnings:
normalization > [info ] [MainThread]: 
normalization > [error] [MainThread]: Database Error in model Case (models/generated/airbyte_tables/SALESFORCE/Case.sql)
normalization > [error] [MainThread]:   100038 (22018): Numeric value '7:30' is not recognized
normalization > [error] [MainThread]:   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/SALESFORCE/Case.sql

Screenshot 2022-08-31 at 14.02.05
Screenshot 2022-08-31 at 14.02.052872×664 113 KB

4

Oh good to know, thank you for that detail!

I’m looking through the Snowflake documentation and it looks like they require specific time/date-time formats:

Time format should be 00:00:00 -
https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#time

Date-time format should use dashes 12-01-01 -
https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#date

Since normalization only happens in relation to the destination connector, I wonder if this could be fixed by using formats listed in the Snowflake documentation. Would you be able to provide your full logs?

5

Hi @natalyjazzviolin – Okay, this is something I can discuss if it would be possible with our salesforce team. In the meantime, here are the logs. Please let me know if you spot anything! Thanks for your help.

Logs (
logs-9868(pt.1).txt (7.4 MB)
)
Logs (
logs-9868(pt.2).txt (6.8 MB)
)
Logs (
logs-9868(pt.3).txt (1.9 MB)
)

6

Thank you for the logs! Are you using any custom transformations?