topic Re: Duplicated rows between notebook and SQL Endpoint in Data Engineering

Duplicated rows between notebook and SQL Endpoint

amaaiia — Mon, 19 Feb 2024 08:37:59 GMT

I'm having troubles when trying to count the number of rows in my table from Lakehouse.

If I count the rows from SQL Endpoint, I get 221555 rows, if I read the table from notebook and then I count the rows (df.count()) I get that number twice: 443110

How can this be possible?

Re: Duplicated rows between notebook and SQL Endpoint

Anonymous — Mon, 19 Feb 2024 08:45:16 GMT

Hi @amaaiia
Thanks for using Fabric Community.
Can you please provide the screenshots for the SQL code and the notebook code? This would help me to understand the question better.
Thanks

Re: Duplicated rows between notebook and SQL Endpoint

amaaiia — Mon, 19 Feb 2024 09:24:05 GMT

Sure, when I get the number of rows from SQL endpoint:

And when I count them through notebook:

Re: Duplicated rows between notebook and SQL Endpoint

Anonymous — Mon, 19 Feb 2024 09:19:23 GMT

Hi @amaaiia
I tried checking the same with my Lakehouse tables, but the count is same. I checked for two tables.

The discrepancy in row counts between SQL Endpoint and notebook could be due to several reasons:

Data Duplication: There might be duplicate rows in your data. When you read the data into a DataFrame and use df.count(), it counts all rows, including duplicates. If this is the case, you can remove duplicates using the df.dropDuplicates() function.

Data Inconsistency: There might be inconsistencies between the data in your SQL Endpoint and the data in your notebook. This could be due to issues with data ingestion, data updates, or data synchronization. You can verify this by comparing a subset of your data in both SQL Endpoint and notebook.

Caching Issues: Sometimes, Spark might cache the DataFrame, and if the underlying data changes, the cached DataFrame might not reflect these changes2. You can try to clear the cache using the spark.catalog.clearCache() function in your notebook.

Hope this helps. Please let me know if you have any further questions.

Re: Duplicated rows between notebook and SQL Endpoint

Anonymous — Tue, 20 Feb 2024 14:12:56 GMT

Hi @amaaiia
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Duplicated rows between notebook and SQL Endpoint

amaaiia — Tue, 20 Feb 2024 18:55:02 GMT

Hi,

I didn't find a solution. As I was in dev environment trying a demo I just deleted an laoded data again. I'm not having duplicates now. I hope this won't happen again.

Re: Duplicated rows between notebook and SQL Endpoint

frithjof_v — Tue, 20 Feb 2024 21:26:05 GMT

@amaaiia Thanks for sharing!

Did you use Dataflow Gen2 to ingest data into your Lakehouse?

Here is a similar issue:

https://community.fabric.microsoft.com/t5/General-Discussion/Duplicated-Rows-In-Tables-Built-By-Notebook/m-p/3680801#M3995

Re: Duplicated rows between notebook and SQL Endpoint

MysticSapphire — Fri, 23 Feb 2024 13:36:49 GMT

I'm experiencing a similar issue:
My dataflow Gen2 is storing data in lakehouse. Once in a while the 'replace' table setting in the dataflow Gen2 doesn't seem to work and it results in having the same data copied twice. It only seems to be affecting smaller tables. If I delete the table it works for a few days but then suddenly there are duplicates again.

Re: Duplicated rows between notebook and SQL Endpoint

frithjof_v — Thu, 04 Apr 2024 15:02:09 GMT

I created an idea: https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=cea60fc6-93f2-ee11-a73e-6045bd7cb2b6

Please vote if you want this issue to be fixed

Re: Duplicated rows between notebook and SQL Endpoint

Scott_Powell — Thu, 04 Apr 2024 15:55:16 GMT

Hi all, there's apparently a bug where the metadata on which parquet file is the latest and greatest can get hosed up between the lakehouse SQL endpoint and the notebooks. I worked a ticket with Microsoft and they had me run the following code. After running it, wait 30 minutes or so to retry running the notebook. In my case this perfectly fixed the issue.

mssparkutils.fs.unmount("/default", {"scope": "default_lh"})

sc._jvm.com.microsoft.spark.notebook.common.trident.TridentRuntimeContext.reset()

sc._jvm.com.microsoft.spark.notebook.common.trident.TridentRuntimeContext.personalizeSession()

Hope this helps,

Scott

Re: Duplicated rows between notebook and SQL Endpoint

Anonymous — Thu, 04 Apr 2024 16:15:17 GMT

Hi @Scott_Powell
Thanks for sharing the solution here. Please continue using Fabric Community for any help regarding your queries.

Re: Duplicated rows between notebook and SQL Endpoint

DCELL — Wed, 05 Mar 2025 21:49:05 GMT

Does this mean that when reading lakehouse tables from a notebook that I need to run these 3 lines of code for every table I read??