row count after performing SCD2 using Merge statement in delta format.

[chetanhiwale]

Currently, we using merge using deltatable module. if already existing record from silver we are getting in bronze. Here in this condition we should get row count as 0 as there are no changes. But in delta table history it doesnt show any data as newly updated / inserted data as 0. So, how to get newly inserted / updated row count.I am using "describe table table_name" command to get rows count. I also tried Change feed capture but also doesnt capture the changed rows as 0. Any solution to this or anyone faced the same issue

[v-csrikanth]

Hi @chetanhiwale 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Cheri Srikanth

[v-csrikanth]

Hi @chetanhiwale 

When a MERGE operation commits changes, you can use the DESCRIBE HISTORY command to inspect the operationMetrics column for details such as:

• numTargetRowsInserted: Number of rows inserted.
• numTargetRowsUpdated: Number of rows updated.

However, if no changes occur (e.g., identical records), Delta does not create a new version, and this information won't be available. This is expected behavior in Delta Lake.

If you want to ensure a new version is created even when no data changes occur (e.g., for auditing purposes), consider adding metadata during the operation.
For example:
***********************************************
MERGE INTO your_delta_table USING source_table

ON condition

WHEN MATCHED THEN UPDATE SET ...

WHEN NOT MATCHED THEN INSERT ...

COMMENT 'Forced update for audit';
***********************************************

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[nilendraFabric]

Hello @chetanhiwale 

DESCRIBE HISTORY your_delta_table;

You can query the commit history using the `DESCRIBE HISTORY` command and inspect the `operationMetrics` field for your merge operation. This method will show you the counts that the merge operation registered—even if they are zero—but only if a commit was made. When no change is committed (because the records are identical), you won’t see a new row with nonzero metric

operationMetrics: A map containing metrics specific to the operation. For a MERGE operation, for example, this can include the number of rows inserted, updated

[chetanhiwale]

Yeah,  i am using the same method to get the rows count. 

this i am getting before and after performing scd with the given scenario

[chetanhiwale · Accepted solution]

As per my understanding, fabric is not creating any new version when same record is being added to silver , thus describe command is not able to pick the version. Please folks , let me know your thoughts on this.