Skip to content

Decimal precision lost in some columns after upgrade to sling==1.4.12 #636

Closed
Closed
Decimal precision lost in some columns after upgrade to sling==1.4.12#636

Description

@SIVERTKRISTIANSEN
SIVERTKRISTIANSEN
opened on Aug 29, 2025

Description
After upgrading to sling==1.4.12, some numeric columns from Oracle sources lose decimal precision and are rounded to whole numbers. This only affects certain columns, while others keep the correct precision.

Impact

Downstream tools (e.g., reports, Excel extracts) show incorrect rounded values.

Business reporting accuracy is compromised for affected datasets.

Steps to Reproduce

Upgrade to sling==1.4.12.

Ingest Oracle data containing decimal fields (expected NUMBER(24,6)).

Observe that some columns are rounded to whole numbers instead of keeping decimals.

Expected Behavior

Decimal fields should retain their defined precision from the Oracle source.

Observed Behavior

Some columns lose decimals and display only whole numbers.

Issue appears inconsistent across columns.

Notes

Likely related to auto-profiling of data types not enforcing source definitions.

It would be useful to allow disabling auto-profiling so that source-defined types are applied consistently.

Environment

Sling version: 1.4.12

Source: Oracle

Activity

  1. flarco commented on Aug 29, 2025

    @flarco
    flarco
    on Aug 29, 2025
    Collaborator

    @SIVERTKRISTIANSEN thanks for reporting, this version is a bit old now (latest is 1.4.19).
    Can you try with the latest version to see if you face the same issue?

    Also, I will need some debug logs and the replication config. Can you give example of original values which are losing precision? What is the column type DDL in the target database?

    There is a test specifically for this (created after your last issue: #584), which runs and need to pass before a new release. Here is the log for a new run:

    84 | sling run -r cmd/sling/tests/replications/r.28.oracle_number_scale.yaml
2025-08-29 07:54:28 INF Sling CLI | https://slingdata.io
2025-08-29 07:54:28 DBG executing hook "start-01" (type: query)
2025-08-29 07:54:29 DBG CLI Pro token validated
2025-08-29 07:54:29 DBG opened "oracle" connection (conn-oracle-Jk9)
2025-08-29 07:54:29 DBG BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ORACLE.TEST_NUMBER_PRECISION PURGE';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END
2025-08-29 07:54:29 DBG successfully executed hook "start-01" (type: query)
2025-08-29 07:54:29 DBG executing hook "start-02" (type: query)
2025-08-29 07:54:29 DBG CREATE TABLE ORACLE.TEST_NUMBER_PRECISION (
  id NUMBER,
  -- NUMBER without precision/scale can store decimals
  value_decimal NUMBER,
  -- NUMBER(10,2) has explicit scale
  value_explicit NUMBER(10,2),
  -- NUMBER(10) has scale of 0 
  value_integer NUMBER(10)
)
2025-08-29 07:54:29 DBG successfully executed hook "start-02" (type: query)
2025-08-29 07:54:29 DBG executing hook "start-03" (type: query)
2025-08-29 07:54:29 DBG INSERT INTO ORACLE.TEST_NUMBER_PRECISION (id, value_decimal, value_explicit, value_integer) 
VALUES (1, 0.01233, 0.01, 10)
2025-08-29 07:54:29 DBG successfully executed hook "start-03" (type: query)
2025-08-29 07:54:29 DBG executing hook "start-04" (type: query)
2025-08-29 07:54:29 DBG INSERT INTO ORACLE.TEST_NUMBER_PRECISION (id, value_decimal, value_explicit, value_integer) 
VALUES (2, 123.456789, 123.45, 123)
2025-08-29 07:54:29 DBG successfully executed hook "start-04" (type: query)
2025-08-29 07:54:29 DBG executing hook "start-05" (type: query)
2025-08-29 07:54:29 DBG select column_name, data_type, data_precision, data_scale
from all_tab_columns
where owner = 'ORACLE'
  and table_name = 'TEST_NUMBER_PRECISION'
  and column_name = 'VALUE_DECIMAL'
2025-08-29 07:54:30 DBG successfully executed hook "start-05" (type: query)
2025-08-29 07:54:30 INF Checking Oracle metadata for VALUE_DECIMAL column:
metadata_check => [{"column_name":"VALUE_DECIMAL","data_precision":null,"data_scale":null,"data_type":"NUMBER"}]
data_scale => 

2025-08-29 07:54:30 DBG executing hook "start-07" (type: check)
2025-08-29 07:54:30 DBG successfully executed hook "start-07" (type: check)
2025-08-29 07:54:30 DBG executing hook "start-08" (type: query)
2025-08-29 07:54:32 DBG opened "snowflake" connection (conn-snowflake-lqa)
2025-08-29 07:54:34 DBG drop table if exists public.test_number_precision
2025-08-29 07:54:34 DBG successfully executed hook "start-08" (type: query)
2025-08-29 07:54:34 INF Sling Replication | ORACLE -> SNOWFLAKE | ORACLE.TEST_NUMBER_PRECISION
2025-08-29 07:54:34 DBG Sling version: dev (darwin arm64)
2025-08-29 07:54:34 DBG type is db-db
2025-08-29 07:54:34 DBG using: {"columns":null,"mode":"full-refresh","select":null,"transforms":null}
2025-08-29 07:54:34 DBG using source options: {"empty_as_null":false,"datetime_format":"AUTO","max_decimals":-1}
2025-08-29 07:54:34 DBG using target options: {"datetime_format":"auto","file_max_rows":0,"max_decimals":-1,"use_bulk":true,"add_new_columns":true,"adjust_column_type":false,"column_casing":"normalize"}
2025-08-29 07:54:34 INF connecting to source database (oracle)
2025-08-29 07:54:34 DBG opened "oracle" connection (conn-oracle-2nR)
2025-08-29 07:54:34 INF connecting to target database (snowflake)
2025-08-29 07:54:35 DBG opened "snowflake" connection (conn-snowflake-Vo6)
2025-08-29 07:54:38 INF reading from source database
2025-08-29 07:54:38 DBG select * from "ORACLE"."TEST_NUMBER_PRECISION"
2025-08-29 07:54:38 DBG applying column casing (normalize) for target type (snowflake)
2025-08-29 07:54:38 INF writing to target database [mode: full-refresh]
2025-08-29 07:54:39 DBG drop table if exists "PUBLIC"."TEST_NUMBER_PRECISION_TMP"
2025-08-29 07:54:39 DBG table "PUBLIC"."TEST_NUMBER_PRECISION_TMP" dropped
2025-08-29 07:54:40 DBG create transient table "PUBLIC"."TEST_NUMBER_PRECISION_TMP" ("ID" integer,
  "VALUE_DECIMAL" decimal(24,6),
  "VALUE_EXPLICIT" decimal(10,2),
  "VALUE_INTEGER" integer)
2025-08-29 07:54:41 INF created table "PUBLIC"."TEST_NUMBER_PRECISION_TMP"
2025-08-29 07:54:41 INF streaming data
2025-08-29 07:54:42 DBG CREATE STAGE IF NOT EXISTS "PUBLIC"."SLING_STAGING"
2025-08-29 07:54:43 DBG USE SCHEMA PUBLIC
2025-08-29 07:54:43 DBG opened "file" connection (conn-file-tW6)
2025-08-29 07:54:43 DBG writing to file:///var/folders/49/1zc24t595j79t5mw7_t9gtxr0000gn/T/snowflake/put/public.test_number_precision_tmp/2025-08-29T075443.753/part.01 [fileRowLimit=500000 fileBytesLimit=300000000 compression=zstd concurrency=7 useBufferedStream=false fileFormat=csv singleFile=false]
2025-08-29 07:54:43 DBG REMOVE @"PUBLIC"."SLING_STAGING"/public.test_number_precision_tmp/2025-08-29T075443.753
2025-08-29 07:54:44 DBG PUT 'file:///var/folders/49/1zc24t595j79t5mw7_t9gtxr0000gn/T/snowflake/put/public.test_number_precision_tmp/2025-08-29T075443.753/part.01.0001.csv.zst' @"PUBLIC"."SLING_STAGING"/public.test_number_precision_tmp/2025-08-29T075443.753 PARALLEL=10 AUTO_COMPRESS=FALSE
2025-08-29 07:54:46 DBG COPY INTO "PUBLIC"."TEST_NUMBER_PRECISION_TMP" ("ID", "VALUE_DECIMAL", "VALUE_EXPLICIT", "VALUE_INTEGER")
from ( 
  select T.$1, T.$2, T.$3, T.$4
  from @"PUBLIC"."SLING_STAGING"/public.test_number_precision_tmp/2025-08-29T075443.753 as T
)
FILE_FORMAT = (
  TYPE = CSV
  RECORD_DELIMITER = '\n'
  ESCAPE_UNENCLOSED_FIELD = NONE
  FIELD_OPTIONALLY_ENCLOSED_BY = '0x22'
  EMPTY_FIELD_AS_NULL = FALSE
  NULL_IF = '\\N'
  SKIP_HEADER = 1
  REPLACE_INVALID_CHARACTERS = TRUE
)
ON_ERROR = ABORT_STATEMENT
2025-08-29 07:54:48 DBG 
+-------------------------------------------------------------------------------------------+--------+-------------+-------------+
| FILE                                                                                      | STATUS | ROWS_LOADED | ERRORS_SEEN |
+-------------------------------------------------------------------------------------------+--------+-------------+-------------+
| sling_staging/public.test_number_precision_tmp/2025-08-29T075443.753/part.01.0001.csv.zst | LOADED |           2 |           0 |
+-------------------------------------------------------------------------------------------+--------+-------------+-------------+
2025-08-29 07:54:48 DBG REMOVE @"PUBLIC"."SLING_STAGING"/public.test_number_precision_tmp/2025-08-29T075443.753
2025-08-29 07:54:49 DBG select count(*) cnt from "PUBLIC"."TEST_NUMBER_PRECISION_TMP"
2025-08-29 07:54:50 DBG drop table if exists "PUBLIC"."TEST_NUMBER_PRECISION"
2025-08-29 07:54:50 DBG table "PUBLIC"."TEST_NUMBER_PRECISION" dropped
2025-08-29 07:54:50 DBG create table "PUBLIC"."TEST_NUMBER_PRECISION" ("ID" integer,
  "VALUE_DECIMAL" decimal(24,6),
  "VALUE_EXPLICIT" decimal(10,2),
  "VALUE_INTEGER" integer)
2025-08-29 07:54:51 INF created table "PUBLIC"."TEST_NUMBER_PRECISION"
2025-08-29 07:54:51 DBG insert into "PUBLIC"."TEST_NUMBER_PRECISION" ("ID", "VALUE_DECIMAL", "VALUE_EXPLICIT", "VALUE_INTEGER") select "ID" as "ID", "VALUE_DECIMAL" as "VALUE_DECIMAL", "VALUE_EXPLICIT" as "VALUE_EXPLICIT", "VALUE_INTEGER" as "VALUE_INTEGER" from "PUBLIC"."TEST_NUMBER_PRECISION_TMP"
2025-08-29 07:54:52 DBG inserted rows into "PUBLIC"."TEST_NUMBER_PRECISION" from temp table "PUBLIC"."TEST_NUMBER_PRECISION_TMP"
2025-08-29 07:54:52 INF inserted 2 rows into "PUBLIC"."TEST_NUMBER_PRECISION" in 18 secs [0 r/s] [63 B]
2025-08-29 07:54:52 DBG drop table if exists "PUBLIC"."TEST_NUMBER_PRECISION_TMP"
2025-08-29 07:54:53 DBG table "PUBLIC"."TEST_NUMBER_PRECISION_TMP" dropped
2025-08-29 07:54:54 DBG closed "snowflake" connection (conn-snowflake-Vo6)
2025-08-29 07:54:54 DBG closed "oracle" connection (conn-oracle-2nR)
2025-08-29 07:54:54 INF execution succeeded
2025-08-29 07:54:54 DBG executing hook "end-01" (type: query)
2025-08-29 07:54:54 DBG select * from public.test_number_precision order by id
2025-08-29 07:54:54 DBG successfully executed hook "end-01" (type: query)
2025-08-29 07:54:54 INF store.result => 
+----+---------------+----------------+---------------+
| ID | VALUE_DECIMAL | VALUE_EXPLICIT | VALUE_INTEGER |
+----+---------------+----------------+---------------+
| 1  | 0.012330      | 0.01           | 10            |
| 2  | 123.456789    | 123.45         | 123           |
+----+---------------+----------------+---------------+
store.result[0] => {"id":1,"value_decimal":"0.012330","value_explicit":"0.01","value_integer":10}
store.result[0].value_decimal => 0.012330
store.result[1] => {"id":2,"value_decimal":"123.456789","value_explicit":"123.45","value_integer":123}
store.result[1].value_decimal => 123.456789

2025-08-29 07:54:54 DBG executing hook "end-03" (type: check)
2025-08-29 07:54:54 DBG successfully executed hook "end-03" (type: check)
2025-08-29 07:54:54 DBG executing hook "end-04" (type: check)
2025-08-29 07:54:54 DBG successfully executed hook "end-04" (type: check)
2025-08-29 07:54:54 DBG executing hook "end-05" (type: check)
2025-08-29 07:54:54 DBG successfully executed hook "end-05" (type: check)
2025-08-29 07:54:54 DBG executing hook "end-06" (type: query)
2025-08-29 07:54:54 DBG BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ORACLE.TEST_NUMBER_PRECISION PURGE';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END
2025-08-29 07:54:54 DBG successfully executed hook "end-06" (type: query)
2025-08-29 07:54:54 DBG executing hook "end-07" (type: query)
2025-08-29 07:54:54 DBG drop table if exists public.test_number_precision
2025-08-29 07:54:55 DBG successfully executed hook "end-07" (type: query)

2025-08-29 07:54:55 DBG closed "snowflake" connection (conn-snowflake-lqa)
2025-08-29 07:54:55 DBG closed "oracle" connection (conn-oracle-Jk9)

  2. SIVERTKRISTIANSEN commented on Sep 1, 2025

    @SIVERTKRISTIANSEN
    SIVERTKRISTIANSEN
    on Sep 1, 2025
    Author

    Thanks for the quick reply!

    Status:

    Upgraded and re-tested on sling==1.4.19 — the issue persists: several Oracle numeric columns that should carry scale end up created/handled without scale at some stage, and decimals are effectively lost/rounded in downstream use. Now we have tested with version 1.4.4 and the newer versions. The 1.4.4 works perfectly fine.

    Replication config:

    source: ifs8
target: snowflake_ingest
defaults:
  mode: truncate
  object: ods.{stream_table}
  source_options:
    max_decimals: 38
  target_options:
    max_decimals: 38
    adjust_column_type: false

streams:
  ifsinfo.tm_credit_rep_month:
    object: ods_ifs8_tm_credit_rep_month
    primary_key: ['ledger_item_id', 'company', 'i_invoice_id']

env:
  SLING_LOADED_AT_COLUMN: timestamp
  SLING_LOGGING: JSON
  SLING_SHOW_PROGRESS: true
  SLING_DISABLE_TELEMETRY: true
  SLING_KEEP_TEMP: true

    Run details (from --log-level=debug)

    • Sling: 1.4.19 (linux amd64)

    • Source options: {"empty_as_null":false,"datetime_format":"AUTO","max_decimals":38}

    • Target options: {"datetime_format":"auto","file_max_rows":0,"max_decimals":38,"use_bulk":true,"add_new_columns":true,"adjust_column_type":false,"column_casing":"normalize"}

    • Source query: select * from "IFSO"."TM_CREDIT_REP_MONTH"

    Evidence from the same execution

    1. TMP table DDL created by Sling (excerpt) — mixed typing for similar numeric fields: some integers, some scaled decimals:
    -- ODS_IFS8_TM_CREDIT_REP_MONTH_TMP
...
SALE_DOM_AMOUNT        NUMBER(38,0),
SALE_CURR_AMOUNT       NUMBER(38,0),
OPEN_CURR_AMOUNT       NUMBER(24,6),
DOM_OUTST_INTERVAL_0   NUMBER(38,0),
CURR_OUTST_INTERVAL_0  NUMBER(38,0),
...
DOM_OUTST_INTERVAL_4   NUMBER(38,0),
CURR_OUTST_INTERVAL_4  NUMBER(38,0),
DOM_OUTST_INTERVAL_5   NUMBER(24,6),
CURR_OUTST_INTERVAL_5  NUMBER(24,6),
...

    Sling then logs type changes “integer → decimal” for those same columns (even though adjust_column_type: false):

    
column type change for SALE_DOM_AMOUNT (integer to decimal)
column type change for SALE_CURR_AMOUNT (integer to decimal)
column type change for DOM_OUTST_INTERVAL_0 (integer to decimal)
column type change for CURR_OUTST_INTERVAL_0 (integer to decimal)
... for _1/_2/_3/_4, etc.

    Final target table DDL — many columns end up as NUMBER(38,10) (not the expected source scale like NUMBER(24,6)), and timestamps are also normalized:

    -- ODS_IFS8_TM_CREDIT_REP_MONTH
...
DOM_OUTST_INTERVAL_0   NUMBER(38,10),
SALE_DOM_AMOUNT        NUMBER(38,10),
DOM_OUTST_INTERVAL_5   NUMBER(38,10),
OPEN_CURR_AMOUNT       NUMBER(38,10),
SALE_CURR_AMOUNT       NUMBER(38,10),
...

    What we believe is happening

    Within a single run, the TMP table is created with inconsistent numeric types (some NUMBER(38,0) and some NUMBER(24,6)), and then Sling emits multiple “integer → decimal” adjustments despite adjust_column_type:false.

    The final table types consolidate to NUMBER(38,10) for many fields, which differs from the Oracle column definitions (e.g., NUMBER(24,6)), and this mismatch appears to be the root of decimal handling issues we’re seeing in downstream tools.

    What we can additionally provide (say the word and we’ll attach):

    Full debug log from this run.

    Oracle column metadata for affected columns (data_type, data_precision, data_scale).

    SHOW CREATE TABLE / DESC TABLE for target (TMP and final) before/after run.

    A few concrete source→target value examples illustrating decimal loss/rounding (sanitized).

    Questions for guidance

    1. Why does the TMP DDL assign NUMBER(38,0) to several fields that have scale at the Oracle source, while other fields in the same table become NUMBER(24,6)?

    2. With max_decimals=38 and adjust_column_type=false, what is the expected type selection path? Should we ever see “integer → decimal” transitions logged?

    3. Is there a recommended way to force strict preservation of source scale/precision from Oracle (e.g., a setting to bypass any auto-profiling/inference for numeric types)?

  3. flarco commented on Sep 1, 2025

    @flarco
    flarco
    on Sep 1, 2025
    Collaborator

    Hey @SIVERTKRISTIANSEN , can you try with the dev build for upcoming 1.4.20? There were some changes pushed to improve decimal logic. If you're using python, set the env var SLING_BINARY to the path of the binary so it can use it.

    Furthermore, you should use column typing instead of max_decimals (that will give you more control over the DDL generated):

    target_options:
  column_typing:
    decimal:
      min_precision: <int>
      max_precision: <int>
      min_scale: <int>
      max_scale: <int>

  4. flarco commented on Sep 3, 2025

    @flarco
    flarco
    on Sep 3, 2025
    Collaborator

    1.4.20 was released which should have fixed this. Closing, if the issue persists, feel free to re-open this issue.

  5. flarco
    closed this as completedon Sep 3, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions