Insert into Warehouse Table from Notebook

Hi Hamidr​,

Yes, you can insert into a Fabric Warehouse table that has an IDENTITY column, but I would separate the Warehouse behaviour from the PySpark DataFrame write behaviour.

Fabric Warehouse IDENTITY columns generate their value automatically. With a normal T-SQL INSERT, you omit the identity column from the column list:

INSERT INTO dbo.TargetTable (ColumnA, ColumnB) VALUES ('A', 'B');

The Warehouse then generates the identity value. IDENTITY_INSERT is not supported in Fabric Warehouse, so I would not add a dummy identity column to the DataFrame.

The Spark connector for Fabric Data Warehouse uses a staged write followed by COPY INTO. The current documentation does not describe identity-specific column handling for DataFrame writes, so the schema mismatch you are seeing may be occurring in that write/mapping path.

A pattern I would use is:

1. Write the PySpark DataFrame to a staging Warehouse table without an identity column.
2. Run a T-SQL INSERT INTO . . . (non-identity columns) SELECT . . . from staging into the final table.

For example:

INSERT INTO dbo.TargetTable (ColumnA, ColumnB) SELECT ColumnA, ColumnB FROM stg.TargetTable_Load;

That leaves generation of the key with the Warehouse engine.

If you want to keep everything in the notebook, Fabric now also supports running T-SQL directly from Python notebooks, so the Spark transformation and final Warehouse insert can be orchestrated from the same notebook. That T-SQL magic capability is currently Preview.

So I wouldn't consider this a general notebook-to-Warehouse limitation. It is more specifically a mismatch between the DataFrame write path and a destination table containing a Warehouse-generated identity column.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.