Forum Discussion

pmscorca's avatar
pmscorca
Kudo Kingpin
4 months ago
Solved

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 t...
General Question
SQL database
  • v-anbandari's avatar
    v-anbandari
    4 months ago

    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.

     

Aparnaa_MS's avatar
Aparnaa_MS
Advocate III
4 months ago

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
Aparnaa
pmscorca's avatar
pmscorca
Kudo Kingpin
4 months ago

Hi, 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

  • Aparnaa_MS's avatar
    Aparnaa_MS
    Advocate III
    4 months ago

    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

    • pmscorca's avatar
      pmscorca
      Kudo Kingpin
      4 months ago

      Hi,

      for me it makes little sense to query an OLTP SQL database for analytical purposes without tranforming it in a data warehouse.

      However, the two layers remain disaligned after some object creation or drop operations;  it's not a question of waiting time.

      • v-anbandari's avatar
        v-anbandari
        Community Support
        4 months ago

        Hi pmscorca ,

        The SQL Analytics endpoint is kept in sync with the SQL Database through a background metadata synchronization process.

        This process runs automatically when the SQL Analytics endpoint is active and typically reflects changes within seconds to a minute. However, it is not strictly real-time or deterministic, and in some scenarios changes (such as table create/drop) may not appear immediately or consistently.

        In such cases, Microsoft recommends triggering an on-demand metadata sync instead of waiting for the background process.

        You can do this by:

        1. Using the Refresh option in the SQL query editor, or

        2. Triggering the SQL Endpoint Metadata Refresh API

        This forces the SQL Analytics endpoint to update with the latest changes from the SQL Database.

        If the issue still persists even after a manual refresh, it may need further investigation.

         

        For more details, you can refer to the official documentation:

        Items - Refresh Sql Endpoint Metadata - REST API (SQLEndpoint) | Microsoft Learn

        Thank you.