Forum Discussion

mklevemann's avatar
mklevemann
Frequent Visitor
7 months ago
Solved

Rounding issue with DataFlow Gen2

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:

 

 

Data Pipeline
  • deborshi_nag's avatar
    deborshi_nag
    7 months ago

    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! 

3 Replies

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    7 months ago

    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



  • deborshi_nag's avatar
    deborshi_nag
    Super User
    7 months ago

    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's avatar
      mklevemann
      Frequent Visitor
      7 months ago

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