Error writing table to Warehouse from notebook

[reubtfp]

Using the following function

def copy_table(source_table: str, dest_table: str):
    """Read from lakehouse and write to TARGET_DB, replacing existing table."""

    full_source = f"{SOURCE_DB}.{SOURCE_SCHEMA}.{source_table}"
    df = spark.read.table(full_source)

    full_dest = f"{TARGET_DB}.{DEST_SCHEMA}.{dest_table}"

    if df.count() > 0:
        df.write.mode("overwrite").synapsesql(full_dest)
        print(f"Copied {source_table} -> {dest_table}")

    else:
        print(f"Table {source_table} is empty, skipping copy!")

We were receiving multiple "Schemas are not equal" errors. 

com.microsoft.spark.fabric.tds.write.error.FabricSparkTDSWriteError: Schemas are not equal (ignoring metadata):

The error is correct in that the schemas are not equal, but the .synapsesql function was being invoked in overwrite mode, so the schema mismatch shouldn't error.

[v-sgandrathi · Accepted solution]

Hi @reubtfp,

The behavior you're seeing is expected with the current .synapsesql() connector. Even if you use mode("overwrite"), it only replaces the data in the target Warehouse table and doesn't support schema overwrite or evolution. The option("overwriteSchema","true") parameter isn't supported with .synapsesql(), which is why you get the “Schemas are not equal” error in both cases.

With this connector, the source DataFrame schema must match the target Warehouse table schema exactly—any differences in columns, data types, order, or nullability will cause the write to fail, regardless of the overwrite mode. This is a known connector limitation and isn't related to the runtime version.

To resolve the issue, make sure your DataFrame schema matches the target table before writing. If you expect schema changes, you'll need to drop and recreate the target table before writing. Alternatively, you can write to a Lakehouse (Delta table) first, where schema overwrite is supported, and then load the data into the Warehouse after aligning the schema.

Thank you.

[reubtfp]

Actually, this still doesn't work. I think it's a bug.

both of; 

df.write.mode("overwrite").synapsesql(full_dest)

and;

df.write.option('overwriteSchema','true').mode("overwrite").synapsesql(full_dest)

have failed. Runtime 1.3 was not the solution.

[reubtfp]

Thought I'd post that the root cause for this is because we have only recently updated the workspace default Fabric runtime in the environment from 1.2 to 1.3, so clearly some incompatability in the delta table format meant that the overwrite was failing.

Other tables which were also previously built using runtime 1.2 (spark 3.4, delta 2.4)... but which did not have a schema difference... did not experience the issue.

The solution was to drop or recreate the table outside the notebook - following which, the .synapsesql call works correctly.

[Srisakthi]

Hi @reubtfp ,

Overwrite will only overwrite table data, not the schema. If you want to overwrite schema, then use option("overwriteSchema", "True") while writing to table.

Thanks,

Srisakthi

[reubtfp]

... but I'm also not sure that option is supported for the synapsesql connector. It's not in the documentation at least.

[reubtfp]

No, I tried this also and got the same error.