Surface lake data via Synapse Serverless views into Power BI

+-------------------------------------------------+
|            Power BI Reports / Dashboards        |
|      Connects via DirectQuery or Import mode    |
+-------------------------------------------------+
                         | standard T-SQL endpoint
                         v
+-------------------------------------------------+
|        Synapse Serverless SQL Database          |
|    Virtual Views defining business logic        |
+-------------------------------------------------+
                         | OPENROWSET queries files
                         v
+-------------------------------------------------+
|           Azure Data Lake Storage Gen2          |
|    Partitioned Parquet and Delta Lake files     |
+-------------------------------------------------+

Implementing the virtual semantic layer

• Create a Serverless Database: Create a logical database inside your Synapse workspace. This database stores only metadata and views, not user data files.
• Define SQL Views: Create views using OPENROWSET over partitioned lake directories.

CREATE VIEW gold.monthly_revenue AS
SELECT
    r.filepath(1) AS order_year,
    r.filepath(2) AS order_month,
    r.customer_id,
    SUM(r.amount) AS total_amount
FROM OPENROWSET(
    BULK 'https://mystorage.dfs.core.windows.net/lake/curated/sales/*/*/*.parquet',
    FORMAT = 'PARQUET'
) AS r
GROUP BY r.filepath(1), r.filepath(2), r.customer_id;

Partition pruning with filepath()

Using the filepath() function in the view allows Power BI filters to push down partition pruning directly to the storage layer. When an analyst selects '2024', Serverless SQL scans only the folders matching order_year=2024, avoiding scanning historical data.

Power BI connects using standard SQL endpoints. You pay only the $5.00/TB scan fee (check current pricing) for data actively queried by dashboard refreshes. This pattern completely eliminates provisioned cluster expenses while delivering clean relational schemas to business users.

Architectural trade-offs

While Serverless SQL views provide an economical, zero-maintenance presentation layer, they are intended for read-only exploration and BI consumption. They do not support data modifications or local indexing. For intensive enterprise queries that require sub-second multi-table joins across hundreds of concurrent dashboard sessions, Dedicated SQL Pools or Microsoft Fabric Warehouse remain the preferred destination.