Hi all, I have a question concerning the use_singe...
# getting-started
f
Hi all, I have a question concerning the use_singer_decimal property. My pipeline is from tap-oracle (variant: s7clarke10) to target-snowflake (variant: meltanolabs). The source column MYCOLUMN is Number(20,2) in the Oracle DB. My extractor's properties look like this:
Copy code
plugins:
  extractors:
  - name: tap-oracle
    variant: s7clarke10
    pip_url: git+<https://github.com/s7clarke10/pipelinewise-tap-oracle.git>
    config:
      ...
      use_singer_decimal: true
    select:
    - TEST-TEST_TABLE.*
    schema:
      TEST-TEST_TABLE:
        MYCOLUMN:
          type: [string, 'null']
          format: x-singer.decimal
          precision: 20
          scale: 2
- The column MYCOLUMN is extracted in the JSON as expected: ...,"MYCOLUMN":"1234.56",... - However, in Snowflake the column MYCOLUMN is created as NUMBER(38,0) instead of NUMBER(20,2) and inserted as the rounded value '1235' - In case I create the target table in advance as NUMBER(20,2), the inserted value looks like '1235.00' What am I missing here / doing wrong?
Since the values are correct in the extracted JSON, I suspect that the issue might be with the target-snowflake. - Is there a configuration I have to adjust in order to have the correct precision and scale in snowflake? I already tried to move the schema settings to a custom catalog, but that did not help.
In case anyone else stumbles over a similar issue: The cause are different implementations of Singer Decimals in the tap and target: • tap-oracle (s7clarke10) is not based on Meltano SDK and supports
singer.decimal
• target-snowflake (meltanolabs variant), on the other hand, is based on Meltano SDK and supports
x-singer.decimal
Our solution: a custom modified version of target-snowflake (meltanolabs variant) to also support
singer.decimal
with
additionalProperties
scale_precision
. (added a modified version of
_handle_singer_decimal
from singer_sdk connector/sql.py to
jsonschema_to_sql
in the target-snowflake connector.py that can handle
additionalProperties
)