🔗 Graph in Fabric — Relationship Modeling and Graph Analytics¶

// Patron node table
.create table Patrons (
    PatronId: string,
    Name: string,
    TierLevel: string,
    JoinDate: datetime,
    RiskScore: int
)

// Machine node table
.create table Machines (
    MachineId: string,
    MachineType: string,
    FloorLocation: string,
    Denomination: decimal
)

// Employee node table
.create table Employees (
    EmployeeId: string,
    Name: string,
    Role: string,
    Department: string
)

// Transaction edges: Patron → Machine
.create table Transactions (
    TransactionId: string,
    PatronId: string,      // Source node
    MachineId: string,     // Target node
    Timestamp: datetime,
    Amount: decimal,
    TransactionType: string
)

// Patron-to-Patron edges (shared sessions, referrals)
.create table PatronRelationships (
    RelationshipId: string,
    PatronId1: string,     // Source node
    PatronId2: string,     // Target node
    RelationshipType: string,
    FirstObserved: datetime
)

// Batch ingest from Lakehouse (one-time or scheduled)
.set-or-append Patrons <|
    external_table('lh_gold.gold_player_profiles')
    | project PatronId = player_id, Name = player_name,
              TierLevel = tier_level, JoinDate = registration_date,
              RiskScore = risk_score

// Continuous ingestion via Eventstream mapping
// Configure Eventstream → Eventhouse destination with table mapping

// Basic graph query: Find all patrons who transacted on the same machine
Transactions
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron1)-[tx1]->(machine)<-[tx2]-(patron2)
    where patron1.PatronId != patron2.PatronId
    and tx1.Amount > 5000
    and tx2.Amount > 5000
| project patron1.PatronId, patron2.PatronId, machine.MachineId,
          tx1.Amount, tx2.Amount, tx1.Timestamp

Transactions
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron)-[tx]->(machine)
    where patron.PatronId == "P-12345"
| project machine.MachineId, machine.FloorLocation,
          tx.Amount, tx.Timestamp
| order by tx.Timestamp desc

Transactions
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (p1)-[tx1]->(machine)<-[tx2]-(p2)
    where p1.PatronId != p2.PatronId
    and abs(datetime_diff('minute', tx1.Timestamp, tx2.Timestamp)) < 30
| summarize SharedMachines = dcount(machine.MachineId),
            CoLocationCount = count()
    by p1.PatronId, p2.PatronId
| where CoLocationCount >= 3
| order by CoLocationCount desc

Transactions
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-shortest-paths (source)-[e*1..5]->(target)
    where source.PatronId == "P-12345"
    and target.PatronId == "P-67890"
| project PathLength = array_length(e),
          PathNodes = e

Transactions
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron)-[tx]->(machine)
| summarize UniqueMachines = dcount(machine.MachineId),
            TotalTransactions = count(),
            TotalAmount = sum(tx.Amount)
    by patron.PatronId, patron.TierLevel, patron.RiskScore
| extend ConnectionDensity = TotalTransactions / UniqueMachines
| order by ConnectionDensity desc

Transactions
| where Timestamp > ago(24h)
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron)-[tx]->(machine)
| summarize Machines = make_set(machine.MachineId),
            Timestamps = make_list(tx.Timestamp),
            Amounts = make_list(tx.Amount)
    by patron.PatronId
| where array_length(Machines) >= 5
| extend AvgTimeBetween = datetime_diff('minute',
    Timestamps[array_length(Timestamps)-1], Timestamps[0]) / array_length(Timestamps)
| where AvgTimeBetween < 15
| project patron.PatronId, MachineCount = array_length(Machines),
          AvgTimeBetween, TotalAmount = array_sum(Amounts)

// Detect structuring: patrons with multiple transactions $8K-$9.9K within 24 hours
Transactions
| where Timestamp > ago(24h)
| where Amount between (8000.0 .. 9999.99)
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron)-[tx]->(machine)
| summarize
    TransactionCount = count(),
    TotalAmount = sum(tx.Amount),
    MachinesUsed = dcount(machine.MachineId),
    Transactions = make_list(pack(
        "machine", machine.MachineId,
        "amount", tx.Amount,
        "time", tx.Timestamp
    ))
    by patron.PatronId, patron.Name, patron.RiskScore
| where TransactionCount >= 3 and TotalAmount >= 20000
| extend StructuringRisk = case(
    TotalAmount >= 30000 and MachinesUsed >= 4, "Critical",
    TotalAmount >= 25000 and MachinesUsed >= 3, "High",
    "Medium")
| order by TotalAmount desc

// Find patron clusters: groups of patrons who share machines and have
// correlated transaction timing (potential coordinated activity)
Transactions
| where Timestamp > ago(7d)
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (p1)-[tx1]->(machine)<-[tx2]-(p2)
    where p1.PatronId != p2.PatronId
    and abs(datetime_diff('minute', tx1.Timestamp, tx2.Timestamp)) < 60
    and tx1.Amount > 2000
    and tx2.Amount > 2000
| summarize
    SharedMachines = dcount(machine.MachineId),
    Interactions = count(),
    CombinedAmount = sum(tx1.Amount) + sum(tx2.Amount)
    by p1.PatronId, p2.PatronId
| where Interactions >= 5
| order by CombinedAmount desc

// Detect suspicious employee-patron relationships
// Employees who authorize payouts for the same patron repeatedly
let PayoutAuthorizations = datatable(
    AuthId: string, EmployeeId: string, PatronId: string,
    PayoutAmount: decimal, Timestamp: datetime
) [/* populated from Eventstream */];
PayoutAuthorizations
| make-graph EmployeeId --> PatronId
    with Employees on EmployeeId, Patrons on PatronId
| graph-match (employee)-[auth]->(patron)
| summarize
    AuthorizationCount = count(),
    TotalPayout = sum(auth.PayoutAmount),
    AvgPayout = avg(auth.PayoutAmount)
    by employee.EmployeeId, employee.Name, employee.Role,
       patron.PatronId, patron.Name
| where AuthorizationCount >= 10
| extend SuspicionLevel = case(
    AuthorizationCount >= 20 and TotalPayout >= 50000, "Critical",
    AuthorizationCount >= 15, "High",
    "Medium")
| order by TotalPayout desc

// Identify patrons with frequent high-value jackpots across multiple machines
// W-2G threshold: $1,200 (slots), $600 (table games), $5,000 (poker)
Transactions
| where Amount >= 1200 and TransactionType == "jackpot"
| where Timestamp > ago(30d)
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron)-[tx]->(machine)
| summarize
    JackpotCount = count(),
    TotalWinnings = sum(tx.Amount),
    MachinesHit = dcount(machine.MachineId),
    Locations = make_set(machine.FloorLocation)
    by patron.PatronId, patron.Name
| where JackpotCount >= 5
| extend AnomalyScore = JackpotCount * MachinesHit
| order by AnomalyScore desc

// Node tables: Vendors, Agencies, Contracts
// Edge table: ContractAwards (Agency → Vendor)
ContractAwards
| make-graph AgencyId --> VendorId
    with Agencies on AgencyId, Vendors on VendorId
| graph-match (agency)-[c1]->(vendor1)-[sub]->(vendor2)<-[c2]-(agency)
    where vendor1.VendorId != vendor2.VendorId
    and c1.AwardAmount > 100000
| summarize
    SharedContracts = count(),
    TotalValue = sum(c1.AwardAmount)
    by vendor1.VendorName, vendor2.VendorName, agency.AgencyName
| where SharedContracts >= 3
| order by TotalValue desc

// Supply chain graph: Farm → Processor → Distributor → Retailer
SupplyChainShipments
| make-graph SourceFacilityId --> DestFacilityId
    with Facilities on FacilityId
| graph-shortest-paths (source)-[shipment*1..6]->(destination)
    where source.FacilityId == "FARM-12345"
    and source.FacilityType == "Farm"
| project
    PathLength = array_length(shipment),
    SourceFarm = source.FacilityName,
    Destination = destination.FacilityName,
    DestType = destination.FacilityType,
    Products = shipment[0].ProductType
| order by PathLength asc

// Map relationships between parent companies, facilities, and chemical releases
FacilityOwnership
| make-graph ParentCompanyId --> FacilityId
    with Companies on CompanyId, Facilities on FacilityId
| graph-match (company)-[owns]->(facility)
| summarize
    FacilityCount = dcount(facility.FacilityId),
    TotalReleases = sum(facility.AnnualReleaseLbs),
    States = make_set(facility.State)
    by company.CompanyName
| order by TotalReleases desc

// Create a KQL queryset that runs every 15 minutes
// Data Activator monitors the output and triggers when StructuringRisk == "Critical"
Transactions
| where Timestamp > ago(1h)
| where Amount between (8000.0 .. 9999.99)
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (patron)-[tx]->(machine)
| summarize TransactionCount = count(), TotalAmount = sum(tx.Amount)
    by patron.PatronId
| where TransactionCount >= 3 and TotalAmount >= 25000

// ❌ Unoptimized: Projects entire transaction history into graph
Transactions
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (p1)-[tx]->(m)
| where tx.Timestamp > ago(24h) and tx.Amount > 5000

// ✅ Optimized: Filter BEFORE graph projection
Transactions
| where Timestamp > ago(24h) and Amount > 5000
| make-graph PatronId --> MachineId with Patrons on PatronId, Machines on MachineId
| graph-match (p1)-[tx]->(m)