To replicate your scenario, you need an All-Purpose Cluster and a notebook defaulted to python language. Then query a table using %sql as below. This creates a temp dataframe for you to use it in the python cells. Keep in mind, that this dataframe keep changing as you execute a different %sql cell.
%sql
-- cell 1
select * from catalog.schema.123_sample# cell 2
display(_sqldf)
To summarize, the %sql magic command behaves differently depending on whether your Databricks notebook is connected to an All-Purpose cluster or a SQL Warehouse.
- All-Purpose Cluster: %sql creates a DataFrame named _sqldf that you can use in subsequent Python cells.
- SQL Warehouse: %sql executes the query but does not create the _sqldf DataFrame.
Please let me know for anything, else mark it as a solution.
