Hello, I found a workaround that you might want to try (example code below the listing):
1. Take your Pandas df either by converting a spark one to Pandas (does not have to be loaded from SQL but could also be loaded via spark.read.csv('file') or spark.read.parquet('file') etc.
2. process you data as usual
3. Get list of columns and list of tuples of data. IMPORTANT: no special characters (even whitespaces) allowed in column names. you might need to rename them.
4. Do some spark stuff I do not know what it does, seems to generate a new spark like DataFrame.
5. Save you data. IMPORTANT: option('delta.columnMapping.mode','name') will create the table but it wont be accessible at the SQL endpoint.
6. If the writing fails, you might need to manually change the data type in the columns. This has to be done before step 3.
7. Refresh you lake/warehouse and use your tables :D.
# 1.
df = spark.sql("SELECT * FROM Lakehouse.SQLData").toPandas()
# 2.
test_df = ### processing(df)
# 3.
cols = list(test_df.columns.values) # gets list of columns
ncols = []
for c in cols:
ncols.append(re.sub(r'[-\%#\s\/]','_',c)
data = list(test_df.itertuples(index=False, name=None)) # gets data as list of tuples
# 4.
rdd = spark.sparkContext.parallelize(data)
n_df = rdd.toDF(cols)
# 5.
### Make sure that your column names do not include any special character, also not white space#
n_df.write.format("delta").saveAsTable('auto_test_3')
