topic Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't in Data Engineering

spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

Zoe_Guest — Thu, 19 Jun 2025 09:08:19 GMT

I have data in a Lakehouse and I have deleted some of it. I am trying to load it from a Fabric Notebook.

When I use spark.sql("SELECT * FROM parquet.`<abfs_path>/Tables/<table_name>`" then I get the old data I have deleted from the lakehouse.

When I use spark.read.load(<abfs_path>/Tables/<table_name>) I dont get this deleted data.

I have to use the abfs path as I am not setting a default lakehouse and can't set one to solve this.

Why is this old data coming up when I use spark.sql when the paths are exactly the same?

Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

wardy912 — Thu, 19 Jun 2025 10:08:14 GMT

The paths are the same but you're using a different method to query them

spark.sql("SELECT * FROM parquet.`<abfs_path>/Tables/<table_name>`")

Spark SQL - may be using cached metadata

spark.read.load("<abfs_path>/Tables/<table_name>")

Dataframe API - reads current state of files

You could add a cell to your notebook that clears the cache if you want to use the Spark SQL code

spark.catalog.clearCache()

Please give a thumbs up if this helps, thanks

Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

Zoe_Guest — Thu, 19 Jun 2025 10:25:15 GMT

Unfortuantly clearing the cache doesn't work.

however this also gets the deleted data, so i think it's in specifying parquet.

spark.read.format("parquet").load(_table_abfs)

I want to be able to use a sql query and the abfs path to the data to load the data, any ideas on how i can do this?

Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

wardy912 — Thu, 19 Jun 2025 10:40:39 GMT

df = spark.sql("""

SELECT *

FROM <lakehouse>.<schema>.<table>

""")

You can also drag the table from the left hand side in the lakehouse to a cell and it will automatically add a SQL query for that table.

Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

Zoe_Guest — Thu, 19 Jun 2025 10:43:16 GMT

I can't set it as the default lakehouse which is why i want to use the abfs path, how do you do this with the abfs path?

Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

Zoe_Guest — Thu, 19 Jun 2025 10:51:08 GMT

solved by changing it to delta

spark.sql("SELECT * FROM delta.`<abfs_path>/Tables/<table_name>`")

Re: spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't

v-prasare — Thu, 19 Jun 2025 12:59:16 GMT

Zoe_Guest Thanks for being part of Fabric community and making it grow.

wardy912 Thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support