Forum Discussion
Different behaviour between SQL database and SQL Analytics endpoint
Hi,
I've noticed that there are diffencies between SQL database view and SQL Analytics endpoint view in the explorer.
F.e. when I create a table with a datetime column I can do it successfully in the SQL database view while in the SQL Analytics endpoint view I need to use a datetime2 column.
Moreover, it could occur that a dropped table in the SQL database view remains in the SQL Analytics endpoint view.
The SQL Analytics endpoint view should correspond to SQL database view.
Thanks
Hi pmscorca ,
To align the SQL Analytics Endpoint with the SQL Database Editor, you need to explicitly call the Fabric REST API refreshMetadata operation. This forces the endpoint to sync its schema with the latest definitions.You don’t use this API inside the SQL Database Editor or SQL Analytics Endpoint UI directly it’s a REST API call that you trigger from your own automation or client code. In practice, you’d use it in one of these places:
1. Fabric Pipelines → Add a Web activity after your ingestion/transform steps to call the refreshMetadata API, so the SQL Analytics Endpoint is always aligned before downstream reporting.
2. Notebooks → Use Python (with requests or http libraries) to call the API as part of your workflow.
3. External scripts → PowerShell, Python, or any HTTP client can invoke the endpoint. This is common if you’re orchestrating refreshes outside Fabric.
4. CI/CD automation → Integrate the call into deployment pipelines so schema changes are automatically reflected in the SQL Analytics Endpoint.
For more details, you can refer to the official documentation:
Items - Refresh Sql Endpoint Metadata - REST API (SQLEndpoint) | Microsoft Learn
Thank you.
12 Replies
The SQL Analytics endpoint view should correspond to SQL database view.Not really. The SQL Endpoint is a pared down, read only version. Cannot be used for transactions, only for consumption. Meta data synchronization is a one way street, and is not deterministic.
Hi, I don't understand the usefulness of the SQL analytics endpoint layer for a SQL database.
These two features don't align with each other, even over time.
Hi pmscorca ,
It may seem that the SQL Database and SQL Analytics endpoint should behave the same, but they are designed for different purposes in Microsoft Fabric.
The SQL Database is used for transactional operations like insert, update, and delete. The SQL Analytics endpoint is a read-only layer used for reporting and analytical queries. The purpose is not to make both the same, but to allow analytics on the same data without affecting transactional performance and without moving data.
Because the SQL Analytics endpoint uses a different engine, some differences are expected, such as requiring datetime2 instead of datetime and slight delays in reflecting changes.
Both are meant to work together, not to behave exactly the same.
Thank you.
Hi pmscorca
What your observing is majorly due to the architectural difference between the two.
The SQL analytics endpoint layer is a read-only layer which is mirrored over the data in OneLake, but it doesnt support transactional SQL operations due to which the support of different datatypes may vary, like datetime and datetime2 which you have mentiond and changes such as dropped tables will appear but sometimes it there maybe a delay you will just have to wait until the refreshed data/metadata catches up.Additionally it is recommended to use the SQL analytics endpoint mainly for analytics/reporting i.e OLAP tasks, and the SQL database for any operational queries i.e OLTP operations.
Hope this helps !
Thanks
AparnaaHi, thanks for your reply.
The SQL analytics endpoint is an experience also offered from a SQL database. The SQL database experience already allows to manage a database similar to Azure SQL or SQL Server.
Why should a Fabric SQL database also include a SQL analytics endpoint? Moreover, with some differencies between the two experiences in architectural terms?
It makes more sense to have the SQL analytics endpoint layer for a warehouse or a lakehouse.Thanks
Hi pmscorca
thanks for the follow-up.The reason for including the SQL analytics endpoint with Fabric SQL Database, wrt architecture, is mainly about separating OLTP and OLAP workloads, i.e to separate operational processing from analytical consumption.
While the SQL Database handles transactional (OLTP) operations, like CRUD support and reads/writes, but running heavy analytical queries on the same can impact performance.
So the SQL analytics endpoint will handle the analytical queries over the same data without the need for any data movement (like running pipelines, ETL... etc)Also its not that a SQL Endpoint might make more sense in a warehouse as a warehouse is already optimized for heavy analytics, but there are limitations to the operational queries you can perform in warehouse than in SQL DB.
In short, even though a SQL analytics endpoint is also useful for lakehouses/warehouses, in the case of Fabric SQL Database it exists to provide a built-in analytics for the same database, without requiring any external setup or movment.Hope this helps!
Thanks
Aparnaa
