Microsoft Fabric Eventhouse has expanded its SQL query federation capabilities by supporting query federation to more Tabular Data Stream (TDS) protocol endpoints, like Fabric data warehouses and SQL Analytical endpoints. Through the Create and Alter Azure SQL External Tables command, users can now connect to Fabric SQL Analytical endpoints and warehouses in addition to the previously supported Azure SQL Database, SQL Server, and other TDS-compatible databases.
While all data in Microsoft Fabric is stored in OneLake using the open Delta Parquet format, making it inherently accessible to all engines, this new capability enables seamless query federation to SQL endpoints, allowing organizations to leverage their existing SQL code investments, including functions and stored procedures.
Key Capabilities of the T-SQL query federation in Fabric Eventhouse
- Comprehensive Data Analysis - Create external table connections to SQL data sources to unify your analytics across different data types and sources. This enables you to combine high-granularity operational data from Eventhouse with business objects from data warehouses, lakehouses, and mirrored databases. All data is queryable using either KQL or SQL. For example, you can analyze streaming IoT sensor data alongside enterprise business data for deeper insights.
SQL Logic Integration- Access your existing SQL business logic through views, including:
-
- Views based on stored procedures, allowing you to leverage pre-built data transformations.
- Views incorporating SQL functions, enabling complex calculations and business rules.
- Custom SQL logic that can be called directly from your KQL queries.
- Simplified Data Architecture - Eliminate the need for data movement or duplicate storage by querying SQL data sources directly within your KQL analytics pipeline. This reduces data latency, storage costs, and maintenance overhead.
Example: Combining Real-Time Bike Station Data with Business Analytics
Let's demonstrate how to combine streaming bike station telemetry with business metrics to enable smart station management:
// Create external table for station business metrics
.create external table StationBusinessMetrics (
BikePointID: string,
TargetAvailability: double,
MaintenancePriority: int,
RevenuePerBike: decimal,
PeakHours: string,
StationOwner: string,
ServiceTier: string,
MonthlySubscribers: int
)
kind=sql
(@'Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;')
with
(Table='dbo.usp_GetStationMetrics' // Stored procedure with business logic)
// Example query: Alert on high-priority stations with low bike availability
BikeStationTelemetry
| where TimeStamp > ago(15m)
| extend AvailabilityRate = todouble(No_Bikes) / todouble(No_Bikes + No_Empty_Docks) * 100
| lookup kind=inner StationBusinessMetrics on BikePointID
| where AvailabilityRate < TargetAvailability
| where MaintenancePriority <= 2 // High priority stations
| order by AvailabilityRate ascIn this sample, this integration enables leveraging stable, tested and heavily used business logic that resides in SQL servers and warehouses to deliver the below sample business value without rewriting, migration or duplicating logic:
- Monitor real-time bike availability against business targets
- Prioritize rebalancing based on:
- Current availability vs. target thresholds
- Station priority and service tier
- Revenue impact
- Geographic clustering (Knightsbridge, Chelsea, etc.)
- Generate smart alerts that include both operational and business context
Next steps
To learn more refer to our documentation
We encourage you to try this and share your feedback!