Question

A data engineering team is building a new analytics solution in Microsoft Fabric. The bronze and silver layers require heavy transformation of large volumes of semi-structured JSON and Parquet files using PySpark. The gold layer will serve a set of downstream analysts and BI developers who are only comfortable writing T-SQL and require full multi-table INSERT/UPDATE/DELETE transactional support with multi-statement (ACID) transactions. The team wants to minimize the number of engines they manage while satisfying both groups. Which storage design should they choose?

Accepted Answer

Use a Lakehouse for the bronze and silver layers with Spark, then load the gold layer into a Warehouse for full T-SQL DML support.

Answer

Use a Lakehouse for all layers and expose the gold layer through the Lakehouse SQL analytics endpoint for the analysts.

Answer

Use a Warehouse for all layers and run the PySpark transformations against the Warehouse using the Spark connector.

Answer

Use a KQL database for the bronze and silver layers and a Lakehouse for the gold layer.