Forum Discussion
Insert into Warehouse Table from Notebook
Trying to insert data into a warehouse table from pyspark notebook and table in warehouse has a key column with identity datatype , it gives me error regarding mismatch the schemas (apparently it expects to have identity column part of data which would be inserted). Is it possible to do it or it is part of limitation Fabric to insert directly from Notebook to Warehouse . Thanks
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.
5 Replies
Warehouse only generates IDENTITY on T‑SQL inserts, and notebook writes do not use that behavior, so tables with an IDENTITY column cannot take direct PySpark writes. I usually load the rows into a staging table without the IDENTITY and then run one insert…select into the real table.
Hi,
I've faced this problem before, it's a known limitation. Spark writes to Warehouse expect a matching schema including the identity column, so most connectors won't skip it automatically like SQL Server's native INSERT does.
Workaround I've used, exclude the identity column entirely from your dataframe before writing, then use synapsesql() or the connector's insert method targeting only the non-identity columns explicitly, some connectors respect this if you specify columns rather than a full schema match.
If that still fails, safer route is writing to a staging table without identity, then running a stored procedure or T-SQL INSERT INTO...SELECT from staging into the real table, letting SQL handle identity generation.
If this helped, feel free to give it a kudos or mark it as solution, helps others find it too.
Hi @Hamidr ,
With T-SQL INSERT, Fabric creates the ID value for you if you leave that column out.But with PySpark, Spark looks for matching columns between the DataFrame and the table schema. If the ID column is missing or extra, it throws an error.
Since Fabric Warehouse doesn't let you manually insert ID values (IDENTITY_INSERT isn't supported), you can't just pass your own list of IDs.
Recommended Solution:
Instead, use the notebook to execute a Warehouse T-SQL INSERT, explicitly excluding the identity column:
Instead, use the notebook to execute a Warehouse T-SQL INSERT, explicitly excluding the identity column:
%%SQL
INSERT INTO dbo.Customer ( CustomerName, Amount, TransactionDate ) SELECT CustomerName, Amount, TransactionDate FROM dbo.Customer_Staging;
If this helps, ✓ Mark as Kudos | Help Others
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.
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 )
