Microsoft Azure Services and Architecture Questions

Direct answer

For 10 TB/day with a 6-hour SLA, Azure Batch with Spot VMs is the strongest default if the workload can checkpoint and tolerate node preemption (lowest cost, most operational overhead to build the checkpointing yourself); Databricks with a mostly-Spot job cluster is the strongest default if the workload is genuinely Spark-shaped and the team wants Delta Lake's transactional guarantees (Delta Lake is a transactional table format built on top of a data lake) with less custom infrastructure to build; AKS (Azure Kubernetes Service) with scale-to-zero fits best when this batch job is one of several workload types already living on a shared Kubernetes platform and the team wants one operational model instead of a dedicated batch service; serverless orchestration (Azure Functions/Durable Functions or Logic Apps fanning out to one of the above compute options) is the wrong primary compute layer for 10 TB of data processing itself, but is a strong choice for the orchestration and glue around whichever compute layer does the heavy lifting.

Structured elaboration

Sizing the requirement first. 10 TB/day inside a 6-hour processing window means an average sustained throughput of 10 x 10^12 bytes / (6 x 3600 seconds) = 10,000,000,000,000 / 21,600 ≈ 463,000,000 bytes/second ≈ 463 MB/s ≈ 3.7 Gbps sustained, before accounting for any headroom for slow starts, retries, or uneven daily volume (a real pipeline should size for its P95 daily volume within the SLA window, not its average, since an SLA measured against the average day is not really a 6-hour SLA on the days that matter). This number matters because it rules out anything that can only reach a fraction of that sustained rate regardless of how the compute layer is chosen.

Azure Batch with Spot VMs. Best throughput-per-dollar of the four options for embarrassingly parallel or checkpoint-friendly workloads (partition the 10 TB into independent chunks, process each on a pool of Spot VMs). Checkpointing is entirely the team's responsibility: each work item must be small enough, and progress tracked durably enough (a completion marker per chunk in Blob Storage or a database row), that a preempted node's in-flight chunk can be picked up by another node without reprocessing the whole thing or double-counting a partial result. Handling preemption means subscribing to Batch's node preemption notification and requeuing the affected tasks; Batch does this requeueing for you at the task level if the pool is configured correctly, but the task itself must be idempotent (safe to run again) for that requeue to be safe. Operational overhead is the highest of the four here: no managed job scheduler UI beyond Batch's own, no built-in data-lineage or table format, so the team is building more of the plumbing.

AKS with scale-to-zero node pools. Good fit if the team already runs other workloads on this AKS cluster and wants one platform rather than a separate batch-specific service. A user node pool's cluster autoscaler minimum can be set to 0 (system node pools cannot go to zero, since the control plane (the managed Kubernetes components that run the cluster itself) has its own critical pods that need somewhere to run), so the pool costs nothing when idle and scales up only when the daily job starts, using KEDA (Kubernetes Event-Driven Autoscaling, a component that scales workloads based on external event metrics) or a scheduled CronJob to trigger the scale-up. Spot node pools are supported for AKS user node pools too, combining scale-to-zero with Spot pricing. Preemption handling here is Kubernetes-native (a SIGTERM and a grace period before eviction, and a Pod Disruption Budget to control how many can go at once), which a team already fluent in Kubernetes will find more familiar than Batch's task-preemption model, at the cost of needing genuine Kubernetes operational maturity (node pool sizing, PDBs, resource requests/limits tuned correctly) to avoid the scheduler thrashing under a sudden 10 TB/day burst.

Databricks with Spot workers. Best fit when the transformation logic is naturally expressed in Spark and the team wants Delta Lake's ACID transactions and schema enforcement instead of hand-rolling idempotency and checkpoint tracking. Databricks Job Clusters support a mix of on-demand and Spot workers (a small on-demand "driver-adjacent" core plus a larger Spot-heavy worker pool), and Spark's own task-level retry and lineage tracking absorbs a fair amount of the preemption-handling burden that Batch or raw AKS would otherwise put entirely on the team. Operational overhead is the lowest of the compute-heavy three here for a Spark-shaped workload, at a real dollar premium over raw Batch/AKS for the same underlying VM cost (Databricks' own per-DBU charge (DBU = Databricks Unit, Databricks' own compute-billing unit) sits on top of the VM cost), which needs to be weighed against the engineering time saved.

Serverless orchestration. Azure Functions/Durable Functions have per-invocation duration and payload-size limits that make them a poor fit for moving or transforming 10 TB directly; use them instead as the layer that kicks off the batch job on a schedule, fans out work items with retry semantics, and orchestrates dependent steps (validate inputs, trigger the Batch/AKS/Databricks job, wait for completion, trigger the next stage), while the actual data movement and transformation happens in one of the three compute options above.

Worked example: throughput and checkpoint granularity

Given the ≈463 MB/s sustained requirement, if the pipeline partitions the day's 10 TB into fixed-size chunks of, say, 2 GB each, that is 10,000 GB / 2 GB = 5,000 chunks to process within the 6-hour window. At an assumed per-node sustained throughput of roughly 100 MB/s per worker (a conservative, illustrative figure for a general-purpose VM doing real transformation work, not just a network copy), reaching the required 463 MB/s aggregate needs on the order of 5 concurrent workers running for the full window in the ideal case, but Spot preemption and uneven chunk processing time mean the pool should be sized with real headroom (commonly 30-50% above the theoretical minimum) so a wave of simultaneous preemptions does not, by itself, blow the 6-hour SLA. This is exactly the kind of number a design answer should compute rather than assert: it turns "add enough Spot VMs" into a concrete target the on-call team can check the pool against during an actual run.

Trade-offs and pitfalls

The single biggest risk across all Spot-based options is correlated preemption: if the pipeline's compute is concentrated in one VM size/region/zone, a regional capacity crunch can evict a large fraction of the pool simultaneously, which is very different from losing one node at a time; diversify across a few VM sizes/families in the pool (Batch and AKS both support this) so a capacity shortage in one SKU does not take down the whole job at once. A second common pitfall is treating "6-hour SLA" as license to start the job right at the edge of a 6-hour window every night with no margin; build in a genuine safety margin (start with enough time that even a bad-but-plausible night, with above-average preemption or above-average data volume, still finishes inside the SLA) rather than a plan that only works on an average day.

Direct answer

Azure SQL Database (single database or elastic pool) is the right default for a new application that doesn't need instance-level SQL Server features: Microsoft owns patching, backups, and most high availability (HA) entirely. Azure SQL Managed Instance (MI) is the migration-friendly middle ground: near-complete SQL Server surface-area compatibility (SQL Server Agent, Common Language Runtime/CLR, cross-database queries and transactions, linked servers, Service Broker) inside your own Virtual Network (VNet), still as a managed service. SQL Server on an Azure virtual machine (VM) is the last resort for workloads that need something even MI doesn't support, or need an exact SQL Server version, edition, or operating-system-level control pinned, at the cost of owning patching and HA yourself.

Comparison

Evaluation checklist

When choosing among the three for a given workload, walk through, in order: (1) which SQL Server compatibility features the application actually calls, since that alone can rule out SQL Database; (2) the team's appetite for owning patching and HA operations; (3) the target Recovery Time Objective (RTO, how long the application can stay down before it must be back up) and Recovery Point Objective (RPO, how much recent data you can afford to lose); (4) the budget model, Database Transaction Unit (DTU) or virtual core (vCore) consumption pricing versus a VM plus a SQL Server license.

Worked example

A legacy on-premises application uses SQL Server Agent jobs, several cross-database transactions, and one linked server to a partner system. Azure SQL Database supports none of that by default (cross-database access there is limited to a workaround called elastic query, not true cross-database transactions), so a rewrite would be required to land it there. SQL Managed Instance supports SQL Agent, cross-database transactions, and linked servers directly, letting the migration proceed largely unchanged; the real migration effort shifts to the VNet integration itself, MI requires a dedicated subnet, correctly sized and with no other resource types placed in it, plus Domain Name System (DNS) and network security group planning before cutover. If the application also depended on something MI still doesn't support (an unusual Component Object Model/COM object hosted in-process, for example), SQL Server on a VM would be the fallback, accepting full patching and HA ownership in exchange for full compatibility.

Trade-offs and pitfalls

• Picking SQL Database first because it's the simplest to provision, without checking for SQL Agent or linked-server dependencies, is a common way to discover a rewrite is needed mid-migration.
• MI's VNet requirement is not optional and has real subnet-sizing and DNS-planning work attached; underestimating that work is the most common MI migration surprise.
• Running SQL Server on a VM to dodge MI's constraints reintroduces the exact patching and HA burden the other two options were built to remove; only choose it when a specific, named feature gap forces it.

Direct answer

Diagnose before tuning: use Query Store and Query Performance Insight to find which specific queries regressed and when, check wait statistics to confirm what resource the database is actually waiting on, then apply the fix that matches the diagnosis (an index, a parameter-sniffing mitigation, partitioning, or In-Memory OLTP, OLTP meaning Online Transaction Processing) rather than reaching for the first plausible-sounding technique. Only after the workload has been tuned as far as a single database reasonably goes should horizontal scaling or Hyperscale (an Azure SQL service tier that separates compute, storage, and log so you can scale each independently and add read replicas) enter the conversation.

Diagnostic steps, in order

1. Query Store and Query Performance Insight: Query Store retains historical execution plans and runtime statistics per query, and Query Performance Insight (built on top of it in the Azure portal) surfaces the top resource-consuming and longest-running queries over the peak window. Start here to identify which queries regressed, not just that "the database is slow."
2. Wait statistics (sys.dm_os_wait_stats and Query Store's per-query wait breakdown): confirm what the engine is actually blocked on, lock contention, disk I/O, CPU, or memory grants, before assuming the fix is an index. Tuning an index when the real wait is lock contention wastes effort and doesn't move the needle.
3. Missing-index analysis (sys.dm_db_missing_index_details, _groups, _group_stats): these dynamic management views (DMVs) surface indexes the optimizer would have used had they existed, ranked by estimated impact. Cross-check candidates against Query Store's top offenders before creating anything, since the missing-index DMVs can suggest indexes that overlap with existing ones or that only help a rare query.
4. Parameter sniffing check: if Query Store shows the same query plan-cached statement performing very differently across executions, and wait stats don't point at a hardware bottleneck, parameter sniffing is the likely cause: the optimizer cached a plan built for one parameter value's data distribution and it performs badly for a different value. Mitigations, cheapest first: OPTION (RECOMPILE) on the specific statement if it's infrequent enough to tolerate replanning cost each time; Query Store forced plans to pin a known-good plan; OPTIMIZE FOR UNKNOWN to plan for an average case; and, on database compatibility level 160, Parameter Sensitive Plan (PSP) optimization, an intelligent query-processing feature that automatically maintains multiple cached plans for one parameterized statement when the data distribution is genuinely non-uniform, which is the current, lowest-effort fix when available.
5. Partitioning: for a large, date-ordered table (order history, event logs), range-partitioning by date lets old partitions be maintained (index rebuilds, archiving) independently of the hot, recent partition, and lets the optimizer skip irrelevant partitions entirely for date-scoped queries. This addresses maintenance-window and query-scan cost, not raw peak-hour throughput.
6. In-Memory OLTP: memory-optimized tables and natively compiled stored procedures remove locking and latching almost entirely for the tables converted, which is the right tool specifically when wait statistics point at lock or latch contention on a small number of very hot tables, not a general-purpose speed-up for the whole database.
7. Horizontal scaling or Hyperscale: once single-database tuning is exhausted, add a free built-in read-scale-out replica to offload reporting-style reads from the primary, or move to the Hyperscale service tier, which separates compute, storage, and log so you can add up to 30 independently-scaled named read replicas and scale compute up or down without a size-of-data operation, precisely for workloads that have outgrown vertical tuning on a single compute node.

Worked example

Query Performance Insight shows one specific stored procedure spiking in duration only during the 9am login rush. Query Store's plan history shows two different execution plans for that procedure over the past week, with the slow plan doing a table scan and the fast plan doing an index seek, a signature of parameter sniffing rather than a missing index (the index exists; the wrong plan just isn't using it well for some parameter values). Forcing the known-good plan via Query Store gives an immediate fix; migrating the database to compatibility level 160 to get Parameter Sensitive Plan optimization is the durable fix, since it lets the engine keep multiple plans instead of relying on one to fit every login pattern.

Trade-offs and pitfalls

• Creating an index suggested by the missing-index DMVs without checking Query Store's actual top offenders can add write overhead (every index has an insert/update cost) for a query that wasn't actually the peak-hour bottleneck.
• OPTION (RECOMPILE) fixes parameter sniffing but adds CPU cost to compile a fresh plan on every execution; it's a reasonable stopgap for a low-frequency statement, a poor permanent fix for a hot one.
• Partitioning helps scan cost and maintenance windows, but it does not, by itself, relieve lock contention on hot rows within the current partition; don't reach for it as a fix for contention symptoms.
• Jumping straight to Hyperscale or horizontal scaling before ruling out an index, plan, or contention problem trades a real fix for a bigger, more expensive database that still has the same underlying issue.

Direct answer

Chain the three stages (validate, transform, notify) as independent Azure Functions, each triggered off its own Service Bus queue rather than directly off each other, with Event Grid only as the entry trigger reacting to the blob upload; this gives each stage its own retry policy, its own dead-letter queue (DLQ), and its own ability to scale independently, and makes replay straightforward because a failed or reprocessed message can be resubmitted to exactly the stage that needs to redo its work rather than restarting the whole pipeline from the file upload.

flowchart LR
  Upload[Blob upload] --> EG[Event Grid: BlobCreated]
  EG --> SBQ[Service Bus queue: validate]
  SBQ --> FnValidate[Function: validate]
  FnValidate -->|valid| SBQ2[Service Bus queue: transform]
  FnValidate -->|invalid| DLQ1[(DLQ: validate)]
  SBQ2 --> FnTransform[Function: transform]
  FnTransform -->|success| SBQ3[Service Bus queue: notify]
  FnTransform -->|failure, retryable| SBQ2
  FnTransform -->|failure, exhausted| DLQ2[(DLQ: transform)]
  SBQ3 --> FnNotify[Function: notify]
  FnNotify --> DLQ3[(DLQ: notify)]

Structured elaboration

Why Event Grid at the front and Service Bus in the middle, not one technology throughout. Event Grid is the natural fit for "react to a blob being created," a discrete platform-level state-change notification it is purpose-built to deliver with very low latency and no polling. Once inside the pipeline, Service Bus queues carry the message between the three processing stages because Service Bus, unlike Event Grid, gives each stage its own durable queue with built-in retry counts, a dead-letter queue, and (if genuinely needed) message sessions, which are exactly the primitives idempotency, poison-message handling, and elasticity below depend on. Using Event Grid to chain the internal stages together instead (each stage publishing an event the next stage subscribes to) is a workable alternative but loses the built-in per-message retry/DLQ bookkeeping Service Bus gives for free, pushing that bookkeeping into custom code.

At-least-once semantics with idempotent processing. Service Bus's default delivery mode (Peek-Lock) guarantees at-least-once: a Function locks a message, processes it, and completes (deletes) it only after processing succeeds; if the Function crashes or times out mid-processing, the lock expires and the message becomes available again for another attempt, meaning the same message can be processed more than once. Each stage must therefore be idempotent: the validate stage's output (a "validated" marker, or the validated file's normalized path) should be written keyed by the file's own identity (its blob path plus an ETag or a content hash), so re-running validation on the same file produces the same result and does not, for example, double-enqueue a transform message for a file that was already fully validated in a prior, crashed attempt. The transform stage's write to its output store should be an upsert keyed by the file identity, not a blind append, for the same reason.

Poison message handling. Service Bus queues support a configurable MaxDeliveryCount; once a message has been delivered and abandoned (or has expired its lock) that many times without being completed, Service Bus automatically dead-letters it, moving it to the queue's built-in DLQ subqueue rather than retrying it forever. Each stage's DLQ should have its own alert and a defined triage process (a malformed file that will never pass validation needs a human or an automated quarantine action, not infinite silent retries consuming worker capacity). Setting MaxDeliveryCount too low risks dead-lettering a message that would have succeeded on a transient retry (a brief downstream dependency blip); setting it too high delays detecting a genuinely poison message and wastes processing capacity retrying something that will never succeed, so this value should be tuned per stage based on the realistic transient-failure rate for that stage's dependencies.

Replayability. Because the raw uploaded blob is never deleted by this pipeline (only referenced by path in the messages moving between stages), and each stage's DLQ retains the original message payload, replay at any granularity is possible: resubmitting a DLQ'd message re-attempts just that one stage for just that one file, and re-publishing a synthetic BlobCreated-shaped message replays the entire pipeline from the top for a file that needs full reprocessing (for example, after a bug fix in the transform logic that affected every file processed in a bad time window). This is a direct benefit of keeping each stage's contract as "given this file's identity, do your stage's work," rather than passing large payloads through the messages themselves, which would make replay require reconstructing state the message no longer carries.

Elasticity for bursty workloads without losing events. Azure Functions on a Service Bus trigger scale out automatically based on queue depth (more competing consumers spin up as the queue backlog grows, up to configured limits), and because the file itself sits durably in Blob Storage and the message sits durably in a Service Bus queue with a long-enough lock duration and message TTL, a burst of 10,000 simultaneous uploads does not lose any of them even if processing temporarily falls behind: the backlog simply grows in the queue (visible as a queue-depth metric worth alerting on) and drains as scaled-out consumers catch up, rather than the excess uploads being dropped or rejected at the front door.

Worked example: tracing one file through a transient failure

A 50 MB file uploads; Event Grid fires a BlobCreated event within seconds and a message lands on the validate queue. The validate Function picks it up, confirms the file's format and size are acceptable, and enqueues a message on the transform queue, then completes (deletes) the original validate message. The transform Function picks up the transform message but the downstream service it calls to enrich the data times out; the Function throws, the message's lock expires after the configured lock duration, and Service Bus makes the message visible again for redelivery, now with DeliveryCount = 2. On retry, the transform Function calls the same downstream service, which has recovered, succeeds, and upserts its result keyed by the file's blob path (so if this had instead been a false failure where the enrichment actually completed before the timeout was reported, the retry's upsert simply overwrites with the same result rather than creating a duplicate record), then enqueues the notify message. If the downstream service had instead stayed down through MaxDeliveryCount attempts (say, 5), the message dead-letters to the transform stage's DLQ, an alert fires on that DLQ's depth, and an on-call engineer can inspect the dead-lettered message, fix or wait out the downstream outage, and resubmit it, at which point processing resumes from exactly the transform stage rather than needing the file re-uploaded.

Trade-offs and pitfalls

The main cost of this three-queue design over a single Function doing all three steps inline is operational surface area: three queues, three DLQs, and three sets of scaling/retry settings to reason about instead of one. That cost buys real resilience properties (independent retry tuning per stage, independent scaling per stage, and stage-level replay) that a single monolithic Function cannot offer, since a monolithic Function that fails partway through transform has no clean way to resume only from transform on retry, it restarts the entire validate-transform-notify sequence, doing already-completed work again and multiplying load on the validate stage's dependencies for no benefit. The most common correctness pitfall is forgetting that Peek-Lock's at-least-once guarantee means idempotency is not optional for any stage with a side effect (a database write, an external API call with side effects, sending an actual notification); a notify stage that is not idempotent will, eventually, send a duplicate notification to a real user during some transient failure, which is a customer-visible bug even though the underlying messaging infrastructure behaved exactly as designed.

Direct answer

A worthwhile correction to the premise first: on a Consumption-plan Function, the token is not actually fetched from the raw platform Instance Metadata Service (IMDS, 169.254.169.254) the way an IaaS VM or VM Scale Set would; App Service and Functions expose their own local token endpoint (the IDENTITY_ENDPOINT/IDENTITY_HEADER environment variables, sometimes called the "App Service MSI extension"), which is a different mechanism serving the same purpose. Knowing that distinction is itself a senior-level signal, since the two failure modes (a VNet [Virtual Network, Azure's private network boundary] blocking outbound to IMDS's link-local address versus a missing App Service environment variable) are different problems with different fixes.

The actual flow

1. Environment variables, set automatically when the identity is enabled: IDENTITY_ENDPOINT (a URL like http://127.0.0.1:<port>/msi/token, local to the sandbox the Function runs in) and IDENTITY_HEADER (a rotating secret value used to authenticate the local call and mitigate server-side request forgery against that local endpoint).
2. Token request: the Azure Identity SDK (or a raw HTTP client) makes a GET to IDENTITY_ENDPOINT with the resource URI to get a token for (https://vault.azure.net for Key Vault), the api-version query parameter (2019-08-01), and the X-IDENTITY-HEADER header set to the value of IDENTITY_HEADER.
3. What answers that request: the App Service/Functions host's local identity service (not Key Vault, not Entra ID directly) which itself holds a certificate or exchanges with Entra ID (Microsoft's cloud identity service, formerly Azure AD) on the platform's behalf, and returns a bearer token scoped to the requested resource, along with an expires_on timestamp.
4. Using the token: the caller (typically the Azure Identity SDK's SecretClient, not hand-rolled code) attaches the token as a Bearer header on the actual call to Key Vault's REST API to read the secret.
5. Authorization check at Key Vault: Key Vault checks whether the token's principal (the Function's managed identity's object ID) has been granted an RBAC (Role-Based Access Control) role (or, on the older access-policy model, an access policy) allowing get/list on secrets. This is a separate check from "the token was issued successfully"; a perfectly valid token for a principal with no grant on this vault gets a 403, not a 401.

sequenceDiagram
    participant F as Function code
    participant L as Local identity endpoint (IDENTITY_ENDPOINT)
    participant KV as Key Vault
    F->>L: GET ?resource=vault.azure.net&api-version=2019-08-01, X-IDENTITY-HEADER
    L-->>F: access_token, expires_on
    F->>KV: GET secret, Authorization: Bearer <token>
    KV-->>F: secret value (if RBAC grants get/list)

Token caching and refresh

The Azure Identity SDK's credential objects cache the token in memory for the life of the process and refresh proactively, typically requesting a new one a few minutes before the cached one expires, rather than on every single call. Two consequences worth knowing: first, a Consumption-plan Function that goes cold and gets a fresh instance also gets a fresh in-memory cache, so cold starts always pay the token-acquisition round trip; second, if an admin changes the managed identity's role assignment (grants or revokes access), the token the app already holds is still valid until it expires, since revocation doesn't invalidate already-issued tokens, and the platform's group/role membership cache for managed identities can itself take up to roughly 24 hours to reflect the change even for a brand-new token request in some cases, so "I just added the role assignment and it still fails" for a short window afterward is expected behavior, not a bug.

Common failure modes

• Missing or absent IDENTITY_ENDPOINT/IDENTITY_HEADER: happens when running the same code locally (outside Azure) without local.settings.json providing an alternative credential path, or when the managed identity was never actually enabled on the Function App; the Azure Identity SDK's DefaultAzureCredential will fail through its whole credential chain and surface a fairly generic "no credential available" error, which is easy to misdiagnose as a Key Vault problem when it's actually an identity configuration problem.
• Network restrictions: if the Function App has VNet integration and a route that forces all outbound traffic through an NVA (Network Virtual Appliance)/firewall or a restrictive NSG (Network Security Group, a per-subnet/NIC firewall rule set), calls to Key Vault's public endpoint (or, if Key Vault has a Private Endpoint, calls that aren't routed correctly to it) can time out; this looks identical to an RBAC failure from the error message alone (both surface as the SDK failing to get the secret) unless you check whether the failure happens before or after a token was actually issued.
• Insufficient RBAC: a valid token but no Key Vault Secrets User (or equivalent) role assignment on the vault (or the specific secret, if using more granular access controls) for that identity's object ID; this returns a 403 from Key Vault itself, not an SDK-level credential error, which is the fastest way to tell it apart from the two failure modes above.
• Wrong identity used: on a Function App with both a system-assigned and one or more user-assigned identities, DefaultAzureCredential without an explicit client ID can pick the wrong one (or fail ambiguously); when more than one identity exists, always specify which one explicitly (ManagedIdentityCredential(client_id=...)) rather than relying on the default resolution.

Trade-offs and pitfalls

The most common misdiagnosis is treating every "can't get the secret" failure as an RBAC problem and re-granting roles repeatedly, when the actual cause is a network path issue or a missing identity configuration; check whether a token was even issued (App Service diagnostic logs, or a deliberate try/except around just the credential acquisition step versus the Key Vault call) before assuming the permission model is wrong. The role-membership propagation delay is also a frequent source of false alarms right after granting access; if it's been minutes, not hours, since the role assignment, the fix might already be correct and just not yet visible everywhere.