Forum Discussion
Advocate IICopy Job from on premise Oracle DB causing discrepancy in number columns
Copying numeric data from an Oracle view to a Fabric Lakehouse produces inconsistent results. Some Batch/Append tests preserve the values, while Snapshot + Incremental / Upsert tests and a separate cast-test destination contain incorrect numbers.
Source types
The four affected Oracle fields are NUMBER columns. Column1 and Column2 have scale 0; precision and scale are unspecified for Column3 and Column4.
Example
For one anonymized sample row:
| Column | Oracle value | Incorrect Lakehouse value |
|---|---|---|
| Column1 | 23 | 1822247737828079764651510857728 |
| Column2 | 16 | 1267650600228229401496703205376 |
| Column3 | 4022.13 | 31866596929349802.016512092896493568 |
| Column4 | 900 | 71305346262837.903834189555302400 |
The incorrect values for Column1 and Column2 equal the Oracle values multiplied by 2^96
The expected values appeared in Oracle query results and previews. The corrupted values were observed in the Lakehouse through its SQL analytics endpoint and Spark.
Tests and results
1-Snapshot + Incremental / Upsert, table source: A full-table copy produced correct sampled integer fields, but incorrect number columns values.
2- Snapshot + Incremental / Upsert, query source: A full-column SELECT * copy also produced corrupted numeric values.
3- Batch/full copy + Append, targeted casts: A one-row test cast ID-like fields to NUMBER(18,0) and amount/quantity fields to NUMBER(28,8); the sampled values matched Oracle.
4- Batch/full copy + Append, high-precision casts: Three quantity samples cast to NUMBER(38,18) matched Oracle.
5- Batch/full copy + Append, uncast tests: An uncast filtered query and an uncast seven-column table copy also preserved the tested values.
6- Snapshot + incremental /upsert, query source with Separate cast-test destination: The sample row remained corrupted there as well, so casts did not fix that tested path.
Is there any limitation related to read mode as watermark mode, and write mode as merge on key columns when using copy jobs from oracle DB's? and how can i resolve this issue.
Thanks
Root Cause and Recommended Resolution
Your test results strongly indicate a Microsoft Fabric Copy Job issue with high-precision Oracle NUMBER columns when using Snapshot + Incremental / Merge (Upsert).
Microsoft has documented a similar known issue where Oracle NUMBER values are incorrectly converted when writing to a Lakehouse using Merge/Upsert.
Key observations:
- Batch + Append preserves the original Oracle values.
- Snapshot + Incremental / Upsert produces incorrect values.
- Explicit casts do not resolve the issue in the tested Upsert scenario.
- The issue also occurs when copying an Oracle VIEW, not just physical tables.
Recommended workaround:
- Use Full Copy + Append for affected tables.
- Explicitly cast Oracle NUMBER columns to appropriate precision and scale.
- Validate source and destination values.
- Avoid Merge/Upsert for affected columns until Microsoft provides a fix.
Additional consideration: Check the metadata exposed by the Oracle view, particularly columns with unspecified precision and scale.
Microsoft reference: Fabric Known Issue – Oracle High-Precision NUMBER Conversion.
Findings: The evidence points towards a conversion issue in the Fabric Copy Job Merge/Upsert path rather than the watermark configuration itself.
If this helps, ✓ Mark as Kudos | Help Other
Text Generated through AI Assitance
2 Replies
Murtaza_GhafoorSuper User
Root Cause and Recommended Resolution
Your test results strongly indicate a Microsoft Fabric Copy Job issue with high-precision Oracle NUMBER columns when using Snapshot + Incremental / Merge (Upsert).
Microsoft has documented a similar known issue where Oracle NUMBER values are incorrectly converted when writing to a Lakehouse using Merge/Upsert.
Key observations:
- Batch + Append preserves the original Oracle values.
- Snapshot + Incremental / Upsert produces incorrect values.
- Explicit casts do not resolve the issue in the tested Upsert scenario.
- The issue also occurs when copying an Oracle VIEW, not just physical tables.
Recommended workaround:
- Use Full Copy + Append for affected tables.
- Explicitly cast Oracle NUMBER columns to appropriate precision and scale.
- Validate source and destination values.
- Avoid Merge/Upsert for affected columns until Microsoft provides a fix.
Additional consideration: Check the metadata exposed by the Oracle view, particularly columns with unspecified precision and scale.
Microsoft reference: Fabric Known Issue – Oracle High-Precision NUMBER Conversion.
Findings: The evidence points towards a conversion issue in the Fabric Copy Job Merge/Upsert path rather than the watermark configuration itself.
If this helps, ✓ Mark as Kudos | Help Other
Text Generated through AI Assitance- mkj1213
UPDATE: What i am copying is a view form the oracle Database not a physical table.
Thought of mentioning this info as it wasn't mentioned before in the original post