Add support for workloads to Spark-based Lakehouse

[boxysean]

Add native support to adapter to send workloads to Spark-based Lakehouse, in addition to Fabric Warehouse. We've heard numerous requests from dbt Cloud users and interested teams, that they would like to use dbt not only on the Warehouse, but also on the Lakehouse, in order to work with the best platform for their needs. It may be cost optimization, or architectural decisions -- but this flexibility is greatly desired.

There is the related project microsoft/dbt-fabricspark, however this request is to unify the approaches to provide a simpler experience to developers.

[prdpsvs]

@boxysean , we do not have any plans to merge two adapters at this time. The reason is pretty simple. The warehouse/lake house artifacts must able to support all scenarios using spark and warehouse to unify this experience. Without it, the maintenance of a single adapter for both warehouse and lake house scenarios involves complexity.

[prdpsvs]

I will keep this active but not going to act on it for a while.

[arasdk]

Definitely voting for this one — unification is essential.
Managing two separate projects for materializing data models within the same Fabric workspace is highly impractical for developers. It also prevents us from fully adopting Microsoft’s recommended reference architecture as long as the two plugins remain separate.

I’d be open to some reasonable restrictions on dual-use if that helps make the integration more feasible. For example:

• Spark models can reference other Spark models and Lakehouse sources — but not Warehouse models.
• Warehouse models can reference other Warehouse models as well as Spark models.
  (Referencing Lakehouse tables from the Warehouse is already seamless today, so that shouldn’t be a major hurdle.)

This approach could help simplify certain complexities — like handling models materialized as views in the Warehouse. We’d still be able to use Spark models for the bronze/silver layers to process unstructured or semi-structured data sources, and then consume those models from the Warehouse — fully aligned with Microsoft’s reference architecture.