Fabric SQL (T-SQL)
A Fabric SQL source connects to a Warehouse or a SQL analytics endpoint, including a lakehouse's, and has Fabric run T-SQL for you, as the person viewing the map. There is no proxy or gateway to set up. For a SQL database, choose its SQL analytics endpoint: the query runs on that endpoint, the copy Fabric mirrors to OneLake, unless you choose the live connection through Eventhouse.
Fabric SQL or Warehouse & SQL?
| Fabric SQL | Warehouse & SQL | |
|---|---|---|
| Query language | T-SQL | DuckDB SQL |
| Where queries run | In Fabric, on your capacity | In the browser, over OneLake |
| Views | Yes | No |
| Live data and spatial columns from a SQL database | Yes, with the live connection | No |
Choosing a connection
A SQL database offers two connections. A Warehouse is always read directly.
| SQL analytics endpoint | Live database via Eventhouse | |
|---|---|---|
| Setup | None | Choose a KQL database |
| What it reads | The copy Fabric mirrors to OneLake | The database itself |
| New data appears | Within about a minute with New metadata sync; otherwise in about 30 seconds if you can edit the workspace, and several minutes or longer for everyone else | Immediately |
| Geography and geometry columns | Not available | Available |
| The database's views | Not available | Available |
| The database's row-level security and masking | Not applied | Applied |
SQL analytics endpoint. Fabric mirrors new data to OneLake in about 20 seconds. How soon the endpoint shows it depends on which metadata sync the endpoint uses:
- New metadata sync. Turn on New metadata sync in the workspace settings, under Warehouse settings, before you create the database. Its endpoint then shows new data within about a minute, to everyone. Endpoints that already exist keep the original sync. The setting is in preview in Fabric.
- The original sync can take many minutes, and it stops after 15 minutes with no queries. So before reading, Icon Map asks the endpoint to refresh. Only people with the Contributor role or higher on the workspace can do that; for anyone else the map shows what the endpoint has at that moment.
The security difference matters: if you rely on row-level security or masking in the database, everyone who can read the endpoint sees the rows and values it would have hidden. Use the live connection, or define the security on the endpoint.
Live database via Eventhouse. A browser can't connect to a SQL database directly, so this runs your query through a KQL database, using Eventhouse's sql_request. Any KQL database will do, and it stores none of your data. Everyone who views the map needs access to both that KQL database and the SQL database, and may be asked to approve access to Azure Data Explorer the first time.
Opening a map can wake a SQL database that has paused.
Tables, views and queries
- Pick a table or view, or
- Write a T-SQL query. It must be a single read-only
SELECT, which can start withWITH.
A query must name every column. Writes and stored procedures aren't allowed: a saved query runs with the permissions of whoever opens the map, so Icon Map refuses anything else before it is sent. To reuse logic from a stored procedure, put it in a view. If a column is named after a T-SQL keyword, put it in square brackets, such as [update].
Spatial columns
When you pick a table or view, geography and geometry columns arrive as WKT. Under Location is stored as, choose A geometry column, then the column, with the WKT encoding.
In your own query, convert them to text:
SELECT id, name, geom.STAsText() AS wkt
FROM dbo.sites
If you only have longitude and latitude columns on the SQL analytics endpoint, you can still compute geometry in the query, such as geography::Point(lat, lon, 4326).STAsText() AS wkt.
Limits
- A query can run for up to 100 seconds.
- One load can return about 20 MB of data on the SQL analytics endpoint and about 64 MB on the live connection. Past that, Icon Map keeps the rows that arrived complete. Use the row limit, a filter, or Only load data in the current map view on larger tables.
- Queries use your Fabric capacity, so aggregating at source and viewport filtering also keep costs down.
- Charts summarize the rows loaded into the map.
- Published maps carry this source as a snapshot taken when you publish, because an anonymous reader has no Fabric identity to run queries as.
Next steps
- Data sources overview - common configuration.
- Security & permissions - the access your sources require.