The error raises from the double quote " in string wraping the value "1.00".

ERROR type-and-dedupe i.a.i.b.d.t.TypeAndDedupeTransaction(executeTypeAndDedupe):48 Encountered Exception on unsafe SQL for stream INV SOAllocation with suffix , attempting with error handling org.postgresql.util.PSQLException: ERROR: invalid input syntax for type numeric: ""1.00""

I go deep in db query, could see this:

CASE WHEN (
	"_airbyte_data" -> 'Qty' is not null and JSONB_TYPEOF("_airbyte_data" -> 'Qty') <> 'null' 
	and "pg_temp"."airbyte_safe_cast"(
		case when (
			"_airbyte_data" -> 'Qty' is null or 
			JSONB_TYPEOF("_airbyte_data" -> 'Qty') = 'null') 
			then null 
			else cast("_airbyte_data" -> 'Qty' as varchar) 
		end, 
		cast(null as decimal(38, 9))
	) is null
	) 
THEN JSONB_BUILD_OBJECT('field', 'Qty', 'change', 'NULLED', 'reason', 'DESTINATION_TYPECAST_ERROR') 
ELSE cast(null as jsonb) 
END

Note: Airbyte setting these fields to

null

only happens on subsequent incremental syncs.

The error you're encountering,

invalid input syntax for type numeric: ""1.00""

, suggests that there is an issue with the way the value

"1.00"

is being cast or handled in your SQL query. Specifically, the double quotes around the value are causing the problem. From the provided information, it seems that the error occurs during the normalization step when Airbyte attempts to cast JSONB data to a numeric type. The relevant part of the query is:

sql CASE WHEN (  "_airbyte_data" -> 'Qty' is not null and JSONB_TYPEOF("_airbyte_data" -> 'Qty') <> 'null'   and "pg_temp"."airbyte_safe_cast"(   case when (    "_airbyte_data" -> 'Qty' is null or     JSONB_TYPEOF("_airbyte_data" -> 'Qty') = 'null')     then null     else cast("_airbyte_data" -> 'Qty' as varchar)    end,    cast(null as decimal(38, 9))  ) is null  )  THEN JSONB_BUILD_OBJECT('field', 'Qty', 'change', 'NULLED', 'reason', 'DESTINATION_TYPECAST_ERROR')  ELSE cast(null as jsonb)  END

### Steps to Resolve 1. Check the Source Data: Ensure that the source data does not contain extra double quotes around numeric values. The value should be a valid numeric type without additional quotes. 2. Modify the SQL Query: Adjust the SQL query to handle cases where the value might be improperly quoted. You can use string functions to remove any extraneous quotes before casting. 3. Update Airbyte Version: Ensure you are using the latest version of Airbyte, as there might be fixes or improvements related to data type handling in newer versions. ### Example Adjustment You might need to adjust the SQL query to strip out any extra quotes before attempting to cast the value. Here is an example of how you might modify the query:

sql CASE WHEN (  "_airbyte_data" -> 'Qty' is not null and JSONB_TYPEOF("_airbyte_data" -> 'Qty') <> 'null'   and "pg_temp"."airbyte_safe_cast"(   case when (    "_airbyte_data" -> 'Qty' is null or     JSONB_TYPEOF("_airbyte_data" -> 'Qty') = 'null')     then null     else cast(regexp_replace(cast("_airbyte_data" -> 'Qty' as varchar), '["]', '', 'g') as varchar)    end,    cast(null as decimal(38, 9))  ) is null  )  THEN JSONB_BUILD_OBJECT('field', 'Qty', 'change', 'NULLED', 'reason', 'DESTINATION_TYPECAST_ERROR')  ELSE cast(null as jsonb)  END

In this example,

regexp_replace

is used to remove any double quotes from the value before casting it to a numeric type. ### Additional Resources For more detailed guidance, you can refer to the [Airbyte documentation](https://docs.airbyte.com) or seek further assistance on the [Airbyte community forum](https://discuss.airbyte.io). If you continue to experience issues, please provide additional details or logs to help diagnose the problem further.

@kapa.ai Yes, the problem maybe from the Source Data. Airbyte only wrap these fields with double quotes on subsequent incremental syncs, my source is mssql server.

It seems like you are experiencing an issue where Airbyte wraps fields with double quotes during incremental syncs from an MSSQL source. This behavior can be related to how Airbyte handles data types and formats during the sync process. Based on the information from the knowledge sources, here are a few points that might be relevant to your situation: 1. Datetimeoffset Parsing Issue: - There is a known issue with parsing

datetimeoffset

fields in MSSQL during incremental syncs. This can cause failures if the datetime format is not correctly parsed. - Example error:

java.time.format.DateTimeParseException: Text '2022-11-14 21:20:06.446667 +00:00' could not be parsed at index 19

- [Source](https://github.com/airbytehq/airbyte/issues/32780) 2. Incremental Sync Behavior: - When using the incremental append option, Airbyte might treat updates or deletions as new inserts, which could lead to unexpected behavior in the target database. - If you need to update existing records, you might need to use the incremental + dedup option, but this is only available for certain destinations. - [Source](https://github.com/airbytehq/airbyte/issues/33242) 3. CSV Metadata Wrapping: - There is an issue where the S3 destination connector wraps metadata columns in double quotes during syncs. This might be similar to what you are experiencing with MSSQL. - Example:

"_airbyte_ab_id","_airbyte_emitted_at","_airbyte_data"      "465e901d-739a-4568-a5af-0567496034e8",1709812731552,"{"id":"1","first_name":"Michael","last_name":"Perez","email":"<mailto:mperez0@chronoengine.com|mperez0@chronoengine.com>"}"

- [Source](https://github.com/airbytehq/airbyte/issues/35957) Given these points, here are some actionable steps you can take: 1. Check Data Types: - Ensure that the data types in your MSSQL source are compatible with Airbyte's expected formats. Pay special attention to

datetimeoffset

fields. 2. Review Sync Options: - If you are using incremental append and facing issues with updates, consider if your destination supports incremental + dedup and switch to that if possible. 3. Update Airbyte Version: - Make sure you are using the latest version of Airbyte and the connectors, as there might be fixes for these issues in newer releases. If these steps do not resolve your issue, you might want to provide more specific details or logs to the Airbyte community or support team for further assistance.

@kapa.ai How can I change the query and apply to the process? I use the OSS version.

A message from kapa.ai