Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This article describes what the SQL analytics endpoint is, how it stays in sync with your lakehouse, and the controls you have to manage that sync. The SQL analytics endpoint automatically detects the change and updates its SQL metadata via a background process. You can also manually refresh SQL analytics endpoint metadata via the Fabric portal, REST API, or T-SQL stored procedure.
The metadata sync background process reads the Delta logs from the /Tables folder in OneLake and keeps the SQL schema up to date.
Fabric automatically keeps the SQL analytics endpoint in sync with the underlying lakehouse. There's no infrastructure to set up and, in most cases, no user action required. The platform is responsible for:
- Table discovery - detecting newly created or dropped Delta tables in the lakehouse, and creating or removing the corresponding SQL tables in the endpoint.
- Data freshness - detecting changes (inserts, updates, deletes) made to the data in existing tables and ensuring queries return up-to-date results.
- Schema change detection - detecting column additions, removals, or type changes in existing Delta tables and refreshing the corresponding SQL table definition.
For details on how this sync works and factors that affect sync latency, see SQL analytics endpoint performance considerations.
Schema change detection and metadata sync
Every lakehouse, warehouse, database, and mirrored database in Fabric is automatically provisioned with its own SQL analytics endpoint, so you can query Delta tables with T-SQL and the TDS protocol, without moving or copying data.
- Every lakehouse, warehouse, database, and mirrored database has exactly one SQL analytics endpoint.
- Each Delta table in the underlying item automatically surfaces as a SQL table in the endpoint.
- The endpoint is read-only over the autogenerated tables. You can extend the model with your own SQL schemas, views, stored procedures, and functions.
- The endpoint is powered by the same compute engine as Fabric Data Warehouse, so it provides high-performance, low-latency SQL queries against open Delta files.
New metadata sync
The new metadata sync option keeps the data available for querying within seconds of it landing in the lakehouse. You can enable new metadata sync for new SQL analytics endpoints through workspace settings, or upgrade existing SQL analytics endpoints.
The new metadata sync process includes the following features:
- A new external tables-based architecture for parsing Delta logs and creating the catalog.
- A decoupled architecture that detects schema and data changes separately and refreshes the catalog accordingly.
- A background refresh that checks for data updates periodically.
- An on-demand refresh of the data when an incoming read query happens and the system detects that the data isn't fresh.
Enable new metadata sync
To enable new metadata sync for a workspace, open Workspace settings, expand Data Warehouse, and select New metadata sync.
When you enable new metadata sync, new SQL analytics endpoints in the workspace sync faster when refreshing changes from underlying tables. This setting doesn't upgrade existing SQL analytics endpoints. To upgrade an existing SQL analytics endpoint, follow the upgrade steps.
When you start provisioning a new SQL analytics endpoint in an existing Fabric workspace, the workspace configuration is automatically set to New metadata sync. The new SQL analytics endpoint uses this configuration.
Upgrade existing SQL analytics endpoints to new metadata sync
Before you upgrade, identify which metadata sync version your SQL analytics endpoint uses. You can check the version in the Fabric portal or with the REST API.
Check the version in the Fabric portal
- In your workspace, open the SQL analytics endpoint.
- In the item header, open the item details.
- Check the Metadata sync value. Legacy indicates that the SQL analytics endpoint uses legacy metadata sync (V1).
Check the version with the REST API
Use Get Sql Endpoint Metadata Sync Migration Status to find the metadata sync version of a SQL analytics endpoint and tables within the item.
Check the metadata sync version returned in the response:
V1indicates that the SQL analytics endpoint uses legacy metadata sync.V2indicates that the SQL analytics endpoint uses new metadata sync.
Run the upgrade assessment
After you check the metadata sync version, run a preflight assessment with the Preflight Sql Endpoint Metadata Sync Upgrade. The assessment evaluates each table without modifying it and reports its readiness for upgrade.
Review the assessment results for each table and address the reported issues:
- Multi-part checkpoints: Migrate tables that use multi-part checkpoints to use
single-part classic checkpoints or V2 checkpoints before upgrading. The corresponding
Delta checkpoint policies are
classicandv2. These policies are separate from metadata sync versions V1 and V2. See Delta table properties. - More than 1,000 commits since the last checkpoint: Consider creating a checkpoint for the table before upgrading.
- Column mapping by ID: If a table uses the
idcolumn mapping mode, review the migration options supported by the runtime that manages the Delta table before moving tononeorname. Don't assume that changingdelta.columnMapping.modedirectly is supported. Removing column mapping can rewrite data files and require you to pause concurrent writers. For tables managed by Azure Databricks, follow Remove column mapping.
- Multi-part checkpoints: Migrate tables that use multi-part checkpoints to use
single-part classic checkpoints or V2 checkpoints before upgrading. The corresponding
Delta checkpoint policies are
Upgrade metadata sync
After you review the assessment results and address the reported issues, use the Upgrade Sql Endpoint Metadata Sync to upgrade the SQL analytics endpoint.
Important
Plan the upgrade as a maintenance activity. Unlike the preflight assessment, the upgrade changes individual tables in the SQL analytics endpoint.
Review the upgrade results for each table:
- If at least one table upgrades successfully, the SQL analytics endpoint reports new metadata sync (V2). Any tables that fail to upgrade remain on V1 and don't sync automatically.
- If no tables upgrade successfully, the SQL analytics endpoint remains on legacy metadata sync (V1).
If any tables fail to upgrade, address the reported errors and send the same Upgrade Sql Endpoint Metadata Sync request again. On the second and subsequent attempts, only the tables that still need upgrading are upgraded. Tables already on V2 remain unchanged.
If you previously used the Refresh SQL analytics endpoint metadata REST API to refresh metadata or schedule refreshes, you no longer need to do so for tables using new metadata sync. New metadata sync handles metadata freshness automatically. To refresh a specific table, consider using the
tablesparameter in the request body to refresh individual tables.
Find when the metadata was last updated
If you're using the new version of the metadata sync, you can use dynamic management views (DMVs) to get details about the most recent update to the SQL analytics endpoint. Use the sys.dm_db_external_tables_log_status DMV to get details about the latest metadata sync:
last_update_time_utc: Timestamp for the last time the table was updated with new data.latest_log_version: The highest Delta transaction log version of the table that was updated.latest_checkpoint_version: The latest Delta checkpoint version that was processed.is_blocked: Indicates whether the last attempt at the table update was blocked (1) or successful (0).
For more information, see sys.dm_db_external_tables_log_status.
Limitations of the new metadata sync
- The new metadata sync doesn't support multi-part checkpoint, a deprecated delta feature. If your lakehouse tables contain multi-part checkpoints, they will fail to update in the new metadata sync.
Manual refresh
In addition to the background processes that automatically update data, you can manually refresh the metadata:
Refresh from the portal: In the Fabric portal SQL analytics endpoint editor, select the Refresh icon in the Explorer toolbar to force an on-demand metadata sync. Go to Query your SQL analytics endpoint, and look for the refresh button in the toolbar.
Refresh with the REST API: Use the Refresh SQL analytics endpoint metadata REST API to programmatically refresh the metadata of a SQL analytics endpoint. To refresh specific table, use
tablesparameter of the API.Refresh via stored procedure: Use the sys.sp_dw_refresh_ext_table system stored procedure to refresh the data for a specific table within a SQL analytics endpoint. This T-SQL method requires new metadata sync. For example:
EXEC sys.sp_dw_refresh_ext_table "dbo.publicholidays";
Use the API only if you have schema changes, such as adding or deleting tables or columns, or changing types of columns, and need to refresh the entire item or a set of tables. For data-only changes in a SQL analytics endpoint, use the sys.sp_dw_refresh_ext_table system stored procedure to update a specific table.
For tuning the underlying Delta tables to get the best metadata sync and query performance, see SQL analytics endpoint performance considerations.