Showing posts with label Microsoft Fabric. Show all posts
Showing posts with label Microsoft Fabric. Show all posts

17 November 2026

🏭🗒️Microsoft Fabric: Capacity Management [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: 16-Nov-2026

[Microsoft Fabric] Capacity Management

  • {def} process for the administration and governance of the compute resources (CUs) available in a Fabric capacity
  • {concept} capacity 
    • {def} a dedicated set of compute resources that are available at a given time to be used
      • each capacity has fixed compute resources measured in CUs [6]
      • defines the ability of a resource to perform an activity or to produce output
      • resides in a specific region of the buyers’ choice where both the data & compute reside [3]
      • provides the compute and storage for all the workspace artifacts [3]
        • workspaces are assigned to a capacity 
    • shared across:
      • workloads
        • a single capacity provides the computing power for all Fabric workloads, and there is no need to allocate compute separately per engine.
      • projects
        • a capacity typically supports dozens of separate projects, each in its own workspace(s).
      • users
        • every user action that consumes compute (e.g. queries, refreshes, pipeline runs, notebook execution) uses the same capacity pool.
    • priced at a fixed hourly rate 
      • multiple capacities can be purchased, deployed and managed by different owners residing in a single tenant 
        • {benefit} allows each business unit to pay for their own consumption [3]
      • based on CUs provisioned [3]
      • dimensioned as a function of cores: 
        • F2=2^1, F4=2^2, ..., F32=2^5, F64=2^6, ..., F2048=2^11 (powers of 2 from 1..11)
        • ⇐ flexible building blocks for growth
        • with increased Fx it provides
          • better concurrency 
          • lower throttling risk
          • more realistic PoCs
          • better workload isolation
          • increased reliability 
      • purchased either as 
        • PAYG model
        • RI resource
        • F0 
  • {concept} Stock Keeping Unit (SKU)
    • {def} the capacity tier that determines how much compute power, memory, and overall performance a Fabric environment receives
  • {concept} capacity unit (CU) 
    • {def} the standardized measure of computing resources available to a Fabric capacity
      • used to abstract away the actual CPU cores, memory, and infrastructure so that customers can size Fabric workloads consistently
        •  measure the compute power available for each SKU [2]
    • CUs consumption is not a hard limit per SKU
      • based on an average over a 24-hour period
        • gives customers the ability to take advantage of bursting and smoothing
  • {concept} timepoint 
    • {def} internal measurement intervals MF uses to evaluate how much CU consumption is occurring over time and whether the capacity is being throttled
      •  24 hours contain 2,880 timepoints
      •  Fabric automatically manages the amount of consumed CUs in each timepoint [2]
      •  the boundaries are aligned to the Fabric service clock
      •  a workload is charged to whichever 30-second timepoint(s) are active when the work executes
  • {concept} overage
    • the amount of capacity debt that would otherwise cause throttling because a capacity has consumed more compute than its provisioned CUs can support after smoothing
      •  when overages exceed the allowed 10-minute throttling window, they become carryforward CUs [2]
        • carryforward CUs apply to each subsequent timepoint [2]
        • if a timepoint isn't full, the unused CUs reduce the carryforward CUs amount (aka burndown)
  • {operation} provisioning (aka capacity provisioning)
    • provisioned with a certain amount of compute units, analogous to CPU cores
    • there is no authoritative guide to help customers calculate the CUs required by a solution
    • {recommendation} experiment to figure out the best fit
      • allows to find the optimum size for workloads  
  • {operation} configuration (aka capacity configuration) 
    • capacities can be configured in endless ways to meet scale, usage and governance requirements while tuning to minimize TCO and performance goals [3]
  • {mechanism} bursting
    • the ability for a Fabric capacity to temporarily consume more compute resources beyond what the customer purchased, respectively beyond what was allocated for a given job
    • {benefit} job acceleration 
      • allows to speed workloads execution
        • e.g. instead of running a job on 64 CUs in 60 seconds, bursting could use 256 CUs to complete in 15 seconds
    • {benefit} use extra resources
      • allows workloads to use additional compute resources to handle spikes in capacity requirements over short periods of time
        • allows jobs to utilize far more resources than 'face value'
      • improves users’ experience
      • provides a temporary relief, until the burst allowance is eventually exhausted
      • {warning} can hide capacity sizing problems
        • because bursting absorbs occasional overloads
        • potential for future throttling, if not addressed timely
    • {characteristic} automatic 
      • automatically allocates resources as needed to execute at maximum performance [3]
        • regardless of SKU
        • {warning} one query could consume all the quota of a single time window and much more! [3]
      • when the system reasons it can accelerate the job by applying extra resources [3]
        • occurs behind the scenes, acting as a buffer
        • no settings are required [3]
  • {mechanism} smoothing (aka averaging)
    • a mechanism that spreads the CU consumption of operations across future time periods [5]
      • instead of scheduling all consumption immediately [5]
      • run when small usage bursts occur, flattening compute usage over time to help customers avoid being penalized with additional cost [5]
        • operation's utilization type determines the number of timepoints that Fabric uses for smoothing [2]
          • interactive operations are smoothed over a minimum of five minutes, and up to 64 minutes depending on how much CU usage they consume [2]
          • background operations are smoothed over a 24-hour period because [2]
            • they typically have long runtimes and large CU consumption [2]
      • ensures users can enjoy consistently fast performance without throttling [2]
        • only a portion of the CU usage for an operation applies to any individual timepoint, which reduces throttling overall [2]
        •  smoothed CU usage accumulates as operations run [2]
        • future capacity (CUs available in future timepoints) pays for smoothed usage [2]
          • because the capacity runs continuously [2]
    • bursting and smoothing work together
      • let capacities self-manage and self-heal when temporary spikes in usage would otherwise cause other systems to fail or slow down [2]
      • aren't supported when Autoscale Billing for Spark is enabled [2]
        • Spark usage operates in a PAYG mode [2]
    • {benefit} workload stabilization
      • helps capacities self-stabilize by flattening loads with large spikes into a smooth load profile, eliminating temporal spikes [3]
      • pays back that additional compute consumption when the capacity is less in demand 
        • prevents overloads as the smoothing mechanism will always flatten the resource burst [3]  
    • {benefit} scheduling contention elimination
      • large/scheduled jobs are smoothed over 24 hours [5]
        • eliminates the need to decide the timing and order of job execution [5]
        • operations are always executed for maximum performance [5]
    • {benefit} bad actor protection
      • prevents a single actor (user) with a very demanding query from hogging the entire capacity [5]
        • interactive operations are smoothed over several minutes [5]
  • {mechanism} throttling 
    • {def} protection mechanism that activates when a capacity resource consumption exceeds the capacity threshold for the current time window [5]
      • there can be a short delay before throttling begins [6]
      • running too many operations can overload a capacity [2]
        • the capacity automatically throttles new operations when it's overloaded [2]
      • happens in progressive steps [2]
        • helps to minimize the impact on important tasks like data refreshes [2]
      • not applied automatically when a capacity is operating above 100% utilization [2]
        •  see overage protection
      • an indicator that the capacity is experiencing sustained pressure [5]
        • while one capacity, or set of workspaces, might experience reduced performance from being overloaded, other capacities might continue running normally [2]
      • too much throttling can result in a degraded end-user experience [2]
      • applied at the capacity level [2]
    • starts when a capacity uses up all its CU resources for the next 10 minutes, when overage protection ends [2]
      •  
      • {phase} applies 20-second delays to new interactive operations [2]
      • {phase} rejects new interactive operations when a capacity uses up all its CU resources for the next one hour [2]
        • background operations can start and run [2]
      • {phase} rejects all new requests, interactive and background, when the capacity uses up all its available CU resources for the next 24 hours [2]
        •  the capacity continues to throttle requests until you pay off the consumed CUs [2]
      •  its enforcement continues until unused capacity pays off all carryforward CUs [2]
    • consistently high throttling levels indicate the need to either
      • load balance across multiple capacities [2]
      • increase the capacity SKU size [2]
    • {concept} throttling policies
      • {def} rules Fabric uses to slow down or reject workload execution when a capacity is overloaded
      • prevent a capacity from becoming unusable when its smoothed consumption exceeds the available Capacity Units (CUs).
      • refined to deliver multiple benefits [3]
    • reduced throttling for capacities that only experience occasional spikes [3]
    • [monitoring] Fabric capacity metrics app cane be used to confirm throttling
      • via Compute page >> System events table 
        • shows the history of t4hrottling events [2]
      • via Compute page >> Throttling charts 
        • show when smoothed usage exceeds one of the throttling limits [2]
  • {feature} capacity overage (aka overage protection)
    • {def} feature that allows a capacity to continue running when it would otherwise be throttled because the purchased CU where exceeded
  • {feature} surge protection
    • {def} mechanism that helps prevent a Fabric capacity from becoming overloaded by proactively limiting background workloads before the capacity reaches severe throttling
      • interactive operations smoothed over several minutes, preventing a single user with a very demanding query from hogging the entire capacity [3]
  • {capacity size} F0
    • a zero-provisioned capacity SKU 
      • announced at FabCon Europe Sep, 2026
    • {characteristic} billed on demand
      • no upfront compute charge
      • accommodates one billing category workloads
        • e.g. Data Warehouse
      • designed to work with on-demand billing 
        • rather than requiring customers to reserve compute capacity upfront
    • {characteristic} built-in monitoring
      • via Capacity Metrics >> 'On-demand compute' tab⠀
    • {characteristic} supports no smoothing
      • priced as a multiplier of PAYG
        • prices yet to be disclosed 
    • {advantage} start small, scale on demand
    • {disadvantage} steep learning curve
      • especially when no trial capacity is available
    • {scenario} development & testing with minimal workloads
    • {scenario} small proof-of-concepts
    • {scenario} sporadic workloads
    • {scenario} organizations wanting consumption-based billing
  • {capacity size} F2
    • entry-level capacity with access to standard Fabric features
    • best suited for lightweight, low‑volume, or exploratory workloads that don't require parallelism 
      •  useful to prove that solutions work
      • {scenario} proof-of-concepts
      • {scenario} individual usage
      • {scenario} training & testing with minimal workloads
    • can occasionally execute workloads larger than its nominal size
      •  e.g. small notebooks, warehouse queries may complete successfully 
    • {limitation} many workloads require at least F4 
      • e.g. Spark notebooks, Dataflows, Power BI refreshes), while bursting and smoothing provides less benefit; 
    • {limitation} not viable for production environments
      • very limited compute power
      • throttling occurs often 
      • poor fit for concurrent access 
      • limited refresh & ETL throughput 
  • {capacity size} F4
    • first usable production‑grade capacity for light to moderate workloads
      • ideal for PoCs, small production workloads, notebooks, though workloads may run soon into its limitations
    • allows to run concurrent activities
      • e.g. combined BI & Data Engineering 
    • includes all standard F-SKU features (Copilot, Managed Private Endpoints, etc.)
    • can support 
      • Power BI reports
      • small lakehouses
      • Data Factory pipeliness 
  • {capacity size} F8
    • generally considered strong enough for multiple developers, analysts, and BI users working at the same time
    • often support dozens to hundreds of actively used reports 
    • provides a more realistic environment for testing
      • scheduled refreshes 
      • concurrent users
      • automated pipelines
      • engineering workloads
  • {capacity size} F16
    • works well if workloads are light
    • often support dozens to hundreds of actively used reports
    • good for
      • smaller lakehouses
      • scheduled pipelines
      • moderate notebook execultion 
  • {capacity size} F32
    • suitable for mid-sized workloads, though it may be still insufficient for heavy workloads
    • often support hundreds to thousands of actively used reports.
    • good for
      • departmental BI
      • smaller semantic model
      • limited refresh schedules
    • best for
      • departmental Fabric deployments
      • medium-sized organizations
      • initial production rollouts
      • single-team ownership
  • {capacity size} F64
    • first high‑performance, enterprise‑grade Fabric capacities
      • it's designed for advanced workloads, multiple teams, and high‑throughput data operations
    • with it the licensing model for Power BI changes
      •  equivalent to a Power BI Premium-sized capacity
        •  ⇒ free Power BI user consumption 
          •  ⇐ a license is still needed for development
    • often support hundreds to thousands of actively used reports
    • often where organizations begin running
      • larger Spark workloads
      • multiple engineering team
      • more complex medallion architectures
      • heavy ingestion schedule
    • often the capacity where organizations start implementing
      • workspace isolation strategies
      • capacity governance
      • chargeback/showback
      • surge protection policies
      • dedicated engineering and BI workloads
  • {capacity size} F128+ 
    • ideal for scenarios in which multiple business units share a capacity
    • provides more flexibility in workload scheduling
    • can support very large enterprise estates with thousands of reports
    • governance becomes more important than performance 
  • {type} trial capacity
    • automatically created when a user begins to work with Fabric items in the Fabric portal
    • users are prompted to start the trial capacity [4] 
    • users using a non-Fabric-enabled workspace and who are trying to create Fabric-specific item for the first time, are automatically upgraded to a trial license for MF [4]
    • has a limitation of five trial capacities per tenant [4]
    • users can opt out between a F4 and F64 capacity
    • doesn't have access to all features
      • e.g. Copilot requires at least a F2 capacity
    • valid for 60 days from activation
      •  
  • {feature} Microsoft Fabric Capacity Metrics app 
    • allows monitoring the CUs usage to better understand how many CUs are required for operating your solution and whether you might want to consider scaling up or scaling out your Fabric SKU(s)

Previous Post <<||>> Next Post 

References:
[1] Microsoft Learn (2025) Manage your capacities in the Admin portal [link]
[2] Microsoft Learn (2026) Understand the Fabric capacity throttling policy [link]
[3] Benni De Jagere (202x) Understanding Fabric Capacities
[4] Brian Bønk & Valerie Junk (2026) Microsoft Fabric Analytics Engineer Associate Study Guide: Hands-On Practice and Expert Tips for Acing the DP600 Certification Exam
[5] Microsoft Learn (2026) Microsoft Fabric: Advanced Real-Time Intelligence
[6] Capacity overage in Microsoft Fabric [link]

Acronyms:
BI - Business Intellgience
CI/CD - Continuous Integration and Continuous Deployment
CU - Capacity Unit
IDE - Integrated Development Environments
MF - Microsoft Fabric 
PAYG - Pay-As-You-Go
SKU - Stock Keeping Unit 

12 October 2026

💎🏭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]

17 August 2026

🏭𝐌𝐢𝐜𝐫𝐨𝐬𝐨𝐟𝐭 𝐅𝐚𝐛𝐫𝐢𝐜: 𝐃𝐚𝐭𝐚 𝐖𝐚𝐫𝐞𝐡𝐨𝐮𝐬𝐞 (𝐓𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐎𝐯𝐞𝐫𝐯𝐢𝐞𝐰)

 Introduction (Some Background)

Typically, PowerPoint slides and similar content must be broken in small pieces and structured in such a way that the audience can digest the information presented. Thus, a presentation ends up spreading over multiple slides that must be structured in such a way that it facilitates also digestion, retention, and whatever further aspects are targeted. Conversely, on social networks the average author/publisher has only a small chance of capturing an audience's attention, given that the competition for readers' attention increases exponentially in the long sequence of posts. So, what information would we show for this purpose? 

When I want to learn something, typically I need a few pieces of information/knowledge that would allow me to anchor and integrate the text into existing knowledge. It usually starts with high-level definitions of the main concepts that reflect the various aspects that would help me associate and differentiate the respective concepts from similar concepts or knowledge. The volume of such information depends on many aspects - the complexity of the concepts and the contexts they belong to, how many similar concepts or metaphors are available, the gaps available, analogies and differentiators, etc.

A high-level list of the main capabilities for the respective concept(s) would further allow a (wider) surface to anchor the various information of interest. No matter how much text can be included, we shouldn't forget that a (well-chosen) picture is (often) worth a thousand words. Therefore, a well-chosen image that depicts a high-level representation of the concepts/ideas presented, the architecture or even a metaphor can have a considerable impact on the readers.  

Ideally the image should serve as a sketch or sample of the finished product. Then, even if from the used representation one can guess what are the ingredients used, the difference between a good dish and a fiasco often resides in details. Like in preparing a dish, one needs to know what ingredients are needed and how they must be prepared and used together, for the maximum effect. An experienced cook needs at least the list of ingredients and some general information on how the dish differentiates from other dishes. The more inexperienced the cook, the more information needs to be provided and somewhere a line must be drawn, otherwise a simple recipe becomes the cookbook itself. Therefore, like in any cookbook, I must assume that some basic knowledge is available!

So, as part of the learning/teaching process, I started a series of slides on Microsoft Fabric that should help me narrow down the recipes for delivering great products. The information comes from training material, various presentations delivered by Microsoft or third-parties, the various books and other technical material I read over the years. 

Let's start with the basics - the (data) warehouse!

   📣Technical Overview 

    

Conclusion

The slide barely scratches the surface, especially if we consider it from a broader context - what the average professional must know. Even if the gap between the presented information and a business case, for example, or anything similar capable of convincing the audience is considerable, the slide should serve as a starting point, if not a foundation on which something durable can be built. 

Enjoy the ride and feel free to like it, download it, respectively share it further!

06 October 2025

🏭🗒️Microsoft Fabric: Git [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-Oct-2025

[Microsoft Fabric] Git

  • {def} an open source, distributed version control platform
    • enables developers commit their work to a local repository and then sync their copy of the repository with the copy on the server [1]
    • to be differentiated from centralized version control 
      • where clients must synchronize code with a server before creating new versions of code [1]
    • provides tools for isolating changes and later merging them back together
  • {benefit} simultaneous development
    • everyone has their own local copy of code and works simultaneously on their own branches
      •  Git works offline since almost every operation is local
  • {benefit} faster release
    • branches allow for flexible and simultaneous development
  • {benefit} built-in integration
    • integrates into most tools and products
      •  every major IDE has built-in Git support
        • this integration simplifies the day-to-day workflow
  • {benefit} strong community support
    • the volume of community support makes it easy to get help when needed
  • {benefit} works with any team
    • using Git with a source code management tool increases a team's productivity 
      • by encouraging collaboration, enforcing policies, automating processes, and improving visibility and traceability of work
    • the team can either
      • settle on individual tools for version control, work item tracking, and continuous integration and deployment
      • choose a solution that supports all of these tasks in one place
        • e.g. GitHub, Azure DevOps
  • {benefit} pull requests
    • used to discuss code changes with the team before merging them into the main branch
    • allows to ensure code quality and increase knowledge across team
    • platforms like GitHub and Azure DevOps offer a rich pull request experience
  • {benefit} branch policies
    • protect important branches by preventing direct pushes, requiring reviewers, and ensuring clean build
      •  used to ensure that pull requests meet requirements before completion
    •  teams can configure their solution to enforce consistent workflows and process across the team
  • {feature} continuous integration
  • {feature} continuous deployment
  • {feature} automated testing
  • {feature} work item tracking
  • {feature} metrics
  • {feature} reporting 
  • {operation} commit
    • snapshot of all files at a point in time [1]
      •  every time work is saved, Git creates a commit [1]
      •  identified by a unique cryptographic hash of the committed content [1]
      •  everything is hashed
      •  it's impossible to make changes, lose information, or corrupt files without Git detecting it [1]
    •  create links to other commits, forming a graph of the development history [2A]
    • {operation} revert code to a previous commit [1]
    • {operation} inspect how files changed from one commit to the next [1]
    • {operation} review information e.g. where and when changes were made [1]
  • {operation} branch
    •  lightweight pointers to work in progress
    •  each developer saves changes to their own local code repository
      • there can be many different changes based on the same commit
        •  branches manage this separation
      • once work created in a branch is finished, it can be merged back into the team's main (or trunk) branch
    • main branch
      • contains stable, high-quality code from which programmers release
    • feature branches 
      • contain work in progress, which are merged into the main branch upon completion
      •  allows to isolate development work and minimize conflicts among multiple developers [2]
    •  release branch
      •  by separating the release branch from development in progress, it's easier to manage stable code and ship updates more quickly
  • if a file hasn't changed from one commit to the next, Git uses the previously stored file [1]
  • files are in one of three states
    • {state}modified
      • when a file is first modified, the changes exist only in the working directory
        •  they aren't yet part of a commit or the development history
      •  the developer must stage the changed files to be included in the commit
      •  the staging area contains all changes to include in the next commit
    •  {state}committed
      •  once the developer is happy with the staged files, the files are packaged as a commit with a message describing what changed
        •  this commit becomes part of the development history
    •  {state}staged
      •  staging lets developers pick which file changes to save in a commit to break down large changes into a series of smaller commits
        •   by reducing the scope of commits, it's easier to review the commit history to 
  • {best practice} set up a shared Git repository and CI/CD pipelines [2]
    • enables effective collaboration and deployment in PBIP [2]
    • enables implementing version control in PBIP [2]
      • it’s essential for managing project history and collaboration [2]
      • allows to track changes throughout the model lifecycle [2]
      • allows to enable effective governance and collaboratin
    •  provides robust version tracking and collaboration features, ensuring traceability
  • {best practice} use descriptive commit messages [2]
    • allows to ensure clarity and facilitate collaboration in version control [2]
  • {best practice} avoid sharing Git credentials [2]
    • compromises security and accountability [2]
      •  can lead to potential breaches [2]
  • {best practice} define a naming conventions for files and communicated accordingly [2]
  • {best practice} avoid merging changes directly into the master branch [2]
    • {risk} this can lead to integration issues [2]
  • {best practice} use git merge for integrating changes from one branch to another [2]
    • {benefit} ensures seamless collaboration [2]
  • {best practice} avoid skipping merges [2]
    • failing to merge regularly can lead to complex conflicts and integration challenges [2]
Previous Post <<||>> Next Post 

References:
[1] Microsoft Learn (2022) DeveOps: What is Git? [link]
[2] M Anand, Microsoft Fabric Analytics Engineer Associate: Implementing Analytics Solutions Using Microsoft Fabric (DP-600), 2025 

Acronyms:
PBIP - Power BI Project
CI/CD - Continuous Integration and Continuous Deployment
IDE - Integrated Development Environments
 

05 July 2025

🏭🗒️Microsoft Fabric: Git Repository [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: 4-Jul-2025

[Microsoft Fabric] Git Repository

  • {def} set of features that enable developers to integrate their development processes, tools, and best practices straight into the Fabric platform [2]

  • {goal} the repo serves as single-source-of-truth
  • {feature} backup and version control [2]
  • {feature} revert to previous stages [2]
  • {feature} collaborate with others or work alone using Git branches [2]
  • {feature} source control 
    • provides tools to manage Fabric items [2]
    • supported for Azure DevOps and GitHub [3]
  • {configuration} tenant switches 
    • ⇐ must be enabled from the Admin portal 
      • by the tenant admin, capacity admin, or workspace admin
        • dependent on organization's settings [3]
    • users can create Fabric items
    • users can synchronize workspace items with their Git repositories
    • create workspaces
      • only if is needed to branch out to a new workspace [3]
    • users can synchronize workspace items with GitHub repositories
      • for GitHub users only [3]
  • {concept} release process 
    • begins once new updates complete a Pull Request process and merge into the team’s shared branch [3]
  • {concept} branch
    • {operation} switch branches
      • the workspace syncs with the new branch and all items in the workspace are overridden [3]
        • if there are different versions of the same item in each branch, the item is replaced [3]
        • if an item is in the old branch, but not the new one, it gets deleted [3]
      • one can't switch branches if there are any uncommitted changes in the workspace [3]
    • {action} branch out to another workspace 
      • creates a new workspace, or switches to an existing workspace based on the last commit to the current workspace, and then connects to the target workspace and branch [4]
      • {permission} contributor and above
    • {action} checkout new branch )
      • creates a new branch based on the last synced commit in the workspace [4]
      • changes the Git connection in the current workspace [4]
      • doesn't change the workspace content [4]
      • {permission} workspace admin
    • {action} switch branch
      • syncs the workspace with another new or existing branch and overrides all items in the workspace with the content of the selected branch [4]
      • {permission} workspace admin
    • {limitation} maximum length of branch name: 244 characters.
    • {limitation} maximum length of full path for file names: 250 characters
    • {limitation} maximum file size: 25 MB
  • {operation} connect a workspace to a Git Repos 
    • can be done only by a workspace admin [4]
      • once connected, anyone with permissions can work in the workspace [4]
    • synchronizes the content between the two (aka initial sync)
      • {scenario} either of the two is empty while the other has content
        • the content is copied from the nonempty location to the empty on [4]
      • {scenario}both have content
        • one must decide which direction the sync should go [4]
          • overwrite the content from the destination [4]
      • includes folder structures [4]
        • workspace items in folders are exported to folders with the same name in the Git repo [4]
        • items in Git folders are imported to folders with the same name in the workspace [4]
        • if the workspace has folders and the connected Git folder doesn't yet have subfolders, they're considered to be different [4]
          • leads to uncommitted changes status in the source control panel [4]
            • one must to commit the changes to Git before updating the workspace [4]
              • update first, the Git folder structure overwrites the workspace folder structure [4]
        • {limitation} empty folders aren't copied to Git
          • when creating or moving items to a folder, the folder is created in Git [4]
        • {limitation} empty folders in Git are deleted automatically [4]
        • {limitation} empty folders in the workspace aren't deleted automatically even if all items are moved to different folders [4]
        • {limitation} folder structure is retained up to 10 levels deep [4]
        • {limitation} the folder structure is maintained up to 10 levels deep
    •  Git status
      • synced 
        • the item is the same in the workspace and Git branch [4]
      •  conflict 
        • the item was changed in both the workspace and Git branch [4]
      •  unsupported item
      •  uncommitted changes in the workspace
      •  update required from Git [4]
      •  item is identical in both places but needs to be updated to the last commit [4]
  • source control panel
    • shows the number of items that are different in the workspace and Git branch
      • when changes are made, the number is updated
      • when the workspace is synced with the Git branch, the Source control icon displays a 0
  • commit and update panel 
    • {section} changes 
      • shows the number of items that were changed in the workspace and need to be committed to Git [4]
      • changed workspace items are listed in the Changes section
        • when there's more than one changed item, one can select which items to commit to the Git branch [4]
      • if there were updates made to the Git branch, commits are disabled until you update your workspace [4]
    • {section} updates 
      • shows the number of items that were modified in the Git branch and need to be updated to the workspace [4]
      • the Update command always updates the entire branch and syncs to the most recent commit [4]
        • {limitation} one can’t select specific items to update [4]
        • if changes were made in the workspace and in the Git branch on the same item, updates are disabled until the conflict is resolved [4]
    • in each section, the changed items are listed with an icon indicating the status
      •  new
      •  modified
      •  deleted
      •  conflict
      •  same-changes
  • {concept} related workspace
    • workspace with the same connection properties as the current branch [4]
      • e.g.  the same organization, project, repository, and git folder [4] 
[2] Microsoft Learn (2025) Fabric: What is Microsoft Fabric Git integration? [link] 
What is lifecycle management in Microsoft Fabric? [link]
[3] Microsoft Fabric Updates Blog (2025) Fabric: Introducing New Branching Capabilities in Fabric Git Integration [link] 
[4] Microsoft Learn (2025) Fabric: Basic concepts in Git integration [link]
[5]  [link]

Resources:
[R1] Microsoft Learn (2025) Fabric: 

Acronyms:
CI/CD - Continuous Integration and Continuous Deployment

21 June 2025

🏭🗒️Microsoft Fabric: Result Set Caching in SQL Analytics Endpoints [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: 21-Jun-2025

[Microsoft Fabric] Result Set Caching in SQL Analytics Endpoints

  • {def} built-in performance optimization for Warehouse and Lakehouse that improves read latency [1]
    • fully transparent to the user [3]
    • persists the final result sets for applicable SELECT T-SQL queries
      • caches all the data accessed by a query [3]
      • subsequent runs that "hit" cache will process just the final result set
        • can bypass complex compilation and data processing of the original query[1]
          • ⇐ returns subsequent queries faster [1]
      • the cache creation and reuse is applied opportunistically for queries
    • works on
      • warehouse tables
      • shortcuts to OneLake sources
      • shortcuts to non-Azure sources
    • the management of cache is handled automatically [1]
      • regularly evicts cache as needed
    • as data changes, result consistency is ensured by invalidating cache created earlier [1]
  • {operation} enable setting
    • via ALTER DATABASE <database_name> SET RESULT_SET_CACHING ON
  • {operation} validate setting
    • via SELECT name, is_result_set_caching_on FROM sys.databases
  • {operation} configure setting
    • configurable at item level
      • once enabled, it can then be disabled 
        • at the item level
        • for individual queries
          • e.g. debugging or A/B testing a query
        • via OPTION ( USE HINT ('DISABLE_RESULT_SET_CACHE') 
    • {default} during the preview, result set caching is off for all items [1]
  • [monitoring] 
    • via Message Output
      • applicable to Fabric Query editor, SSMS
      • the statement "Result set cache was used" is displayed after query execution if the query was able to use an existing result set cache
    • via queryinsights.exec_requests_history system view
      • result_cache_hit displays indicates result set cache usage for each query execution [1]
        • {value} 2: the query used result set cache (cache hit)
        • {value} 1: the query created result set cache
        • {value} 0: the query wasn't applicable for result set cache creation or usage [1]
          • {reason} the cache no longer exists
          • {reason} the cache was invalidated by a data change, disqualifying it for reuse [1]
          • {reason} query isn't deterministic
            • isn't eligible for cache creation [1]
          • {reason} query isn't a SELECT statement
  • [warehousing] 
    • {scenario} analytical queries that process large amounts of data to produce a relatively small result [1]
    • {scenario} workloads that trigger the same analytical queries repeatedly [1]
      • the same heavy computation can be triggered multiple times, even though the final result remains the same [1]

References:
[1] Microsoft Learn (2025) Result set caching (preview) [link]
[2] Microsoft Fabric Update Blog (2025) Result Set Caching for Microsoft Fabric Data Warehouse (Preview) [link|aka]
[3] Microsoft Learn (2025) In-memory and disk caching [link]
[4] Microsoft Learn (2025) Performance guidelines in Fabric Data Warehouse [link] 

Resources:
[R1] Microsoft Fabric (2025) Fabric Update - June 2025 [link]

Acronyms:
MF - Microsoft Fabric
SSMS - SQL Server Management Studio

24 May 2025

🏭🗒️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]

    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
    Related Posts Plugin for WordPress, Blogger...

    About Me

    My photo
    Koeln, NRW, Germany
    IT Professional with more than 25 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.