🏭🗒️Microsoft Fabric: Direct Lake [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 13-Oct-2026

[MF] Direct Lake

• {def} Power BI semantic model table storage mode option available in MF [1]
• optimized for large volumes of data to be quickly loaded into memory from Delta tables available in the OneLake [1] 
• leverages VertiPaq query engine [1]
• {benefit} deliver query performance comparable to Import mode 
• without the management overhead of data refresh cycles to load the entire data volume [1]
• similar to Import mode
• {difference}refreshes copies only metadata (aka framing)
•  [Import mode] replicates the data and creates an entire cached copy of the data for the semantic model [1]
•  depends on data preparation being done in the data lake [1]
• {benefit} enables high-performance interactive analysis [1]
•  ideal for semantic models connecting to large data sources
• e.g. lakehouse, warehouse and other data source with Delta tables [1]
• performance depends on well-tuned Delta tables [1]
•  {benefit} performance
• normally outperforms DirectQuery queries when loading and interacting with visuals in reports [1]
• {benefit}maximizes ROI because 
• analyzed data volumes can exceed capacity's max memory limits [1]
• only the needed data to answer a query is loaded into memory [1]  
• {benefit} minimizes data latencies 
•  makes new data available without refresh schedules [1]
• quickly and automatically synchronizing a semantic model with its sources [1]
• {operation} refresh 
• low-cost operation that analyzes the metadata of the latest version of the Delta tables and updates references to the latest files in OneLake
• DL moves data preparation to OneLake
• ⇐ uses the full breadth of Fabric technologies for data prep
• incl. Spark jobs, T-SQL DML statements, dataflows, pipelines, etc.
• {operation} transcoding
• the process of loading data from OneLake only when a query requests a column for the first time 
• {action} the semantic model receives a DAX (or MDX) query 
• {step} determines what columns are needed to produce the result
• {step} determines which columns are already resident in memory [2]
• {step} loads the missing columns from OneLake [2]
•  typically a fast operation, however it can depend on factors 
•  e.g. the cardinality of data stored in the columns
•  remains resident until there's reason for it to be removed (evicted) from memory
• {reason} the model or table was refreshed after a Delta table update at the source [2]
• {reason} no query used the column for some time [2]
• {reason} other memory management reasons [2]
•  e.g. memory pressure in the capacity due to other, concurrent operations [2]
•  SKU determines the maximum available memory for each Direct Lake semantic model on the capacity [2]
• {operation} framing 
• an operation triggered by a refresh of a semantic model [2]
• it usually takes only a few seconds to complete
• ⇐ because it's a low-cost operation where the semantic model analyzes the metadata of the latest version of the Delta Lake tables and updates to reference the latest Parquet files in OneLake [2]
• the process might evict resident table column segments and dictionaries from memory if the underlying data changed [2]
• the point in time of the refresh becomes the new baseline for all future transcoding events [2]
• DL queries only consider data in the Delta tables as of the time of the most recent framing operation [2]
• DL tables are queried to return data based on the state of the Delta table at the point of the most recent successful framing operation [2]
• that time isn't necessarily the latest state of the Delta tables [2]
• the semantic model analyzes the Delta log of each Delta table during framing to drop only the affected column segments and to reload newly added data during transcoding [2]
• {optimization} dictionaries usually aren't dropped when incremental framing takes effect, and new values are added to the existing dictionaries [2]
• helps reduce the reload burden and benefits query performance [2]
• when a Delta table receives no updates, no reload is necessary for columns already resident in memory [2]
•  queries show far less performance impact after framing because incremental framing essentially enables the semantic model to update substantial portions of the existing in-memory data in place [2]
• can fail if a Delta table exceeds the Fabric capacity guardrails [2]
•  e.g. when a Delta table has more than 10,000 parquet files [2]
•  {architecture} Direct Lake on OneLake
•  isn't coupled with the SQL analytics endpoint [2]
•  offers tighter integration with OneLake features such as OneLake security [2]
•  offers more efficient DAX query plans because [2]
• ⇐ isn’t required to check for SQL based security [2]
•  fallback isn't supported [2]
• {architecture} Direct Lake on SQL analytics endpoint
•  a DAX query might use DirectQuery fallback [2]
•  involves seamlessly switching to DirectQuery mode [2]
•  DirectQuery fallback
• retrieves data directly from the SQL analytics endpoint of the lakehouse or the warehouse [2]
•  fallback occurs when SQL based security is detected in the SQL analytics endpoints
•  a DirectQuery operation sends a query to the SQL analytics endpoint. Fallback operations might result in slower query performance [2]
•  {setting} Keep your Direct Lake data up to date
• performs automatic updates of Direct Lake tables [2]
• via semantic model settings >> Refresh 
• {enabled|default} ensures that data changes in OneLake are automatically reflected in the Direct Lake semantic model [2]
•  the semantic model performs a framing operation whenever data modifications in the underlying Delta tables are detected [2]
• {recommendation} leave the setting on, especially when you have a small or medium-sized semantic model
• useful in case of low-latency reporting requirements and Delta tables are modified regularly [2]
•  {disabled} it might be needed to disable automatic updates to allow completion of data preparation jobs or an ETL process before exposing any new data to consumers of the semantic model [2]
•  users can trigger a refresh manually in the Fabric portal [2]
•  via refresh schedule, or programmatically by using the Power BI REST API or TOM [2]
• 
  

References: 
[1] Microsoft Learn (2026) Direct Lake overview [link]
[2] Microsoft Learn (2026) 

Acronyms:
API - Application Programming Interface
DL - Direct Lake 
MDX - Multidimensional Expressions
MF - Microsoft Fabric
REST
SKU - Stock Keeping Unit
TOM - Tabular Object Model
T-SQL - Transact SQL

 

💎🏭SQL Reloaded: QUALIFY in Microsoft Fabric (Before/After) 🆕

One recent addition to T-SQL is the QUALIFY clause, which filters rows returned by a query based on a search condition that can reference window (analytic) functions [1]. The QUALIFY clause allows filtering directly in the block in which the window function is defined. According to the documentation QUALIFY can be used directly after HAVING clause:

SELECT select_list
FROM   table_source
[ WHERE <search_condition> ]
[ GROUP BY group_by_specification ]
[ HAVING <search_condition> ]
[ QUALIFY <filter_condition> ]
[ ORDER BY <order_by_expression> [ , ...n ] ]
[ FOR JSON <options> ]

So, for example, if we consider a fictitious Sales Invoices table, to retrieve the customers who bought a product more than once, we had to apply the constraint outside the block in which the window function was defined:

-- product bought more than once by same customer (before)
SELECT *
FROM (-- calculating resultset
	SELECT customer_id
	, product_id
	, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
	FROM [Sales].[sales_invoices]
) DAT
WHERE total_quantity>1

Now, a constraint can be applied directly in the block with the window function via QUALIFY followed by one or more constraints:

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1

Given the order in which the constraints are processed, QUALIFY allows to reduce redundancy by referencing a new column defined in the SELECT statement (update 19-Oct-2026):

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY total_quantity>1

Hopefully this is available on all supported platforms. Otherwise, the logic must be repeated if a second constraint is needed for checking whether the value is within a boundary. Upon case, the logic can become even more complex (e.g. multiple intervals are checked):

-- product bought more than once by same customer (after) 
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY SUM(quantity) OVER(PARTITION BY invoice_id, product_id)>1

  AND SUM(quantity) OVER(PARTITION BY invoice_id, product_id)<10

Imagine how complex can become a query like the following (update 19-Oct-2026):

-- product bought more than once by same customer with interval testing
SELECT customer_id
, product_id
, SUM(quantity) OVER(PARTITION BY invoice_id, product_id) total_quantity
FROM [Sales].[sales_invoices]
QUALIFY (total_quantity>1 AND total_quantity)<10)

    OR (total_quantity>21 AND total_quantity)<30)

    OR (total_quantity>41 AND total_quantity)<50)

    OR (total_quantity>61 AND total_quantity)<70)  

Notes:
1) Having fewer blocks of code improves in theory code's readability and hopefully maintainability when the code doesn't need to be replicated as in the last example. This approach should improve also performance, however further tests are needed to validate this. 
2) It's useful to include the same logic used in QUALIFY also in the main SELECT statement, at least for troubleshooting purposes. 
3) The syntax seems to be supported only in Fabric warehouse, respectively SQL Endpoints for warehouse, lakehouse and SQL database.4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16).
4) QUALIFY clause requires at least one window function in the SELECT list, ORDER BY list or QUALIFY expression (see Msg 8826, Level 16). (update 19-Oct-2026)
5) One should check also whether the syntax is supported by all the features. For example, it's not supported by UPDATE and DELETE statements in warehouse. The restrictions for SQL Endpoints should be considered as well.
6) Developers must consider also portability of code when it's the case.
7) According to Copilot, QUALIFY is supported in Teradata (originator of the feature), Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL, DuckDB and ClickHouse. (update 19-Oct-2026)
8) Of course, the window functions can be mixed to address different business questions, though it's maybe a good idea to move the DENSE_RANK in SELECT and reference the new column within QUALIFY (update 19-Oct-2026):

-- first/last customer by product incl. total
SELECT product_id
, SUM(quantity) OVER(PARTITION BY product_id) quantity_total
, FIRST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date ASC) first_status
, LAST_VALUE(customer_id) OVER(PARTITION BY product_id ORDER BY invoice_date DESC) last_status
FROM [LH_CRM_Data].ERP.[sales_invoices_partitioned4]
QUALIFY DENSE_RANK() OVER(PARTITION BY product_id ORDER BY invoice_date)=1

 9) QUALIFY can be used also in combination with a CROSS/OUTER APPLY. For example, to retrieve the latest invoice details one could write a query like the one below (update 02-Nov-2026):

-- OUTER APPLY + QUALIFY
SELECT top 10 CST.CustomerID
, CST.CompanyName
, DAT.*
FROM [SalesLT].[Customer] CST
   OUTER APPLY (
   SELECT SalesOrderID
   , [ShipDate]
   , ROW_NUMBER() OVER (PARTITION BY SOH.CustomerID ORDER BY ShipDate) Ranking
   FROM [SalesLT].[SalesOrderHeader] SOH
   WHERE CST.CustomerID = SOH.CustomerID
   QUALIFY Ranking = 1
   ) DAT
--WHERE DAT.SalesOrderID IS NOT NULL -- testing
ORDER BY CST.CompanyName

It can be surprising that it works (at least I was positively surprised).
Instead of OUTER APPLY one could use a LEFT JOIN (the two versions return the same results):

-- LEFT JOIN + QUALIFY
SELECT top 10 CST.CustomerID
, CST.CompanyName
, DAT.*
FROM [SalesLT].[Customer] CST
   LEFT JOIN (
   SELECT SalesOrderID
   , CustomerID
   , [ShipDate]
   , ROW_NUMBER() OVER (PARTITION BY SOH.CustomerID ORDER BY ShipDate) Ranking
   FROM [SalesLT].[SalesOrderHeader] SOH
   QUALIFY Ranking = 1
   ) DAT
   ON CST.CustomerID = DAT.CustomerID
--WHERE DAT.SalesOrderID IS NOT NULL -- testing
ORDER BY CST.CompanyName

Happy coding!

References:
[1] Microsoft Learn (2026) SELECT - QUALIFY clause (Transact-SQL) [link]

🏭🗒️Microsoft Fabric: Materialized Lake Views (MLV) [Notes] 🆕🗓️

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 27-Jul-2025

-- create schema
CREATE SCHERA IF NOT EXISTS <lakehouse_name>.<schema_name>

-- create a materialized view
CREATE MATERIALIZED VIEW IF NOT EXISTS <lakehouse_name>.<schema_name>.<view_name> 
[(
    CONSTRAINT <constraint_name> CHECK (<constraint>) ON MISMATCH DROP 
)] 

[PARTITIONED BY (col1, col2, ... )] 
[COMMENT “description or comment”] 
[TBLPROPERTIES (“key1”=”val1”, “key2”=”val2”, 

AS 
SELECT ...
FROM ...
-- WHERE ...
--GROUP BY ...

[Microsoft Fabric] Materialized Lake Views (MLVs)

• {def} persisted, continuously updated view of data [1]
• {benefit} allows to build declarative data pipelines using SQL, complete with built-in data quality rules and automatic monitoring of data transformations
• simplifies the implementation of multi-stage Lakehouse processing [1]
• ⇐ aids in the creation, management, and monitoring of views [3]
• ⇐ improves transformations through a declarative approach [3]
• streamline data workflows
• enable developers to focus on business logic [1]
• ⇐ not on infrastructural or data quality-related issues [1]
• the views can be created in a notebook [2]
• {benefit} allows developers visualize lineage across all entities in lakehouse, view the dependencies, and track its execution progress [3]
• can have data quality constraints enforced and visualized for every run, showing completion status and conformance to data quality constraints defined in a single view [1]
• empowers developers to set up complex data pipelines with just a few SQL statements and then handle the rest automatically [1]
• faster development cycles 
• trustworthy data
• quicker insights
• {goal} process only the new or changed data instead of reprocessing everything each time [1]
• ⇐  leverages Delta Lake’s CDF under the hood
• ⇒ it can update just the portions of data that changed rather than recompute the whole view from scratch [1]
• {operation} creation
• allows defining transformations at each layer [1]
• e.g. aggregation, projection, filters
• allows specifying certain checks that the data must meet [1]
• incorporate data quality constraints directly into the pipeline definition
• via CREATE MATERIALIZED LAKE VIEW
• the SQL syntax is declarative and Fabric figures out how to produce and maintain it [1]
• {operation} refresh
• refreshes only when its source has new data [1]
• if there’s no change, it can skip running entirely (saving time and resources) [1]
• via REFRESH MATERIALIZED LAKE VIEW [workspace.lakehouse.schema].MLV_Identifier [FULL];
• {operation} list views from schema [3]
• via SHOW MATERIALIZED LAKE VIEWS <IN/FROM> Schema_Name;
• {opetation} retrieve definition
• via SHOW CREATE MATERIALIZED LAKE VIEW MLV_Identifier;
• {operstion} update definition
• via ALTER MATERIALIZED LAKE VIEW MLV_Identifier RENAME TO MLV_Identifier_New;
• {operstion} drop view
• via DROP MATERIALIZED LAKE VIEW MLV_Identifier;
• {warning} dropping or renaming a materialized lake view affects the lineage view and scheduled refresh [3]
• {recommendation} update the reference in all dependent materialized lake views [3]
• {operation} schedule view run
• lets users set how often the MLV should be refreshed based on business needs and lineage execution timing [5]
• depends on
• data update frequency: the frequency with which the data is updated [5]
• query performance requirements: Business requirement to refresh the data in defined frequent intervals [5]
• system load: optimizing the time to run the lineage without overloading the system [5]
• {operation} view run history
• users can access the last 25 runs including lineage and run metadata
• available from the dropdown for monitoring and troubleshooting
• {concept} lineage
• the sequence of MLV that needs to be executed to refresh the MLV once new data is available [5]
• {feature} automatically generate a visual report that shows trends on data quality constraints 
• {benefit} allows to easily identify the checks that introduce maximum errors and the associated MLVs for easy troubleshooting [1]
• {feature} can be combined with Shortcut Transformation feature for CSV ingestion 
• {benefit} facilitate the building of end-to-end Medallion architectures
• {feature} dependency graph
• allows to see the dependencies existing between the various objects [2]
• ⇐ automatically generated [2]
• {feature} data quality
• {benefit} allows to compose precise queries to exclude poor quality data from the source tables [5]
• [medallion architecture] ensuring data quality is essential at every stage of the architecture [5]
• maintained by setting constraints when defining the MLVs [5]
• {action} FAIL
• stops refreshing an MLV if any constraint is violated [5]
• {default} halt is at the first instance
• even without specifying the FAIL keyword [5]
• takes precedence over DROP
• {action} DROP
• processes the MLV and removes records that don't meet the specified constrain [5]
• provides the count of removed records in the lineage view [5]
• {constraint} updating data quality constraints after creating an MLV isn't supported [5]
• ⇐ the MLV must be recreated
• {constraint} the use of functions and pattern search with operators in constraint condition is restricted [5]
• e.g. LIKE, regex 
• {known issue} the creation and refresh of an MLV with a FAIL action in constraint may result in a "delta table not found" error
• {recommendation} recreate the MLV and avoid using the FAIL action [5] 
• {feature} data quality report
• built-in Power BI dashboard that shows several aggregated metrics [2]
• {feature} monitor hub
• centralized portal to browse MLV runs in the lakehouse [7]
• {operation} view runs' status [7]
• {operation} search and filter the runs [7]
• based on different criteria
• {operation} cancel in-progress run [7]
• {operation} drill down run execution details [7]
• doesn't support
• {feature|planned} PySpark [3]
• {feature|planned} incremental refresh [3]
• {feature|planned} integration with Data Activator [3]
• {feature|planned} API [3]
• {feature|planned} cross-lakehouse lineage and execution [3]
• {limitation} Spark properties set at the session level aren't applied during scheduled lineage refresh [4]
• {limitation} creation with delta time-travel [4]
• {limitation} DML statements [4]
• {limitation} UDFs in CTAS [4] 
• {limitation} temporary views can't be used to define MLVs [4]
•  {recommendation} create all MLVs for a workspace within a single Lakehouss [8]
• allows to ensure a consolidated and clear representation in the DAG view [8]

Previous Post <<||>> Next Post

References:

[1] Microsoft Fabric Update Blog (2025) Simplifying Medallion Implementation with Materialized Lake Views in Fabric [link|aka]

[2] Power BI Tips (2025) Microsoft Fabric Notebooks with Materialized Views - Quick Tips [link]

[3] Microsoft Learn (2025) What are materialized lake views in Microsoft Fabric? [link]

[4] Microsoft Learn (2025) Materialized lake views Spark SQL reference [link]

[5] Microsoft Learn (2025) Manage Fabric materialized lake views lineage [link] 

[6] Microsoft Learn (2025) Data quality in materialized lake views [link]

[7] Microsoft Learn (2025) Monitor materialized lake views [link] 
[8] Microsoft Learn (2025) Development and Monitoring [link]

Resources:
[R1] Databricks (2025) Use materialized views in Databricks SQL [link]

[R2] Microsoft Learn (2025) Implement medallion architecture with materialized lake views [link]

[R3] Mastering Declarative Data Transformations with Materialized Lake Views [link]

Acronyms:

API - Application Programming Interface

CDF - Change Data Feed

CTAS - Create Tables As Select

DML - Data Manipulation Language 

ETL - Extract, Transfer, Load

MF - Microsoft Fabric
MLV - Materialized Lake views

UDF - User-defined functions

🏭🗒️Microsoft Fabric: Warehouse Snapshots [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 23-May-2025

[Microsoft Fabric] Warehouse Snapshots

• {def} read-only representation of a warehouse at a specific point in time [1]
• allows support for analytics, reporting, and historical analysis scenarios without worrying about the volatility of live data updates [1]
• provide a consistent and stable view of data [1]
• ensuring that analytical workloads remain unaffected by ongoing changes or ETL  operations [1]
• {benefit} guarantees data consistency
• the dataset remains unaffected by ongoing ETL processes [1]
• {benefit} immediate roll-Forward updates
• can be seamlessly rolled forward on demand to reflect the latest state of the warehouse
• ⇒ {benefit} consumers access the same snapshot using a consistent connection string, even from third-party tools [1]
• ⇐ updates are applied immediately, as if in a single, atomic transaction [1]
• {benefit} facilitates historical analysis
• snapshots can be created on an hourly, daily, or weekly basis to suit their business requirements [1]
• {benefit} enhanced reporting
• provides a point-in-time reliable dataset for precise reporting [1]
• ⇐ free from disruptions caused by data modifications [1]
• {benefit} doesn't require separate storage [1]
• relies on source Warehouse [1]
• {limit} doesn't support database objects 
• {limit} capture a state within the last 30 days
• {operation} create snapshot
• via New warehouse snapshot
• multiple snapshots can be created for the same parent warehouse [1]
• appear as child items of the parent warehouse in the workspace view [1]
• the queries run against provide the current version of the data being accessed [1]
• {operation} read properties 
• via 
• GET https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items/{warehousesnapshotId} Authorization: Bearer <bearer token>
• {operation} update snapshot timestamp
• allows users to roll forward data instantly, ensuring consistency [1]
• use current state
• via ALTER DATABASE [<snapshot name>] SET TIMESTAMP = CURRENT_TIMESTAMP; 
• use point in time
• ALTER DATABASE snapshot SET TIMESTAMP = 'YYYY-MM-DDTHH:MM:SS.SS'//UTC time
• queries that are in progress during point in time update will complete against the version of data they were started against [1]
• {operation} rename snapshot
• {operation} delete snapshot
• via DELETE
• when the parent warehouse gets deleted, the snapshot is also deleted [1]
• {operation} modify source table
• DDL changes to source will only impact queries in the snapshot against tables affected [1]
• {operation} join multiple snapshots
• the resulting snapshot date will be applied to each warehouse connection [1]
• {operation} retrieve metadata
• via sys.databases [1]
• [permissions] inherited from the source warehouse [1]
• ⇐ any permission changes in the source warehouse applies instantly to the snapshot [1]
• security updates on source database will be rendered immediately to the snapshot databases [1]
• {limitation} can only be created against new warehouses [1]
• created after Mar-2025
• {limitation} do not appear in SSMS Object Explorer but will show up in the database selection dropdown [1]
• {limitation} datetime can be set to any date in the past up to 30 days or database creation time (whichever is later)  [1]
• {limitation} modified objects after the snapshot timestamp become invalid in the snapshot [1]
• applies to tables, views, and stored procedures [1]
• {limitation} must be recreated if the data warehouse is restored [1]
• {limitation} aren’t supported on the SQL analytics endpoint of the Lakehouse [1]
• {limitation} aren’t supported as a source for OneLake shortcuts [1]
•  [Power BI]{limitation} require Direct Query or Import mode [1]
• don’t support Direct Lake

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2025) Fabric: Warehouse Snapshots in Microsoft Fabric (Preview) [link]

[2] Microsoft Learn (2025) Warehouse snapshots (preview) [link]

[3] Microsoft Learn (2025) Create and manage a warehouse snapshot (preview) [link]

Resources:

Acronyms:

DDL - Data Definition Language
ETL - Extract, Transfer, Load

MF - Microsoft Fabric
SSMS - SQL Server Management Studio

🏭🗒️Microsoft Fabric: Parameters in Dataflows Gen2 [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 26-Apr-2

[Microsoft Fabric] Dataflow Gen2 Parameters

• {def} parameters that allow to dynamically control and customize Dataflows Gen2
• makes them more flexible and reusable by enabling different inputs and scenarios without modifying the dataflow itself [1]
• the dataflow is refreshed by passing parameter values outside of the Power Query editor through either
• Fabric REST API [1]
• native Fabric experiences [1]
• parameter names are case sensitive [1]
• {type} required parameters
• {warning} the refresh fails if no value is passed for it [1]
• {type} optional parameters
• enabled via Parameters >> Enable parameters to be discovered and override for execution [1]
• {limitation} dataflows with parameters can't be
• scheduled for refresh through the Fabric scheduler [1]
• manually triggered through the Fabric Workspace list or lineage view [1]
• {limitation} parameters that affect the resource path of a data source or a destination are not supported [1]
• ⇐ connections are linked to the exact data source path defined in the authored dataflow
• can't be currently override to use other connections or resource paths [1]
• {limitation} can't be leveraged by dataflows with incremental refresh [1]
• {limitation} supports only parameters of the type decimal number, whole number, text and true/false can be passed for override
• any other data types don't produce a refresh request in the refresh history but show in the monitoring hub [1]
• {warning} allow other users who have permissions to the dataflow to refresh the data with other values [1]
• {limitation} refresh history does not display information about the parameters passed during the invocation of the dataflow [1]
• {limitation} monitoring hub doesn't display information about the parameters passed during the invocation of the dataflow [1]
• {limitation} staged queries only keep the last data refresh of a dataflow stored in the Staging Lakehouse [1]
• {limitation} only the first request will be accepted from duplicated requests for the same parameter values [1]
• subsequent requests are rejected until the first request finishes its evaluation [1]

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2025) Use public parameters in Dataflow Gen2 (Preview) [link] 

Resources:
[R1] Microsoft Fabric Blog (2025) Passing parameter values to refresh a Dataflow Gen2 (Preview) [link] 

Acronyms:
API - Application Programming Interface

REST - Representational State Transfer

🏭🗒️Microsoft Fabric: Dataflows Gen2's Incremental Refresh [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 24-Nov-2025

[Microsoft Fabric] Incremental Refresh in Dataflows Gen2

• {feature} enables to incrementally extract data from data sources, apply Power Query transformations, and load into various output destinations [5]
• designed to reduce the amount of data that needs to be processed and retrieved from the source system [8]
• configurable directly in the dataflow editor [8]
• doesn't need to specify the historical data range [8]
• ⇐ the dataflow doesn't remove any data from the destination that's outside the bucket range [8]
• doesn't need to specify the parameters for the incremental refresh [8]
• the filters and parameters are automatically added as the last step in the query [8]
• {prerequisite} the data source 
• supports folding [8]
• needs to contain a Date/DateTime column that can be used to filter the data [8]
• {prerequisite} the data destination supports incremental refresh [8]
• available destinations
• Fabric Warehouse
• Azure SQL Database
• Azure Synapse Analytics
• Fabric Lakehouse [preview]
• other destinations can be used in combination with incremental refresh by using a second query that references the staged data to update the data destination [8]
• allows to use incremental refresh to reduce the amount of data that needs to be processed and retrieved from the source system [8]
• a full refresh from the staged data to the data destination is still needed [8]
• works by dividing the data into buckets based on a DateTime column [8]
• each bucket contains the data that changed since the last refresh [8]
• the dataflow knows what changed by checking the maximum value in the specified column 
• if the maximum value changed for that bucket, the dataflow retrieves the whole bucket and replaces the data in the destination [8]
• if the maximum value didn't change, the dataflow doesn't retrieve any data [8]
• {setting|required} bucket size
• specifies the size of the buckets that the dataflow uses to filter data [8] 
• smaller bucket size
• the dataflow processes less data in each iteration, but requires more iterations to process all the data [8]
• larger bucket size
• the dataflow processes more data in each iteration, but requires fewer iterations to process all the data [8]
• {setting} only extract new data when the maximum value in this column changes
•   specifies the column that the dataflow uses to determine if data changed [8]
• the dataflow compares the maximum value to the maximum value from the previous refresh [8]
• if the maximum value changed, the dataflow gets the data that changed since the last refresh [8]
• if the maximum value didn't change, the dataflow doesn't get any data [8]
• {setting|optional} only extract data for concluded periods
• specifies whether the dataflow should only extract data for concluded periods [8]
• when disabled, the dataflow extracts data for all periods, including periods that aren't complete and contain future data [8]
• {setting} require incremental refresh query to fully fold [8]
• controls whether the incremental refresh query must 'fully fold' [8]
• ⇒ it gets pushed down entirely to the source system for processing [8]
• {recommendation}keep this setting enabled
• after saving the dataflow, it validates whether query folding to the source is possible
• if the validation fails, the dataflow may suffer from reduced performance and could end up retrieving unnecessary, unfiltered data [8]
• when enabled the query must fully fold [8]
• when disabled, the query can be partially processed by the dataflow instead of the source system [8]
• {limitation} the maximum number of concurrent evaluations: 10 [8]
•  the dataflow can only evaluate 10 buckets at the same time [8]
•  for more than 10 buckets, is needed to limit the number of buckets or limit the number of concurrent evaluations [8]
• {limitation} [lakehouse] if other tools (like Spark) or processes also write to the same table, they can interfere with incremental refresh [8]
• {recommendation} avoid using other writers while using incremental refresh [8]
• {recommendation}make sure other writers don't conflict with the incremental refresh process [8] 
• {limitation} table maintenance operations aren't supported for tables that use incremental refresh [8]
• e.g. OPTIMIZE/REORG TABLE
• {limitation} the data destination must be set to a fixed schema [8]
• ⇒ table's schema in the data destination must be fixed and can't change [8]
• ⇒ dynamic schema must be changed to fixed schema before configuring incremental refresh [8]
• {limitation} the only supported update method in the data destination: replace
• ⇒the dataflow replaces the data for each bucket in the data destination with the new data [8]
• data that is outside the bucket range isn't affected [8
• {limitation} maximum number of buckets
• single query: 50
• {workaround} increase the bucket size or reduce the bucket range to lower the number of buckets [8]
• whole dataflow: 150
• {workaround} reduce the number of incremental refresh queries or increase the bucket size [8]
• {downside} the dataflow may take longer to refresh after enabling incremental refresh [8]
• because the additional overhead of checking if data changed and processing the buckets is higher than the time saved by processing less data [8]
• {recommendation} review the settings for incremental refresh and adjust them to better fit the scenario
• {option} increase the bucket size to reduce the number of buckets and the overhead of processing them [8]
• {option} reduce the number of buckets by increasing the bucket size [8]
• {option} disable incremental refresh [8]
• {recommendation} don't use the column for detecting changes also for filtering [8]
• because this can lead to unexpected results [8]
• {recommendation} ensure that the gateway is updated to at least version May 2025 update (3000.270) or later [8]
• allows to maintain compatibility while ensuring that incremental refresh functions correctly with lakehouse destinations [8]
• {limitation} switching from non-incremental to incremental refresh with existing overlapping data in the destination is not supported [8]
• {recommendation} filter the initial ingestion to include only data before the earliest incremental bucket to avoid overlap and ensure correct refresh behavior [8[
• {setting} limit number of concurrent evaluation
• setting the value to a lower number, reduces the number of requests sent to the source system [8]
• via global settings >> Scale tab >> maximum number of parallel query evaluations
• {recommendation} don't enable this limit unless there're issues with the source system [8]

Previous Post <<||>> Next Post 

References:

[5] Microsoft Learn (2023) Fabric: Save a draft of your dataflow [link]
[8] Microsoft Learn (2025) Fabric: Incremental refresh in Dataflow Gen2 [link] 

Resources:

🏭🗒️Microsoft Fabric: Quotas [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 13-Apr-2025

[Microsoft Fabric] Quotas

• {def} assigned number of resources for an Azure subscription
• depend on the Azure subscription type [5]
• set and enforced in the scope of the subscription [1]
• each subscription has a default value for each quota [1]
• determine the maximum number of CUs for each of the capacities on the subscription [5]
• customers don't get charged for quotas, but on capacities [5]
• each quota represents a specific countable resource
• e.g. number of VMs that can be created [2]
• e.g. the number of storage accounts that can be used concurrently [2]
• e.g. the number of networking resources that can be consumed [2]
• e.g. the number of API calls to a particular service that can be made [2]
• designed to help protect customers from unexpected behavior [1]
• e.g. inaccurately resourced deployments, mistaken consumption
• helps minimize risks from deceptive or inappropriate consumption and unexpected demand [1]
• limitations
• ⇐ some limits are managed at a regional level [2]
• ⇐ variable and dependent on several factors
• {type} adjustable quotas
• quotas for which customers can request quota increases
• via Azure Home My quotas page
• based on amount or usage percentage and submitting it directly [1]
• each subscription has a default quota value for each quota [1]
• the quickest way to increase quotas [1]
• {type} non-adjustable quotas
• quotas which have a hard limit, usually determined by the scope of the subscription [1]
• to make changes, customers must submit a support request for the Azure support team [1]
• [Fabric] limit the number of CUs customers can provision across multiple capacities in a subscription
• calculation based on
• subscription plan type
• Azure region
• {action} provision a new capacity
• {level} Azure management group limits
• {level} Azure subscription limits
• {level} Azure resource group limits
• {level} Template limits
• {level} Microsoft Entra ID limits
• {level} Azure API Center limits
• {level} Azure API Management limits
• {level} API Management v2 tiers
• {level} Azure App Service limits
• {level} Azure Automation limits
• {action} request quota increase
• via support request
• there is no cost associated with requesting a quota increase [1]
• ⇐ costs are incurred based on resource usage, not the quotas themselves [1]
• should be based on workloads' characteristics [1]
• {action} view quota
• [permission] contributor role, or another role that includes contributor access [5]
• {action} manage quota
• [permission] see Quota Request Operator
• [Azure] quota alerts
• notifications triggered when the usage of a specific Azure resource nears the predefined quota limit [4]
• facilitates proactive resource management [4]
• multiple alert rules can be created for a given quota or across quotas in a subscription [4]

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2024) Fabric: Quotas overview [link] 

[2] Microsoft Learn (2025) Fabric: Azure subscription and service limits, quotas, and constraints [link] 

[3] Microsoft Learn (2025) Fabric: Quota monitoring and alerting [link] 

[4] Microsoft Fabric Update Blog (2024) Announcing the launch of Microsoft Fabric Quotas [link]
[5] Microsoft Learn (2025) Fabric: Microsoft Fabric capacity quotas [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms:

API - Application Programming Interface
CU - Capacity Units

VM - Virtual Machine

🏭🗒️Microsoft Fabric: User Data Functions (UDFs) in Python [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 7-Apr-2025

[Microsoft Fabric] User Data Functions (UDFs)

• {def} a platform that allows customers to host and run custom logic on Fabric from different types of items and data sources [1]
• empower data developers to write custom logic and embed it into their Fabric ecosystem [1]
• the logic can include internal algorithms and libraries [1]
• {benefit} allows to handle complex transformations, optimizing performance, and integrating diverse data sources beyond the capabilities of low-code tools [5]
• via public libraries from PyPI
• via native Fabric integrations to 
• connect to Fabric data sources
• e.g. warehouse, lakehouse or SQL Database
• invoke functions from other Fabric items
• e.g. notebooks, Power BI reports, data pipelines
• via edit functions directly in the Fabric portal [1]
• via in-browser tooling, VS Code extension
• supports the Python 3.11 runtime [1]
• {goal} reusability
• by creating libraries of standardized functionality [1]
• can be used in many solutions across the organization [1]
• {goal} customization
• the applications are tailored to customers’ needs [1]
• {goal} encapsulation
• functions can perform various tasks to build sophisticated workflows [1]
• {goal} external connectivity
• data functions can be invoked from external client applications using a REST endpoint [1]
• allows integrations with external systems [1]
• {feature} programming model
• SDK that provides the necessary functionality to author and publish runnable functions in Fabric [4]
• {benefit} allows to seamlessly integrate with other items in the Fabric ecosystem [4]
• e.g. Fabric data sources
• {concept} user data functions item 
• contains one or many functions that can be invoked from the Fabric portal using the provided REST endpoint [4]
• from another Fabric item, or from an external application 
• each function is a method in Python script that allows passing parameters and returning an output to the invoker [4]
• {component} fabric.functions library 
• provides the code needed to create user data functions in Python [4]
• imported in the template by default [4]
• {method} fn.UserDataFunctions() 
• provides the execution context [4]
• added at the beginning of the code file in all new user data functions items, before any function definitions [4]
• {concept} functions
• every function is identified with a @udf.function() decorator
• can be invoked individually from the portal or an external invoker [4]
• functions without the decorator can be invoked directly [4]
• {feature} invocation logs
• function invocations are logged
• {benefit} allows to check the status or perform debugging  [3]
• {limitation} can take few minutes to appear [3]
• {recommendation} refresh the page after a few minutes [3]
• {limitation} daily ingestion limit: 250 MB [3]
• reset the next day 
• a new log is made available [3]
• {limitation} logs are sampled while preserving a statistically correct analysis of application data [3]
• the sampling is done by User data functions to reduce the volume of logs ingested [3]
• if some logs are partially missing, it might be because of sampling [3]
• {limitation} supported log types: information, error, warning, trace [3]
• {feature} Manage connections
• {limitation} only supports connecting to Fabric-native data sources [2]
• {limitation} only available in a subset of Fabric regions [2]
• {limitation} editable by the owner only [2]
• can only be modified and published by the user who is the owner of the User Data Functions Fabric item [2]
• {limitation} adds further keywords to the list of reserved keywords
• namely: req, context, reqInvocationId
• can't be used as parameter names or function names [2]

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2025) Microsoft Fabric: What is Fabric User data functions (Preview)? [link]

[2] Microsoft Learn (2025) Microsoft Fabric: Service details and limitations of Fabric User Data Functions [link]

[3] Microsoft Learn (2025) Microsoft Fabric: User data functions invocation logs (Preview) [link]

[4] Microsoft Learn (2025) Microsoft Fabric: Fabric User data functions programming model overview (Preview) [link]
[5] Microsoft Fabric Updates Blog (2025) Announcing Fabric User Data Functions (Preview) [link]

[6] Microsoft Learn (2025) Microsoft Fabric: Use the Functions activity to run Fabric user data functions and Azure Functions [link] 

Resources:

[R1] Microsoft Fabric Updates Blog (2025) Utilize User Data Functions in Data pipelines with the Functions activity (Preview) [link] 

Acronyms:
PyPI - Python Package Index
REST - Representational State Transfer
SDK - Software Development Kit
UDF - User Data Function
VS - Visual Studio

🏭🗒️Microsoft Fabric: Query Optimizer in Warehouse [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 6-Apr-2025

[Microsoft Fabric] Hints in Warehouse

• {def} keywords that users can add to SQL statements to provide additional information or instructions to the query optimizer [2]
• options or strategies specified for enforcement by the SQL Server query processor [1]
• applicable to SELECT, INSERT, UPDATE, or DELETE statements [1]
• {benefit} help improve the performance, scalability, or consistency of queries by overriding the default behavior of the query optimizer [2]
• {type} join hints
• specify what join strategy/algorithm used between two tables [3]
• improves the performance of queries that involve large or complex joins [2]
• specified in the FROM clause of a query [1]
• if a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query [3]
• based on the position of the ON keywords [3]
• when a CROSS JOIN is used without the ON clause, parentheses can be used to indicate the join order [3]
• looping
• via LOOP JOIN
• {restriction} can't be specified together with RIGHT or FULL as a join type [3]
• hashing
• via HASH JOIN
• merging
• via MERGE JOIN
• REPLICATE
• causes a broadcast move operation
• a specific table to be replicated across all distribution nodes [2]
• with INNER or LEFT` join
• the broadcast move operation will replicate the right side of the join to all nodes [2]
• with RIGHT join
• the broadcast move operation will replicate the left side of the join to all nodes [2]
• with FULL` join
• an estimated plan cannot be created [2]
• REDISTRIBUTE [(colsCount)]
• ensures two data sources are distributed based on JOIN clause columns [2]
• handles multiple join conditions, specified by the first n columns in both tables, where n is the column_count argument [2]
• redistributing data optimizes query performance by evenly spreading data across nodes during intermediate steps of execution [2]
• the (columns_count) argument is only supported in MF warehouse [2]
• {type} query hint
• specify that the indicated hints are used in the scope of a query [3]
• affect all operators in the statement
• [UNION only the last query involving a UNION operation can have the OPTION clause [3]
• specified as part of the OPTION clause [3]
• Error 8622 occurs if one or more query hints cause the Query Optimizer not to generate a valid plan [3]
• used via the OPTION clause at the end of a query [2]
• followed by the name of the query hint and its optional parameters in parentheses [2]
• multiple hints can be used in the same query, separated by commas
• e.g. FORCE ORDER and MAX_GRANT_PERCENT
• instruct the QO to preserve the join order specified in the query and to limit the memory grant to 20 percent of the available memory
• {hint} HASH GROUP
• specifies that the QO should use a hash-based algorithm for the GROUP BY operation [2]
• {benefit} can improve the performance of queries that involve large or complex grouping sets [2]
• {hint} ORDER GROUP
• specifies that the QO should use a sort-based algorithm for the GROUP BY operation [2]
• {benefit} can improve the performance of queries that involve small or simple grouping sets [2]
• {hint} MERGE UNION
• specifies that the QO should use a merge-based algorithm for the UNION or UNION ALL operation [2]
• {benefit} can improve the performance of queries that involve sorted inputs [2]
• {hint} HASH UNION
• specifies that the query optimizer should use a hash-based algorithm for the UNION or UNION ALL operation [2]
• {benefit} can improve the performance of queries that involve unsorted or large inputs [2]
• {hint} CONCAT UNION
• specifies that the QO should use a concatenation-based algorithm for the UNION or UNION ALL operation [2]
• {benefit} can improves the performance of queries that involve distinct or small inputs[2]
• {hint} FORCE ORDER
• specifies that the QO should preserve the join order specified in the query [2]
• {benefit} can improves the performance or consistency of queries that involve complex join conditions or hints [2]
• {hint} FORCE SINGLE NODE PLAN/FORCE DISTRIBUTED PLAN
• allows to choose whether to force a single node plan or a distributed plan for query’s execution [2]
• {hint} USE HINT
• adds one or more extra hints to the query processor, where the hints are specified with a hint name inside single quotation marks inside OPTION clause [2] OPTION(USE HINT(‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’)) 
• used with several hint names, changing the behavior of CE derivation
• {hint name} ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
• applies when calculating cardinality estimates for AND predicates for filters [2]
• MF assumes full correlation among filters when a high level of underestimation on AND predicates for filters is observed [2]
• [SQL Server] equivalent TF 4137 [4]
• {hint name} ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES
• applies when calculating cardinality estimates for AND predicates for filters  [2]
• MF assumes full independence among filters [2]
• if a high level of overestimation on AND predicates for filters is observed, this hint can help produce a better estimate [2]
• {hint name} ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES
• applies when calculating cardinality estimates for AND predicates for filters [2]
• {default} MF assumes partial correlation among filters [2]
• ⇐ it is unlikely that this hint will help improve the estimates [2]
• {hint name} ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
• applies when calculating cardinality estimate for joins [2]
• uses Simple Containment assumption instead of the default Base Containment assumption [2]
• [SQL Server] equivalent TF 9476 [4]
• {type} table hints
• none enforced currently

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2025) SQL: Hints (T-SQL) [link]

[2] Microsoft Fabric Updates Blog (2025) Hints in Fabric Data Warehouse [link]
[3] Microsoft Learn (2025) SQL: OPTION clause (T-SQL) [link]
[4] Microsoft Support (2016) KB3189813 - Update introduces USE HINT query hint argument in SQL Server 2016 [link]

Resources:
[R1] Microsoft Learn (2025) SQL Server: Query hints (T-SQL) [link]
[R2] Most Useful Query Hints [link]

Acronyms:
MF - Microsoft Fabric
TF - Trace Flag
QO - Query Optimizer