Rounding issue with DataFlow Gen2

[mklevemann]

I am loading a fabric data warehouse using Dataflow Gen2 and have run into a weird problem.  Whenever I do any floating point math with a couple of columns of the data I'm uploading, I get a good floating point result in dataflow but when it loads the data warehouse it rounds off the decmial portion of the number.   I am loading it into a decimal(38) field in the database.

Here is an example of the transform statement and you can see the new column does have decimal data in it:

[deborshi_nag · Accepted solution]

Hello @mklevemann

This usually happens when the destination column (in your Fabric Data Warehouse) is defined with no scale, e.g., DECIMAL(38) which defaults to DECIMAL(38,0). In T‑SQL, if you omit the scale, SQL rounds/truncates the fractional part on insert. Dataflow Gen2 shows the correct floating‑point value in the preview, but when it lands in the warehouse it’s implicitly cast to DECIMAL(38,0), so the decimals are lost.

 

Alter the Warehouse column to include scale

Pick a scale that fits your business rules (common choices: 2 for money, 4 or more for rates, 6–12 for scientific/FX).

 

-- Example: keep up to 10 decimal places

ALTER TABLE dbo.YourTable

ALTER COLUMN YourNumericColumn DECIMAL(38, 10) NOT NULL;  -- or NULL if applicable

Hope this helps - please appreciate giving a Kudos or accepting as a Solution! 

[mklevemann]

Thanks!   My brain must not have been working properly that day.  Your solution worked perfectly.

[v-csrikanth]

Hi @mklevemann 
Thanks for reaching out to the Fabric Community.
This behavior occurs because in Fabric Warehouse:
DECIMAL(38) = DECIMAL(38,0)

When Dataflow Gen2 loads data, decimal scale is not inferred, so a column defined as DECIMAL(38) defaults to scale 0, causing the fractional portion to be silently truncated during load — even though the Dataflow preview shows decimals correctly.

Always specify precision AND scale for decimal columns, or pre-create the table before loading:
CREATE TABLE dbo.TableName
(
invoice_amount_with_net_retaining DECIMAL(38,6)
);

Then load the Dataflow into this table.

Note: The rounding does not happen in Dataflow Gen2 — it happens at insert time in the Warehouse due to implicit DECIMAL(38,0).

Thanks,
Cheri Srikanth