Write data into synapse dedicated sql pool from fabric notebook

[SriThiru]

How to write data into synapse dedicated sql pool from fabric notebook. 

I want to save the spark dataframe from Fabric Notebook into Azure Synapse dedicated sql pool.

Is it doable, if yes, can anyone please share the code, along with any configuration settings if needed at the dedicated sql pool level.

[Prince0011]

Yes, it is possible to write data from a Microsoft Fabric Notebook Spark DataFrame into an Azure Synapse Dedicated SQL Pool.

The recommended approach is to use the Synapse Spark connector for SQL DW, which allows Spark to write data directly into Synapse Dedicated SQL Pool.

Example:

# Install the connector if required
# (Usually available in Fabric Spark runtime)

df.write \
    .format("com.databricks.spark.sqldw") \
    .option("url", "jdbc:sqlserver://<server-name>.sql.azuresynapse.net:1433;database=<database-name>") \
    .option("dbTable", "<schema>.<table_name>") \
    .option("forwardSparkAzureStorageCredentials", "true") \
    .option("tempDir", "abfss://<container>@<storage-account>.dfs.core.windows.net/<temp-folder>") \
    .mode("overwrite") \
    .save()

Before running this, make sure:

• The Fabric workspace identity or user identity has permission to access the Synapse SQL Pool.

• The required network connectivity is available (firewall, private endpoints, managed identity configuration if applicable).

• The staging storage location (tempDir) is accessible because the connector uses Azure Storage as an intermediate step before loading into Dedicated SQL Pool.

• The target table permissions are configured correctly (CREATE TABLE, INSERT, etc., depending on the operation).

Another option is to use the standard JDBC approach:

df.write \
  .format("jdbc") \
  .option("url", "jdbc:sqlserver://<server-name>.sql.azuresynapse.net:1433;database=<database-name>") \
  .option("dbtable", "<schema>.<table_name>") \
  .option("user", "<username>") \
  .option("password", "<password>") \
  .mode("append") \
  .save()

However, for larger datasets, the dedicated SQL Pool connector is generally preferred because it uses parallel loading.

A few best practices:

• For large data volumes, avoid row-by-row inserts through JDBC.

• Use bulk loading patterns whenever possible.

• Partition your Spark DataFrame appropriately before writing.

• Monitor Synapse SQL Pool distribution and table design (hash distribution, round-robin, clustered columnstore indexes) for better performance.

For more information:

• Azure Synapse Dedicated SQL Pool Spark connector: https://learn.microsoft.com/azure/synapse-analytics/spark/synapse-spark-sql-pool-import-export

• Fabric Spark notebooks: https://learn.microsoft.com/fabric/data-engineering/how-to-use-notebook

💡 Helpful? Give a Kudos 👍 — keep the community growing.

✅ Solved your issue? Mark this as the Accepted Solution ✔️

Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast

[Areeshabaloch7]

Hi @SriThiru,

Yes, ye bilkul possible hai. Fabric Notebook se Synapse Dedicated SQL Pool me data likhne ke 2 tareeke hain:

**Option 1: Using spark.write with JDBC** 

```python

df.write \

  .format("com.microsoft.sqlserver.jdbc.spark") \

  .mode("overwrite") \

  .option("url", "jdbc:sqlserver://your-server.sql.azuresynapse.net:1433;database=yourDB") \

  .option("dbtable", "schema.tableName") \

  .option("user", "username") \

  .option("password", "password") \

  .save()

[mabdollahi]

Hi @SriThiru ,

I see your question is remained as unanswered for a long time.

Yes, you can definitely write data from a Spark DataFrame in a Fabric Notebook to an Azure Synapse dedicated SQL pool.

You’ll just need to use the .write method with the jdbc format. Here's a simple example to get you started:

df.write \
  .format("jdbc") \
  .option("url", "jdbc:sqlserver://<your-server-name>.sql.azuresynapse.net:1433;database=<your-database-name>") \
  .option("dbtable", "<your-schema>.<your-table-name>") \
  .option("user", "<your-username>") \
  .option("password", "<your-password>") \
  .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
  .mode("append") \
  .save()

A few quick tips:

• Make sure your dedicated SQL pool allows Azure services to connect (you can set this in the firewall settings).

• The table you're writing to should already exist in Synapse with the right schema.

• If you're using managed identity instead of SQL auth, you’ll need to handle authentication a bit differently (happy to help with that if needed).

Regards,

Mehrdad Abdollahi