Lakehouse table error in Spark notebook in Fabric

For your code above SamKum , the join function requires specifying a dataframe to join into, not a Delta Table.

With the Default Lakehouse attached to your notebook, you can reference the Delta Table to build a dataframe either via the absolute path:

absolute_path = 'abfss://00000000-0000-0000-0000-000000000000@onelake.dfs.fabric.microsoft.com/00000000-0000-0000-0000-000000000000/Tables/DimCustomer'

#Can I connect absolutely?
absolute_df = spark.read.format("delta").load(absolute_path)
display(absolute_df)

Or the Relative Path:

relative_path = 'Tables/DimCustomer'
relative_df = spark.read.format("delta").load(relative_path)
display(relative_df)

Or via a Spark SQL query against the default lakehouse:

#Default way from drag-and-drop
df = spark.sql("SELECT * FROM Databard_Demo.DimCustomer LIMIT 1000")
display(df)

You are correct, using the relative path and Spark SQL is simpler. However, it requires attaching your notebook to a default lakehouse, which must then be managed should you deploy your notebook to different environments (i.e. Dev/Test/Prod). Without a default lakehouse, the Spark SQL and relative paths won't work, and you would have to reference the absolute path.

As far as I'm aware, I've never seen a way where Delta Tables can be referenced directly like in your example, at least not without defining the Delta Table as a variable in PySpark. The closest I've seen is loading the Delta Tables into their own variables, and then using Spark SQL to join the tables. Here's an example:

#These show absolute paths, but you can do relative with a default lakehouse
delta_table_path_1 = "abfss://<your-workspace-id>@onelake.dfs.fabric.microsoft.com/<your-lakehouse-id>/<your-delta-table-path-1>"
delta_table_path_2 = "abfss://<your-workspace-id>@onelake.dfs.fabric.microsoft.com/<your-lakehouse-id>/<your-delta-table-path-2>"

# Load the Delta Tables
delta_table_df_1 = spark.read.format("delta").load(delta_table_path_1)
delta_table_df_2 = spark.read.format("delta").load(delta_table_path_2)

# Create temporary views
delta_table_df_1.createOrReplaceTempView("delta_table_1")
delta_table_df_2.createOrReplaceTempView("delta_table_2")

# Execute a SQL query to join the tables
# Assumes Customer data for demo purposes, but could be any query
joined_df = spark.sql("""
    SELECT 
        t1.CustomerKey, 
        t1.FirstName, 
        t1.LastName, 
        t2.OrderKey, 
        t2.OrderDate
    FROM 
        delta_table_1 t1
    JOIN 
        delta_table_2 t2
    ON 
        t1.CustomerKey = t2.CustomerKey
""")

# Show the result
joined_df.show()

Hope this helps! If it does, be sure to accept an answer so others can learn from this conversation.