Unexplainable behavior in Notebook when using pyspark

Hi Morris98 ,

Thanks for using Fabric Community.

As I understand you're facing a strange issue with your DataFrames after deleting data from the lakehouse. Let's break down what's happening and how to fix it.

The Issue:

When you create copies of your DataFrames using select("*").cache(), they seem to be referencing the same data in the Delta Lake tables. This is because DataFrames are like snapshots of data – they don't actually hold the data themselves.

So, when you delete data from the lakehouse tables using spark.sql(), both the original and copied DataFrames are affected because they point to the same underlying data source.

The Fix:

There are a couple of ways to achieve what you want:

1. True Deep Copy:
   Instead of creating a "copy" that references the same data, you can create a truly independent DataFrame. Here's how:
   
   
   from pyspark.sql import Row

   copied_df = df.rdd.map(lambda row: Row(**row.asDict())).toDF()
   
   
   This code goes through each row in your original DataFrame, creates a new row object with the same data, and builds a completely new DataFrame from those new rows.
   
   
2. Read After Deletion:
   Alternatively, you can simply read the data again after deleting it from the lakehouse:
   
   

   # Delete data
   spark.sql(f"DELETE FROM SAMPLE_LH.{table_name}")

   # Read data again for "issues" table
   debug_info = spark.read.format("delta").load(f"SAMPLE_LH.{table_name}")

   
   This ensures your DataFrame reflects the latest state of the data in the lakehouse after the deletion.
   
   
3. Versioning (Optional):
   If you need to work with historical data, Delta Lake's versioning feature can be helpful. You can read a specific version of the data before the deletion for analysis.
   
   

By implementing one of these solutions, you can ensure your DataFrames are not influenced by deletions in the lakehouse and manipulate data independently.

Hope this might help. Do let me know incase of further queries.