Data not inserted on delta table

Hello Jona,

Thank you for your question about the data insertion issue in your Azure Synapse Notebook with Delta tables. It sounds like you've set up a simple table creation script using Spark SQL, inserted some rows, but when you query it afterward, nothing shows up. This is a common gotcha with Delta tables in Synapse, often related to how the table is managed, session scope, or the way inserts are committed.

Let's break it down and get this fixed step by step. Based on what you've described, your table is being created as a managed table (without a explicit LOCATION clause), which means its data is stored in the Synapse warehouse path. However, inserts via %%sql might not always persist or become immediately visible if there's a session mismatch or if the table isn't properly registered in the metastore.

Quick Verification

First, confirm if the data is actually being written to storage:

• • Switch to a PySpark cell (remove %%sql) and run this to check the underlying Delta files: ```
  delta_path = "abfss://<your-filesystem>@<your-storage-account>.dfs.core.windows.net/warehouse/myTable" # Adjust based on your warehouse path

df = spark.read.format("delta").load(delta_path)

display(df) ``

If this shows your data, the issue is with how the table is registered or queried in SQL. If it returns nothing, the inserts aren't landing.

Likely Causes and Fixes

1. Managed Table Persistence: In Synapse, managed Delta tables store data in the default warehouse location (e.g., /warehouse/myTable). But if your notebook session ends or restarts, the metastore might not sync properly. Try specifying an explicit LOCATION to make it an external table, which is more reliable for notebooks:

   
      %%sql
   
      CREATE TABLE myTable (
   
          id INT,
   
          name STRING,
   
          lastName STRING
   
      ) USING DELTA
   
      LOCATION 'abfss://<your-filesystem>@<your-storage-account>.dfs.core.windows.net/delta/myTable/';
   
   

   This points to a specific ADLS Gen2 path, ensuring data persists outside the session.
2. Insert Data Using PySpark for Reliability: Spark SQL inserts can sometimes fail silently if there's a transaction issue. Instead, create a DataFrame and write it as Delta:
   • In a PySpark cell:

     
          from pyspark.sql import Row
     
          data = [Row(id=1, name="John", lastName="Doe"),
     
                  Row(id=2, name="Jane", lastName="Smith")]  # Add your sample data here
     
          df = spark.createDataFrame(data)
     
          df.write.format("delta").mode("append").saveAsTable("myTable")  # Or save to the LOCATION path if external
     
     

   • Then query it back with %%sql:

     
          %%sql
     
          SELECT * FROM myTable;
     
     

     This should now return your rows.
3. Commit and Refresh: After inserts, explicitly refresh the table to ensure the metastore is updated:

   
      %%sql
   
      REFRESH TABLE myTable;
   
      SELECT * FROM myTable;
   
   

4. Common Pitfalls to Check:
   • Session Scope: If you're running cells in different sessions or kernels, the table might not be visible. Restart the notebook and run everything in sequence.
   • Permissions: Ensure your Synapse workspace has write access to the storage account/path. Check for any ACL errors in the notebook logs.
   • Delta Version: Synapse uses a specific Delta runtime— if you're on an older Spark pool, upgrade to the latest (e.g., Spark 3.3+ with Delta 2.0+) for better compatibility.
   • No Results Even with Files: Sometimes, Delta logs (_delta_log folder) exist, but queries return empty if the table isn't registered. Use DeltaTable.forPath(spark, delta_path) in PySpark to load and verify.

If you run the above and still see no data, share more details like the exact insert statements, any error messages in the output, or your Spark pool version—I can refine the advice further. This should get your Delta table working smoothly for querying!

Best regards,

Jerald Felix

Hello Jona,

The issue you are facing because when in append mode, tries to merge the schema of the new data with the schema of the existing table. If it finds a column with the same name but different data types (like IntegerType and LongType), it throws this exception.

I would suggest you to follow the below steps, in order to resolve the issue

Drop and recreate the table: If the table is new or can be safely recreated, drop the existing table and then save the DataFrame. Spark will create a new table with the correct schema from your DataFrame.

# Drop the existing table
spark.sql('DROP TABLE IF EXISTS your_table_name')

Cast the DataFrame column to the type used in the target table (e.g., LongType):

from pyspark.sql.functions import col
df = df.withColumn('id', col('id').cast('long'))
df.write.format("delta").mode("append").saveAsTable("myTable")

Please make sure while using this code replace the datafram and table name according to yours, let me know how this works

Thanks,

Kalyani