The Limits of SQL: How to Recognize Data Tasks That Belong in Functional Code (Graphs, Matrices, and Attribution)

In modern cloud data architectures on Microsoft Azure—such as Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric—SQL is the primary language for transforming data. SQL is built on relational algebra and declarative set theory. When you write a SELECT query with JOIN, WHERE, and GROUP BY, you tell the database engine what result you want, and the query optimizer decides how to execute it across millions of rows in parallel.

For filtering, joining tables, and calculating standard aggregations like sums and averages, SQL is unmatched. However, data teams often try to force every business problem into SQL. When a business rule depends on sequential state changes, deep graph traversal, matrix algebra, or combinatorial probabilities, declarative set theory works against you. Queries turn into hundreds of lines of fragile Common Table Expressions (CTEs), recursive loops, and self-joins that consume massive compute capacity in Azure Synapse or Fabric while becoming difficult to test and maintain.

In the Microsoft ecosystem, pairing Azure data stores with F# (running on Azure Functions, Azure Container Apps, or .NET for Apache Spark jobs) offers a clean architectural boundary. While SQL handles set-based data preparation, functional code handles algorithmic computation using immutable data structures, pattern matching, and recursion.

This guide walks through 12 real-world business cases on Azure, organized from sequential state problems to attribution models, graph algorithms, and matrix optimizations, followed by an architectural blueprint for connecting SQL and F# in production without I/O bottlenecks.

Part 1: Stateful Sequential Logic

SQL window functions (LAG, LEAD, SUM() OVER()) allow you to inspect adjacent rows, but they cannot maintain a complex, mutating state where the decision made on row N changes the state available for row N + 1.

Case 1: FIFO Inventory Batch Depletion and COGS Calculation

Business Context

A retail distributor running its ERP reporting on Azure SQL and Microsoft Fabric needs to calculate the exact Cost of Goods Sold (COGS) using the First-In, First-Out (FIFO) accounting method. The company buys the same product from suppliers at different prices over time (Purchase Batches). When a customer buys items, the system must deduct those units from the oldest available purchase batch first. If the oldest batch runs out, the remainder is taken from the next batch.

Why SQL Hits a Limit

In SQL, matching outgoing sales against incoming inventory batches requires calculating rolling cumulative sums for both purchases and sales, and then joining them on overlapping quantity intervals:

Plaintext

FIFO Overlap Condition:
Batch_Cumulative_Start < Sale_Cumulative_End
AND Batch_Cumulative_End > Sale_Cumulative_Start

As soon as partial customer returns, warehouse write-offs, or batch expiration dates are introduced, the static cumulative-sum interval trick breaks down because a return modifies the available inventory for subsequent sales. Solving this with a T-SQL CURSOR over millions of order lines causes severe row-by-row locking and tempdb contention.

Functional Solution in F#

In F#, FIFO depletion is a single linear fold (List.fold) over chronological events. We maintain a queue of active inventory batches as an immutable list and deplete the head of the list as sales arrive, accumulating results in O(N + M) linear time.

F#

module FifoInventory

type PurchaseBatch = {
    BatchId: string
    UnitsRemaining: int
    UnitCost: decimal
}

type SaleOrder = {
    OrderId: string
    UnitsSold: int
}

type CogsAllocation = {
    OrderId: string
    BatchId: string
    AllocatedUnits: int
    TotalCost: decimal
}

// Tail-recursively match a single sale against the oldest available purchase batches
let rec allocateSale
    (orderId: string)
    (unitsNeeded: int)
    (batches: PurchaseBatch list)
    (accReversed: CogsAllocation list) : (CogsAllocation list * PurchaseBatch list) =

    match unitsNeeded, batches with
    | 0, _ ->
        (accReversed, batches)
    | needed, [] ->
        // Out-of-stock scenario: record unfulfilled units explicitly
        let shortage = {
            OrderId = orderId
            BatchId = "OUT_OF_STOCK"
            AllocatedUnits = needed
            TotalCost = 0M
        }
        (shortage :: accReversed, [])
    | needed, currentBatch :: restBatches ->
        if currentBatch.UnitsRemaining <= needed then
            // Deplete current batch completely and continue to next batch
            let allocated = {
                OrderId = orderId
                BatchId = currentBatch.BatchId
                AllocatedUnits = currentBatch.UnitsRemaining
                TotalCost = decimal currentBatch.UnitsRemaining * currentBatch.UnitCost
            }
            let remainingNeeded = needed - currentBatch.UnitsRemaining
            allocateSale orderId remainingNeeded restBatches (allocated :: accReversed)
        else
            // Partially deplete current batch; order is fully satisfied
            let allocated = {
                OrderId = orderId
                BatchId = currentBatch.BatchId
                AllocatedUnits = needed
                TotalCost = decimal needed * currentBatch.UnitCost
            }
            let updatedBatch = {
                currentBatch with UnitsRemaining = currentBatch.UnitsRemaining - needed
            }
            (allocated :: accReversed, updatedBatch :: restBatches)

// Process all sales sequentially against the inventory queue in O(N) time
let calculateFifoCogs (initialBatches: PurchaseBatch list) (sales: SaleOrder list) : CogsAllocation list =
    let (allAllocationsReversed, _) =
        sales
        |> List.fold (fun (allocsAcc, currentBatches) sale ->
            allocateSale sale.OrderId sale.UnitsSold currentBatches allocsAcc
        ) ([], initialBatches)

    List.rev allAllocationsReversed

Case 2: Dynamic User Sessionization with Stateful Resets

Business Context

In Case 1, we tracked physical inventory queues. A similar sequential dependency appears in user telemetry. A media streaming company collects clickstream events in Azure Event Hubs and stores them in Azure Data Lake Storage Gen2 (ADLS Gen2). They need to group clicks into user sessions based on three rules:

1. A session expires after 30 minutes of inactivity.
2. A session immediately resets on the next event after a user completes a SubscriptionPurchased event.
3. A session cannot exceed 4 hours in total duration from its first event—even if the user clicks every 5 minutes.

Why SQL Hits a Limit

Rule 1 is simple in SQL using LAG(timestamp) to detect gaps greater than 30 minutes. However, Rule 3 introduces a state dependency: to know whether an event exceeds 4 hours from the start of the current session, you must already know when the current session started—which itself depends on where the previous 4-hour session ended. SQL window functions cannot reference the dynamically computed boundary of the previous window within the same pass.

Functional Solution in F#

In F#, a user’s event stream is sorted chronologically and processed in one pass using List.fold. The accumulator holds the exact start timestamp and last activity timestamp of the active session.

F#

module Sessionization

open System

type UserEvent = {
    UserId: string
    EventType: string
    Timestamp: DateTimeOffset
}

type EnrichedEvent = {
    UserId: string
    EventType: string
    Timestamp: DateTimeOffset
    SessionId: string
}

type SessionState = {
    CurrentSessionIndex: int
    SessionStartTime: DateTimeOffset
    LastEventTime: DateTimeOffset
    PreviousEventWasPurchase: bool
    ResultsReversed: EnrichedEvent list
}

let assignSessions (rawEvents: UserEvent list) : EnrichedEvent list =
    // Ensure strict chronological order before running state machine
    let sortedEvents = rawEvents |> List.sortBy (fun e -> e.Timestamp)

    match sortedEvents with
    | [] -> []
    | firstEvent :: restEvents ->
        let firstSessionId = sprintf "%s-S1" firstEvent.UserId
        let firstEnriched = {
            UserId = firstEvent.UserId
            EventType = firstEvent.EventType
            Timestamp = firstEvent.Timestamp
            SessionId = firstSessionId
        }

        let initialState = {
            CurrentSessionIndex = 1
            SessionStartTime = firstEvent.Timestamp
            LastEventTime = firstEvent.Timestamp
            PreviousEventWasPurchase = (firstEvent.EventType = "SubscriptionPurchased")
            ResultsReversed = [ firstEnriched ]
        }

        let finalState =
            restEvents
            |> List.fold (fun state ev ->
                let idleMinutes = (ev.Timestamp - state.LastEventTime).TotalMinutes
                let totalSessionHours = (ev.Timestamp - state.SessionStartTime).TotalHours

                let mustStartNewSession =
                    idleMinutes > 30.0
                    || totalSessionHours >= 4.0
                    || state.PreviousEventWasPurchase

                let nextIndex =
                    if mustStartNewSession then state.CurrentSessionIndex + 1
                    else state.CurrentSessionIndex

                let nextStart =
                    if mustStartNewSession then ev.Timestamp
                    else state.SessionStartTime

                let sessionId = sprintf "%s-S%d" ev.UserId nextIndex
                let enriched = {
                    UserId = ev.UserId
                    EventType = ev.EventType
                    Timestamp = ev.Timestamp
                    SessionId = sessionId
                }

                {
                    CurrentSessionIndex = nextIndex
                    SessionStartTime = nextStart
                    LastEventTime = ev.Timestamp
                    PreviousEventWasPurchase = (ev.EventType = "SubscriptionPurchased")
                    ResultsReversed = enriched :: state.ResultsReversed
                }
            ) initialState

        List.rev finalState.ResultsReversed

Case 3: SaaS Subscription Proration and Expiring Credit Rollovers

Business Context

Building on stateful timelines, consider a B2B cloud provider running its billing engine on Azure. Customers purchase monthly API credit packages. Unused credits from Month 1 roll over into Month 2, but they expire at the end of Month 2 if unused. Furthermore, whenever an API call is made, the billing engine must consume expiring rollover credits first before touching the current month’s newly granted credits.

Why SQL Hits a Limit

A cumulative sum SUM(credits_granted) - SUM(credits_consumed) in SQL produces wrong balances because unused rollover credits vanish at month boundaries, and the exact amount that expires depends on how much consumption happened in that specific month:

Plaintext

Monthly Balance Equations:
Used_From_Rollover = MIN(Available_Rollover, Consumed_Credits)
Expired_Rollover   = Available_Rollover − Used_From_Rollover
Remaining_Demand   = Consumed_Credits − Used_From_Rollover
Next_Rollover      = MAX(0, Granted_Credits − Remaining_Demand)

Because Available_Rollover in Month T equals Next_Rollover from Month T − 1, every month’s expiration depends recursively on the entire history of prior months.

Functional Solution in F#

We model the billing ledger as an explicit state machine where each monthly cycle updates the rollover bucket and calculates exact overages and expirations.

F#

module SaasBilling

type MonthlyActivity = {
    YearMonth: string
    GrantedCredits: int
    ConsumedCredits: int
}

type MonthlyBillingResult = {
    YearMonth: string
    OverageBilledCredits: int
    ExpiredRolloverCredits: int
    EndingCarryOver: int
}

let processBillingHistory (months: MonthlyActivity list) : MonthlyBillingResult list =
    let (_, resultsReversed) =
        months
        |> List.fold (fun (availableRollover, acc) activity ->
            // 1. Consume from expiring rollover bucket first
            let usedFromRollover = min availableRollover activity.ConsumedCredits
            let remainingDemand = activity.ConsumedCredits - usedFromRollover
            let expiredRollover = availableRollover - usedFromRollover

            // 2. Consume remaining demand from current month's fresh credits
            let usedFromFresh = min activity.GrantedCredits remainingDemand
            let overage = remainingDemand - usedFromFresh

            // 3. Only unused fresh credits roll over into the next month
            let nextMonthRollover = activity.GrantedCredits - usedFromFresh

            let summary = {
                YearMonth = activity.YearMonth
                OverageBilledCredits = overage
                ExpiredRolloverCredits = expiredRollover
                EndingCarryOver = nextMonthRollover
            }

            (nextMonthRollover, summary :: acc)
        ) (0, [])

    List.rev resultsReversed

Part 2: Multi-Touch Marketing Attribution

In Part 1, we processed linear sequences of events for a single entity. Now let us see what happens when those sequences represent customer journeys across marketing channels, and the business wants to calculate the financial contribution of each channel.

Case 4: Time-Decay Attribution with Fatigue Suppression

Business Context

An e-commerce company stores marketing touchpoints (Email, Push Notification, Paid Search, Social Ads) in Azure Synapse. Normally, they use a Time-Decay attribution model where touchpoints closer to the conversion receive more credit based on a 7-day (168-hour) half-life:

Plaintext

Raw_Weight(t) = 0.5 ^ ((Conversion_Time − Touch_Time_Hours) / 168.0)
Normalized_Share(t) = Raw_Weight(t) / SUM(Raw_Weight of all valid touches)

However, marketing analysts noticed “notification spam”: if a customer receives 2 or more unclicked Push Notifications within a rolling 24-hour window, any subsequent unclicked Push Notification in that window annoys the user and must receive zero attribution weight. If the user clicks a Push Notification, their fatigue counter resets.

Why SQL Hits a Limit

In SQL, suppressing touchpoint #4 based on a rolling state of prior unclicked notifications changes the denominator SUM(Raw_Weight) for the entire customer journey, which alters the normalized revenue share of touchpoints #1, #2, and #3.

Functional Solution in F#

An F# pipeline filters out post-conversion events, evaluates the rolling fatigue state sequentially, and normalizes the surviving weights so that allocated revenue sums cleanly to the order total.

F#

module FatigueAttribution

open System

type Touchpoint = {
    TouchId: string
    Channel: string
    Timestamp: DateTimeOffset
    Clicked: bool
}

type AttributedTouch = {
    TouchId: string
    Channel: string
    NormalizedWeight: float
    AttributedRevenue: decimal
}

let attributeWithFatigue
    (conversionTime: DateTimeOffset)
    (orderRevenue: decimal)
    (rawTouches: Touchpoint list) : AttributedTouch list =

    // Keep only pre-conversion touches sorted chronologically
    let validTouches =
        rawTouches
        |> List.filter (fun t -> t.Timestamp <= conversionTime)
        |> List.sortBy (fun t -> t.Timestamp)

    // Step 1: Assign raw decay weights while tracking rolling unclicked Push notifications
    let (_, weightedReversed) =
        validTouches
        |> List.fold (fun (unclickedPushHistory: DateTimeOffset list, acc) touch ->
            // Retain only unclicked pushes within the last 24 hours relative to current touch
            let recentUnclicked =
                unclickedPushHistory
                |> List.filter (fun ts -> (touch.Timestamp - ts).TotalHours <= 24.0)

            let isFatiguedPush =
                touch.Channel = "Push"
                && (not touch.Clicked)
                && (List.length recentUnclicked >= 2)

            let rawWeight =
                if isFatiguedPush then 0.0
                else
                    let hoursBeforeConversion = (conversionTime - touch.Timestamp).TotalHours
                    Math.Pow(0.5, hoursBeforeConversion / 168.0)

            let nextPushHistory =
                if touch.Channel <> "Push" then recentUnclicked
                elif touch.Clicked then [] // Clicking a push notification resets fatigue
                else touch.Timestamp :: recentUnclicked

            (nextPushHistory, (touch, rawWeight) :: acc)
        ) ([], [])

    // Step 2: Normalize weights and allocate order revenue
    let totalWeight = weightedReversed |> List.sumBy snd
    if totalWeight <= 0.0 then []
    else
        weightedReversed
        |> List.rev
        |> List.map (fun (touch, weight) ->
            let share = weight / totalWeight
            {
                TouchId = touch.TouchId
                Channel = touch.Channel
                NormalizedWeight = share
                AttributedRevenue = Math.Round(orderRevenue * decimal share, 2)
            }
        )

Case 5: Markov Chain Attribution (Removal Effect Calculation)

Business Context

Moving beyond heuristic formulas like Time-Decay, a digital retailer on Azure wants data-driven attribution using Markov Chains. Customer journeys are modeled as a directed graph of states (Start → Facebook → Email → Google → Conversion or DropOff). To find the true contribution of Facebook, the system calculates the Removal Effect: how much the overall conversion probability from Start drops if Facebook is removed from the network (redirecting all traffic entering Facebook straight to DropOff):

Plaintext

Removal_Effect(Channel_i) = Base_Conversion_Prob − Conversion_Prob_Without(Channel_i)
Channel_Share(Channel_i)  = Removal_Effect(Channel_i) / SUM(All_Removal_Effects)

Why SQL Hits a Limit

Counting direct transitions between channel pairs (FromState → ToState) is easy in SQL using LEAD() and GROUP BY. However, once you have the transition matrix, computing the total absorption probability into Conversion when customers loop between channels (Email → Google → Email → Conversion) requires iterative state propagation or matrix inversion—and you must re-run that simulation separately for every channel removal.

Functional Solution in F#

SQL aggregates millions of raw events into a compact list of state-to-state transition probabilities (typically a few hundred rows). An F# function reads those probabilities and simulates the removal effect for every channel in memory.

F#

module MarkovAttribution

type Transition = {
    FromState: string
    ToState: string
    Probability: float
}

// Propagate probability mass from "Start" across the transition graph
let simulateConversionProbability
    (transitions: Transition list)
    (removedChannel: string option)
    (iterations: int) : float =

    let adjacency =
        transitions
        |> List.groupBy (fun t -> t.FromState)
        |> Map.ofList

    let initialDist = Map.ofList [ ("Start", 1.0) ]

    let finalDist =
        [ 1 .. iterations ]
        |> List.fold (fun currentDist _ ->
            currentDist
            |> Map.toList
            |> List.collect (fun (state, mass) ->
                // Absorbing states keep their accumulated probability mass
                if state = "Conversion" || state = "DropOff" then
                    [ (state, mass) ]
                elif removedChannel = Some state then
                    [ ("DropOff", mass) ]
                else
                    match Map.tryFind state adjacency with
                    | None -> [ ("DropOff", mass) ]
                    | Some edges ->
                        edges
                        |> List.map (fun edge ->
                            // Redirect traffic entering the removed channel straight to DropOff
                            let targetState =
                                if removedChannel = Some edge.ToState then "DropOff"
                                else edge.ToState
                            (targetState, mass * edge.Probability)
                        )
            )
            |> List.groupBy fst
            |> List.map (fun (st, pairs) -> (st, pairs |> List.sumBy snd))
            |> Map.ofList
        ) initialDist

    Map.tryFind "Conversion" finalDist |> Option.defaultValue 0.0

// Compute normalized Removal Effect for each marketing channel
let calculateMarkovAttribution (transitions: Transition list) (channels: string list) : (string * float) list =
    let baseConversion = simulateConversionProbability transitions None 40

    let removalEffects =
        channels
        |> List.distinct
        |> List.map (fun ch ->
            let convWithoutChannel = simulateConversionProbability transitions (Some ch) 40
            let effect = max 0.0 (baseConversion - convWithoutChannel)
            (ch, effect)
        )

    let totalEffect = removalEffects |> List.sumBy snd
    if totalEffect <= 0.0 then []
    else
        removalEffects |> List.map (fun (ch, eff) -> (ch, eff / totalEffect))

Case 6: Game-Theoretic Shapley Value Attribution

Business Context

A B2B software enterprise has long sales cycles involving webinars, whitepapers, outbound sales calls, and partner referrals. Because multiple channels often engage the client in parallel rather than in a strict sequence, the analytics team uses Shapley Values from cooperative game theory. This requires evaluating the conversion rate of every possible subset (coalition) of channels to measure what a channel adds on average when joining an existing subset S.

In plain mathematical notation, the Shapley Value for channel i across the full set of channels N is:

Plaintext

Shapley_Value(i) = SUM over all subsets S (where S does not contain i) of:
   [ |S|! × (|N| − |S| − 1)! / |N|! ] × [ Value(S union {i}) − Value(S) ]

Where:

• |N| is the total number of marketing channels.
• S is a subset (coalition) of channels excluding channel i.
• |S|! is the factorial of the number of channels in subset S.
• Value(S) is the historical conversion rate when only the channels in S are present.

Why SQL Hits a Limit

With 12 marketing channels, there are 2¹² = 4,096 possible coalitions. Computing the marginal contribution Value(S union {i}) − Value(S) weighted by factorials requires generating power sets and joining each subset with its single-element expansion. Writing dynamic power-set generation in T-SQL produces massive cross-joins that quickly time out.

Functional Solution in F#

SQL groups historical journeys by their distinct channel set to produce the empirical lookup table Value(S), and F# generates the power sets and computes the exact factorial weights in memory.

F#

module ShapleyAttribution

// Recursively generate all subsets (power set) of a list of channels
let rec powerSet (items: string list) : Set<string> list =
    match items with
    | [] -> [ Set.empty ]
    | head :: tail ->
        let subSets = powerSet tail
        subSets @ (subSets |> List.map (fun s -> Set.add head s))

let rec factorial (n: int) : float =
    if n <= 1 then 1.0 else float n * factorial (n - 1)

// Calculate exact Shapley value for each channel given empirical coalition conversion rates
let computeShapleyValues
    (rawChannels: string list)
    (coalitionValueLookup: Map<Set<string>, float>) : (string * float) list =

    let allChannels = rawChannels |> List.distinct
    let n = List.length allChannels
    let nFact = factorial n

    // Generate the full power set once and filter per channel
    let allSubsets = powerSet allChannels

    allChannels
    |> List.map (fun targetChannel ->
        let coalitionsWithoutTarget =
            allSubsets |> List.filter (fun s -> not (Set.contains targetChannel s))

        let shapleyValue =
            coalitionsWithoutTarget
            |> List.sumBy (fun s ->
                let sSize = Set.count s
                let weight = (factorial sSize * factorial (n - sSize - 1)) / nFact

                let sWithTarget = Set.add targetChannel s
                let valWith = Map.tryFind sWithTarget coalitionValueLookup |> Option.defaultValue 0.0
                let valWithout = Map.tryFind s coalitionValueLookup |> Option.defaultValue 0.0

                weight * (valWith - valWithout)
            )

        (targetChannel, shapleyValue)
    )

Part 3: Graph Traversal, Cycles, and Network Topologies

Notice how Case 5 introduced directed graphs. When data represents networks—financial transactions, manufacturing trees, or customer identity links—SQL’s tabular relational model hits its hardest performance limit.

Case 7: Circular Payment Detection in Anti-Money Laundering (AML)

Business Context

A financial technology platform running on Azure processes millions of transfers daily. Compliance regulations require detecting circular fund flows (“round-tripping”): cases where money leaves Account A, passes through at least 3 intermediate accounts (A → B → C → D → A), and returns to Account A within a 24-hour window, with each transfer occurring chronologically after the previous one.

Why SQL Hits a Limit

In T-SQL, traversing a graph requires a recursive CTE. However, financial transaction graphs contain high-degree merchant nodes and millions of paths. To prevent infinite loops in a T-SQL recursive CTE, developers concatenate visited account IDs into a string column (CAST(Path + '->' + NextAccount AS VARCHAR(MAX))) and filter with WHERE Path NOT LIKE '%' + NextAccount + '%'. On millions of transactions, string pattern matching inside a recursive join spills gigabytes to tempdb and fails.

Functional Solution in F#

By loading the suspicious transfer window into an F# worker, we run a constrained Depth-First Search (DFS) that enforces three pruning rules at every hop: chronological progression, the 24-hour total cycle window, and cycle length limits using a fast immutable Set.

F#

module AmlCycleDetection

open System

type TransferEdge = {
    FromAccount: string
    ToAccount: string
    Amount: decimal
    Timestamp: DateTimeOffset
}

// Find chronological cycles returning to startAccount within 24 hours and 3..maxHops intermediate nodes
let findCircularFlows (edges: TransferEdge list) (maxHops: int) : (string list) list =
    let adjacency =
        edges
        |> List.groupBy (fun e -> e.FromAccount)
        |> Map.ofList

    let rec dfs
        (startAccount: string)
        (currentAccount: string)
        (cycleStartTime: DateTimeOffset option)
        (lastTimestamp: DateTimeOffset)
        (visited: Set<string>)
        (pathReversed: string list)
        (hopsLeft: int) : (string list) list =

        if hopsLeft <= 0 then []
        else
            match Map.tryFind currentAccount adjacency with
            | None -> []
            | Some outgoingEdges ->
                outgoingEdges
                |> List.collect (fun edge ->
                    let firstHopTime = defaultArg cycleStartTime edge.Timestamp
                    let totalElapsedHours = (edge.Timestamp - firstHopTime).TotalHours

                    // Pruning Rule 1: Transfer must be chronological and within 24 hours of first hop
                    if edge.Timestamp < lastTimestamp || totalElapsedHours > 24.0 then
                        []
                    // Pruning Rule 2: Returned to startAccount after at least 3 intermediate accounts (path length >= 4)
                    elif edge.ToAccount = startAccount && List.length pathReversed >= 4 then
                        [ List.rev (startAccount :: pathReversed) ]
                    // Pruning Rule 3: Do not revisit intermediate accounts already in the current path
                    elif Set.contains edge.ToAccount visited then
                        []
                    else
                        dfs
                            startAccount
                            edge.ToAccount
                            (Some firstHopTime)
                            edge.Timestamp
                            (Set.add edge.ToAccount visited)
                            (edge.ToAccount :: pathReversed)
                            (hopsLeft - 1)
                )

    let allSourceAccounts = adjacency |> Map.keys |> Seq.toList
    allSourceAccounts
    |> List.collect (fun startAcc ->
        dfs startAcc startAcc None DateTimeOffset.MinValue (Set.singleton startAcc) [ startAcc ] maxHops
    )

Case 8: Multi-Level Bill of Materials (BOM) Cost Rollup with Scrap and Setup Amortization

Business Context

In manufacturing ERP systems hosted on Azure, a finished product (such as an industrial compressor) is assembled from sub-assemblies, which are built from smaller parts, which are made from raw materials—often 8 to 12 levels deep. Each assembly step has a material scrap factor, direct labor cost, and a fixed machine setup cost amortized over a standard production batch size:

Plaintext

Effective_Child_Cost = Child_Unit_Cost × Quantity_Required × (1.0 + Scrap_Factor)
Assembly_Unit_Cost   = Labor_Cost + (Setup_Cost / Batch_Size) + SUM(Effective_Child_Cost)

When raw steel or copper prices change, the finance team needs to recalculate the exact unit cost of 50,000 finished products while also detecting any accidental circular references in the engineering BOM table.

Why SQL Hits a Limit

Because sub-assembly X can appear at Level 2 in one product and Level 6 in another, SQL recursive CTEs recompute the same shared sub-assembly thousands of times across every parent tree instead of caching the result once.

Functional Solution in F#

In F#, a Directed Acyclic Graph (DAG) cost rollup is solved with recursive traversal, cycle detection (visiting: Set<string>), and memoization (Dictionary<string, decimal>) so that every sub-assembly is calculated exactly once in O(V + E) time.

F#

module BomCostRollup

open System.Collections.Generic

type ComponentRequirement = {
    ChildItemId: string
    QuantityRequired: decimal
    ScrapFactor: decimal // e.g., 0.05 means 5% scrap loss during assembly
}

type ItemNode =
    | RawMaterial of itemId: string * unitPurchaseCost: decimal
    | ManufacturedAssembly of
        itemId: string *
        laborCost: decimal *
        setupCost: decimal *
        batchSize: int *
        components: ComponentRequirement list

let calculateAllUnitCosts (catalog: Map<string, ItemNode>) : Map<string, decimal> =
    let memoCache = Dictionary<string, decimal>()

    let rec computeItemCost (visiting: Set<string>) (itemId: string) : decimal =
        if Set.contains itemId visiting then
            failwithf "Circular BOM reference detected involving item: %s" itemId

        match memoCache.TryGetValue(itemId) with
        | true, cachedCost -> cachedCost
        | false, _ ->
            let nextVisiting = Set.add itemId visiting
            let computed =
                match Map.tryFind itemId catalog with
                | None -> failwithf "Missing item in BOM catalog: %s" itemId
                | Some (RawMaterial (_, unitCost)) -> unitCost
                | Some (ManufacturedAssembly (_, laborCost, setupCost, batchSize, requirements)) ->
                    let safeBatchSize = max 1 batchSize
                    let amortizedSetup = setupCost / decimal safeBatchSize
                    let materialCost =
                        requirements
                        |> List.sumBy (fun req ->
                            let childCost = computeItemCost nextVisiting req.ChildItemId
                            let effectiveQty = req.QuantityRequired * (1.0M + req.ScrapFactor)
                            childCost * effectiveQty
                        )
                    laborCost + amortizedSetup + materialCost

            memoCache.[itemId] <- computed
            computed

    catalog
    |> Map.keys
    |> Seq.map (fun id -> (id, computeItemCost Set.empty id))
    |> Map.ofSeq

Case 9: Customer Identity Resolution (Connected Components Clustering)

Business Context

A retail group with online stores, mobile apps, and physical loyalty cards stores customer interactions in Azure Data Lake. The same person might place Order 1 using email_A + phone_1, Order 2 using phone_1 + loyalty_card_X, and Order 3 using loyalty_card_X + device_99. To build a unified Customer 360 profile in Microsoft Fabric, the data engineering team needs to link all transitively connected identifiers under a single deterministic MasterProfileId.

Why SQL Hits a Limit

In graph theory, this is the Connected Components problem. In SQL, finding transitive connections across an arbitrary number of hops requires writing a WHILE loop that self-joins an edge table repeatedly until an iteration produces zero new pairs. On millions of identifiers, repeated self-joins cause heavy data shuffling across compute nodes.

Functional Solution in F#

Using a tail-recursive, frontier-based Breadth-First Search (BFS) over immutable Set structures in F#, we visit every identifier and edge once (O(V + E) complexity), assigning the alphabetically smallest identifier in each connected component as the canonical MasterProfileId.

F#

module IdentityResolution

type IdentityEdge = {
    IdA: string
    IdB: string
}

let resolveIdentityClusters (edges: IdentityEdge list) : Map<string, string> =
    // Build bidirectional adjacency map
    let adjacency =
        edges
        |> List.collect (fun e -> [ (e.IdA, e.IdB); (e.IdB, e.IdA) ])
        |> List.groupBy fst
        |> List.map (fun (node, pairs) -> (node, pairs |> List.map snd |> Set.ofList))
        |> Map.ofList

    // Tail-recursive frontier BFS using Sets to avoid list concatenation overhead
    let rec exploreCluster (frontier: Set<string>) (visitedInCluster: Set<string>) : Set<string> =
        if Set.isEmpty frontier then
            visitedInCluster
        else
            let nextFrontier =
                frontier
                |> Set.toList
                |> List.collect (fun node ->
                    Map.tryFind node adjacency
                    |> Option.defaultValue Set.empty
                    |> Set.toList
                )
                |> Set.ofList
                |> fun neighbors -> Set.difference neighbors visitedInCluster

            exploreCluster nextFrontier (Set.union visitedInCluster nextFrontier)

    let allNodes = adjacency |> Map.keys |> Seq.toList

    let (_, masterMappingsReversed) =
        allNodes
        |> List.fold (fun (globallyVisited: Set<string>, accMappings: (string * string) list) node ->
            if Set.contains node globallyVisited then
                (globallyVisited, accMappings)
            else
                let initialSet = Set.singleton node
                let cluster = exploreCluster initialSet initialSet
                let masterId = Set.minElement cluster // Deterministic canonical ID

                let newPairs =
                    cluster
                    |> Set.toList
                    |> List.map (fun memberId -> (memberId, masterId))

                (Set.union globallyVisited cluster, newPairs @ accMappings)
        ) (Set.empty, [])

    Map.ofList masterMappingsReversed

Part 4: Matrix Operations, Linear Algebra, and Constrained Optimization

In Part 3, we saw how networks defeat relational joins. Our fourth category explores problems where variables interact simultaneously through systems of linear equations, high-dimensional sparse vectors, or bipartite supply-and-demand constraints.

Case 10: Reciprocal Cost Allocation (Solving Simultaneous Linear Equations)

Business Context

In corporate finance systems running on Azure Synapse, internal service departments (IT, Human Resources, Facilities) provide services to commercial business units (Retail, Wholesale) and to each other. For example:

• IT has $100,000 in direct expenses; HR consumes 10% of IT’s workload, Retail consumes 50%, and Wholesale consumes 40%.
• HR has $80,000 in direct expenses; IT consumes 20% of HR’s workload, Retail consumes 40%, and Wholesale consumes 40%.

To allocate service costs accurately without leaving unallocated remainders (the Reciprocal Allocation Method), finance must first solve a system of simultaneous linear equations for the service departments:

Plaintext

System of Simultaneous Equations:
IT_Total = $100,000 + (0.20 × HR_Total)
HR_Total = $80,000  + (0.10 × IT_Total)

Matrix Form:
(I − A) × X = B

Once IT_Total ($118,367.35) and HR_Total ($91,836.73) are found, they are allocated to the commercial units (Retail and Wholesale) using their respective consumption shares so that the final commercial costs sum exactly to the original $180,000 cash expenditure.

Why SQL Hits a Limit

When 25 internal service departments cross-charge each other, this forms a 25 × 25 linear system. SQL has no built-in matrix solver or iterative convergence loop for simultaneous equations.

Functional Solution in F#

In F#, we converge on the exact reciprocal service totals using iterative fixed-point substitution and then project the converged vector onto the commercial target departments.

F#

module ReciprocalCostAllocation

type DepartmentCost = {
    DeptId: string
    IsServiceDept: bool
    DirectExpense: float
}

type AllocationShare = {
    FromServiceDept: string
    ToDept: string
    ShareRatio: float // Fraction of FromServiceDept consumed by ToDept
}

let allocateReciprocalCosts
    (departments: DepartmentCost list)
    (shares: AllocationShare list)
    (iterations: int) : Map<string, float> =

    let serviceDepts =
        departments
        |> List.filter (fun d -> d.IsServiceDept)
        |> List.map (fun d -> (d.DeptId, d.DirectExpense))
        |> Map.ofList

    let commercialDepts =
        departments
        |> List.filter (fun d -> not d.IsServiceDept)

    let incomingByRecipient =
        shares
        |> List.groupBy (fun s -> s.ToDept)
        |> Map.ofList

    // Step 1: Solve (I - A) * X = B iteratively for all service departments
    let convergedServiceTotals =
        [ 1 .. iterations ]
        |> List.fold (fun currentTotals _ ->
            serviceDepts
            |> Map.map (fun serviceId directExpense ->
                let crossCharge =
                    match Map.tryFind serviceId incomingByRecipient with
                    | None -> 0.0
                    | Some incomingList ->
                        incomingList
                        |> List.sumBy (fun s ->
                            let providerTotal = Map.tryFind s.FromServiceDept currentTotals |> Option.defaultValue 0.0
                            providerTotal * s.ShareRatio
                        )
                directExpense + crossCharge
            )
        ) serviceDepts

    // Step 2: Allocate converged service totals to commercial (production) departments
    commercialDepts
    |> List.map (fun commDept ->
        let allocatedOverhead =
            match Map.tryFind commDept.DeptId incomingByRecipient with
            | None -> 0.0
            | Some incomingList ->
                incomingList
                |> List.sumBy (fun s ->
                    let serviceTotal = Map.tryFind s.FromServiceDept convergedServiceTotals |> Option.defaultValue 0.0
                    serviceTotal * s.ShareRatio
                )
        (commDept.DeptId, commDept.DirectExpense + allocatedOverhead)
    )
    |> Map.ofList

Case 11: High-Dimensional Sparse Cosine Similarity for Cross-Sell Recommendations

Business Context

A B2B industrial supplier on Azure has 40,000 active products (SKUs) and 100,000 business customers. They want to compute item-to-item Cosine Similarity based on customer spend vectors A and B to power product recommendations:

Plaintext

Cosine_Similarity(A, B) = SUM(A_i × B_i) / ( SQRT(SUM(A_i²)) × SQRT(SUM(B_i²)) )

Because most customers buy only 10 to 30 distinct products out of 40,000, the customer-product matrix is 99.9% sparse (zeros).

Why SQL Hits a Limit

To calculate cosine similarity between product pairs in SQL, you must self-join the customer_sku_spend table on a.customer_id = b.customer_id AND a.sku_id < b.sku_id. If one popular item (such as safety gloves) was bought by 50,000 customers who each bought 25 other items, that single SKU generates over a million intermediate joined rows before GROUP BY aggregates the dot product. Across 40,000 SKUs, the self-join spills hundreds of gigabytes to disk.

Functional Solution in F#

By pre-normalizing each SKU’s spend vector to unit length (L2 norm = 1.0) and storing only non-zero customer entries in an F# Map<int, float>, we compute the sparse dot product in memory across all CPU cores using Array.Parallel.

F#

module SparseCosineSimilarity

open System

type PurchaseRecord = {
    SkuId: string
    CustomerId: int
    SpendAmount: float
}

type SkuSimilarity = {
    SkuA: string
    SkuB: string
    CosineScore: float
}

// Aggregate duplicate (SkuId, CustomerId) rows and L2-normalize each SKU vector
let buildNormalizedVectors (records: PurchaseRecord list) : (string * Map<int, float>) array =
    records
    |> List.groupBy (fun r -> r.SkuId)
    |> List.map (fun (sku, rawItems) ->
        // Deduplicate by CustomerId first so multiple orders by the same customer sum properly
        let customerSpend =
            rawItems
            |> List.groupBy (fun x -> x.CustomerId)
            |> List.map (fun (custId, rows) -> (custId, rows |> List.sumBy (fun r -> r.SpendAmount)))

        let l2Norm =
            customerSpend
            |> List.sumBy (fun (_, spend) -> spend * spend)
            |> Math.Sqrt

        let normalizedVector =
            if l2Norm <= 0.0 then Map.empty
            else
                customerSpend
                |> List.map (fun (custId, spend) -> (custId, spend / l2Norm))
                |> Map.ofList

        (sku, normalizedVector)
    )
    |> List.toArray

// Compute sparse dot product by iterating only over the smaller non-zero map
let sparseDotProduct (vecA: Map<int, float>) (vecB: Map<int, float>) : float =
    let (smaller, larger) =
        if Map.count vecA <= Map.count vecB then (vecA, vecB) else (vecB, vecA)

    smaller
    |> Map.fold (fun acc custId weightA ->
        match Map.tryFind custId larger with
        | Some weightB -> acc + (weightA * weightB)
        | None -> acc
    ) 0.0

let computeTopSimilarities (records: PurchaseRecord list) (minScoreThreshold: float) : SkuSimilarity list =
    let vectors = buildNormalizedVectors records
    let count = vectors.Length

    if count < 2 then []
    else
        [| 0 .. count - 2 |]
        |> Array.Parallel.collect (fun i ->
            let (skuA, vecA) = vectors.[i]
            let matches = ResizeArray<SkuSimilarity>()
            for j in (i + 1) .. (count - 1) do
                let (skuB, vecB) = vectors.[j]
                let score = sparseDotProduct vecA vecB
                if score >= minScoreThreshold then
                    matches.Add({ SkuA = skuA; SkuB = skuB; CosineScore = score })
            matches.ToArray()
        )
        |> Array.toList

Case 12: Multi-Warehouse Stock Rebalancing (Least-Cost Bipartite Allocation)

Business Context

Our twelfth case combines matrix costs with stateful resource depletion. A retail chain with 60 regional warehouses on Azure notices that after a seasonal promotion, 20 warehouses have excess inventory of winter jackets (Surplus), while 25 other warehouses are running out of stock (Shortage). They have a freight cost matrix between every pair of warehouses (FromWarehouse, ToWarehouse, UnitShippingCost). The logistics team needs a daily automated transfer plan using the Least-Cost Allocation Heuristic to satisfy shortages from surpluses.

Why SQL Hits a Limit

If you join the Surplus table with the Shortage table and sort by UnitShippingCost ASC in SQL, you cannot allocate stock in a declarative SELECT query: the moment Warehouse A ships 50 jackets to Warehouse B, Warehouse A’s remaining surplus drops by 50 and Warehouse B’s remaining shortage drops by 50 simultaneously:

Plaintext

Simultaneous Depletion Rule at Step k:
Transfer_Qty       = MIN(Available_Surplus[From], Remaining_Shortage[To])
New_Surplus[From]  = Available_Surplus[From] − Transfer_Qty
New_Shortage[To]   = Remaining_Shortage[To]  − Transfer_Qty

That single transfer changes the available capacity for every subsequent row in the sorted shipping matrix.

Functional Solution in F#

In F#, we sort the candidate shipping lanes by unit cost once and fold over them sequentially, updating both the SurplusMap and the ShortageMap immutably after each transfer.

F#

module InventoryRebalancing

type ShippingLane = {
    FromWarehouse: string
    ToWarehouse: string
    UnitShippingCost: decimal
}

type TransferInstruction = {
    FromWarehouse: string
    ToWarehouse: string
    UnitsToTransfer: int
    TotalShippingCost: decimal
}

let optimizeStockTransfers
    (surpluses: Map<string, int>)
    (shortages: Map<string, int>)
    (lanes: ShippingLane list) : TransferInstruction list =

    let sortedLanes = lanes |> List.sortBy (fun lane -> lane.UnitShippingCost)

    let (_, _, transfersReversed) =
        sortedLanes
        |> List.fold (fun (currSurplus: Map<string, int>, currShortage: Map<string, int>, acc: TransferInstruction list) lane ->
            let availSurplus = Map.tryFind lane.FromWarehouse currSurplus |> Option.defaultValue 0
            let neededShortage = Map.tryFind lane.ToWarehouse currShortage |> Option.defaultValue 0

            let transferQty = min availSurplus neededShortage

            if transferQty <= 0 then
                (currSurplus, currShortage, acc)
            else
                let instruction = {
                    FromWarehouse = lane.FromWarehouse
                    ToWarehouse = lane.ToWarehouse
                    UnitsToTransfer = transferQty
                    TotalShippingCost = decimal transferQty * lane.UnitShippingCost
                }

                let nextSurplus = Map.add lane.FromWarehouse (availSurplus - transferQty) currSurplus
                let nextShortage = Map.add lane.ToWarehouse (neededShortage - transferQty) currShortage

                (nextSurplus, nextShortage, instruction :: acc)
        ) (surpluses, shortages, [])

    List.rev transfersReversed

Part 5: Engineering the Data Bridge on Azure (How to Connect SQL and F# Without I/O Bottlenecks)

A common concern when moving algorithms out of SQL into F# is data movement overhead. If an Azure Function pulls 50 million raw rows over JDBC/ODBC from Azure Synapse, computes a graph algorithm, and inserts 50 million rows back one by one, network serialization will become the new bottleneck.

To make a hybrid SQL + F# architecture fast in production on Azure, follow three architectural rules:

Rule 1: Let SQL Pre-Aggregate the Graph or Matrix Input

Never send raw event logs to F# if SQL can compress them first.

• In Case 5 (Markov Attribution), SQL should compress 100 million raw clickstream rows into 400 rows of (FromState, ToState, Probability) using LEAD() and GROUP BY. F# then reads only 400 rows.
• In Case 6 (Shapley Attribution), SQL groups journeys by their distinct channel combination (STRING_AGG(Channel, ',') WITHIN GROUP (ORDER BY Channel)) and sends only the summary counts to F#.
• In Case 11 (Cosine Similarity), SQL filters out inactive SKUs and aggregates total spend per (SkuId, CustomerId) before F# computes the vector products.

Rule 2: Exchange Bulk Data via Parquet on ADLS Gen2 / OneLake

When the algorithm genuinely needs millions of rows (such as Case 2 Sessionization or Case 9 Identity Resolution), bypass row-by-row database drivers:

1. Export: In Azure Synapse or Microsoft Fabric, run CREATE EXTERNAL TABLE AS SELECT (CETAS) to write the pre-filtered dataset directly to Azure Data Lake Storage Gen2 (or Fabric OneLake) as compressed Parquet files.
2. Compute: Trigger an F# worker in Azure Container Apps Jobs or Azure Functions that reads the Parquet columns directly into memory using Parquet.Net or Apache.Arrow, executes the functional algorithm, and writes the output back as a Parquet file to a staging path in ADLS Gen2.
3. Ingest: Run a single COPY INTO statement in Synapse or Fabric to load the resulting Parquet file into the final analytical table in seconds.

Production Example: F# Parquet Pipeline Worker for Azure Container Apps

Below is a structural template showing how an F# worker reads pre-staged input from Azure Blob Storage / ADLS Gen2, runs a pure functional algorithm, and writes the output batch back seamlessly:

F#

module AzureDataBridge

open System.IO
open Azure.Storage.Blobs
open Parquet
open Parquet.Serialization

// Pure domain types matching the Parquet schema
type IdentityEdgeRow = {
    IdA: string
    IdB: string
}

type MasterMappingRow = {
    Identifier: string
    MasterProfileId: string
}

// Read input Parquet file from ADLS Gen2, execute F# algorithm, and write output Parquet file
let runIdentityResolutionJob
    (connectionString: string)
    (containerName: string)
    (inputBlobPath: string)
    (outputBlobPath: string) =
    async {
        let containerClient = BlobContainerClient(connectionString, containerName)

        // 1. Download compressed Parquet stream from ADLS Gen2 into memory
        let inputBlob = containerClient.GetBlobClient(inputBlobPath)
        use inputStream = new MemoryStream()
        let! _ = inputBlob.DownloadToAsync(inputStream) |> Async.AwaitTask
        inputStream.Position <- 0L

        // 2. Deserialize Parquet rows into F# records
        let! rawRows = ParquetSerializer.DeserializeAsync<IdentityEdgeRow>(inputStream) |> Async.AwaitTask
        let edges =
            rawRows
            |> Seq.map (fun r -> { IdentityResolution.IdA = r.IdA; IdentityResolution.IdB = r.IdB })
            |> Seq.toList

        // 3. Execute pure functional Connected Components algorithm (from Case 9)
        let resolvedMap = IdentityResolution.resolveIdentityClusters edges

        let outputRecords =
            resolvedMap
            |> Map.toArray
            |> Array.map (fun (id, masterId) -> { Identifier = id; MasterProfileId = masterId })

        // 4. Serialize results back to Parquet and upload to ADLS Gen2 staging path
        use outputStream = new MemoryStream()
        let! _ = ParquetSerializer.SerializeAsync(outputRecords, outputStream) |> Async.AwaitTask
        outputStream.Position <- 0L

        let outputBlob = containerClient.GetBlobClient(outputBlobPath)
        let! _ = outputBlob.UploadAsync(outputStream, overwrite = true) |> Async.AwaitTask
        return outputRecords.Length
    }

Summary Checklist: How to Decide Between SQL and Functional Code

When designing data pipelines on Azure, ask yourself four diagnostic questions about the business logic before writing a 500-line SQL script:

Diagnostic Question	If “No” → Keep in SQL (Synapse / Fabric)	If “Yes” → Offload to Functional Code (F#)
1. Does row N modify the capacity, balance, or window boundary needed to evaluate row N + 1?	Standard Window Functions (SUM() OVER, LAG, LEAD)	Stateful Fold (List.fold, FIFO queues, dynamic session resets, expiring credit buckets)
2. Does the logic require evaluating 2ⁿ subsets or running N separate graph simulations?	Standard GROUP BY and static weights (First/Last Touch)	Markov Chain removal simulations, Game-Theoretic Shapley Values
3. Does the data represent a network with variable depth, shared sub-trees, or cycles?	Flat relational joins (1 to 3 fixed hierarchy levels)	Graph DFS/BFS, AML cycle detection, memoized BOM rollups, Connected Components
4. Do variables depend on simultaneous linear equations or bipartite supply-demand depletion?	Independent row formulas (Unit_Price × Quantity)	Reciprocal cost matrix solvers, sparse cosine similarity, constrained stock rebalancing

By keeping set-based filtering and pre-aggregation in SQL while offloading stateful, graph, and matrix algorithms to pure functions in F#, your Azure data platform remains fast, testable, and predictable in cost.