Analytics on Azure Blog articles

Building AI apps and agents with Azure Databricks, Copilot Studio, and GitHub Copilot

Jason_Pereira — Tue, 02 Jun 2026 16:15:00 GMT

A workspace-wide Genie MCP endpoint for Copilot Studio

Genie is Azure Databricks’ AI agent that lets any employee chat with their data and get trusted answers instantly. Genie Spaces are curated, business‑domain workspaces for teams to find strategic insights for their targeted use cases. Until now, connecting Azure Databricks Genie to Microsoft Copilot Studio meant adding each Genie Space as a separate tool. This works and adds value for customers wanting to integrate a specific Genie Space with Copilot Studio, but the per-space MCP server added overhead when trying to connect multiple Genie spaces to one Copilot Studio agent. The workspace-wide MCP endpoint changes that. One endpoint per workspace gives a Copilot Studio agent access to every connected Genie space and Unity Catalog dataset, and the curated context inside each Genie space stays in place.

Key capabilities:

Natural-language access across the workspace. Copilot Studio agents can route questions across every connected Genie Space and Unity Catalog dataset without losing the curation that keeps answers accurate.
Unity Catalog governance. Access controls are enforced at query time, so existing data permissions extend to every agent built in Copilot Studio.
Beyond a single domain. Move from a finance agent or a supply chain agent to a workspace-aware agent that follows users wherever the data lives.

Lakebase branching with GitHub Copilot agent mode

Production AI agents fail on real-data edge cases that synthetic or mocked environments do not catch. But giving a developer direct production access to investigate is not a realistic option in most enterprises. Lakebase branching, now integrated with GitHub Copilot agent mode, gives you a way to debug against real data without ever connecting to the production database.

Key capabilities:

Copy-on-write branching. Create a full-fidelity branch of a Lakebase production database in seconds. No data is moved and no production records are altered.
Native GitHub Copilot agent debugging. Point GitHub Copilot agent mode at the branch endpoint to reproduce, root-cause, and resolve data-dependent issues with AI assistance.
Azure-native end-to-end workflow. The full loop runs across GitHub, Azure Databricks, and Lakebase. No third-party tools or custom infrastructure required.
Compliance built in. Fixes ship through the standard Git-based deployment and compliance workflows already in place, so debug cycles compress from hours to minutes.

What this unlocks for AI agent teams

Together, the two capabilities cover both halves of the agent lifecycle on Azure:

Author Copilot Studio agents that reason over an entire Azure Databricks workspace through one MCP connection.
Debug production AI agents against real Lakebase data using GitHub Copilot agent mode, reducing production data risk.
Keep Unity Catalog governance and existing compliance controls in place from authoring through deployment.
Standardize the data, agent, and developer toolchain on GitHub, Azure Databricks, and Microsoft 365.

Get started

Both features are available in public preview on June 2, 2026, directly in Azure Databricks workspaces.

Secure Medallion Architecture Pattern on Azure Databricks (Part II)

mscagliola — Fri, 15 May 2026 08:48:58 GMT

Disclaimer: The views in this article are my own and do not represent Microsoft or Databricks.

This article is part of a series focused on deploying a secure Medallion Architecture. The series follows a top-down approach , beginning with a high-level architectural perspective and gradually drilling down into implementation details using repeatable, code.

In this part we will discuss the implementation of the pattern using GitHub Copilot

If you have missed, please read first the first part of this blog series. It can be found at: Secure Medallion Architecture Pattern on Azure Databricks (Part I).

I waited a while before publishing this article. Partly due to other priorities, but also because I wanted to experiment with deploying infrastructure and data pipelines using agents. At that point, I was looking to leverage agents with a spec-driven approach, and through using GitHub Copilot, I learned what skills are and how I can use them to achieve my scope.

In this blog I'll share what I learned using GitHub Copilot for spec-driven development.

I'll use the content from my previous article, Secure Medallion Architecture Pattern on Azure Databricks (Part I) , as a technical specification to extract implementation details and generate two outputs:

Terraform code for infrastructure, platform configuration, and deployment
Databricks Declarative Automation Bundles for jobs, pipelines, and other deployment-ready workload resources

I've tried not to overfit the prompts within the skills I've developed, so they remain portable to other technical articles, not just the one mentioned in this blog.

Separate the platform from the workload

When I started the design, I decided to modularise the automation scripts by separating the platform from the actual data platform workloads. I assigned networking, storage, identities, secret scopes, and workspace configuration to Terraform, while Databricks notebook runs, job clusters, pipelines, and environment-specific deployments were developed within Databricks Declarative Automation Bundles (formerly known as Databricks Asset Bundles).

That may sound obvious, but it's exactly where generated code often goes wrong. Without explicit instructions, AI tools tend to blur these boundaries and produce one oversized block of configuration. That's why my Copilot skill needs to enforce a clear contract by:

Infer the architecture from the article
Identify what is explicit and what is assumed
Emit Terraform only for infrastructure concerns
Emit bundle files only for workload concerns
Leave placeholders for anything the article does not specify

That last point is critical. A blog post or low-level technical specification is not a source of truth for account IDs, hostnames, catalog names, secret values, or subnet IDs. Good automation should never fabricate those values. Instead, I decided to produce a starter implementation with TODO markers wherever environment-specific values are required.

Skills are a great way to get more consistent, repeatable output across runs, so I decided to use them for this project. I could have used one of the tools listed in the table below, but I chose to go my own way, into developing a Spec-Driven Development (SDD) framework which I hope it will carryon improve with time.

Tool	Creator	Type	Link	Description
GitHub Spec Kit	GitHub	Open source	github/spec-kit	Turns feature ideas into specs, plans, and task lists before any code is written. Works with multiple AI coding agents. Specification first, code as generated output.
BMAD Method	BMad Code LLC	Open source	bmad-code-org/BMAD-METHOD	An AI-driven agile framework with specialised agents covering the full lifecycle from ideation to deployment. Scale-adaptive — adjusts planning depth from a bug fix to an enterprise system.
OpenSpec	Fission AI	Open source	Fission-AI/OpenSpec	Lightweight spec layer that sits above your existing AI tools. Each change gets a proposal, specs, design, and task list. No rigid phase gates, no IDE lock-in.

What are skills, and why are they a good fit?

Skills are essentially reusable prompt modules that aim to force LLMs to produce repeatable answers. Within a skill, I define the behavior and then attach supporting resources or scripts so Copilot can perform the task consistently. That means a skill can do more than just "write some code."

A skill can define a repeatable workflow like this:

Fetch the blog URL
Extract headings, paragraphs, and code snippets
Normalize the article into a lightweight implementation spec
Decide what belongs in Terraform
Decide what belongs in the Databricks bundle
Generate files in a predictable project structure
Produce a TODO.md file for unresolved values

This approach turns Copilot from a generic assistant into a specialized code-conversion tool.

However, there are some constraints I had to be mindful of when developing skills:

Context window limits. The model has limited space to read instructions, process input, and generate output. Long prompts can cause files to be cut off or steps to be skipped.
Non-determinism. Output may vary between runs, even with strict instructions. I always lint, validate, and review the diff before committing.
Boundary leakage. Models may invent plausible but incorrect values. The TODO.md pattern must be enforced as a rule, not a suggestion.
Model and tool drift. Copilot's model and tool surface change over time. I use example inputs and outputs as repeatable sanity checks.
Maintainability. A skill is code-as-prompt and will age with the platforms it targets. I keep skills narrowly scoped so they stay easy to update.

I'll explain the TODO.md file in more detail later in this post.

The GitHub repo

The repository can be found at the link MarcoScagliola/CopilotBlogToCode

Below you will find a function I have added that, when invoked, deletes all the files produced by the skills, so you can test the repo from a clean state.

python .github/skills/blog-to-databricks-iac/scripts/reset_generated.py --force;

If you want to tried it out, please clone and try it on your copy. In GitHub Copilot, I usually keep:

Model as Auto
Foer the configure tools I keep just the built-in tools selected.

Below you can find the prompt that I use to run the skills and have the blog analysed.

Use the blog-to-databricks-iac skill on this article:
https://techcommunity.microsoft.com/blog/analyticsonazure/secure-medallion-architecture-pattern-on-azure-databricks-part-i/4459268

Inputs:

workload: blg

environment: dev

azure_region: uksouth

github_environment:

To make this more repeatable and less manual, I've added a prompt file at run-blogToDatabricksIac-selected-tools.prompt.md, which can be run directly from VS Code by opening the file and clicking the run button at the top. Feel free to experiment with it and let me know what you think.

Further instructions on how to use the repo are available READ_FIRST.md.

Following you will find the exact repository setup I used for this workflow, starting with my initial configuration and ending with the final directory structure and files.

1. Create a new GitHub repository and clone it locally

I started by creating a new repository on GitHub, then cloned it to my local machine so I could add the Copilot skill, Terraform scaffolding, and Databricks bundle files in a centralized location.

git clone https://github.com/YOUR-ORG/blog-to-databricks-iac.git cd blog-to-databricks-iac

This approach keeps the workflow organised from the start: the repository exists on GitHub first, and the local clone becomes the working directory for all subsequent setup steps.

2. Create the GitHub skill folder structure (first iteration)

GitHub Copilot skills are file-based and centered on a SKILL.md file inside a skill folder. GitHub's current pattern places these under .github/skills/.

I used the script below to create the folder hierarchy for my initial integration.

mkdir -p .github/skills/blog-to-databricks-iac/scripts mkdir -p .github/skills/blog-to-databricks-iac/templates mkdir -p infra/terraform mkdir -p databricks-bundle/resources mkdir -p databricks-bundle/src

This script generates the structure depicted below.

3. Add the main skill definition

Next, I created the SKILL.md file at .github/skills/blog-to-databricks-iac/. The orchestrator decides what happens and in what order, while each specialist decides what its own file should contain (as an example the Terraform specialist owns the Terraform, the bundle specialist owns the bundle, and so on).

In practice, SKILL.md turns Copilot from a general assistant into a domain-specific generator for this repo. GitHub documents this SKILL.md-based structure as the foundation of agent skills. My first iteration of .github/skills/blog-to-databricks-iac/SKILL.md> was very simple and can be found here.

4. Add a script to fetch and normalize the blog article

Next, I created a Python script that the main orchestrator SKILL.md invokes to read the blog article. This script is stored at .github/skills/blog-to-databricks-iac/scripts/ and named fetch_blog.py. Within SKILL.md, the script is invoked as shown below.

### 1. Fetch article ```bash python .github/skills/blog-to-databricks-iac/scripts/fetch_blog.py "<url>" ``` If fetch fails, stop and return the fetch error output. Do not retry; surface the error to the user and wait for guidance.</url>

The script validates the URL, fetches the HTML with a 30-second timeout, and uses a spoofed Mozilla User-Agent to avoid being blocked by CDNs (Content Delivery Networks). It reads through the HTML one tag at a time, flagging when it enters relevant sections like paragraphs, headings, or code blocks, and buffering text until the tag closes. Before storing anything, it cleans the text by decoding HTML objects, collapsing whitespace, and trimming edges.

As it parses, the script also scans for cloud platform keywords: AWS, S3, Azure, ADLS, GCP, Google Cloud. The first match wins; if none are found, it returns unknown. This is a quick heuristic, not authoritative.

Finally, it outputs clean JSON with the extracted data: title, headings, paragraphs, code blocks, and cloud hint, capped at reasonable sizes to keep the output manageable. If anything goes wrong, such as a network error, timeout, bad HTML, or empty content, the script exits cleanly with a structured error message, making it easy to integrate into larger workflows without surprises.

The Python scrip can be found here.

5. The output and output contract

Now I needed to think about the output I wanted GitHub Copilot to deliver through the skills. To reiterate, I needed the following:

File Name	Description
README.md	This is the operator-facing runbook that turns the generated artifacts into a working deployment. It contains no unresolved placeholders and no embedded credentials. The header summarizes the architecture and links back to the source blog. A prerequisites section lists required Azure access, Entra permissions, GitHub Environment setup, and local CLI versions. It includes tables of always-required GitHub secrets and variables, plus conditional ones based on deployment mode. Step-by-step numbered sections walk through bootstrapping the deployment principal and populating the GitHub Environment. Workflow blocks describe each Terraform validation, infrastructure deployment, and DAB deployment step, including file paths, triggers, and outputs. A commands section lists the exact Terraform and Databricks bundle sequences to run. Finally, assumption notes point the operator to `TODO.md` and `SPEC.md` for context.
TODO.md	The operator's checklist of remaining tasks. It uses a strict five-section format (Heading, What this is, Why deferred, Source, Resolution, Done looks like) with no commands or code, only concepts and decisions. Each section captures a different layer of post-deployment work, pre-deployment tasks like RBAC roles and GitHub secrets, deployment-time inputs like region and environment, post-infrastructure setup like Key Vault secrets and external locations, post-DAB work like Unity Catalog grants and job schedules, and architectural choices the orchestrator couldn't make (network posture, schemas, partitioning). Every entry comes from something the article left unstated, plus the universal post-deploy work for any Databricks deployment. The operator works through `TODO.md` sequentially, resolving each item before the system is production-ready.
SPEC.md	The structured, source-faithful read of the blog article, organized by checklist. Every item is marked as a stated value, inferred from code or diagrams, or "not stated in article." It includes architecture details, Azure services configuration, Databricks setup, data model, security and identity requirements, and observations. `SPEC.md` is the single source of truth that Terraform and DAB generators read from, `TODO.md` is populated from every "not stated" entry, and `README.md` references it for assumptions. This ensures the deployment is built on documented decisions, not hidden assumptions.

Together, these files create a clear boundary:

SPEC.md answers what the blog says,
TODO.md captures what's missing or must be decided,
README.md tells you exactly how to deploy.

This split is enforced by validation rules that fail if any content duplicates across the three files.

To make these files as repeatable as possible, I needed two things:

Two templates, one for README.md and one for TODO.md, that the orchestrator fills in from SPEC.md at generation time.
A broader delivery contract, output-contract.md, which lists the five files the orchestrator must produce. README.md and TODO.md are two of those five, and the templates are how they get produced.

The output-contract.md file defines a strict, ordered format that the agent must follow when transforming a blog article about Databricks-on-Azure architecture into a runnable repository.

The first commit was deliberately minimal, as you can see from the file available here. No leaf-skill routing, no repo-context.md, no GitHub Actions workflows, no validation rules, no entry-field templates for TODO.md. That commit's single job was to lock down the shape of the output: what gets produced and in what order. Every commit since has refined how to produce that shape without changing what gets produced.

Putting the contract in the very first commit gave every later change a fixed reference point. Every leaf skill, generator script, and validation rule I've added since has fit into one of its five sections. The pipeline has changed; the deliverables haven't.

The structure of the GitHub repo at commit 17ab443 can be see in the pictorial below.

6. The `README.md` and `TODO.md` templates

After iteratively working on the orchestrator, a clear pattern emerged, the code-generation paths were kind of stable, but the documentation outputs weren't. Every run produced README.md and TODO.md from scratch in free-form Markdown. Across runs, the same content kept drifting.

Section ordering changed between runs and the explanation of GitHub Environments was rewritten with subtle wording differences. RBAC roles appeared sometimes as lists, sometimes in prose, sometimes split across sections. Universal post-deploy actions (create the secret scope, populate the vault, set up Unity Catalog grants) were re-derived every time, occasionally with steps missing. The root cause was that the orchestrator was treating durable, universal content as if it were per-run content.

So I've decided to add two templates: README.md.template and TODO.md.template. Templates separate universal content (RBAC, TODO sections, GitHub setup) in the template from per-workload content (catalog names, credentials) substituted from SPEC.md. This delivers consistency across runs. The README and TODO are structurally identical, so readers can navigate them intuitively. Universal content is correct by construction; I write it once, review carefully, and every run inherits that quality.

Validation also becomes more precise, and the agent's job shrinks from open-ended writing to mechanical substitution, which is easier to validate and maintain. Templates introduce clear vocabulary: {placeholder} is filled by the orchestrator at generation time, by the deployer at run time. Finally, templates enforce traceability: every "not stated in article" entry in SPEC.md automatically becomes a TODO entry via the from SPEC.md slot, making this an automatically-enforced rule.

I'm invoking the templates in the orchestrator as shown below. The Git commit with this code can be found at this link.

### 3.1 Generate README from template Load the template: `.github/skills/blog-to-databricks-iac/templates/README.md.template` ### 3.2 Generate TODO from template Load the template: `.github/skills/blog-to-databricks-iac/templates/TODO.md.template`

7. The output of the `fetch_blog.py` file and the interaction with the orchestrator

When the orchestrator invokes fetch_blog.py, the script produces a JSON output and passes it back to the orchestrator. The orchestrator then reads the JSON document into its working context and maps each field onto an analysis checklist. The title and meta description establish the article identity and scope. Headings with their levels reveal the structure, helping the agent locate sections about architecture, security, data flow, and naming. Paragraphs provide evidence for stated values like regions, resource types, and RBAC models. Code blocks become the source of inferred values. As an example, a Terraform snippet might reveal SKU choices or naming patterns not mentioned in the text.

These inferred values get tagged "inferred from code snippet" when recorded. The cloud hint acts as a sanity check that the article actually describes an Azure architecture.

For every checklist item, the agent records either an extracted value or the literal string "not stated in article". This becomes SPEC.md, the single source of truth for everything downstream. SPEC.md drives every subsequent step. Steps 3 through 7 (the Terraform module, workflows, and Databricks bundle generators) read architectural decisions from it. Step 8 then produces TODO.md by converting every "not stated in article" entry into a TODO item the operator must resolve before deployment.

What I find worth pointing out is how little the output contract has actually moved since that very first commit. The implementation underneath has changed completely. Leaf skills emerged, generator scripts came in, validation rules got added, a soft-delete state machine showed up to handle Key Vault recovery. None of those existed at the start. But what the orchestrator delivers, the list of files it puts on disk, has stayed exactly the same.

We have a much larger SKILL.md today that still mirrors the initial five-item output list. The contract itself has changed by exactly one line: the addition of "Design of the architecture" to section 5.

SPEC.md: the structured, source-faithful read of the article, organised by the analysis checklist ( link )
TODO.md: the operator's checklist of everything the article didn't specify, plus the universal post-deploy actions ( link )
Terraform code under infra/terraform/: the platform layer with networking, storage, identities, Key Vault, workspace ( link )
Databricks Asset Bundle under databricks-bundle/: the workload layer with jobs, entry points, environment configuration ( link )
README.md: the operator runbook, with the architecture design diagram embedded ( link )

If the JSON contains an error, the orchestrator stops immediately. Per the skill rule "If fetch fails, stop and return the fetch error output. Do not retry," the error surfaces to the user rather than propagating downstream.

So the script's output is the raw evidence pack: title, structure, prose, code, cloud hint. The agent uses it to fill the architecture spec, which parameterises every generated artifact.

At this point the fetch_blog.py output is sent to Step 2 of the orchestrator, as shown in the code snippet below.

### 2. Analyse article Analyse the fetched article against the structured checklist in `.github/skills/blog-to-databricks-iac/references/blog-analysis-checklist.md`. The analysis covers the article text, diagrams, screenshots, and code snippets.

And, much later in the orchestrator, Step 8 closes the loop by turning everything that's been recorded into the two operator-facing documents:

### 8. Generate README and TODO from templates Use the templates in `.github/skills/blog-to-databricks-iac/templates/`: - `README.md.template` -> `README.md` - `TODO.md.template` -> `TODO.md`

8. How this actually came together

What I've described so far is how the orchestrator works currently. The reality of building it was much cumbersome , but also fun.

I got from the first version to the current one by iterating. Rerun the orchestrator, find the defect, identify the rule that would have caught it, add the rule to the skill that owns the artifact, rerun.

The reason I'm calling this out now, before walking through the rest of the pipeline, is that everything from this point on is a story about a specific lesson learned that way. The leaf skills exist because a single SKILL.md got too dense. The restricted-tenant guardrails exist because the deployment failed against a tenant that couldn't read Microsoft Graph. The validation harness exists because prose rules weren't catching the regressions that mattered. The soft-delete state machine exists because the same vault name kept colliding with a previous deploy. None of these rules were present from day-one.

So in the next sections I'll walk through how the pipeline actually matured: how the single skill split into a graph, what the inner regenerate-fix loop felt like in practice, the day the project pivoted to support restricted tenants, the bugs that became rules, and the Key Vault soft-delete state machine that closed the project out.

9. From a single skill to a skill graph

When I started, everything lived inside a single SKILL.md. It was simpler that way, and to be honest, at that point I didn't yet know which rules would actually matter. But as I kept rerunning the orchestrator on the article, a pattern emerged. Each rerun produced something that broke in a slightly different way, and the fix always belonged to a very specific concern: Terraform authoring, bundle structure, workflow generation, or the orchestration logic itself. Stuffing the rules for all of them into one file was making the orchestrator unreadable and, worse, was silently dropping rules when the context window got tight.

So I split it. The orchestrator stayed at the top, kept routing the work and validating the result, and each concern got promoted to its own leaf skill. The Databricks bundle skill itself ended up needing one more split a few days later, it had got too dense, so I broke it into two leaves:

databricks-yml-authoring ( link )
Python-entrypoints ( link )

The diagram below shows the shape the repo has today.

The orchestrator now does almost no authoring. It owns the sequence of steps, the contract, and the validation gates, while everything else is delegated. This was the single biggest readability win. I wish I'd done it earlier.

The REPO_CONTEXT.md is one extra node in that diagram that I want to call out But I'll come back to later in section 12.

10. The inner loop: rerun, fail, fix the skill

If I had to describe the middle of this project in one sentence, it would be: every commit was a regeneration. I'd run the orchestrator end-to-end against the article, inspect the generated Terraform, the bundle, the workflows. I'd find a defect, identify the rule that would have prevented it, add that rule to the skill that owns the artifact, then rerun. As shown in the image below.

This loop is what I think people miss when they treat AI-generated infrastructure code as a one-shot. The first run is never the deliverable. The deliverable is the skill that produces good runs. The generated files are disposable and can always be reproduced. The skill is what carries the knowledge forward.

I had to actively resist the temptation to fix bugs in the generated code directly. Patching infra/terraform/main.tf by hand fixes today's run but not tomorrow's, because the rule that would prevent the bug doesn't exist anywhere. So I made it a discipline: never edit the output, always edit the skill, then regenerate.

11. Restricted-tenant compatibility

The bug was simple to describe and brutal to fix: the deployment principal in the target tenant couldn't read Microsoft Graph. Any Terraform data source that resolved an Entra name to an object ID at plan time (e.g., azuread_user, azuread_group, azuread_service_principal) blew up at terraform plan.

My first instinct was to think "I just give the principal Graph permissions". But in a lot of real environments this is not possible. The principal that runs your IaC is governed by a security team, the team has a policy, and the policy says no Graph reads.

The pivot was getting the skill to produce Terraform that never reads Graph. Object IDs are inputs, not lookups. They come in as trusted secrets, the workflow exports them as TF_VAR_*, and Terraform consumes them as variables. No data "azuread_*" block is allowed in the generated code, ever.

I thought this was a simple fix. It wasn't. It cascaded into about six other things:

App Registration vs Service Principal object IDs. The workflow was being given the wrong one. Role assignments need the Enterprise Application (Service Principal) object ID, not the App Registration object ID. The two are different objects in Entra with different IDs. I encoded the distinction in the skill as *_SP_OBJECT_ID (the Service Principal) versus *_CLIENT_ID (the App Registration's application ID). Naming carries the meaning now, so the wrong value is hard to pass.
Single-principal mapping. In some tenants you only have one principal and it has to play both deployment and runtime roles. The skill grew a layer_sp_mode = existing input so the generator stops trying to create a new Service Principal and reuses the deployment one instead.
Key Vault access policies, gone. Access policies were Graph-touching, and not all tenants support them anyway. The skill switched fully to RBAC role assignments (Key Vault Secrets User, and so on). A few cascading bugs followed, but this was the right call.

It took some time to harden the Terraform skill against everything the restricted tenant was throwing back. Each iterations had the same shape, each orchestrator runs, hits a fresh provider error, I add the rule, run again, hit the next one. The commit subjects from that run are basically a transcript of the conversation I was having with the platform.

12. The bugs that became rules

There are three bugs that I believe are worth telling the story of, because they each illustrate a slightly different lesson.

The HCL trim() arity bug. The generator emitted trim(var.something) in a validation block. HCL's trim() takes two arguments, not one. The function I actually wanted was trimspace(). This is the kind of bug that any human would catch in a code review in two seconds, and which the model produced confidently because the shape of the call looked right. I added the rule to the Terraform skill ("for whitespace trimming use trimspace, never trim") and the bug never came back.

Lesson: even for trivial syntactic mistakes, the fix belongs in the skill.

The variable shadowing bug. The deploy workflow had a job-level env: block that set TF_VAR_key_vault_recover_soft_deleted to a static value. A detection step earlier in the workflow was supposed to compute the right value at runtime and write it via $GITHUB_ENV. The problem is that GitHub Actions resolves job-level environment variables before $GITHUB_ENV writes take effect, so the static value always won and the dynamic one was silently ignored.

The fix was to never set the recovery flag at job level. It must be written in the detection step, on every code path, including the trivial "no recovery needed" path.

Lesson: state must be explicit, not inherited. If a flag has three possible meanings, three code paths must each write it.

The hardcoded -platform suffix. The workflow had a shell-side suffix that someone (let's be honest, the model) had invented to make the resource group name "look right". When recovery logic started running and the workflow looked for the canonical resource group, it looked for -platform instead of whatever the Terraform locals.tf actually emitted. The result was that the recovery handler was happily reaching past the real resource group and into a different one. I made it a rule in the orchestrator: workflow-invented suffixes are not permitted. Naming is owned by Terraform's locals.tf.

There are seventeen more defects in the catalogue, and the pattern is the same in every case. The bug surfaces, the rule gets written, the rule lives in the skill that owns the affected artifact.

There is no implementation-learnings.md in the repo. There used to be, but I've deleted it because a tracked log of past bugs, sitting next to a skill that's already supposed to encode the lessons from those bugs, is a duplication waiting to drift. I believe that if the rule is in the skill, the log is redundant. If the rule isn't in the skill, the log is an evidence that I haven't finished the work. Either way, the right place for bug history is git log.

13. Splitting "the skill" from "this repo's defaults"

I then wanted the orchestrator to be portable, but every run kept needing the same handful of decisions. Which Azure region by default? Which environment names? Which catalog naming convention? These weren't part of the article. They weren't part of the Terraform skill either. They were specific to this repository's opinion about how things should be deployed.

If I baked them into the orchestrator, the orchestrator stopped being portable. If I left them out, every run produced unhelpful "not stated in article" entries for the same five universal decisions.

The answer was a new file called REPO_CONTEXT.md stored in the repo root. It's read by the orchestrator before generation and it carries the defaults that are owned by the repo, not by the skill. The split looks like this in practice:

SKILL.md answers the question "how do I turn an article into a runnable repo?" It is portable.
REPO_CONTEXT.md answers the question "what does this repo default to when the article doesn't say?" It is local.

Cloning the orchestrator into another GitHub project is now a clean operation. You take the skill, you write your own REPO_CONTEXT.md, and the same generator produces output appropriate to your environment.

14. The Validations

Most of the rules I'd written into the skills were prose. "Don't invent suffixes." "Object IDs are inputs, not lookups." "Every required Terraform variable must have a matching TF_VAR_* in the workflow." The model is good at following prose rules most of the time.

So a few of the most regression-prone rules became executable. The most important one is scripts/validate_workflow_parity.sh. Every variable declared in infra/terraform/variables.tf must appear as a TF_VAR_* export in the deploy workflow. The script greps both files, diffs the sets, and exits non-zero if they don't match. It is run at the end of generation. If it fails, the run failed, even if everything else looks fine.

This caught real bugs. The most embarrassing was a variable I'd added to variables.tf and forgot to wire through the workflow. Terraform plan would prompt interactively for it on a non-interactive runner, and the run would hang.

The rule of thumb I've ended up with is: prose rules are the default, but if a rule has been violated more than twice, it gets promoted to an executable check. There's a short list of those checks now, and it's the load-bearing one.

15. Key Vault soft-delete state machine

Key Vaults in Azure have soft delete on by default. When you delete a vault, it sticks around for ninety days in a "soft-deleted" state. If you try to create a vault with the same name in the same subscription during that window, the deploy fails. The right behaviour is to recover the soft-deleted vault, not create a new one.

The first version of my recovery handler covered exactly one case: if the vault is soft-deleted, recover it. This worked the first time I ran it.

The second time, the recovered vault came back into the previous resource group, not the new one I had just created. Terraform then tried to create a new vault in the correct resource group and failed because the name was already taken globally. The handler had no concept of "the recovered vault is in the wrong resource group." So I added that case.

The third time, the previous resource group itself was gone, and the handler was looking for it to verify the move. So I added that case too.

By the end, the state machine had three distinct cases and two preconditions, as shown in the diagram below.

The reason I keep coming back to this state machine is that it captures something that I think is generally true about agent-generated infrastructure code. The happy path is easy and meaningless, while the value is in the failure modes. The first version that worked on a clean tenant was about ten lines of bash. The version that works on a tenant that has been deployed-into and partially-torn-down five times is six times longer, and every additional line of it corresponds to a real environmental condition that I had to learn the hard way.

16. What I've learned so far

I'm not going to pretend the full list of principles below was clear to me on day one. Every single one of these was learned by getting it wrong first. Looking back at the history, though, they are the ones that survived contact with reality.

The contract precedes the implementation. output-contract.md was committed before any generator existed. Locking the shape of the deliverable first meant every later change had a fixed reference point.
Generators, not stencils. Workflows are produced by Python scripts that take parameters and emit YAML. When restricted-tenant logic and the soft-delete state machine arrived, they needed conditional structure that a static template can't express.
Every bug becomes a rule. Patching the generated code is a tax on tomorrow's run. While patching the skill is an investment.
Each concern has a clear owner. The orchestrator routes, the leaves author, and the repo context holds the local defaults.
Restricted-tenant compatibility is non-negotiable. No Microsoft Graph reads from generated Terraform. Object IDs are trusted inputs. Single-principal mapping is supported.
Naming is owned by Terraform. No suffixes invented in shell. The validation harness enforces this.
State must be explicit, not inherited. Every workflow run writes its own flags. No reliance on env defaults from a previous step or a previous run.
Validation is executable when a rule has been violated more than twice. Prose rules are the default. Promotion to a script is earned.
Operator docs describe concepts, not commands. Command syntax ages out, while conceptual descriptions don't. The TODO template enforces this rule.
Add strong testing at the end of the process, once all the files are generated. Each run may produce slightly different output and introduce bugs, even if the previous run was successful.
End-to-end runs against dirty tenants are the truth. The acceptance test isn't a clean-room deploy. It's a deploy into a tenant that has soft-deleted vaults, lingering RGs, and existing role assignments. Until that works, the project isn't done.
From time to time, skills need to be reviewed and consolidated.

The summary above of the journey is the one I find most useful to share when people ask whether this approach actually goes anywhere. From an empty repo to a generator that produces a deployable, restricted-tenant-compatible infrastructure-as-code repository from a blog URL, with executable validation and a recovery state machine that survives a previously-deployed environment.

The first commit was an empty workspace. The last commit was the one where the same orchestrator, run against the same blog, against a tenant carrying state from five previous runs, deployed cleanly with no manual intervention.

That is what I what I was aiming to achieve when I started!

Thanks for reading.

Resilient by Design: Azure Databricks Disaster Recovery Strategy

KonstantinaF — Wed, 06 May 2026 16:28:58 GMT

Introduction: From Recovery Plans to Resilience Strategy

As organizations increasingly rely on Azure Databricks for mission-critical analytics and data engineering workloads, the need for robust disaster recovery (DR) strategies becomes paramount. These platforms are no longer just analytics engines, they power real-time decisions, AI models, and core business operations. Yet many organizations still approach Disaster Recovery (DR) as a reactive safeguard rather than a strategic capability. Resilience today is not about “if something fails,” but about ensuring continuity, trust, and performance under any condition.

A modern DR strategy must therefore evolve beyond backup configurations and failover scripts. It must align with business priorities, regulatory requirements, risk tolerance, and operational maturity to become a core pillar of the enterprise data platform. In this context, organizations are increasingly adopting architecture patterns that enable cross-region resilience for the Azure Databricks Lakehouse. This pattern includes synchronizing Unity Catalog objects—catalogs, schemas, tables, views, function, models, and volumes—across regions, combined with scalable data movement mechanisms and secure data access approaches such as Delta Sharing and high-performance transfer tools.

To help organizations operationalize this approach today, we have defined a structured strategy for synchronizing Unity Catalog objects and associated data across regions, enabling a resilient-by-design Azure Databricks architecture. This post focuses on that approach, outlining the key architectural patterns, strategic considerations, and practical implementation steps required to design and enable cross-region resilience.

In October 2025, Databricks announced a Managed Disaster Recovery solution, developed in collaboration with Capital One, which includes managed replication, customer-specified failover, and read-only secondary capabilities. The approach outlined in this post serves as a complementary, customer-managed pattern, providing a practical and production-ready path for organizations to achieve robust disaster recovery and business continuity while Databricks continues to expand its native DR capabilities.

Why Disaster Recovery for Azure Databricks is Different

Traditional Disaster Recovery approaches do not fully apply to modern Lakehouse platforms.

In Azure Databricks, resilience must account for:

Tight coupling between data, compute, and metadata (Unity Catalog)

Distributed pipelines (batch, streaming, ML)

Decentralized workspace ownership and rapid platform growth

This makes disaster recovery not just an infrastructure concern, but a data platform design challenge.

Figure 1. Main Disaster Recovery Considerations

Understanding the Fundamentals: RTO, RPO, and DR Trade-offs

Before defining a disaster recovery strategy, it is essential to understand the core concepts that drive design decisions.

Recovery Time Objective (RTO) defines how quickly a system must be restored after a disruption; while Recovery Point Objective (RPO) defines how much data loss is acceptable. These two metrics directly influence the architecture, cost, and complexity of any DR solution.

As illustrated in Figure 1, there is a clear trade-off between cost and recovery performance:

Active-active (hot) architectures, minimize downtime and data loss but come at a higher cost.

Warm standby provides a balance between cost and recovery time.

Cold DR is cost-efficient but results in longer recovery times and higher data loss risk.

Understanding these trade-offs is critical to aligning DR strategy with business expectations.

Understanding the Fundamentals: RTO, RPO, and DR Trade-offs

Before defining a disaster recovery strategy, it is essential to understand the core concepts that drive design decisions.

As illustrated in Figure 1, there is a clear trade-off between cost and recovery performance:

Active-active (hot) architectures, minimize downtime and data loss but come at a higher cost.

Warm standby provides a balance between cost and recovery time.

Cold DR is cost-efficient but results in longer recovery times and higher data loss risk.

Understanding these trade-offs is critical to aligning DR strategy with business expectations.

Designing for Resilience: A Phased Disaster Recovery Approach

Disaster recovery has evolved beyond a one-time setup into a structured, lifecycle-driven capability. Leading organizations design resilience intentionally, implement it systematically, and continuously validate it to ensure ongoing effectiveness.

The framework outlined below provides a practical and strategic approach to operationalizing disaster recovery in Azure Databricks environments, bridging the gap between architectural intent and true operational readiness.

Figure 2. Different Phases of Azure Databricks Disaster Recovery

Phase 1: Discovery & Assessment

A resilient disaster recovery strategy starts with clarity—yet in many Azure Databricks environments, that clarity is often missing. As platforms evolve, clusters multiply, jobs are duplicated, and data assets grow, making it increasingly difficult to answer a simple question: what do we actually have, and how critical is it?

The Discovery phase addresses this by establishing a single, authoritative view of the platform. By consolidating all assets, dependencies, and usage patterns into a structured baseline, organizations can move from fragmented visibility to informed decision-making.

This approach aligns closely with the concepts outlined in “From Chaos to Clarity: Your Databricks Workspace on a Single Pane of Glass”, where establishing a comprehensive inventory becomes the foundation for governance, optimization, and ultimately resilience.

This foundation enables teams to identify what matters most, define appropriate RTO and RPO targets, and understand the dependencies that will ultimately shape their disaster recovery strategy.

Outcome

A clear, data-driven baseline of the environment—enabling confident workload prioritization and effective disaster recovery design.

Phase 2: Strategy & Design

Once visibility is established, the next step is making deliberate design choices—balancing resilience, cost, and complexity. At this stage, organizations define how their platform should behave under failure. This typically starts with selecting a multi-site deployment pattern, in which two primary approaches are commonly adopted:

Active–Active, where both regions are fully operational and serve live workloads

Active–Passive (Warm Standby), where a secondary region is pre-provisioned and activated only during failover

Active–active architectures provide near-zero downtime and minimal data loss but come with increased cost and architectural complexity. Active–passive patterns offer a more cost-efficient alternative, with slightly higher recovery times depending on how failover is orchestrated. Beyond selecting the deployment pattern, a key architectural decision is how data is replicated across the Medallion architecture (Bronze, Silver, Gold). Our approach introduces a set of practical scenarios that allow organizations to tailor resilience based on both workload criticality and recovery requirements.

A common starting point is aligning the DR strategy to workload tiers, such as:

Tier 1 (Mission-critical): Active–Active with full replication

Tier 2 (Business-critical) : Active–Passive with partial replication

Building on this, organizations can further refine their approach by defining how data is replicated across the Medallion layers:

Full replication (Bronze, Silver, Gold) , i.e. fastest recovery at highest cost;

Bronze-only replication, lower cost, with re-computation required during recovery;

Gold-only replication, optimized for consumption-focused use cases.

This combination of workload tiering and Medallion replication strategies enables a flexible, fit-for-purpose approach to disaster recovery, which balances performance, cost, and operational complexity.

Below we demonstrate, as an example, two representative patterns: (a) Active–Active architecture, where data pipelines operate in continuous trigger mode across regions, enabling near real-time synchronization; and (b) Active–Passive architecture, where all layers are replicated using a clone-based approach and activated on demand during failover.

These scenarios highlight how organizations can balance recovery performance and cost by adjusting both the deployment model and the depth of data replication.

Figure 3. Active - Active Scenario - Continuous Trigger Mode

Within the active–passive model, multiple variations can be applied, ranging from full replication of all medallion layers to more selective approaches (such as replicating only Bronze or Gold layers). This flexibility allows organizations to further balance recovery performance, cost, and operational complexity.

Figure 4. Active - Passive Scenario - Clone All Layers Mode

Phase 3: Disaster Recovery Implementation & Enablement

With the strategy defined, the focus shifts to translating design into a repeatable and operational solution. At this stage, resilience is no longer conceptual, it is embedded into the platform through automation, data replication, and standardized deployment patterns.

From Strategy to Architecture

At a high level, the DR architecture spans both the primary and secondary Azure regions, ensuring that all critical components can be either replicated or recreated:

Control plane synchronization: Users, groups, and workspace assets are replicated using SCIM, Terraform, and CI/CD pipelines.

Workspace and metadata portability: Jobs, notebooks, and configurations are defined as code and deployed consistently across regions.

Data layer replication: Managed data, external data, and streaming checkpoints are synchronized using deep clone operations.

This layered approach ensures that the platform can be reconstructed end-to-end, not just partially recovered.

Unity Catalog-Driven Replication

A critical aspect of the implementation is the replication of Unity Catalog metadata and associated data assets. This includes:

Synchronizing catalogs, schemas, tables, views, functions, and volumes

Using Delta Sharing to expose datasets across regions

Leveraging deep clone and storage replication to ensure data availability

Recreating external and managed locations in the target region

By combining metadata synchronization with data replication, the target environment becomes a fully functional mirror of the source.

Figure 5. Unity Catalog Focused DR Mechanisms

Operationalizing with a DR Pipeline

To make this repeatable, the architecture is supported by a DR pipeline that orchestrates the process end-to-end:

Synchronize schemas and Unity Catalog structures

Perform deep clone of Delta tables

Recreate views and dependent objects

Provision volumes and copy associated data

Ensure consistency across storage layers (e.g., ADLS via AzCopy)

This pipeline can operate either continuously or on demand, depending on the selected DR pattern.

Figure 6. Azure Databricks DR Replication Workflow

Outcome

A fully implemented disaster recovery solution where data, metadata, and platform components are consistently synchronized, enabling rapid and reliable activation of workloads in a secondary region.

Phase 4: DR Drill: Validation, Operations & Continuous Improvement

A disaster recovery strategy is only valuable if it works when needed. This phase focuses on validating, operating, and continuously improving the DR solution to ensure it meets business expectations.

Failover & Failback in Practice

In a real failure scenario, the transition to the secondary region must be simple, predictable, and fast.

A typical failover process includes:

Detecting primary region unavailability

Executing a final synchronization (if possible)

Redirecting connections to the DR workspace

Resuming operations without requiring code changes

Equally important is failback, once the primary region is restored:

Re-synchronizing data from DR to primary

Switching pipelines and configurations back

Gradually restoring normal operations

Because infrastructure and metadata are standardized, this process becomes operational rather than reactive.

Operating DR as a Continuous Capability

Beyond failover, DR must be actively managed as part of daily operations:

Monitoring & Alerting: Track job failures, performance bottlenecks, and system health

Governance & Change Management: Maintain consistency between environments using IaC and version-controlled pipelines

Continuous Optimization: Adjust replication strategies, scaling, and performance as workloads evolve

This ensures the DR solution remains aligned with both technical and business changes over time.

Ensuring Performance, Integrity, and Security

A production-ready DR solution must also guarantee:

Performance & Scalability: Optimize compute, autoscaling, and data transfer to handle recovery scenarios efficiently

Data Integrity & Consistency: Validate schema synchronization, monitor replication jobs, and ensure parity between regions

Security & Compliance: Enforce consistent access controls, secure credentials, and enable audit logging across environments

Outcome

A validated and continuously evolving DR capability—where recovery processes are tested, monitored, and improved over time, providing confidence to both technical teams and business stakeholders.

Key Takeaways and Closing Thoughts

Resilience in modern data platforms is no longer defined by how quickly systems can recover, but by how effectively they are designed to withstand disruption in the first place. Azure Databricks, as a core engine for data, analytics, and AI, requires a disaster recovery approach that extends beyond infrastructure—one that treats data, metadata, pipelines, and governance as a unified system. By combining a structured discovery phase, a strategy aligned to workload criticality, and automated, repeatable implementation patterns, organizations can move from reactive recovery to resilience by design. This not only reduces risk, but also ensures that critical data workloads remain available, trusted, and performant when it matters most.

The approach outlined in this post provides a practical and flexible way to enable cross-region resilience today, while also complementing the managed disaster recovery capabilities expected to be introduced by Databricks. As we anticipate the availability of these native features, this approach offers a production-ready foundation that can extend and integrate with future platform capabilities. In a world where disruption is inevitable, the objective is no longer simply to recover—but to maintain continuity of data, decisions, and business operations with confidence.

Special thank you to Vasilis Zisiadis, Dimitris Kotanis who contributed their expertise to create this material and bring it to life.

Thank You Antony Bitar, Collin Brian and Jason Pereira for their support in reviewing the content.

From Chaos to Clarity: Your Databricks Workspace on a Single Pane of Glass

AmitDamle — Wed, 06 May 2026 16:25:47 GMT

The question that never stays answered — until now

As Azure Databricks workspaces evolve, complexity creeps in unnoticed. Every Azure Databricks conversation with customers eventually lands on the same question: “What do we actually have in this workspace?” Over time, clusters multiply, jobs get cloned, warehouses are spun up for one-off demos and forgotten, and Unity Catalog keeps expanding until it’s hard to reason about. In most enterprises, each business or data science team operates its own workspace, while the central platform or operations team has little to no visibility into what’s being created or why. Teams often spend days—or weeks—trying to piece together what exists, who owns it, and the business purpose behind it, only to realize they still don’t have the full picture. And when the same question comes up next quarter, the cycle starts all over again.

To address this, we built a utility that helps customers answer exactly that—by providing a single pane of glass for all Databricks assets through comprehensive cataloging and usage analysis. The utility works in two phases: Discovery and Analysis. This post focuses on the first step—the Discovery phase, where we establish a clear, authoritative inventory of everything that exists in the workspace.

What the Discovery Phase delivers?

Think of the Discovery phase as a workspace health assessment. Once configured against a target workspace, the utility runs in a selected mode and consolidates all discovered assets into a centralized, Delta-based repository. The result is a structured, queryable, and dashboard-ready metadata store.

Behind the scenes, ten purpose-built scanners run in a tiered and parallelized architecture, enabling a fast yet comprehensive scan of the entire workspace.

Scanner	What is Cataloged
Clusters	Interactive, job, SQL — configs, policies, pools
Jobs	Workflows, schedules, tasks, run history
Warehouses	SQL endpoints, sizes, serverless settings
Pipelines	Delta Live Tables and their state
Unity Catalog	Catalogs, schemas, tables, volumes
Workspace Objects	Notebooks, repos, ML experiments, serving endpoints, alerts, Genie spaces
Security	Identity, network, data protection settings
Billing	30–180 days of DBU usage by SKU and product
Utilization	Real CPU, memory, runtime patterns (deep scan)
Spark Job Optimizer (plugin)	Skew, spill, small files, broadcast hints (deep scan)

Design Overview

#	Block	Role	Contents / Flow
1	Source	Starting point — the Databricks environments being discovered.	One or more Azure Databricks workspaces. Auth via OAuth. Outputs an authenticated WorkspaceClient to the Orchestrator.
2	Orchestrator	The brain of the utility — coordinates scanning, concurrency, retries, timing.	Tiered thread-pool executor, scan config (mode, billing window, UC depth, max workers). Dispatches scanners in controlled waves.
3	Tier 1 Scanners	Lightweight, high-concurrency scans. Run first for quick signal.	Clusters, Warehouses, Pipelines, Security. Up to 12 workers, 10-min timeout. Artifacts flow to the Centralized Repository.
4	Tier 2 Scanners	High-volume scans. Controlled concurrency to avoid API throttling.	Jobs, Workspace Objects (notebooks, repos, experiments, serving, alerts, Genie), Unity Catalog, Billing (30–180 days DBU). 1/2 workers, 30-min timeout.
5	Tier 3 Scanners	Sequential, analysis-grade scans (deep scan only).	Utilization (CPU, memory, SQL usage patterns) and Spark Job Optimizer plugin (skew, spill, small files, broadcast hints). Runs after Tiers 1 & 2.
6	Centralized Repository	The catalog of truth — where all output lands, timestamped and queryable.	Unity Catalog Delta tables (dashboard-ready) plus portable JSON and CSV exports for offline sharing or downstream tools.
7	Single Pane of Glass	The user-facing view — insight at a glance.	Pre-built Lakeview dashboard: KPI strip, inventory charts, and week-over-week trends. Refresh to see current workspace state.

Why users love the view — visualization that earns its keep

This is where the Discovery phase stops being just a scan and starts becoming a decision-making tool. Because everything is consolidated into a single, Unity Catalog–backed source of truth, the Lakeview dashboard delivers a genuine single pane of glass for the entire Databricks workspace.

At a glance, you get:

KPI strip at the top — total clusters, active jobs, UC tables, SQL warehouses, DLT pipelines, workspace objects. One glance, one number each.

Inventory charts — clusters by type, jobs by schedule, warehouses by size, tables by catalog. The shape of your workspace becomes obvious.

The “that doesn’t look right” moments — The idle SQL warehouse with zero queries, the cluster running the wrong runtime, the notebook floating outside any repo. These surface instantly, without hunting. Change over time — because every scan is timestamped, you can literally see your platform grow (or sprawl) week over week.

In the first customer walkthrough, the platform team identified an always-on SQL warehouse with zero queries and three jobs running on the wrong compute tier—all within the first 30 minutes. That single view paid for the project.

Sample Item Catalog

Closing thoughts

The Discovery phase isn’t about governance for governance’s sake—it’s about clarity. Before teams can optimize costs, improve performance, or enforce standards, they first need a reliable answer to a basic question: what actually exists today? By giving platform and operations teams a single, authoritative view of all Databricks assets—grounded in data, not tribal knowledge—Discovery turns guesswork into informed decisions. In the next phase, Analysis, that foundation is used to go deeper: identifying inefficiencies, risks, and opportunities to simplify and optimize the platform. But it all starts here—by finally knowing what you have.

Special thank you to Antony Bitar, Collin Brian and Jason Pereira for their support in reviewing the content.

From Manual Backfills to Autonomous Pipelines: Building an LLM-Powered Backfill Agent on Azure

Sally_Dabbah — Tue, 05 May 2026 10:16:19 GMT

Introduction:

Data backfills are a common operational requirement in modern data platforms. Missing partitions, upstream delays, or failed pipeline runs often require engineers to manually identify gaps, determine the appropriate recovery window, and trigger reprocessing. This approach does not scale well it introduces operational overhead, increases the risk of human error, and requires deep knowledge of data dependencies and pipeline behavior.

In this post, I describe how to build a backfill agent using Azure AI Foundry, Model Context Protocol (MCP), Azure Functions, Synapse, and ADX. The goal is to automate the decision-making process while keeping execution controlled, observable, and governed.

The design separates responsibilities across three layers:

Decision layer: an LLM-based agent determines whether a backfill is required and defines the recovery scope (e.g., which dates, datasets, or layers)
Execution layer: an MCP server hosted on Azure Functions exposes controlled operations such as triggering pipelines and querying system state
State layer: ADX tables maintain backfill control metadata, data availability signals, and execution history

This separation keeps the system flexible while ensuring that all actions are traceable, auditable, and policy-driven.

Importantly, this pattern is not limited to a single dataset or pipeline. It can be applied across all datasets and across all layers of a medallion architecture Bronze, Silver, and Gold with layer-specific validation rules and backfill strategies. For example, Bronze may focus on completeness of ingestion, while Silver and Gold can enforce data quality and business logic constraints before initiating recovery.

The key benefit of a backfill agent is that it shifts backfilling from a manual, reactive process to an automated, intelligent, and consistent workflow. Instead of engineers investigating incidents and triggering reruns, the agent continuously evaluates data state, identifies gaps, and initiates-controlled recovery actions. This reduces operational burden, improves reliability, and ensures faster recovery from data issues while maintaining governance, observability, and strict control over execution.

Architecture Overview

The solution is designed as a controlled orchestration pattern that separates decision-making, execution, and state management. This allows backfill operations to be automated without compromising governance or observability.

The architecture consists of four main components.

Logic Apps Trigger

The workflow is initiated using a Logic App. The trigger can be scheduled or invoked on demand, depending on operational requirements.

It provides the input context required for the backfill evaluation, such as dataset name, processing layer, and scope constraints (for example, maximum number of dates to process).

Azure AI Foundry Agent (Decision Layer)

The Azure AI Foundry agent acts as the decision layer.

It evaluates the request and determines whether a backfill is required, and if so, what scope should be applied. The agent does not interact directly with data systems. Instead, it invokes predefined tools exposed through the MCP server.

This ensures that decision logic is flexible, while execution remains controlled.

Azure Function App – MCP Server (Execution Layer)

The Azure Function App hosts the MCP server and exposes a set of operations to the agent.

These operations include querying missing partitions, triggering Synapse pipelines, retrieving execution status, and updating control tables.

All interactions with external systems (Synapse and ADX) are handled within this layer. It is responsible for input validation, authorization, and enforcing execution rules.

This abstraction ensures that infrastructure actions are not directly performed by the agent.

Synapse Pipelines (Processing Layer)

Backfill execution is handled by a parameterized Synapse pipeline.

The pipeline follows a consistent pattern:

Data is first written to a staging table
Validation is performed
Data is promoted to the main table only if validation succeeds

This approach ensures data quality and prevents partial or invalid data from being published.

Azure Data Explorer(State and Observability Layer)

ADX is used as the central state store.

It maintains control and execution tables that track expected partitions, missing data, pipeline runs, and execution outcomes.

This enables:

Detection of missing partitions
Idempotent execution (avoiding duplicate processing)
Full traceability of backfill operations

The agent relies on this state, accessed via the MCP server, to make decisions.

End-to-End Flow

The Logic App triggers the workflow and passes the request context.
The Foundry agent evaluates the request.
The agent invokes an MCP tool to retrieve missing partitions from ADX.
Based on the result, the agent determines whether a backfill is required.
If required, the agent invokes an MCP tool to trigger the Synapse pipeline.
The pipeline executes the backfill using a staging and validation pattern.
Execution details are written to ADX.
The agent returns a summary of the operation.

Analytics Layer:

Azure Synapse Analytics:

In the Synapse workspace, I created a generic parameterized pipeline that has three steps:
1.Copy data from upstream and ingest it to ADX staging table

2. Run Data validation

3 ingest staging data to main dataset table.

the pipeline gets as a parameter dataset name, partitioning date, isbackfill flag and layer and ingest dataset into kusto table.

values for layer : Bronze,Silver or Gold.

Kusto:

In Kusto, the solution relies on the following tables:

Dataset tables for example, the Customers table in this demo [the same pattern can be extended to support multiple datasets.]
BackfillControl:
its the central configuration and decision input for the backfill process. It defines which dataset partitions require backfill and provides the metadata needed for the agent to make execution decisions.
each row in this table represents a specific dataset partition (for example, a given date in a specific layer) and its current backfill state.
BackfillExecutionLog :

this table is used to track the execution of backfill operations. It provides a complete record of when backfills were triggered, their outcome, and the associated pipeline runs, while the BackfillControl table defines what should be processed, the BackfillExecutionLog captures what actually happened.

Code for Creating the tables:
.create table BackfillExecutionLog ( ExecutionId: string, DatasetName: string, Layer: string, PartitionDate: datetime, PipelineName: string, PipelineRunId: string, TriggeredAt: datetime, TriggeredBy: string, ExecutionStatus: string, Reason: string ) .create table BackfillControl ( DatasetName: string, Layer: string, PartitionDate: datetime, BackfillRequired: bool, Status: string, DQStatus: string, RetryCount: int, MaxRetryCount: int, Reason: string )

Output examples:

In this demo, Logic Apps, Synapse, and Kusto are treated as existing systems; the focus is how to expose controlled MCP tools from an Azure Function App and connect them to Azure AI Foundry agent.

Microsoft’s Azure Functions MCP extension lets a Function App expose functions as MCP tools, and Foundry can connect to the deployed MCP endpoint.

Steps:

Step1: Create the local Function App project

in VS code, run the command:

mkdir backfill-kusto-mcp cd backfill-kusto-mcp func init . --worker-runtime python --python

Step2: Implement the MCP tools

add requirements to requirements.txt file:
azure-functions>=1.24.0 azure-identity azure-kusto-data requests python-dotenv
The host.json file defines runtime-level behavior for the Azure Function App.
In this implementation, it is used to configure the MCP extension, logging, and extension bundles.
{ "version": "2.0", "extensions": { "mcp": { "system": { "webhookAuthorizationLevel": "Anonymous" } } }, "logging": { "applicationInsights": { "samplingSettings": { "isEnabled": true, "excludedTypes": "Request" }, "enableLiveMetricsFilters": true } }, "extensionBundle": { "id": "Microsoft.Azure.Functions.ExtensionBundle.Experimental", "version": "[4.*, 5.0.0)" } }
The local.settings.json file is used to define environment-specific configuration for the Azure Function App during local development.

It contains application settings (environment variables) that are read by the Function App at runtime. These settings are not checked into source control and are replaced by App Settings in Azure after deployment.
For example:
{ "IsEncrypted": false, "Values": { "AzureWebJobsStorage": "UseDevelopmentStorage=true", "FUNCTIONS_WORKER_RUNTIME": "python", "KUSTO_CLUSTER": "https://<ClusterName>.<Region>.kusto.windows.net", "KUSTO_DATABASE": "<DatabaseName>", "BACKFILL_CONTROL_TABLE": "BackfillControl", "BACKFILL_EXECUTION_LOG_TABLE": "BackfillExecutionLog", "SYNAPSE_WORKSPACE": "<SynapseWorkspaceName>", "SYNAPSE_PIPELINE": "<PipelineName>", "AUTH_MODE": "az_login", "AZURE_CLIENT_ID": "", "DEFAULT_DATASET_NAME": "Customers", "DEFAULT_LAYER": "Bronze", "MAX_DATES_DEFAULT": "5" } }
For local development, AUTH_MODE is set to az_login.

Before deploying to Azure Functions, change AUTH_MODE to MANAGED_IDENTITY in the Function App application settings.
The function_app.py defines the main implementation of MCP server

ir:
- Exposes MCP tools (find_backfill_candidates, trigger_backfill, run_backfill_agent, get_backfill_execution_log)
- Reads configuration from environment variables
- Authenticates using Azure CLI (local) or Managed Identity (Azure)
- Queries BackfillControl in Kusto to identify missing partitions
- Triggers Synapse pipelines for backfill
- Writes execution results to BackfillExecutionLog
- Enforces idempotency by checking if a partition was already triggered
Code:

import os import uuid import json import logging from datetime import datetime, timezone from urllib.parse import quote import requests import azure.functions as func from azure.identity import ManagedIdentityCredential, AzureCliCredential from azure.kusto.data import KustoClient, KustoConnectionStringBuilder app = func.FunctionApp(http_auth_level=func.AuthLevel.ANONYMOUS) logging.basicConfig(level=logging.INFO) AUTH_MODE = os.getenv("AUTH_MODE", "MANAGED_IDENTITY").lower() KUSTO_CLUSTER = os.getenv("KUSTO_CLUSTER") KUSTO_DATABASE = os.getenv("KUSTO_DATABASE") CONTROL_TABLE = os.getenv("BACKFILL_CONTROL_TABLE", "BackfillControl") EXECUTION_LOG_TABLE = os.getenv("BACKFILL_EXECUTION_LOG_TABLE", "BackfillExecutionLog") SYNAPSE_WORKSPACE = os.getenv("SYNAPSE_WORKSPACE") SYNAPSE_PIPELINE = os.getenv("SYNAPSE_PIPELINE", "Customer Dataset") DEFAULT_DATASET_NAME = os.getenv("DEFAULT_DATASET_NAME", "Customers") DEFAULT_LAYER = os.getenv("DEFAULT_LAYER", "Bronze") def utc_now() -> str: return datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ") def log_event(event: str, **properties): logging.info( "MCP_BACKFILL %s", json.dumps( { "event": event, "timestamp_utc": utc_now(), **properties, }, default=str, ), ) def require_setting(name: str, value: str | None): if not value: raise ValueError(f"Missing required app setting: {name}") def escape_kusto_string(value: str | None) -> str: if value is None: return "" return str(value).replace("\\", "\\\\").replace('"', '\\"') def get_credential(): if AUTH_MODE == "az_login": return AzureCliCredential() managed_identity_client_id = os.getenv("AZURE_CLIENT_ID") if managed_identity_client_id: log_event( "using_user_assigned_managed_identity", client_id=managed_identity_client_id, ) return ManagedIdentityCredential(client_id=managed_identity_client_id) log_event("using_system_assigned_managed_identity") return ManagedIdentityCredential() def get_kusto_client() -> KustoClient: require_setting("KUSTO_CLUSTER", KUSTO_CLUSTER) if AUTH_MODE == "az_login": kcsb = KustoConnectionStringBuilder.with_az_cli_authentication(KUSTO_CLUSTER) else: managed_identity_client_id = os.getenv("AZURE_CLIENT_ID") if managed_identity_client_id: kcsb = KustoConnectionStringBuilder.with_aad_managed_service_identity_authentication( KUSTO_CLUSTER, client_id=managed_identity_client_id, ) else: kcsb = KustoConnectionStringBuilder.with_aad_managed_service_identity_authentication( KUSTO_CLUSTER ) return KustoClient(kcsb) def execute_kusto_query(query: str): require_setting("KUSTO_DATABASE", KUSTO_DATABASE) client = get_kusto_client() response = client.execute(KUSTO_DATABASE, query) return response.primary_results[0] def execute_kusto_command(command: str): require_setting("KUSTO_DATABASE", KUSTO_DATABASE) client = get_kusto_client() return client.execute_mgmt(KUSTO_DATABASE, command) def find_backfill_candidates_core( dataset_name: str, layer: str, max_dates: int, ) -> list[dict]: dataset = escape_kusto_string(dataset_name) layer_name = escape_kusto_string(layer) query = f""" {CONTROL_TABLE} | where DatasetName == "{dataset}" | where Layer == "{layer_name}" | where BackfillRequired == true | where RetryCount < MaxRetryCount | where Status in ("Missing", "Failed") or DQStatus == "Failed" | top {int(max_dates)} by PartitionDate asc | project DatasetName, Layer, PartitionDate, Status, DQStatus, RetryCount, MaxRetryCount, Reason """ rows = execute_kusto_query(query) return [ { "DatasetName": row["DatasetName"], "Layer": row["Layer"], "PartitionDate": str(row["PartitionDate"])[:10], "Status": row["Status"], "DQStatus": row["DQStatus"], "RetryCount": row["RetryCount"], "MaxRetryCount": row["MaxRetryCount"], "Reason": row["Reason"], } for row in rows ] def was_backfill_already_triggered( dataset_name: str, layer: str, partition_date: str, ) -> bool: dataset = escape_kusto_string(dataset_name) layer_name = escape_kusto_string(layer) query = f""" {EXECUTION_LOG_TABLE} | where DatasetName == "{dataset}" | where Layer == "{layer_name}" | where PartitionDate == datetime({partition_date}) | where ExecutionStatus == "Triggered" | summarize Count = count() """ rows = list(execute_kusto_query(query)) return bool(rows and rows[0]["Count"] > 0) def write_execution_log( execution_id: str, dataset_name: str, layer: str, partition_date: str, pipeline_name: str, pipeline_run_id: str, execution_status: str, reason: str, ): command = f""" .set-or-append {EXECUTION_LOG_TABLE} <| print ExecutionId = "{escape_kusto_string(execution_id)}", DatasetName = "{escape_kusto_string(dataset_name)}", Layer = "{escape_kusto_string(layer)}", PartitionDate = datetime({partition_date}), PipelineName = "{escape_kusto_string(pipeline_name)}", PipelineRunId = "{escape_kusto_string(pipeline_run_id)}", TriggeredAt = datetime({utc_now()}), TriggeredBy = "FoundryMCPBackfillAgent", ExecutionStatus = "{escape_kusto_string(execution_status)}", Reason = "{escape_kusto_string(reason)}" """ execute_kusto_command(command) def trigger_synapse_pipeline( dataset_name: str, layer: str, partition_date: str, ) -> str: require_setting("SYNAPSE_WORKSPACE", SYNAPSE_WORKSPACE) require_setting("SYNAPSE_PIPELINE", SYNAPSE_PIPELINE) credential = get_credential() token = credential.get_token("https://dev.azuresynapse.net/.default").token encoded_pipeline_name = quote(SYNAPSE_PIPELINE, safe="") url = ( f"https://{SYNAPSE_WORKSPACE}.dev.azuresynapse.net" f"/pipelines/{encoded_pipeline_name}/createRun" f"?api-version=2020-12-01" ) payload = { "DatasetName": dataset_name, "Layer": layer, "PartitionDate": partition_date, "IsBackfill": True, } response = requests.post( url, headers={ "Authorization": f"Bearer {token}", "Content-Type": "application/json", }, json=payload, timeout=30, ) log_event( "synapse_create_run_response", status_code=response.status_code, body=response.text[:2000], ) response_json = {} try: response_json = response.json() except Exception: pass if "runId" in response_json: return response_json["runId"] raise Exception( f"Synapse trigger failed. " f"StatusCode={response.status_code}. " f"Body={response.text}" ) def trigger_backfill_core( dataset_name: str, layer: str, partition_date: str, ) -> dict: execution_id = str(uuid.uuid4()) log_event( "trigger_backfill_started", execution_id=execution_id, dataset_name=dataset_name, layer=layer, partition_date=partition_date, ) try: if was_backfill_already_triggered(dataset_name, layer, partition_date): return { "ExecutionId": execution_id, "DatasetName": dataset_name, "Layer": layer, "PartitionDate": partition_date, "Status": "Skipped", "Reason": "Backfill was already triggered for this partition.", } pipeline_run_id = trigger_synapse_pipeline( dataset_name=dataset_name, layer=layer, partition_date=partition_date, ) write_execution_log( execution_id=execution_id, dataset_name=dataset_name, layer=layer, partition_date=partition_date, pipeline_name=SYNAPSE_PIPELINE, pipeline_run_id=pipeline_run_id, execution_status="Triggered", reason="Triggered by Foundry MCP backfill agent", ) return { "ExecutionId": execution_id, "DatasetName": dataset_name, "Layer": layer, "PartitionDate": partition_date, "PipelineName": SYNAPSE_PIPELINE, "PipelineRunId": pipeline_run_id, "Status": "Triggered", } except Exception as ex: error_message = str(ex) log_event( "trigger_backfill_failed", execution_id=execution_id, dataset_name=dataset_name, layer=layer, partition_date=partition_date, error=error_message, ) try: write_execution_log( execution_id=execution_id, dataset_name=dataset_name, layer=layer, partition_date=partition_date, pipeline_name=SYNAPSE_PIPELINE or "", pipeline_run_id="", execution_status="FailedToTrigger", reason=error_message, ) except Exception as log_ex: log_event( "failed_to_write_execution_log", execution_id=execution_id, original_error=error_message, log_error=str(log_ex), ) return { "ExecutionId": execution_id, "DatasetName": dataset_name, "Layer": layer, "PartitionDate": partition_date, "Status": "FailedToTrigger", "Error": error_message, } def run_backfill_agent_core( dataset_name: str, layer: str, max_dates: int, ) -> list[dict]: log_event( "run_backfill_agent_started", dataset_name=dataset_name, layer=layer, max_dates=max_dates, ) candidates = find_backfill_candidates_core( dataset_name=dataset_name, layer=layer, max_dates=max_dates, ) results = [] for candidate in candidates: result = trigger_backfill_core( dataset_name=candidate["DatasetName"], layer=candidate["Layer"], partition_date=candidate["PartitionDate"], ) results.append(result) log_event( "run_backfill_agent_completed", dataset_name=dataset_name, layer=layer ) return results def get_execution_log_core( dataset_name: str, limit: int, ) -> list[dict]: dataset = escape_kusto_string(dataset_name) query = f""" {EXECUTION_LOG_TABLE} | where DatasetName == "{dataset}" | top {int(limit)} by TriggeredAt desc | project ExecutionId, DatasetName, Layer, PartitionDate, PipelineName, PipelineRunId, TriggeredAt, TriggeredBy, ExecutionStatus, Reason """ rows = execute_kusto_query(query) return [ { "ExecutionId": row["ExecutionId"], "DatasetName": row["DatasetName"], "Layer": row["Layer"], "PartitionDate": str(row["PartitionDate"])[:10], "PipelineName": row["PipelineName"], "PipelineRunId": row["PipelineRunId"], "TriggeredAt": str(row["TriggeredAt"]), "TriggeredBy": row["TriggeredBy"], "ExecutionStatus": row["ExecutionStatus"], "Reason": row["Reason"], } for row in rows ] @app.mcp_tool() @app.mcp_tool_property(arg_name="dataset_name", description="Dataset name, for example Customers.") @app.mcp_tool_property(arg_name="layer", description="Layer name, for example Bronze.") @app.mcp_tool_property(arg_name="max_dates", description="Maximum number of dates to return.") def find_backfill_candidates( dataset_name: str = DEFAULT_DATASET_NAME, layer: str = DEFAULT_LAYER, max_dates: int = 5, ) -> list[dict]: return find_backfill_candidates_core(dataset_name, layer, max_dates) @app.mcp_tool() @app.mcp_tool_property(arg_name="dataset_name", description="Dataset name, for example Customers.") @app.mcp_tool_property(arg_name="layer", description="Layer name, for example Bronze.") @app.mcp_tool_property(arg_name="partition_date", description="Partition date in yyyy-MM-dd format.") def trigger_backfill( dataset_name: str, layer: str, partition_date: str, ) -> dict: return trigger_backfill_core(dataset_name, layer, partition_date) @app.mcp_tool() @app.mcp_tool_property(arg_name="dataset_name", description="Dataset name, for example Customers.") @app.mcp_tool_property(arg_name="layer", description="Layer name, for example Bronze.") @app.mcp_tool_property(arg_name="max_dates", description="Maximum number of dates to trigger.") def run_backfill_agent( dataset_name: str = DEFAULT_DATASET_NAME, layer: str = DEFAULT_LAYER, max_dates: int = 5, ) -> list[dict]: return run_backfill_agent_core(dataset_name, layer, max_dates) @app.mcp_tool() @app.mcp_tool_property(arg_name="dataset_name", description="Dataset name, for example Customers.") @app.mcp_tool_property(arg_name="limit", description="Maximum number of execution log rows to return.") def get_backfill_execution_log( dataset_name: str = DEFAULT_DATASET_NAME, limit: int = 10, ) -> list[dict]: return get_execution_log_core(dataset_name, limit)

Step3: . Run locally
1. Activate virtual environment:

python -m venv .sally-env .\.sally-env\Scripts\activate

2. Install dependencies :

pip install -r requirements.txt npm install -g azurite

3. Open 2 terminals, in one terminal run:

azurite

4. in the second terminal:

az login

start the function app:

func start

P.S make sure to change auth in local.settings.json file to

"AUTH_MODE": "az_login"

Step4: Create Azure resources and deploy

# LOGIN az login # VARIABLES $RG="rg-backfill-kusto-mcp-demo" $LOCATION="westeurope" $STORAGE="stbackfillmcp$((Get-Random -Minimum 10000 -Maximum 99999))" $FUNCAPP="func-backfill-kusto-mcp-$((Get-Random -Minimum 10000 -Maximum 99999))" # CREATE RESOURCE GROUP az group create --name $RG --location $LOCATION # CREATE STORAGE ACCOUNT az storage account create ` --name $STORAGE ` --resource-group $RG ` --location $LOCATION ` --sku Standard_LRS # CREATE FUNCTION APP az functionapp create ` --resource-group $RG ` --consumption-plan-location $LOCATION ` --runtime python ` --runtime-version 3.11 ` --functions-version 4 ` --name $FUNCAPP ` --storage-account $STORAGE ` --os-type Linux # ENABLE MANAGED IDENTITY az functionapp identity assign ` --resource-group $RG ` --name $FUNCAPP # GET PRINCIPAL ID $FUNC_PRINCIPAL_ID = az functionapp identity show ` --resource-group $RG ` --name $FUNCAPP ` --query principalId ` --output tsv Write-Host "Function App Principal ID: $FUNC_PRINCIPAL_ID" # CONFIGURE APP SETTINGS az functionapp config appsettings set ` --resource-group $RG ` --name $FUNCAPP ` --settings ` AUTH_MODE=MANAGED_IDENTITY ` KUSTO_CLUSTER="https://<ClusterName>.<Region>.kusto.windows.net" ` KUSTO_DATABASE="<DatabaseName>" ` BACKFILL_CONTROL_TABLE="BackfillControl" ` BACKFILL_EXECUTION_LOG_TABLE="BackfillExecutionLog" ` SYNAPSE_WORKSPACE="<SynapseWorkspaceName>" ` SYNAPSE_PIPELINE="<PipelineName>" ` DEFAULT_DATASET_NAME="Customers" ` DEFAULT_LAYER="Bronze" ` MAX_DATES_DEFAULT="5" # DEPLOY FUNCTION APP (RUN FROM PROJECT FOLDER) func azure functionapp publish $FUNCAPP # GET MCP ENDPOINT $MCP_ENDPOINT="https://$FUNCAPP.azurewebsites.net/runtime/webhooks/mcp" Write-Host "MCP Endpoint: $MCP_ENDPOINT" # GET MCP KEY $MCP_KEY = az functionapp keys list ` --resource-group $RG ` --name $FUNCAPP ` --query "systemKeys.mcp_extension" ` --output tsv Write-Host "MCP Key: $MCP_KEY" # TEST MCP TOOL $body = @{ jsonrpc = "2.0" id = "1" method = "tools/call" params = @{ name = "find_backfill_candidates" arguments = @{ dataset_name = "Customers" layer = "Bronze" max_dates = 1 } } } | ConvertTo-Json -Depth 10 Invoke-RestMethod ` -Uri $MCP_ENDPOINT ` -Method POST ` -Headers @{ Accept = "application/json, text/event-stream" "x-functions-key" = $MCP_KEY } ` -ContentType "application/json" ` -Body $body

After deployment, the Function App’s managed identity must be granted the appropriate permissions in both Kusto and Synapse with Function app principal id , this allows the Function App to query Kusto tables and trigger Synapse pipelines without issues.

Step5: . Connect the MCP server to Azure AI Foundry

Go to Azure AI Foundry portal
Navigate to your Project
Open your Agent
Add MCP as a tool :
- Go to Tools
- Click Add Tool
- Select:
Custom → Model Context Protocol (MCP)

Configure custom MCP and click on Save
MCP endpoint:
https://<function-app-name>.azurewebsites.net/runtime/webhooks/mcp

Step6: Define Agent instructions

You are a Backfill Reliability Agent. You MUST use the backfill_agent MCP tool. Do NOT ask the user for candidate dates. When asked to run backfill: Find dataset name 1. Call find_backfill_candidates with dataset_name layer max_dates 2. Then call run_backfill_agent with dataset_name, layer= max_dates Return the PipelineRunId.

Note:
The instructions are very generic; you need to modify it based on your business scenario.

Step7: Test prompt

Now in Synapse Monitor:
Search for PipelineRunId: df1b1920-09dd-415b-bbe9-d810d8505f58:

Future Enhancements:

The backfill agent automates recovery by detecting missing or failed data and triggering controlled reprocessing via MCP.

It can scale across all datasets and medallion layers (Bronze, Silver, Gold) with layer-specific rules.

The design can evolve into a multi-agent workflow for example, if backfill fails multiple times, a notification agent can automatically send emails or create incidents for upstream teams.

Overall, this shift backfilling from a manual, reactive task to an automated, governed, and intelligent data operations process.

Links:
Tutorial: Host an MCP server on Azure Functions | Microsoft Learn

Quickstart: Set up Microsoft Foundry resources - Microsoft Foundry | Microsoft Learn

Quickstart: Connect Azure Data Explorer to an Azure Synapse Analytics workspace - Azure Synapse Analytics | Microsoft Learn

Would love to hear your Feedback: Sally Dabbah | LinkedIn

Step by Step Guide to Ontology and Plan for Financial Service

NaufalPrawironegoro — Sun, 19 Apr 2026 15:06:24 GMT

What We Will Build

In this guide, we will construct a complete Fabric IQ solution that accomplishes the following:

First, a Lakehouse that ingests publicly available data including bank financials, P2P lending statistics, borrower demographics, and licensing information. Second, a Semantic Model that defines the analytical layer with proper dimensions, measures, and relationships. Third, an Ontology that elevates these tables into business entities such as Bank, P2P Platform, Borrower, and Loan, connected by meaningful relationships and governed by regulatory rules. Fourth, a Planning sheet that enables supervisors to forecast enforcement workloads, allocate examination budgets, and model scenarios based on live data.

Step 1: Preparing the Data Foundation in Fabric Lakehouse

Every Fabric IQ solution begins with data. Before we can model business semantics or build planning sheets, we need a well structured Lakehouse that holds our source data in a governed and queryable format.

Creating the Lakehouse

Navigate to your Fabric workspace and create a new Lakehouse. In this example, we have named it P2PLendingLH, housed within the workspace P2P Lending CrossSector Demo. The Lakehouse serves as the Bronze and Silver layer of our medallion architecture, storing both raw ingested data and transformed analytical tables.

Data Sources and Tables

The Lakehouse is populated with data from publicly available publications. The table structure follows a dimensional modeling pattern with clear separation between dimension tables (prefixed with dim_) and relationship tables (prefixed with rel_). The following tables form the foundation of our model:

Table Name	Description
dim_bank	Bank profiles including KBMI tier, total assets, CAR, NPL, channeling exposure percentage
dim_borrower	Borrower demographics with credit score, employment type, province, and risk segment
dim_p2p_platform	Licensed P2P lending operators with TWP90 rate, outstanding balance, and total borrowers
dim_loan	Individual loan records with amount, tenure, interest rate, and repayment status
dim_supervisor_team	supervisory teams and their regional assignments
dim_channeling_agreement	Bank to P2P channeling contracts and exposure limits

In addition to dimension tables, several relationship tables capture the connections between entities. These include rel_bank_channels_platform (which bank funds which P2P platform), rel_borrower_takes_loan (linking borrowers to their loans), rel_loan_funded_by_bank (tracing the funding chain), rel_platform_issues_loan (connecting platforms to the loans they originate), and rel_supervisor_oversees_platform and rel_supervisor_oversees_bank (mapping supervisory responsibility).

Step 2: Creating the Semantic Model

With data in the Lakehouse, the next step is to create a Semantic Model that defines the analytical interface. The Semantic Model is a Power BI construct that organizes your tables into a star schema with proper relationships, hierarchies, and measures. More importantly for our purpose, this Semantic Model will later serve as the blueprint from which we generate our Ontology.

Generating the Model from Lakehouse

From within the Lakehouse, click on "New semantic model" in the toolbar. A dialog appears allowing you to name your model and select which tables to include. In our case, we select all dimension and relationship tables to ensure the Ontology will have full visibility into the data landscape.

Figure 1. Creating a new Direct Lake semantic model from the P2PLendingLH Lakehouse, selecting dimension and relationship tables for inclusion.

Notice that the dialog shows the workspace name (P2P Lending CrossSector Demo) and provides a searchable list of all available tables. The Direct Lake mode is automatically selected, which means the Semantic Model will query data directly from the Lakehouse parquet files without importing a copy. This is important for our use case because it ensures that when regulator publishes updated monthly statistics and the Lakehouse is refreshed, the Semantic Model and subsequently the Ontology will reflect the latest data.

Configuring Relationships and Properties

After creation, the Semantic Model opens in the editing view where you can configure relationships, add calculated measures, and define display properties. The model view shows the entity cards with their fields and the lines connecting related tables.

Figure 2. The Semantic Model editor showing entity cards for dim_bank and dim_borrower, with relationship lines and the full table listing in the Data panel.

In the screenshot above, you can see two of the core dimension tables. The dim_bank table contains fields such as bank_id, bank_type, channeling_exposure_pct, channeling_total, name, regulator_team, and total_assets. The dim_borrower table holds borrower_id, credit_score, employment_type, name, province, and risk_segment. The Data panel on the right reveals the complete set of tables available in this model, including all the relationship tables that define the connections between entities.

At this stage, you should verify that all necessary relationships are correctly established. For example, dim_bank should connect to rel_bank_channels_platform through bank_id, and dim_p2p_platform should connect to rel_platform_issues_loan through platform_id. These relationships are what enable the Ontology to reason across domains in the next step.

You may also want to add calculated measures at this point, such as a weighted average TWP90 across all platforms funded by a specific bank, or a total channeling exposure as a percentage of the bank's total assets. These measures will be carried forward into the Ontology and can be used by AI agents for natural language querying.

Step 3: Generating the Ontology

This is the step where the magic of Fabric IQ truly comes alive. The Ontology transforms your Semantic Model from a reporting layer into an intelligence layer. While the Semantic Model answers the question "what does the data look like," the Ontology answers the question "what does the data mean."

What the Ontology Does

An Ontology in Fabric IQ is a machine understandable vocabulary of your business. It consists of entity types (the things in your environment, such as Bank, Borrower, or P2P Platform), properties (the facts about those entities, such as a bank's NPL ratio or a platform's TWP90 rate), and relationships (the ways entities connect, such as a Bank channels funding to a P2P Platform). Beyond static modeling, the Ontology also supports rules and constraints that can trigger automated actions when business conditions are met.

Generating from the Semantic Model

To create the Ontology, open your Semantic Model and look for the "Generate Ontology" button in the toolbar. Clicking it opens the generation dialog, which presents three key value propositions:

Unify models into a semantic layer allows you to align concepts across domains and modeling paradigms, bringing banking data and P2P lending data into a shared vocabulary.

Model expressively enables you to capture complex relationships, domain specific rules, and actions that drive business workflows, such as triggering an alert when a P2P platform's TWP90 crosses the 5 percent regulatory threshold.

Reason over events and temporal patterns means that the Ontology can use sequences and trends to inform decisions and automation, such as detecting three consecutive months of TWP90 deterioration.

Figure 3. The Ontology generation dialog, creating a new Ontology named NewP2P from the existing Semantic Model within the P2P Lending CrossSector Demo workspace.

In the dialog, you specify the workspace (P2P Lending CrossSector Demo) and give your Ontology a name (in this example, NewP2P). After clicking Create, Fabric IQ analyzes the Semantic Model's structure, identifies entity types from dimension tables, infers relationships from the foreign key connections, and generates a navigable graph that represents your business domain.

Enriching the Ontology with Rules

Once the Ontology is generated, you can enrich it with business rules that reflect regulatory requirements. For the P2P lending use case, the following rules are particularly relevant:

Rule Name	Condition	Action
Elevated TWP90	P2P Platform TWP90 exceeds 5 percent	Flag platform as high risk and alert PVML supervisor
Contagion Risk	Bank channeling exposure to flagged P2P platform exceeds 10 percent of portfolio	Alert Banking supervisor and recommend joint examination
Youth Overleveraged	Borrowers aged 19 to 34 represent more than 60 percent of a platform's portfolio AND TWP90 is above average	Trigger consumer protection review and education program allocation
CAR Threshold	Bank CAR drops below 10 percent while having active P2P channeling agreements	Escalate to Kepala Eksekutif Pengawas Perbankan

These rules integrate with Fabric Activator, enabling the Ontology to automatically initiate business processes through alerts and automated actions. This means that when new monthly P2P statistics are ingested and a platform's TWP90 crosses the threshold, the system does not wait for an analyst to discover it manually. The rule fires, the alert is sent, and the supervisory workflow begins.

Querying with Natural Language

One of the most powerful capabilities enabled by the Ontology is the ability to query across domains using natural language through a Data Agent. Because the Ontology defines the business vocabulary and binds it to real data, a supervisor can ask questions like:

"Which banks have channeling agreements with P2P platforms whose TWP90 is currently above 5 percent, and what is their total exposure?"

The Data Agent resolves this query by traversing the Ontology graph: from the Bank entity through the channels_funding_to relationship to P2P Platform, filtering by the TWP90 property, and aggregating the channeling_total measure.

Step 4: Setting Up Planning Sheets

While the Ontology tells you what is happening in your business right now, the Plan item in Fabric IQ helps you decide what should happen next. Planning in Fabric IQ brings budgeting, forecasting, and scenario modeling directly into the same environment where your data lives, eliminating the disconnect between analytical insights and forward looking decisions.

Creating a Planning Sheet

To create a Plan, navigate to your workspace and select New Item followed by Plan (preview). After naming the plan and connecting it to your Semantic Model, you can begin building Planning sheets that pull dimensions and measures directly from the same data that powers your Ontology.

In the screenshot below, we see a Planning sheet named "Planning P2P" that presents a tabular view of all P2P lending platforms alongside their key risk metrics.

Figure 4. The Planning sheet showing P2P lending platforms with their TWP90 rates, total outstanding balances (in trillions of Rupiah), total borrower counts (in thousands), and risk categories.

The Planning sheet is structured with the platform name and risk_category as row dimensions, and three critical measures as values: Sum of twp90_rate, Sum of total_outstanding (displayed in trillions of Rupiah), and Sum of total_borrowers (displayed in thousands). The risk_category column provides an immediate visual classification of each platform's health status, with categories such as Elevated and Very High clearly indicating where supervisory attention should be directed.

Looking at the data, several insights emerge immediately. DanaBijak and DanaCepat both carry a Very High risk category, with TWP90 rates of 18.77 and 17.79 respectively. CashWagon ID shows an Elevated risk designation despite a comparatively modest TWP90 of 8.26, likely due to its substantial outstanding balance of 144.97 thousand borrowers. The aggregate row at the top reveals the industry total: a combined TWP90 of 365.38 (this is a sum across all platforms), total outstanding of 29.86 trillion Rupiah, and 7,281.55 thousand borrowers across the monitored universe.

Using Planning for Supervisory Resource Allocation

The real power of the Planning sheet becomes apparent when supervisors begin using it for forward looking decisions. Consider the following scenarios that can be modeled directly within the Planning interface:

Enforcement Forecasting: Based on the current data showing multiple platforms in the Very High risk category, supervisors can forecast the expected volume of warning letters and administrative sanctions for the coming quarter. If historical patterns show that each Very High platform typically receives two to three rounds of correspondence before resolution, the planning sheet can project staffing requirements for the enforcement team.

Budget Allocation: The Planning sheet can incorporate budget dimensions alongside risk metrics. If the current quarterly examination budget allows for on site visits to 15 platforms, the risk category column helps prioritize which platforms should be visited first. The forecast capability can then project whether the budget is sufficient given the current risk trajectory, or whether a reallocation request should be submitted.

Microsoft Fabric Operations Agent Step by Step Walkthrough

NaufalPrawironegoro — Sat, 18 Apr 2026 07:00:41 GMT

Fabric Capacity and Workspace

You need a Microsoft Fabric workspace backed by a paid capacity. Trial capacities are not supported for Operations Agent. Your capacity must be provisioned in a supported region. As of April 2026, Operations Agent is available in all Microsoft Fabric regions except South Central US and East US. If your capacity is outside the US or EU, you will also need to enable cross geo processing and storage for AI through the tenant settings.

Your workspace must contain an Eventhouse with at least one KQL database. The Eventhouse is the telemetry backbone, and the KQL database holds the tables the agent will monitor. In the screenshot below, you can see a workspace named OperationAgent-WS that contains an Eventhouse (ops_eventhouse), two KQL databases (ops_db and ops_eventhouse), and a Lakehouse (ops_lakehouse). This is the environment used throughout this guide.

Figure 1. Workspace contents showing the Eventhouse, KQL databases, and Lakehouse ready for the Operations Agent.

Enabling the Operations Agent in the Admin Portal

A Fabric administrator must enable the Operations Agent preview toggle in the Admin Portal before anyone in the organization can create an agent. Navigate to the Admin Portal, locate the section for Real Time Intelligence, and find the setting labeled Enable Operations Agents (Preview). Toggle it to Enabled for the entire organization or for specific security groups depending on your governance requirements.

In addition to this toggle, ensure that Microsoft Copilot and Azure OpenAI Service are also enabled at the tenant level. The Operations Agent relies on Azure OpenAI to generate its playbook and to reason about data when conditions are met.

Figure 2. The Admin Portal showing the Enable Operations Agents (Preview) toggle set to Enabled for the entire organization.

Note that messages sent to Operations Agents are processed through the Azure AI Bot Service. If your capacity is outside the EU Data Boundary, data may be processed outside your geographic or national cloud boundary. Be sure to communicate this to your compliance stakeholders before enabling the feature in production tenants.

Microsoft Teams Account

Every person who will receive recommendations from the agent must have a Microsoft Teams account. The Operations Agent delivers its findings and action suggestions through a dedicated Teams app called Fabric Operations Agent. You can install this app from the Teams app store by searching for its name. Once installed, the agent will be able to send messages containing data summaries and recommended actions directly to the designated recipients.

Creating and Configuring the Operations Agent

With your prerequisites in place, you are ready to create the Operations Agent. The following steps walk you through the entire configuration process using the Fabric portal.

Step 1: Create a New Operations Agent

Open the Microsoft Fabric portal and navigate to your workspace. On the Fabric home page, select the ellipsis icon and then select Create. In the Create pane, scroll to the Real Time Intelligence section and select Operations Agent. A dialog will appear asking you to name your agent and select the target workspace. Choose a descriptive name that reflects the agent’s purpose. In this guide, the agent is named OperationsAgent_1 and is deployed to the OperationAgent-WS workspace.

Step 2: Define Business Goals and Agent Instructions

Once the agent is created, you are taken to the Agent Setup page. This page is divided into two halves. On the left side, you configure the agent’s behavior. On the right side, you see the generated Agent Playbook after saving.

The first field is Business Goals, where you describe the high level objective the agent should accomplish. Write this in clear, outcome oriented language. In this demo, the business goal is set to:

“Monitor data pipeline execution and alert on failures.”

The second field is Agent Instructions, where you provide more specific guidance on how the agent should reason about the data. Think of this as a brief you would hand to an analyst who will be watching your systems overnight. Be explicit about the table name, the column to watch, and the condition that constitutes an alert. In this demo, the instruction reads:

“Monitor pipeline_runs table. Alert when status is failed.”

Together, the business goals and instructions give the underlying large language model enough context to generate an accurate playbook. The more specific your instructions, the more reliable the agent’s behavior will be.

Figure 3. The Agent Setup page showing business goals, agent instructions, and the generated playbook on the right.

On the right side of the screen, you can see the Agent Playbook that was generated after saving. The playbook includes a Business Term Glossary, which shows the business objects the agent inferred from your goals and data. In this case, it identified an object called PipelineRun, mapped to the pipeline_runs table, with two properties: status (the pipeline run status from the status column) and runId (the unique identifier from the run_id column). It also displays the Rules section, which contains the conditions the agent will evaluate.

Review the playbook carefully. Since it is generated by an AI model, there may be occasional misinterpretations. Verify that every property maps to the correct column and that the rules reflect your intended thresholds. If something is off, update your goals or instructions and save again to regenerate the playbook.

Step 3: Add a Knowledge Source

Scroll down on the Agent Setup page to find the Knowledge section. This is where you connect the agent to the data it will monitor. When you first open this section, it will display a message indicating that no knowledge source has been added yet.

Figure 4. The Knowledge section before any data source has been added.

Select the Add Data button to browse the available data sources. A panel will appear listing the KQL databases and Eventhouses accessible within your Fabric environment. In this demo, three sources are available: ops_db in the OperationAgent-WS workspace, wms_eventhouse in the WMS-CDC-Demo workspace, and ops_eventhouse in the OperationAgent-WS workspace. Select the database that contains the table you want the agent to monitor. For this guide, select ops_db, which holds the pipeline_runs table referenced in the agent instructions.

Figure 5. Selecting the knowledge source from available KQL databases and Eventhouses.

Once the knowledge source is connected, the agent will be able to query this database at regular intervals (approximately every five minutes) to evaluate its rules. Make sure the table in your selected database is actively receiving data, especially if you plan to demonstrate the agent detecting a condition in real time.

Step 4: Define Actions

Actions are the responses the agent can recommend when it detects a condition that matches its rules. Scroll further down the Agent Setup page to find the Actions section. Select the Add Action button to define a new custom action.

A dialog titled New Custom Action will appear. It has three fields. The Action Name is a short, descriptive label for the action. The Action Description explains the purpose of the action and gives the agent context about when to use it. The Parameters section allows you to define input fields that pass dynamic values (such as names, dates, or identifiers) into the Power Automate flow that will be triggered.

Figure 6. The New Custom Action dialog where you define the action name, description, and optional parameters.

In this demo, the action is named Send Email Alert with a description indicating that it should send an email notification when a pipeline failure is detected. Once created, you can see the action listed in the Actions section with a green status indicator showing that the action is successfully connected.

Figure 7. The Actions section showing the Send Email Alert action with a connected status.

Step 5: Configure the Custom Action with Power Automate

After creating the action, you need to configure it by linking it to an activator item and a Power Automate flow. Select the action you just created to open the Configure Custom Action pane.

In this pane, you will see several fields. First, select the Workspace where the activator item resides. In this demo, the workspace is OperationAgent-WS. Next, select the Activator, which is the Fabric item that bridges the Operations Agent and Power Automate. Here, the activator is named Email_Alert_Activator.

Once the connection is created, a Connection String is generated. This string is a unique identifier that links the Operations Agent to the Power Automate flow. Select the Copy button to copy this connection string to your clipboard. You will need it in the next step.

Below the connection string, you will find the Open Flow Builder button. Select this to launch the Power Automate flow designer where you will build the email notification flow.

Figure 8. The Configure Custom Action pane showing the workspace, activator, connection string, and the button to open the flow builder.

Step 6: Build the Power Automate Flow

When you select Open Flow Builder, a new browser tab opens with the Power Automate designer. The flow is pre-configured with a trigger called When an Activator Rule is Triggered. This trigger fires whenever the Operations Agent approves an action.

In the Parameters tab of the trigger, you will see a field labeled Connection String. Paste the connection string you copied from the previous step into this field. This is the critical link that connects the Power Automate flow back to your Operations Agent. If this string is incorrect or missing, the flow will not fire when the agent recommends the action.

Figure 9. The Power Automate flow builder with the activator trigger and the Connection String field.

Below the trigger, you can add any actions your workflow requires. For an email alert scenario, add an Office 365 Outlook action to send an email to the operations team. You can use dynamic content from the trigger to include details such as the pipeline run ID, the failure status, and any parameters passed through from the Operations Agent.

Save the flow and return to the Fabric portal. Your action is now fully configured and ready to be triggered by the agent.

Step 7: Generate the Playbook and Start the Agent

With all configuration complete (business goals, instructions, knowledge source, and actions), select Save on the Agent Setup page. Fabric will use the underlying large language model to generate the agent’s playbook. The playbook is a structured summary of everything the agent knows: its goals, the properties it monitors, and the rules it evaluates.

You can also select Generate Playbook at the top of the page to regenerate the playbook if you have made changes. Review the playbook one final time to confirm that properties map correctly to your table columns and that rules reflect the exact conditions you want to monitor.

When you are satisfied, select Start in the toolbar at the top of the page. The agent will begin actively monitoring your data. It queries the knowledge source approximately every five minutes, evaluating the playbook rules against the latest data. If a condition is met, the agent uses the LLM to summarize the data, generate a recommendation, and send a message to the designated recipients through Microsoft Teams.

To pause the agent at any time, select Stop. This is useful during demos when you want to control the timing of the demonstration.

How the Agent Operates at Runtime

Once started, the Operations Agent follows a continuous loop. Every five minutes, it queries the connected KQL database to evaluate the rules defined in the playbook. If no conditions are met, it continues silently. If a condition is matched (for example, a pipeline run with a status of "failed" appears in the pipeline_runs table), the agent proceeds through the following sequence.

First, the agent uses the large language model to analyze the data that triggered the condition. It summarizes the context, identifies the relevant business object (such as a specific pipeline run), and determines which action to recommend.

Second, the agent sends a message to the designated recipients through Microsoft Teams. This message contains a summary of the detected insight, the data context that triggered it, and a suggested action. Recipients can approve the action by selecting Yes or reject it by selecting No. If parameters are included (such as a run ID or a severity level), they can be reviewed and adjusted before final approval.

Third, if the recipient approves the action, the agent executes it on behalf of the creator using the creator’s credentials. In this demo, approving the action would trigger the Power Automate flow that sends an email alert.

It is important to note that if a recommendation is not responded to within three days, the operation is automatically canceled. After cancellation, the action can no longer be approved or interacted with.

Azure Managed Redis & Azure Databricks: Real-time Feature Serving for Low-Latency Decisions

Jason_Pereira — Wed, 25 Mar 2026 16:00:00 GMT

This blog content has been a collective collaboration between the Azure Databricks and Azure Managed Redis Product and Product Marketing teams.

Executive summary

Modern decisioning systems, fraud scoring, payments authorization, personalization, and step-up authentication, must return answers in tens of milliseconds while still reflecting the most recent behavior. That creates a classic tension: lakehouse platforms excel at large-scale ingestion, feature engineering, governance, training, and replayable history, but they are not designed to sit directly on the synchronous request path for high-QPS, ultra-low-latency lookups. This guide shows a pattern that keeps Azure Databricks as the primary system for building and maintaining features, while using Azure Managed Redis as the online speed layer that serves those features at memory speed for real-time scoring.

The result is a shorter and more predictable critical path for your application: the Payment API (or any online service) reads features from Azure Managed Redis and calls a model endpoint; Azure Databricks continuously refreshes features from streaming and batch sources; and your authoritative systems of record (for example, account/card data) remain durable and governed. You get real-time responsiveness without giving up data correctness, lineage, or operational discipline.

What each service does

Azure Databricks is a first-party analytics and AI platform on Azure built on Apache Spark and the lakehouse architecture. It is commonly used for batch and streaming pipelines, feature engineering, model training, governance, and operationalization of ML workflows. In this architecture, Azure Databricks is the primary data and AI platform environment where features are defined, computed, validated, published, as well as where governed history is retained.

Azure Managed Redis is a Microsoft‑managed, in‑memory data store based on Redis Enterprise, designed for low‑latency, high‑throughput access patterns. It is commonly used for traditional and real‑time caching, counters, and session state, and increasingly as a fast state layer for AI‑driven applications. In this architecture, Azure Managed Redis serves as the online feature store and speed layer: it holds the most recent feature values and signals required for real‑time scoring and can also support modern agentic patterns such as short‑ and long‑term memory, vector lookups, and fast state access alongside model inference.

Business story: real-time fraud scoring as a running example

Consider a payment system that must decide to approve, decline, or step-up authentication in tens of milliseconds—faster than a blink of an eye! The decision depends on recent behavioral signals, velocity counters, device changes, geo anomalies, and merchant patterns, combined with a fraud model. If the online service tries to compute or retrieve those features from heavy analytics systems on-demand, the request path becomes slower and more variable, especially at peak load. Instead, Azure Databricks pipelines continuously compute and refresh those features, and Azure Managed Redis serves them instantly to the scoring service. Behavioral history, profiles, and outcomes are still written to durable Azure datastores such as Delta tables, and Azure Cosmos DB so fraud models can be retrained with governed, reproducible data.

The pattern: online feature serving with a speed layer

The core idea is to separate responsibilities. Azure Databricks owns “building” features, ingest, join, aggregate, compute windows, and publish validated governed results. Azure Managed Redis owns “serving” features, fast, repeated key-based access on the hot path. The model endpoint then consumes a feature payload that is already pre-shaped for inference. This division prevents the lakehouse from becoming an online dependency and lets you scale online decisioning independently from offline compute.

Pseudocode: end-to-end flow (online scoring + feature refresh)

The pseudocode below intentionally reads like application logic rather than a single SDK. It highlights what matters: key design, pipelined feature reads, conservative fallbacks, and continuous refresh from Azure Databricks.

# ---------------------------- # Online scoring (critical path) # ---------------------------- function handleAuthorization(req): schemaV = "v3" keys = buildFeatureKeys(schemaV, req) # card/device/merchant + windows feats = redis.MGET(keys) # single round trip (pipelined) feats = fillDefaults(feats) # conservative, no blocking payload = toModelPayload(req, feats) score = modelEndpoint.predict(payload) # Databricks Model Serving or an Azure-hosted model endpoint decision = policy(score, req) # approve/decline/step-up emitEventHub("txn_events", summarize(req, score, decision)) # async emitMetrics(redisLatencyMs, modelLatencyMs, missCount(feats)) return decision # ----------------------------------------- # Feature pipeline (async): build + publish # ----------------------------------------- function streamingFeaturePipeline(): events = readEventHubs("txn_events") ref = readCosmos("account_card_reference") # system of record lookups feats = computeFeatures(events, ref) # windows, counters, signals writeDelta("fraud_feature_history", feats) # ADLS Delta tables (lakehouse) publishLatestToRedis(feats, schemaV="v3") # SET/HSET + TTL (+ jitter) # ----------------------------------- # Training + deploy (async lifecycle) # ----------------------------------- function trainAndDeploy(): hist = readDelta("fraud_feature_history") labels = readCosmos("fraud_outcomes") # delayed ground truth model = train(joinPointInTime(hist, labels)) register(model) deployToDatabricksModelServing(model)

Why it works

This architecture works because each layer does the job it is best at. The lakehouse and feature pipelines handle heavy computation, validation, lineage, and re-playable history. The online speed layer handles locality and frequency: it keeps the “hot” feature state close to the online compute so requests do not pay the cost of re-computation or large fan-out reads. You explicitly control freshness with TTLs and refresh cadence, and you keep clear correctness boundaries by treating Azure Managed Redis as a serving layer rather than the authoritative system of record, with durable, governed feature history and labels stored in Delta tables and Azure data stores such as Azure Cosmos DB.

Design choices that matter

Cost efficiency and availability start with clear separation of concerns. Serving hot features from Azure Managed Redis avoids sizing analytics infrastructure for high‑QPS, low‑latency SLAs, and enables predictable capacity planning with regional isolation for online services. Azure Databricks remains optimized for correctness, freshness, and re-playable history while the online tier scales independently by request rate and working set size.

Freshness and TTLs should reflect business tolerance for staleness and the meaning of each feature. Short velocity windows need TTLs slightly longer than ingestion gaps, while profiles and reference features can live longer. Adding jitter (for example ±10%) prevents synchronized expirations that create load spikes.

Key design is the control plane for safe evolution and availability. Include explicit schema version prefixes and keep keys stable by entity and window. Publish new versions alongside existing ones, switch readers, and retire old versions to enable zero‑downtime rollouts.

Protect the online path from stampedes and unnecessary cost. If a hot key is missing, avoid triggering widespread re-computation in downstream systems. Use a short single‑flight mechanism and conservative defaults, especially for risk‑sensitive decisions.

Keep payloads compact so performance and cost remain predictable. Online feature reads are fastest when values are small and fetched in one or two round trips. Favor numeric encodings and small blobs, and use atomic writes to avoid partial or inconsistent reads during scoring.

Reference architecture notes (regional first, then global)

Start with a single-region deployment to validate end-to-end freshness and latency. Co-locate the Payment API compute, Azure Managed Redis, the model endpoint, and the primary data sources for feature pipelines to minimize round trips. Once the pattern is proven, extend to multi-region by deploying the online tier and its local speed layer per region, while keeping a clear strategy for how features are published and reconciled across regions (often via regional pipelines that consume the same event stream or replicated event hubs).

Operations and SRE considerations

Layer	What to Monitor	Why It Matters	Typical Signals / Metrics
Online service (API / scoring)	End‑to‑end request latency, error rate, fallback rate	Confirms the critical path meets application SLAs even under partial degradation	p50/p95/p99 latency, error %, step‑up or conservative decision rate
Azure Managed Redis (speed layer)	Feature fetch latency, hit/miss ratio, memory pressure	Indicates whether the working set fits and whether TTLs align with access patterns	GET/MGET latency, miss %, evictions, memory usage
Model serving	Inference latency, throughput, saturation	Separates model execution cost from feature access cost	Inference p95 latency, QPS, concurrency utilization
Azure Databricks feature pipelines	Streaming lag, job health, data freshness	Ensures features are being refreshed on time and correctness is preserved	Event lag, job failures, watermark delay
Cross‑layer boundaries	Correlation between misses, latency spikes, and pipeline lag	Helps identify whether regressions originate in serving, pipelines, or models	Redis miss spikes vs pipeline delays vs API latency

Monitor each layer independently, then correlate at the boundaries. This makes it clear whether an SLA issue is caused by online serving pressure, model inference, or delayed feature publication, without turning the lakehouse into a synchronous dependency.

Putting it all together

Adopt the pattern incrementally. First, publish a small, high-value feature set from Azure Databricks into Azure Managed Redis and wire the online service to fetch those features during scoring. Measure end-to-end impact on latency, model quality, and operational stability. Next, extend to streaming refresh for near-real-time behavioral features, and add controlled fallbacks for partial misses. Finally, scale out to multi-region if needed, keeping each region’s online service close to its local speed layer and ensuring the feature pipelines provide consistent semantics across regions.

Sources and further reading

Azure Databricks documentation: https://learn.microsoft.com/en-us/azure/databricks/

Azure Managed Redis documentation (overview and architecture): https://learn.microsoft.com/azure/redis/

Azure Architecture Center: Stream processing with Azure Databricks: https://learn.microsoft.com/azure/architecture/reference-architectures/data/stream-processing-databricks

Databricks Feature Store / feature engineering docs (Azure Databricks): https://learn.microsoft.com/azure/databricks/

Announcing the New Home for the Azure Databricks Blog

AnaviNahar — Wed, 18 Mar 2026 18:44:42 GMT

We’re excited to share that the Azure Databricks blog has moved to a new address on Microsoft Tech Community Hub!

Azure Databricks | Microsoft Community Hub

Our new blog home is designed to make it easier than ever for you to discover the latest product updates, deep technical insights, and real-world best practices directly from the Azure Databricks product team. Whether you're a data engineer, data scientist, or analytics leader, this is your go-to destination for staying informed and inspired.

What You’ll Find on the New Blog

At our new address, you can expect:

Latest Announcements – Stay up to date with new features, capabilities, and releases
Best Practice Guidance – Learn proven approaches for building scalable data and AI solutions
Technical Deep Dives – Explore detailed walkthroughs and architecture insights
Customer Stories – See how organizations are driving impact with Azure Databricks

Why the Move?

This new blog gives us the flexibility to deliver a better reading experience, improved navigation, and richer content dedicated to Azure Databricks. It also allows us to bring you more frequent updates and more in-depth resources tailored to your needs.

Stay Connected

We encourage you to bookmark the new blog and check back regularly. Even better—follow along so you never miss an update. By staying connected, you’ll be among the first to hear about new features, performance improvements, and expert recommendations to help you get the most out of Azure Databricks.

👉 Follow the new Azure Databricks blog today and stay ahead with the latest announcements and best practices.

We’re looking forward to continuing this journey with you—now at our new home!

Check out the latest blogs if you haven’t already:

• Introducing Lakeflow Connect Free Tier, now available on Azure Databricks | Microsoft Community Hub

•Near–Real-Time CDC to Delta Lake for BI and ML with Lakeflow on Azure Databricks | Microsoft Community Hub

Smart Pipelines Orchestration: Designing Predictable Data Platforms on Shared Spark

Sally_Dabbah — Sun, 08 Feb 2026 09:21:51 GMT

Introduction

In mature data platforms, scaling compute is rarely the primary challenge. Shared, elastic Spark pools already provide sufficient processing capacity for most workloads. The harder problem is achieving predictable execution when multiple pipelines compete for the same resources.

In Azure Synapse, Spark pools are commonly shared across pipelines to optimize cost and utilization. While this model is efficient, it introduces a key limitation: execution order is determined by scheduling behavior, not business priority.

This post describes an orchestration pattern that makes priority explicit, allowing critical workloads to run predictably on shared Spark compute without modifying Spark code, configuration, or cluster capacity.

Goal

This work does not aim to optimize Spark performance.
Its goal is to ensure that, when pipelines share a Spark pool:

latency-sensitive workloads run first
heavy backfills do not delay critical pipelines
execution order is deterministic under contention

All of this needed to be achieved without changes to Spark configuration, notebook logic, or cluster size.

Why This Problem Occurs

In a naïve orchestration model, pipelines are triggered in parallel. From Spark’s perspective:

all jobs are equivalent
all jobs attempt to acquire executors at the same time
scheduling decisions are based on availability and timing

As a result, priority is implicit and often incorrect. A heavy workload may acquire executors before a lightweight but critical one simply because it requests more resources earlier.

This behavior is expected from Spark. The issue lies in orchestration, not in compute.

Core Concept: Priority as Execution Ordering

In shared Spark platforms, priority is enforced through execution ordering, not compute tuning.

The orchestration layer controls when workloads are admitted to shared compute. Once execution begins, Spark processes each workload normally.

This preserves Spark’s execution model while providing deterministic workload ordering.

Step 1: Workload Classification

In the demo presented in this blog, workloads are classified during configuration based on business impact:

Category	Description	Priority example
Light (critical)	SLA sensitive dashboard and downstream consumers	High priority , low resource weight(data volume)
Medium (High)	Core reporting workloads	Medium priority
Heavy(Best Effort)	Backfills and historical computes	Low priority, high resource weight(data volume)

This classification is external to Spark and external to code. It represents business intent, not implementation.

As a future phase, classification can be automated,for example, an agent may adjust priority based on observed failure rates or execution stability.
Workload classification is expressed as orchestration metadata, for example:

[ {"name":"ExecDashboard","pipeline":"PL_Light_ExecDashboard","weight":1,"tier":"Critical"}, {"name":"FinanceReporting","pipeline":"PL_Medium_FinanceReporting","weight":3,"tier":"High"}, {"name":"Backfill","pipeline":"PL_Heavy_Backfill","weight":8,"tier":"BestEffort"} ]

What Runs in Each Workload Category

All pipelines execute on the same shared Spark pool, but the work they perform differs in scope, data volume, and sensitivity to contention.

Light workloads power SLA-sensitive dashboards and downstream consumers. Their notebooks perform targeted reads with strong filtering, limited joins, and small aggregations. Execution time is short, and overall pipeline duration is dominated by executor availability rather than computation.

Medium workloads represent core reporting and analytics logic. These notebooks process larger datasets, perform joins across multiple sources, and apply aggregations that are more expensive than Light workloads but still time-bounded and business-critical.

Heavy workloads are best-effort pipelines such as backfills and historical recomputation. Their notebooks scan large data volumes, apply expensive transformations, and are optimized for throughput rather than responsiveness. These workloads tolerate delay but place significant pressure on shared compute when admitted concurrently.

All workloads use the same Spark pool, executor configuration, and runtime. The distinction reflects business intent and execution characteristics, not Spark tuning.

Example notebooks for each category are available in the accompanying GitHub repository.

Step 2: Naïve Orchestration (Baseline)

The following pipeline run illustrates the baseline behavior when all workloads are triggered in parallel against a shared Spark pool.

All Light, Medium, and Heavy pipelines are admitted concurrently. Executor acquisition and execution order depend on timing rather than business priority, resulting in non-deterministic behavior under contention.

Although Light workloads require minimal compute, they are delayed by executor contention caused by Medium and Heavy pipelines entering the Spark pool at the same time.

Step 3: Smart Orchestration (Priority-Aware)

Orchestration Model

The same child pipelines and notebooks are reused.
The parent pipeline enforces admission order:

Light (Critical)
Medium (High)
Heavy (Best Effort)

Dependencies control admission to the Spark pool. Parallelism is preserved within a priority class.

Effect on Shared Spark

Light workloads enter the Spark pool without contention
Medium workloads run after Light completes
Heavy workloads are intentionally delayed
Executor acquisition aligns with business priority

Light pipelines execute first and complete before medium pipelines are admitted. Heavy workloads run last by design. No Spark configuration changes are introduced.

The Spark pool, notebooks, and executor configuration are identical to the naïve run. Only the orchestration graph differs.

Step 4: Impact on Light Workloads

Light workloads are particularly sensitive to orchestration because their runtime is dominated by queueing time, not computation.

Comparing the naïve and priority-aware runs shows that Spark execution time is unchanged, but pipeline duration improves due to earlier admission to the Spark pool and immediate executor access

Naïve Execution

Spark execution time: short and unchanged
Pipeline duration: minutes under contention
Delay caused by executor unavailability

Smart Execution

Spark execution time: unchanged
Pipeline duration closely matches compute time
Immediate access to executors

The improvement comes from removing admission contention, not from increasing resources.

Results and Performance

Compared to naïve orchestration, priority-aware orchestration ensures that Light workloads complete in minutes rather than tens of minutes under contention, while Spark execution time itself remains unchanged. Heavy workloads no longer delay latency-sensitive pipelines, and execution order is deterministic across runs. These improvements are achieved solely by controlling admission to the shared Spark pool, without modifying Spark configuration, notebook logic, or cluster capacity.

Next Steps:

1. Optimizing Heavy Workloads

Once heavy workloads are isolated by priority, they can be optimized independently:

retries with backoff
tolerance for transient failures
increased executor counts or larger pools

Without admission control, these optimizations increase contention, with smart orchestration, they do not impact critical pipelines.

2. Moving Beyond Static Classification

In this implementation, workload classification is static and configuration-driven, which is sufficient for stabilization.

A next phase is adaptive classification:

collect execution metrics and failure rates
detect unstable pipelines
reclassify pipelines that exceed thresholds (e.g., >20% failures in a rolling window)

This prevents unstable workloads from impacting critical execution paths and makes the pipeline reliable with minimal maintenance.

3. Assisted Classification with Copilot agent

At scale, priority decisions benefit from automation. A Copilot-style agent can use historical execution data to recommend classification changes, grounding decisions in observed behavior while keeping engineers in control.

Example: Changing workload classification from Light to Medium

Consider a pipeline initially classified as Light because it powers an SLA-sensitive dashboard and typically executes quickly with minimal resource usage.

Over time, execution telemetry shows a change in behavior:

The pipeline fails in 4 of the last 10 runs due to transient Spark errors
Average duration has increased by 3×, even when admitted early
Retry attempts amplify contention for other Light workloads

Based on these signals, an automated agent flags the workload as unstable and recommends reclassifying it from Light to Medium.

After reclassification:

The pipeline is admitted after Light workloads but before Heavy workloads
It no longer blocks latency-critical paths when retries occur
Execution remains predictable, while instability is isolated from critical workloads

The notebook logic and Spark configuration remain unchanged, only the workload’s admission priority is updated via orchestration metadata.

This approach allows the platform to adapt to changing workload characteristics while preserving deterministic execution for critical pipelines.

Conclusion

Parallel execution is a default, not a strategy. In shared environments, orchestration must explicitly encode business intent rather than relying on scheduler behavior.

Enforcing priority at the orchestration layer restores predictability without sacrificing efficiency and provides a foundation for adaptive, policy-driven execution as platforms evolve.

Building a Reliable Real Time Data Pipeline with Microsoft Fabric

NaufalPrawironegoro — Tue, 27 Jan 2026 01:34:03 GMT

The Two Pillars That Determine Success

Data Quality Cannot Be an Afterthought

The most common mistake we see is treating data quality as something to address after the pipeline is running. This approach creates technical debt that compounds over time and erodes trust in your data.

Your CDC pipeline will ingest millions of events daily. Without proper validation at each layer, small issues become major problems. A single source system changing a column from integer to string can silently corrupt downstream analytics for days before anyone notices.

What you need to implement from day one:

Validation at the Bronze layer should focus on structural integrity. Every record landing in your raw layer needs verification that required fields exist, timestamps are valid, and CDC operation types are recognized.
The Silver layer is where business validation happens. Here you check referential integrity, apply domain specific rules, and flag anomalies. A customer ID that does not exist in your customer master table needs to be caught here.
Schema drift detection deserves special attention. Source systems change without warning. Your pipeline needs to detect these changes before they break downstream processes.
The quality score approach works well in practice. Rather than binary pass or fail checks, calculate a quality score for each batch. A score above 95 percent proceeds normally. Between 90 and 95 percent sends a warning. Below 90 percent halts processing.

Replication Lag Requires Active Management

The second pillar is understanding and managing replication lag. In a real time pipeline, the value of data degrades rapidly with age. A five minute delay might be acceptable for daily reporting but catastrophic for fraud detection or inventory management.

Lag accumulates at multiple points in your pipeline. There is capture lag between when a change occurs in the source database and when the CDC mechanism detects it. Processing lag occurs within Eventstream as events are transformed and routed. Ingestion lag happens between Eventstream and your destination tables. Each component adds latency, and under load, these delays compound.

Building effective lag management:

Monitor each stage independently. Knowing your total lag is useful, but knowing where lag accumulates is actionable.
Establish baselines before setting alerts. Collect at least two weeks of baseline metrics before configuring alert thresholds.
Implement automatic recovery procedures. When lag exceeds acceptable thresholds, your system should respond without waiting for human intervention.

Operational Foundations You Cannot Skip

Capacity Planning Prevents Expensive Surprises

Microsoft Fabric uses a capacity unit model where all workloads draw from a shared pool. Underprovisioning leads to throttling and failed jobs. Overprovisioning wastes budget. Start with realistic estimates based on your data volumes.

The F4 SKU handles most development and small production workloads comfortably. Medium deployments with 10 to 25 sources typically need F8. Large enterprise deployments should start at F16 and scale based on observed utilization. Watch for sustained utilization above 70 percent as a signal to consider scaling up.

Network Security Shapes Your Architecture

For production deployments handling sensitive data, network isolation is not optional. Private endpoints keep traffic on the Microsoft backbone network, eliminating exposure to the public internet. Plan your network architecture before building pipelines. Retrofitting private connectivity into an existing deployment is significantly more complex than designing it from the start.

Logging Enables Troubleshooting

When something goes wrong at 2 AM, your ability to diagnose the problem depends entirely on what information you captured beforehand. Centralized logging using Eventhouse gives you a queryable record of everything that happened across your pipeline. Log more than you think you need initially. Storage is inexpensive compared to the cost of troubleshooting without adequate information.

Key Decisions You Need to Make

Before proceeding with implementation, your team should align on several important decisions.

Data retention requirements affect storage costs and query performance. How long do you need to keep Bronze layer data versus aggregated Gold layer data?
Recovery time objectives determine how you architect for resilience. If the pipeline can be down for four hours without business impact, your approach differs from a scenario where even 15 minutes causes significant problems.
Who owns data quality shapes how you design validation and alerting. If source system teams are responsible, your pipeline detects and reports. If your team owns quality, you implement correction and enrichment.

Moving Forward

The patterns and practices in this guide reflect lessons learned from real implementations. Every organization has unique requirements, but the fundamentals of data quality, lag management, capacity planning, and operational readiness apply universally.

Start with the foundations. A pipeline that handles one source reliably is more valuable than one that theoretically handles fifty but fails unpredictably. Build observability from day one. Automate responses to common problems. Document what you learn.

Your data is a strategic asset. The pipeline that delivers it reliably deserves the same careful engineering you would apply to any critical business system.

A Technical Implementation Guide for Multi-Store Retail Environments

NaufalPrawironegoro — Thu, 22 Jan 2026 08:39:19 GMT

Understanding the Problem Space

When organizations first approach multi-source data ingestion, they typically start with explicit configuration. Each database connection is defined individually, each table mapping is specified, and each destination partition is created manually. This works reasonably well for five or ten sources. It becomes painful at twenty. It becomes nearly unmanageable at fifty or more.

The operational cost manifests in several ways. Every new store requires a ticket to the data engineering team. Someone must configure the connection, verify the schema compatibility, set up the ingestion pipeline, create the destination structures, and validate the data flow. In a fast-growing retail operation, this creates a bottleneck that delays time-to-insight for new locations.

Beyond the immediate operational burden, there is also the risk of configuration drift. When each source is configured individually, small inconsistencies creep in over time. One store might have slightly different table names. Another might be ingesting an extra column that was added during a schema migration. These inconsistencies compound, making the overall system harder to maintain and debug.

The solution presented here eliminates most of this manual work by implementing two complementary patterns: automatic source detection through regex-based CDC configuration, and dynamic partition creation through Delta Lake's native capabilities.

Architecture Overview

The proposed architecture places Debezium as the CDC engine, reading from PostgreSQL databases and publishing change events to Azure Event Hubs. Fabric EventStream consumes these events and writes them to a Delta Lake table in the Lakehouse. The key insight is that neither Debezium nor Delta Lake requires explicit enumeration of every data source. Both can operate on patterns rather than explicit lists.

At the source layer, Debezium connects to PostgreSQL and monitors the write-ahead log for changes. Rather than configuring a separate connector for each store database, a single connector is configured with a regex pattern that matches all store databases. When a new database is created that matches this pattern, Debezium automatically begins capturing its changes without any configuration update.

At the destination layer, Delta Lake tables are defined with partition columns but without explicit partition values. When a record arrives with a previously unseen partition value, Delta Lake automatically creates the necessary directory structure and begins writing data to the new partition. No DDL statement is required, no manual intervention is needed.

The combination of these two behaviors creates an end-to-end pipeline where adding a new store database is as simple as creating the database itself. Everything downstream happens automatically.

Implementation Details

Configuring Debezium for Automatic Source Detection

The critical configuration element in Debezium is the database include list parameter. Rather than specifying each database explicitly, this parameter accepts a regular expression that defines which databases should be monitored.

For a retail environment where store databases follow a naming convention such as store_001, store_002, and so forth, the configuration would specify a pattern like store_.* as the include list. This pattern matches any database whose name begins with store_ followed by any characters. When the DBA creates store_058, Debezium detects this new database during its periodic metadata refresh and automatically begins capturing changes from it.

The connector configuration should also specify which tables within each database to monitor. In most retail scenarios, the schema is standardized across all stores, so this can be a fixed list such as public.transactions, public.inventory, and public.products. If schema variations exist between stores, additional filtering logic may be required, but this is generally a sign that the source systems need standardization rather than accommodation of inconsistency.

The topic routing configuration is equally important. All CDC events should be routed to a single Event Hubs topic, with the store identifier included in the message payload. This allows a single EventStream to process all store data while preserving the ability to identify which store each record originated from. The source metadata that Debezium includes with each change event contains the database name, which serves as the natural store identifier.

Initial snapshot behavior must be considered carefully. When Debezium detects a new database, it will perform an initial snapshot to capture the current state before beginning to track incremental changes. For a store with substantial historical data, this snapshot may take anywhere from a few minutes to several hours. During this period, the connector is occupied with the snapshot and may exhibit increased latency for change events from other databases. Scheduling new store database creation during off-peak hours helps mitigate this impact.

Configuring Delta Lake for Dynamic Partitioning

Delta Lake supports dynamic partition creation as a default behavior. When a table is created with partition columns specified, any INSERT operation that includes a previously unseen partition value will automatically create the corresponding partition directory.

The table definition should include the store identifier as the primary partition column. A secondary partition on date is typically advisable for managing data lifecycle and optimizing query performance. The combination of store_id and event_date provides a natural organization that supports both store-specific queries and time-range queries efficiently.

The table should be created with automatic optimization enabled. The autoOptimize.optimizeWrite property causes Delta Lake to automatically coalesce small files during write operations, reducing the small file problem that frequently plagues streaming ingestion workloads. The autoOptimize.autoCompact property enables background compaction of files that have accumulated between optimization runs.

When EventStream writes a record with store_id equal to store_058 and this value has never been seen before, Delta Lake creates the partition directory structure automatically. The first write creates the directory, and subsequent writes append to files within that directory. From the perspective of downstream queries, the new store's data is immediately available without any schema changes or administrative intervention.

EventStream Processing Logic

The EventStream configuration bridges the gap between Event Hubs and the Lakehouse. It must parse the Debezium change event format, extract the relevant fields including the store identifier, and route the data to the appropriate Delta table.

The transformation logic should extract the store identifier from the source metadata section of the Debezium payload. This is typically found at a path like source.db within the JSON structure. The operation type indicating whether the change is an insert, update, or delete should be preserved for downstream CDC merge processing.

A derived column for the event date should be computed from the event timestamp. This serves as the secondary partition key and enables time-based data management. The date extraction should use the source system timestamp rather than the ingestion timestamp to ensure that data is partitioned based on when the business event occurred rather than when it was processed.

The destination configuration specifies the Delta table and the partition columns. EventStream handles the actual write operations, and Delta Lake handles the partition management automatically based on the values present in each record.

Best Practices for Production Deployment

Naming Convention Enforcement

The automatic detection pattern is only as reliable as the naming convention it depends on. Before implementing this architecture, establish and enforce a strict naming convention for store databases. Document the convention, communicate it to all teams that provision databases, and implement validation checks in the database provisioning process.

The naming convention should be simple and unambiguous. A pattern like store_NNN where NNN is a zero-padded three-digit number provides clear structure and allows for up to 999 stores without format changes. Avoid conventions that might conflict with other databases or that include characters with special meaning in regex patterns.

Schema Standardization

Automatic source detection assumes that all detected sources share a compatible schema. If store_058 has different table structures than store_001, the downstream processing will fail or produce incorrect results. Schema standardization must be enforced at the source system level before relying on automatic detection.

Implement schema validation as part of the store database provisioning process. When a new store database is created, it should be created from a template that guarantees schema compatibility. If schema migrations are necessary, they should be applied uniformly across all store databases before being reflected in the CDC configuration.

Partition Key Selection

The choice of partition keys has significant implications for both storage efficiency and query performance. The store identifier is the natural first-level partition because it provides the strongest cardinality and aligns with common query patterns such as analyzing a specific store's performance.

Date as a secondary partition enables efficient time-range queries and simplifies data lifecycle management. Retention policies can be implemented by dropping old date partitions rather than scanning and deleting individual records. However, the combination of store and date partitions can produce a large number of partition directories. With 100 stores and 365 days of retained data, the table would have 36,500 partitions. While Delta Lake handles this reasonably well, query planning overhead increases with partition count.

Consider using month rather than date as the secondary partition if the total partition count becomes problematic. This reduces the partition count by a factor of roughly 30 while still enabling reasonably efficient time-based queries and lifecycle management.

Monitoring and Alerting

Automatic detection reduces operational burden but does not eliminate the need for monitoring. Implement alerting for several key scenarios.

First, monitor for new store detection. While the system handles new stores automatically, operations teams should be notified when a new store begins ingesting data. This serves as a sanity check that the detection is working and provides visibility into the growth of the system.

Second, monitor for schema compatibility failures. If a new database is detected but its schema does not match expectations, the CDC process may fail or produce malformed data. Alerting on processing errors helps catch these issues quickly.

Third, monitor for snapshot completion. When a new store database triggers an initial snapshot, track the snapshot progress and completion. Extended snapshot times may indicate unusually large source tables or performance issues that warrant investigation.

Fourth, monitor partition growth. While dynamic partitioning is convenient, runaway partition creation can indicate a problem such as incorrect store identifiers being generated. Alert if the number of distinct store partitions grows faster than expected based on the known rate of new store openings.

Initial Snapshot Planning

The initial snapshot that occurs when a new database is detected can be resource-intensive for both the source database and the CDC infrastructure. Plan for this by establishing a new store onboarding window during off-peak hours when the impact of snapshot processing is minimized.

Consider implementing a two-phase onboarding process for stores with large historical datasets. In the first phase, configure the database but exclude it from the Debezium include pattern. Perform a bulk historical load using batch processing, which can be throttled and scheduled more flexibly than the streaming snapshot. In the second phase, add the database to the include pattern to begin capturing incremental changes. This approach reduces the load on the streaming infrastructure while still achieving complete data capture.

Capacity Planning

Dynamic detection and partitioning enable easy scaling in terms of configuration, but the underlying infrastructure must still be sized appropriately. Event Hubs throughput units must accommodate the aggregate event volume from all stores. Fabric capacity units must handle the combined processing load of EventStream and Delta Lake operations.

Develop a capacity model that estimates resource requirements per store. Multiply by the current store count plus a growth buffer to determine infrastructure sizing. Review and adjust this model as actual usage patterns become clear.

Risk Assessment and Mitigation

Uncontrolled Source Proliferation

The convenience of automatic detection carries a risk of unintended sources being captured. If the naming convention is not strictly enforced, or if the regex pattern is too broad, databases that should not be ingested may be detected and processed.

Mitigation involves implementing strict naming convention governance and using precise regex patterns. The pattern should be as specific as possible while still accommodating legitimate variations. Consider implementing a whitelist in addition to the pattern match, where new databases matching the pattern are flagged for approval before ingestion begins. This adds a manual step but provides a safety checkpoint.

Schema Drift Between Sources

Over time, individual store databases may drift from the standard schema due to local modifications, failed migrations, or version inconsistencies. When the CDC process encounters unexpected schema elements, it may fail or produce incorrect data.

Mitigation requires implementing schema validation at both the source and destination. At the source, periodic schema audits should compare each store database against the canonical schema and flag deviations. At the destination, schema evolution policies in Delta Lake should be configured to reject incompatible changes rather than silently accepting them. The merge schema option should be used cautiously and only when schema evolution is intentional.

Partition Explosion

Dynamic partition creation can lead to an excessive number of partitions if the partition key has unexpectedly high cardinality or if erroneous data introduces spurious partition values. A misconfigured pipeline might create thousands of partitions, degrading query performance and complicating data management.

Mitigation involves implementing partition count monitoring with alerts at defined thresholds. Additionally, validate partition key values before writing to Delta Lake. If a store identifier does not match the expected format, reject the record or route it to an error table for investigation rather than creating an erroneous partition.

CDC Lag During Snapshot

When a new database triggers an initial snapshot, the Debezium connector dedicates resources to reading the full table contents. During this period, change events from other databases may experience increased latency. In severe cases, the replication slot lag may grow to problematic levels.

Mitigation involves scheduling new database provisioning during low-activity periods, sizing the CDC infrastructure with headroom for snapshot operations, and monitoring replication slot lag with alerts at defined thresholds. For very large initial loads, consider the two-phase onboarding approach described earlier.

Event Hubs Partition Affinity

Event Hubs uses partitions to parallelize message processing. If all messages are routed to a single partition, throughput is limited to what that partition can handle. If messages are distributed across partitions without regard to ordering requirements, related events may be processed out of order.

Mitigation involves configuring the Debezium producer to use the store identifier as the partition key. This ensures that all events for a given store are routed to the same Event Hubs partition, preserving ordering within each store while distributing load across partitions for different stores. The number of Event Hubs partitions should be set high enough to accommodate the expected number of stores with room for growth. Unlike some properties, partition count cannot be increased after the Event Hub is created.

Orphaned Replication Slots

If a store database is decommissioned but the replication slot is not cleaned up, PostgreSQL continues to retain write-ahead log segments for the orphaned slot. Over time, this can fill the disk and cause database outages.

Mitigation requires implementing a decommissioning procedure that includes replication slot cleanup. Monitor for inactive replication slots and alert when a slot has not been read from in an extended period. Consider implementing automatic slot cleanup for slots that have been inactive beyond a defined threshold, though this should be done cautiously to avoid accidentally removing slots that are temporarily inactive due to maintenance.

Operational Procedures

Adding a New Store

The procedure for adding a new store is intentionally minimal. The DBA creates the store database following the established naming convention. The database should be created from the standard template to ensure schema compatibility.

Within minutes of database creation, Debezium detects the new database and begins the initial snapshot. Operations teams receive a notification of the new store detection. The snapshot progresses, with completion typically occurring within 30 minutes for a standard store data volume.

Once the snapshot completes, incremental CDC begins. The first records arriving at the Lakehouse trigger automatic partition creation. From this point forward, the new store's data is fully integrated into the analytics platform with no additional intervention required.

Removing a Store

Store removal requires more deliberate action than store addition. First, stop ingesting new data by either renaming the database to no longer match the include pattern or by dropping the database entirely. Second, drop the replication slot associated with the store to prevent WAL retention issues. Third, decide whether to retain or purge historical data in the Lakehouse.

If historical data should be retained, no action is needed at the Lakehouse level. The partition remains but simply receives no new data. If historical data should be purged, drop the partition using Delta Lake's partition drop capability. This removes the data files and the partition metadata in a single atomic operation.

Handling Schema Changes

Schema changes require coordination across all store databases and the downstream processing logic. Minor additive changes such as new nullable columns can often be handled through Delta Lake's schema evolution capabilities. The merge schema option allows new columns to be added automatically when encountered.

Breaking changes such as column renames, type changes, or column removals require a more deliberate migration process. First, update the downstream processing logic to handle both the old and new schemas. Deploy this change and verify it works with existing data. Then, apply the schema change to source databases in a rolling fashion. Finally, once all sources have been migrated, remove support for the old schema from the processing logic.

Disaster Recovery

The architecture provides several recovery options depending on the failure scenario. If Event Hubs experiences an outage, Debezium buffers changes locally and resumes publishing when connectivity is restored. The replication slot ensures no changes are lost during the outage, though extended outages may cause WAL accumulation on the source database.

If Fabric experiences an outage, events accumulate in Event Hubs up to the retention period. Once Fabric recovers, EventStream resumes processing from its last checkpoint, catching up on accumulated events. The Delta Lake table remains consistent due to its transactional nature.

If a source database is lost, recovery depends on backup strategy. The Lakehouse contains a copy of all ingested data, which can serve as a read-only recovery source. Full database recovery requires restoring from PostgreSQL backups.

Dynamic partitioning and automatic source detection transform multi-store data ingestion from an operational burden into a largely self-managing system. The combination of Debezium's pattern-based database detection with Delta Lake's dynamic partition creation eliminates most manual configuration work while maintaining the flexibility to accommodate growth.

The implementation requires careful attention to naming conventions, schema standardization, and monitoring. The risks are real but manageable with appropriate governance and operational procedures. For organizations operating at scale with dozens or hundreds of similar data sources, this architecture provides a sustainable path to unified analytics without proportional growth in operational overhead.

The key principle underlying this approach is that systems should adapt to data rather than requiring data to conform to rigid system configurations. By designing for automatic detection and dynamic accommodation, the data platform becomes a utility that business operations can leverage without constant engineering involvement. This shift from explicit configuration to pattern-based adaptation is essential for organizations seeking to derive value from data at scale.

Azure Databricks & Fabric Disaster Recovery: The Better Together Story

Rafia_Aqil — Tue, 10 Mar 2026 01:46:28 GMT

Author's: Amudha Palani amudhapalani, Oscar Alvarado oscaralvarado, Eric Kwashie ekwashie, Peter Lo PeterLo and Rafia Aqil Rafia_Aqil

Disaster recovery (DR) is a critical component of any cloud-native data analytics platform, ensuring business continuity even during rare regional outages caused by natural disasters, infrastructure failures, or other disruptions.

Identify Business Critical Workloads

Before designing any disaster recovery strategy, organizations must first identify which workloads are truly business‑critical and require regional redundancy. Not all Databricks or Fabric processes need full DR protection; instead, customers should evaluate the operational impact of downtime, data freshness requirements, regulatory obligations, SLAs, and dependencies across upstream and downstream systems. By classifying workloads into tiers and aligning DR investments accordingly, customers ensure they protect what matters most without over‑engineering the platform.

Azure Databricks

Azure Databricks requires a customer‑driven approach to disaster recovery, where organizations are responsible for replicating workspaces, data, infrastructure components, and security configurations across regions.

Full System Failover (Active-Passive) Strategy

A comprehensive approach that replicates all dependent services to the secondary region. Implementation requirements include:

Infrastructure Components:

- - Replicate Azure services (ADLS, Key Vault, SQL databases) using Terraform

- - Deploy network infrastructure (subnets) in the secondary region

- - Establish data synchronization mechanisms

Data Replication Strategy:

- - Use Deep Clone for Delta tables rather than geo-redundant storage

- - Implement periodic synchronization jobs using Delta's incremental replication

- - Measure data transfer results using time travel syntax

Workspace Asset Synchronization:

- - Co-deploy cluster configurations, notebooks, jobs, and permissions using CI/CD

- - Utilize Terraform and SCIM for identity and access management

- - Keep job concurrencies at zero in the secondary region to prevent execution

Fully Redundant (Active-Active) Strategy

The most sophisticated approach where all transactions are processed in multiple regions simultaneously. While providing maximum resilience, this strategy:

- Requires complex data synchronization between regions

- Incurs highest operational costs due to duplicate processing

- Typically needed only for mission-critical workloads with zero-tolerance for downtime

- Can be implemented as partial active-active, processing most workload in primary with subset in secondary

Enabling Disaster Recovery

- Create a secondary workspace in a paired region.

- Use CI/CD to keep Workspace Assets Synchronized continuously.

Requirement	Approach	Tools
Cluster Configurations	Co-deploy to both regions as code	Terraform
Code (Notebooks, Libraries, SQL)	Co-deploy with CI/CD pipelines	Git, Azure DevOps, GitHub Actions
Jobs	Co-deploy with CI/CD, set concurrency to zero in secondary	Databricks Asset Bundles, Terraform
Permissions (Users, Groups, ACLs)	Use IdP/SCIM and infrastructure as code	Terraform, SCIM
Secrets	Co-deploy using secret management	Terraform, Azure Key Vault
Table Metadata	Co-deploy with CI/CD workflows	Git, Terraform
Cloud Services (ADLS, Network)	Co-deploy infrastructure	Terraform

- Update your orchestrator (ADF, Fabric pipelines, etc.) to include a simple region toggle to reroute job execution.

- Replicate all dependent services (Key Vault, Storage accounts, SQL DB).

- Implement Delta “Deep Clone” synchronization jobs to keep datasets continuously aligned between regions.

- Introduce an application‑level “Sync Tool” that redirects:

- - data ingestion

- - compute execution

- Enable parallel processing in both regions for selected or all workloads.

- Use bi‑directional synchronization for Delta data to maintain consistency across regions.

- For performance and cost control, run most workloads in primary and only subset workloads in secondary to keep it warm.

Implement Three-Pillar DR Design

Primary Workspace: Your production Databricks environment running normal operations

Secondary Workspace: A standby Databricks workspace in a different(paired) Azure region that remains ready to take over if the primary fails. This architecture ensures business continuity while optimizing costs by keeping the secondary workspace dormant until needed.

The DR solution is built on three fundamental pillars that work together to provide comprehensive protection:

1. Infrastructure Provisioning (Terraform)

The infrastructure layer creates and manages all Azure resources required for disaster recovery using Infrastructure as Code (Terraform).

What It Creates:

- - Secondary Resource Group: A dedicated resource group in your paired DR region (e.g., if primary is in East US, secondary might be in West US 2)
  - Secondary Databricks Workspace: A standby Databricks workspace with the same SKU as your primary, ready to receive failover traffic
  - DR Storage Account: An ADLS Gen2 storage account that serves as the backup destination for your critical data
  - Monitoring Infrastructure: Azure Monitor Log Analytics workspace and alert action groups to track DR health
  - Protection Locks: Management locks to prevent accidental deletion of critical DR resources
  - Key Design Principle: The Terraform configuration references your existing primary workspace without modifying it. It only creates new resources in the secondary region, ensuring your production environment remains untouched during setup.

2. Data Synchronization (Delta Notebooks)

The data synchronization layer ensures your critical data is continuously backed up to the secondary region.

How It Works: The solution uses a Databricks notebook that runs in your primary workspace on a scheduled basis. This notebook:

- - Connects to Backup Storage: Uses Unity Catalog with Azure Managed Identity for secure, credential-free authentication to the secondary storage account
  - Identifies Critical Tables: Reads from a configuration list you define (sales data, customer data, inventory, financial transactions, etc.)
  - Performs Deep Clone: Uses Delta Lake's native CLONE functionality to create exact copies of your tables in the backup storage
  - Tracks Sync Status: Logs each synchronization operation, tracks row counts, and reports on data freshness

Authentication Flow: The synchronization process leverages Unity Catalog's managed identity capabilities:

- - An existing Access Connector for Unity Catalog is granted "Storage Blob Data Contributor" permissions on the backup storage.
  - Storage credentials are created in Databricks that reference this Access Connector.
  - The notebook uses these credentials transparently—no storage keys or secrets are required.

What Gets Synced: You define which tables are critical to your business operations. The notebook creates backup copies including:

- - Full table data and schema
  - Table partitioning structure
  - Delta transaction logs for point-in-time recovery

3. Failover Automation (Python Scripts)

The failover automation layer orchestrates the switch from primary to secondary workspace when disaster strikes.

Microsoft Fabric

Microsoft Fabric provides built‑in disaster recovery capabilities designed to keep analytics and Power BI experiences available during regional outages. Fabric simplifies continuity for reporting workloads, while still requiring customer planning for deeper data and workload replication.

Power BI Business Continuity

Power BI, now integrated into Fabric, provides automatic disaster recovery as a default offering:

- No opt-in required: DR capabilities are automatically included.

- Azure storage geo-redundant replication: Ensures backup instances exist in other regions.

- Read-only access during disasters: Semantic models, reports, and dashboards remain accessible.

- Always supported: BCDR for Power BI remains active regardless of OneLake DR setting.

Microsoft Fabric

Fabric's cross-region DR uses a shared responsibility model between Microsoft and customers:

Microsoft's Responsibilities:

- Ensure baseline infrastructure and platform services availability

- Maintain Azure regional pairings for geo-redundancy.

- Provide DR capabilities for Power BI as default.

Customer Responsibilities:

- Enable disaster recovery settings for capacities

- Set up secondary capacity and workspaces in paired regions

- Replicate data and configurations

Enabling Disaster Recovery

Organizations can enable BCDR through the Admin portal under Capacity settings:

1. Navigate to Admin portal → Capacity settings
2. Select the appropriate Fabric Capacity
3. Access Disaster Recovery configuration
4. Enable the disaster recovery toggle

Critical Timing Considerations:

- 30-day minimum activation period: Once enabled, the setting remains active for at least 30 days and cannot be reverted.

- 72-hour activation window: Initial enablement can take up to 72 hours to become fully effective.

Azure Databricks & Microsoft Fabric DR Considerations

Building a resilient analytics platform requires understanding how disaster recovery responsibilities differ between Azure Databricks and Microsoft Fabric. While both platforms operate within Azure’s regional architecture, their DR models, failover behaviors, and customer responsibilities are fundamentally different.

Recovery Procedures

Procedure	Databricks	Fabric
Failover	Stop workloads, update routing, resume in secondary region.	Microsoft initiates failover; customers restore services in DR capacity.
Restore to Primary	Stop secondary workloads, replicate data/code back, test, resume production.	Recreate workspaces and items in new capacity; restore Lakehouse and Warehouse data.
Asset Syncing	Use CI/CD and Terraform to sync clusters, jobs, notebooks, permissions.	Use Git integration and pipelines to sync notebooks and pipelines; manually restore Lakehouses.

Business Considerations

Consideration	Databricks	Fabric
Control	Customers manage DR strategy, failover timing, and asset replication.	Microsoft manages failover; customers restore services post-failover.
Regional Dependencies	Must ensure secondary region has sufficient capacity and services.	DR only available in Azure regions with Fabric support and paired regions.
Power BI Continuity	Not applicable.	Power BI offers built-in BCDR with read-only access to semantic models and reports.
Activation Timeline	Immediate upon configuration.	DR setting takes up to 72 hours to activate; 30-day wait before changes allowed.

Tableau to Power BI Migration: Semantic Layer-First Approach for Cloud Architects

Rafia_Aqil — Thu, 07 May 2026 18:15:11 GMT

Author's: Mahjabin Ahmed, Yassine El Ouardi, Lavanya Sreedhar LavanyaSreedhar, Peter Lo PeterLo, Aryan Anmol aryananmol, Shreya Harvu shreyaharvu and Rafia Aqil Rafia_Aqil

In this guide, we provide practical guidance for migrating from Tableau to Power BI, with a focus on technical best practices and architecture. Unifying business intelligence on the Microsoft Fabric platform, enterprises gain closer integration with Microsoft 365 (Teams, Copilot, Excel). For cloud solution architects and BI developers, a successful migration is not just about rebuilding dashboards in a new tool. It requires thoughtful architectural planning and a shift to a more model-centric approach to BI.

Why Semantic Layer-First Architecture Matters

The Traditional Migration Challenge

Most Tableau to Power BI migrations follow a dashboard-centric approach: teams attempt to replicate existing Tableau workbooks, calculated fields, and LOD (Level of Detail) expressions directly into Power BI reports. While this may seem efficient initially, it creates significant downstream challenges:

- Duplicated logic: Each report embeds its own calculations and business rules, leading to conflicting KPIs across the organization

- Maintenance overhead: Changes to business logic require updating dozens or hundreds of individual reports

- Governance gaps: Without centralized definitions, semantic drift occurs—different teams calculate "Revenue" or "Active Customer" differently

- Scalability issues: As data volumes grow, report-level transformations become performance bottlenecks

The Semantic Layer-First Alternative

Microsoft's recommended approach centers on semantic models (formerly called datasets)—centralized, governed data models that separate business logic from visualization. In this architecture:

The payoff is substantial: when data evolves or business rules change, you update the semantic model once, and all dependent reports automatically reflect the changes—no manual redesign required.

Understanding Migration Complexity: Simple to Very Complex Dashboards

Not all Tableau dashboards are created equal. The migration strategy should align with dashboard complexity, and the semantic layer approach becomes increasingly valuable as complexity grows.

Follow a Step-by-Step Migration Strategy

Migrating from Tableau to Power BI is not a one-click effort – it requires a mix of automated and manual refactoring, plus a sound change management plan. Below are key strategies and best practices for a successful migration:

1. Audit your Tableau estate: Start by taking inventory of all existing Tableau workbooks, data sources, and dashboards. Determine what needs to be migrated (focus on high-value, widely used reports first) and identify any redundant or obsolete content that can be retired rather than converted.
2. Conduct a proof-of-concept (PoC): Before migrating everything, pick a representative complex dashboard (or a subset of your data) and perform a pilot migration. This will help you validate that Power BI can connect to your data (e.g. setting up the Power BI gateways for on-premises sources), test performance (Import vs DirectQuery modes), and experiment with replicating key visuals or calculations. Use the PoC to uncover any surprises early – for example, test that any Level of Detail expressions or table calculations in Tableau can be re-created in DAX. The lessons learned here should inform your overall project plan.
3. Use a phased migration approach: Plan to run Tableau and Power BI in parallel for some period, rather than switching everything at once. Migrate in waves – for example, by business unit or subject area – and incorporate user feedback as you go. This phased approach reduces risk and allows your team to improve the process with each iteration. It also gives end users time to adjust gradually.
4. Migrate high-impact dashboards first: Prioritize the migration of key reports and dashboards that are critical to the business or have the most usage. Delivering these early wins will not only surface any technical challenges to solve but will also help demonstrate the value of Power BI’s capabilities to stakeholders. Early success builds buy-in and momentum for the rest of the migration.
5. Reimagine (don’t just replicate) the experience: It’s rarely possible – or desirable – to exactly re-create every Tableau visualization pixel-for-pixel in Power BI. Embrace the opportunity to focus on business questions and improve user experience with Power BI’s features. For example, rather than replicating a complex Tableau workaround, you might implement a cleaner solution in Power BI using native features (like bookmarks, drilldowns, or simpler navigation between pages). Engage business users and subject matter experts during this redesign to ensure the new reports meet their needs.
6. Enable dataset reusability: One major benefit of the Power BI approach is the ability to create shared datasets and dataflows. As you migrate, look for opportunities to create central semantic models (datasets) that can serve multiple reports. For instance, if several Tableau workbooks are all using similar data about sales, you can create one central Sales dataset in Power BI. Report creators across the organization can then build different Power BI reports on that single dataset without duplicating data or logic. This reduces maintenance and promotes a “build once, reuse often” strategy.
7. Provide training and support: Expect a learning curve for teams moving to Power BI – especially those who are very fluent in Tableau. Plan for user upskilling and training programs. Establish a support community or office hours where new users can ask questions and get help. If possible, identify Power BI champions or recruit a Power BI Center of Excellence (COE) team who can guide others. During the transition, ensure there are subject matter experts (SMEs) available to address questions and validate that the new reports are correct.
8. Manage change and expectations: It’s important to communicate why the organization is moving to Power BI (e.g. benefits like deeper integration, lower TCO, better governance) to get buy-in from end users. Some users may be resistant to change, especially if they’ve invested a lot of time in mastering Tableau. Prepare to handle varying responses – emphasize the personal benefits (like improved performance, new capabilities, or career growth with popular skills) to encourage adoption. Also, involve influential business users early and gather their feedback, so they feel ownership in the new solution.
9. Establish governance from Day 1: Don’t wait until after migration to think about governance. Use this chance to set up Power BI governance aligned to best practices. Decide on important aspects such as workspace naming conventions, who can create or publish content, how you’ll monitor usage and costs, and how to manage data access and security (for example, designing a strategy for RLS/OLS/CLS, and deciding when to use per-user datasets vs. organizational semantic models). Good governance will ensure your shiny new Power BI environment doesn’t sprawl into chaos over time.
10. Allow time for adjustment and iteration: Finally, be patient and iterative. Depending on the scale of your organization and the number of Tableau assets, a full migration can take months or even a year or more. Plan realistic transition periods where both systems might coexist. Continuously refine your approach with each wave of migration. Power BI’s frequent update cadence (monthly releases) means new features may emerge even during your project – stay updated, as new capabilities could simplify your migration (for example, the introduction of field parameters or Copilot might let you modernize certain Tableau features more easily).

Reimagine (don’t just replicate) the experience (Step 5):

Phase 1: Assessment and Planning

1. Audit Your Tableau Estate

- - Inventory all workbooks, data sources, and calculated fields

- - Identify high-traffic dashboards (prioritize for early migration)

- - Categorize by complexity (Simple/Medium/Complex/Very Complex)

2. Design Your Semantic Architecture

- - Map Tableau data sources to Power BI data sources (DirectQuery, Import, or Direct Lake)

- - Plan star schema for fact/dimension tables

- - Identify shared calculations that should live in semantic models vs. report-specific logic

3. Choose Storage Modes

Source Type	Recommended Mode	Rationale
Databricks Delta Lake	Direct Lake	Real-time analytics, no refresh lag
Azure SQL Database	DirectQuery or Import	Based on data volume and refresh SLAs
On-Premises SQL Server	Import (via Gateway)	Network latency considerations
Excel/CSV files	Import	Small reference data

Phase 2: Build the Semantic Layer

1. Create Star Schema Data Models

Tableau often relies on flat, denormalized datasets. Power BI performs best with star schemas:

- - Fact tables: Transactional data (sales, orders, events) with foreign keys to dimensions

- - Dimension tables: Descriptive attributes (customers, products, dates) with primary keys

- - Relationships: One-to-many from dimension to fact, leveraging bidirectional filtering sparingly

2. Migrate Calculations to DAX Measures

Convert Tableau calculated fields to DAX measures in the semantic model:

--Example of DAX:

-- Define as measure:

Total Revenue =

SUMX(

    'Sales',

    'Sales'[Quantity] * 'Sales'[Unit Price]

)

2.1 Use Copilot to Accelerate DAX Development

Leverage Copilot in Power BI Desktop to generate and validate DAX:

- - - Describe the calculation in natural language
    - Copilot suggests DAX syntax
    - Review, test, and refine

2.2 Document your Semantic Model

Invest in creating an AI-ready foundation for your semantic model. AI systems need to understand unique business contexts in order to prioritize correct information to provide consistent and reliable responses to your end users.

- - - Name Tables and Columns Clearly: Avoid ambiguity in your semantic model. Use human-readable, business-friendly names. Avoid abbreviations, acronyms, or technical terms. This improves Copilot’s ability to interpret user intent.
    - Create Meaningful Measures: Define reusable DAX measures for key business metrics (e.g., Revenue, Profit Margin). AI features rely on these to generate insights and summaries.
    - Document Semantic Model objects: Add descriptions and synonyms to your Tables, Columns and measures. This enhances natural language querying and improves Copilot’s contextual understanding.
    - Build an AI Data Schema: prepare your semantic model for AI by utilizing tooling features such as Prep data for AI.

Phase 3: Understanding Migration Complexity: Simple to Very Complex Dashboards

Not all Tableau dashboards are created equal. The migration strategy should align with dashboard complexity, and the semantic layer approach becomes increasingly valuable as complexity grows.

1. Dashboard Conversion Best Practices

- - - Think in "pages" not "sheets": Power BI reports combine multiple visuals per page; group related visuals logically
    - Use slicers for interactivity: Replace Tableau filters with Power BI slicers and filter pane
    - Leverage bookmarks for navigation: Create dynamic report experiences with show/hide containers

Simple Complexity Level

Category

Tableau Feature

Power BI Equivalent

Microsoft Fabric Enhancements

Best Practice Notes

Data Model

Single custom SQL

Power Query for data shaping and ETL.

OneLake Shortcuts for unified data access.

Use star schema for optimized performance; push logic into the semantic layer rather than visuals.

Calculations

Basic IF/ELSE, SUM

Data Analysis Expressions (DAX) for measures and calculated columns.

Copilot for Power BI to assist with DAX creation.

Fabric IQ for natural language queries.

Centralize calculations in semantic models for consistency and governance.

Medium Complexity Level

Category	Tableau Feature	Power BI Equivalent	Fabric Enhancements	Best Practice Notes
Data Model	Multiple custom SQL (up to 3)	Connect live to databases (Azure Databricks): DirectQuery in Power BI Connect with cloud data sources: Power BI data sources	OneLake Shortcuts for unified access without databricks compute cost. Semantic Models can combine multiple sources.	Optimize with star schema; Prefer OneLake Shortcuts for performance; avoid heavy transformations in visuals.
Calculations	Nested IFs, CASE	Data Analysis Expressions (DAX) for measures and calculated columns.	Copilot for Power BI to assist with DAX creation. Fabric Data Agent for conversational BI. Fabric IQ for natural language queries: Fabric IQ	Centralize logic in semantic models; use Copilot for automation and validation; keep calculations reusable.
Reporting	Tooltip format in Bar and Map visuals Select All/Clear option for Single Select dropdown	Standard tooltips offer help tooltips, text, and background formatting. Dynamic tooltip will be able to create the Tooltip page and reuse it in multiple visuals The customization is so much better than the OOB tooltips Create report tooltip pages in Power BI - Power BI \| Microsoft Learn Use Clear All Slicers Button.		Disable Single Select, Add Clear All Slicers button, Customize the Button and Use the Button

Complex Complexity Level

Category

Tableau Feature

Power BI Equivalent

Fabric Enhancements

Best Practice Notes

Data Model

Multiple sources

Create relationship using more than one column

Composite Models in Power BI (DirectQuery + Import) for combining multiple sources, also connect to various cloud services.

Dataflows for pre-processing.

Power BI allows a relationship between 2 tables based on only one active column.

OneLake Shortcuts for unified access without Azure Databricks compute cost;

Microsoft Fabric Dataflows Gen2 offers multiple ways to ingest, transform, and load data efficiently.

Consolidate sources into semantic models; use Direct Lake for performance;

Plan and design data model to comply with star schema supported by Power BI

Relationship DAX

USERELATIONSHIP DAX for activating relationships in Power BI for a specific calculation

Calculations

LOD, window functions

Data Analysis Expressions (DAX) for measures and calculated columns.

Copilot to assist with complex DAX.

Fabric IQ Ontology for semantic alignment.

Change how visuals interact in a Power BI report.

Centralize calculations in semantic layer; use variables in DAX for readability and performance.

Fabric Data Agent for a conversational BI.

Very Complex Complexity Level

Category

Tableau Feature

Power BI Equivalent

Fabric Enhancements

Best Practice Notes

Data Model

Multi-source, Excel, SQL

Composite Models in Power BI (DirectQuery + Import) for combining multiple sources, also connect to various cloud services.

Dataflows for pre-processing.

OneLake Shortcuts for unified access;

Connector overview build-in support.

Mirroring for real-time sync.

Combine multiple sources into well-structured semantic models for consistency and optimized performance.

Calculations

Predictive logic

Data Analysis Expressions (DAX) for measures and calculated columns.

Fabric AutoML, ML models, AI Insights, Python/R, Notebook‑based ML (Spark/Scikit‑Learn), Fabric AI Functions, Fabric IQ Ontology

Fabric Data Agent for a conversational BI.

Centralize logic in semantic models; leverage Copilot for automation and parameter-driven workflows.

Prepare for Copilot.

2. Tableau Feature Equivalents

Tableau Feature	Power BI Equivalent	Microsoft Learn Link
Calculated Fields	DAX Measures	DAX Documentation
Parameters	Field Parameters / Bookmarks	Use report readers to change visuals
Actions	Drillthrough / Bookmarks	Drillthrough
Tableau Prep	Power Query / Dataflows	Differences between Dataflow Gen1 and Dataflow Gen2
Tableau Server	Power BI Service	What is Power BI? Overview of Components and Benefits

Phase 4: Governance and Deployment

Workspace Planning (Dev / Test / Prod Separation)

A proper workspace strategy is essential for governed deployments in Fabric and Power BI.

- Fabric supports separate Development, Test, and Production stages using Deployment Pipelines, enabling controlled promotions of semantic models, reports, dataflows, notebooks, lakehouses, and other items.

- You can assign each workspace to a pipeline stage (Dev → Test → Prod) to ensure safe lifecycle management.

Sensitivity Labeling (Microsoft Purview Information Protection)

Sensitivity labels allow governed classification and protection of data across Fabric items.

- Sensitivity labels can be applied directly to Fabric items (semantic models, reports, dataflows, etc.) through the item's header flyout or the item settings.

- Labels from Microsoft Purview Information Protection enforce data access rules and help organizations meet compliance requirements.

Endorsement & Certification (Promoted, Certified, Master Data)

Endorsement improves discoverability and trust in shared organizational content.

- Promoted: Item creators mark content as recommended for broader use.

- Certified: Administrators or authorized reviewers validate content meets organizational quality standards.

- Master Data: Indicates authoritative single‑source‑of‑truth items such as semantic models or lakehouses.

- All Fabric items except dashboards can be promoted or certified; data‑containing items can be designated as Master Data.

Monitoring & Capacity Planning

Determine the appropriate size for fabric capacity when migrating from Tableau to PowerBI. The Fabric SKU Estimator can generate a SKU recommendation (estimate) for your capacity requirements.

Ensuring performance and cost efficiency requires ongoing monitoring of your Fabric capacity.

- Microsoft recommends evaluating workloads using Fabric Capacity Metrics and planning SKU sizes based on real usage.

- Fabric uses bursting and smoothing to handle spikes while enforcing capacity limits.

- Monitoring helps identify high compute usage, background refreshes, and interactive workloads to optimize performance.

Fabric Data Source Connections (OneLake+ Manage Connections)

Microsoft Fabric is designed as an end‑to‑end analytics platform that integrates data from many different source systems into a unified environment powered by OneLake, Data Factory, Real‑Time Analytics, Dataflows , Lakehouses, Warehouses, and Mirrored Databases.

The Strategic Advantage: Semantic Layer + Fabric IQ

The semantic layer-first approach sets the foundation for the next evolution in enterprise analytics. Fabric IQ (announced at Ignite 2025) is Microsoft's semantic intelligence platform that auto-elevates semantic models into ontologies—structured knowledge graphs that power AI agents, Copilot experiences, and cross-domain data reasoning.

What this means for your migration:

- Semantic models you build today become the foundation for AI-driven analytics tomorrow

- Data Agents can reason across multiple semantic models, answering questions that span domains

- Business users transition from "report consumers" to "data explorers" via natural language interfaces

Conclusion: Build for the Future, Not Just for Today

Migrating from Tableau to Power BI is more than a technology swap—it's an opportunity to re-architect your analytics strategy for the cloud-native, AI-powered era.

The semantic layer-first approach requires upfront investment in data modeling, DAX expertise, and Fabric platform adoption. But the payoff is transformative:

- Consistency: Single source of truth for all business metrics
- Scalability: Semantic models that serve hundreds of reports and thousands of users
- Agility: Changes to business logic propagate instantly across the enterprise
- Future-readiness: Foundation for Fabric IQ, Data Agents, and AI-driven insights

Start your migration with the end in mind: not just convert dashboards, but a modern, governed, AI-ready analytics platform that scales with your business.

Addressing Key Migration Concerns

(1) Why a semantic‑layered model approach is better than recreating Tableau dashboards

A semantic‑layered modeling approach is the optimal strategy for migration and is significantly more effective than attempting to recreate Tableau dashboards exactly as they exist. By contrast, Power BI and Fabric encourage a semantic model–first architecture, where all business rules, relationships, calculations, and transformations are centralized in a governed model that serves many dashboards.

The approach not only provides consistency and reuse across the enterprise but also ensures that report authors build on a single certified version of the truth.

(2) How semantic-layered model approach reduces the constant redesign caused by changing data needs.

A semantic‑layered modeling approach directly addresses concern about constant changes and frequent redesigns of dashboards when data evolves. With a semantic layer, changes are absorbed in the model layer—so the logic is updated once and flows automatically into all dependent reports. Combined with Fabric features like OneLake shortcuts, Direct Lake mode, and centralized governance, the semantic layer drastically reduces breakage, minimizes rework, and ensures scalability as data continues to grow and shift.

Additional Resources

Direct Lake in Microsoft Fabric

From Bronze to Gold: Data Quality Strategies for ETL in Microsoft Fabric

Sally_Dabbah — Tue, 09 Dec 2025 08:12:15 GMT

Introduction

Data fuels analytics, machine learning, and AI but only if it’s trustworthy. Most organizations struggle with inconsistent schemas, nulls, data drift, or unexpected upstream changes that silently break dashboards, models, and business logic.

Microsoft Fabric provides a unified analytics platform with OneLake, pipelines, notebooks, and governance capabilities. When combined with Great Expectations, an open-source data quality framework, Fabric becomes a powerful environment for enforcing data quality at scale.

In this article, we explore how to implement enterprise-ready, parameterized data validation inside Fabric notebooks using Great Expectations including row-count drift detection, schema checks, primary-key uniqueness, and time-series batch validation.

A quick reminder: ETL (Extract, Transform, Load) is the process of pulling raw data from source systems, applying business logic and quality validations, and delivering clean, curated datasets for analytics and AI. While ETL spans the full Medallion architecture, this guide focuses specifically on data quality checks in the Bronze layer using the NYC Taxi sample dataset.

🔗 Full implementation is available in my GitHub repository:
sallydabbahmsft/Data-Quality-Checks-in-Microsoft-Fabric: Data Quality Checks in Microsoft Fabric

Why Data Quality Matters More Than Ever?

AI and analytics initiatives fail not because of model quality but because the underlying data is inaccurate, incomplete, or inconsistent. Organizations adopting Microsoft Fabric often ask:

How can we validate data as it lands in Bronze?
How do we detect schema changes before they break downstream pipelines?
How do we prevent silent failures, anomalies, and drift?
How do we standardize data quality checks across multiple tables and pipelines?

Great Expectations provides a unified, testable, automation-friendly way to answer these questions.

Great Expectations in Fabric

Great Expectations (GX) is an open-source library for:

✔ Declarative data quality rules ("expectations")
✔ Automated validation during ETL
✔ Rich documentation and reporting
✔ Batch-based validation for time-series or large datasets
✔ Integration with Python, Spark, SQL, and cloud data platforms

Fabric notebooks now support Great Expectations natively (via PySpark), enabling engineering teams to:

Build reusable DQ suites
Parameterize expectations by pipeline
Validate full datasets or daily partitions
Integrate validation into Fabric pipelines and alerting

Data Quality Across the Medallion Architecture

This solution follows the Medallion Architecture, with validation at every layer.

This pipeline follows a Medallion Architecture, moving data through the Bronze, Silver, and Gold layers while enforcing data quality checks at every stage.

📘 P.S. Fabric also supports this via built-in Medallion task flows:
Task flows overview - Microsoft Fabric | Microsoft Learn

🥉Bronze Layer: Ingestion & Validation

Ingest raw source data into Bronze without transformations.
Run foundational DQ checks to ensure structural integrity.

Bronze DQ answers:
➡ Did the data arrive correctly?

🥈Silver Layer: Transformation & Validation

Clean, standardize, and enrich Bronze data.
Validate business rules, schema consistency, reference values, and more.

Silver DQ answers:
➡ Is the data accurate and logically correct?

🥇 Gold Layer: Enrichment & Consumption

Produce curated, analytics-ready datasets.
Validate metrics, aggregates, and business KPIs.

Gold DQ answers:
➡ Can executives trust the numbers?

Recommended Data Quality Validations:

Bronze Layer (Raw Ingestion)

Ingestion Volume & Row Drift – Validate total row count and detect unexpected volume drops or spikes.
Schema & Data Type Compliance – Ensure the table structure and column data types match the expected schema.
Null / Empty Column Checks – Identify missing or empty values in required fields.
Primary Key Uniqueness – Detect duplicate records based on the defined composite or natural key.

Silver Layer (Cleaned & Standardized Data)

Reference & Domain Value Validation – Confirm that values match valid categories, lookups, or reference datasets.
Business Rule Enforcement – Validate logic constraints (e.g., StartDate <= EndDate, percentages within range).
Anomaly / Outlier Detection – Identify unusual patterns or values that deviate from historical behavior.
Post-Standardization Deduplication – Ensure standardized and enriched records no longer contain duplicates.

Gold Layer (Curated, Business-Ready Data)

Metric & Aggregation Consistency – Validate totals, ratios, rollups, and other aggregated metrics.
KPI Threshold Monitoring – Trigger alerts when KPIs exceed defined thresholds.
Data / Feature Drift Detection (for ML) – Monitor changes in distributions across time.
Cross-System Consistency Checks – Compare business metrics across internal systems to ensure alignment.

Implementing Data Quality with Great Expectations in Fabric

Step 1 - Read data from Lakehouse (parametrized):

lakehouse_name = "Bronze" table_name = "NYC Taxi - Green" query = f"SELECT * FROM {lakehouse_name}.`{table_name}`" df = spark.sql(query)

Step 2 - Create and Register a Suite:

context = gx.get_context() suite = context.suites.add( gx.ExpectationSuite(name="nyc_bronze_suite") )

Step 3 - Add Bronze Layer Expectations (Reusable Function):

import great_expectations as gx def add_bronze_expectations( suite: gx.ExpectationSuite, primary_key_columns: list[str], required_columns: list[str], expected_schema: list[str], expected_row_count: int | None = None, max_row_drift_pct: float = 0.2, ) -> gx.ExpectationSuite: # 1. Ingestion Count & Row Drift if expected_row_count is not None: min_rows = int(expected_row_count * (1 - max_row_drift_pct)) max_rows = int(expected_row_count * (1 + max_row_drift_pct)) row_count_expectation = gx.expectations.ExpectTableRowCountToBeBetween( min_value=min_rows, max_value=max_rows, ) suite.add_expectation(expectation=row_count_expectation) # 2. Schema Compliance schema_expectation = gx.expectations.ExpectTableColumnsToMatchSet( column_set=expected_schema, exact_match=True, ) suite.add_expectation(expectation=schema_expectation) # 3. Required columns: NOT NULL for col in required_columns: not_null_expectation = gx.expectations.ExpectColumnValuesToNotBeNull( column=col ) suite.add_expectation(expectation=not_null_expectation) # 4. Primary key uniqueness (if provided) if primary_key_columns: unique_pk_expectation = gx.expectations.ExpectCompoundColumnsToBeUnique( column_list=primary_key_columns ) suite.add_expectation(expectation=unique_pk_expectation) return suite

Step 4 - Attach Data Asset & Batch Definition:

data_source = context.data_sources.add_spark(name="bronze_datasource") data_asset = data_source.add_dataframe_asset(name="nyc_bronze_data") batch_definition = data_asset.add_batch_definition_whole_dataframe("full_bronze_batch")

Step 5 - Run Validation:

validation_definition = gx.ValidationDefinition( data=batch_definition, suite=suite, name="Bronze_DQ_Validation" ) results = validation_definition.run( batch_parameters={"dataframe": df} ) print(results)

7. Optional: Time-Series Batch Validation (Daily Slices)

Fabric does not yet support add_batch_definition_timeseries, so your notebook implements custom logic to validate each day independently:

dates_df = df.select(F.to_date("lpepPickupDatetime").alias("dt")).distinct() for d in dates: df_day = df.filter(F.to_date("lpepPickupDatetime") == d) results = validation_definition.run(batch_parameters={"dataframe": df_day})

This enables:

Daily anomaly detection
Partition-level completeness checks
Early schema drift detection

Automating DQ with Fabric Pipelines

Fabric pipelines can orchestrate your data quality workflow:

Trigger notebook after ingestion
Pass parameters (table, layer, suite name)
Persist DQ results to Lakehouse or Log Analytics
Configure alerts in Fabric Monitor

Production workflow

Run the notebook
Check validation results
If failures exist:
- Raise an incident
- Fail the pipeline
- Notify the on-call engineer

This creates a closed loop of ingestion → validation → monitoring → alerting.

An example of DQ pipeline:

Results:

How Enterprises Benefit

By standardizing data quality rules across all domains, organizations ensure consistent expectations and uniform validation practices , improved observability makes data quality issues visible and actionable, enabling teams to detect and resolve failures early.

This, in turn, enhances overall reliability, ensuring downstream transformations and Power BI reports operate on clean, trustworthy data.

Ultimately, stronger data quality directly contributes to AI readiness high-quality, well-validated data produces significantly better analytics and machine learning outcomes.

Conclusion

Great Expectations + Microsoft Fabric creates a scalable, modular, enterprise-ready approach for ensuring data quality across the entire medallion architecture. Whether you're validating raw ingested data, transformed datasets, or business-ready tables, the approach demonstrated here enables consistency, observability, and automation across all pipelines.

With Fabric’s unified compute, orchestration, and monitoring, teams can now integrate DQ as a first-class citizen not an afterthought.

Links:

End-to-End Observability for Azure Databricks: From Infrastructure to Internal Application Logging

Rafia_Aqil — Tue, 09 Dec 2025 16:56:23 GMT

Author's: Peter Lo PeterLo, Amudha Palani amudhapalani, Geoffrey Rathinapandi geofegeo and Rafia Aqil Rafia_Aqil

Observability in Azure Databricks is the ability to continuously monitor and troubleshoot the health, performance, and usage of data workloads by capturing metrics, logs, and traces. In a structured observability approach, we consider two broad categories of logging: Internal Databricks Logging (within the Databricks environment) and External Databricks Logging (leveraging Azure services). Each plays a distinct role in providing insights.

By combining internal and external observability mechanisms, organizations can achieve a comprehensive view: internal logs enable detailed analysis of Spark jobs and data quality, while external logs ensure global visibility, auditing, and integration with broader monitoring dashboards and alerting systems. The article is organized into two main sections:

Infrastructure Logging for Azure Databricks (external observability)
Internal Databricks Logging (in-platform observability)

Considerations

Addressing key questions upfront ensures your observability strategy is tailored to your organization’s unique workloads, risk profile, and operational needs. By proactively evaluating what to monitor, where to store logs, and who needs access, you can avoid blind spots, streamline incident response, and align monitoring investments with business priorities.

What types of workloads are running in Databricks?

- Why it matters: Different workloads (e.g., batch ETL, streaming pipelines, ML training, interactive notebooks) have distinct performance profiles and failure modes.

- Business impact: Understanding workload types helps prioritize monitoring for mission-critical processes like real-time fraud detection or daily financial reporting.

What failure scenarios need to be monitored?

- Examples: Job failures, cluster provisioning errors, quota limits, authentication issues.

- Business impact: Early detection of failures reduces downtime, improves SLA adherence, and prevents data pipeline disruptions that could affect reporting or customer-facing analytics.

Where should logs be stored and analyzed?

- Options: Centralized Log Analytics workspace, Azure Storage for archival, Event Hub for streaming analysis.

- Business impact: Centralized logging enables unified dashboards, cross-team visibility, and faster incident response across data engineering, operations, and compliance teams.

Who needs access to logs and alerts?

- Stakeholders: Data engineers, platform administrators, security analysts, compliance officers.

- Business impact: Role-based access ensures that the right teams can act on insights while maintaining data governance and privacy controls.

Infrastructure Logging for Azure Databricks

Approach 1: Diagnostic Settings for Azure Databricks

Diagnostic settings in Azure Monitor allow you to capture detailed logs and metrics from your Azure Databricks workspace, supporting operational monitoring, troubleshooting, and compliance. By configuring diagnostic settings at the workspace level, administrators can route Databricks logs—including cluster events, job statuses, and audit logs—to destinations such as Log Analytics, Azure Storage, or Event Hub. This enables unified analysis, alerting, and long-term retention of critical operational data.

Configuration Overview

- Enable Diagnostic Settings on the Databricks workspace to route logs to Log Analytics Workspace.
- Logs can also be combined with other logs mentioned below for full Azure Databricks observability.
- Here is a Guide to Azure Databricks Diagnostic Settings Log Reference: Configure diagnostic log delivery
- Implement tagging strategy-organizations can gain granular visibility into resource consumption and align spending with business priorities.
  - Default tags: Automatically applied by Databricks to cloud-deployed resources.
  - Custom tags: User-defined tags that you can add to compute resources and serverless workloads.

Use Cases

- Operational Monitoring: Detect job or resource bottlenecks.
- Security & Compliance: Audit user actions and enforce governance policies.
- Incident Response: Correlate Databricks logs with infrastructure events for faster troubleshooting.

Best Practices

- Enable only relevant log categories to optimize cost and performance.
- Use role-based access control (RBAC) to secure access to logs.

Approach 2: Azure Databricks Compute Log Delivery

Compute log delivery in Azure Databricks enables you to automatically collect and archive logs from Spark driver nodes, worker nodes, and cluster events for both all-purpose and job compute resources. When you create a cluster, you can specify a log delivery location—such as DBFS, Azure Storage, or a Unity Catalog volume—where logs are delivered every five minutes and archived hourly. All logs generated up until the compute resource is terminated are guaranteed to be delivered, supporting troubleshooting, auditing, and compliance.

Configure: To configure the log delivery location:

1. On the compute page, click the Advanced toggle.
2. Click the Logging tab.
3. Select a destination type: DBFS or Volumes (Preview).
4. Enter the Log path.
5. To store the logs, Databricks creates a subfolder in your chosen log path named after the compute's cluster_id.

Approach 3: Azure Activity Logs

Whenever you create, update, or delete Databricks resources (such as provisioning a new workspace, scaling a cluster, or modifying network settings), these actions are captured in the Activity Log. This enables teams to track who made changes, when, and what impact those changes had on the environment. Each event in the Activity Log has a particular category that is described in the following document: Azure Activity Log event schema. For Databricks, this is especially valuable for:

- Auditing resource deployments and configuration changes
- Investigating failed provisioning or quota errors
- Monitoring compliance with organizational policies
- Responding to incidents or unauthorized actions

Use Cases

- Auditing infrastructure-level changes outside the Databricks workspace.
- Monitoring provisioning delays or resource availability.

Best Practices

- Use Activity Logs in conjunction with other logs for full-stack visibility.
- Set up alerts for critical infrastructure events.
- Review logs regularly to ensure compliance and operational health.

Approach 4: Azure Monitor VM Insights

Azure Databricks cluster nodes run on Azure virtual machines (VMs), and their infrastructure-level performance can be monitored using Azure Monitor VM Insights (formerly OMS). This approach provides visibility into resource utilization across individual cluster VMs, helping identify bottlenecks that may affect Spark job performance or overall workload efficiency.

Configuration Overview: To enable VM performance monitoring:

- Enable VM Insights on the Databricks cluster for VMs.

Monitored Metrics: Once enabled, VM Insights collects: CPU usage, Memory consumption, Disk I/O, Network throughput, Process-level statistics. These metrics help assess whether Spark workloads are constrained by infrastructure limits, such as insufficient memory or high disk latency.

Considerations

- This is a standard Azure VM monitoring technique and is not specific to Databricks.
- Use role-based access control (RBAC) to secure access to performance data.

Approach 5: Virtual Network Flow Logs

For Azure Databricks workspaces deployed in a custom Azure Virtual Network (VNet-injected mode), enabling Virtual Network Flow Logs provides deep visibility into IP traffic flowing through the virtual network. These logs help monitor and optimize resources or support large enterprises that are trying to detect intrusion, flow logs can help. Review common use cases here: Vnet Flow Logs Common Usecases and how logging works here: Key properties of virtual network flow logs. Follow these steps to setup Vnet Flow Logs: Create a flow log

Configuration Overview

- Virtual Network Flow Logs are a feature of Azure Network Watcher.
- Optionally, logs can be analyzed using Traffic Analytics for deeper insights.

These logs help identify:

- Unexpected or unauthorized traffic
- Bandwidth usage patterns
- Effectiveness of NSG rules and network segmentation

Considerations

- NSG flow logging is only available for VNet-injected deployment modes.
- Ensure Network Watcher is enabled in the region where the Databricks workspace is deployed.
- Use Traffic Analytics to visualize trends and detect anomalies in network flows.

Approach 6: Spark Monitoring Logging & Metrics

The spark-monitoring library is a Python toolkit designed to interact with the Spark History Server REST API. Its main purpose is to help users programmatically access, analyze, and visualize Spark application metrics and job details after execution. Here’s what it offers:

- Application Listing: Retrieve a list of all Spark applications available on the History Server, including metadata such as application ID, name, start/end time, and status.
- Job and Stage Details: Access detailed information about jobs and stages within each application, including execution times, status, and resource usage.
- Task Metrics: Extract metrics for individual tasks, such as duration, input/output size, and shuffle statistics, supporting performance analysis and bottleneck identification.

Considerations

- The Spark Monitoring Library must be installed, see Git Repository here.
- Metrics can be exported to external observability platforms for long-term retention and alerting.

Use cases

- Automated reporting of Spark job performance and resource usage
- Batch analysis of completed Spark applications
- Integration of Spark metrics into external dashboards or monitoring systems
- Post-execution troubleshooting and optimization

Internal Databricks Logging

Approach 7: Databricks System Tables (Unity Catalog)

Databricks System Tables are a recent addition to Azure Databricks observability, offering structured, SQL-accessible insights into workspace usage, performance, and cost. These tables reside in the Unity Catalog and are organized into schemas such as system.billing, system.lakeflow, and system.compute. You can enable System Tables through these steps: _enable_system_tables - Databricks

Overview and Capabilities

When enabled by an administrator, system tables allow users to query operational metadata directly using SQL. Examples include:

- system.billing.usage: Tracks compute usage (CPU core-hours, memory) per job.
- system.compute.clusters: Captures cluster lifecycle events.
- system.lakeflow.job_run: Provides job execution details.

Use Cases

- Cost Monitoring: Aggregate usage records to identify high-cost jobs or users.
- Import pre-built usage dashboards to your workspaces to monitor account- and workspace-level usage: Usage dashboards and Create and monitor budgets
- Operational Efficiency: Track job durations, cluster concurrency, and resource utilization.
- In-Platform BI: Build dashboards in Databricks SQL to visualize usage trends without relying on external billing tools.

Best Practices

- Schedule regular queries to track cost trends, job performance, and resource usage.
- Apply role-based access control to restrict sensitive usage data.
- Integrate system table insights into Databricks SQL dashboards for real-time visibility.

Approach 8: Data Quality Monitoring

Data Quality Monitoring is a native Azure Databricks feature designed to track data quality and machine learning model performance over time. It enables automated monitoring of Delta tables and ML inference outputs, helping teams detect anomalies, data drift, and reliability issues directly within the Databricks environment. Follow these steps to enable Data Quality Monitoring. Data Quality Monitoring supports three profile types:

- Time Series: Monitors time-partitioned data, computing metrics per time window.
- Inference: Tracks prediction drift and anomalies in model request/response logs.
- Snapshot: Performs full-table scans to compute metrics across the entire dataset.
- From the enabling Lakehouse Monitoring, on step 5 you can also enable data profiling to view Data Profiling Dashboards.

Use Cases

- Data Quality Monitoring: Track null values, column distributions, and schema changes.
- Model Performance Monitoring: Detect concept drift, prediction anomalies, and accuracy degradation.
- Operational Reliability: Ensure consistent data pipelines and ML inference behavior.

Approach 9: Databricks SQL Dashboards and Alerts

Databricks SQL Dashboards and Alerts provide in-platform observability for operational monitoring, enabling teams to visualize metrics and receive notifications based on SQL query results. This approach complements infrastructure-level monitoring by focusing on application-level conditions, data correctness, and workflow health. Users can build dashboards using Databricks SQL or SQL Warehouses by querying: System tables (e.g., job runs, billing usage), Data Quality Monitoring metric tables, Custom operational datasets. You can create alerts through these steps: Databricks SQL alerts.

Alerting Features: Databricks SQL supports alerting on query results, allowing users to define conditions that trigger notifications via: Email, Slack (via webhook integration). Alerts can be configured for scenarios such as:

- Job failure counts exceeding thresholds
- Row count drops in critical tables
- Cost/Workload spikes or resource usage anomalies

Considerations

- Alerts are query-driven and run on a schedule; ensure queries are optimized for performance.
- Dashboards and alerts are workspace-specific and require appropriate permissions.

Best Practices

- Use system tables and Data Quality Monitoring metrics as data sources for dashboards.
- Schedule alerts to run at appropriate intervals (e.g., hourly for job failures).
- Combine internal alerts with external monitoring for full-stack coverage.

Approach 10: Custom Tags for Workspace-Level Assets

Custom tags allow organizations to classify and organize Databricks resources (clusters, jobs, pools, notebooks) for better governance, cost tracking, and observability. Tags are key-value pairs applied at the resource level and can be propagated to Azure for billing and monitoring.

Why Use Custom Tags?

- Cost Attribution: Assign tags like Environment=Prod, Project=HealthcareAnalytics to track costs in Azure Cost Management.
- Governance: Enforce policies based on tags (e.g., restrict high-cost clusters to Environment=Dev).
- Observability: Filter logs and metrics by tags for dashboards and alerts.

Taggable Assets

- Clusters: Apply tags during cluster creation via the Databricks UI or REST API.
- Jobs: Include tags in job configurations for workload-level tracking.
- Instance Pools: Tag pools to manage shared compute resources.
- Notebooks & Workflows: Use tags in metadata for classification and reporting.

Best Practices

- Define a standard tag taxonomy (e.g., Environment, Owner, CostCenter, Compliance).
- Validate tags regularly to ensure consistency across workspaces.
- Use tags in Log Analytics queries for cost and performance dashboards.

Architecting the Next-Generation Customer Tiering System

BonnieAo — Thu, 04 Dec 2025 21:17:55 GMT

Authors

Sailing Ni*, Joy Yu*, Peng Yang*, Richard Sie*, Yifei Wang*
*These authors contributed equally.

Affiliation
Master of Science in Business Analytics (MSBA),
UCLA Anderson School of Management,
Los Angeles, California 90095, United States
(Conducted December 2025)

Acknowledgment
This research was conducted as part of a Microsoft-sponsored Capstone Project, led by Juhi Singh and Bonnie Ao from the Microsoft MCAPS AI Transformation Office.

Microsoft’s global B2B software business classifies customers into four tiers to guide coverage, investment, and sales strategy. However, the legacy tiering framework mixes historical rules with manual heuristics, causing several issues:

Tiers do not consistently reflect customer potential or revenue importance.
Statistical coherence and business KPIs (TPA, TCI, SFI) are not optimized or enforced.
Tier distributions are imbalanced due to legacy ±1 movement and capacity rules.
Sales coverage planning depends on a tier structure not grounded in data.

To address these limitations, we, UCLA Anderson MSBA class of Dec'25, designed a next-generation KPI-driven tiering architecture. Our objective was to move from a heuristic, static system toward a scalable, transparent, and business-aligned framework.
Our redesigned tiering system follows five complementary analytical layers, each addressing a specific gap in the legacy process:

Natural Segmentation (Unsupervised Baseline): Identify the intrinsic structure of the customer base using clustering to understand how customers naturally group
Pure KPI-Based Tiering (Upper-Bound Benchmark): Show what tiers would look like if aligned only to business KPIs, quantifying the maximum potential lift and exposing trade-offs.
Hybrid KPI-Aware Segmentation (Our Contribution): Integrate clustering geometry with KPI optimization and business constraints to produce a realistic, interpretable, and deployable tiering system.
Dynamic Tiering (Longitudinal Diagnostics): Analyze historical patterns to understand how companies evolve over time, separating structural tier drift from noise.
Optimization & Resource Allocation (Proof of Concept): Demonstrate how the new tiers could feed into downstream coverage and whitespace prioritization through MIP- and heuristic-based approaches.

Together, these components answer a core strategic question:

“How should Microsoft tier its global customer base so that investment, coverage, and growth strategy directly reflect business value?”

Our final architecture transforms tiering from a static classification exercise into a KPI-driven, interpretable, and operationally grounded decision framework suitable for Microsoft’s future AI and data strategy.

Solution Architecture Diagram

Fig 1. Solution Architecture Diagram

1. Success Metrics Definition

Before designing any segmentation system, the first step is to establish success metrics that define what “good” looks like. Without these metrics, models can easily produce clusters that are statistically neat but misaligned with business needs. A clear KPI framework ensures that every model—regardless of method or complexity—is evaluated consistently on both analytical quality and real business impact.

We define success across two complementary dimensions:

1.1 Alignment & Segmentation Quality:

These metrics evaluate whether the segmentation meaningfully separates customers based on business potential.

1.1.1 Tier Potential Alignment (TPA)

Measures how well assigned tiers follow the rank order of PI_acct, our composite indicator of future growth potential. Implemented as a Spearman rank correlation, TPA tests whether higher-potential accounts systematically land in higher tiers.

Step 1 - Formula for PI_acct (Potential Index per Account)

Step 2 - Formula for TPA (Tier Potential Alignment)

𝜌_𝑠 = Spearman rank correlation
Tier Rank = ordinal tier number (Tier A = highest → Tier D = lowest)

Interpretation:

TPA=1 ⇒ Perfect alignment (higher potential → higher tier)
TPA=0 ⇒ No statistical relationship
TPA<0 ⇒ Misalignment (tiers contradict potential)

1.1.2 Tier Compactness Index (TCI)

Measures how homogeneous each tier is. Low within-tier variance on PI_acct or Revenue indicates that customers grouped together truly share similar characteristics, improving interpretability and resource planning.

(1) Potential-based Compactness - TCI_PI

(2) Revenue-based Compactness - TCI_REV

TCI=1 ⇒ tiers are tight and well-separated
TCI=0 ⇒ tiers are random or overlapping
TCI<0 ⇒ within-tier variance exceeds total variance (poor grouping)

1.2 Business Impact

These metrics test whether the segmentation supports strategic goals, not just statistical structure.

1.2.1 Strategic Focus Index (SFI)

Quantifies how much revenue comes from the company’s most strategically important tiers. High SFI means segmentation helps focus investments—sales coverage, specialist time, programs—on the customers that matter most.

Under the Tier Policy framework, the definition of “strategic” automatically adapts to the number of tiers K - for example, taking the top L tiers (e.g., top 2) or top x % of tiers ranked by mean potential or revenue.

High SFI: strong emphasis on top strategic segments (potentially efficient but watch concentration risk).
Moderate SFI: balanced focus across tiers.
Low SFI: diffuse portfolio, limited emphasis on priority segment

2. Static Segmentation

2.1 Pure Unsupervised Clustering

2.1.1 Model Conclusions at a Glance

Across all unsupervised models evaluated—Ward, Weighted Ward, K-Medoids, K-Means / K-Means++, and HDBSCAN — only the Ward model (K=4, Policy v2) provides a segmentation that is simultaneously:

statistically coherent,
business-aligned (high SFI),
geometrically stable (clean Silhouette), and
operationally interpretable.

All alternative models either distort cluster geometry, collapse SFI, or produce unstable/illogical tier structures.
Final Recommendation: Use Ward (K=4, Policy v2) as the natural segmentation baseline.

2.1.2 High-Level Algorithm Comparison

Table 1. Algorithm Comparison
Model	Algorithm Summary	Strengths	Weaknesses	Business Use
Ward	Variance-minimizing hierarchical merges	Best balance of TPA/TCI/SFI; stable geometry	Sensitive to correlated features	Primary model for segmentation
Weighted Ward	Distance reweighted by PI + revenue	Higher TPA	Silhouette collapse; unstable	Not recommended
K-Medoids	Medoid-based dissimilarity minimization	Robust to outliers	Cluster compression; weak SFI	Diagnostic only
K-Means / K-Means++	Squared-distance minimization	Fast baseline	SFI collapse; over-tight clusters	Numeric benchmark only
HDBSCAN	Density-based clustering with noise	Good for anomaly detection	TPA collapse; noisy tiers; broken PI ordering	Not suitable for tiering

2.1.3 Modeling Results

Table 2. Unsupervised Clustering Model Results
Metric	FY26 Baseline (Legacy A+B)	Ward K=4 (Policy v2)	Weighted Ward2-B (α=4, β=0.8, s=0.7, K=5)	Unweighted Ward (Policy v2, K=4)	Unweighted Ward (Policy v2, K=3)	K-Medoids B4 Behavior-only (K=3)	K-means K=4 (Policy v2)	K-means++ K=4 (Policy v2)	HDBSCAN (baseline settings)
TPA	0.260	0.260	0.860	0.260	0.300	0.520	0.310	0.310	0.040
TCI_PI	0.222	0.461	0.772	0.461	0.405	0.173	0.476	0.476	0.004
TCI_REV	0.469	0.801	0.640	0.801	0.672	0.002	0.831	0.831	0.062
SFI	0.807	0.868	0.817	0.868	0.960	0.656	0.332	0.332	0.719
Silhouette	nan	0.560	0.145	0.560	0.604	0.466	0.523	0.523	0.186

Ward (K=4, Policy v2) remains the strongest performer: SFI ≈ 0.87, Silhouette ≈ 0.56, stable geometric structure.
Weighted Ward raises TPA/PI slightly but Silhouette collapses (~0.15) → structural instability → not viable.
K-Medoids consistently compresses clusters; TPA/TCI gain is offset by TCI_REV collapse and low SFI.
K-Means / K-Means++ tighten numeric clusters but SFI drops to ~0.33 → tiers lose strategic meaning.
HDBSCAN generates large noisy segments; TPA = 0.044, TCI_PI = 0.004, Silhouette = 0.186, and Tier A/B contain negative PI → fundamentally unsuitable.

Conclusion: Only Ward (K=4) produces segmentation with both statistical integrity and business relevance.

2.1.4 Implications, Limitations, Next Steps

Implications

Our current unsupervised segmentation delivers statistically coherent and operationally usable tiers, but several structural findings emerged:

Unsupervised methods reveal the data’s natural shape, not business priorities: Ward/K-means/HDBSCAN can discover separations in the feature space but cannot move clusters toward preferred PI or revenue patterns.
Cluster outcomes cannot guarantee business-desired constraints.
For example:
- If Tier A’s PI mean is too low, the model cannot raise it.
- If Tier C becomes too large, clustering cannot rebalance it.
- If the business wants stronger SFI, clustering alone cannot optimize that objective
Some business-critical metrics are only evaluated after clustering, not optimized within clustering: Tier size distributions, average PI per tier, and revenue share are structurally important but not part of the unsupervised objective.

Hence,

Unsupervised clustering provides a statistically coherent view of the data’s natural structure, but it cannot guarantee business-preferred tier outcomes. The models cannot enforce hard constraints (e.g., desired A/B/C distribution, monotonic PI means, revenue share targets), nor can they adjust tiers when PI is too low or clusters become imbalanced. Additionally, key tier-level KPIs—such as average PI per tier, tier size stability, and revenue distribution—are only evaluated after clustering rather than optimized during it, limiting their influence on the final tier design.

To overcome these structural limitations, the next stage of the system must incorporate semi-supervised guidance and policy-based optimization, where business KPIs directly shape tier boundaries and ranking. Future iterations will expand the policy beyond PI and revenue to include behavioral and market signals and bring tier-level metrics into the objective function to better align the segmentation with real-world operational priorities.

2.2 Semi-supervised KPI-Driven Learning

Composite Score — KPI-Driven Objective for Tiering

To guide our semi-supervised and hybrid methods, we define a Composite Score that unifies Microsoft’s key business KPIs into a single optimization target. It ensures that all modeling layers—Pure KPI-Based Tiering and Hybrid KPI-Aware Segmentation—optimize toward the same business priorities. Unsupervised clustering cannot optimize business outcomes. A composite objective is needed to consistently evaluate and improve tiering performance across:

Potential uplift (TPA)
Stability of tier structure (SFI)
Within-tier improvement (TCI_PI)
Revenue scale (TCI_REV)

To align tiering with business priorities, we summarize four key KPIs—TPA, SFI, TCI_PI, and TCI_REV—into one normalized measure:

Composite Score = 0.35×TPA + 0.35×SFI + 0.30×(TCI_PI + TCI_REV)

This score provides a single benchmark for comparing methods and serves as the optimization target in our semi-supervised and hybrid approaches.

How It Is Used

Benchmarking: Compare all methods on a unified scale.
Optimization: Serves as the objective in constrained local search (Method 3).
Rule Learning: Guides the decision-tree logic extracted after optimization.

Why It Matters

The Composite Score centers the analysis around a single question:
“Which tiering structure creates the strongest balance of growth potential, stability, and revenue impact?”

2.3 Pure KPI-Based Tiering

2.3.1 Model Conclusions at a Glance

Pure KPI-based tiering shows what the tiers would look like if Microsoft prioritized business KPIs above all else. It achieves the largest KPI improvements, but causes major distribution shifts and violates movement rules, making it operationally unrealistic.

Final takeaway: Pure KPI tiering is a valuable benchmark for understanding KPI potential, but cannot be operationalized.

2.3.2 High-Level Algorithm Summary

Table 3. Methods of KPI-Based Tiering
Method	Algorithm Summary	Strengths	Weaknesses	Business Use
New_Tier_Direct (PI ranking only)	Rank accounts by PI/KPI score and assign tiers directly	Highest KPI gains; preserves overall tier distribution	Moves ~20–40% companies; violates ±1 rule; disrupts continuity	KPI upper-bound benchmark
Tier_PI_Constrained (PI ranking + ±1 rule)	Same as above but restrict movement to adjacent tiers	KPI lift + respects movement constraint	Still moves ~20–40%; breaks tier distribution (Tier C inflation)	Diagnostic only

2.3.3 Modeling Results

Table 4. Modeling Results for KPI-Based Tiering
KPI	FY26 Baseline	New_Tier_Direct	Tier_PI_Constrained
Composite Score	0.5804	0.8105	0.763
TPA	0.2590	0.8300	0.721
TCI_PI	0.2220	0.5360	0.492
TCI_REV	0.4690	0.3970	0.452
SFI	0.8070	0.6860	0.650

New_Tier_Direct

Composite Score: 0.5804 → 0.8105
TPA increases sharply (0.259 → 0.830)
Violates ±1 rule; major reassignments (~20%–40%)

Tier_PI_Constrained

Respects ±1 movement
KPI still improves (Composite 0.763)
But tier distribution collapses (Tier C over-expands)
Still ~20–40% movement → not feasible

Hence:
No PI-only method balances KPI lift with operational feasibility.

2.3.4 Limitations & Next Steps

Pure KPI tiering cannot simultaneously:

preserve tier distribution,
respect ±1 movement rule, and
deliver consistent KPI improvements.

This creates the need for a hybrid model that combines clustering structure with KPI-aligned tier ordering.

2.4 Hybrid KPI-Aware Segmentation (Our Contribution)

2.4.1 Model Conclusions at a Glance

Our hybrid method blends clustering geometry with KPI-driven optimization, achieving a practical balance between:

statistical structure,
business constraints, and
KPI improvement.

Final Recommendation:
This is the segmentation framework we recommend Microsoft to adopt.

➔ It produces the most deployable segmentation by balancing KPI lift with stability and interpretability.

➔ Delivers meaningful KPI improvement while changing only ~5% of accounts, compared to Model B’s 20–40%.

2.4.2 High-Level Algorithm Summary

Table 5. Algorithm Comparison
Component	Purpose	Strengths	Notes
Constrained Local Search	Optimize composite KPI score starting from FY26 tiers	KPI uplift with strict constraints	Only small movements allowed (~5%)
Tier Movement Constraint (+1/–1)	Ensure realistic transitions	Guarantees business rules; keeps structure stable	Limits improvement ceiling
Decision Tree	Learn interpretable rules from optimized tiers	Deployable, explainable, reusable	Accuracy ~80%; tunable with weighting
Closed Loop Optimization	Improve both rules and allocation iteratively	Stable + interpretable	Future extension

2.4.3 Modeling Results

Table 6. Modeling Results for Hybrid Segmentation
KPI	FY26 Baseline	New_Tier_Direct	Tier_PI_Constrained	ImprovedTier
Composite Score	0.5804	0.8105	0.763	0.6512
TPA	0.2590	0.8300	0.721	0.2990
TCI_PI	0.2220	0.5360	0.492	0.3450
TCI_REV	0.4690	0.3970	0.452	0.5250
SFI	0.8070	0.6860	0.650	0.8160

Fig 2. Visualization of Decision TreeFig 3. Explanation of Decision Tree

Interpretation of Hybrid Model (Improved Tier)

Composite Score: 0.5804 → 0.6512
TPA improvement (0.259 → 0.299)
TCI_PI and TCI_REV both rise
SFI improves compared to constrained PI method
Only ~5% of companies move tiers, versus Method 2’s 20–40%

This makes Method 3 the only method that simultaneously satisfies:

KPI improvement
original tier distribution
±1 movement rule
low operational disruption
interpretability (via decision tree)

2.4.4 Conclusion

Model C offers a pragmatic middle ground: KPI lift close to pure PI tiering, operational impact close to clustering, and full interpretability.

For Microsoft, this hybrid framework is the most realistic and sustainable segmentation approach

3. Dynamic Tier Progression

3.1 Model Conclusions at a Glance

Our benchmarking shows that CatBoost and XGBoost consistently deliver the strongest overall performance, achieving the highest macro-F1 (~0.76) across all tested methods. However, despite these gains, the underlying business pattern remains dominant: tier changes are extremely rare (≈5.4%), and Microsoft’s one-step movement rule severely limits model learnability.

Dynamic tiering is far more valuable as a diagnostic signal generator than a strict forecasting engine. While models cannot reliably predict future tier transitions, they can surface atypical account patterns, signals of risk, and emerging opportunities that support earlier sales intervention and more proactive account planning.

3.2 Models

To predict future model upgrades and downgrades, we tested the following models:

Table 7. Models Used for Dynamic Prediction
Model	Strengths	Weaknesses	When to Use
MLR	Simple; interpretable; fast baseline	Weak on imbalanced data	When transparency and explainability are needed
Neural Network	Captures nonlinear patterns; stronger recall than MLR	Requires tuning; sensitive to imbalance data	When exploring richer behavioral signals
CatBoost (baseline, weighted, oversampled)	Strongest overall balance; robust with categorical data; best macro-F1	Still limited by rarity of tier changes; weighted/oversampled versions risk overfitting	Default diagnostic model for surfacing atypical account patterns
XGBoost (baseline, weighted)	High performance; scalable; production-ready	Limited by structural imbalance; weighted versions increase false positives	When deploying a stable scoring layer to sales teams

Performance was then measured using accuracy, but more importantly, macro recall, precision, and F1, since upgrades and downgrades are much rarer and require balanced evaluation.

3.3 Model Results

Across all models, overall accuracy appears high (0.95–0.97), but this metric is dominated by the fact that Tier transitions are extremely rare — only 808 of 15,000 cases (5.4%) moved tiers, while 95% stayed unchanged. According to macro metrics such as recall, precision, and F1, every model struggles to reliably detect upgrades and downgrades.

CatBoost and XGBoost deliver the strongest balanced results, achieving the highest macro F1 scores (~0.76). However, even these advanced methods only capture half or fewer of the true upgrade and downgrade events. This reinforces that the challenge is not algorithmic performance, but the underlying business pattern: tier movements are infrequent, policy-driven, and weakly connected to observable account features.

Table 8. Results for Dynamic Prediction
Model	Accuracy	Macro
Model	Accuracy	Recall	Precision	F1 Score
MLR	0.95	0.36	0.70	0.37
Neural Network	0.95	0.58	0.71	0.63
CatBoost	0.97	0.94	0.67	0.76
CatBoost (Weighted)	0.82	0.49	0.82	0.54
CatBoost (Oversampling)	0.69	0.42	0.75	0.42
XGBoost	0.97	0.93	0.67	0.76
XGBoost (Weighted)	0.97	0.85	0.70	0.76

3.4 Dynamic Tiering Implications

Based on the results, our dynamic tiering will have the following implications to Microsoft:

Tier changes are not reliably forecastable under current rules.

Year-over-year stability is so dominant that even strong ML models cannot surface consistent upgrade or downgrade signals. This suggests that transitions are driven more by sales judgment and tier policy than by measurable account behavior.

The dynamic model is still valuable: just not as a predictor of future tiers.

Rather than serving as a forecasting engine, this pipeline should be viewed as a diagnostic tool that helps identify accounts with unusual patterns, emerging risks, or outlier behavior worth reviewing.

Dynamic progression complements, rather than replaces, the core segmentation.

It provides an additional layer of insight alongside clustering and KPI-optimized segmentation, helping Microsoft maintain both structural clarity (static segmentation) and forward-looking awareness (dynamic progression).

4. Optimization in Practice

To understand how segmentation could support downstream coverage planning, we developed a small optimization proof-of-concept using Microsoft’s seller–tier capacity guidelines (e.g., max accounts per role × tier, geo-entity restrictions, in-person vs remote coverage rules).

4.1 What We Explored

Using our final hybrid segmentation (Method 3), we tested a simplified workflow:

Formulate a coverage optimization problem

○ Assign sellers to accounts under constraints such as:

- - role × tier capacity limits,
  - single-geo assignment,
  - ±1 tier movement rules,
  - domain restrictions for Tier C/D.

○ This naturally forms a mixed-integer optimization problem (MIP).

Prototype with standard optimization tools

○ Linear and integer programming formulations using Gurobi, OR-Tools, and Pyomo.

○ Heuristic solvers (e.g., local search, greedy reallocation, hill climbing) as faster alternatives.

Simulate coverage scenarios

○ Estimate changes in workload balance and whitespace prioritization under different seller–tier mixes.

○ Validate feasibility of the optimization with respect to Microsoft’s operational rules.

4.2 What We Learned

Due to limited operational metrics (detailed whitespace values, upgrade probabilities, territory boundaries) and time constraints, we did not build a fully deployable engine. However, the PoC confirmed that:

The segmentation integrates cleanly into a prescriptive segmentation → optimization → coverage pipeline.
A full solver could feasibly allocate sellers under realistic business constraints.
Gurobi-style MIP formulations and simulation-based heuristics are both valid paths for future development.

In short: the optimization layer is technically viable and aligns naturally with our segmentation design, but its full implementation exceeds the scope of this capstone.

5. AI & LLM Integration

To make segmentation accessible to a broad set of stakeholders like sales leaders, strategists, and business analysts, we built a conversational tiering assistant powered by LLM-based interpretation of strategic priorities. The assistant allows users to describe their intended segmentation direction in natural language, which the system translates into numerical weights and a refreshed set of tier assignments.

5.1 LLM Workflow Architecture

The following flowchart demonstrates how the LLM work:

Fig 4. End-to-End LM Workflow

Users communicate their goals using intuitive, high-level language (e.g. “prioritize runway growth”, “reward high-potential emerging accounts”). Front end collects the user’s tiering preference through a chat interface.
The frontend sends this prompt to our cloud FastAPI service on Render.
The LLM interprets the prompt and infers the relative strategic weights and which clustering method to use (KPI-based or Hybrid Approach).
The server applies these weights in the tiering code to generate updated tiers based on the selected approach.
The server returns a refreshed CSV with new tier assignments which can be exported through the chat interface.

5.2 Why LLMs Matter

LLMs enhanced the project in three ways:

Interpretation Layer: Helps business users articulate strategy in plain English and convert it to quantifiable modeling inputs.
Explainability Layer: Surfaces cluster drivers, feature differences, and trade-offs across segments in natural language.
Acceleration Layer: Enables real-time exploration of “what-if” tiering scenarios without engineering support.

This integration transforms segmentation from a static analytical artifact into a dynamic, interactive decision-support tool, aligned with how Microsoft teams actually work.

5.3 Backend Architecture and LLM Integration Pipeline

The conversational tiering system is supported by a cloud-based backend designed to translate natural-language instructions into structured model parameters. The service is deployed on Render and implemented with FastAPI, providing a lightweight, high-performance gateway for managing requests, validating inputs, and coordinating LLM interactions.

FastAPI as the Orchestration Layer - User instructions are submitted through the chat interface and delivered to a FastAPI endpoint as JSON. FastAPI validates this payload using Pydantic, ensuring the request is well-formed before any processing occurs. The framework manages routing, serialization, and error handling, isolating request management from the downstream LLM and computation layers.
LLM Invocation Through the OpenAI API - Once a validated prompt is received, the backend invokes the OpenAI API using a structured system prompt engineered to enforce strict JSON output. The LLM returns four normalized weights reflecting the user’s strategic intent, along with metadata used to determine whether the user explicitly prefers a KPI-based method or the default Hybrid approach. If no method is specified, the system automatically defaults to Hybrid. Low-temperature decoding is used to minimize stochastic variation and ensure repeatability across identical user prompts. All OpenAI keys are securely stored as Render environment variables.
Schema Enforcement and Robust Parsing -To maintain reliability, the backend enforces strict schema validation on LLM responses. The service checks both JSON structure and numeric constraints, ensuring values fall within valid ranges and sum to one. If parsing fails or constraints are violated, the backend automatically reissues a constrained correction prompt. This design prevents malformed outputs and guards against conversational drift.
Render Hosting and Operational Considerations - The backend runs in a stateless containerized environment on Render, which handles service orchestration, HTTPS termination, and environment-variable management. Data required for computation is loaded into memory at startup to reduce latency, and the lightweight tiering pipeline ensures that the system remains responsive even under shared compute resources.
Response Assembly and Delivery - After LLM interpretation and schema validation, the backend applies the resulting weights and streams the recalculated results back to the user as a downloadable CSV. FastAPI’s Streaming Response enables direct transmission from memory without temporary filesystem storage, supporting rapid interactive workflows.

Together, these components form a tightly integrated, cloud-native pipeline: FastAPI handles orchestration, the LLM provides semantic interpretation, Render ensures secure and reliable hosting, and the default Hybrid method ensures consistent behavior unless the user explicitly requests the KPI approach.

DEMO: Microsoft x UCLA Anderson MSBA - AI-Driven KPI Segmentation Project (LLM demo)

6. Conclusion

Our work delivers a strategic, KPI-driven tiering architecture that resolves the limitations of Microsoft’s legacy system and sets a scalable foundation for future segmentation and coverage strategy. Across all analyses, five differentiators stand out:

Clear separation of natural structure vs. business intent: We diagnose where the legacy system diverges from true customer potential and revenue—establishing the analytical ground truth Microsoft never previously had.
A precise map of strategic trade-offs: By comparing unsupervised, KPI-only, and hybrid approaches, we reveal the operational and business implications behind every tiering philosophy—making the framework decision-ready for leadership.
A business-aligned segmentation ready for deployment: Our hybrid KPI-aware model uniquely satisfies KPI lift, distribution stability, ±1 movement rules, and interpretability—providing a reliable go-forward tiering backbone.
A future-proof architecture that extends beyond static tiers: Dynamic progression modeling and optimization PoC show how tiering can evolve into forecasting, prioritization, whitespace planning, and resource optimization.
A blueprint for Microsoft’s next-generation tiering ecosystem: The system integrates data science, business KPIs, optimization, and LLM interpretability into one cohesive workflow—positioning Microsoft for an AI-enabled tiering strategy.

In essence, this work transforms customer tiering into a strategic, explainable, and scalable system—ready to support Microsoft’s growth ambitions and future AI initiatives.

Guide for Architecting Azure-Databricks: Design to Deployment

Rafia_Aqil — Mon, 04 May 2026 16:07:44 GMT

Author's: Chris Walk cwalk, Dan Johnson danjohn1234, Eduardo dos Santos eduardomdossantos, Ted Kim tekim, Eric Kwashie ekwashie, Chris Haynes Chris_Haynes, Tayo Akigbogun takigbogun and Rafia Aqil Rafia_Aqil

Peer Reviewed: Mohamed Sharaf mohamedsharaf

Note: We are currently updating this article to add: Serverless Workspace option. Also, while Terraform is the recommended method for production deployments due to its automation and repeatability, for simplicity in this article we will demonstrate deployment through the Azure portal.

Introduction Video to Databricks: what is databricks | introduction - databricks for dummies

DESIGN: Architecting a Secure Azure Databricks Environment

Step 1: Plan Workspace, Subscription Organization, Analytics Architecture and Compute

Planning your Azure Databricks environment can follow various arrangements depending on your organization’s structure, governance model, and workload requirements. The following guidance outlines key considerations to help you design a well-architected foundation.

1.1 Align Workspaces with Business Units

A recommended best practice is to align each Azure Databricks workspace with a specific business unit. This approach—often referred to as the “Business Unit Subscription” design pattern—offers several operational and governance advantages.

- Streamlined Access Control: Each unit manages its own workspace, simplifying permissions and reducing cross-team access risks. For example, Sales can securely access only their data and notebooks.

- Cost Transparency: Mapping workspaces to business units enables accurate cost attribution and supports internal chargeback models. Each workspace can be tagged to a cost center for visibility and accountability. Even within the same workspace, costs can be controlled using system tables that provide detailed usage metrics and resource consumption insights.

Challenges to keep-in-mind: While per-BU workspaces have high impact, be mindful of workspace sprawl. If every small team spins up its own workspace, you might end up with dozens or hundreds of workspaces, which introduces management overhead. Databricks recommends a reasonable upper limit (on Azure, roughly 20–50 workspaces per account/subscription) because managing “collaboration, access, and security across hundreds of workspaces can become extremely difficult, even with good automation” [1]. Each workspace will need governance (user provisioning, monitoring, compliance checks), so there is a balance to strike.

1.2 Workspace Alignment and Shared Metastore Strategy

As you align workspaces with business units, it's essential to understand how Unity Catalog and the metastore fit into your architecture. Unity Catalog is Databricks’ unified governance layer that centralizes access control, auditing, and data lineage across workspaces. Each Unity Catalog is backed by a metastore, which acts as the central metadata repository for tables, views, volumes, and other data assets. In Azure Databricks, you can have one metastore per region, and all workspaces within that region share it. This enables consistent governance and simplifies data sharing across teams. If your organization spans multiple regions, you’ll need to plan for cross-region sharing, which Unity Catalog supports through Delta Sharing. By aligning workspaces with business units and connecting them to a shared metastore, you ensure that governance policies are enforced uniformly, while still allowing each team to manage its own data assets securely and independently.

1.3 Distribute Workspaces Across Subscriptions

When scaling Azure Databricks, consider not just the number of workspaces, but also how to distribute them across Azure subscriptions. Using multiple Azure subscriptions can serve both organizational needs and technical requirements:

- Environment Segmentation (Dev/Test/Prod): A common pattern is to put production workspaces in a separate Azure subscription from development or test workspaces. This provides an extra layer of isolation. Microsoft highly recommends separating workspaces into prod and dev, in separate subscriptions. This way, you can apply stricter Azure policies or network rules to the prod subscription and keep the dev subscription a bit more open for experimentation without risking prod resources.

- Honor Azure Resource Limits: Azure subscriptions come with certain capacity limits and Azure Databricks workspaces have their own limits (since it’s a multi-tenant PaaS). If you put all workspaces in one subscription, or all teams in one workspace, you might hit those limits.

Most enterprises naturally end up with multiple subscriptions as they grow – planning this early avoids later migration headaches. If you currently have everything in one subscription, evaluate usage and consider splitting off heavy workloads or prod workloads into a new one to adhere to best practices.

1.4 Consider Completing Azure Landing Zone Assessment

When evaluating and planning your next deployment, it’s essential to ensure that your current landing zone aligns with Microsoft best practices. This helps establish a robust Databricks architecture and minimizes the risk of avoidable issues. Additionally, customers who are early in their cloud journey can benefit from Cloud Assessments—such as an Azure Landing Zone Review and a review of the “Prepare for Cloud Adoption” documentation—to build a strong foundation.

1.5 Planning Your Azure Databricks Workspace Architecture

Your workspace architecture should reflect the operational model of your organization and support the workloads you intend to run, from exploratory notebooks to production-grade ETL pipelines. To support your planning, Microsoft provides several reference architectures that illustrate well-architected patterns for Databricks deployments. These solution ideas can serve as starting points for designing maintainable environments: Simplified Architecture: Modern Data Platform Architecture, ETL-Intensive Workload Reference Architecture: Building ETL Intensive Architecture, End-to-End Analytics Architecture: Create a Modern Analytics Architecture.

1.6 Planning for that “Right” Compute

Choosing the right compute setup in Azure Databricks is crucial for optimizing performance and controlling costs, as billing is based on Databricks Units (DBUs) using a per-second pricing model.

- Classic Compute: You can fine-tune your own compute by enabling auto-termination and autoscaling, using Photon acceleration, leveraging spot instances, selecting the right VM type and node count for your workload, and choosing SSDs for performance or HDDs for archival storage.

- - Preferred by mature internal teams and developers who need advanced control over clusters—such as custom VM selection, tuning, and specialized configurations.

- Serverless Compute: Alternatively, managed services can simplify operations with built-in optimizations.

- - Removes infrastructure management and offers instant scaling without cluster warm-up, making it ideal for agility and simplicity.

Step 2: Plan the “Right” CIDR Range (Classic Compute)

Note: You can skip this step if you plan to use serverless compute for all your resources, as CIDR range planning is not required in serverless deployments.

When planning CIDR ranges for your Azure Databricks workspace, it's important to ensure your virtual network has enough IP address capacity to support cluster scaling. Why this matters: If you choose a small VNet address space and your analytics workloads grow, you might hit a ceiling where you simply cannot launch more clusters or scale-out because there are no free IPs in the subnet. The subnet sizes—and by extension, the VNet CIDR—determine how many nodes you can. Databricks recommends using a CIDR block between /16 and /24 for the VNet, and up to /26 for the two required subnets: the container subnet and the host subnet. Here’s a reference Microsoft provides.

If your current workspace’s VNet lacks sufficient IP space for active cluster nodes, you can request a CIDR range update through your Azure Databricks account team as noted in the Microsoft documentation.

2.1 Considerations for CIDR Range

- Workload Type & Concurrency: Consider what kinds of workloads will run (ETL Pipelines, Machine Learning Notebooks, BI Dashboards, etc.) and how many jobs or clusters may need to run in parallel. High concurrency (e.g. multiple ETL jobs or many interactive clusters) means more nodes running at the same time, requiring a larger pool of IP addresses.

- Data Volume (Historical vs. Incremental): Are you doing a one-time historical data load or only processing new incremental data? A large backfill of terabytes of data may require spinning up a very large cluster (hundreds of nodes) to process in a reasonable time. Ongoing smaller loads might get by with fewer nodes. Estimate how much data needs processing.

- Transformation Complexity: The complexity of data transformations or machine learning workloads matters. Heavy transformations (joins, aggregations on big data) or complex model training can benefit more workers. If your use cases include these, you may need larger clusters (more nodes) to meet performance SLAs, which in turn demands more IP addresses available in the subnet.

- Data Sources and Integration: Consider how your Databricks environment will connect to data. If you have multiple data sources or sinks (e.g. ingest from many event hubs, databases, or IoT streams), you might design multiple dedicated clusters or workflows, potentially all active at once. Also, if using separate job clusters per job (Databricks Jobs), multiple clusters might launch concurrently. All these scenarios increase concurrent node count.

2.2 Configuring a Dedicated Network (VNet) per Workspace with Egress Control

By default, Azure Databricks deploys its classic compute resources into a Microsoft-managed virtual network (VNet) within your Azure subscription. While this simplifies setup, it limits control over network configuration. For enhanced security and flexibility, it's recommended to use VNet Injection, which allows you to deploy the compute plane into your own customer-managed VNet. This approach enables secure integration with other Azure services using service endpoints or private endpoints, supports user-defined routes for accessing on-premises data sources, allows traffic inspection via network virtual appliances or firewalls, and provides the ability to configure custom DNS and enforce egress restrictions through network security group (NSG) rules.

Within this VNet (which must reside in the same region and subscription as the Azure Databricks workspace), two subnets are required for Azure Databricks: a container subnet (referred to as private subnet) and a host subnet (referred to as public subnet). To implement front-end Private Link, back-end Private Link, or both, your workspace VNet needs a third subnet that will contain the private endpoint (PrivateLink subnet). It is recommended to also deploy an Azure Firewall for egress control.

Step 3: Plan Network Architecture for Securing Azure-Databricks

3.1 Secure Cluster Connectivity

Secure Cluster Connectivity, also known as No Public IP (NPIP), is a foundational security feature for Azure Databricks deployments. When enabled, it ensures that compute resources within the customer-managed virtual network (VNet) do not have public IP addresses, and no inbound ports are exposed. Instead, each cluster initiates a secure outbound connection to the Databricks control plane using port 443 (HTTPS), through a dedicated relay. This tunnel is used exclusively for administrative tasks, separate from the web application and REST API traffic, significantly reducing the attack surface. For the most secure deployment, Microsoft and Databricks strongly recommend enabling Secure Cluster Connectivity, especially in environments with strict compliance or regulatory requirements. When Secure Cluster Connectivity is enabled, both workspace subnets become private, as cluster nodes don’t have public IP addresses.

3.2 Egress with VNet Injection (NVA)

3.3 Front-End Connectivity with Azure Private Link (Standard Deployment)

To further enhance security, Azure Databricks supports Private Link for front-end connections. In a standard deployment, Private Link enables users to access the Databricks web application, REST API, and JDBC/ODBC endpoints over a private VNet interface, bypassing the public internet.

For organizations with no public internet access from user networks, a browser authentication private endpoint is required. This endpoint supports SSO login callbacks from Microsoft Entra ID and is shared across all workspaces in a region using the same private DNS zone. It is typically hosted in a transit VNet that bridges on-premises networks and Azure. Note: There are two deployment types: standard and simplified. To compare these deployment types, see Choose standard or simplified deployment.

3.4 Serverless Compute Networking

Azure Databricks offers serverless compute options that simplify infrastructure management and accelerate workload execution. These resources run in a Databricks-managed serverless compute plane, isolated from the public internet and connected to the control plane via the Microsoft backbone network.

To secure outbound traffic from serverless workloads, administrators can configure Serverless Egress Control using network policies that restrict connections by location, FQDN, or Azure resource type. Additionally, Network Connectivity Configurations (NCCs) allow centralized management of private endpoints and firewall rules. NCCs can be attached to multiple workspaces and are essential for enabling secure access to Azure services like Data Lake Storage from serverless SQL warehouses.

DEPLOYMENT: Step-to-Step Implementation using Azure Portal

Step 1: Create an Azure Resource Group

For each new workspace, create a dedicated Resource Group (to contain the Databricks workspace resource and associated resources). Ensure that all resources are deployed in the same Region and Resource Group (i.e. workspace, subnets...) to optimize data movement performance and enhance security.

Step 2: Deploy Workspace Specific Virtual Network (VNET)

From your Resource Group, create a Virtual Network.

Under the Security section, enable Azure Firewall.
Deploying an Azure Firewall is recommended for egress control, ensuring that outbound traffic from your Databricks environment is securely managed.

Define address spaces for your Virtual Network (Review Step 2 from Design).

As documented, you could create a VNet with these values:

- IP range: First remove the default IP range, and then add IP range 10.28.0.0/23.

- Create subnet public-subnet with range 10.28.0.0/25.

- Create subnet private-subnet with range 10.28.0.128/25.

- Create subnet private-link with range 10.28.1.0/27.

Please note: your IP values can be different depending on your IPAM and available scopes.

Review + Create your Virtual Network.

Step 3: Deploy Azure-Databricks Workspace:

Now that networking is in place, create the Databricks workspace. Below are detailed steps your organization should review while creating workspace creation:

In Azure Portal, search for Azure Databricks and click Create.

Choose the Subscription, RG, Region, select Premium, enter in “Managed Resource Group name” and click Next.

Managed Resource Group- will be created after your Databrick workspace is deployed and contains infrastructure resources for the workspace i.e. VNets, DBFS.

Required: Enable “Secure Cluster Connectivity” (No Public IP for clusters), to ensure that Databricks clusters are deployed without public IP addresses (Review Section 3.1).

Required: Enable the option to deploy into your Virtual Network (VNet Injection), also known as “Bring Your Own VNet” (Review Section 3.2).
Select the Virtual Network created in Step 2.

Enter Private, Public Subnet Names.

Enable or Disable “Deploying Nat Gateway”, according to your workspace requirement.

Disable “Allow Public Network Access”.
Select “No Azure Databricks Rules” for Required NSG Rules.

Select “Click on add to create a private endpoint”, this will open a panel for private endpoint setup.

Click “Add” to enter your Private Link details created in Step 2.
Also, ensure that Private DNS zone integration is set to “Yes” and that a new Private DNS Zone is created, indicated by (New)privatelink.azuredatabricks.net. Unless an existing DNS zone for this purpose already exists.

(Optional) Under Encryption Tab, Enable Infrastructure Encryption, if you have requirement for FIPS 140-2. It comes at a cost, it takes time to encrypt and decrypt. By default your data is already encrypted. If you have a standard regulatory requirement (ex. HIPAA).

(Optional) Compliance security profile- for HIPAA.

(Optional) Automatic cluster updates, First Sunday of every Month.

Review + Create the workspace and wait for it to deploy.

Step 4: Create a private endpoint to support SSO for web browser access:

Note: This step is required when front-end Private Link is enabled, and client networks cannot access the public internet.

After creating your Azure Databricks workspace, if you try to launch it without the proper Private Link configuration, you will see an error like the image below:

This happens because the workspace is configured to block public network access, and the necessary Private Endpoints (including the browser_authentication endpoint for SSO) are not yet in place.

Create Web-Auth Workspace

Note: Deploy a “dummy”: WEB_AUTH_DO_NOT_DELETE_<region> workspace in the same region as your production workspace. Purpose: Host the browser_authentication private endpoint (one required per region). Lock the workspace (Delete lock) to prevent accidental removal.

Follow step 2 to create Virtual Network (Vnet)

Follow step 3 and create a VNet injected “dummy” workspace.

Create Browser Authentication Private Endpoint

In Azure Portal, Databricks workspace (dummy), Networking, Private endpoint connections, + Private endpoint.

Resource step:

Target sub-resource: browser_authentication

Virtual Network step:

VNet: Transit/Hub VNet (central network for Private Link)
Subnet: Private Endpoint subnet in that VNet (not Databricks host subnets)

DNS step:

Integrate with Private DNS zone: Yes

Zone: privatelink.azuredatabricks.net
Ensure DNS zone is linked to the Transit VNet

After creation:

A-records for *.pl-auth.azuredatabricks.net are auto-created in the DNS zone.

Workspace Connectivity Testing

If you have VPN or ExpressRoute, Bastion is not required. However, for the purposes of this article we will be testing our workpace connectivity through Bastion.

If you don’t have private connectivity and need to test from inside the VNet, Azure Bastion is a convenient option.

Step 5: Create Storage Account

From your Resource Group, click Create and select Storage account.

On the configuration page:

Select Preferred Storage type as: Azure Blob Storage or Azure Data Lake Storage Gen 2.

Choose Performance and Redundancy options based on your business requirements.

Click Next to proceed.

Under the Advanced tab:

Enable Hierarchical namespace under Data Lake Storage Gen2.
This is critical for: Directory and file-level operations, Access Control Lists (ACLs).

Under the Networking tab:

Set Public Network Access to Disabled.

Complete the creation process and then create container(s) inside the storage account.

Step 6: Create Private Endpoints for Workspace Storage Account

Pre-requisite:

You need to create two private endpoints from the VNet used for VNet injection to your workspace storage account for the following Target sub-resources: dfs and blob.

Navigate to your Storage Account.
Go to Networking, Private Endpoints tab and click on to + Create Private Endpoint.

In the Create Private Endpoint wizard:

Resource tab:

Select your Storage Account.
Set Target sub-resource to dfs for the first endpoint.

Virtual Network tab:

Choose the VNet you used for VNet injection.
Select the appropriate subnet.

Complete the creation process. The private endpoint will be auto approved and visible under Private Endpoints.

Repeat the process for the second private endpoint:

- This time set Target sub-resource to blob.

Step 7: Link Storage and Databricks Workspace:

Create Access Connector

In your Resource Group, create an Access Connector for Azure Databricks.
No additional configuration is required during creation.

Assign Role to Access Connector

Navigate to your Storage Account, Access Control (IAM), Add role assignment.

Select:
- Role: Storage Blob Data Contributor

- Assign access to: Managed Identity

Under Members:

- Click Select members.

- Find and select your newly created Access Connector for Azure Databricks.

- Save the role assignment.

Copy Resource ID

Go to the Access Connector Overview page.

Copy the Resource ID for later use in Databricks configuration.

Step 8: Link Storage and Databricks Workspace:

Navigate to Unity Catalog

In your Databricks Workspace, go to Unity Catalog, External Data and select “Create external Location” button.

Configure External Location

Select ADLS as the storage type.

Enter the ADLS storage URL in the following format:

abfss://<container_name>@<storage_account_name>.dfs.core.windows.net/

Update these two parameters: <container_name> and <storage_name>

Provide Access Connector

Select “Create new storage credential” from Storage credential field.
Paste the Resource ID of the Access Connector for Azure Databricks (from Step 10) into the Access Connector ID field.

Validate Connection

Click Submit.
You should see a “Successful” message confirming the connection.

Click submit and you should receive a “Successful” message, indicating your connection has succeeded.

You can now create Catalogs and link your secure storage.

Step 9: Configuring Serverless Compute Networking:

If your organization plans to use Serverless SQL Warehouses or Serverless Jobs Compute, you must configure Serverless Networking.

Add Network Connectivity Configuration (NCC)

Go to the Databricks Account Console: https://accounts.azuredatabricks.net/
Navigate to Cloud resources, click Add Network Connectivity Configuration.
Fill in the required fields and create a new NCC.

Associate NCC with Workspace

In the Account Console, go to Workspaces.

Select your workspace, click Update Workspace.
From the Network Connectivity Configuration dropdown, select the NCC you just created.

Add Private Endpoint Rule

In Cloud resources, select your NCC, select Private Endpoint Rules and click Add Private Endpoint Rule.

Provide:
Resource ID: Enter your Storage Account Resource ID.

Note: this can be found from your storage account, click on “JSON View” top right.

Azure Subresource type: dfs & blob.

Approve Pending Connection

Go to your Storage Account, Networking, Private Endpoints.

You will see a Pending connection from Databricks.

Approve the connection and you will see the Connection status in your Account Console as ESTABLISHED.

Step 10: Test Your Workspace:

Launch a small test cluster and verify the following:

- It can start (which means it can talk to the control plane).

- It can read/write from the storage, following the following code to confirm read/write to storage: Set Spark properties to configure Azure credentials to access Azure storage.

- Check Private DNS Record has been created.

- (Optional) If on-prem data is needed: try connecting to an on-prem database (using the ExpressRoute path): Connect your Azure Databricks workspace to your on-premises network - Azure Databricks | Microsoft Learn.

Step 11: Account Console, Planning Workspace Access Controls and Getting Started:

Once your Azure Databricks workspace is deployed, it's essential to configure access controls and begin onboarding users with the right permissions. From your account console: https://accounts.azuredatabricks.net/, you can centrally manage your environment: add users and groups, enable preview features, and view or configure all your workspaces.

Azure Databricks supports fine-grained access management through Unity Catalog, cluster policies, and workspace-level roles. Start by defining who needs access to what—whether it's notebooks, tables, jobs, or clusters—and apply least-privilege principles to minimize risk.

DBFS Limitation: DBFS is automatically created upon Databricks Workspace creation. DBFS can be found in your Managed Resource Group. Databricks cannot secure DBFS (see reference image below). If there is a business need to avoid DBFS then you can disable DBFS access following instructions here: Disable access to DBFS root and mounts in your existing Azure Databricks workspace.

Use Unity Catalog to manage data access across catalogs, schemas, and tables, and consider implementing cluster policies to standardize compute configurations across teams. To help your teams get started, Microsoft provides a range of tutorials and best practice guides: Best practice articles - Azure Databricks | Microsoft Learn.

Step 12: Planning Data Migration:

As you prepare to move data into your Azure Databricks environment, it's important to assess your migration strategy early. This includes identifying source systems, estimating data volumes, and determining the appropriate ingestion methods—whether batch, streaming, or hybrid. For organizations with complex migration needs or legacy systems, Microsoft offers specialized support through its internal Azure Cloud Accelerated Factory program. Reach out to your Microsoft account team to explore nomination for Azure Cloud Accelerated Factory, which provides hands-on guidance, tooling, and best practices to accelerate and streamline your data migration journey.

Summary

Regular maintenance and governance are as important as the initial design. Continuously review the environment and update configurations as needed to address evolving requirements and threats. For example, tag all resources (workspaces, VNets, clusters, etc.) with clear identifiers (workspace name, environment, department) to track costs and ownership effectively. Additionally, enforce least privilege across the platform: ensure that only necessary users are given admin privileges, and use cluster-level access control to restrict who can create or start clusters. By following the above steps, an organization will have an Azure Databricks architecture that is securely isolated, well-governed, and scalable.

References:

[1] 5 Best Practices for Databricks Workspaces

AzureDatabricksBestPractices/toc.md at master · Azure ... - GitHub

Deploy a workspace using the Azure Portal

Additional Links:

Quick Introduction to Databricks: what is databricks | introduction - databricks for dummies

Connect Purview with Azure Databricks: Integrating Microsoft Purview with Azure Databricks

Secure Databricks Delta Share between Workspaces: Secure Databricks Delta Share for Serverless Compute

Azure-Databricks Cost Optimization Guide: Databricks Cost Optimization: A Practical Guide

Integrate Azure Databricks with Microsoft Fabric: Integrating Azure Databricks with Microsoft Fabric

Databricks Solution Accelerators for Data & AI

Azure updates

Appendix

3.5 Understanding Data Transfer (Express Route vs. Public Internet)

For data transfers, your organization must decide to use ExpressRoute or Internet Egress. There are
several considerations that can help you determine your choice:

3.5.1. Connectivity Model

• ExpressRoute: Provides a private, dedicated connection between your on-premises infrastructure and
Microsoft Azure. It bypasses the public internet entirely and connects through a network service
provider.
• Internet Egress: Refers to outbound data traffic from Azure to the public internet. This is the default
path for most Azure services unless configured otherwise.

3.6 Planning for User-Defined Routes (UDRs)

When working with Databricks deployments—especially in VNet-injected workspaces—setting up User
Defined Routes (UDRs) is a smart move. It’s a best practice that helps manage and secure network traffic more effectively. By using UDRs, teams can steer traffic between Databricks components and external services in a controlled way, which not only boosts security but also supports compliance efforts.

3.6.1 UDRs and Hub and Spoke Topology
If your Databricks workspace is deployed into your own virtual network (VNet), you’ll need to
configure standard user-defined routes (UDRs) to manage traffic flow. In a typical hub-and-spoke architecture,
UDRs are used to route all traffic from the spoke VNets to the hub VNet.

3.6.2 Hub and Spoke with VWANHUB
If your Databricks workspace is deployed into your own virtual network (VNet) and is peered to a Virtual WAN (VWAN) hub as the primary connectivity hub into Azure, a user-defined route (UDR) is not required—provided that a private traffic routing policy or internet traffic routing policy is configured in the VWAN hub.

3.6.3 Use of NVAs and Service Tags

For Databricks traffic, you’ll need to assign a UDR to the Databricks-managed VNet with a next hop type of Network Virtual Appliance (NVA)—this could be an Azure Firewall, NAT Gateway, or another routing device. For control plane traffic, Databricks recommends using Azure service tags, which are logical groupings of IP addresses for Azure services and should be routed with the next hop type of internet. This is important because Azure IP ranges can change frequently as new resources are provisioned, and manually maintaining IP lists is not practical. Using service tags ensures that your routing rules automatically stay up to date.

3.6.4 Default Outbound Access Retirement (Non-Serverless Compute) Microsoft is retiring default outbound internet access for new deployments starting September 30,2025. Going forward, outbound connectivity will require an explicit configuration using an NVA, NAT Gateway, Load Balancer, or Public IP address. Also, note that using a Public IP Address in the deployment is discouraged for Security purposes, and it is recommended to deploy the workspace in a ‘Secure Cluster Connectivity ration.” Configure connectivity will require an explicit configuration using an NVA, NAT Gateway, Load Balancer, or Public IP address. Also, note that using a Public IP Address in the deployment is discouraged for Security purposes, and it is recommended to deploy the workspace in a ‘Secure Cluster Connectivity ration.”

Azure Databricks Cost Optimization: A Practical Guide

Rafia_Aqil — Sun, 14 Dec 2025 05:43:10 GMT

Co-Authored by: Sanjeev Nair Sanjeev Nair and Rafia Aqil Rafia_Aqil

This guide walks through a proven approach to Databricks cost optimization, structured in three phases: Discovery, Cluster/Data/Code Best Practices, and Team Alignment & Next Steps.

Phase 1: Discovery

Assessing Your Current State

The following questions are designed to guide your initial assessment and help you identify areas for improvement. Documenting answers to each will provide a baseline for optimization and inform the next phases of your cost management strategy.

Environment & Organization

Cluster Management

Cost Optimization

Data Management

Performance Monitoring

Future Planning

What is the current scale of your Databricks environment?

How many workspaces do you have?

How are your workspaces organized (e.g., by environment type, region, use case)?

How many clusters are deployed?

How many users are active?

What are the primary use cases for Databricks in your organization?

Data engineering
Data science
Machine learning
Business intelligence

How are clusters currently managed?

Manual configuration
Automated scripts
Databricks REST API
Cluster policies

What is the average cluster uptime?

Hours per day
Days per week

What is the average cluster utilization rate?

CPU usage
Memory usage

What is the current monthly spend on Databricks?

Total cost
Breakdown by workspace
Breakdown by cluster

What cost management tools are currently in use?

Azure Cost Management
Third-party tools

Are there any existing cost optimization strategies in place?

Reserved instances
Spot instances
Cluster auto-scaling

What is the current data storage strategy?

Data lake
Data warehouse
Hybrid

What is the average data ingestion rate?

GB per day
Number of files

What is the average data processing time?

ETL jobs
Machine learning models

What types of data formats are used in your environment?

Delta Lake
Parquet
JSON
CSV
Other formats relevant to your workloads

What performance monitoring tools are currently in use?

Databricks Ganglia
Azure Monitor
Third-party tools

What are the key performance metrics tracked?

Job execution time
Cluster performance
Data processing speed

Are there any planned expansions or changes to the Databricks environment?

New use cases
Increased data volume
Additional users

What are the long-term goals for Databricks cost optimization?

Reducing overall spend
Improving resource utilization & cost attribution
Enhancing performance

Understanding Databricks Cost Structure

Total Cost = Cloud Cost + DBU Cost

- Cloud Cost: Compute (VMs, networking, IP addresses), storage (ADLS, MLflow artifacts), other services (firewalls), cluster type (serverless compute, classic compute)

- DBU Cost: Workload size, cluster/warehouse size, photon acceleration, compute runtime, workspace tier, SKU type (Jobs, Delta Live Tables, All Purpose Clusters, Serverless), model serving, queries per second, model execution time

Diagnose Cost and Issues

Effectively diagnosing cost and performance issues in Databricks requires a structured approach. Use the following steps and metrics to gain visibility into your environment and uncover actionable insights.

1. Identify Costly Workloads

- Account Console Usage Reports: Review usage reports to identify usage breakdowns by product, SKU name, and custom tags.
- - Usage Breakdown by Product and SKU: Helps you understand which services and compute types (clusters, SQL warehouses, serverless options) are consuming the most resources.
  - Custom Tags for Attribution: Tags allow you to attribute costs to teams, projects, or departments, making it easier to identify high-cost areas.
  - Workflow and Job Analysis: By correlating usage data with workflows and jobs, you can pinpoint long-running or resource-heavy workloads that drive costs.
- Focus on Long-Running Workloads: Examine workloads with extended runtimes or high resource utilization.
- Key Question: Which pipelines or workloads are driving the majority of your costs?

Now That You’ve Identified Long-Running Workloads, Review These Key Areas:

2. Review Cluster Metrics

- CPU Utilization: Track guest, iowait, idle, irq, nice, softirq, steal, system, and user times to understand how compute resources are being used.
- Memory Utilization: Monitor used, free, buffer, and cached memory to identify over- or under-utilization.

- Key Question: Is your cluster over- or under-utilized? Are resources being wasted or stretched too thin?

3. Review SQL Warehouse Metrics

1. Live Statistics: Monitor warehouse status, running/queued queries, and current cluster count.
2. Time Scale Filter: Analyze query and cluster activity over different time frames (8 hours, 24 hours, 7 days, 14 days).
3. Peak Query Count Chart: Identify periods of high concurrency.
4. Completed Query Count Chart: Track throughput and query success/failure rates.
5. Running Clusters Chart: Observe cluster allocation and recycling events.
6. Query History Table: Filter and analyze queries by user, duration, status, and statement type.

- Key Question: Is your SQL Warehouse over- or under-utilized? Are resources being wasted or stretched too thin?

4. Review Spark UI

- Stages Tab: Look for skewed data, high input/output, and shuffle times. Uneven task durations may indicate data skew or inefficient data handling.

- Jobs Timeline: Identify long-running jobs or stages that consume excessive resources.

- Stage Analysis: Determine if stages are I/O bound or suffering from data skew/spill.

- Executor Metrics: Monitor memory usage, CPU utilization, and disk I/O. Frequent garbage collection or high memory usage may signal the need for better resource allocation.

4.1. Spark UI: Storage & Jobs Tab

- Storage Level: Check if data is stored in memory, on disk, or both.

- Size: Assess the size of cached data.

- Job Analysis: Investigate jobs that dominate the timeline or have unusually long durations. Look for gaps caused by complex execution plans, non-Spark code, driver overload, or cluster malfunction.

4.2. Spark UI: Executor Tab

- Storage Memory: Compare used vs. available memory.

- Task Time (Garbage Collection): Review long tasks and garbage collection times.

- Shuffle Read/Write: Measure data transferred between stages.

5. Additional Diagnostic Methods

- System Tables in Unity Catalog:
  - Query system tables for cost attribution and resource usage trends.
  - Cost Observability Queries
- Tagging Analysis:
  Use tags to identify which teams or projects consume the most resources.
- Dashboards & Alerts:
  Set up cost dashboards and budget alerts for proactive monitoring.

Phase 2: Cluster/Code/Data Best Practices Alignment

Cluster UI Configuration and Cost Attribution

Effectively configuring clusters/workloads in Databricks is essential for balancing performance, scalability, and cost. Tunning settings and features when used strategically can help organizations maximize resource efficiency and minimize unnecessary spending.

Key Configuration Strategies

1. Reduce Idle Time: Clusters to incur costs even when not actively processing workloads. To avoid paying for unused resources:

- Enable Auto-Terminate: Set clusters automatically shut down after a period of inactivity. This simple setting can significantly reduce wasted spending.

Enable Autoscaling: Workloads fluctuate in size and complexity. Autoscaling allows clusters to dynamically adjust the number of nodes based on demand:

- Automatic Resource Adjustment: Scale up for heavy jobs and scale down for lighter loads, ensuring you only pay for what you use.

- - It significantly enhances cost efficiency and overall performance.
  - For serverless and streaming, using Delta Live Tables with autoscaling is recommended.
  - This approach leads to better resource management and reliability.

Use Spot Instances: For batch processing and non-critical workloads, spot instances offer substantial cost savings:

- Lower VM Costs: Spot instances are typically much cheaper than standard VMs. However, they are not recommended for jobs requiring constant uptime due to potential interruptions.
- Considerations:
  - Azure Spot VMs are intended for non-critical, fault-tolerant tasks.
  - They can be evicted without notice, riskingproduction stability.
  - No SLA guarantees mean potentialdowntime for critical applications.
  - Using Spot VMs could lead to reliability issues in production environments.

Leverage Photon Engine: Photon is Databricks’ high-performance, vectorized query engine:

- Accelerate Large Workloads: Photon can dramatically reduce runtime for compute-intensive tasks, improving both speed and cost efficiency.

Keep Runtimes Up to Date: Using the latest Databricks runtime ensures optimal performance and security:

- Benefit from Improvements: Regular updates include performance enhancements, bug fixes, and new features.

Apply Cluster Policies: Cluster policies help standardize configurations and enforce cost controls across teams:

- Governance and Consistency: Policies can restrict certain settings, enforce tagging, and ensure clusters are created with cost-effective defaults.

Optimize Storage: type impacts both performance and cost:

- Switch from HDDs to SSDs: SSDs provide faster caching and shuffle operations, which can improve job efficiency and reduce runtime.

Tag Clusters for Cost Attribution: Tagging clusters enables granular tracking and reporting:

- Visibility and Accountability: Use tags to attribute costs to specific teams, projects, or environments, supporting better budgeting and chargeback processes.

Select the Right Cluster Type: Different workloads require different cluster types, see table below for Serverless vs Classic Compute:

Feature	Classic Compute	Serverless Compute
Control	Full control over config & network	Minimal control, fully managed by Databricks
Startup Time	Slower (unless pre-warmed)	Instant
Cost Model	Hourly, supports reservations	Pay-per-use, elastic scaling
Security	VNet injection, private endpoints	NCC-based private connectivity
Best For	Heavy ETL, ML, compliance workloads	Interactive queries, unpredictable demand

- Job Clusters: Ideal for scheduled jobs and Delta Live Tables.
- All-Purpose Clusters: Suited for ad-hoc analysis and collaborative work.
- Single-Node Clusters: Efficient for simple exploratory data analysis or pure Python tasks.
- Serverless Compute: Scalable, managed workloads with automatic resource management.

11. Monitor and Adjust Regularly: review cluster metrics and query history:

- Continuous Optimization: Use built-in dashboards to monitor usage, identify bottlenecks, and adjust cluster size or configuration as needed.

Code Best Practices

Avoid Reprocessing Large Tables

- Use a CDC (Change Data Capture) architecture with Delta Live Tables (DLT) to process only new or changed data, minimizing unnecessary computation.

Ensure Code Parallelizes Well

- Write Spark code that leverages parallel processing. Avoid loops, deeply nested structures, and inefficient user-defined functions (UDFs) that can hinder scalability.

Reduce Memory Consumption

- Tweak Spark configurations to minimize memory overhead. Clean out legacy or unnecessary settings that may have carried over from previous Spark versions.

Prefer SQL Over Complex Python

- Use SQL (declarative language) for Spark jobs whenever possible. SQL queries are typically more efficient and easier to optimize than complex Python logic.

Modularize Notebooks

- Use %run to split large notebooks into smaller, reusable modules. This improves maintainability.

Use LIMIT in Exploratory Queries

- When exploring data, always use the LIMIT clause to avoid scanning large datasets unnecessarily.

Monitor Job Performance

- Regularly review Spark UI to detect inefficiencies such as high shuffle, input, or output. Review the below table for optimization opportunities:

For more guidance review: Spark stage high I/O - Azure Databricks | Microsoft Learn

Databricks Code Performance Enhancements & Data Engineering Best Practices

By enabling the below features and applying best practices, you can significantly lower costs, accelerate job execution, and build Databricks pipelines that are both scalable and highly reliable. For more guidance review: Comprehensive Guide to Optimize Data Workloads | Databricks.

Feature / Technique	Purpose / Benefit	How to Use / Enable / Key Notes
Disk Caching	Accelerates repeated reads of Parquet files	Set spark.databricks.io.cache.enabled = true
Dynamic File Pruning (DFP)	Skips irrelevant data files during queries, improves query performance	Enabled by default in Databricks
Low Shuffle Merge	Reduces data rewriting during MERGE operations, less need to recalculate ZORDER	Use Databricks runtime with feature enabled
Adaptive Query Execution (AQE)	Dynamically optimizes query plans based on runtime statistics	Available in Spark 3.0+, enabled by default
Deletion Vectors	Efficient row removal/change without rewriting entire Parquet file	Enable in workspace settings, use with Delta Lake
Materialized Views	Faster BI queries, reduced compute for frequently accessed data	Create in Databricks SQL
Optimize	Compacts Delta Lake files, improves query performance	Run regularly, combine with ZORDER on high-cardinality columns
ZORDER	Physically sorts/co-locates data by chosen columns for faster queries	Use with OPTIMIZE, select columns frequently used in filters/joins
Auto Optimize	Automatically compacts small files during writes	Enable optimizeWrite and autoCompact table properties
Liquid Clustering	Simplifies data layout, replaces partitioning/ZORDER, flexible clustering keys	Recommended for new Delta tables, enables easy redefinition of clustering keys
File Size Tuning	Achieve optimal file size for performance and cost	Set delta.targetFileSize table property
Broadcast Hash Join	Optimizes joins by broadcasting smaller tables	Adjust spark.sql.autoBroadcastJoinThreshold and spark.databricks.adaptive.autoBroadcastJoinThreshold
Shuffle Hash Join	Faster join alternative to sort-merge join	Prefer over sort-merge join when broadcasting isn’t possible, Photon engine can help
Cost-Based Optimizer (CBO)	Improves query plans for complex joins	Enabled by default, collect column/table statistics with ANALYZE TABLE
Data Spilling & Skew	Handles uneven data distribution and excessive shuffle	Use AQE, set spark.sql.shuffle.partitions=auto, optimize partitioning
Data Explosion Management	Controls partition sizes after transformations (e.g., explode, join)	Adjust spark.sql.files.maxPartitionBytes, use repartition() after reads
Delta Merge	Efficient upserts and CDC (Change Data Capture)	Use MERGE operation in Delta Lake, combine with CDC architecture
Data Purging (Vacuum)	Removes stale data files, maintains storage efficiency	Run VACUUM regularly based on transaction frequency

Phase 3: Team Alignment and Next Steps

Implementing Cost Observability and Taking Action

Effective cost management in Databricks goes beyond configuration and code—it requires robust observability, granular tracking, and proactive measures. Below outlines how your teams can achieve this using system tables, tagging, dashboards, and actionable scripts.

Cost Observability with System Tables

Databricks Unity Catalog provides system tables that store operational data for your account. These tables enable historical cost observability and empower FinOps teams to analyze spend independently.

- System Tables Location: Found inside the Unity Catalog under the “system” schema.
- Key Benefits: Structured data for querying, historical analysis, and cost attribution.
- Action: Assign permissions to FinOps teams so they can access and analyze dedicated cost tables.

Enable Tags for Granular Tracking

Tagging is a powerful feature for tracking, reporting, and budgeting at a granular level.

- Classic Compute: Manually add key/value pairs when creating clusters, jobs, SQL Warehouses, or Model Serving endpoints. Use cluster policies to enforce custom tags.

- Serverless Compute: Create budget policies and assign permissions to teams or members for serverless workloads.

- Action: Tag all compute resources to enable detailed cost attribution and reporting.

Track Costs with Dashboards and Alerts

Databricks offers prebuilt dashboards and queries for cost forecasting and usage analysis.

- Dashboards: Visualize spend, usage trends, and forecast future costs.
- Prebuilt Queries: Use top queries with system tables to answer meaningful cost questions.
- Budget Alerts: Set up alerts in the Account Console (Usage > Budget) to receive notifications when spend approaches defined thresholds.

Build Culture of Efficiency

To go beyond technical fixes and build a culture of efficiency, by focusing on the below strategic actions:

- Collaborate with Internal Engineers:
  Spend time with engineering teams to understand workload patterns and optimization opportunities.
- Peer Reviews and Code Audits:
  Conduct regular code review sessions and peer reviews to ensure best practices are followed for Spark jobs, data pipelines, and cluster configurations.
- Create Internal Best Practice Documentation:
  Develop clear guidelines for writing optimized code, managing data, and maintaining clusters. Make these resources easily accessible for all teams.
- Implement Observability Dashboards:
  Use Databricks’ built-in features to create dashboards that track spend, monitor resource utilization, and highlight anomalies.
- Set Alerts and Budgets:
  Configure alerts for long-running workloads and establish budgets using prebuilt Databricks capabilities to prevent cost overruns.

5. Azure Reservations and Azure Savings Plan

When optimizing Databricks costs on Azure, it’s important to understand the two main commitment-based savings options: Azure Reservations and Azure Savings Plans. Both can help you reduce compute costs, but they differ in flexibility and how savings are applied.

Which Should You Choose?
- Reservations are ideal if you have stable, predictable Databricks workloads and want maximum savings.
- Savings Plans are better if you expect your compute needs to change, or if you want a simpler, more flexible way to save across multiple services.
Pro Tip: You can combine both options—use Reservations for your baseline, always-on Databricks clusters, and Savings Plans for bursty, variable, or new workloads.

Summary Table: Action Steps

It’s critical to monitor costs continuously and align your teams with established best practices, while scheduling regular code review sessions to ensure efficiency and consistency.

Area	Best Practice / Action
System Tables	Use for historical cost analysis and attribution
Tagging	Apply to all compute resources for granular tracking
Dashboards	Visualize spend, usage, and forecasts
Alerts	Set budget alerts for proactive cost management
Scripts/Queries	Build custom analysis tools for deep insights
Cluster/Data/Code Review & Align	Regularly review best practices, share findings, and align teams on optimization
Save on your Usage	Consider Azure Reservations and Azure Savings Plan

Overload to Optimal: Tuning Microsoft Fabric Capacity

Rafia_Aqil — Sun, 14 Dec 2025 05:44:40 GMT

Co-Authored by: Daya Ram, Sr. Cloud Solutions Architect and Rafia Aqil, Could Solutions Architect

Optimizing Microsoft Fabric capacity is both a performance and cost exercise. By diagnosing workloads, tuning cluster and Spark settings, and applying data best practices, teams can reduce run times, avoid throttling, and lower total cost of ownership—without compromising SLAs. Use Fabric’s built-in observability (Monitoring Hub, Capacity Metrics, Spark UI) to identify hot spots and then apply cluster- and data-level remediations.

Capacity Planning

For capacity planning and sizing guidance, see Plan your capacity size. Selecting the wrong SKU can lead to two major issues:

Over-provisioning: Paying for resources you don’t need.
Under-provisioning: Struggling with performance bottlenecks and failed jobs.

To simplify this process, Microsoft provides the Fabric SKU Estimator, a powerful tool designed to help organizations accurately size their capacity based on real-world usage patterns. Run the SKU Estimator before onboarding new workloads or scaling existing ones. Combine its recommendations with monitoring tools like Fabric Capacity Metrics to validate performance and adjust as needed.

Options to Diagnose Capacity Issues

1) Monitoring Hub — Start with the Story of the Run

What to use it for: Browse Spark activity across applications (notebooks, Spark Job Definitions, and pipelines). Quickly surface long‑running or anomalous runs; view read/write bytes, idle time, core allocation, and utilization.

How to use it

- From the Fabric portal, open Monitoring (Monitor Hub).

- Select a Notebook or Spark Job Definition to run and choose Historical Runs.

- Inspect the Run Duration chart; click on a run to see read/write bytes, idle time, core allocation, overall utilization, and other Spark metrics.

What to look for

- Use the guide: application detail monitoring to review and monitor your application.

2) Capacity Metrics App — Measure the Whole Environment

What to use it for: Review capacity-wide utilization and system events (overloads, queueing); compare utilization across time windows and identify sustained peaks.

How to use it

- Open the Microsoft Fabric Capacity Metrics app for your capacity.

- Review the Compute page (ribbon charts, utilization trends) and the System events tab to see overload or throttling windows.

- Use the Timepoint page to drill into a 30‑second interval and see which operations consumed the most compute.

What to look for

- Use the Troubleshooting guide: Monitor and identify capacity usage to pinpoint top CU‑consuming items.

3) Spark UI — Diagnose at Deeper Level

Why it matters: Spark UI exposes skew, shuffle, memory pressure, and long stages. Use it after Monitoring Hub/Capacity Metrics to pinpoint the problematic job.

Key tabs to inspect

- Stages: uneven task durations (data skew), heavy shuffle read/write, large input/output volumes.

- Executors: storage memory, task time (GC), shuffle metrics. High GC or frequent spills indicate memory tuning is needed.

- Storage: which RDDs/cached tables occupy memory; any disk spill.

- Jobs: long‑running jobs and gaps in the timeline (driver compilation, non‑Spark code, driver overload).

What to look for

Set via environment Spark properties or session config.

- Data skew, Memory usage, High/Low Shuffles: Adjust Apache Spark settings: i.e. spark.ms.autotune.enabled, spark.task.cpus and spark.sql.shuffle.partitions.

Remediation and Optimization Suggestions

A) Cluster & Workspace Settings

Runtime & Native Execution Engine (NEE)

- Use Fabric Runtime 1.3 (Spark 3.5, Delta 3.2) and enable the Native Execution Engine to boost performance; enable at the environment level under Spark compute → Acceleration.

Starter Pools vs. Custom Pools

- Starter Pool: prehydrated, medium‑size pools; fast session starts, good for dev/quick runs.

- Custom Pools: size nodes, enable autoscale, dynamic executors. Create via workspace Spark Settings (requires capacity admin to enable workspace customization).

High Concurrency Session Sharing

- Enable High Concurrency to share Spark Sessions across notebooks (and pipelines) to reduce session startup latency and cost; use session tags in pipelines to group notebooks.

Autotune for Spark

Enable Autotune (spark.ms.autotune.enabled = true) to auto‑adjust per‑query:

- spark.sql.shuffle.partitions

- Spark.sql.autoBroadcastJoinThreshold

- spark.sql.files.maxPartitionBytes.

Autotune is disabled by default and is in preview; enable per environment or session.

B) Data‑level best practices

Microsoft Fabric offers several approaches to maintain optimal file sizes in Delta tables, review documentation here: Table Compaction - Microsoft Fabric.

Intelligent Cache

- Enabled by default (Runtime 1.1/1.2) for Spark pools: caches frequently read files at node level for Delta/Parquet/CSV; improves subsequent read performance and TCO.

OPTIMIZE & Z‑Order

- Run OPTIMIZE regularly to rewrite files and improve file layout.

V‑Order

- V‑Order (disabled by default in new workspaces) can accelerate reads for read‑heavy workloads; enable via spark.sql.parquet.vorder.default = true.

Vacuum

- Run VACUUM to remove unreferenced files (stale data); default retention is 7 days; align retention across OneLake to control storage costs and maintain time travel.

Collaboration & Next Steps

Engage Data Engineering Team to Define an Optimization Playbook

- Start with reviewing capacity sizing guidance, cluster‑level optimizations (runtime/NEE, pools, concurrency, Autotune) and then target data improvements (Z‑order, compaction, caching, query refactors).

- Triage: Monitor Hub → Capacity Metrics → Spark UI to map workloads and identify high‑impact jobs, and workloads causing throttling.

- Schedule: Operationalize maintenance: OPTIMIZE (full or selective) during off‑peak windows; enable Auto Compaction for micro‑batch/streaming writes; add VACUUM to your cadence with agreed retention.
- Add regular code review sessions to ensure consistent performance patterns.

- Fix: Adjust pool sizing or concurrency; enable Autotune; tune shuffle partitions; refactor problematic queries; re‑run compaction.

- Verify: Re‑run the job and change, i.e. reduced run time, lower shuffle, improved utilization.

Analytics on Azure Blog articles

Building AI apps and agents with Azure Databricks, Copilot Studio, and GitHub Copilot

A workspace-wide Genie MCP endpoint for Copilot Studio

Lakebase branching with GitHub Copilot agent mode

What this unlocks for AI agent teams

Get started

Secure Medallion Architecture Pattern on Azure Databricks (Part II)

Separate the platform from the workload

What are skills, and why are they a good fit?

The GitHub repo

1. Create a new GitHub repository and clone it locally

2. Create the GitHub skill folder structure (first iteration)

3. Add the main skill definition

4. Add a script to fetch and normalize the blog article

5. The output and output contract

6. The README.md and TODO.md templates

7. The output of the fetch_blog.py file and the interaction with the orchestrator

8. How this actually came together

9. From a single skill to a skill graph

10. The inner loop: rerun, fail, fix the skill

11. Restricted-tenant compatibility

12. The bugs that became rules

13. Splitting "the skill" from "this repo's defaults"

14. The Validations

15. Key Vault soft-delete state machine

16. What I've learned so far

Resilient by Design: Azure Databricks Disaster Recovery Strategy

Introduction: From Recovery Plans to Resilience Strategy

Why Disaster Recovery for Azure Databricks is Different

Understanding the Fundamentals: RTO, RPO, and DR Trade-offs

Understanding the Fundamentals: RTO, RPO, and DR Trade-offs

Designing for Resilience: A Phased Disaster Recovery Approach

Phase 1: Discovery & Assessment

Outcome

Phase 2: Strategy & Design

Phase 3: Disaster Recovery Implementation & Enablement

Unity Catalog-Driven Replication

Operationalizing with a DR Pipeline

Outcome

Phase 4: DR Drill: Validation, Operations & Continuous Improvement

Failover & Failback in Practice

Outcome

Key Takeaways and Closing Thoughts

From Chaos to Clarity: Your Databricks Workspace on a Single Pane of Glass

The question that never stays answered — until now

What the Discovery Phase delivers?

Design Overview

Why users love the view — visualization that earns its keep

Sample Item Catalog

Closing thoughts

From Manual Backfills to Autonomous Pipelines: Building an LLM-Powered Backfill Agent on Azure

Introduction:

Architecture Overview

Logic Apps Trigger

Azure AI Foundry Agent (Decision Layer)

Azure Function App – MCP Server (Execution Layer)

Synapse Pipelines (Processing Layer)

Azure Data Explorer(State and Observability Layer)

End-to-End Flow

Analytics Layer:

Steps:

Step by Step Guide to Ontology and Plan for Financial Service

What We Will Build

Step 1: Preparing the Data Foundation in Fabric Lakehouse

Creating the Lakehouse

Data Sources and Tables

Step 2: Creating the Semantic Model

Generating the Model from Lakehouse

Configuring Relationships and Properties

Step 3: Generating the Ontology

What the Ontology Does

Generating from the Semantic Model

Enriching the Ontology with Rules

Querying with Natural Language

Step 4: Setting Up Planning Sheets

Creating a Planning Sheet

Using Planning for Supervisory Resource Allocation

Microsoft Fabric Operations Agent Step by Step Walkthrough

Fabric Capacity and Workspace

Enabling the Operations Agent in the Admin Portal

6. The `README.md` and `TODO.md` templates

7. The output of the `fetch_blog.py` file and the interaction with the orchestrator