Data Variances between dataflow output and Lakehouse data

[sufregs]

Hi Everyone. Been troubleshooting this for a while now.

I have a query in a dataflow that writes into Lakehouse. The result of the query (Power query preview) from the dataflow is as expected. However when I run the dataflow and the table is created in Lakehouse, I see a different result in my amount column. Has anyone faced this before? The data value in Lakehouse is lower than that in the dataflow.

[v-pnaroju-msft]

Hi sufregs,

Based on our understanding, in Dataflows Gen2, Power Query numeric types are translated to Spark or Delta types when data is written to a Fabric Lakehouse. 

Please follow the steps outlined below, as they may help resolve the issue:

1. Ensure the Amount column uses a fixed decimal type such as Currency or Fixed Decimal in each source query participating in Table.Combine.
2. Reapply the type immediately after the Table.Combine step, since combine operations can relax column metadata: Table.TransformColumnTypes(CombinedTable, {{"Amount", Currency.Type}})
3. Confirm the destination schema in the Lakehouse SQL endpoint. The column should be stored as DECIMAL(p, s) and not DOUBLE.

If the discrepancy persists after enforcing the schema and validating the Lakehouse column type, reproduce the issue with a simple dataset. If the behavior remains reproducible with a simple dataset, we kindly request you to raise a support ticket using the provided link:Microsoft Fabric Support and Status | Microsoft Fabric so that the product team can investigate the Dataflow to Lakehouse write behavior in detail.

Additionally, please refer to the link provided below:
Data types in Power Query - Power Query | Microsoft Learn

We hope the information furnished herein helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[sufregs]

This issue occurred again and the previous fix did not work. If any one has any other ideas on how I can resolve this, I'm all ears!

[v-pnaroju-msft]

Hi sufregs,

We are pleased to note that your issue has been resolved. Thank you for sharing your insights and approach in resolving the issue, which will be beneficial to other members of the community. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[sufregs · Accepted solution]

Thanks to everyone for the responses and guidance in resolving the issue.
Steps tried:

Setting amount column to Decimal.Type before writing into lakehouse - Failed

Pre-creating the table in lakehouse before writing into it and defining its data type - Failed
Ensuring the amount column in each of the combined queries was set to Decimal.Type (as last step) prior to combine - Failed

What eventually worked for me was setting the amount column to Decimal.Type at the point of import into every query that was part of the Table.Combine. I also set it as the last step. I did this as well for every column (numeric) that interacted/contributed to the amount column in each of the queries.
I hope this helps someone who faces the same issue. It was literally driving me nuts...

[v-pnaroju-msft]

Thankyou, @deborshi_nag and @nielsvdc for your responses.

Hi sufregs,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @deborshi_nag and @nielsvdc to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[nielsvdc]

Hi @sufregs,

After the Table.Combine step, could you check whether the dataset in Power Query contains more or fewer rows than the table in your Lakehouse? The easiest way is to compare the row counts in both places, or validate by checking the sum of your amount column.

That should help you identify what’s happening before or during the combine step. One or more source tables used in the Table.Combine step might already be filtering rows, or the combine itself could be introducing duplicates.

Hope this helps. If so, please give kudos ‌‌👍 and mark as Accepted Solution ‌‌✔️ to help others. If you resolved your question, let us know what worked for you.

[sufregs]

Hi. The row count is the same before and after the combine step.

[sufregs]

I have done this using Decimal.Type but the variance still exists. Any other guidiance on this would be much appreciated.

[deborshi_nag]

Hello @sufregs This is probably because of a data type translation between Dataflow(PowerQuery) and Lakehouse. I am guessing that the amount in PowerQuery is using a Decimal number (internally that is IEEE Double), different to a Fixed decimal number/Currency. 

It is best if you Fixed decimal number, so ensure that the amount coulmn is set to Fixed Decimal number. Also make sure the Changed Type is the last step before any calculation. 

Confirm that the data type in your Lakehouse is also Decimal (precision, scale) i.e. Decimal (7, 2) rather than a Double. 

[sufregs]

Thank you for the response.
You are right, the Power Query is using Decmal number. I have no calculations within the query. The data type in my lakehouse is also decimal (this is what I defined it to be in the data destination settings). Is there a way to change it in the Lakehouse database?
As its decimal number on both ends, should I still have these variances?

[deborshi_nag]

hello @sufregs can you use Currency in Power Query and run your Pipeline again please? That would used Fixed Decimal with 4 decimal precision. 

[sufregs]

I tried it, I still get the variance. I have narrowed down the issue to a Table.Combine step in my query though. Prior to that the variance does not occur

[deborshi_nag]

great! you narrowed it down! 

Make sure every source table has the same explicit type for Amount before the combine—prefer Decimal.Type or Currency.Type. If the same column (e.g., Amount) has different types across the input tables Power Query will widen the type to Any or Number during the combine.

Later, when writing to Lakehouse, that can materialize as a DOUBLE (float) rather than DECIMAL, causing rounding/precision loss and slightly lower values.