Add support for Microsoft Fabric

[mattiasthalen]

Microsoft Fabric is becoming more and more popular with our clients. And most of the time it's new projects or migrations. I.e., the perfect time to implement SQLMesh.

I know that there are some issues with their lakehouse entity and creating schemas / tables. And that's why dbt only work on their warehouse entity.

Anyway, I hope this is something you'd be willing to look into!

[treysp]

Hello and thanks for the suggestion. Unfortunately, Fabric seems to have a number of limitations relative to SQL Server, and some of those are used in core SQLMesh operations. If there is sufficient demand, it may be possible to add Fabric once those limitations are addressed.

[tobymao]

@treysp which limitations do you think are critical? it seems like if the user doesn't do certain operations, we won't hit these limitations?

[treysp]

The limits on ALTER TABLE ADD/ALTER/DROP COLUMN were the main ones that concerned me.

But you're right that those limitations would be feature-specific (primarily on forward-only), so there's no inherent problem if we just tell people the affected features aren't supported.

Sorry for the premature closing @mattiasthalen!

[mattiasthalen]

@treysp No worries!

I'm curious though, are those there for the warehouse object as well? I was under the impression that it only was on the lakehouse.

[treysp]

Totally possible I'm misinterpreting that page - this text made me think it was platform-wide:

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

This article covers the T-SQL language syntax capabilities of Microsoft Fabric, when querying the SQL analytics endpoint or Warehouse.

[mattiasthalen]

@treysp you are absolutely right, I missed that part!

It sure does have it quirks, e.g., supporting UUID as a data type in the lakehouse/warehouse, but not in PowerBI. So much for consistency 😅

[pycal]

+1

our company has to use fabric, and if a connector for fabric is created we will use sqlmesh.

[tobymao]

we're planning to work on this soon after the holidays

[erindru]

Update on this:

SQLMesh needs some fundamental constructs in order to work properly, namely - schemas, tables and views.

It turns out the Lakehouse entity in Fabric does not support schemas by default. However, if we enable the preview feature for schemas - it stops supporting views. This is documented as a limitation in the public preview of schemas.

We need schemas for the physical layer and views for the virtual layer, so SQLMesh cannot work with Lakehouse until Microsoft resolves this. According to their documentation:

They'll be resolved in the coming releases before General Availability.

So we can revisit this after schema-enabled Lakehouses becomes generally available and theoretically support views.

Of course, the Warehouse endpoints that are TSQL-only are unaffected.

I'm curious - is there any demand for Warehouse / TSQL or are people mostly wanting to use Lakehouse / Spark ?

[mattiasthalen]

IIRC, dbt uses Warehouse for this reason. And you can reference the lakehouse tables as sources (external table)...

So our workflow is that the bronze layer is the lakehouse, where the EL lands. Then we do all our silver/gold (transform) in the warehouse.

That's how I'd want to use SQLMesh as well.

And tbh, while being able to use a lakehouse would be nice, it's not a deal breaker. A deal breaker would be not to be able to use SQLMesh at all 😉

So to answer your question, (pretty) please proceed with supporting the warehouse endpoint! ❤️

[cseHdz]

+1 agree with @mattiasthalen above

We also would like to use SQLMesh with Warehouse. Lakehouse is a nice to have.
Our workflow is also using Lakehouse as a landing zone, and Warehouse for transforms.

[mattiasthalen]

And for reference, you can target the lakehouse via the warehouse endpoint, it's "just" a different database.

E.g.:
lakehouse__landing_zone.dbo.raw__table
warehouse.silver.stg__table

[odikia]

+1 also agree with @mattiasthalen above.

we’ll be transitioning to fabric architecture for primary data products in the coming year, and would like to take advantage of sqlmesh across our multi-cloud enterprise. Will be fine to start with warehouse endpoint. As Microsoft irons out the limitations you mention @erindru , support for Lakehouse/Spark will be great long term!

[mhofmeester]

+1 with @mattiasthalen here!

For our clients we offer either a Databricks or Fabric centric approach often with DBT. It would be great if SQLMesh would support the warehouse endpoint, as you advertise to solve some of the limitations/annoyances we faced with DBT.

Fabric (Microsoft/Azure) has quite a dominant presence here in the Netherlands for data engineering capabilities. I figure it would be an interesting userbase.

With regards to the lakehouse vs. warehouse discussion, ideally SQLMesh would support both eventually (when technically feasible). However, the warehouse endpoint is currently by far the most resource efficient transform solution within Fabric, and would always have my preference for cost reasons.

[toninator]

+1 for warehouse

[pycal]

our implementation is, lakehouse for bronze, warehouse for silver and gold

we could definitely live with the limitation

[mattiasthalen]

Any news yet? ☺️

[tobymao]

@mattiasthalen this is not a priority for us at the moment. however, if you're interested, we do offer feature prioritization as a part of our enterprise / paid offering.