Crawl, Walk, Run with Agentic Development of Power BI Assets

, , ,

If you’ve been watching AI roll through the data community and thinking, “this seems useful, but I have no idea where to start,” this post is for you. If you have ethical objections to using AI, follow your convictions — this post isn’t here to convince you otherwise. My position these days is that AI use is inevitable in this industry, so I want to understand how to use it carefully and effectively while advocating for mitigation to the societal and environment damage that it causes. So if you’re curious, read on. Including AI in your Power BI development process doesn’t require a dramatic leap. It’s a progression. Start small, build confidence, add more capability when you’re ready.

I use examples from both Claude Code and Github Copilot in this post. I currently prefer Claude and Claude Code as my AI harness, but everything described here applies broadly to other generative AI tools. The principles are the same; only the menus change.

Before diving in: make sure you understand your organization’s policies around AI tool use. Even pasting a DAX measure or a data model description into a chat interface is sharing potentially sensitive business logic with an external service, and the concerns only grow as AI tools gain more direct access to your files and environment. The European Data Protection Board’s AI Privacy Risks and Mitigations in LLMs is a vendor-neutral reference for understanding what’s at stake, and worth a read before having that conversation with your security or compliance team.

Let’s walk through five stages, from a simple chat window to an AI-assisted workflow.

Note: Both AI tooling and Power BI capabilities evolve quickly. The specifics here are accurate as of June 2026, but some details may have changed by the time you’re reading this.

🐛 Crawl: Ask Questions in an AI Chat Interface

You open Claude, ChatGPT, or your preferred AI chat. You ask it a question. You use the answer.

This is the easiest place to start, and it can be genuinely useful. You’re not giving the AI access to your files or your environment; you’re just asking it things.

Some examples that may work well at this stage:

  • “How do I write a DAX measure that calculates rolling 12-month sales?”
  • “How do I build a small multiples chart in Power BI?”
  • “Why is my RANKX measure returning the same value for all rows?”
  • “Create a Power BI theme file that uses these hex colors: …”

You paste the DAX into Power BI Desktop yourself or follow the steps the AI outlines. The AI never touches your files; it’s just a resource you can query.

A screenshot of a Claude conversation titled "Building small multiples charts in Power BI." The user asked "How do I build a small multiples chart in Power BI?" and Claude responded with three sections: Enable Small Multiples (3 numbered steps covering adding a supported visual, dragging a field to the Small multiples well, and the automatic tiling result), Format the Grid (bullet points covering Layout, Border, Title, and Background options in the Format pane), and Tips (beginning with a note about shared vs. independent axes). The interface shows Sonnet 4.6 on Low usage at the bottom.
Using Claude chat in a browser window to answer a Power BI question

Always validate the output. AI is often wrong, particularly when it lacks context about your specific setup. It’s also non-deterministic: ask the same question twice and you may get a different answer. Test DAX measures against known values, verify steps actually exist in the UI, and don’t assume a confident-sounding answer is a correct one. Accuracy also varies with how you use the chat. Short, focused questions with fresh context tend to produce more reliable answers than long, winding conversations where the AI may lose track of earlier details or carry forward incorrect assumptions.

It’s worth starting here because it builds your intuition for how to prompt AI effectively. Learning to give clear context (“I have a fact table with a DateKey column and a Calendar table with a Date column joined on…”) makes a real difference in answer quality, and that skill pays off as you add more capabilities.

You’re ready for the next stage when you find yourself copy-pasting a lot, or wishing the AI could just see what you’re working with.

🚶 Walk: Bring AI Into Your Code Editor

You set up Visual Studio Code with an AI extension, open your Power BI project in PBIP format, and let the AI suggest or make changes, with your approval before anything is applied.

A screenshot of a dark-themed VS Code interface showing Claude Code running in a workspace. The left sidebar displays a Power BI project file structure (.pbip and .tmdl files). The main Claude Code pane shows a terminal-like conversation history where Claude has executed Grep and PowerShell tasks to update a semantic model table and is explaining the column logic for a data quality flag field
Using Claude Code in Visual Studio Code to update a semantic model

This is where things get a lot more powerful. A few important things to know:

You Need PBIP Format

Power BI Desktop’s default .pbix format is a binary file, so AI tools can’t read or modify it meaningfully. The PBIP (Power BI Project) format saves your report and semantic model as human-readable text files (TMDL for the model, PBIR-format JSON for the report). This is what makes AI-assisted editing possible.

To enable it: in Power BI Desktop, go to File > Options and settings > Options > Preview features and turn on Power BI Project (.pbip) save format.

AI Suggests, You Approve

In VS Code with an AI extension like GitHub Copilot or Claude Code, the AI proposes changes that you can review before accepting them. Nothing changes without your approval. Think of it this way: AI as a very fast first draft, you as the editor with final say.

This is also where planning mode is useful. Before writing a single line, you can ask: “I need to add a chart to the Sales Overview page showing which products have the highest gross margin this quarter.” The AI will lay out a plan; you can push back or refine it, then execute when you’re ready.

Tip: Give AI References

AI works much better when it has context. A few things worth providing:

  • Point the AI to your existing TMDL files so it can read your naming conventions, relationships, and calculation patterns directly from the model.
  • Link to the TMDL or PBIR schema documentation. Microsoft Learn has docs on TMDL and the PBIR report folder format, and pointing AI to the schema encourages it to avoid hallucinating property names.
  • Share a snippet from an existing report in PBIR format so the AI learns how you’ve structured visuals, pages, and filters.

The more context you give, the fewer iterations you need to achieve useful output. You’re ready for the next stage when you want the AI to do things autonomously — query documentation, check schemas, look things up — without you having to manually feed it everything.

🏃 Run: Add Skills and Connect to MCP Servers

You install skills and connect your AI harness to MCP (Model Context Protocol) servers, giving it purpose-built capabilities and the ability to take actions on your behalf.

Skills are packaged capabilities that teach the AI how to perform specific tasks. Your AI harness likely comes with some built-in skills, but they likely are not specific to Power BI. MCP servers connect your AI harness to external tools and live data. Together, skills and MCP servers let the AI do significantly more than suggest text in an editor.

Microsoft publishes Power BI-specific options for both. The report-focused skills — Power BI Report Authoring and Power BI Report Design — ship together in the Power BI authoring plugin from Microsoft’s Skills for Fabric catalog. For semantic model work, the Power BI MCP server connects the AI directly to your model.

Installing Skills and Connecting MCP Servers in VS Code

For GitHub Copilot CLI, skills are installed as plugins from a marketplace. To get the Power BI skills, first register the Microsoft Fabric marketplace, then install the authoring bundle:

/plugin marketplace add microsoft/skills-for-fabric
/plugin install powerbi-authoring@fabric-collection

Once installed, those skills are active in Copilot conversations in your project. No further configuration needed to start using them.

For MCP servers, VS Code with Copilot connects to them through a .mcp.json file in your workspace. The Power BI local MCP server runs on your machine and works with a running instance of Power BI Desktop, or with your PBIP files directly. The Power BI authoring plugin automatically registers the Power BI modeling MCP server on your machine.

If you don’t like the Microsoft-provided plugins (they are fairly new and still in preview), there are others available in the community.

Once skills and MCP servers are in place, the AI knows how to work with Power BI assets and has the tools to act. You spend less time explaining the territory and more time reviewing what it did.

The key difference from the previous stage: the AI has agency within the boundaries you set. It’s not just suggesting text; it’s taking steps in a workflow. That’s a lot more capability, and it’s worth being deliberate about what you let it do on its own.

Use Source Control

If you’re using PBIP format, your project files are plain text and work naturally with Git. This becomes especially valuable at this stage: you can have the AI commit its changes after each meaningful step, giving you a clean history you can roll back to if something goes wrong. Treating AI-assisted changes like any other code change — committed, reviewable, reversible — is one of the better habits you can build at this stage. If you struggle to understand Git, you can ask your AI harness to help you.

⚙ Customize: Add Your Own Instruction Files

You create markdown files that give your AI persistent, project-specific context about how you work: your conventions, your preferences, your patterns.

Most AI coding tools support some form of instruction file. In Claude Code, it’s CLAUDE.md. In GitHub Copilot, it’s custom instructions in settings. The format varies; the concept is the same.

For Power BI work, your instruction file might include things like:

  • Your DAX formatting preferences (line breaks, indentation, variable naming)
  • Your measure organization conventions (measure tables, display folders)
  • Your model naming standards (for example, human-readable table and column names over technical prefixes or source system names)
  • Design rules for your reports (color palette, font choices, no pie charts)
  • How your date table is structured and what the key columns are called
  • Links to your organization’s style guide or data dictionary

A good instruction file means fewer corrections and more outputs you can actually use.

Rather than putting everything into a single file, consider a lean primary instruction file that describes the project and references separate, focused files: one for DAX conventions, one for report design standards, one documenting your date table, and so on. In Claude Code specifically, this also takes advantage of how context is loaded: referenced files are only pulled in when relevant to the task at hand, rather than everything being loaded upfront. A single bloated instruction file consumes context window space on every interaction whether or not that content is needed. In GitHub Copilot, file inclusion is controlled by applyTo glob patterns in each instruction file, so you get similar control by scoping each file to the relevant context. The CLAUDE.md file can be created and edited manually or by the AI. Once it’s in your project root, the AI picks it up automatically on every interaction with that project.

⭐ Bonus: Turn Instructions Into Skills

You package your instruction content into a reusable skill that you can invoke across any project, not just the one where you wrote the file.

Skills are bundles of instructions that activate a specific capability or set of behaviors. Rather than copying your instruction file into every new project, you install the skill once and it’s available everywhere.

Why Skills Beat Generic MCP Servers for Personal Preferences

External MCP servers give your AI tools and data the ability to query an API, read a file, and fetch documentation. What they can’t give it is your preferences. An MCP server doesn’t know that you always put measures in a dedicated table, or that you prefer your cards to have a specific light gray background color, or that you never use the default blue theme.

Skills fill that gap. They capture the preferences and conventions that make your work yours.

A Real Example: Power BI Report Design Skill

I built a Power BI Report Design skill that I use across my projects. It contains:

  • Design guidelines: My preferred color palettes, typography choices, visual spacing rules, when to use which chart type, accessibility considerations
  • HTML mockup instructions: Detailed guidance for generating high-fidelity HTML mockups that closely reflect what’s actually achievable in Power BI Desktop, not idealized designs that look great in a browser but can’t be replicated in the tool

That last point matters more than it might seem. When you ask AI to design a report, it can easily generate beautiful HTML that would be impossible to build in Power BI Desktop: a fully custom tooltip on hover, pixel-perfect custom fonts, complex CSS animations. My skill steers the AI away from those dead ends and toward designs that translate cleanly into real Power BI visuals, formatting options, and layout constraints.

The skill also generates a Power BI theme file from the mockup, so the colors and typography translate directly into Desktop without manual configuration.

The result: mockups that set accurate expectations for stakeholders, designs I can actually build, and a theme file ready to apply. I drafted the original instruction files in Claude Desktop, had it package them into a skill, and installed it. Now I just invoke the skill to generate the HTML mockup and theme file.

🧱 Putting It Together

You don’t have to climb all five rungs at once. For many Power BI developers, the chat interface delivers real value without the file access risks that come with deeper integration. It’s not a compromise; it’s a real starting point. Master the first layer, and layer on the next when you find yourself bumping into its ceiling (and when you understand the risks and rewards of each step).

StageWhat You NeedWhat You Gain
Chat interfaceA browser or desktop application (Claude)On-demand expertise, no setup
VS Code + AI extensionVS Code, PBIP format, AI extensionAI sees your code, proposes diffs
Skills + MCP serversGitHub Copilot CLI, Claude Code, or similar harnessAI takes actions, queries live context
Instruction filesA markdown file in your projectAI learns your conventions
SkillsA skill file installed in your AI harnessReusable preferences across all projects

At every stage, you decide how much autonomy the AI has and what it has access to. Start where you’re comfortable and dial up the automation only as your trust and understanding of the tradeoffs grow.

The post Crawl, Walk, Run with Agentic Development of Power BI Assets first appeared on Data Savvy.

/by

T-SQL Tuesday 198: The Hidden ETL Mistake That Silently Wastes Time And Compute

, ,
Read more
/by

Programmatically Retrieving MLV Lineage and Refresh Times

, , , , ,

Materialized lake views (MLVs) in Microsoft Fabric are an effective way to implement medallion architecture declaratively, but once you have a pipeline of MLVs in production, you need visibility into whether they’re current. Fabric’s MLV management area gives you a visual lineage and refresh history, but if you want to build automated alerting, logging, or custom tooling, you need to get that information programmatically. This post walks through one way to do that, using a small demo lakehouse built entirely in a Fabric notebook.

Getting lineage from table properties

When you create a materialized lake view, Fabric automatically populates a table property called fabric.source.entities that describes every immediate upstream source. You can retrieve it with SHOW TBLPROPERTIES and parse the JSON payload:

props = spark.sql(f"SHOW TBLPROPERTIES dbo.mlvc").collect()
source_entities_raw = next(
    (row["value"] for row in props if row["key"] == "fabric.source.entities"),
    None
)
sources = [e["tableName"] for e in json.loads(source_entities_raw)]

The payload includes workspace, artifact, and schema context for each source, which means it can support cross-lakehouse lineage scenarios as that capability matures. Combining this across all MLVs in a schema, using SHOW MATERIALIZED LAKE VIEWS IN dbo, gives you the full dependency graph without any hardcoding.

Getting last modified time from source tables

If you have Change Data Feed enabled on your base Delta tables (which you should for optimal MLV refresh anyway) you can retrieve the timestamp of the last committed operation from the Delta transaction log: 

history = spark.sql(f"DESCRIBE HISTORY dbo.table1 LIMIT 1").collect()
last_modified = history[0]["timestamp"]

DESCRIBE HISTORY returns the full transaction log in reverse chronological order. LIMIT 1 gives you the most recent entry, which corresponds to the last time data was written to the table. This is more reliable than file system metadata because it reflects committed Delta transactions, not just file touches.

Getting last refresh time from an MLV

MLVs are persisted as Delta tables under the hood, so the same DESCRIBE HISTORY approach works for them too:

history = spark.sql(f"DESCRIBE HISTORY dbo.mlva LIMIT 1").collect()
last_refresh = history[0]["timestamp"]

This gives you the timestamp of the last time the MLV was successfully refreshed, whether that was triggered by a schedule, a notebook, or manually from the portal.

Detecting staleness

With timestamps for both sources and MLVs in hand, you can compare them to determine whether any MLV is out of date. An MLV is stale if any of its upstream sources have a newer timestamp than the MLV itself. Staleness also propagates downstream — if mlvb is stale, then mlvc which depends on mlvb is also stale regardless of its own source timestamps. From there you can write results to a Delta table for historical tracking, feed them into an alerting pipeline, or visualize them inline in the notebook.

Visualizing dependencies in the notebook

Once you have the lineage graph and timestamps as Python objects, you can render an interactive dependency diagram directly in a Fabric notebook cell using displayHTML. Rather than building the diagram from scratch, I used vis.js, a JavaScript network visualization library that loads directly from a public URL at render time with no package dependencies to manage.

vis.js handles node layout, edge routing, and interactivity out of the box, which makes it a practical choice for notebook-based visualization. The diagram is built by serializing the Python lineage dict and timestamp dict to JSON, injecting them into an HTML string, and passing that to displayHTML. vis.js renders a hierarchical left-to-right layout that mirrors data flow direction, with each node labeled with the object name and its last modified or last refresh timestamp. Nodes are color-coded by type — green for base Delta tables, blue for MLVs — and when staleness detection is enabled, any node that needs a refresh turns orange, as do the edges feeding into it. The resulting diagram is interactive — nodes can be dragged to reposition them, the canvas can be scrolled to zoom, and hovering over a node highlights it and its connected edges, which is useful when tracing dependencies in a more complex pipeline.

One thing worth noting: I attempted to use matplotlib first, which is natively available in Fabric notebooks, but I ran into limitations with arrow routing that made the diagram hard to read when nodes were at different vertical positions. vis.js solved that cleanly without any additional setup, as long as the CDN is accessible from your Fabric capacity.

Walkthrough

To demonstrate this end to end, my notebook sets up three base Delta tables and three MLVs with the following dependencies:

  • mlva is sourced from table1
  • mlvb is sourced from table1 and table2
  • mlvc is sourced from mlva, mlvb, and table3

Change Data Feed is enabled on all three base tables so the Delta transaction log captures each write, and CDF is set on the MLVs as well so incremental refresh is available. After setup, the notebook refreshes all three MLVs in dependency order — mlva and mlvb first, then mlvc — to get a clean baseline. You could also refresh them from the Materialized lake views tab on the Lakehouse item in the Fabric portal.

With everything refreshed, parsing fabric.source.entities and DESCRIBE HISTORY across all objects gives us the full lineage graph and a timestamp for every node. At this point nothing is stale, and the baseline diagram reflects that:

> A vis.js network diagram titled "MLV Lineage — Current State" showing six nodes arranged left to right. Three green nodes represent base Delta tables: table1 timestamped 2026-04-30 19:22, table2 timestamped 2026-04-30 19:20, and table3 timestamped 2026-04-30 19:20. Three blue nodes represent materialized lake views: mlva timestamped 2026-04-30 19:21, mlvb timestamped 2026-04-30 19:22, and mlvc timestamped 2026-04-30 19:22. Arrows show dependencies: table1 feeds into mlva and mlvb, table2 feeds into mlvb, mlva and mlvb feed into mlvc, and table3 feeds into mlvc. All arrows are gray and all nodes are their default colors, indicating that the staleness check has run and no MLVs need a refresh.

Next, three new rows are inserted into table1. That single write makes table1 newer than mlva, mlvb, and mlvc — all three depend on table1 either directly or through a dependency. Re-fetching timestamps and running the staleness check propagates that through the graph, and the updated diagram highlights all affected nodes:

A vis.js network diagram titled "MLV Lineage — Staleness Check" showing six nodes arranged left to right. Two green nodes represent up-to-date base Delta tables: table2 timestamped 2026-04-30 19:20 and table3 timestamped 2026-04-30 19:20. One green node represents table1 timestamped 2026-04-30 19:25, which has a newer timestamp than the MLVs downstream of it. Three orange nodes represent materialized lake views that need a refresh: mlva, mlvb, and mlvc, all timestamped 2026-04-30 19:24. Arrows from table1 to mlva and table1 to mlvb are orange, indicating table1 is the source of the staleness. The arrows from mlva to mlvc and mlvb to mlvc are also orange, showing staleness propagating downstream. The arrows from table2 to mlvb and table3 to mlvc remain gray, as those source tables have not changed since the last MLV refresh.

The Fabric portal already gives you a visual lineage and refresh history for MLVs, but doing this programmatically means you can build on top of it — logging refresh state over time, triggering alerts when MLVs go stale, or embedding a live diagram in an operational notebook. The full notebook is available in my GitHub repo.

What would you do with programmatic access to MLV lineage and refresh state? Let me know in the comments.

The post Programmatically Retrieving MLV Lineage and Refresh Times first appeared on Data Savvy.

/by

New Video: Defaulting Power BI Slicers to the Current Month

,

There is currently no way to set a default value in a Power BI slicer visual. If you create a report with a slicer for month and choose the current month (e.g. April 2026), save the report, and then come back to the report a month later, your original selection will be enforced and the data will now show the prior month. So how do you make reports with slicers show data for the current month by default while allowing users to select other months as needed? This video shows 3 options. My current personal favorite is the button slicer solution I show as the third option.

/by

Monitoring Fabric Mirroring for SQL 2025

, ,
I previously wrote about how the underlying technology for Fabric mirroring changed with SQL Server 2025. The latest version of mirroring that uses the SQL Server Change Feed is reading from the database transaction logs and pushing the data to a landing zone in OneLake. The data is then merged into the Delta tables for the Fabric mirrored database. In this blog post, we will look at how to monitor this process, both in SQL Server and in Fabric.

Monitoring in the Fabric Portal

The item page for the mirrored database in the Fabric portal shows replication status for the database overall as well as for each table. The per-table status includes:
  • rows replicated: the cumulative count of replicated rows, including all inserts, updates, and deletes applied to the target table
  • last completed: the last time the mirrored table was refreshed from the source
  • delay (seconds): the time between when a change was committed at the source and when it was successfully applied to the destination
Screenshot of the Monitor replication page in the Fabric portal showing a database status of Running and a table-level grid with columns for Name, Status, Rows replicated, Last completed, and Delay (seconds). Five tables are visible, all with a Running status, with row counts ranging from 1 to 762 and delays between 23 and 38 seconds.
It is possible for the overall database status to show “Running” while a specific table has an issue. The last completed date will typically reflect the last time data in the table changed — if nothing has changed, nothing is sent and the date does not update. The delay is tracked on the database side and sent to Fabric for display on the monitoring page.

Monitoring in the Database

When you configure Fabric mirroring, it enables the change feed on the database and the tables you have selected to mirror. Once everything is configured, there are some system views and stored procedures you can access to see what’s going on.

sys.dm_change_feed_log_scan_sessions

This DMV is your primary window into change feed health and activity. It returns one row per log scan session, plus an aggregate row where session_id = 0 that summarizes all sessions since the instance last started. The aggregate row is useful for understanding overall health since the instance last started; the individual session rows show you what’s happening in discrete scans. You want to see sessions completing without errors, with tran_count incrementing and currently_processed_lsn or currently_processed_commit_lsn advancing over time. If sessions are stalling at the same batch_processing_phase repeatedly, or error_count is climbing, something needs attention. The schema_change_count column tracks DDL-related log records processed in a session. Note that this is not a 1:1 mapping with the number of DDL operations — some operations, like adding, altering, or dropping a column, generate two log records per operation, so the count may be higher than you expect.

sys.dm_change_feed_errors

When error_count in the sessions DMV is non-zero, this is where you find out what went wrong. Some errors are transient and will resolve on their own; others won’t. Repeated errors of the same type are a signal to investigate rather than wait it out.

sys.sp_help_change_feed

This stored procedure gives you a configuration-level view of which tables are enrolled in mirroring and their current state. It’s useful after initial setup to confirm everything was picked up correctly, and when a table stops replicating and you want to verify it’s still enrolled.

sys.databases

Unlike CDC, the change feed doesn’t write to change tables in the source database, so it won’t directly cause the transaction log to grow. However, it does hold log truncation until changes are successfully replicated to Fabric. You can use is_data_lake_replication_enabled = 1 to filter sys.databases to only the databases where Fabric mirroring is enabled. A value of REPLICATION for log_reuse_wait_desc means mirroring is currently holding log truncation — not necessarily something that needs immediate action, but if log usage is also growing, that’s a signal that mirroring may not be keeping pace and warrants investigation. If the log hits its size limit, writes to the database will fail.

Extended Events

Microsoft also provides an extended events session you can use for deeper troubleshooting. The session captures change feed activity including errors, snapshots, performance, and scheduler events. Because it can be verbose, Microsoft recommends only running it when you’re actively troubleshooting a problem rather than leaving it on all the time. You can find the session definition in the Troubleshoot Fabric Mirrored Databases from SQL Server documentation.

Files in OneLake

You can use Azure Storage Explorer to browse the files for your Fabric mirrored database. The Monitoring folder contains two files:
  • tables.json shows each mirrored table along with its status, any error message, and metrics.
  • replicator.json shows the overall database replication status along with any error message.
The Manifest_1.json file in the LandingZone folder is a newline-delimited JSON log that records everything the change feed has published to OneLake — initial snapshots, incremental change batches, DDL events, and table creation notifications. It’s not something you’d monitor routinely, but it’s useful for troubleshooting — for example, to confirm that a snapshot completed and how many rows were captured, to see whether incremental batches are being written, or to trace when a DDL change occurred and verify that a re-snapshot was triggered as a result. Within each table’s subfolder you’ll find a TableSchema.json file with the table’s schema definition, a FullCopyData folder where the initial snapshot parquet files are written, and a ChangeData folder where incremental CSV change files land after the snapshot completes. If you’re troubleshooting a data discrepancy or want to confirm that specific changes have made it to OneLake, you can browse these folders directly to check whether the expected files are present.

Knowing Where to Look

For day-to-day health checks, the portal monitoring page and sys.dm_change_feed_log_scan_sessions will cover most of what you need. If you see errors or stalled sessions, sys.dm_change_feed_errors and sys.sp_help_change_feed are the next stop. The OneLake files and extended events are there when you need to dig deeper into a specific problem. Mirroring continues to get new features and changes, so keep an eye on the Microsoft Fabric Mirrored Databases from SQL Server documentation for updates. The post Monitoring Fabric Mirroring for SQL 2025 first appeared on Data Savvy.
/by

SQL Server 2025 Fabric Mirroring: Change Feed vs CDC Explained

,

Mirroring of SQL Server databases in Microsoft Fabric was first released in public preview in March 2024. Mirrored databases promise near-real-time replication without the need to manage and orchestrate pipelines, copy jobs, or notebooks. John Sterrett blogged about them last year here. But since that initial release, the mechanism under the hood has evolved significantly. Let's talk about Fabric Mirroring: Change Feed vs CDC Explained.

How Fabric Mirroring Works in SQL Server 2016–2022 (CDC)

When mirroring was first released for Azure SQL Database, it used Change Data Capture (CDC). That is still what is used to mirror SQL Server 2016 – 2022.

CDC works by asynchronously scanning the transaction log to find changes related to tracked tables, then writing those changes to dedicated change tables — one per tracked source table, in the format cdc.schema_tablename_CT. A SQL Server Agent job (sys.sp_cdc_scan) is responsible for that log scan and write. Fabric’s replication layer then polls those change tables and pulls changes into OneLake, where they’re converted to Delta Parquet format. The result is a two-hop process: log → change table → OneLake. That intermediary write step is where the overhead lives. If you have a busy SQL Server, this overhead may not be acceptable.

Setting up CDC for SQL Server 2016–2022 requires the fabric_login principal to be a member of the sysadmin server role, at least temporarily, and any future CDC maintenance also requires sysadmin membership. For security-conscious organizations, that’s a significant ask. You can drop the login from sysadmin after CDC is configured, but having to elevate it in the first place causes friction.

 
In SQL Server 2016–2022, if a table’s schema changes after CDC is enabled, the mirrored table schema no longer matches the source, and mirroring fails. Getting replication back on track requires manually disabling and re-enabling CDC on the affected tables.

 
SQL Server 2025 Fabric Mirroring: The Change Feed Explained

SQL Server 2025 has a much better solution: the change feed. Rather than routing changes through change tables, the change feed scans the transaction log at a high frequency and publishes committed changes directly to a landing zone in OneLake. Fabric’s replicator engine then merges those files into the target Delta tables. The intermediary write step is gone.

Because the change feed doesn’t write data back into the source database, it carries lower overhead than CDC. There are no change tables to maintain, no SQL Server Agent jobs to keep healthy, and no cleanup jobs running in the background purging old change records. For busy OLTP systems where CDC’s performance overhead was a concern, this is a meaningful improvement.

DDL changes are handled better, too. Rather than failing when a schema change is detected, the change feed triggers a full re-snapshot of the affected table and reseeds the data automatically. That re-snapshot has a cost if the table is large, but it’s self-healing. You won’t come in Monday morning to find mirroring has been broken since Friday’s deployment.

The permissions model is also cleaner. Rather than requiring sysadmin elevation to configure CDC, SQL Server 2025 mirroring uses a system-assigned managed identity to handle outbound authentication to Fabric. You still create a dedicated login with minimal permissions on the source database, but sysadmin is never required.

SQL Server 2025 Fabric Mirroring Change Feed vs CDC architecture diagram

How to Plan Your Fabric Mirroring Migration: CDC vs Change Feed

CDC-based mirroring is still what SQL Server 2016–2022 uses, and it works. Just go in with eyes open about the performance overhead, the sysadmin requirement, and the DDL limitations.

If you’re already on SQL Server 2025 or planning to upgrade, the change feed makes mirroring a more attractive option than it was under CDC — particularly for busy OLTP systems where the overhead of change table writes was a concern.

Before you enable mirroring on SQL Server 2025, there are a few constraints worth knowing. The source database must be set to the full recovery model (simple recovery is not supported). The change feed is also mutually exclusive with CDC: if CDC is already enabled on a database, you cannot enable Fabric mirroring on that same database. If you’re running CDC today for other consumers, you have a decision to make. You’ll need to decide whether to remove CDC and consolidate on the change feed or keep CDC and find another path for getting that data into Fabric.

Note: As of March 11, 2026, SQL Server 2025 mirroring is supported for on-premises instances only. It is not supported for SQL Server 2025 running in an Azure Virtual Machine or on Linux. It also requires the instance to be connected to Azure Arc with the Azure Extension for SQL Server installed.

Mirroring is still evolving, so it’s worth keeping an eye on the Fabric Mirroring roadmap. And of course, stay tuned for announcements from FABCON next week!

The post How Fabric Mirroring Transformed with SQL Server 2025 first appeared on Data Savvy.

/by

SQL Server Dynamic Data Masking Security Pitfalls: How Masked Data Gets Exposed

, ,
SQL Server Dynamic Data Masking (DDM) is one of those SQL Server features that is commonly misused as a primary security feature used in production. Since it landed in SQL Server 2016, I’ve seen teams throw it at compliance requirements and call it a day, only to find out later that their “masked” data was completely readable by anyone willing to spend 20 minutes in SSMS.

Let me show you some data masking limitations in SQL Server when used for security, and what you should be doing instead (or alongside it) if you’re serious about Zero Trust data security.

TL;DR SQL Server Dynamic Data Masking is a presentation layer feature, not a security feature.

 

SQL Server Dynamic Data Masking Security?

Dynamic Data Masking intercepts query results at the engine level and replaces sensitive column values with masked versions for users who lack the UNMASK permission. The actual data in the database is unchanged — DDM is purely a presentation-layer filter. Again, DDM is a presentation-layer filer.

Security Pitfall 1: Inferring Masked Values with WHERE Clause Attacks

This is the big one. SQL Server Dynamic Data Masking masks the output of a query. It does nothing to prevent a user from using the masked column in a WHERE clause.

Here’s what that looks like in practice:

-- The user can't see the salary column directly (it's masked)
SELECT EmployeeID, Salary FROM HR.Employees;
-- Returns: 1, xxxx

-- But they CAN do this:
SELECT EmployeeID FROM HR.Employees WHERE Salary BETWEEN 95000 AND 105000;
-- Returns rows — meaning they just learned who earns in that range

By running enough range queries, a patient attacker can binary-search their way to the exact value of any masked numeric column. For salary data, SSNs, account numbers, or any structured numeric field, this completely defeats the purpose of masking the data.

Microsoft’s own documentation acknowledges this. It’s not a bug — it’s a documented architectural constraint. DDM was never designed to stop a determined insider.

Here is another example, from start to finish, showing why you should encrypt sensitive data. Let’s look at using predicates to extract Social Security numbers (SSNs). The following demo uses fictitious generated data for people.

SQL Server Dynamic Data Masking Security – SSN Demo


CREATE DATABASE [demo]
GO

USE [demo]
go

CREATE TABLE dbo.Person (
    PersonID  int   NOT NULL
      CONSTRAINT PK_dboPerson 
           PRIMARY KEY CLUSTERED  identity
    ,FirstName  varchar(50)  NULL
    ,LastName  varchar(50) NULL
    ,SSN  varchar(11)  NULL
)
GO

INSERT INTO dbo.Person (FirstName, LastName, SSN)
VALUES
    ('Patricia', 'Smith', '760-36-4013'),
    ('Linda', 'Jones', '755-14-8936'),
    ('John', 'Lee', '433-05-0489'),
    ('John', 'Miller', '239-65-9864'),
    ('James', 'Martin', '204-92-8929'),
    ('Jessica', 'Davis', '460-76-4558'),
    ('James', 'Garcia', '715-55-5575'),
    ('William', 'Jones', '221-98-5515'),
    ('Patricia', 'Williams', '390-13-5882'),
    ('Susan', 'Perez', '271-06-7528'),
    ('Karen', 'Brown', '388-11-9045'),
    ('Barbara', 'Perez', '883-47-9460'),
    ('Michael', 'Williams', '047-85-3734'),
    ('Barbara', 'Williams', '876-30-1655'),
    ('Richard', 'Martinez', '465-82-5978'),
    ('Jennifer', 'Wilson', '364-27-4375'),
    ('John', 'Perez', '651-22-8752'),
    ('Linda', 'Garcia', '474-49-4423'),
    ('Karen', 'Davis', '702-42-0917'),
    ('Linda', 'Johnson', '825-41-6573');
    GO

-- Nothing is masked, can see SSN's 
select * from dbo.Person
If we are users with UNMASK access, here is what we would see.

Fictitious SSN data that will be exposed through SQL Server Dynamic Data Masking

Fictitious SSN data that will be exposed through SQL Server Dynamic Data Masking

Let’s now enable SQL Server Dynamic Data Masking to mask Social Security numbers (SSNs).  We will create a MaskedUser for testing purposes to see the results from a masked user’s perspective.

/* Mask SSN's */
ALTER TABLE dbo.Person
ALTER COLUMN SSN
ADD MASKED WITH (FUNCTION = 'default()')
GO

/* Create testing user */
CREATE USER [MaskedUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT SELECT ON dbo.Person TO [MaskedUser];
GO

EXECUTE AS USER = 'MaskedUser';
GO
SELECT * FROM dbo.Person
GO

SQL Server Dynamic Data Masking at work. SSN's are masked for this user.

SQL Server Dynamic Data Masking at work. SSNs are masked for this user.

Okay, let’s now take a look at leveraging predicates to learn the data, even if it’s masked. Remember, DDM masks the output, not the input, in this case, the predicates. In the next example, we’re going to use predicates to check whether the SSNs match the xxx-xx-xxxx or xxxxxxxxx format.

EXECUTE AS USER = 'MaskedUser';
GO
SELECT COUNT(*) FROM dbo.Person
SELECT COUNT(*) FROM dbo.Person WHERE CHARINDEX('-', SSN) = 4
SELECT COUNT(*) FROM dbo.Person WHERE CHARINDEX('-', SSN, 5) = 7
REVERT;

SQL Server Dynamic Data Masking doesn't mask predicates just the output results

SQL Server Dynamic Data Masking doesn’t mask predicates, just the output results

Finally, here is where things get interesting. We can build table variables for each possible numeric value for a digit, filter the SSN masked column on each digit, and select the ones that match. This is how you can see SSNs even when you are the user who should see them masked.


SET NOCOUNT ON
GO
EXECUTE AS USER = 'MaskedUser';
GO
DECLARE @SSN1 TABLE (
    SSN1 char(3) PRIMARY KEY CLUSTERED
);
DECLARE 
    @SSN1Loop1        int = 0
    ,@SSN1Loop2        int = 0
    ,@SSN1Loop3        int = 0
WHILE @SSN1Loop1 < 10
BEGIN
    SELECT @SSN1Loop2 = 0
    WHILE @SSN1Loop2 < 10
    BEGIN
        SELECT @SSN1Loop3 = 0
        WHILE @SSN1Loop3 < 10
        BEGIN
            INSERT INTO @SSN1 (SSN1)
            SELECT CONVERT(char(1),@SSN1Loop1) 
                    + CONVERT(char(1),@SSN1Loop2) 
                    + CONVERT(char(1),@SSN1Loop3)
            SELECT @SSN1Loop3 += 1
        END
        SELECT @SSN1Loop2 += 1
    END
    SELECT @SSN1Loop1 += 1
END
--SELECT * FROM @SSN1
DECLARE @SSN2 TABLE (
    SSN2 char(2) PRIMARY KEY CLUSTERED
)
DECLARE 
    @SSN2Loop1        int = 0
    ,@SSN2Loop2        int = 0
WHILE @SSN2Loop1 < 10
BEGIN
    SELECT @SSN2Loop2 = 0
    WHILE @SSN2Loop2 < 10
    BEGIN
        INSERT INTO @SSN2 (SSN2)
        SELECT CONVERT(char(1),@SSN2Loop1) 
                  + CONVERT(char(1),@SSN2Loop2)
        SELECT @SSN2Loop2 += 1
    END
    SELECT @SSN2Loop1 += 1
END
--SELECT * FROM @SSN2
DECLARE @SSN3 TABLE (
    SSN3 char(4) PRIMARY KEY CLUSTERED
)
DECLARE 
    @SSN3Loop1        int = 0
    ,@SSN3Loop2        int = 0
    ,@SSN3Loop3        int = 0
    ,@SSN3Loop4        int = 0
WHILE @SSN3Loop1 < 10
BEGIN
    SELECT @SSN3Loop2 = 0
    WHILE @SSN3Loop2 < 10
    BEGIN
        SELECT @SSN3Loop3 = 0
        WHILE @SSN3Loop3 < 10
        BEGIN
            SELECT @SSN3Loop4 = 0
            WHILE @SSN3Loop4 < 10
            BEGIN
                INSERT INTO @SSN3 (SSN3)
                SELECT CONVERT(char(1),@SSN3Loop1)  
                     + CONVERT(char(1),@SSN3Loop2) 
                     + CONVERT(char(1),@SSN3Loop3) 
                     + CONVERT(char(1),@SSN3Loop4)
                SELECT @SSN3Loop4 += 1
            END
            SELECT @SSN3Loop3 += 1
        END
        SELECT @SSN3Loop2 += 1
    END
    SELECT @SSN3Loop1 += 1
END
SELECT
    P.PersonID
    ,P.FirstName
    ,P.LastName
    ,P.SSN
    ,T1.SSN1
    ,T2.SSN2
    ,T3.SSN3
FROM dbo.Person P
    LEFT JOIN @SSN1 T1
        ON SUBSTRING(P.SSN,1,3) = T1.SSN1
    LEFT JOIN @SSN2 T2
        ON SUBSTRING(P.SSN,5,2) = T2.SSN2
    LEFT JOIN @SSN3 T3
        ON SUBSTRING(P.SSN,8,4) = T3.SSN3
ORDER BY P.PersonID;
GO
REVERT
GO

SQL Server Dynamic Data Masking predicate bypass attack showing SSN exposure

SQL Server Dynamic Data Masking predicate bypass attack showing SSN exposure

What to do instead: If users shouldn’t be able to filter on a column, they shouldn’t have SELECT on that table at all. Row-Level Security (RLS) combined with column-level encryption or Always Encrypted is the right tool here, not Dynamic Data Masking.

Security Pitfall 2: Anyone with ALTER TABLE Can Remove the Mask Entirely

Okay, let’s assume pitfall #1 didn’t stop you in your tracks from wanting to use SQL Server Dynamic Data Masking for security purposes outside the presentation layer (it should have).

Here’s one that gets overlooked in permission audits. Any user with ALTER TABLE or ALTER ANY MASK permission can simply drop it like it’s hot, the masking function from a column:

-- Requires ALTER TABLE permission (not UNMASK):
ALTER TABLE HR.Employees
ALTER COLUMN Salary DROP MASKED;

After that, everyone can read the data — no UNMASK needed. The mask is gone permanently until someone re-adds it. So users, could drop it, see what they need to see, and add it back.

If your people have db_ddladmin or elevated schema permissions (which many do in smaller shops), they can silently unmask entire tables. And because DDM changes don’t appear in the default audit logs unless you’ve explicitly set up an audit specification for it, this can go undetected.
What to do instead: Audit who has ALTER TABLE permissions. Create a SQL Server Audit specification that captures DATABASE_OBJECT_CHANGE_GROUP events to catch mask additions and removals. Treat DDM schema changes as security events, not as schema changes.

 

Security Pitfall 3: Metadata Is Fully Visible to Every User

Even a user with only VIEW ANY DEFINITION permissions can see exactly which columns are masked. This where Young Joc would say, “I know you see…”

SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS ColumnName,
    masking_function
FROM sys.masked_columns;
This tells an attacker exactly which data is considered sensitive, which masking function is applied (which reveals the data type and format), and where to focus their inference attacks.
For something like a credit card number masked with partial(0,’XXXX-XXXX-XXXX-‘,4), the masking function itself reveals the exact format the attacker is trying to reconstruct.
What to do instead: There’s no way to hide this metadata — it’s in system views. Accept it as a constraint of Dynamic Data Masking and compensate by limiting who has any database access. Don’t rely on obscurity. All sensitive data should be encrypted and audited.

 

Security Pitfall 4: DDM Doesn’t Survive Data Movement

This is the one that kills compliance reviews. SQL Server Dynamic Data Masking is defined at the column level in the source table. If Cardi B was a DBA she would say, “Make the Data Move.”

The moment that data moves anywhere else, the mask disappears:

  • SELECT INTO / INSERT SELECT: The mask function is not copied to the destination table.
  • OPENROWSET / linked server queries: DDM is not enforced on remote result sets.
  • BCP / bulk export: Exports the unmasked data if run by a privileged account.
  • SSRS, Power BI, or any reporting tool: If the service account has UNMASK (or DDM isn’t configured in the cloud copy), the reports show real data.
  • Database backups: Backups contain the actual unmasked data. If the backup is restored by someone with elevated permissions, DDM means nothing.

Teams that use DDM to “protect” data flowing into a staging database or a data warehouse are providing exactly zero protection for that downstream data.

Here is an example of seeing that SELECT INTO doesn’t mask data in the destination table.


EXECUTE AS USER = 'MaskedUser';
GO
SELECT * 
FROM dbo.Person2
GO
REVERT
GO

Fictitious SSN data that will be exposed through SQL Server Dynamic Data Masking

Fictitious SSN data that will be exposed through SQL Server Dynamic Data Masking

What to do instead: Treat data movement as a security event. Apply encryption for data that needs to stay protected during movement. DDM belongs only in the presentation layer, not as a data-pipeline security control.

Security Pitfall 5: The UNMASK Permission A Security Vulnerability Sledgehammer

When Dynamic Data Masking was released with SQL Server 2016, you could either grant UNMASK  at the database level.  Just like O-town would sing, you can either Mask it All or Nothing at All.
With SQL Server 2022, Microsoft introduced granular UNMASK permissions (you can grant UNMASK on a specific schema, table, or column). On SQL Server 2019 and below, UNMASK is a database-level permission.
That means: if you grant a developer UNMASK so they can debug a production issue, they can now see unmasked data in every masked column across the entire database.
-- Granting this one permission unlocks everything:
GRANT UNMASK TO [DevUser];

This isn’t a theoretical risk. Dev teams routinely need production access for troubleshooting. If your escalation playbook involves handing out UNMASK, you’ve just handed them a master key.

What to do instead: Upgrade your permission model. If you’re on SQL Server 2022 or Azure SQL, use column-level UNMASK grants. If you’re on older versions, use static data masking with sanitized data for dev access rather than granting UNMASK on production. Again, I wouldn’t use Dynamic Data Masking for security purposes.

So Should You Use DDM at All?

Yes,but in the right context. DDM is legitimately useful for:

  • Reducing casual exposure in reporting environments where users cannot probe the data.
  • Limiting blast radius if a low-privilege account is compromised (they see masked output, not raw PII)
  • Satisfying compliance documentation requirements as only one layer in a defense-in-depth stack.

The problem is treating Dynamic Data masking as your primary security control. In a Zero Trust model, every layer needs to hold up under adversarial conditions. DDM does not by itself. It’s a trip wire, not a vault.

Your actual Zero Trust data security stack in SQL Server should look like this:

Layer Tool
Authentication Active Directory Domain Services / Entra ID + MFA
Column-level protection Always Encrypted or Column Level Encryption.
Row-level access Row-Level Security (RLS)
Presentation-layer masking Dynamic Data Masking (DDM)
Audit and detection SQL Server Audit + Microsoft Defender for SQL

DDM goes in the “presentation layer” bucket. The moment you understand that distinction, you’ll stop over-relying on it.

FAQ: Dynamic Data Masking in SQL Server

Here are some common questions that come up with SQL Server Dynamic Data Masking:

Does SQL Server Dynamic Data Masking protect against SQL injection?

No, Dynamic Data Masking in SQL Server provides zero protection against SQL injection attacks. SQL injection exploits vulnerabilities in how an application constructs queries, allowing an attacker to execute arbitrary SQL against your database.

Is Dynamic Data Masking HIPAA compliant?

Dynamic Data Masking in SQL Server alone is not sufficient for HIPAA compliance, but it can be one contributing layer in a compliant architecture.

What is the difference between DDM and Always Encrypted?

These two features operate at completely different layers of the stack and solve fundamentally different problems. At a high level, Dynamic Data Masking works at the presentation layer, and Always Encrypted encrypts the data.

The Takeaway

Dynamic Data Masking is a convenience feature with security side effects. Dynamic Data Masking is not a security feature with convenience side effects. If you’re using it as your main line of defense against unauthorized data access, you’re one curious person or one clever WHERE clause away from an data exposure incident.

 

John Sterrett is a Principal at ProcureSQL, a Microsoft Data Platform consultancy specializing in SQL Server performance, security, and Azure migrations. Follow him on YouTube and Instagram for weekly SQL Server content.

Questions or war stories about DDM? Drop them in the comments.

/by

Operating Materialized Lake Views in Microsoft Fabric

, , ,

This is the last post in a three-part series exploring the mechanics of Materialized Lake Views (MLVs). The goal is to help you understand how they work and whether they make sense for your environment. What they are, when they help, and when they fall short.

Read more
/by

Designing with Materialized Lake Views in Microsoft Fabric

, ,

This is the second post in a three-part series exploring the mechanics of Materialized Lake Views. The goal is to help you understand how they work and whether they make sense for your environment. What they are, when they help, and when they fall short.

Read more
/by

We’re Speaking at FabCon/SQLCon

, ,

We are happy to announce that three ProcureSQL employees are on the FABCON/SQLCON agenda. These co-located conferences are happening in Atlanta, Georgia March 16 – 20.

FABCON is the largest conference dedicated to Microsoft Fabric, bringing together tech enthusiasts, innovators, and industry leaders to explore the future of data, analytics, business intelligence, and AI integration.

SQLCON stands out as a premier conference within FABCON, featuring sessions and workshops that take you on a dive deep into SQL Server, Azure SQL, SQL in Fabric, SQL Tools, AI Apps with SQL, migration & modernization, optimization & performance, database security, and much more.

Justin Cunningham and Meagan Longoria are presenting Mirroring for SQL Server in Fabric: Inside the Replication Process. This session examines how Mirroring for SQL Server in Fabric really works: what happens on the source database, how data lands in OneLake, data retention policies, monitoring and logging, configuration of replicated objects, and which scenarios require restarting replication.

Justin is also presenting Materialized Lake Views: Simplifying Your Medallion ETL. Materialized lake views turn Spark SQL into auto-orchestrated smart tables in your Fabric lakehouse. This session covers how to replace fragile ETL with declarative medallion layers, understand refresh and lineage, and decide when materialized lake views beat notebooks, jobs, or warehouses.

John Sterrett is presenting Modern Data Protection Strategies with SQL Server. Today, we handle increasing volumes of sensitive data. DBAs and Data Engineers must protect their data from external attackers, employees, and privileged admins like themselves. This session will teach you how to protect your data while maintaining functionality and performance. The session focuses on using column-level encryption, row-level security, and auditing to secure your data.

We are looking forward to speaking and hope to see you in our sessions.

/by