Re: floating precision error in databricks

Aviral-Bhardwaj · Friday

Hey team,

I ran into this issue back in 2019, but I don't recall how we resolved it. I'm looking for guidance from experienced Spark/Databricks engineers.

Our source system contains values like:

source in-databricks source in-databricks

24.5638	24.560000	24.5600	24.5700
18.4421	18.440000	18.4400	18.4500
32.9187	32.910000	32.9100	32.9200
15.7749	15.770000	15.7700	15.7800
27.1365	27.130000	27.1300	27.1400

My requirement is simple: perform a SELECT and see the exact values stored in the source. I don't want any rounding, truncation, casting, or formatting changes.

This is critical financial data where every decimal place matters. Is there a Spark/Databricks setting, data type handling issue, or display behavior that could cause precision differences between the source and Databricks?

Would appreciate any suggestions from anyone who has dealt with this before.

Thanks

AB

AviralBhardwaj

Ashwin_DSA · Friday

Hi @Aviral-Bhardwaj,

I don't think this is a display issue, and it isn't normal rounding either.

In your first pair, 24.5638 became 24.560000 and 18.4421 became 18.440000. That's truncation (floor) to 2 decimal places.
In your second pair, 24.5638 became 24.5700 and 18.4421 became 18.4500. That's a ceiling to 2 decimal places.

In both cases, the trailing zeros indicate that the extra digits had already been removed before the data reached the table. They were later padded back out to a wider scale.

Databricks doesn't silently floor or ceil DECIMAL values. A CAST to a smaller scale rounds half up, so 24.5638 cast to DECIMAL(10,2) gives 24.56, not 24.57. The DECIMAL type docs have examples. DECIMAL columns are also displayed at their full declared scale. So something in your ingestion path is probably converting to 2 decimals on purpose, most likely an explicit FLOOR, CEIL or TRUNC, a target schema with scale 2, or a step in the extract tool.

CAn you run DESCRIBE TABLE on the Databricks table and on any staging or intermediate tables to check each column's precision and scale? You can also go through the pipeline code and any ETL or extract tool settings for rounding functions or a DECIMAL(p,2) cast. If you land files first, check what the CSV or Parquet files actually contain, and make sure you read them with an explicit DECIMAL schema rather than inferSchema.

If you read over JDBC, check the type mapping. For Oracle, for example, NUMBER without a declared precision maps to DECIMAL(38,10) (Oracle type mappings). That keeps your 4 decimals, but a custom schema or a downstream cast can still cut them. You can set the exact type yourself with the JDBC customSchema option, for example customSchema "amount DECIMAL(18,4)".

Avoid DOUBLE and FLOAT for financial data. They are base-2 types and can't represent many base-10 decimals exactly (DOUBLE type).

Pick one record, such as the 24.5638 row, and trace it through each stage: source query result, landed file, bronze table, then final table. Run typeof(col) on the value at every stage. The stage where it first shows up as 24.56 or 24.57 is where the conversion happens. Define the column as DECIMAL with the same precision and scale as the source from that stage onward, and the values should match exactly.

If you share how you ingest the data (JDBC, Auto Loader, Lakeflow Connect or a third-party tool) and the column definition in the source, the community can help narrow it down.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

Regards,
Ashwin | Delivery Solution Architect @ Databricks
Helping you build and scale the Data Intelligence Platform.
***Opinions are my own***

indradbxlearn · yesterday

Hi @Aviral-Bhardwaj -

From your sample it looks like the values are losing everything past the 2nd decimal place (e.g. 24.5638 → 24.56 / 24.57), which usually points to the scale of the target column or a cast in the pipeline, not a Databricks display setting.

Could you share:

The target column’s data type (DESCRIBE TABLE your_table), e.g. is it DECIMAL(10,2), FLOAT, DOUBLE?
How the data is loaded (JDBC read, Auto Loader, COPY INTO, or a file export) and whether a schema is inferred or explicitly defined?
Whether any transformation in between uses CAST, ROUND, or format_number?

If it’s a DECIMAL scale issue, the fix is to define the column with enough scale (e.g. DECIMAL(18,6)) and re-load. If you’re reading via JDBC, also check the source column’s precision/scale, since Spark maps it from the source metadata.