Forum Discussion
Decimal precision exceeds when copying data from SQL Server to Databricks using Fabric Copy activity
Scenario
I am using Microsoft Fabric Data Pipeline to copy data from SQL Server to Databricks SQL Warehouse using Simba Spark ODBC as the sink.
Steps
Create a Fabric Data Pipeline
Add a Copy Data activity
Source: SQL Server table
Sink: Databricks SQL Warehouse (ODBC – Simba Spark driver)
Run the pipeline
Source Data
SQL Server table contains DECIMAL(38,18) columns
No column has precision greater than 38
Error
The copy activity fails with the following error:
Decimal precision 47 exceeds max precision 38 [DECIMAL_PRECISION_EXCEEDS_MAX_PRECISION] SQLSTATE: 22003Issue
Although the source table only has decimals with precision up to 38, Spark reports that precision is expanding to 47 during the copy operation.
Expected Result
Data should be copied with the same precision and scale as the SQL Server source, without exceeding Spark’s maximum precision.
Question
Why is the decimal precision increasing during the copy, and how can I prevent this while copying data from SQL Server to Databricks using Fabric?
Hi renjith_kumar , so it's a two-questions, let's go one at a time
#1. Why is the decimal precision increasing during the copy? During Fabric Copy using the Spark ODBC driver, Spark does not preserve the original precision during intermediate operations and applies expression widening rules (e.g. when decimals are cast implicitly, moved through an ODBC layer, passed through serialization, etc.), furhermore Fabric Copy also does schema reconciliation and attempts type safety forcing Spark to materialize the entire schema upfront, which triggers decimal widening.
#2. how can I prevent this while copying data from SQL Server to Databricks using Fabric? You can...
a) Explicit CAST in Source Query (Recommended) in a view and use this as the source query in Fabric Copy, not the table.
b) Use a use a pre‑created table as a target and configure Copy Activity to Disable auto‑create and schema drift
Thanks for being so precise in the description of your issue as this makes the analysis easier, hope you find this information useful and you get your pipeline working as expected... a would appreciate a kudos and if you consider, accept this as solution. All the very best!
5 Replies
Hi renjith_kumar,
Thank you for reaching out to the Microsoft fabric community forum.
When the pipeline writes data to Databricks, it uses Spark in the backend. While writing through the Simba ODBC driver, Spark sometimes internally increases the decimal precision, so a DECIMAL(38,18) column can temporarily look like higher precision for example 47. Databricks supports decimal precision only up to 38, so the copy fails. To avoid this, create the target table in Databricks with fixed decimal types instead of auto-create, make sure decimal columns are explicitly mapped in the Copy activity, avoid any implicit casts or transformations, or use a staging step like a Lakehouse before loading into Databricks.
please go through with the below document hope it may resolve your Issue:
DECIMAL type - Azure Databricks - Databricks SQL | Microsoft Learn
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
community Support Team.Hi renjith_kumar,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.Hi renjith_kumar,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Community Support Team.
Hi renjith_kumar , so it's a two-questions, let's go one at a time
#1. Why is the decimal precision increasing during the copy? During Fabric Copy using the Spark ODBC driver, Spark does not preserve the original precision during intermediate operations and applies expression widening rules (e.g. when decimals are cast implicitly, moved through an ODBC layer, passed through serialization, etc.), furhermore Fabric Copy also does schema reconciliation and attempts type safety forcing Spark to materialize the entire schema upfront, which triggers decimal widening.
#2. how can I prevent this while copying data from SQL Server to Databricks using Fabric? You can...
a) Explicit CAST in Source Query (Recommended) in a view and use this as the source query in Fabric Copy, not the table.
b) Use a use a pre‑created table as a target and configure Copy Activity to Disable auto‑create and schema drift
Thanks for being so precise in the description of your issue as this makes the analysis easier, hope you find this information useful and you get your pipeline working as expected... a would appreciate a kudos and if you consider, accept this as solution. All the very best!
Hi renjith_kumar,
I recently encountered the same issue, but when loading from an Oracle database.
Unfortunately the only solution is to cast the types in the source query so that it is smaller than (38,38).
I hope that one day Fabric will add the ability to just allow data truncation in this scenario without failing the pipeline, but until then we need to cast it.
