We have been working getting an enterprise grade event driven orchestration of our ETL system to operate like an airport control tower, managing a fleet of flights (data processes) as they progress through various stages of take-off, transit, and landing. All of this, because Microsoft Fabric has a core-based limit to the number of Notebook executions that a capacity can execute and have queued up in line for execution when invoking them using the REST API. Read the details here: limits (you know, it’s funny that there is no stated limits for Azure Service Bus Queues on number of messages in queue, but there is for Microsoft Fabric, which uses a Service Bus queue underneath…)

Each flight (file ingestion) is linked to a flight plan (orchestration) that ensures that flights follow predefined routes, encounter minimal delays, and arrive at their destination efficiently. Sometimes we have to wait for a combination of flights to arrive, before we can venture on to the next leg of the complete flight (multiple interdependent files landed in bronze before we process silver).

Each Flight Plan represents a predefined sequence of Notebook executions in Microsoft Fabric, moving data through:

1. Bronze (Raw Data Ingestion)
2. Silver (Data Transformation & Cleansing)
3. Gold (Final High-Value Data)

Flight plans are determined using a combination of customer and some characteristics of the data.

A Finite State Machine (FSM) tracks each flight’s progress in states like Pending, Queued, Running, WaitingForDependency, and Succeeded, ensuring smooth execution and real-time tracking.

Receiving thousands of files, we are initiating a great number of Notebook executions this way using the REST API, we can easily hit the limits of Microsoft Fabric, a seat manager attends to the number of seats currently booked and will direct flights into a holding pattern if no free capacity is available. Flights enter WaitingForDependency status (runway congestion) and when a Notebook completes execution, a Notebook/Stop event is triggered, allowing the next waiting flight to take off.

A Fabric Database persists all flight plans, execution history, and dependencies, ensuring auditability, recovery, and coordination across multiple data loads. To handle all of the messaging we have chosen Azure Event Grid in combination with Azure Service Bus Queues. At the end of each queue there is an Azure Function App to process messages as they arrive.

---

✈ The Tower Control (Orchestration Engine)

At the heart of this system is the Tower Control, which manages and directs all incoming and outgoing flights (ETL jobs).

• It receives flight requests (messages) from the execute-queue (Azure Service Bus), ensuring that each flight follows an authorized flight plan before take-off.
• The Tower Control doesn’t make decisions alone—it relies on the Planner to chart out the appropriate flight paths and manage scheduling.
• The entire system ensures efficiency and scalability, preventing airport congestion (resource overutilization).

---

📅 The Planner (Orchestration Logic & SQL Database)

Before a flight can take off, it must file a flight plan—a predefined orchestration of file loads.

1. Flight Plan Activation:
   • When a classifier/success event arrives in the execute-queue, it first checks if a flight plan exists in the SQL Server database for the given customer-provider pair.
   • If no active flight plan exists, a new one is registered and stored in the SQL database.
   • Each flight plan persists in the database, allowing the system to track ongoing operations, monitor execution history, and manage dependencies.
   • Flight plans are defined as templates in the meta schema, and the runtime version contains runtime information in addition to steps, workspace- and notebook-references as well as other required information to ensure the safe passage of the files.
2. Flight Check-ins:
   • Any subsequent flights (events) for the same customer-provider combination check into the active flight plan instead of creating a new one.
   • This ensures coordination between related flights, preventing redundant take-offs and reducing airspace (resource) congestion.
   • This also ensures interdependency can be enforced, so that a crash can or cannot trigger a later flight, depending on the configuration for that flight plan.
3. Stored Data & Execution Logic:
   • The SQL Server database keeps track of flight plans, execution states, dependencies, and processing history.
   • It enables seamless recovery, auditing, and tracking of past, present, and upcoming flights.

---

🚦 The Flight Status System (Finite State Machine)

Every flight is tracked by a Finite State Machine (FSM) monitoring its progress in real time. The SQL database stores and updates these statuses.

• ✈ Pending – The flight request has been submitted but is waiting for the plan to commence.
• ✈ Queued – The flight is cleared for take-off and waiting in the queue.
• ✈ WaitingForDependency – The flight is delayed due to a lack of available runway space (Microsoft Fabric capacity maxed out).
• ✈ Running – The flight is airborne and actively processing data.
• ✈ Succeeded – The flight has landed safely, completing the data pipeline stage.

This FSM ensures systematic execution, prevents bottlenecks, and allows intelligent scheduling of flights.

---

🚀 Flight Execution (Notebook Orchestration in Microsoft Fabric)

Once a flight is cleared for take-off, it follows a prescribed route (Notebook executions in Microsoft Fabric):

1. Bronze Notebook: ✈ The flight transports raw, unprocessed data into the Bronze layer.
2. Silver Notebook: ✈ Data undergoes refinement, transformations, and cleansing.
3. Gold Notebook: ✈ The final high-value data destination is reached, ensuring premium quality data is ready for analytics and reporting.

Each flight plan contains pre-determined orchestrations of file loads, meaning the system knows in advance which Notebooks need to be executed and in what sequence.

---

🛫 Managing Runway Congestion (Handling Resource Constraints in Microsoft Fabric)

Just like a busy airport can only handle a limited number of take-offs at a time, Microsoft Fabric has capacity constraints when executing Notebooks.

• If the Fabric capacity is maxed out, new flights cannot take off immediately and are moved to “WaitingForDependency” status.
• As soon as a Notebook completes execution, it sends a Notebook/Stop event, notifying the Tower Control that a slot has freed up.
• This allows one waiting flight to be cleared for take-off, ensuring fair scheduling and optimal use of available resources.

---

🛬 Landing Safely (Successful Data Pipeline Execution)

• Once all required Notebooks are executed successfully, the flight reaches its final destination.
• The flight’s status is updated to “Succeeded” in the SQL Server database for logging, tracking, and reporting.
• The system is now ready to accept new incoming flights and restart the process.
• For every advancement from Bronze -> Silver -> Gold there will be fired a progression event for the Tower Control  to pick up.

---

✈ Summary of the System’s Aviation Workflow

Aviation Concept	ETL System Equivalent	Purpose
Tower Control	Azure Function Apps	Manages flight take-off, scheduling, and execution
Flight Plan	SQL Server Database	Stores orchestrations and execution history
Planner	Orchestration Logic	Determines optimal execution pathways
Flight Status (FSM)	State Machine	Tracks execution progress
Flight Take-off	Notebook Execution	Begins processing raw data
Flight Delay	WaitingForDependency	Notebook execution paused due to capacity constraints
Flight Stop Event	Notebook Completion Signal	Frees up capacity for the next waiting flight
Successful Landing	Data Processing Completed	Pipeline execution finished successfully

The aviation theme has helped us create better mental images and relevant discussions during development.

Long story, short (2 days later)

While implementing an Azure Function that is designed to fetch secrets from Azure KeyVault, I ran into a funny and odd issue. I am not able to explain why and what is going on, but I have tried every trick a google search can conjure, at least until page 30 in the search results. It was by coincidence I came across some of the parameters in the DefaultAzureCredentialOptions class that got me going, at least locally.

The idea, as far as I have understood, is that whenever you invoke the Azure.Identity.DefaultAureCredential class, it provides a flow for attempting authentication using one of the following credentials, in listed order:

• EnvironmentCredential
• WorkloadIdentityCredential
• ManagedIdentityCredential
• AzureDeveloperCliCredential
• SharedTokenCacheCredential
• VisualStudioCredential
• VisualStudioCodeCredential
• AzureCliCredential
• AzurePowerShellCredential
• InteractiveBrowserCredential

I suspect that since I have deployed my Azure Function using the Managed Identity setting to a Systems Assigned identity, like this:

AND the fact that ManagedIdentityCredential is before VisualStudioCredential in the authentication flow, it fails, since it is unable to authenticate the managed identity – which is the main principle of the design – none other than the service can assume the identity of the service.

See more detail here: https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview
Snip

• System-assigned. Some Azure resources, such as virtual machines allow you to enable a managed identity directly on the resource. When you enable a system-assigned managed identity:
  • A service principal of a special type is created in Azure AD for the identity. The service principal is tied to the lifecycle of that Azure resource. When the Azure resource is deleted, Azure automatically deletes the service principal for you.
  • By design, only that Azure resource can use this identity to request tokens from Azure AD.
  • You authorize the managed identity to have access to one or more services.
  • The name of the system-assigned service principal is always the same as the name of the Azure resource it is created for. For a deployment slot, the name of its system-assigned identity is <app-name>/slots/<slot-name>.

Love rears it’s ugly head

Having assigned the proper permissions in the Azure KeyVault, you are able to connect using your credentials in Visual Studio to said KeyVault. A code example of that could look like this:

public static string GetSecret( string keyvault, string secret )
{            
   var kvUri = $"https://{keyvault}.vault.azure.net";
 
   var creds = new DefaultAzureCredential();
 
   var client = new SecretClient(new Uri(kvUri), creds);
   var secret = client.GetSecretAsync(secret).Result.Value.Value;
 
   return secret;
}

(link to NuGet: NuGet Gallery | Azure.Security.KeyVault.Secrets 4.5.0)

Usually this works, and I have no other explanation than having deployed the solution to a live running App Service is what breaks this otherwise elegant piece of code. The above listed code does not work for me.

Workaround

You can instantiate the DefaultAzureCredential class using a constructor that takes a DefaultAzureCredentialOptions object as a parameter and this object has a great number of attributes that are of interest. You can actively remove items in the authentication flow and you can specify the tenant id, if you have access to multiple tenants.

The code that resolved the issue locally looks something like this. (I can probably just do without the ManagedIdentity, will test)

public static string GetSecret( string keyvault, string secret )
{            
   var kvUri = $"https://{keyvault}.vault.azure.net";
 
 
    var creds = new DefaultAzureCredential(
        new DefaultAzureCredentialOptions() {
        TenantId = "<INSERT TENANT ID HERE>"
        , ExcludeAzureCliCredential = true
        , ExcludeAzurePowerShellCredential = true
        , ExcludeSharedTokenCacheCredential = true
        , ExcludeVisualStudioCodeCredential = true
        , ExcludeEnvironmentCredential = true
        , ExcludeManagedIdentityCredential = true
    });
 
 
    var client = new SecretClient(new Uri(kvUri), creds);
   var secret = client.GetSecretAsync(secret).Result.Value.Value;
 
   return secret;
}

I am not sure this will work when I deploy the solution, but I will probably create a test on environment (local debug or running prod)

HTH