Forum Discussion
Issues writing to Warehouse from PySpark Notebook
Hi TNastyCodes ,
What your errors mean:
• saveAsTable(... 'warehouse_name.dbo.table') -> PUT not allowed on Tables path – you tried to write a Delta table directly into a Warehouse’s managed storage. Warehouses don’t accept Delta writes from Spark; all writes go through the SQL engine, not straight into OneLake folders. See “two-phase write via COPY INTO” in the Spark <-> Warehouse connector doc, and note that Warehouse tables are stored/managed by the SQL engine even though they live in OneLake. (Spark connector for Fabric Data Warehouse, Lakehouse & Warehouse “Better Together”)
• df.write.synapsesql(...)-> FabricSparkTDSWriteError ... Content ... cannot be listed – this usually indicates a staging step failure in the connector’s two-phase write (Spark stages Parquet, then the engine runs COPY INTO). Common causes: Private Link enabled (write not supported) or environment/permission issues. (doc notes & restrictions)
• JDBC -> NVARCHAR(MAX) not supported – Fabric Warehouse doesn’t support NVARCHAR types; use VARCHAR (UTF-8). VARCHAR(MAX) exists but is preview with a 1 MB limit, so the safest path is VARCHAR(n) where possible. (Warehouse data types)Quick solution
- Prefer the Fabric Spark connector (synapsesql) for PySpark → Warehouse.
– Use Runtime 1.3 (or newer).
– Disable Private Link for write scenarios (tenant/workspace).
– Pre-create the Warehouse table with VARCHAR columns (not NVARCHAR) if you need strict typing.
(doc with examples & constraints) - If you must use JDBC:
– Pre-create the target table in the Warehouse with VARCHAR types and then TRUNCATE TABLE + mode("append") from Spark.
– Or specify createTableColumnTypes so Spark won’t default to NVARCHAR.
(data type support) - Consider SQL-first ingestion for Bronze>Silver:
– Use Warehouse T-SQL (CTAS / INSERT-SELECT) reading from the Lakehouse SQL analytics endpoint (via shortcuts/gold views) or
– Use COPY INTO into Warehouse from external storage. OneLake as a direct COPY source has been in preview and may vary by tenant; if it isn’t enabled, stage in ADLS Gen2.
(Lakehouse/Warehouse integration, Warehouse ingest overview)
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.- Prefer the Fabric Spark connector (synapsesql) for PySpark → Warehouse.
TNastyCodes the challenges you describe with your approach do require some deeper investigation. However, I wanted to ask you about your approach from an overall architecture perspective. Using Warehouse in a silver layer might make sense in your case but traditionally a Warehouse is used in a gold layer because it represents a finalized data analytics product ready for consumption through reports or direct queries, while the silver layer is usually reserved for conformed data that can be shared across other data products. The main difference between a Warehouse and a Lakehouse is the Warehouse is primarily SQL oriened as it's expected to have a star schema with data loaded from silver and/or bronze layers. That said, I'd like to postulate a few suggestions:
- Using a Notebook for SQL-centric storage (Warehouse) is possible but not the most effective.
- When working with a warehouse a two-staged approach is preferred: first create a star or snowflake schema and then load data into the warehouse as a separate activity.
- Loading data into a warehouse on a regular basis implies that an incremental load should be used as there are already some historical data in the warehouse after a first load.
- As warehouse is SQL-centric it would be easier to use t-sql or spark SQL in the notebook without a need to use a python-native technique to connect to a SQL endpoint.
That being said, there is a way to load data into a warehouse using PySpark in a notebook. The code sample below does that using a specific synapsesql connector:
import com.microsoft.spark.fabric
df = spark.sql("SELECT * FROM Lakehouselab.silver.sales LIMIT 1000")
df.write.mode("overwrite").synapsesql("LabDwh.dbo.sales_data")As you can see from the screenshot the code executes successfully:
Please note that tables from both Lakehouslab lakehouse and LabDwh warehouse are referred to by a full name.
Hope this helps. If you find this post helped solve your problem please mark it as a solution. If you find this post useful otherwise please give it a kudo.
Hi apturlov thanks for your insight!
So to your points, my goal was to have the bronze layer in a lakehouse with the silver and gold in a warehouse as a lot of our silver analytics will still have highly useful data for analytics for downstream users. We do have a STAR schema for centric tables and a high level SCD process for loading in data in more of the two staged process you're describing which it seems like I'll be trying to adapt as the notebook is still limited seemingly for me. I again tried the syntax you showed since before I was using synapsesql to read from the bronze layer and then also to write, and instead tried to use spark.sql to read and synapsesql for the write but its giving me the same error which may be a symptom of access issues.
