I am getting below error while using

tap-mssql

to copy data into

target-jsonl

. It looks like

tap-mssql

supports decimal value upto Decimal('1E-17'). Any way to change configuration or increase size for decimal values?

jsonschema.exceptions.ValidationError: Decimal('0.002045454545454545') is not a multiple of Decimal('1E-17') cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl
Failed validating 'multipleOf' in schema['properties']['col_name']: Decimal('0.002045454545454545')

You can probably override the

multipleOf

property of the field's schema using Meltano's schema extra.

Hi, I strongly recommend that you don't emit numeric data using the default pattern of emitting it in a numeric form. I recommend that you enable the setting

use_singer_decimal

if you are using the following tap https://github.com/wintersrd/pipelinewise-tap-mssql. I choose to move numeric data as strings to avoid rounding issues. In the target system I cast the data to the appropriate datatype. I understand there is more work, however it is a lot safer especially where you are working with decimal data with a large precision and scale. We have a custom version of target snowflake which will read the additional properties and will correctly cast the data as it is loaded into Snowflake. Otherwise cast the data to the correct numeric in your transform tool.

Optional:

To emit all numeric values as strings and treat floats as string data types for the target, set use_singer_decimal to true. The resulting SCHEMA message will contain an attribute in additionalProperties containing the scale and precision of the discovered property:

"property": {
            "inclusion": "available",
            "format": "singer.decimal",
            "type": [
              "null",
              "number"
            ],
            "additionalProperties": {
              "scale_precision": "(12,0)"
            }
Usage:

{
  "use_singer_decimal": true
}

@steve_clarke @Edgar Ramírez (Arch.dev) I also tried

use_singer_decimal

setting it to True, but getting below error

jsonschema.exceptions.ValidationError: '0.0000' is not of type 'null', 'number' cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl
 Failed validating 'type' in schema['properties']['col_name']: cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl
2024-02-20T06:46:36.240386Z [info     ]     {'additionalProperties': {'scale_precision': '(None,4)'}, cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl
2024-02-20T06:46:36.240465Z [info     ]      'format': 'singer.decimal', cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl
2024-02-20T06:46:36.240543Z [info     ]      'inclusion': 'available', cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl
2024-02-20T06:46:36.240666Z [info     ]      'type': ['null', 'number']} cmd_type=elb consumer=True name=target-jsonl producer=False stdio=stderr string_id=target-jsonl

Hi @Rahul Sawale, in my mind there is a scenario where the single decimal logic is not handling either the values e.g. 0 correctly or the datatype - I suspect there needs to be an enhancement to the tap to cater for a specific scenario. To assist, are you able to describe the table structure in SQL Server and identify the column in question so I understand the native datatype, scale and precision. It would also be good to have a sample of a few distinct values related to this column. I would like to be able to replicate this issue essentially to understand what needs to be fixed.

@steve_clarke Please find below column definition from

tap.properties.json

"DLD_ExemptAmount": {
            "inclusion": "available",
            "format": "singer.decimal",
            "type": [
              "null",
              "number"
            ],
            "additionalProperties": {
              "scale_precision": "(None,4)"
            }
          }

Also find below column data type in SQL server database

Thank you @Rahul Sawale. I will look to replicate this issue, and will let you know what I find out. I do have a lot of work on so it could be several business working days before I can look at this properly. Ultimately I would like to raise a PR to resolve the Singer Decimal issue.

Sure. Thanks !

Hi @Rahul Sawale, I have a PR branch built which will resolve the issue with Singer Decimal. https://github.com/wintersrd/pipelinewise-tap-mssql/pull/65 It is in for review, but you are welcome to test that it works with Singer Decimal enabled.

Sure I will re-test. Thanks

Don't know if it's relevant, but I see this in the logs of the failed

Publish to PyPI

job:

Installing dependencies from lock file

pyproject.toml changed significantly since poetry.lock was last generated. Run `poetry lock [--no-update]` to fix the lock file.

Okay, that sounds like it is on me. I should have re-built the lock file as part of my release. Thanks @Edgar Ramírez (Arch.dev) for that heads up. I can look at re-building the lock file and push another release through.

ok. I will install as above and try