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 with WITH.

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