Unable to read from Mirrored Database via synapsesql — 'Failed to lookup datamart by name'

[praveensreeram]

Hi community,

I'm running into an issue reading from a Mirrored Azure SQL Database using the `synapsesql` connector in a Fabric Notebook (Runtime 1.3, Spark 3.5.1). I've added both the Mirrored Database item **and** its auto-created SQL Analytics Endpoint as data sources in the notebook Explorer panel, but the read still fails.

---

**Environment**
- Fabric Runtime: 1.3 (Spark 3.5, Delta 3.2)
- Notebook language: PySpark (Python)
- Source item: Mirrored Azure SQL Database (display name: `wideworldimporters`)
- Both the MirroredDatabase item and its SQLEndpoint are added as data sources in the notebook

---

**Code**

```python
import com.microsoft.spark.fabric

mirror_db_name = 'wideworldimporters'

customers_df = spark.read.synapsesql(f'{mirror_db_name}.Sales.Customers')
```

---

**Error**

```
Py4JJavaError: An error occurred while calling o5276.synapsesql.
: com.microsoft.spark.fabric.tds.error.FabricSparkTDSInternalError:
Failed to lookup datamart by name wideworldimporters
at com.microsoft.spark.fabric.tds.meta.FabricTDSEndPoint$
.lookupDataMartFromWorkspaceItems(FabricTDSEndPoint.scala:609)
```

---

**What I've found so far**

When I run `fabric.list_items()` via sempy, I can see that Fabric has created **two items** with the display name `wideworldimporters` in the same workspace:

| Id | Display Name | Type |
|---|---|---|
| 05560d20-... | wideworldimporters | **SQLEndpoint** |
| 5f49d0ff-... | wideworldimporters | **MirroredDatabase** |

It appears the `synapsesql` reader is resolving by display name, picking the `SQLEndpoint` item instead of the `MirroredDatabase`, and failing with a 404. This is the auto-generated SQL Analytics Endpoint that Fabric creates alongside the Mirror — it shares the exact same display name.

[praveensreeram · Accepted solution]

For those who have similar situation like me and don't want to use SparkSQL here is how i was able to read the data from Mirrored SQL database using Onelake path itself and store the data into the warehouse table

# nb_load_dim_customer
# Loads dim_customer. Joins Customers to dim_city for city_key lookup.
# DEPENDENCY: nb_load_dim_city must have run first.
# Requires: Runtime 1.3+, wh_wwi attached to notebook.

from pyspark.sql.functions import col, monotonically_increasing_id
import com.microsoft.spark.fabric



# ---------------------------------------------------------------------------
# 1. Read Customers from Mirror via OneLake Delta path
# ---------------------------------------------------------------------------
base = f'abfss://{workspace_id}@onelake.dfs.fabric.microsoft.com/{mirror_item_id}/Tables'
customers_df = spark.read.format('delta').load(f'{base}/Sales/Customers')

# Read dim_city back from the Warehouse via synapsesql (no name collision here)
dim_city_ref = spark.read.synapsesql(f'{warehouse_name}.dbo.dim_city').select('city_key', 'city_id')

# ---------------------------------------------------------------------------
# 2. Join and select
#    customer_key: BIGINT generated by monotonically_increasing_id()
#    city_key FK: BIGINT to match dim_city.city_key
# ---------------------------------------------------------------------------
dim_customer_df = (
    customers_df.alias('cu')
    .join(dim_city_ref.alias('dc'), col('cu.DeliveryCityID') == col('dc.city_id'))
    .select(
        monotonically_increasing_id().alias('customer_key'),
        col('cu.CustomerID').alias('customer_id'),
        col('cu.CustomerName').alias('customer_name'),
        col('dc.city_key'),
        col('cu.AccountOpenedDate').alias('account_opened'),
        col('cu.IsOnCreditHold').alias('is_on_credit_hold')
    )
)

dim_customer_df.write.mode('overwrite').synapsesql(f'{warehouse_name}.dbo.dim_customer')
print(f'dim_customer loaded: {dim_customer_df.count():,} rows')

[deborshi_nag]

Hello @praveensreeram 

The synapsesql connector is designed to work only with a Warehouse or a SQL analytics endpoint of a lakehouse; it does not work with Mirrored database items. 

"The Spark connector for Fabric Data Warehouse enables Spark developers and data scientists to access and work with data from a warehouse and the SQL analytics endpoint of a lakehouse."

Spark connector for Microsoft Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

You can use SparkSQL to read a mirrored database item using a Notebook as described here in this Microsoft doc. 

Explore Data in Your Mirrored Database With Notebooks - Microsoft Fabric | Microsoft Learn