Forum Discussion
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:
- Write the PySpark DataFrame to a staging Warehouse table without an identity column.
- 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.
Solution SageHi 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:
- Write the PySpark DataFrame to a staging Warehouse table without an identity column.
- 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.
Thanks ShivekMaharaj , There is a limitation that I can not use T-SQL notebook , T-SQL notebook doesn't let me define a cell as notebook parameter. I did try but got error (also when the notebook runs through a pipeline , the parameters are not detected automatically in notebook despite pyspark notebook which can do it )
