<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Deephaven Data Labs on Medium]]></title>
        <description><![CDATA[Stories by Deephaven Data Labs on Medium]]></description>
        <link>https://medium.com/@deephavendatalabs?source=rss-172b05621ec7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*8w7SjoZ933-SdsSVzyeeRw.png</url>
            <title>Stories by Deephaven Data Labs on Medium</title>
            <link>https://medium.com/@deephavendatalabs?source=rss-172b05621ec7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 16:01:17 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@deephavendatalabs/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[The median of medians is wrong: true medians in Deephaven rollups]]></title>
            <link>https://medium.com/@deephavendatalabs/the-median-of-medians-is-wrong-true-medians-in-deephaven-rollups-42fe310d6c2d?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/42fe310d6c2d</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 18:41:57 GMT</pubDate>
            <atom:updated>2026-11-06T18:41:57.823Z</atom:updated>
            <content:encoded><![CDATA[<h3>Formula aggregations bring median, percentile, and any custom statistic to every level of a rollup</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gm1icAy67RjgGDXZXggQ6w.png" /></figure><p>Your housing report has a rollup: country, then region, then state, then city. Every analyst on the team wants the same number at every level: the median sale price. Means are easy to roll up, but one $9 million mansion drags them upward, which is why housing data is reported as medians.</p><p>Deephaven 42 gives you that number. Put a formula aggregation like median(Price) in a rollup, and it runs against every sale under each node, so every level shows the true median. The rollup still updates in real time as new sales arrive.</p><p>That beats the usual shortcut, which is to take the median of each city and then the median of those. In this post’s example, the shortcut overstates the West’s median price by more than $300,000.</p><h3>Why a median doesn’t roll up</h3><p>A sum rolls up cleanly. The total for a region is the sum of its state totals, so the engine can compute each level from the level below. Counts, minimums, and maximums work the same way.</p><p>A median doesn’t. The median of a region depends on every sale in it, not on its states’ medians. Here is the region-level arithmetic for the three West cities in this post’s example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E6jmtl4YZYXDdHVAwD-y0g.png" /></figure><p>The median of those three medians is Seattle’s $856,000. But Fresno accounts for 1,500 of the West’s 2,700 sales, so the West’s real median sale is far lower: $554,500. The median of medians ignores how many sales sit behind each number.</p><p>This is also why agg.median and agg.pct raise not supported for rollup(). The built-in rollup aggregations compute each level from the one below, and a median can&#39;t be computed that way. Before version 42, a Deephaven rollup had no way to show a median. Formula aggregations take a different route, shown in the next section.</p><blockquote>A median depends on every value underneath it. You can’t build it from the medians of the groups below.</blockquote><h3>Medians and percentiles in a rollup</h3><p>The example generates 11,600 home sales across eight cities. Prices follow a log-normal distribution, the usual shape for housing prices: most sales cluster around the city’s typical price, with a long tail of expensive homes. The random seed is fixed, so you’ll see the same numbers this post reports.</p><pre>import numpy as np<br>from deephaven import new_table<br>from deephaven.column import string_col, double_col<br><br>rng = np.random.default_rng(42)<br><br># Region, state, city, number of sales, and typical price<br>markets = [<br>    (&quot;West&quot;, &quot;CA&quot;, &quot;San Francisco&quot;, 300, 1_350_000),<br>    (&quot;West&quot;, &quot;CA&quot;, &quot;Fresno&quot;, 1_500, 390_000),<br>    (&quot;West&quot;, &quot;WA&quot;, &quot;Seattle&quot;, 900, 850_000),<br>    (&quot;South&quot;, &quot;TX&quot;, &quot;Houston&quot;, 3_000, 330_000),<br>    (&quot;South&quot;, &quot;TX&quot;, &quot;Austin&quot;, 1_200, 520_000),<br>    (&quot;South&quot;, &quot;FL&quot;, &quot;Miami&quot;, 1_100, 610_000),<br>    (&quot;Midwest&quot;, &quot;IL&quot;, &quot;Chicago&quot;, 2_200, 340_000),<br>    (&quot;Midwest&quot;, &quot;OH&quot;, &quot;Columbus&quot;, 1_400, 290_000),<br>]<br><br>region, state, city, price = [], [], [], []<br>for r, s, c, n, typical in markets:<br>    prices = np.round(rng.lognormal(np.log(typical), 0.45, n), -3)<br>    region += [r] * n<br>    state += [s] * n<br>    city += [c] * n<br>    price += prices.tolist()<br><br>sales = new_table(<br>    [<br>        string_col(&quot;Region&quot;, region),<br>        string_col(&quot;State&quot;, state),<br>        string_col(&quot;City&quot;, city),<br>        double_col(&quot;Price&quot;, price),<br>    ]<br>)</pre><p>A <a href="https://deephaven.io/core/docs/reference/table-operations/group-and-aggregate/AggFormula/">formula aggregation</a> applies any query-language expression to the group of values under each key. In a <a href="https://deephaven.io/core/docs/reference/table-operations/create/rollup/">rollup</a>, it does that at every node, from each city up to the root. So median(Price) at the West node receives all 2,700 West sale prices, not three city medians.</p><pre>from deephaven import agg<br><br>home_prices = sales.rollup(<br>    aggs=[<br>        agg.count_(&quot;Sales&quot;),<br>        agg.avg(&quot;AvgPrice = Price&quot;),<br>        agg.formula(&quot;MedianPrice = median(Price)&quot;),<br>        agg.formula(&quot;P90Price = percentile(0.9, Price)&quot;),<br>    ],<br>    by=[&quot;Region&quot;, &quot;State&quot;, &quot;City&quot;],<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZBCBZZPW34uWdkgU.png" /></figure><p>Three things stand out:</p><ul><li><strong>The mean runs high everywhere.</strong> Nationally it’s $492,946 while the median is $398,000, because expensive homes stretch the tail.</li><li><strong>The West’s median is $554,500, not $856,000.</strong> Fresno’s 1,500 sales pull it down, as they should.</li><li><strong>The 90th percentile shows how far that tail reaches.</strong> One in ten West sales closed above $1.36 million.</li></ul><p>median and percentile are only two of the functions you can use. Any expression over the group&#39;s values works, so you can combine them: percentile(0.75, Price) - percentile(0.25, Price) gives the interquartile range, and percentile(0.9, Price) / median(Price) measures how far the high end stretches past a typical sale. Before version 42, a rollup had no way to compute either.</p><h3>Check the numbers yourself</h3><p>You don’t have to take the rollup’s word for it. A plain <a href="https://deephaven.io/core/docs/reference/table-operations/group-and-aggregate/aggBy/">agg_by</a> computes the median for a single level directly, and agg.median works there. This block computes the true regional medians and, for comparison, the median of the city medians:</p><pre>true_median = sales.agg_by([agg.median(&quot;TrueMedian = Price&quot;)], by=[&quot;Region&quot;])<br><br>city_medians = sales.agg_by([agg.median(&quot;CityMedian = Price&quot;)], by=[&quot;Region&quot;, &quot;City&quot;])<br>median_of_medians = city_medians.agg_by(<br>    [agg.median(&quot;MedianOfMedians = CityMedian&quot;)], by=[&quot;Region&quot;]<br>)<br><br>check = true_median.natural_join(median_of_medians, on=&quot;Region&quot;)</pre><p>The true medians match the rollup’s MedianPrice at each region. The shortcut misses by $301,500 in the West and $100,500 in the South. In the Midwest it happens to land close, which is exactly what makes it dangerous: it looks right often enough to go unnoticed.</p><h3>It keeps working as sales tick in</h3><p>A rollup over a ticking table updates in real time, and formula aggregations update with it. This block feeds the same eight markets with ten new sales per second:</p><pre>from deephaven import time_table, new_table, agg<br>from deephaven.column import int_col, string_col, double_col<br><br>market_info = new_table(<br>    [<br>        int_col(&quot;Market&quot;, list(range(8))),<br>        string_col(&quot;Region&quot;, [&quot;West&quot;] * 3 + [&quot;South&quot;] * 3 + [&quot;Midwest&quot;] * 2),<br>        string_col(&quot;State&quot;, [&quot;CA&quot;, &quot;CA&quot;, &quot;WA&quot;, &quot;TX&quot;, &quot;TX&quot;, &quot;FL&quot;, &quot;IL&quot;, &quot;OH&quot;]),<br>        string_col(<br>            &quot;City&quot;,<br>            [<br>                &quot;San Francisco&quot;,<br>                &quot;Fresno&quot;,<br>                &quot;Seattle&quot;,<br>                &quot;Houston&quot;,<br>                &quot;Austin&quot;,<br>                &quot;Miami&quot;,<br>                &quot;Chicago&quot;,<br>                &quot;Columbus&quot;,<br>            ],<br>        ),<br>        double_col(<br>            &quot;Typical&quot;,<br>            [1_350_000, 390_000, 850_000, 330_000, 520_000, 610_000, 340_000, 290_000],<br>        ),<br>    ]<br>)<br><br>live_sales = (<br>    time_table(&quot;PT0.1s&quot;)<br>    .update_view(&quot;Market = (int) (ii % 8)&quot;)<br>    .natural_join(market_info, on=&quot;Market&quot;)<br>    .update(<br>        &quot;Price = Math.round(Typical * Math.exp(0.45 * randomGaussian(0, 1)) / 1000) * 1000.0&quot;<br>    )<br>)<br><br>live_prices = live_sales.rollup(<br>    aggs=[<br>        agg.count_(&quot;Sales&quot;),<br>        agg.formula(&quot;MedianPrice = median(Price)&quot;),<br>        agg.formula(&quot;P90Price = percentile(0.9, Price)&quot;),<br>    ],<br>    by=[&quot;Region&quot;, &quot;State&quot;, &quot;City&quot;],<br>)</pre><p>Open live_prices in the IDE and expand a region. The counts climb, and the medians settle toward each market&#39;s typical price as sales accumulate.</p><h3>What it costs</h3><p>A median can’t be built from the level below, so the engine doesn’t try. At each node, the formula receives the full group of values beneath it, and the root node receives the whole table. When a sale arrives, Deephaven re-evaluates the formula for that sale’s city, state, region, and root.</p><p>For a median, that’s the price of a correct answer. For statistics that do roll up, such as sums and counts, use the built-in aggregations like agg.sum_ and agg.count_, which update each level from the one below. If you write a custom formula that also rolls up, pass reaggregating=True to agg.formula and the engine applies it to the child results instead of to every row. The <a href="https://deephaven.io/core/docs/reference/table-operations/create/rollup/#formula-aggregations-in-rollups">rollup reference</a> shows how.</p><h3>Building your own front end? Expand to any depth</h3><p>A rollup like home_prices opens fully collapsed, so a dashboard user sees one root row and has to click down to the regions they care about. Deephaven 42 adds expand-to-depth to the JavaScript API for hierarchical tables: instead of expanding nodes one at a time, a single call opens a node and a set number of levels below it. This opens the root plus regions and states, and leaves cities collapsed:</p><pre>const homePrices = await connection.getObject({<br>  type: &quot;HierarchicalTable&quot;,<br>  name: &quot;home_prices&quot;,<br>});<br><br>// Load the first rows so row 0 (the root) can be resolved<br>homePrices.setViewport(0, 99);<br>await homePrices.getViewportData();<br><br>// Expand the root row two levels: regions and their states<br>homePrices.expand(0, 2);</pre><p>Passing true instead of a number expands every level, the same as expandAll(). It&#39;s a good fit for dashboards that should always open at the same level of detail.</p><h3>Try it yourself</h3><p>Roll up your own data with true medians in minutes:</p><ul><li><a href="https://controller.try-dh.demo.community.deephaven.io/"><strong>Try Deephaven in your browser</strong></a> with no installation.</li><li><a href="https://deephaven.io/core/docs/getting-started/quickstart/"><strong>Install Deephaven</strong></a> locally. Formula aggregations in rollups require version 42 or later.</li><li><strong>Paste the examples above</strong> into the IDE, then swap in your own table and by columns.</li><li><strong>Read the </strong><a href="https://deephaven.io/core/docs/reference/table-operations/create/rollup/"><strong>rollup reference</strong></a> for formula reaggregation and the __FORMULA_DEPTH__ and __FORMULA_KEYS__ variables, which let a formula behave differently at each level.</li></ul><p>The next time a summary table shows you a median, check where it came from. If you have questions or want to show us what you’re rolling up, join us on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=42fe310d6c2d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Rerouting before the rain]]></title>
            <link>https://medium.com/@deephavendatalabs/rerouting-before-the-rain-ed336dd7c700?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/ed336dd7c700</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Mon, 14 Sep 2026 23:31:31 GMT</pubDate>
            <atom:updated>2026-09-14T23:31:31.059Z</atom:updated>
            <content:encoded><![CDATA[<h3>Building a real-time fleet monitoring dashboard for hurricane season</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MfVNlNmOapM5mE5R5P8_9A.png" /></figure><p>In late September 2024, Hurricane Helene washed out I-40 at the North Carolina-Tennessee border. Within days, the state DOT issued an unusual warning to truckers: stop using Google Maps, Waze, and Apple Maps. The consumer navigation apps kept sending large trucks down narrow mountain detours built for cars, not 40-ton rigs. Trucks got stuck, flipped, or slid off the road trying to follow them.</p><p>The problem wasn’t bad data — the closures and approved truck detours were posted on the DOT’s own site. The problem was that the apps’ routing logic doesn’t reason about truck size or restrictions that are still evolving. It optimizes for the fastest path for a car and leaves the driver to discover the mismatch the hard way.</p><p>Fleet operations teams face the same gap. GPS pings stream in continuously, weather advisories update as conditions change, but the dashboards that connect them still refresh on a schedule — hourly, sometimes less. You get a heatmap of where trucks <em>were</em>. During a hurricane, you need to know where they <em>will be</em>, and whether that’s a problem.</p><p>Hurricanes make the stakes obvious, but the underlying problem shows up anywhere conditions change faster than your analytics refresh: port congestion, traffic incidents, shifting customer demand. Deephaven handles this by joining multiple live feeds, updating incrementally as data arrives, and letting you query real-time and historical data with the same code.</p><blockquote>The hurricane doesn’t wait. Your fleet system shouldn’t either.</blockquote><h3>The hourly refresh problem</h3><p>Most fleet management systems treat real-time data the way a newspaper treats breaking news — they collect it all day and publish once. GPS pings arrive every 30 seconds, weather advisories update as conditions change, and traffic incidents appear and clear throughout the day. But the dashboard that’s supposed to synthesize all of this refreshes on a schedule, and schedules don’t care about hurricanes.</p><p>This isn’t a knock on the tools — real-time data isn’t what they were built for. A major fleet telematics vendor documents GPS data refreshing every three hours in their analytics warehouse, with driver aggregates updating once a day. Power BI Pro allows eight scheduled refreshes daily; even Premium caps at 48. These systems were designed for “how did last month look?” not “the road just washed out.”</p><p>Custom alerting systems can catch specific patterns — “alert if truck enters polygon” — but hurricanes don’t respect static polygons. The track shifts, flooding happens where the NHC didn’t predict, and the fuel station you were counting on loses power. Every new scenario requires a new rule, a new deployment, and another wait.</p><blockquote>Schedules don’t care about hurricanes.</blockquote><p>What fleet operations actually needs is both: real-time detection that runs as fast as the data arrives, and the flexibility to ask new questions without filing a development request.</p><h3>Joining weather, trucks, and trouble</h3><p>This is what real-time fleet awareness looks like:</p><pre>from deephaven import time_table<br><br># Simulated fleet GPS pings - in production, streams from telematics provider<br>fleet_positions = (<br>    time_table(&quot;PT5S&quot;)<br>    .update(<br>        [<br>            &quot;TruckID = `TRK` + String.format(`%04d`, randomInt(1, 200))&quot;,<br>            &quot;Lat = 32.5 + randomDouble(-2.0, 2.0)&quot;,  # Southeast US region<br>            &quot;Lon = -80.0 + randomDouble(-3.0, 3.0)&quot;,<br>            &quot;Speed = randomInt(0, 75)&quot;,<br>            &quot;Heading = randomInt(0, 360)&quot;,<br>            &quot;DestinationCity = (String) new String[] {`Charleston`, `Savannah`, `Jacksonville`, `Wilmington`}[randomInt(0, 4)]&quot;,<br>        ]<br>    )<br>    .last_by(&quot;TruckID&quot;)  # Keep only the latest position per truck<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/570/0*KQ2SNeoyP4ra7mnV.png" /></figure><p><em>(Screenshots below are a single frozen moment of a table that’s continuously updating — run the code yourself and you’ll see different trucks, positions, and timestamps.)</em></p><p>Weather advisories stream in as polygons. GPS pings stream in as truck positions. Road closures and traffic incidents stream in as they’re reported. One ticking table joins them all.</p><pre># Simulated weather advisory zones<br>weather_zones = (<br>    time_table(&quot;PT60S&quot;)<br>    .update(<br>        [<br>            &quot;ZoneID = `ZONE` + String.format(`%02d`, randomInt(1, 10))&quot;,<br>            &quot;CenterLat = 32.8 + randomDouble(-1.0, 1.0)&quot;,<br>            &quot;CenterLon = -79.5 + randomDouble(-1.5, 1.5)&quot;,<br>            &quot;Radius = randomDouble(0.3, 1.2)&quot;,  # degrees<br>            &quot;Severity = (String) new String[] {`WATCH`, `WARNING`, `EMERGENCY`}[randomInt(0, 3)]&quot;,<br>        ]<br>    )<br>    .last_by(&quot;ZoneID&quot;)<br>    .drop_columns(&quot;Timestamp&quot;)  # Avoid conflict when joining with fleet_positions<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/0*4V5c17vofJSN4hFH.png" /></figure><p>Now the join that matters — which trucks are heading toward trouble. Both sides are already reduced to one row per key (last_by(&quot;TruckID&quot;), last_by(&quot;ZoneID&quot;)), so the cross join stays bounded — around 200 trucks × 10 zones, not an ever-growing pair count. A fleet large enough to need a bucketed or spatial join first would still update the same way; only the candidate-narrowing step changes:</p><pre># Calculate each truck&#39;s distance and bearing to each weather zone center<br># Flag trucks within zone radius, or outside it but heading toward the zone<br>trucks_at_risk = (<br>    fleet_positions.join(<br>        weather_zones, on=[]<br>    )  # Cross join: evaluate every truck vs every zone<br>    .update_view(<br>        [<br>            &quot;DistToZone = Math.sqrt(Math.pow(Lat - CenterLat, 2) + Math.pow(Lon - CenterLon, 2))&quot;,<br>            &quot;BearingToZone = (Math.toDegrees(Math.atan2(CenterLon - Lon, CenterLat - Lat)) + 360) % 360&quot;,<br>            &quot;HeadingDiff = 180 - Math.abs(Math.abs(Heading - BearingToZone) - 180)&quot;,<br>            &quot;InZone = DistToZone &lt; Radius&quot;,<br>            &quot;ApproachingZone = DistToZone &lt; Radius * 2 &amp;&amp; DistToZone &gt; Radius &amp;&amp; HeadingDiff &lt; 45&quot;,<br>        ]<br>    )<br>    .where(&quot;InZone || ApproachingZone&quot;)<br>    .view(<br>        [<br>            &quot;Timestamp&quot;,<br>            &quot;TruckID&quot;,<br>            &quot;DestinationCity&quot;,<br>            &quot;ZoneID&quot;,<br>            &quot;Severity&quot;,<br>            &quot;DistToZone&quot;,<br>            &quot;InZone&quot;,<br>            &quot;ApproachingZone&quot;,<br>        ]<br>    )<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/0*AOBTTVd6DW6DR9e5.png" /></figure><p>Every time the weather service updates a zone (shifts the center, expands the radius, or raises the severity), every truck’s risk status recalculates. Not at the next hourly refresh. Immediately.</p><h3>From detection to decision</h3><p>Knowing which trucks are at risk is step one. The dispatcher needs options. For each at-risk truck, what are the alternatives?</p><pre># Reroute recommendations based on current conditions<br>reroute_options = trucks_at_risk.update_view(<br>    [<br>        # Simplified reroute logic - production version queries routing API<br>        &quot;DelayIfContinue = Severity == `EMERGENCY` ? 999 : Severity == `WARNING` ? 180 : 60&quot;,<br>        &quot;AltRouteAvailable = DistToZone &gt; 0.5&quot;,<br>        &quot;EstDetourMinutes = AltRouteAvailable ? (int)(DistToZone * 90) : 0&quot;,<br>        &quot;Recommendation = Severity == `EMERGENCY` ? `HOLD` : AltRouteAvailable ? `REROUTE` : `MONITOR`&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Qf3q6Cke0yBUo33B.png" /></figure><p>This isn’t a black box. The dispatcher can filter, sort, and override. “Show me all trucks heading to Charleston, sorted by detour time.” “Which WARNING-zone trucks have less than 2 hours of drive time remaining?” The queries run against ticking data — the answers update as conditions change.</p><blockquote>The dispatcher makes decisions. The system makes sure they’re informed.</blockquote><h3>The same code, the day after</h3><p>Storm passes. The question shifts from “who’s at risk right now?” to “which shipments carried the most risk?”</p><p>DelayIfContinue was always a projected cost, not a measured one — how bad continuing would have been, based on the zone&#39;s severity at that moment. The debrief query doesn&#39;t change that; it just re-runs the same where clause to pull the high-projected-risk rows for review. Nothing about the code changes if you point it at a static historical table instead of a ticking one:</p><pre># Debrief: which shipments carried the highest projected delay risk<br># Same where clause the dispatcher ran live, run again for the record<br>delayed_analysis = reroute_options.where(&quot;DelayIfContinue &gt; 60&quot;).view(<br>    [<br>        &quot;Timestamp&quot;,<br>        &quot;TruckID&quot;,<br>        &quot;DestinationCity&quot;,<br>        &quot;Severity&quot;,<br>        &quot;DelayIfContinue&quot;,<br>        &quot;Recommendation&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/0*9HjwYmjwRHXLz13_.png" /></figure><p>The analyst running the post-storm debrief uses the same syntax the dispatcher used during the storm. Point the query at yesterday’s data instead of the live feed, and everything else works the same way.</p><h3>The cost of an hour</h3><p>Hurricane season runs June through November. A typical logistics company might face three or four significant rerouting events per year. Each one is a test: does your system move as fast as the storm?</p><p>An hourly refresh means the dispatcher is always behind. The driver calls in asking about conditions the system hasn’t noticed yet. The reroute decision happens after trucks are already committed to bad routes. The fuel stop that lost power three hours ago is still showing as available.</p><p>Real-time awareness doesn’t prevent hurricanes. But it closes the gap between what the driver knows and what dispatch knows — and that gap is where delays, safety incidents, and customer complaints live.</p><h3>Try it yourself</h3><p>Run the fleet monitoring example in your browser with <a href="https://controller.try-dh.demo.community.deephaven.io/">Deephaven’s live demo</a>. To deploy in your own environment, follow the <a href="https://deephaven.io/core/docs/getting-started/quickstart/">quickstart guide</a>. Swap the simulated GPS feed for your telematics provider’s API, and the weather simulation for NWS alerts — the downstream logic works identically.</p><p>Questions about logistics use cases? Join the conversation on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ed336dd7c700" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-time intrusion detection: flag attacks in milliseconds, not minutes]]></title>
            <link>https://medium.com/@deephavendatalabs/real-time-intrusion-detection-flag-attacks-in-milliseconds-not-minutes-749f02a3ae77?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/749f02a3ae77</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[cybersecurity]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[python-programming]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Thu, 30 Jul 2026 17:31:30 GMT</pubDate>
            <atom:updated>2026-07-30T17:31:30.197Z</atom:updated>
            <content:encoded><![CDATA[<h3>Build a streaming intrusion detection system in Deephaven that computes behavioral baselines and flags anomalies as each flow arrives — port scans, beaconing, and exfiltration detected before attackers move laterally.</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YvHRrL8QL_uYyqmh8kIYpg.png" /></figure><p>Your security alert fires — 47 minutes after the attacker already broke into three servers. By the time you see it, they’re long gone.</p><p>Most security tools check logs on a schedule. Whether checks run every 5 minutes or every hour, the delay in between is a window of opportunity for hackers. A real-time system watches every network connection as it happens and flags suspicious activity immediately.</p><p>This post builds a live intrusion detection system in Deephaven. You’ll simulate network traffic, track what “normal” looks like for each device, and detect three attack patterns — all updating continuously as data arrives.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*McgVO0dL8qWNG83r.png" /></figure><h3>The data: network flow records</h3><p>Network flow data (NetFlow, IPFIX, sFlow) summarizes connections without storing full packet payloads. Each record captures a source IP, destination IP, ports, protocol, byte counts, and timestamps. That’s enough for traffic analysis at scale.</p><p>Note: The code in this post runs in a Deephaven console. If you don’t have Deephaven running yet, see the <a href="https://deephaven.io/core/docs/getting-started/quickstart/">quickstart guide</a> — Docker or pip, under five minutes.</p><p><em>The simulation below includes intentional attack patterns — port scanning, beaconing, and data exfiltration — so you can see the detectors populate with meaningful results. In production, you’d connect to real NetFlow sources instead.</em></p><p>Let’s simulate a flow stream. The <a href="https://deephaven.io/core/docs/reference/table-operations/create/timeTable/">time_table</a> function creates a ticking table that emits a new row at each interval — here, every 100ms. The chained <a href="https://deephaven.io/core/docs/reference/table-operations/select/update/">update</a> call computes columns for each row using formulas written in Deephaven&#39;s query language:</p><pre>from deephaven import time_table<br>from deephaven.updateby import rolling_count_tick<br><br># Simulate network flows arriving every 100ms<br>flows = time_table(&quot;PT0.1S&quot;).update(<br>    [<br>        # Normal traffic, port-scanning IPs, and one beaconing host<br>        &quot;SourceIP = (ii % 50 == 0) ? `192.168.1.201` : (ii % 20 &lt; 2) ? `192.168.1.` + (int)(random() * 5 + 202) : `192.168.1.` + (int)(random() * 50 + 1)&quot;,<br>        &quot;DestIP = `10.0.0.` + (int)(random() * 20 + 1)&quot;,<br>        # Port scan pattern: suspicious IPs hit sequential ports<br>        &quot;DestPort = (SourceIP.startsWith(`192.168.1.20`)) ? (int)(ii % 1024) : (int)(randomInt(80, 443))&quot;,<br>        &quot;SourcePort = (int)(randomInt(49152, 65535))&quot;,<br>        # Beaconing pattern: regular intervals from compromised host<br>        &quot;Protocol = (ii % 50 == 0 &amp;&amp; SourceIP.equals(`192.168.1.201`)) ? `DNS` : `TCP`&quot;,<br>        # Exfiltration pattern: suspicious IPs send large volumes<br>        &quot;BytesSent = (SourceIP.startsWith(`192.168.1.20`)) ? (long)(randomInt(500000, 2000000)) : (long)(randomInt(100, 5000))&quot;,<br>        &quot;BytesReceived = (long)(randomInt(100, 10000))&quot;,<br>        &quot;PacketCount = (int)(randomInt(1, 100))&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/862/0*hvJ-L1LJAkAUiarn.png" /></figure><p>This creates a ticking table with ~10 flows per second. The simulated attackers (192.168.1.201-206) exhibit port scanning, beaconing, and large outbound transfers. Give the simulation 3-5 minutes to accumulate enough data for the detectors to populate.</p><blockquote>Every flow carries its own context: how does this transfer compare to what’s normal for this source?</blockquote><h3>Establishing baselines with rolling statistics</h3><p>Anomaly detection requires knowing what’s normal. Most streaming platforms make you choose: either recompute statistics from scratch on every update (slow), or maintain complex state management code (error-prone). Deephaven’s <a href="https://deephaven.io/core/docs/reference/table-operations/update-by-operations/updateBy/">update_by</a> operations handle this natively — rolling windows update incrementally as each row arrives, with no extra code and no performance penalty.</p><pre>from deephaven.updateby import rolling_avg_tick, rolling_std_tick<br><br># Per-source rolling statistics over last 100 flows<br>baselines = flows.update_by(<br>    ops=[<br>        rolling_avg_tick(cols=[&quot;AvgBytes = BytesSent&quot;], rev_ticks=100),<br>        rolling_std_tick(cols=[&quot;StdBytes = BytesSent&quot;], rev_ticks=100),<br>        rolling_count_tick(cols=[&quot;FlowCount = SourceIP&quot;], rev_ticks=100),<br>    ],<br>    by=[&quot;SourceIP&quot;],<br>).update_view(<br>    [<br>        # Z-score: how many standard deviations from mean<br>        &quot;BytesZScore = StdBytes &gt; 0 ? (BytesSent - AvgBytes) / StdBytes : 0&quot;,<br>        # Flag statistical outliers<br>        &quot;VolumeAnomaly = abs(BytesZScore) &gt; 3&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*17Rk58KWdnNZfuKPTOj0WQ.png" /></figure><p>Every incoming flow now carries its context: how does this transfer compare to this source’s recent behavior? A Z-score above 3 means this flow is more than three standard deviations from the source’s norm — statistically rare and worth investigating.</p><p>Notice what’s <em>not</em> here: no windowing boilerplate, no state checkpointing, no manual memory management. The engine handles incremental updates internally. When a new flow arrives, only the affected rolling windows recompute.</p><blockquote>You write the detection logic. The engine handles the state.</blockquote><h3>Detecting attack patterns</h3><p>With baselines established, we can layer on detection rules for specific attack signatures.</p><h4>Port scan detection</h4><p>Port scans probe many ports on a target in rapid succession. The <a href="https://deephaven.io/core/docs/reference/table-operations/group-and-aggregate/aggBy/">agg_by</a> operation groups rows by SourceIP and computes aggregations — here, counting distinct ports and total flows. The <a href="https://deephaven.io/core/docs/reference/table-operations/filter/where/">where</a> filter keeps only sources hitting many ports, and <a href="https://deephaven.io/core/docs/reference/table-operations/select/update-view/">update_view</a> adds classification columns:</p><pre>from deephaven import agg<br><br># Aggregate distinct ports per source<br>port_scan_candidates = (<br>    flows.agg_by(<br>        aggs=[<br>            agg.count_distinct(cols=[&quot;UniquePortsHit = DestPort&quot;]),<br>            agg.count_(col=&quot;FlowCount&quot;),<br>        ],<br>        by=[&quot;SourceIP&quot;],<br>    )<br>    .where(<br>        [<br>            &quot;UniquePortsHit &gt; 20&quot;,  # Hitting many different ports<br>            &quot;FlowCount &gt; 30&quot;,  # With sustained activity<br>        ]<br>    )<br>    .update_view(<br>        [<br>            &quot;ThreatType = `PORT_SCAN`&quot;,<br>            &quot;Severity = UniquePortsHit &gt; 100 ? `HIGH` : `MEDIUM`&quot;,<br>        ]<br>    )<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/460/0*yRJXfvOMsvjGaJxG.png" /></figure><h4>Beaconing detection</h4><p>Command-and-control malware often “phones home” at regular intervals. We detect this using <a href="https://deephaven.io/core/docs/reference/table-operations/update-by-operations/delta/">delta</a>, which computes the difference between consecutive values — here, the time gap between each flow from the same source. Then we aggregate those gaps to find sources with suspiciously consistent timing:</p><pre>from deephaven.updateby import delta<br><br># Calculate inter-flow timing per source<br>beacon_analysis = flows.update_by(<br>    ops=[delta(cols=[&quot;TimeDelta = Timestamp&quot;])], by=[&quot;SourceIP&quot;]<br>).update_view(<br>    [&quot;DeltaSeconds = TimeDelta == null ? 0.0 : (double)TimeDelta / 1_000_000_000&quot;]<br>)<br><br># Sources with very consistent timing are suspicious (exclude first row per source)<br>beacon_candidates = (<br>    beacon_analysis.where(&quot;TimeDelta != null&quot;)<br>    .agg_by(<br>        aggs=[<br>            agg.avg(cols=[&quot;AvgInterval = DeltaSeconds&quot;]),<br>            agg.std(cols=[&quot;StdInterval = DeltaSeconds&quot;]),<br>            agg.count_(col=&quot;FlowCount&quot;),<br>        ],<br>        by=[&quot;SourceIP&quot;],<br>    )<br>    .where(<br>        [<br>            &quot;StdInterval &lt; 5.0&quot;,  # Consistent timing<br>            &quot;AvgInterval &gt; 0.1&quot;,  # Not just burst traffic<br>            &quot;FlowCount &gt; 5&quot;,  # Sustained pattern<br>        ]<br>    )<br>    .update_view(<br>        [&quot;ThreatType = `BEACONING`&quot;, &quot;Severity = StdInterval &lt; 2.0 ? `HIGH` : `MEDIUM`&quot;]<br>    )<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NgorWBNwiYEE7-p1.png" /></figure><h4>Data exfiltration detection</h4><p>Large outbound transfers may indicate data theft. This query filters for flows exceeding 100KB, then aggregates by source-destination pair to find sustained large transfers:</p><pre># Flag high-volume outbound transfers<br>exfil_candidates = (<br>    baselines.where(&quot;BytesSent &gt; 100000&quot;)  # Large single transfer<br>    .agg_by(<br>        aggs=[<br>            agg.sum_(cols=[&quot;TotalBytes = BytesSent&quot;]),<br>            agg.count_(col=&quot;FlowCount&quot;),<br>            agg.max_(cols=[&quot;PeakTransfer = BytesSent&quot;]),<br>        ],<br>        by=[&quot;SourceIP&quot;, &quot;DestIP&quot;],<br>    )<br>    .where(<br>        [<br>            &quot;TotalBytes &gt; 1000000&quot;  # 1MB+ aggregate<br>        ]<br>    )<br>    .update_view(<br>        [<br>            &quot;ThreatType = `DATA_EXFIL`&quot;,<br>            &quot;Severity = TotalBytes &gt; 3000000 ? `CRITICAL` : `HIGH`&quot;,<br>        ]<br>    )<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/620/0*HiMFOtV-cAHrfRgK.png" /></figure><h3>Unified alert stream</h3><p>The <a href="https://deephaven.io/core/docs/reference/table-operations/merge/merge/">merge</a> operation combines multiple tables with the same schema into one. We extract matching columns from each detector, merge them, add a priority score, and <a href="https://deephaven.io/core/docs/reference/table-operations/sort/sort/">sort</a> to surface critical alerts first:</p><pre>from deephaven import merge<br><br># Merge all threat detections<br>all_alerts = merge(<br>    [<br>        port_scan_candidates.view([&quot;SourceIP&quot;, &quot;ThreatType&quot;, &quot;Severity&quot;, &quot;FlowCount&quot;]),<br>        beacon_candidates.view([&quot;SourceIP&quot;, &quot;ThreatType&quot;, &quot;Severity&quot;, &quot;FlowCount&quot;]),<br>        exfil_candidates.view([&quot;SourceIP&quot;, &quot;ThreatType&quot;, &quot;Severity&quot;, &quot;FlowCount&quot;]),<br>    ]<br>)<br><br># Priority scoring for analyst triage<br>prioritized_alerts = all_alerts.update_view(<br>    [&quot;Priority = Severity == `CRITICAL` ? 1 : Severity == `HIGH` ? 2 : 3&quot;]<br>).sort([&quot;Priority&quot;, &quot;FlowCount&quot;])</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/842/0*HWhgF7_f0AESne7X.png" /></figure><p>For a real analyst workstation, color-coded severity makes triage faster. The <a href="https://deephaven.io/core/docs/how-to-guides/deephaven-ui/">deephaven.ui</a> library adds conditional formatting:</p><pre>from deephaven import ui<br><br>beacon_view = ui.table(<br>    beacon_candidates,<br>    format_=[<br>        ui.TableFormat(<br>            if_=&quot;Severity == `HIGH`&quot;, background_color=&quot;firebrick&quot;, color=&quot;white&quot;<br>        ),<br>        ui.TableFormat(if_=&quot;Severity == `MEDIUM`&quot;, background_color=&quot;goldenrod&quot;),<br>    ],<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/0*SWn2CY7XHruHQWtY.png" /></figure><p>The true beaconing host (192.168.1.201, with near-zero timing variance) shows red; other candidates with higher variance show gold.</p><h3>Threat dashboard</h3><p>Detection logic is only half the story. Analysts need to <em>see</em> what’s happening — and that’s where <a href="https://deephaven.io/core/docs/how-to-guides/deephaven-ui/">deephaven.ui</a> transforms your Python scripts into production dashboards.</p><p>You stay in Python — the same language you used for detection logic — and Deephaven renders a live, interactive dashboard that updates as your tables tick:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_9j5rr11bfwnf1tG9-wDrQ.png" /></figure><p>Every panel in this dashboard updates in real-time. The KPI counts tick up as new alerts arrive. The triage queue reorders as priorities change. The color-coded severity formatting — CRITICAL in dark red, HIGH in pink, MEDIUM in gold, LOW in teal — makes pattern recognition instant.</p><p>The dashboard includes:</p><ul><li><strong>Overview KPI bar</strong>: Open alerts, critical/high counts, hosts involved</li><li><strong>Tabbed detector views</strong>: Switch between prioritized alerts and individual detector outputs</li><li><strong>Triage queue</strong>: Filtered to CRITICAL and HIGH severity for immediate attention</li><li><strong>Summary panels</strong>: Alerts by threat type and top talkers by flow volume</li></ul><p>Here&#39;s a reference layout using deephaven.ui. This snippet shows a more complete dashboard than the tables we&#39;ve built — including KPI summaries, triage queues, and aggregations by threat type. Use it as a template for your own dashboards:</p><pre>from deephaven import ui<br><br># Severity-based conditional formatting<br>SEVERITY_FORMAT = [<br>    ui.TableFormat(<br>        if_=&quot;Severity == `CRITICAL`&quot;, background_color=&quot;#7a0d28&quot;, color=&quot;white&quot;<br>    ),<br>    ui.TableFormat(if_=&quot;Severity == `HIGH`&quot;, background_color=&quot;#c2254c&quot;, color=&quot;white&quot;),<br>    ui.TableFormat(<br>        if_=&quot;Severity == `MEDIUM`&quot;, background_color=&quot;#997915&quot;, color=&quot;#0b0f28&quot;<br>    ),<br>    ui.TableFormat(<br>        if_=&quot;Severity == `LOW`&quot;, background_color=&quot;#20454f&quot;, color=&quot;#c8f4ff&quot;<br>    ),<br>    ui.TableFormat(cols=&quot;FlowCount&quot;, value=&quot;#,##0&quot;),<br>]<br><br># For tables using WorstPriority instead of Severity<br>PRIORITY_FORMAT = [<br>    ui.TableFormat(if_=&quot;WorstPriority == 1&quot;, background_color=&quot;#7a0d28&quot;, color=&quot;white&quot;),<br>    ui.TableFormat(if_=&quot;WorstPriority == 2&quot;, background_color=&quot;#c2254c&quot;, color=&quot;white&quot;),<br>    ui.TableFormat(<br>        if_=&quot;WorstPriority == 3&quot;, background_color=&quot;#997915&quot;, color=&quot;#0b0f28&quot;<br>    ),<br>    ui.TableFormat(<br>        if_=&quot;WorstPriority == 4&quot;, background_color=&quot;#20454f&quot;, color=&quot;#c8f4ff&quot;<br>    ),<br>    ui.TableFormat(cols=&quot;Flows&quot;, value=&quot;#,##0&quot;),<br>]<br><br># Table views with formatting<br>alerts_view = ui.table(prioritized_alerts, format_=SEVERITY_FORMAT)<br>beacon_view = ui.table(beacon_candidates, format_=SEVERITY_FORMAT)<br>exfil_view = ui.table(exfil_candidates, format_=SEVERITY_FORMAT)<br>scan_view = ui.table(port_scan_candidates, format_=SEVERITY_FORMAT)<br>type_view = ui.table(by_threat_type, format_=PRIORITY_FORMAT)<br>talkers_view = ui.table(top_talkers, format_=PRIORITY_FORMAT)<br>triage_view = ui.table(triage_queue, format_=SEVERITY_FORMAT)<br>kpi_view = ui.table(kpi_table)<br><br># Dashboard layout<br>threat_dashboard = ui.dashboard(<br>    ui.column(<br>        ui.row(ui.panel(kpi_view, title=&quot;Overview&quot;), height=1),<br>        ui.row(<br>            ui.stack(<br>                ui.panel(alerts_view, title=&quot;prioritized_alerts&quot;),<br>                ui.panel(beacon_view, title=&quot;beacon_view&quot;),<br>                ui.panel(exfil_view, title=&quot;exfil_view&quot;),<br>                ui.panel(scan_view, title=&quot;scan_view&quot;),<br>                width=50,<br>            ),<br>            ui.column(<br>                ui.panel(triage_view, title=&quot;triage_queue&quot;),<br>                ui.panel(talkers_view, title=&quot;top_talkers&quot;),<br>                width=25,<br>            ),<br>            ui.column(<br>                ui.panel(type_view, title=&quot;by_threat_type&quot;),<br>                width=25,<br>            ),<br>            height=5,<br>        ),<br>    )<br>)</pre><p>The ui.dashboard arranges panels in rows and columns with relative sizing. Wrap multiple panels in ui.stack to create tabbed views. The width and height parameters control relative proportions — here, 50/25/25 for a three-column layout.</p><p>The same Python code that defines your detection logic also defines your dashboard. Change a query, and the visualization updates automatically.</p><h3>Adding threat intelligence</h3><p>Real-world IDS systems correlate against known-bad indicators. The <a href="http://localhost:3001/core/docs/reference/table-operations/join/natural-join/">natural_join</a> operation matches each flow&#39;s destination IP against a threat intelligence table, pulling in threat metadata for any matches:</p><pre>from deephaven import new_table<br>from deephaven.column import string_col<br><br># Simulated threat intelligence feed<br>threat_intel = new_table(<br>    [<br>        string_col(&quot;MaliciousIP&quot;, [&quot;10.0.0.5&quot;, &quot;10.0.0.12&quot;, &quot;10.0.0.18&quot;]),<br>        string_col(&quot;ThreatCategory&quot;, [&quot;C2_Server&quot;, &quot;APT29&quot;, &quot;Ransomware&quot;]),<br>        string_col(&quot;Confidence&quot;, [&quot;HIGH&quot;, &quot;MEDIUM&quot;, &quot;HIGH&quot;]),<br>    ]<br>)<br><br># Enrich flows with threat intel matches<br>enriched_flows = flows.natural_join(<br>    threat_intel, on=[&quot;DestIP = MaliciousIP&quot;], joins=[&quot;ThreatCategory&quot;, &quot;Confidence&quot;]<br>).where(&quot;!isNull(ThreatCategory)&quot;)</pre><p>Now any flow to a known-malicious IP is flagged instantly with context about the threat actor.</p><blockquote>When the threat feed updates, every downstream table — alerts, dashboards, escalations — updates automatically.</blockquote><h3>Why streaming beats batch</h3><p>Traditional SIEMs run detection queries on a schedule — every 5 minutes, every hour. That lag creates a window where attackers operate undetected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f5Fgm0DUoAdSqr_OrHDzYg.png" /></figure><p>But “streaming” alone isn’t the differentiator. Kafka, Flink, and Spark Streaming all process data continuously. The difference is <em>how</em> you build detection logic:</p><ul><li><strong>Flink/Spark</strong>: Write Java or Scala, manage state explicitly, deploy compiled jobs. Iteration cycles measured in hours.</li><li><strong>kdb+/q</strong>: Powerful but specialized syntax with a steep learning curve. Limited Python ecosystem integration.</li><li><strong>Custom pipelines</strong>: Glue together Kafka, Redis, and a rules engine. Maintain three systems instead of one.</li><li><strong>Deephaven</strong>: Write Python. Tables update incrementally — rolling statistics, joins, and aggregations are just table operations. Change a threshold, see results immediately. The same code runs on historical data for backtesting and live feeds for production.</li></ul><p>That last point matters for security teams: you can develop and tune detection rules against last month’s logs, then deploy the identical queries against live traffic. No rewrite, no translation layer.</p><h3>Try it yourself</h3><p>The full notebook runs in any Deephaven instance. <a href="https://controller.try-dh.demo.community.deephaven.io/">Launch our free demo sandbox</a> (no install) or <a href="http://localhost:3001/core/docs/getting-started/quickstart/">install Deephaven locally</a>, paste the code blocks above, and watch the alerts tick in as simulated attacks unfold.</p><p><a href="https://controller.try-dh.demo.community.deephaven.io/">Deephaven Data Labs Demo System</a></p><p>For production deployments:</p><ul><li>Replace the simulated time_table with your actual NetFlow source (Kafka, syslog, database subscription).</li><li>Tune detection thresholds based on your network’s baseline.</li><li>Add <a href="http://localhost:3001/core/docs/how-to-guides/deephaven-ui/">deephaven.ui</a> dashboards for analyst workstations.</li></ul><p>The detection logic stays the same whether you’re processing 100 flows per second in a lab or 100,000 per second in a SOC — and unlike micro-batch systems, latency doesn’t degrade as volume increases.</p><p>Have questions or want to share what you’re building? Join the conversation on <a href="http://localhost:3001/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=749f02a3ae77" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-time market surveillance for regulatory compliance]]></title>
            <link>https://medium.com/@deephavendatalabs/real-time-market-surveillance-for-regulatory-compliance-c308afb4fa36?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/c308afb4fa36</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[stock-market]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Tue, 28 Jul 2026 15:25:57 GMT</pubDate>
            <atom:updated>2026-07-28T15:25:57.719Z</atom:updated>
            <content:encoded><![CDATA[<h3>When yesterday’s batch report isn’t fast enough</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5HVjENFc_LISC_CM.png" /></figure><p>Consider a scenario: At 10:42 AM on a Tuesday, an algorithm starts placing 50,000-share orders on AAPL and canceling them 80 milliseconds later. By 10:43, it has done this 247 times. By 10:44, other traders have moved their prices in response to what looks like massive buying pressure. By 10:45, the algorithm is gone, and someone has made money on the price move it manufactured.</p><p>The surveillance analyst reviewing this activity sees it at 8:15 AM the next morning, in a batch report that flags “unusual cancel rates.” By then, the pattern has repeated on three other symbols, the profits have been extracted, and the account has gone quiet.</p><p>This is the asymmetry that keeps federal regulators up at night: the markets move in microseconds, but surveillance infrastructure often moves in hours.</p><blockquote>The manipulation is already profitable before the alert even generates.</blockquote><h3>The 8:15 AM problem</h3><p>That analyst isn’t incompetent, and the surveillance system isn’t broken. It’s working exactly as designed — collecting order data throughout the day, running detection algorithms overnight, and surfacing alerts the next morning.</p><p>This architecture made sense when markets were slower and manipulation was cruder, but spoofing algorithms now operate faster than batch systems can detect — and they’re adaptive. If the same pattern triggers an enforcement action, the algorithm changes. It spreads activity across multiple accounts, or shifts to different time windows, or moves to a different venue. The surveillance system keeps looking for yesterday’s pattern while today’s variation runs unchecked.</p><p>The alternative — complex event processing systems — can detect patterns in real-time, but they’re rigid. They catch what they’re programmed to catch. When the analyst wants to ask “show me all orders from this account that were cancelled within 100 milliseconds of a price move,” the answer is usually “submit a development request and wait six weeks.”</p><p>What surveillance actually needs is both: detection that runs at market speed, and the flexibility to investigate patterns that weren’t anticipated when the system was built.</p><h3>Catching the spoofer at 10:43</h3><p>Here’s what real-time detection looks like. Order flow streams in continuously, and surveillance logic runs against it as it arrives:</p><pre>from deephaven import time_table<br>from deephaven.updateby import rolling_sum_time<br><br># Simulated order flow - in production, this streams from market data feeds<br>orders = time_table(&quot;PT0.01S&quot;).update(<br>    [<br>        &quot;OrderID = randomInt(100000, 999999)&quot;,<br>        &quot;Account = `ACCT` + String.format(`%03d`, randomInt(1, 50))&quot;,<br>        &quot;Symbol = (String) new String[] {`AAPL`, `MSFT`, `GOOGL`, `AMZN`}[randomInt(0, 4)]&quot;,<br>        &quot;Side = randomInt(0, 2) == 0 ? `BUY` : `SELL`&quot;,<br>        &quot;Size = randomInt(100, 10000)&quot;,<br>        &quot;EventType = (String) new String[] {`NEW`, `NEW`, `NEW`, `CANCEL`, `CANCEL`, `FILL`}[randomInt(0, 6)]&quot;,<br>        &quot;IsCancel = EventType == `CANCEL` ? 1 : 0&quot;,<br>        &quot;IsNew = EventType == `NEW` ? 1 : 0&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/734/0*Zi4qUYIGluRXDAS0.png" /></figure><p>Every row that arrives gets incorporated into rolling calculations. For each account, we track how many orders were placed and how many were cancelled over the last 30 seconds:</p><pre># Calculate rolling cancel and new order counts per account<br>cancel_rates = orders.update_by(<br>    ops=[<br>        rolling_sum_time(<br>            ts_col=&quot;Timestamp&quot;, cols=[&quot;CancelCount = IsCancel&quot;], rev_time=&quot;PT30S&quot;<br>        ),<br>        rolling_sum_time(<br>            ts_col=&quot;Timestamp&quot;, cols=[&quot;NewCount = IsNew&quot;], rev_time=&quot;PT30S&quot;<br>        ),<br>        rolling_sum_time(<br>            ts_col=&quot;Timestamp&quot;, cols=[&quot;TotalSize = Size&quot;], rev_time=&quot;PT30S&quot;<br>        ),<br>    ],<br>    by=[&quot;Account&quot;],<br>).update(<br>    [<br>        &quot;OrderCount = CancelCount + NewCount&quot;,<br>        &quot;CancelRatio = OrderCount &gt; 0 ? (double) CancelCount / OrderCount : 0.0&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4WEmiMCF6kz8ZR6m.png" /></figure><p>An account canceling 80% of its orders within 30 seconds, while placing dozens of them? That’s worth a closer look:</p><pre># Flag suspicious activity<br>spoofing_alerts = cancel_rates.where([&quot;CancelRatio &gt; 0.8&quot;, &quot;OrderCount &gt; 20&quot;]).select(<br>    [&quot;Timestamp&quot;, &quot;Account&quot;, &quot;CancelRatio&quot;, &quot;OrderCount&quot;, &quot;TotalSize&quot;]<br>)</pre><p>That’s the entire surveillance system: three queries, about 20 lines of code, running in real time. The rolling calculations update incrementally — when a new order arrives, only the affected windows recompute, not the entire dataset.</p><p>The spoofing_alerts table updates continuously. At 10:43, when that algorithm was still running, this alert would have fired. The analyst could have seen it happening, queried the underlying orders, and watched the pattern unfold in real-time.</p><blockquote>Not better algorithms, but faster ones.</blockquote><p>The detection logic here is simple — cancel ratio over a time window. A batch system could run the same logic, but running it at 8:15 AM the next day, against data that’s already stale, misses the point. The value is in catching the pattern while it’s still actionable.</p><h3>The investigation that follows</h3><p>When an alert fires, the analyst’s next question is usually: “What else has this account done?” In a batch system, that’s a new query against a different database, probably with a different query language, and possibly requiring a data request that takes hours to fulfill.</p><p>Here, the analyst just keeps querying. The same orders table that fed the alert contains the full history. Filter by account, look at the last week, check for similar patterns on other symbols. It&#39;s all the same syntax, the same interface, the same data. And the queries the analyst writes against historical data work identically when pointed at a live feed — no translation layer, no separate codebase.</p><blockquote>The investigation happens at the speed of thought, not the speed of bureaucracy.</blockquote><p>And when the analyst spots a new pattern — maybe the account is also doing something suspicious with order modifications, not just cancellations — building a new detector is Python code, not a six-week development request.</p><h3>Why this matters for federal systems</h3><p>Federal surveillance has constraints that commercial systems don’t:</p><ul><li>The data often can’t leave controlled environments, so Deephaven runs entirely on-premises with no cloud dependencies.</li><li>Enforcement actions require proving exactly what was detected and when; the same queries that generate alerts also produce audit trails.</li><li>And federal systems rarely operate alone. Deephaven connects to Kafka streams, databases via JDBC, case management systems via REST APIs.</li></ul><p>These constraints make one thing clear: federal teams can’t rely on vendor update cycles to keep pace with evolving manipulation. The spoofing patterns of 2020 look different from 2026. The sustainable advantage isn’t any single detection algorithm — it’s the ability to develop, test, and deploy new approaches faster than the manipulators adapt.</p><p>That analyst at 8:15 AM isn’t going to catch tomorrow’s manipulation with yesterday’s batch report. But an analyst watching the market in real-time, with the flexibility to investigate anything that looks suspicious, has a chance.</p><h3>Try it yourself</h3><p>Run the spoofing detection example in your browser with <a href="https://controller.try-dh.demo.community.deephaven.io/">Deephaven’s live demo</a>. To deploy in your own environment, follow the <a href="https://deephaven.io/core/docs/getting-started/quickstart/">quickstart guide</a>. Questions about surveillance use cases? Join the conversation on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c308afb4fa36" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Private Credit Portfolio Analysis, Part 2: Live Excel Reporting with Deephaven]]></title>
            <link>https://medium.com/@deephavendatalabs/private-credit-portfolio-analysis-part-2-live-excel-reporting-with-deephaven-4bc05d38a295?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/4bc05d38a295</guid>
            <category><![CDATA[stock-market]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Thu, 23 Jul 2026 20:04:29 GMT</pubDate>
            <atom:updated>2026-07-23T20:04:29.436Z</atom:updated>
            <content:encoded><![CDATA[<h3>Connect your portfolio stress analysis directly to Excel — no exports, no copy-paste, live data every quarter</h3><p>By Elijah Petty</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-6bHa_VKzzfH_cIHzyiLXQ.png" /></figure><p>In <a href="https://deephaven.io/blog/2026/05/28/private-credit-stress-detection/">Part 1</a>, we built a portfolio stress detection system: segmenting borrowers by risk, stress testing coverage ratios, quantifying sector concentration, and pattern-matching against known problem loans. The analysis lives in Deephaven. This post covers the last step — getting it in front of your LPs and team in the spreadsheet they’re already using.</p><h3>Taking it to Excel</h3><p>The analysis runs in Deephaven, but your LPs and team need the data in a format they can open. With the <a href="https://deephaven.io/enterprise/docs/crash-course/integrations/excel/">Deephaven Excel add-in</a>, you can connect these tables directly to a spreadsheet — no export step, no copy-paste. The watch list is only useful if it stays current; this is how you make sure it does.</p><blockquote>The watch list is only useful if it stays current — live Excel subscriptions make sure it does.</blockquote><p>For the Excel formulas to work, the analysis needs to run as a <a href="https://deephaven.io/enterprise/docs/query-management/ui-queries/">Persistent Query</a> (PQ) — a named, managed worker in Deephaven Enterprise that keeps your tables in memory and accessible to clients. Create a PQ called portfolio_pq in the Enterprise UI, paste the full analysis script below as the script body, and start it. The PQ keeps portfolio_health, stress_scenarios, stress_summary, and watch_list_summary in memory and accessible to Excel by name.</p><p>Before starting the PQ, the CSVs need to actually exist at those paths. /db/Users/&lt;username&gt;/ is your personal directory in Enterprise&#39;s DB storage, reachable from any console or PQ session under your login. If you&#39;re using real loan data, export it from your loan management system and upload it there through the Enterprise UI&#39;s file browser. If you&#39;re following along with the sample data from Part 1, open a console session under your own user and run the same generation script, pointing write_csv at your Enterprise path instead of /data/.</p><p>write_csv doesn&#39;t create missing directories, so if /db/Users/&lt;username&gt;/ hasn&#39;t been written to before, create it first:</p><pre>import os<br><br>os.makedirs(&quot;/db/Users/&lt;username&gt;&quot;, exist_ok=True)</pre><p>Sample data generation script:</p><pre>from deephaven import new_table, write_csv<br>from deephaven.column import string_col, int_col, double_col<br>import random<br><br># Create Q4 2025 borrower financials (62 borrowers)<br>borrower_ids = [f&quot;B{i:03d}&quot; for i in range(1, 63)]<br>quarters = [&quot;Q4_2025&quot;] * 62<br><br># Generate realistic financial metrics<br>random.seed(42)<br>n_small, n_medium, n_large = 34, 19, 9<br>ebitdas = (<br>    [random.randint(12_000_000, 24_000_000) for _ in range(n_small)]<br>    + [random.randint(25_000_000, 49_000_000) for _ in range(n_medium)]<br>    + [random.randint(50_000_000, 75_000_000) for _ in range(n_large)]<br>)<br>random.shuffle(ebitdas)<br>total_debts = [int(ebitda * random.uniform(3.0, 5.5)) for ebitda in ebitdas]<br>coverage_targets = [random.uniform(1.8, 3.5) for _ in range(62)]<br>interest_expenses = [<br>    int(ebitda / coverage) for ebitda, coverage in zip(ebitdas, coverage_targets)<br>]<br>cash_balances = [int(ebitda * random.uniform(0.4, 0.7)) for ebitda in ebitdas]<br>current_liabilities = [int(cash * random.uniform(0.6, 0.9)) for cash in cash_balances]<br><br>borrower_financials = new_table(<br>    [<br>        string_col(&quot;BorrowerID&quot;, borrower_ids),<br>        string_col(&quot;Quarter&quot;, quarters),<br>        int_col(&quot;EBITDA&quot;, ebitdas),<br>        int_col(&quot;TotalDebt&quot;, total_debts),<br>        int_col(&quot;InterestExpense&quot;, interest_expenses),<br>        int_col(&quot;CashBalance&quot;, cash_balances),<br>        int_col(&quot;CurrentLiabilities&quot;, current_liabilities),<br>    ]<br>)<br><br># Create loan terms and covenants<br>borrower_names = (<br>    [<br>        &quot;TechVenture_LLC&quot;,<br>        &quot;DataStream_Corp&quot;,<br>        &quot;CloudSys_Inc&quot;,<br>        &quot;MediCare_Solutions&quot;,<br>        &quot;RetailPro_Group&quot;,<br>        &quot;ManufactureTech_Co&quot;,<br>        &quot;SmallBiz_Services&quot;,<br>        &quot;IndustrialParts_Inc&quot;,<br>        &quot;HealthTech_Innovations&quot;,<br>        &quot;Enterprise_Software&quot;,<br>        &quot;ACME_Corp&quot;,<br>        &quot;FinanceApp_Systems&quot;,<br>    ]<br>    + [&quot;TechStart_Inc&quot;, &quot;Retail_Co&quot;]<br>    + [f&quot;Borrower_{i}&quot; for i in range(15, 63)]<br>)<br><br>industries = (<br>    [&quot;Software&quot;] * 12  # TechVenture_LLC through FinanceApp_Systems<br>    + [&quot;Technology&quot;]  # TechStart_Inc<br>    + [&quot;Retail&quot;]  # Retail_Co<br>    + [&quot;SaaS&quot;] * 8  # generic borrowers<br>    + [&quot;Technology&quot;] * 5  # generic borrowers<br>    + [&quot;IT Services&quot;] * 3  # generic borrowers<br>    + [&quot;Healthcare&quot;] * 8  # generic borrowers<br>    + [&quot;Manufacturing&quot;] * 11  # generic borrowers<br>    + [&quot;Retail&quot;] * 6  # generic borrowers<br>    + [&quot;Business Services&quot;] * 7  # generic borrowers<br>)<br><br>loan_amounts = [random.randint(4_000_000, 11_000_000) for _ in range(62)]<br>interest_rates = [<br>    5.0<br>    if ind in [&quot;Software&quot;, &quot;Technology&quot;, &quot;SaaS&quot;]<br>    else 5.5<br>    if ind == &quot;Healthcare&quot;<br>    else 6.0<br>    for ind in industries<br>]<br>max_leverage_ratios = [<br>    4.5<br>    if ind in [&quot;Software&quot;, &quot;Technology&quot;, &quot;Manufacturing&quot;]<br>    else 5.0<br>    if ind in [&quot;SaaS&quot;, &quot;Healthcare&quot;, &quot;IT Services&quot;]<br>    else 6.0<br>    for ind in industries<br>]<br>min_coverage_ratios = [<br>    2.5<br>    if ind in [&quot;Software&quot;, &quot;Technology&quot;, &quot;Manufacturing&quot;]<br>    else 2.0<br>    if ind in [&quot;SaaS&quot;, &quot;Healthcare&quot;, &quot;IT Services&quot;]<br>    else 1.5<br>    for ind in industries<br>]<br>min_liquidities = [int(loan * 1.2) for loan in loan_amounts]<br><br>loan_terms = new_table(<br>    [<br>        string_col(&quot;BorrowerID&quot;, borrower_ids),<br>        string_col(&quot;BorrowerName&quot;, borrower_names),<br>        string_col(&quot;Industry&quot;, industries),<br>        int_col(&quot;LoanAmount&quot;, loan_amounts),<br>        double_col(&quot;InterestRate&quot;, interest_rates),<br>        double_col(&quot;MaxLeverageRatio&quot;, max_leverage_ratios),<br>        double_col(&quot;MinCoverageRatio&quot;, min_coverage_ratios),<br>        int_col(&quot;MinLiquidity&quot;, min_liquidities),<br>    ]<br>)<br><br># Write to your Enterprise path so portfolio_pq can read them below<br>write_csv(borrower_financials, &quot;/db/Users/&lt;username&gt;/Q4_2025_portfolio_financials.csv&quot;)<br>write_csv(loan_terms, &quot;/db/Users/&lt;username&gt;/loan_terms.csv&quot;)</pre><p>Full portfolio_pq script:</p><pre>from deephaven import read_csv, agg<br>from deephaven.pandas import to_pandas<br><br># ── Data loading ──────────────────────────────────────────────────────────────<br># Replace &lt;username&gt; with your Deephaven Enterprise username<br>borrower_financials = read_csv(&quot;/db/Users/&lt;username&gt;/Q4_2025_portfolio_financials.csv&quot;)<br>loan_terms = read_csv(&quot;/db/Users/&lt;username&gt;/loan_terms.csv&quot;)<br><br>TOTAL_EXPOSURE = to_pandas(loan_terms)[&quot;LoanAmount&quot;].sum()<br><br># ── Current portfolio ─────────────────────────────────────────────────────────<br>portfolio = borrower_financials.natural_join(<br>    loan_terms,<br>    on=[&quot;BorrowerID&quot;],<br>    joins=[<br>        &quot;BorrowerName&quot;,<br>        &quot;Industry&quot;,<br>        &quot;LoanAmount&quot;,<br>        &quot;InterestRate&quot;,<br>        &quot;MaxLeverageRatio&quot;,<br>        &quot;MinCoverageRatio&quot;,<br>        &quot;MinLiquidity&quot;,<br>    ],<br>).update_view(<br>    [<br>        &quot;LeverageRatio = TotalDebt / EBITDA&quot;,<br>        &quot;CoverageRatio = EBITDA / InterestExpense&quot;,<br>        &quot;LiquidityRatio = CashBalance / CurrentLiabilities&quot;,<br>        &quot;SizeSegment = EBITDA &lt; 25_000_000 ? `Small (&lt;$25M)` : &quot;<br>        + &quot;(EBITDA &lt; 50_000_000 ? `Medium ($25-50M)` : `Large (&gt;$50M)`)&quot;,<br>        &quot;IsHighLeverage = LeverageRatio &gt; 4.0 ? 1 : 0&quot;,<br>        &quot;IsLowCoverage = CoverageRatio &lt; 2.5 ? 1 : 0&quot;,<br>        &quot;IsHighRisk = (LeverageRatio &gt; 4.0 || CoverageRatio &lt; 2.5) ? 1 : 0&quot;,<br>    ]<br>)<br><br># ── Stress scenarios ──────────────────────────────────────────────────────────<br>stress_scenarios = portfolio.update_view(<br>    [<br>        &quot;CurrentInterestExpense = InterestExpense&quot;,<br>        &quot;CurrentCoverageRatio = EBITDA / CurrentInterestExpense&quot;,<br>        &quot;InterestExpense_50bp = InterestExpense * (InterestRate + 0.5) / InterestRate&quot;,<br>        &quot;CoverageRatio_50bp = EBITDA / InterestExpense_50bp&quot;,<br>        &quot;Breach_50bp = CoverageRatio_50bp &lt; MinCoverageRatio&quot;,<br>        &quot;InterestExpense_100bp = InterestExpense * (InterestRate + 1.0) / InterestRate&quot;,<br>        &quot;CoverageRatio_100bp = EBITDA / InterestExpense_100bp&quot;,<br>        &quot;Breach_100bp = CoverageRatio_100bp &lt; MinCoverageRatio&quot;,<br>        &quot;InterestExpense_200bp = InterestExpense * (InterestRate + 2.0) / InterestRate&quot;,<br>        &quot;CoverageRatio_200bp = EBITDA / InterestExpense_200bp&quot;,<br>        &quot;Breach_200bp = CoverageRatio_200bp &lt; MinCoverageRatio&quot;,<br>        &quot;MarginToBreach = ((CoverageRatio_100bp / MinCoverageRatio) - 1.0) * 100&quot;,<br>        &quot;IsCurrentBreach = CurrentCoverageRatio &lt; MinCoverageRatio ? 1 : 0&quot;,<br>        &quot;ExposureIfBreach_100bp = Breach_100bp ? LoanAmount : 0&quot;,<br>    ]<br>)<br><br># ── Risk scoring ──────────────────────────────────────────────────────────────<br>portfolio_with_risk = portfolio.update_view(<br>    [<br>        &quot;LeverageScore = (LeverageRatio / MaxLeverageRatio) * 40&quot;,<br>        &quot;CoverageScore = (MinCoverageRatio / CoverageRatio) * 40&quot;,<br>        &quot;SizeScore = EBITDA &lt; 25_000_000 ? 20 : 0&quot;,<br>        &quot;RiskScore = LeverageScore + CoverageScore + SizeScore&quot;,<br>        &quot;RiskCategory = RiskScore &gt; 80 ? `High` : (RiskScore &gt; 60 ? `Medium` : `Low`)&quot;,<br>        &quot;IsCovenantBreach = (LeverageRatio &gt; MaxLeverageRatio || CoverageRatio &lt; MinCoverageRatio || CashBalance &lt; MinLiquidity) ? 1 : 0&quot;,<br>    ]<br>)<br><br># ── Output: portfolio health ──────────────────────────────────────────────────<br>portfolio_health = portfolio_with_risk.agg_by(<br>    [<br>        agg.count_(&quot;TotalBorrowers&quot;),<br>        agg.sum_([&quot;TotalExposure = LoanAmount&quot;]),<br>        agg.avg([&quot;AvgRiskScore = RiskScore&quot;]),<br>        agg.avg([&quot;AvgLeverageRatio = LeverageRatio&quot;]),<br>        agg.avg([&quot;AvgCoverageRatio = CoverageRatio&quot;]),<br>        agg.sum_([&quot;CovenantBreaches = IsCovenantBreach&quot;]),<br>    ]<br>)<br><br># ── Output: stress summary ────────────────────────────────────────────────────<br>stress_summary = stress_scenarios.agg_by(<br>    [<br>        agg.count_(&quot;TotalBorrowers&quot;),<br>        agg.sum_([&quot;CurrentBreaches = IsCurrentBreach&quot;]),<br>        agg.sum_([&quot;Breaches_50bp = Breach_50bp&quot;]),<br>        agg.sum_([&quot;Breaches_100bp = Breach_100bp&quot;]),<br>        agg.sum_([&quot;Breaches_200bp = Breach_200bp&quot;]),<br>        agg.sum_([&quot;ExposureAtRisk_100bp = ExposureIfBreach_100bp&quot;]),<br>    ]<br>).update_view(<br>    [f&quot;PercentAtRisk_100bp = (ExposureAtRisk_100bp / {TOTAL_EXPOSURE}) * 100&quot;]<br>)<br><br># ── Output: watch list ────────────────────────────────────────────────────────<br>watch_list_summary = (<br>    portfolio_with_risk.where([&quot;RiskScore &gt; 70&quot;])<br>    .update_view(<br>        [<br>            &quot;Priority = RiskScore &gt; 80 ? `Critical` : `High`&quot;,<br>            &quot;RecommendedAction = RiskScore &gt; 80 ? `Immediate engagement` : `Enhanced monitoring`&quot;,<br>        ]<br>    )<br>    .sort_descending([&quot;RiskScore&quot;])<br>    .view(<br>        [<br>            &quot;BorrowerName&quot;,<br>            &quot;Industry&quot;,<br>            &quot;RiskScore&quot;,<br>            &quot;RiskCategory&quot;,<br>            &quot;Priority&quot;,<br>            &quot;LeverageRatio&quot;,<br>            &quot;CoverageRatio&quot;,<br>            &quot;LoanAmount&quot;,<br>            &quot;RecommendedAction&quot;,<br>        ]<br>    )<br>)</pre><p>Once the PQ is running, connect to it from Excel using ConnectionID:PQName/TableName.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O43ZH2Mj-YFElG9T.png" /></figure><p>For LP quarterly reports, use DEEPHAVEN_SNAPSHOT — it pulls a point-in-time copy of the data, which is what you want for a static report that reflects a specific quarter. Pull portfolio_health into your existing template:</p><pre>=DEEPHAVEN_SNAPSHOT(&quot;con1+:portfolio_pq/portfolio_health&quot;,,TRUE)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*bLfLletsgnB3IUPK.png" /></figure><p>For ongoing monitoring, subscribe to stress_scenarios — it gives you one row per borrower with coverage ratios under each rate scenario and breach flags, which is well-suited for Excel&#39;s conditional formatting and formula tools:</p><pre>=DEEPHAVEN_SUBSCRIBE(&quot;con1+:portfolio_pq/stress_scenarios&quot;,,TRUE)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*g5cahZhg4MpE8fuj.png" /></figure><p>From there, it’s your spreadsheet — conditional formatting, pivot tables, FILTER, whatever your workflow calls for. When Q1 data arrives and you restart the PQ, the subscribed cells update on their own.</p><h3>Formula examples</h3><p>Once stress_scenarios is subscribed in your sheet, the breach flags and coverage ratios are just cells — Excel&#39;s native tools work on them the same as any other range.</p><p>To see only the highly leveraged borrowers, wrap the subscribed range in FILTER:</p><pre>=FILTER(A2:AI63, S2:S63=1, &quot;No high-leverage borrowers&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YYpsP-aYm0sfXSZS.png" /></figure><p>A2:AI63 covers the table&#39;s full 35 columns and 62 data rows (with A1:AI1 as the header row above it). IsHighLeverage is a reasonable condition to filter on since Part 1&#39;s analysis found high leverage across a meaningful share of the portfolio in every quarter tested. The if_empty argument still matters as a safeguard: without it, FILTER returns #CALC! whenever zero rows match, since Excel refuses to spill an empty array. As the PQ ticks new data, the filtered view updates without you touching the formula.</p><blockquote>Note: If you add or remove columns in the script, every column after the change shifts letters. Recount from the header row in row 1 rather than assuming these letters still apply after editing portfolio_pq.</blockquote><p>Conditional formatting makes breaches visible at a glance without a helper column. Breach_100bp is column 29, AC. Select that column, then <strong>Home &gt; Conditional Formatting &gt; New Rule &gt; Use a formula to determine which cells to format</strong>, and enter:</p><pre>=$AC2=TRUE</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*AHp6r35lbA7iwZyD.png" /></figure><p>Set the fill to your color of choice via the <strong>Format</strong> button in the Conditional Formatting dialog. Every row that breaches at 100bp highlights itself as the underlying table changes — no macro, no refresh button.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6xfmCL1lTPDrbUPu.png" /></figure><p>For sector-level exposure, pivot the same subscribed range: drag Industry to Rows and LoanAmount to Values (Sum), then add Breach_100bp to Values (Average) to see breach rate alongside exposure per sector. Because the source range is a live subscription, refreshing the pivot table picks up whatever the PQ has computed as of that moment.</p><h3>Building the LP report spreadsheet</h3><p>A quarterly LP report is really three tabs, each pulling a different table with a different formula:</p><ul><li><strong>Portfolio Health</strong> — DEEPHAVEN_SNAPSHOT on portfolio_health. This is the point-in-time summary LPs expect in a quarterly letter, so it should stay frozen until you explicitly re-pull it for the next quarter.</li><li><strong>Stress Scenarios</strong> — DEEPHAVEN_SUBSCRIBE on stress_scenarios, with the FILTER and conditional formatting from the section above. This tab is for your team&#39;s ongoing monitoring, not the static LP letter, so it&#39;s fine for it to keep updating.</li><li><strong>Watch List</strong> — DEEPHAVEN_SNAPSHOT on watch_list_summary, since the whole point of a watch list in an LP report is to show the borrowers you flagged as of that quarter, not a list that reshuffles under the reader.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wy23UlZNmQCgdoO0.gif" /></figure><p>For quarterly reuse, keep the sheet itself untouched — the tab structure, formulas, and formatting don’t change. When Q1 data lands, update the CSVs portfolio_pq reads from and restart the PQ. The DEEPHAVEN_SUBSCRIBE cells on the Stress Scenarios tab pick up the new data automatically. For the Portfolio Health and Watch List tabs, re-run the DEEPHAVEN_SNAPSHOT formulas — right-click the cell and <strong>Recalculate</strong>, or press F9 — to pull the new quarter&#39;s point-in-time values. The same sheet works quarter after quarter; only the underlying data changes.</p><h3>Try it yourself</h3><ol><li><a href="https://controller.try-dh.demo.community.deephaven.io/"><strong>Try it in the browser</strong></a> — no install required.</li><li><a href="https://deephaven.io/core/docs/getting-started/quickstart/"><strong>Install Deephaven</strong></a> — free, takes about five minutes.</li><li><a href="https://deephaven.io/enterprise/docs/crash-course/integrations/excel/"><strong>Set up the Excel add-in</strong></a> and connect it to your Enterprise instance.</li><li><strong>Point </strong><strong>portfolio_pq at your own loan data</strong> and pull portfolio_health into a fresh sheet with DEEPHAVEN_SNAPSHOT.</li><li><strong>Subscribe to </strong><strong>stress_scenarios</strong> and add a conditional formatting rule for your own breach threshold.</li></ol><p>Your LPs don’t need another PDF next quarter — they need the sheet that’s already open to update itself. Questions? Join us on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4bc05d38a295" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Miners light the fuse: Tracking Bitcoin liquidation cascades in real-time]]></title>
            <link>https://medium.com/@deephavendatalabs/miners-light-the-fuse-tracking-bitcoin-liquidation-cascades-in-real-time-2d0455eb64af?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/2d0455eb64af</guid>
            <category><![CDATA[bitcoin-mining]]></category>
            <category><![CDATA[bitcoin]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[cryptocurrency]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Tue, 16 Jun 2026 15:55:28 GMT</pubDate>
            <atom:updated>2026-06-16T15:55:28.881Z</atom:updated>
            <content:encoded><![CDATA[<h3>How miner capitulation triggers futures liquidations — and how to watch it happen live</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*69qJHZ62n6PVhQz1WeMAdQ.png" /></figure><p>A 5% Bitcoin price drop doesn’t just hurt holders. It triggers a chain reaction that can turn a routine dip into a 20% crash in under an hour.</p><p>The mechanism is well understood by traders but rarely visible in real-time: miners operating at a loss are forced to sell. Their spot selling pushes price down. Leveraged longs get liquidated, which forces more market sells. Each liquidation triggers the next. The cascade has already started.</p><blockquote>Miners don’t explode the market — but they light the fuse.</blockquote><p>In our <a href="https://deephaven.io/blog/2026/04/09/crypto-mining-economics/">previous post on mining economics</a>, we showed how hashprice has collapsed 70% since the halving, pushing many miners underwater. This post connects that squeeze to what happens next: how miner capitulation feeds into the derivatives market, and how to track the cascade as it unfolds using Deephaven. We’ll also connect an AI assistant via MCP — Claude, Cursor, Windsurf, or any MCP-compatible tool — so you can query your data in plain English while the market moves.</p><p><strong>Quick recap from Part 1:</strong> Hashprice — daily revenue per terahash — has dropped over 70% since the April 2024 halving. At current electricity rates, most home miners are now underwater. Large operations are either selling BTC to cover costs or shutting down entirely.</p><h3>The three-stage cascade</h3><p>Before we build anything, let’s understand the mechanics.</p><p><strong>Stage 1: Miners capitulate.</strong> When the <a href="https://studio.glassnode.com/charts/btc-difficulty-regression?a=BTC">Difficulty Regression Model</a> (Glassnode’s estimate of average mining production cost) shows spot price near or below the cost floor, miners have two options: shut down or sell. Large operations choose to sell, often in bulk. This is sustained, predictable sell pressure.</p><p><strong>Stage 2: Spot price slides.</strong> Miner selling alone rarely crashes markets, but it creates a headwind. Combined with any negative catalyst — a macro event, a regulatory headline, a whale exit — it accelerates the drop.</p><p><strong>Stage 3: Futures liquidations cascade.</strong> A manageable decline becomes a rout. Traders on 10x or 20x leverage have thin margins. A 5% drop liquidates a 20x long entirely. Each forced liquidation is a market sell order, pushing price lower, triggering the next liquidation. During the worst cascades, hundreds of millions of dollars are liquidated in minutes.</p><p>Each stage is visible in data before the next one starts. The rest of this post builds the tools to watch it happen.</p><h3>The miner stress indicator</h3><blockquote>Note: The code in this post runs in a Deephaven console. If you don’t have Deephaven running yet, see the <a href="https://deephaven.io/core/docs/getting-started/quickstart/">quickstart guide</a> — Docker or pip, under five minutes. Once it’s running, install the extra dependency inside your environment:</blockquote><pre>pip install numpy</pre><p>The Difficulty Regression Model estimates Bitcoin’s average production cost as a log-log relationship between price and network difficulty:</p><pre>ln(Price) = A + B × ln(Difficulty)</pre><p>Instead of using published constants that drift over time, we fit A and B directly from three years of historical data.</p><p>The <strong>Difficulty Multiple</strong> (spot price divided by this estimated cost) is the key signal. When it drops toward 1.0, miners are near breakeven. Below 1.0, forced selling begins.</p><p>Let’s load difficulty and price history, fit the regression, and calculate the model:</p><pre>from deephaven import agg, new_table, read_csv<br>from deephaven import dtypes as dht<br>from deephaven.column import string_col, double_col<br>import urllib.request<br>import json<br>import numpy as np<br><br>def fetch_json(url):<br>    with urllib.request.urlopen(url) as r:<br>        return json.loads(r.read().decode())<br><br># Load 3 years of difficulty and price history from Blockchain.com (free, no auth)<br>col_defs = {&quot;Timestamp&quot;: dht.string, &quot;Difficulty&quot;: dht.double}<br>difficulty_csv = read_csv(<br>    &quot;https://api.blockchain.info/charts/difficulty?timespan=3years&amp;format=csv&amp;sampled=true&quot;,<br>    headless=True,<br>    header=col_defs,<br>)<br>price_data = fetch_json(<br>    &quot;https://api.blockchain.info/charts/market-price?timespan=3years&amp;format=json&amp;sampled=true&quot;<br>)<br>diff_data = fetch_json(<br>    &quot;https://api.blockchain.info/charts/difficulty?timespan=3years&amp;format=json&amp;sampled=true&quot;<br>)<br># Align by timestamp (the two endpoints may return different sample counts)<br>price_by_ts = {v[&quot;x&quot;]: v[&quot;y&quot;] for v in price_data[&quot;values&quot;]}<br>diff_by_ts = {v[&quot;x&quot;]: v[&quot;y&quot;] for v in diff_data[&quot;values&quot;]}<br>common_ts = sorted(set(price_by_ts) &amp; set(diff_by_ts))<br># Fit log-log regression: ln(price) = A + B * ln(difficulty)<br>ln_d = np.log([diff_by_ts[t] for t in common_ts])<br>ln_p = np.log([price_by_ts[t] for t in common_ts])<br>B, A = np.polyfit(ln_d, ln_p, 1)<br>difficulty_model = difficulty_csv.update_view(<br>    [<br>        &quot;Date = parseInstant(Timestamp.replace(` `, `T`) + `Z`)&quot;,<br>        f&quot;Regression_Price = Math.exp({A} + {B} * Math.log(Difficulty))&quot;,<br>    ]<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9CAWDMCy27s38IeK3bDv3Q.png" /></figure><p>That gives us the estimated cost floor for each day. Comparing it against spot price gives the Difficulty Multiple:</p><pre># Fetch current BTC spot price from CoinGecko (free, no auth)<br>url = &quot;https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&amp;vs_currencies=usd&quot;<br>with urllib.request.urlopen(url) as response:<br>    spot_data = json.loads(response.read().decode())<br><br>btc_price = spot_data[&quot;bitcoin&quot;][&quot;usd&quot;]<br><br># Get the most recent regression price<br>latest_regression = difficulty_model.tail(1)<br><br># Calculate difficulty multiple<br>difficulty_multiple = latest_regression.update_view(<br>    [<br>        f&quot;Spot_Price = (double){btc_price}&quot;,<br>        f&quot;Difficulty_Multiple = (double){btc_price} / Regression_Price&quot;,<br>        &quot;Status = Difficulty_Multiple &lt; 1.0 ? `STRESS` : Difficulty_Multiple &lt; 1.5 ? `WATCH` : `HEALTHY`&quot;,<br>    ]<br>)</pre><p>A Difficulty Multiple below 1.0 means spot price has fallen below production cost. Miners are selling at a loss. The lower it drops, the harder they sell.</p><h3>Loading liquidation data</h3><p>Now let’s bring in the derivatives side. The example below uses synthetic data so you can follow along without an API subscription — swap in <a href="https://www.coinglass.com/">Coinglass</a> or the <a href="https://developers.binance.com/docs/derivatives/usds-margined-futures/websocket-market-streams/Liquidation-Order-Streams">Binance public WebSocket</a> for a live feed.</p><pre>import time<br><br># Synthetic 48 hours of hourly liquidation data (illustrative)<br>rng = np.random.default_rng(42)<br>n = 48<br>now_ms = int(time.time() * 1000)<br>ts_ms = [float(now_ms - (n - 1 - i) * 3_600_000) for i in range(n)]<br><br>long_liq = list(rng.exponential(8_000_000, n))<br>short_liq = list(rng.exponential(4_000_000, n))<br># Inject a cascade spike ~20 hours ago<br>long_liq[26] = 75_000_000.0<br>long_liq[27] = 120_000_000.0<br>long_liq[28] = 45_000_000.0<br><br>liquidations = new_table(<br>    [<br>        double_col(&quot;Timestamp_Ms&quot;, ts_ms),<br>        double_col(&quot;Long_Liquidations_USD&quot;, long_liq),<br>        double_col(&quot;Short_Liquidations_USD&quot;, short_liq),<br>    ]<br>).update_view(<br>    [<br>        &quot;Time = epochNanosToInstant((long)(Timestamp_Ms * 1_000_000L))&quot;,<br>        &quot;Total_Liquidations_USD = Long_Liquidations_USD + Short_Liquidations_USD&quot;,<br>        &quot;Cascade_Signal = Long_Liquidations_USD &gt; 50_000_000 ? `CASCADE` : Long_Liquidations_USD &gt; 10_000_000 ? `ELEVATED` : `NORMAL`&quot;,<br>    ]<br>)</pre><p>Hours 26–28 in that dataset are the cascade: $75M, then $120M, then $45M in long liquidations across three consecutive hours. That’s the pattern we’re watching for in real data.</p><h3>Connecting miner stress to liquidation events</h3><p>Historically, large liquidation cascades follow periods of miner stress — the Difficulty Multiple drops first, then a catalyst triggers the cascade days or weeks later. Joining the two datasets makes that relationship visible.</p><pre># Align daily difficulty data with daily liquidation totals<br>daily_liquidations = liquidations.update_view(&quot;Date = toLocalDate(Time, &#39;UTC&#39;)&quot;).agg_by(<br>    [agg.sum_(&quot;Daily_Long_Liq = Long_Liquidations_USD&quot;)], by=&quot;Date&quot;<br>)<br><br># Join with difficulty model (align Date types: Instant -&gt; LocalDate)<br>combined = (<br>    difficulty_model.update_view(&quot;Date = toLocalDate(Date, &#39;UTC&#39;)&quot;)<br>    .natural_join(daily_liquidations, on=&quot;Date&quot;)<br>    .update_view(&quot;High_Liq_Day = Daily_Long_Liq &gt; 100_000_000&quot;)<br>)</pre><p>The pattern is consistent: sustained miner stress (Difficulty Multiple below 1.0) precedes major cascade events. The stress doesn’t cause the crash, but it means the market is primed. The June 2022 cascade — over $600M liquidated in 24 hours — followed exactly this pattern: hashprice near all-time lows, then a catalyst (3AC collapse), then the cascade.</p><h3>Connecting Claude to your data</h3><p>With your tables live in Deephaven, connect <a href="https://claude.ai/">Claude</a> so you can ask questions about the cascade in plain English — no query writing while the market is moving. This post uses Claude Desktop, but Cursor, Windsurf, and any other MCP-compatible agent work identically.</p><p>The connection runs through MCP (Model Context Protocol), an open standard for linking AI assistants to external tools. Once configured, Claude can see your ticking tables, write queries, run them, and return results in the chat.</p><h3>Install the MCP bridge</h3><pre>pip install &quot;deephaven-mcp[core]&quot;</pre><h3>Configure Claude Desktop</h3><p>Open ~/Library/Application Support/Claude/claude_desktop_config.json and add:</p><pre>{<br>  &quot;mcpServers&quot;: {<br>    &quot;deephaven-systems&quot;: {<br>      &quot;command&quot;: &quot;/path/to/.venv/bin/dh-mcp-systems-server&quot;,<br>      &quot;args&quot;: [],<br>      &quot;env&quot;: {<br>        &quot;DH_MCP_CONFIG_FILE&quot;: &quot;/path/to/deephaven_mcp.json&quot;<br>      }<br>    }<br>  }<br>}</pre><p>Create deephaven_mcp.json:</p><pre>{<br>  &quot;community&quot;: {<br>    &quot;sessions&quot;: {<br>      &quot;local&quot;: {<br>        &quot;host&quot;: &quot;localhost&quot;,<br>        &quot;port&quot;: 10000,<br>        &quot;auth_type&quot;: &quot;io.deephaven.authentication.psk.PskAuthenticationHandler&quot;,<br>        &quot;auth_token&quot;: &quot;YOUR_PSK_TOKEN&quot;<br>      }<br>    }<br>  }<br>}</pre><p>If you launched Deephaven with anonymous auth, set &quot;auth_type&quot;: &quot;Anonymous&quot; and omit auth_token. Full configuration options are in the <a href="https://deephaven.io/core/docs/how-to-guides/mcp/">MCP setup guide</a>.</p><p>Restart Claude Desktop after saving. For Cursor, Windsurf, or other agents, see the <a href="https://deephaven.io/core/docs/how-to-guides/mcp/">MCP setup guide</a> for per-tool config steps.</p><h3>Ask Claude about your data</h3><p>With the MCP connection configured and your tables loaded, you can query everything without writing code. Here are two examples.</p><h4>“What’s the current difficulty multiple and what does it mean for miner selling pressure?”</h4><p><strong>Claude:</strong> I’ll pull the current difficulty multiple and flag the stress status.</p><pre>miner_stress_summary = difficulty_multiple.view(<br>    [<br>        &quot;Date&quot;,<br>        &quot;Spot_Price&quot;,<br>        &quot;Regression_Price&quot;,<br>        &quot;Difficulty_Multiple&quot;,<br>        &quot;Status&quot;,<br>    ]<br>)</pre><p><strong>Result:</strong> The table shows the current Difficulty Multiple and status flag.</p><p>Below 1.0, miners are operating at a loss and selling is no longer optional — it’s survival. Between 1.0 and 1.5 is the watch zone: margins are thin enough that any price weakness pushes more miners underwater.</p><h4>“Show me the largest liquidation events in the last 24 hours and flag any cascade conditions.”</h4><p><strong>Claude:</strong> I’ll filter the last 24 hours, sort by liquidation size, and flag cascade conditions.</p><pre>from deephaven.time import dh_now<br><br>cascade_events = (<br>    liquidations.where(&quot;Time &gt;= dh_now() - &#39;PT24H&#39;&quot;)<br>    .sort_descending(&quot;Long_Liquidations_USD&quot;)<br>    .view(<br>        [<br>            &quot;Time&quot;,<br>            &quot;Long_Liquidations_USD&quot;,<br>            &quot;Short_Liquidations_USD&quot;,<br>            &quot;Total_Liquidations_USD&quot;,<br>            &quot;Cascade_Signal&quot;,<br>        ]<br>    )<br>)</pre><p><strong>Result:</strong> The table shows hourly liquidation events sorted by size, with cascade signals flagged.</p><h3>Making it real-time</h3><p>Polling for liquidation data every 30 seconds gives you a live feed. The joins, aggregations, and cascade signals run continuously on the ticking table — no code changes required when the data updates.</p><blockquote>The cascade starts long before crypto Twitter notices. Real-time tracking means you’re watching the fuse, not the explosion.</blockquote><pre>from deephaven import function_generated_table, empty_table<br>import random<br><br><br># Synthetic live feed — swap fetch_live_liquidations for a real Coinglass or<br># Binance WebSocket implementation to connect to actual market data.<br>def fetch_live_liquidations():<br>    long_liq_24h = random.expovariate(1 / 80_000_000)<br>    short_liq_24h = random.expovariate(1 / 40_000_000)<br>    signal = (<br>        &quot;CASCADE&quot;<br>        if long_liq_24h &gt; 500_000_000<br>        else &quot;ELEVATED&quot;<br>        if long_liq_24h &gt; 100_000_000<br>        else &quot;NORMAL&quot;<br>    )<br>    return empty_table(1).update(<br>        [<br>            &quot;Timestamp = now()&quot;,<br>            f&quot;Long_Liq_24h = (double){long_liq_24h}&quot;,<br>            f&quot;Short_Liq_24h = (double){short_liq_24h}&quot;,<br>            f&quot;Total_Liq_24h = (double){long_liq_24h + short_liq_24h}&quot;,<br>            f&quot;Cascade_Signal = (String)`{signal}`&quot;,<br>        ]<br>    )<br><br><br># Poll every 30 seconds<br>live_liquidations = function_generated_table(<br>    fetch_live_liquidations, refresh_interval_ms=30_000<br>)</pre><blockquote>Note: Coinglass requires a paid API subscription. For free liquidation data, Binance and Bybit both expose liquidation streams via their public WebSocket APIs — no authentication required for the stream endpoint.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PsVgZFulG3RxL7dO5AHqOQ.gif" /></figure><h3>Why this matters beyond trading</h3><p>Liquidation cascades are often dismissed as a “leverage problem” for speculators. But the effects ripple further.</p><p>Miners who haven’t hedged face a double squeeze: price drops while their energy costs stay fixed. Energy markets feel it too. Mining facilities cut consumption fast during crashes, shifting grid load in Texas and Kazakhstan. Institutional allocators watch cascade depth as a stress test for crypto exposure.</p><h3>Try it yourself</h3><p>The strange thing about cascades is that they’re completely predictable in retrospect. Miner stress shows up in the data. So does liquidation depth. So does the join between them. Most people just aren’t looking until after the crash.</p><p>All the code in this post runs in a stock Deephaven session. Swap the synthetic data for a live feed and everything downstream — joins, signals, Claude queries — updates automatically.</p><ol><li><a href="https://controller.try-dh.demo.community.deephaven.io/"><strong>Try it in the browser</strong></a> — no install required.</li><li><a href="https://deephaven.io/core/docs/getting-started/quickstart/"><strong>Install Deephaven</strong></a> — free, takes about five minutes.</li><li><a href="https://deephaven.io/core/docs/how-to-guides/mcp/"><strong>Connect Claude</strong></a> — point your AI at your Deephaven session.</li><li><strong>Load liquidation data</strong> — the code in this post uses synthetic data to get you started. For live data, <a href="https://www.coinglass.com/">Coinglass</a> offers a paid API, or use the free <a href="https://developers.binance.com/docs/derivatives/usds-margined-futures/websocket-market-streams/Liquidation-Order-Streams">Binance liquidation WebSocket</a> stream.</li><li><a href="https://deephaven.io/blog/2026/04/09/crypto-mining-economics/"><strong>Mining economics post</strong></a> — the first post in this series, covering hashprice and breakeven analysis.</li></ol><p>In <a href="https://deephaven.io/blog/2026/04/09/crypto-mining-economics/">Part 1</a>, we asked: will Bitcoin’s price catch up before more miners capitulate? This post answers the follow-up: when they <em>do</em> capitulate, the cascade doesn’t wait. Miner stress compresses the spring. A single catalyst releases it. Liquidations occur in minutes, not hours.</p><p>The data tells you when the spring is compressed. The question isn’t <em>if</em> the next cascade will happen — it’s whether you’re watching when it does.</p><p><a href="https://controller.try-dh.demo.community.deephaven.io/">Deephaven Data Labs Demo System</a></p><p>Questions? Join us on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2d0455eb64af" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-Time Anomaly Detection: Your ML Model Spots Problems Before Your Dashboard Turns Red]]></title>
            <link>https://medium.com/@deephavendatalabs/real-time-anomaly-detection-your-ml-model-spots-problems-before-your-dashboard-turns-red-6264e75d607b?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/6264e75d607b</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Tue, 09 Jun 2026 20:35:22 GMT</pubDate>
            <atom:updated>2026-06-09T20:35:22.262Z</atom:updated>
            <content:encoded><![CDATA[<h3>Deploy ML models on streaming data — processing only the rows that change</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3kV62unY7H8Smj9Aqc7IAw.png" /></figure><p>You’ve trained an ML model. It works great on test data. Now you need it to score a live stream — flagging anomalies, classifying events, or detecting fraud as data arrives. The catch? You can’t reprocess your entire dataset every time a new row comes in.</p><p>That’s the problem <a href="https://deephaven.io/core/pydoc/code/deephaven.learn.html">deephaven.learn</a> solves. It runs your model on streaming data, processing only the rows that change.</p><blockquote>Train once, predict forever — on data that never stops arriving.</blockquote><p>In this post, we’ll build a real-time anomaly detection system for IoT sensors. You’ll train a model once, deploy it on a live feed, and watch it flag problems within milliseconds of arrival.</p><h3>Prerequisites</h3><p>This tutorial requires a local Deephaven installation — either <a href="https://deephaven.io/core/docs/getting-started/quickstart/#pip">pip</a> or <a href="https://deephaven.io/core/docs/getting-started/quickstart/#docker">Docker</a>. We’ll use pip here.</p><p>If you don’t have Deephaven installed yet, follow the <a href="https://deephaven.io/core/docs/getting-started/quickstart/#pip">pip quickstart</a> (takes about five minutes). Then install the dependencies in the same Python environment:</p><pre>pip install deephaven-server scikit-learn numpy</pre><p>Note: If you’re using Docker instead of pip, you’ll need to install scikit-learn inside the container. Run pip install scikit-learn in the Deephaven console, or <a href="https://deephaven.io/core/docs/how-to-guides/install-and-use-python-packages/">add it to a custom Docker image</a>.</p><p>Start Deephaven:</p><pre>deephaven server --port 10000</pre><p>Open the IDE at http://localhost:10000/ide/ and you&#39;re ready to go.</p><h3>The scenario: Monitoring factory sensors</h3><p>Imagine you’re monitoring sensors on factory equipment. Each sensor reports temperature, vibration, and pressure readings every half second. Most readings are normal, but occasionally something goes wrong — a motor overheats, a bearing starts to fail, or pressure drops unexpectedly.</p><p>You could stare at dashboards all day waiting for numbers to turn red. Or you could let an ML model watch for you, flagging anomalies the instant they appear.</p><p>Let’s build exactly that.</p><h3>Step 1: Create a streaming sensor feed</h3><p>First, let’s simulate a stream of sensor readings. We’ll use Deephaven’s time_table to generate new rows every 500 milliseconds, with occasional anomalies injected:</p><pre>from deephaven import time_table<br>import random<br><br><br>class SensorReading:<br>    &quot;&quot;&quot;Container for sensor readings.&quot;&quot;&quot;<br><br>    def __init__(self, is_anomaly):<br>        if is_anomaly:<br>            self.temperature = random.gauss(95, 5)<br>            self.vibration = random.gauss(2.0, 0.5)<br>            self.pressure = random.gauss(60, 10)<br>        else:<br>            self.temperature = random.gauss(70, 3)<br>            self.vibration = random.gauss(0.5, 0.1)<br>            self.pressure = random.gauss(100, 2)<br><br><br>def sensor_readings():<br>    &quot;&quot;&quot;Generate sensor readings with ~10% chance of anomaly per row.&quot;&quot;&quot;<br>    return SensorReading(random.random() &lt; 0.10)<br><br><br># Create the streaming table<br>sensor_stream = (<br>    time_table(&quot;PT0.5S&quot;)<br>    .update(<br>        [<br>            &quot;sensor_id = (int)(ii % 5)&quot;,<br>            &quot;readings = sensor_readings()&quot;,<br>            &quot;temperature = (double) readings.temperature&quot;,<br>            &quot;vibration = (double) readings.vibration&quot;,<br>            &quot;pressure = (double) readings.pressure&quot;,<br>        ]<br>    )<br>    .drop_columns(&quot;readings&quot;)<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/662/0*WioiqQQNvSxN4N_M.png" /></figure><p>This creates a table that grows by one row every 500 milliseconds. Each row contains readings from one of five sensors, with about 10% of readings showing anomalous behavior.</p><h3>Step 2: Train an anomaly detection model</h3><p>Now let’s train a model to recognize anomalies. We’ll use scikit-learn&#39;s Isolation Forest — an unsupervised algorithm that learns what &quot;normal&quot; looks like and flags outliers.</p><p>First, generate some training data (normal readings only):</p><pre>import numpy as np<br>from sklearn.ensemble import IsolationForest<br><br># Generate training data: 1000 normal readings<br>np.random.seed(42)<br>n_samples = 1000<br><br>train_data = np.column_stack(<br>    [<br>        np.random.normal(70, 3, n_samples),  # temperature<br>        np.random.normal(0.5, 0.1, n_samples),  # vibration<br>        np.random.normal(100, 2, n_samples),  # pressure<br>    ]<br>)<br><br># Train the model<br>model = IsolationForest(<br>    contamination=&quot;auto&quot;,  # Use default threshold since training data is clean<br>    random_state=42,<br>    n_estimators=100,<br>)<br>model.fit(train_data)<br><br>print(&quot;Model trained on&quot;, n_samples, &quot;normal readings&quot;)</pre><p><strong>Result:</strong></p><pre>Model trained on 1000 normal readings</pre><p>The model learns the distribution of normal sensor readings. In production, anything that deviates significantly from this distribution gets flagged as an anomaly.</p><p>Now the interesting part — connecting this trained model to live data without reprocessing the entire table every time a new row arrives.</p><h3>Step 3: Deploy with deephaven.learn</h3><p>Now we connect the trained model to the live stream. The learn function uses a <strong>gather-compute-scatter</strong> pattern that processes only changed rows:</p><ol><li><strong>Gather</strong>: Extract data from table columns into NumPy arrays.</li><li><strong>Compute</strong>: Run your model on the gathered data.</li><li><strong>Scatter</strong>: Write results back into new table columns.</li></ol><pre>from deephaven.learn import learn, Input, Output<br>from deephaven.learn.gather import table_to_numpy_2d<br>import numpy as np<br><br>def gather_features(row_set, col_set):<br>    &quot;&quot;&quot;Gather sensor readings into a 2D NumPy array.&quot;&quot;&quot;<br>    return table_to_numpy_2d(row_set, col_set, np_type=np.double)<br><br>def predict_anomalies(features):<br>    &quot;&quot;&quot;Run the trained model on gathered features.&quot;&quot;&quot;<br>    # model.predict returns: 1 = normal, -1 = anomaly<br>    predictions = model.predict(features)<br>    return predictions<br><br>def scatter_predictions(result, idx):<br>    &quot;&quot;&quot;Extract individual predictions from the result array.&quot;&quot;&quot;<br>    return int(result[idx])<br><br># Apply the model to the streaming table<br>monitored_sensors = learn(<br>    table=sensor_stream,<br>    model_func=predict_anomalies,<br>    inputs=[Input([&quot;temperature&quot;, &quot;vibration&quot;, &quot;pressure&quot;], gather_features)],<br>    outputs=[Output(&quot;anomaly_flag&quot;, scatter_predictions, &quot;int&quot;)],<br>    batch_size=10,<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/616/0*btDf-wS6s0K82p5P.png" /></figure><p>Let’s break down what’s happening:</p><ul><li><strong>Input</strong> specifies which columns to gather and how. Our gather_features function converts the three sensor columns into a NumPy array.</li><li><strong>model_func</strong> is called on each batch of gathered data. Our model returns 1 for normal readings, -1 for anomalies.</li><li><strong>Output</strong> specifies how to scatter results back to the table. The scatter_predictions function extracts each prediction by index.</li><li><strong>batch_size</strong> controls how many rows are processed together. Larger batches are more efficient; smaller batches give faster response times.</li></ul><h3>Step 4: Watch it work</h3><p>The monitored_sensors table now updates in real-time. Every time new sensor readings arrive, deephaven.learn automatically:</p><ol><li>Gathers the new rows (not the entire table — just the changes).</li><li>Runs the model on that batch.</li><li>Scatters the predictions into the anomaly_flag column.</li></ol><p>Let’s add a human-readable status and filter to see just the anomalies:</p><pre># Add readable status with red highlighting for anomalous rows<br>with_status = monitored_sensors.update_view(<br>    [&quot;status = anomaly_flag == -1 ? `ANOMALY` : `normal`&quot;]<br>).format_columns(<br>    [<br>        &quot;temperature = anomaly_flag == -1 ? RED : NO_FORMATTING&quot;,<br>        &quot;vibration = anomaly_flag == -1 ? RED : NO_FORMATTING&quot;,<br>        &quot;pressure = anomaly_flag == -1 ? RED : NO_FORMATTING&quot;,<br>        &quot;status = anomaly_flag == -1 ? RED : NO_FORMATTING&quot;,<br>    ]<br>)<br><br># Filter to anomalies only<br>anomalies_only = with_status.where(&quot;anomaly_flag == -1&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Y5GpqAgVuG2N0SDw.png" /></figure><p>You now have three live tables:</p><ul><li>sensor_stream — raw sensor readings</li><li>with_status — readings with anomaly predictions</li><li>anomalies_only — only the flagged anomalies</li></ul><p>As new data streams in, anomalies are flagged within milliseconds of arrival.</p><h3>Why this matters</h3><p>Traditional batch inference doesn’t scale with streaming data. Every time new rows arrive, you’d have to reload the entire dataset and rerun predictions on everything. Latency grows with table size.</p><blockquote>Your table could have millions of historical rows, but you’re only running inference on the new ones.</blockquote><p>deephaven.learn only processes the rows that changed since the last update cycle. This makes it practical to deploy any trained model — scikit-learn, PyTorch, TensorFlow, XGBoost — on high-volume streams with millisecond-level latency.</p><p>The same pattern works beyond anomaly detection: fraud scoring, demand forecasting, image classification, quality control, and more. Train once, deploy on a stream, let it run.</p><h3>Try it yourself</h3><p>This tutorial requires a local installation — the browser demo doesn’t include scikit-learn. Follow the <a href="https://deephaven.io/core/docs/getting-started/quickstart/#pip">pip quickstart</a> to get started, then check the <a href="https://deephaven.io/core/pydoc/code/deephaven.learn.html">deephaven.learn docs</a> for the full API.</p><p><strong>What to try next:</strong> Replace Isolation Forest with your own model. The gather-compute-scatter pattern works with any Python function, including sklearn estimators, PyTorch modules, TensorFlow models, and XGBoost classifiers. Change model_func and you&#39;re done.</p><p>A few ideas:</p><ul><li>Connect to a live data feed instead of simulated sensors.</li><li>Add a rolling window aggregation before the model.</li><li>Chain multiple models (feature extraction → classification → alerting).</li></ul><p>The <a href="https://deephaven.io/core/docs/how-to-guides/use-scikit-learn/">scikit-learn integration guide</a> covers more patterns. Questions? Join us on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6264e75d607b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build multi-page apps with client-side routing]]></title>
            <link>https://medium.com/@deephavendatalabs/build-multi-page-apps-with-client-side-routing-20a4fa503a40?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/20a4fa503a40</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[web-development]]></category>
            <category><![CDATA[ui-design]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[apps]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Tue, 02 Jun 2026 17:53:31 GMT</pubDate>
            <atom:updated>2026-06-02T17:53:31.862Z</atom:updated>
            <content:encoded><![CDATA[<h3>Navigate between views, share URLs, and read URL parameters in deephaven.ui</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ehbQXfOqolmoJl2vZNEw4Q.png" /></figure><p>By Joseph Numainville</p><p>You can now split a deephaven.ui application into multiple pages, each with its own bookmarkable URL. Navigate between views without a full page reload, share links that open directly to a filtered dashboard, and let users hit the back button to retrace their steps. Route parameters, query strings, and browser history all work out of the box.</p><blockquote>Client-side routing in deephaven.ui lets you build multi-page applications with shareable URLs, route parameters, and browser-native navigation, all in Python.</blockquote><p>This blog will walk through what’s available and show how to build a simple multi-page app.</p><h3>What is client-side routing?</h3><p>If you’ve used frameworks like React Router or Next.js, the concept is familiar: you define a set of URL patterns (routes), and the framework renders the right component based on the current URL. The browser’s back and forward buttons work, users can bookmark specific pages, and you can share a URL that takes someone directly to a filtered view.</p><p>deephaven.ui now provides the same capability. The core pieces are:</p><ul><li><a href="https://deephaven.io/core/ui/docs/components/router/"><strong>ui.router</strong></a><strong> and </strong><strong>ui.route</strong>: Define a hierarchy of routes that map URL paths to components.</li><li><a href="https://deephaven.io/core/ui/docs/components/link/"><strong>ui.link</strong></a>: Render a clickable link that navigates within your app (using the new to prop) or to an external URL (using the preexisting href prop).</li><li><a href="https://deephaven.io/core/ui/docs/hooks/use_params/"><strong>ui.use_params()</strong></a>: Read route parameters (e.g., {symbol} from the path).</li><li><a href="https://deephaven.io/core/ui/docs/hooks/use_query_param/"><strong>ui.use_query_param(name)</strong></a>: Read a single query parameter from the URL.</li><li><a href="https://deephaven.io/core/ui/docs/hooks/use_set_query_param/"><strong>ui.use_set_query_param(name)</strong></a>: Get a setter function that updates a query parameter in the URL.</li></ul><p>There are additional hooks for more advanced use cases: <a href="https://deephaven.io/core/ui/docs/hooks/use_navigate/">use_navigate</a> for programmatic navigation from event handlers, <a href="https://deephaven.io/core/ui/docs/hooks/use_path/">use_path</a> to read the current path, <a href="https://deephaven.io/core/ui/docs/hooks/use_query_params/">use_query_params</a> to get all query parameters at once, and <a href="https://deephaven.io/core/ui/docs/hooks/use_url_components/">use_url_components</a> for low-level URL access.</p><p>Let’s build something with the core features.</p><h3>Example: stock dashboard with route and query parameters</h3><p>This app has two pages: a stock list with a filter, and a detail page for individual symbols. The filter is stored in the URL as a query parameter (?sym=DOG), and the detail page uses a route parameter (/stocks/FISH). Navigation uses ui.link.</p><pre>from deephaven import ui<br>import deephaven.plot.express as dx<br><br>_stocks = dx.data.stocks()<br><br><br>@ui.component<br>def stock_list():<br>    # Read the &quot;sym&quot; query param from the URL, None if not present<br>    sym = ui.use_query_param(&quot;sym&quot;)<br>    # Get a setter that updates the &quot;sym&quot; query param in the URL<br>    set_sym = ui.use_set_query_param(&quot;sym&quot;)<br><br>   filtered = _stocks.where(f&quot;Sym = sym&quot;) if sym else _stocks<br><br>    return ui.flex(<br>        ui.picker(<br>            &quot;All&quot;,<br>            &quot;FISH&quot;,<br>            &quot;DOG&quot;,<br>            &quot;CAT&quot;,<br>            label=&quot;Filter symbol&quot;,<br>            on_change=set_sym,<br>            selected_key=sym or &quot;All&quot;,<br>        ),<br>        # Link to a detail page using the route parameter<br>        ui.link(&quot;View FISH detail&quot;, to=&quot;/stocks/FISH&quot;),<br>        filtered,<br>        direction=&quot;column&quot;,<br>    )<br><br><br>@ui.component<br>def stock_detail():<br>    # Extract the {symbol} route parameter from the URL path<br>    params = ui.use_params()<br>    symbol = params[&quot;symbol&quot;]<br><br>    detail = _stocks.where(f&quot;Sym = `{symbol}`&quot;)<br><br>    return ui.flex(<br>        ui.link(&quot;Back to list&quot;, to=&quot;/&quot;),<br>        ui.text(f&quot;Showing data for {symbol}&quot;),<br>        detail,<br>        direction=&quot;column&quot;,<br>    )<br><br><br>@ui.component<br>def not_found():<br>    return ui.flex(<br>        ui.link(&quot;Go home&quot;, to=&quot;/&quot;),<br>        ui.text(&quot;Page not found&quot;),<br>        direction=&quot;column&quot;,<br>    )<br><br><br>@ui.component<br>def app():<br>    return ui.router(<br>        # index=True matches the root path exactly<br>        ui.route(index=True, element=stock_list),<br>        # Nested route: /stocks/{symbol}<br>        ui.route(<br>            ui.route(path=&quot;{symbol}&quot;, element=stock_detail),<br>            path=&quot;stocks&quot;,<br>        ),<br>        # Wildcard catches anything else<br>        ui.route(path=&quot;*&quot;, element=not_found),<br>    )<br><br><br>app = app()</pre><p>That’s it. With this code running:</p><ul><li>/ renders the stock list. Picking a symbol from the picker updates the URL to /?sym=DOG, filtering the table. Share that URL of a dashboard and the recipient sees the same filter applied.</li><li>/stocks/FISH renders the detail page. ui.use_params() extracts &quot;FISH&quot; from the {symbol} segment.</li><li>/anything-else renders the &quot;not found&quot; page thanks to the * wildcard.</li><li>ui.link handles all navigation without full page reloads, and the browser&#39;s back button works as expected.</li></ul><h3>Why use this?</h3><p>Before routing, the only way to build multi-page deephaven.ui apps was to toggle visibility with state variables or use tab components. That works, but you can&#39;t share a URL that takes someone to a specific page and the browser&#39;s back button doesn&#39;t undo navigation.</p><p>With routing, the URL <em>is</em> the state. Bookmarks work, links are shareable, and each page is its own component.</p><h3>Things to keep in mind</h3><ul><li>Always include a wildcard route (path=&quot;*&quot;) so unmatched paths render something useful rather than an error.</li><li>Route parameters are always strings. Validate them before using them in queries.</li><li>Deephaven reserves the path segment /-/ and query parameters starting with _ or dh. Don&#39;t use those in your app.</li></ul><h3>Get started</h3><p>The router and all navigation hooks are available now in deephaven-plugin-ui. Install or upgrade:</p><pre>pip install --upgrade deephaven-plugin-ui</pre><p>For full API details, check out the docs for <a href="https://deephaven.io/core/ui/docs/components/router/">ui.router</a>, <a href="https://deephaven.io/core/ui/docs/components/link/">ui.link</a>, <a href="https://deephaven.io/core/ui/docs/hooks/use_params/">use_params</a>, <a href="https://deephaven.io/core/ui/docs/hooks/use_query_param/">use_query_param</a>, <a href="https://deephaven.io/core/ui/docs/hooks/use_set_query_param/">use_set_query_param</a>, <a href="https://deephaven.io/core/ui/docs/hooks/use_navigate/">use_navigate</a>, <a href="https://deephaven.io/core/ui/docs/hooks/use_path/">use_path</a>, <a href="https://deephaven.io/core/ui/docs/hooks/use_query_params/">use_query_params</a>, and <a href="https://deephaven.io/core/ui/docs/hooks/use_url_components/">use_url_components</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=20a4fa503a40" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Skill evals for measurable agent self-improvement]]></title>
            <link>https://medium.com/@deephavendatalabs/skill-evals-for-measurable-agent-self-improvement-225f56479b0b?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/225f56479b0b</guid>
            <category><![CDATA[anthropic-claude]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[web-development]]></category>
            <category><![CDATA[claude-code]]></category>
            <category><![CDATA[claude]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Fri, 29 May 2026 18:39:26 GMT</pubDate>
            <atom:updated>2026-05-29T18:39:26.803Z</atom:updated>
            <content:encoded><![CDATA[<h3>Customizing Claude Skill Creator for iterative skill development</h3><p>By Don McKenzie</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*e38-safewVBWYqFyQ6JJbw.jpeg" /></figure><p>Skills are re-usable markdown instructions that teach agents how to accomplish specific tasks. But how do you measure whether your instructions actually help agents complete the task — rather than just add noise — especially given that agents are non-deterministic? The answer is to evaluate behaviour in aggregate across many runs, a practice the AI community calls <a href="https://www.anthropic.com/engineering/demystifying-evals-for-ai-agents">“evals”</a>.</p><p>At Deephaven, we have a public skill <a href="https://github.com/deephaven/agent-skills">deephaven-core-query-writing</a> that helps agents to write real-time Deephaven queries and build dashboards with live data. The following article discusses how we use evals to measure skill performance, and how we iteratively improve our skill using agent-driven recommendations.</p><blockquote>How do you measure whether your instructions actually help agents — rather than just add noise?</blockquote><p><em>Note: These skills are in early development and may give incomplete or inaccurate recommendations to agents. Feedback and contributions are welcome to help improve these skills. The skills include guidance for general Deephaven query writing, joins, aggregations, update_by, time ops, ui, plotting, and CSV importing.</em></p><h3>What to measure</h3><p>Evals can be written for a variety of purposes (from tracking accuracy to making sure updates don’t break things). The first step to writing good evals is to determine what you intend to measure. In our case, my primary goal is to compare the success rate of an agent writing functional deephaven code with and without our skill.</p><p>However, simply measuring the pass rate doesn’t give the full picture. Generally, one of the most successful ways to leverage coding agents is to include a feedback loop in your prompts so agents can iteratively work towards a goal and measure whether they have achieved it. If we give both our “with skill” and “without skill” agents the ability to iteratively run queries towards a goal, the latest <a href="https://huggingface.co/blog/daya-shankar/sota-ai-models">SOTA</a> models may both succeed. In that case, the question becomes how do we decide who did it better? That’s where measuring additional metrics like time taken, tokens used, estimated cost, and number of turns helps, beyond just pass rate.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8b6QKgy8PciX_Be4.jpg" /></figure><h3>Building a test harness</h3><p>The way we’ve set up our test harness specifically for with vs without skills was aided by the Claude <a href="https://github.com/anthropics/skills/blob/main/skills/skill-creator/SKILL.md">skill-creator</a> skill release. We were already testing our skills before its release, but we have since borrowed some of its report-generation features. Consider this a playbook for vibe coding your way through building your own harness.</p><p>We start by constructing a set of prompts that represent our “evals”, which are representative tasks that users may try to accomplish with our skills, and then add expectations that represent how we measure conformance towards a pass rate. For us, that involves trying to perform some level of analysis on data (we use the 20 most popular CSV files on <a href="https://www.kaggle.com/datasets?fileType=csv">kaggle</a>), writing Deephaven queries against that data, and then producing a dashboard. Deephaven is primarily used for real-time data, but for testing, simply using static CSV data keeps it more repeatable. Using real-world datasets ensures we have representative coverage of the data types one might encounter in the wild.</p><p>A single eval might look something like this:</p><pre>{<br>  &quot;prompt&quot;: &quot;I have student performance data split across two files — math course students at evals/data/uciml--student-alcohol-consumption/student-mat.csv and Portuguese course students at evals/data/uciml--student-alcohol-consumption/student-por.csv. Both files share the same schema. Build a Deephaven dashboard that loads both tables, combines them with a join or merge operation, and shows how alcohol consumption (Dalc for weekday, Walc for weekend) correlates with final grades (G3). Include a scatter plot of alcohol vs grades, a bar chart of average grades by alcohol level, and a picker to switch between math and Portuguese students. Use deephaven.ui.dashboard.&quot;,<br>  &quot;expectations&quot;: [<br>    &quot;The script executes without errors via dh exec&quot;,<br>    &quot;The dashboard renders successfully via dh render&quot;,<br>    &quot;The script reads both CSV files&quot;,<br>    &quot;The script combines or compares the two datasets in some way&quot;,<br>    &quot;The dashboard includes a scatter or correlation chart relating alcohol and grades&quot;,<br>    &quot;The dashboard includes aggregated statistics (e.g. avg grades by alcohol level)&quot;,<br>    &quot;The dashboard has a picker or filter to select between math and Portuguese courses&quot;,<br>    &quot;The rendered dashboard contains no panels titled &#39;Untitled&#39;&quot;<br>  ]<br>}</pre><p>Collectively, the prompt and expectations represent our evals. We repeat this pattern across a variety of datasets and prompts to produce a set of evals. Each eval is then inserted into a template that includes generic tooling instructions to provide a feedback loop, such as how to run Deephaven code, how to get table output and exit codes, how to view the dashboard, file paths, and where to place output. The prompt represents the goal given to the agent. For the “with skill” eval runs, they are forced to read the skills entry point file. For the “without skill” run, they do not have access to the skill files and must rely on the model’s inherent knowledge of Deephaven query writing.</p><p>The expectations are not provided to the agent and are used to assess the agent’s work afterward. Expectations are simple statements that can be either deterministically graded (e.g., executes the query with exit code 0) or LLM-graded (e.g., the dashboard includes aggregated statistics, such as average grades by alcohol level). In our case, we used a combination of the two.</p><h3>Capturing output for grading</h3><p>The output of your evals is the work product. For each eval, we capture:</p><ul><li>script.py written by the agent</li><li>the last exit code of the execution</li><li>an accessibility tree of the rendered dashboard</li><li>a screenshot</li><li>the full conversation log of the agent session</li></ul><p>We create a folder for each run of the eval set, and within that is a folder for each individual eval run containing its output.</p><p>We also record model details (ex. Opus, Sonnet), model effort, wall clock time of each agent session, and the total skills token count at time of the run. This is useful for referring back between runs later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*OxZZUj_9wMf7xHTa.jpg" /></figure><h3>Capturing the conversation logs</h3><p>For our evals, we are running using a headless Claude agent. For each run, we copy the JSONL of the session log. This is important for self-reflection later. While iteratively developing, having a large catalog of past runs can help agents dig into specific, commonly encountered failure points. It can also be useful to allow humans to scan the logs to make sure no unexpected failures occur due to configuration issues. For example, because we use a team plan, my agents automatically inherited access to certain MCP servers, which allowed headless agents to cheat and search our docs, despite the MCP not being manually installed. This was unexpected and required a specific flag to disable, but would never have been discovered if it weren’t for manual log review.</p><p>Session logs also contain detailed token usage throughout the session, including input, output, and cached tokens, so capturing the logs provides lots of value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*2QPkCJVaQJTHIflj.jpg" /></figure><h3>Grading</h3><p>After each eval run, an independent agent has instructions to grade the expectations. It is given access to the work output (script, rendered output, etc) and the expectations and returns a structured JSON answer that is either pass or fail for each eval’s expectation. Agents seem to be reasonably okay judges, given specific criteria. Agents must also give reasoning behind the pass/fail in the form of a single sentence citing evidence. This can help you debug incorrect passes or failures to narrow or refine your expectations. LLMs grading their own work can be imperfect, but in aggregate can still produce meaningful results.</p><h3>Agent recommendations</h3><p>This is where the fun begins. Another independent agent takes the grading, the output, the conversation history, and the skill files, and is given an instruction to analyze why it failed. What was missing from the skill files? Where did it stumble or have to spend additional iterations along the way to producing a result? It creates a simple markdown report with recommendations of what worked and what didn’t for each eval.</p><p>After all recommendations complete per eval, yet another agent is given all the summaries and produces a final summary report of common failures across evals. These recommendations can then be used to iteratively improve the skills.</p><h3>Summary stats</h3><p>Alongside the grading, a Python script walks through the session logs and pulls out the metrics we actually care about for each run. Token usage is broken out into input, output, and cached, since cached tokens are billed differently and the with-skill runs lean heavily on caching. The script also records the turn count, wall-clock time, the model and effort used, and the exit code of the final script execution. These are joined back to the grading results so that every eval has both a pass/fail breakdown and the cost to get there.</p><p>The aggregation step then combines this into a single table per run, comparing with-skill and without-skill side by side. While useful in aggregate, in practice we end up looking at the per-eval breakdown more than the overall average, because a single runaway eval that hits the turn limit can drag the average down significantly, even when the median experience is fine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Z56OfLb0YZF41Nfl.jpg" /></figure><h3>How it’s run</h3><p>A single Python script runs each stage in sequence. The script:</p><ul><li>spins up the agent against an eval,</li><li>captures its output,</li><li>validates that the output is real working code,</li><li>parses the session log for metrics,</li><li>grades against the expectations,</li><li>generates recommendations,</li><li>and then aggregates everything into a report.</li></ul><p>Since each eval is independent, they can be run in parallel to save time, with only the later stages requiring all evals to be complete first.</p><p>We use Claude in headless mode (claude -p) for agent runs. The main reason is that it writes a complete session log to disk, which we can later parse for token counts, the model used, the number of turns, and the full transcript. The same pipeline could be pointed at any headless coding agent that produces a parseable log, and future explorations will involve testing against Codex and Antigravity.</p><p><em>Note: Claude recently changed to API-billed instead of a plan, so we may change this approach.</em></p><h3>Iterative loop</h3><p>Once the harness is in place, the interesting question isn’t really whether it works, but what you do with the recommendations. In practice, the loop looks like this: Run the evals, open the report, read the per-eval recommendations alongside the dashboard screenshots and generated scripts, and then decide as a human what’s worth changing in the skill. Usually, only one or two recommendations across the whole batch are worth acting on. The rest are either too specific, already covered, or pointing at a failure that has nothing to do with the skill content.</p><p>The recommendations tend to be too specific by default. An agent that just watched a single eval stumble will happily suggest “add an example of joining a CSV-loaded table to a Kafka stream with a timestamp tolerance”. That might fix the one eval and add a few hundred tokens to the input cost of every agent forever. The human job in the loop is to translate specific failures into general lessons.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*10HCfqPnUgZMei4f.jpg" /></figure><h3>Karpathy loop</h3><p>I wanted to try to remove the human from this loop using an <a href="https://github.com/karpathy/autoresearch">autoresearch</a>-based approach. Run the evals, generate the recommendations, have an agent edit the skill files, run the evals again, repeat.</p><p>It didn’t work. I suspect this is for two reasons that compound elsewhere, too.</p><ol><li>The recommendation step has a strong bias toward adding rather than removing. Asking an agent what’s missing after a partial failure almost never returns “nothing, this failure isn’t about the skill”, even when that would be the right answer. The recommendation agents are also more likely to output additional suggestions than to say there are no further changes needed, which feeds the same direction.</li><li>My loop step had no real sense of the token budget. It dutifully appends every suggestion, and the next round’s recommendations then point at the new failure modes introduced by the now-larger skill. Within a few iterations, the skill had grown considerably, and the pass rate hadn’t meaningfully moved.</li></ol><blockquote>The thing I took away from this is that improving a skill is a multi-objective optimization (pass rate up, token count down, complexity down). I wasn’t able to successfully get an agent to optimize across multiple constraints.</blockquote><p>There might be a version of this that works with a more carefully constructed recommendation prompt, but for now human review seems best.</p><h3>An aside “rendering” dashboards to text</h3><p>It is worth a quick aside on what makes the validation half of all this practical. Earlier versions of the harness validated dashboards by having the agent write a Playwright test, run it, parse the output, and debug DOM selectors when things didn’t line up. A meaningful share of agent turns went into the test-writing loop, rather than the task we actually cared about.</p><p>What replaced it is a small CLI we built for headlessly rendering Deephaven UI components as a text tree, and chaining interactions against them (clicks, selections, snapshots of the rendered accessibility tree, and runtime error diagnosis). From the harness’s point of view, it is a single command that returns either “the dashboard rendered and looks like this” or “it broke and here’s why”. The grading agent works directly off this modified accessibility tree, which is structured enough to answer questions like “does this dashboard have a picker” or “is there a scatter plot” without ever touching a real browser. Screenshots still get taken for the human-facing review page, but no grading depends on pixels.</p><p>This made iteration loops significantly faster and easier, as it no longer needed to write custom Playwright code or use slow browser interaction. If validation requires a generated test for every eval, you’ll spend most of your eval’s time debugging the test harness instead of the skill.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5mSwwmggU8JO4pom.jpg" /></figure><h3>Results</h3><p>The headline result from the most recent runs is that the skill produces <strong>better-looking dashboards, with fewer errors</strong>, and takes less time to do so. This comes at the expense of slightly higher token usage due to reading skill files, and carrying that token weight through the conversation. However, I think for a user trying to iteratively refine a dashboard, actual token usage will likely be dramatically lower than without the skill.</p><p>What is more interesting than the average is the shape of the per-eval results. Most evals pass at or near 100% in both configurations, because the underlying model already knows enough Deephaven from training to handle the basic shape of a query. The skill earns its keep on a small number of evals that consistently fail without it, typically the ones that lean on specific join semantics, less common UI components, or behaviors where the natural-sounding API isn’t the one that actually works.</p><p>I also had a few incidental findings from running the same evals across different models. With a tight feedback loop in place (error output, render results, exit codes), even the smaller models can usually complete the task. They just take many more turns to brute-force their way there, and the skill helps them less than you would expect, because their bottleneck is reasoning rather than knowledge. Moving from Sonnet to Opus noticeably reduces total token usage per eval, because the stronger model one-shots more often rather than stumbling through an error-fix loop. Opus 4.7 one-shots more reliably than 4.6, but uses slightly more tokens on average, spending more on thinking up front and less on retries (and also differences in how it computes token usage).</p><p>The other result worth mentioning is the token-reduction pass we did on the skill itself. A round of compressing prose or tables into comma-separated lists and removing redundant examples cut the overall skill size by roughly a quarter, with no measurable change in pass rate. That kind of change is the one I trust the harness for the most. It is easy to delete things from a skill if you have a way to confirm afterward that nothing important went with them.</p><h3>Try it yourself</h3><p>The skill and eval harness are public at <a href="https://github.com/deephaven/agent-skills">deephaven/agent-skills</a>. Fork it, adapt it for your own skills, and let us know what you find.</p><p>Join us on <a href="https://deephaven.io/slack/">Slack </a>if you have questions or want to share your results.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=225f56479b0b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Finding the Hidden Risks: Portfolio Stress Detection in Private Credit]]></title>
            <link>https://medium.com/@deephavendatalabs/finding-the-hidden-risks-portfolio-stress-detection-in-private-credit-ee33566f3391?source=rss-172b05621ec7------2</link>
            <guid isPermaLink="false">https://medium.com/p/ee33566f3391</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[finance]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[portfolio-management]]></category>
            <category><![CDATA[stocks]]></category>
            <dc:creator><![CDATA[Deephaven Data Labs]]></dc:creator>
            <pubDate>Thu, 28 May 2026 18:34:48 GMT</pubDate>
            <atom:updated>2026-05-28T18:34:48.567Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to identify vulnerable borrowers and concentration risks before they become public problems</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vMR1XfsHHNX88bIIKs__ZQ.png" /></figure><p>Private credit is facing a reckoning. Blue Owl recently restricted withdrawals from one of its funds amid mounting concerns over bad loans. Defaults are rising, particularly among smaller borrowers. Fraud allegations at portfolio companies are making headlines. Uncertainty grows every time an analyst invokes the “cockroach theory” — if you see one problem loan, there are likely many others hiding in your portfolio. Call them stockroaches — borrowers that look stable in quarterly reports until they don’t, and rarely show up alone.</p><p>If you’re managing a private credit fund right now, you’re asking yourself the same question every day: What else is hiding in my portfolio? You have 60+ borrowers across multiple sectors. Interest coverage ratios are hovering around 2.0x, leaving almost no margin for error. Nearly 40% of your portfolio might be concentrated in software and tech. Your Limited Partners (LPs) are getting nervous, with some funds already seeing redemption requests.</p><p>Traditional quarterly reporting doesn’t cut it anymore. By the time you receive financials — 30 to 45 days after quarter-end — you’re looking at data that’s already 4–5 months old. You need to identify patterns, stress points, and concentration risks across your entire portfolio. You need to know which borrowers look like the ones that already defaulted. You need to demonstrate to LPs that you have a handle on risk.</p><blockquote>In a market where one cockroach suggests many others, the funds that survive will be those that can see around corners.</blockquote><p>In this post, we’ll build a portfolio stress detection system using Deephaven and realistic quarterly financial data. You’ll learn how to segment your portfolio by risk factors, run stress tests across multiple rate scenarios, quantify sector concentration, and use pattern matching to find borrowers that look like the ones that already defaulted.</p><h3>Understanding the current market stress</h3><p>Private credit funds are navigating five interconnected risks that are testing portfolio management like never before.</p><ol><li>Small borrower vulnerability: Borrowers with less than $25M in EBITDA are most exposed to rising rates and economic stress. These companies often lack the financial cushion to weather downturns, and they’re showing the highest default rates in the current environment.</li><li>Tight interest coverage: With coverage ratios stabilizing around 2.0x, there’s little room for error. Any EBITDA decline or rate increase could trigger covenant breaches. The margin between compliance and default has never been thinner.</li><li>Sector concentration: Software and tech companies make up nearly 40% of many private credit portfolios. Sector-wide stress could cascade across your entire book, and recent tech sector challenges are raising red flags.</li><li>Hidden correlations: The “cockroach theory” suggests that visible problems are just the tip of the iceberg. Borrowers with similar profiles to known problem loans may be at risk, but traditional quarterly reviews don’t surface these patterns.</li><li>LP confidence: Growing redemptions mean you need to demonstrate strong risk management and portfolio transparency to retain investor confidence. Static quarterly reports aren’t enough anymore.</li><li>Deephaven makes portfolio-wide analysis tractable: the same table operations that work for one borrower work for sixty, and adding more scenarios or metrics doesn’t require more code.</li></ol><h3>Loading your portfolio data</h3><p>We’ll work with three types of data that private credit funds already collect as part of standard operations.</p><ul><li>Quarterly borrower financials: These arrive 30–45 days after quarter-end. Borrowers submit financial statements showing EBITDA, total debt, interest expense, cash balances, and other key metrics. These typically come as PDF or Excel files that get manually entered into spreadsheets.</li><li>Loan terms and covenants: These come from your credit agreements. You track loan amounts, interest rates, and covenant thresholds (maximum leverage ratios, minimum coverage ratios, minimum liquidity requirements) for each borrower.</li><li>Historical data: This accumulates from past quarterly reports. For trend analysis, we’ll use the past 8 quarters, but the analyses work fine with just the current quarter if you’re getting started.</li></ul><p>Deephaven reads data from CSV, Parquet, Excel, and most other common formats. If your quarterly data lives in spreadsheets or gets exported from your loan management system, you can load it directly without conversion.</p><p><em>Note: This analysis uses quarterly data that private credit funds already collect. No real-time feeds or proprietary data sources required. The sample data below is </em><strong><em>randomly generated</em></strong><em> — specific numbers, sector breakdowns, and borrower counts are illustrative only and will differ from any real portfolio.</em></p><h4>Generating sample portfolio data</h4><p>For this example, we’ll create realistic portfolio data directly in Deephaven. In practice, you’d load this from your loan management system or consolidated Excel files.</p><p><a href="https://controller.try-dh.demo.community.deephaven.io/">Deephaven Data Labs Demo System</a></p><pre>from deephaven import new_table<br>from deephaven.column import string_col, int_col, double_col<br>from deephaven import write_csv<br>import random<br><br># Create Q4 2025 borrower financials (62 borrowers)<br>borrower_ids = [f&quot;B{i:03d}&quot; for i in range(1, 63)]<br>quarters = [&quot;Q4_2025&quot;] * 62<br><br># Generate realistic financial metrics<br>random.seed(42)<br>n_small, n_medium, n_large = 34, 19, 9<br>ebitdas = (<br>    [random.randint(12_000_000, 24_000_000) for _ in range(n_small)]<br>    + [random.randint(25_000_000, 49_000_000) for _ in range(n_medium)]<br>    + [random.randint(50_000_000, 75_000_000) for _ in range(n_large)]<br>)<br>random.shuffle(ebitdas)<br>total_debts = [int(ebitda * random.uniform(3.0, 5.5)) for ebitda in ebitdas]<br>coverage_targets = [random.uniform(1.8, 3.5) for _ in range(62)]<br>interest_expenses = [<br>    int(ebitda / coverage) for ebitda, coverage in zip(ebitdas, coverage_targets)<br>]<br>cash_balances = [int(ebitda * random.uniform(0.4, 0.7)) for ebitda in ebitdas]<br>current_liabilities = [int(cash * random.uniform(0.6, 0.9)) for cash in cash_balances]<br><br>borrower_financials = new_table(<br>    [<br>        string_col(&quot;BorrowerID&quot;, borrower_ids),<br>        string_col(&quot;Quarter&quot;, quarters),<br>        int_col(&quot;EBITDA&quot;, ebitdas),<br>        int_col(&quot;TotalDebt&quot;, total_debts),<br>        int_col(&quot;InterestExpense&quot;, interest_expenses),<br>        int_col(&quot;CashBalance&quot;, cash_balances),<br>        int_col(&quot;CurrentLiabilities&quot;, current_liabilities),<br>    ]<br>)<br><br># Create loan terms and covenants<br>borrower_names = (<br>    [<br>        &quot;TechVenture_LLC&quot;,<br>        &quot;DataStream_Corp&quot;,<br>        &quot;CloudSys_Inc&quot;,<br>        &quot;MediCare_Solutions&quot;,<br>        &quot;RetailPro_Group&quot;,<br>        &quot;ManufactureTech_Co&quot;,<br>        &quot;SmallBiz_Services&quot;,<br>        &quot;IndustrialParts_Inc&quot;,<br>        &quot;HealthTech_Innovations&quot;,<br>        &quot;Enterprise_Software&quot;,<br>        &quot;ACME_Corp&quot;,<br>        &quot;FinanceApp_Systems&quot;,<br>    ]<br>    + [&quot;TechStart_Inc&quot;, &quot;Retail_Co&quot;]<br>    + [f&quot;Borrower_{i}&quot; for i in range(15, 63)]<br>)<br><br>industries = (<br>    [&quot;Software&quot;] * 12  # TechVenture_LLC through FinanceApp_Systems<br>    + [&quot;Technology&quot;]  # TechStart_Inc<br>    + [&quot;Retail&quot;]  # Retail_Co<br>    + [&quot;SaaS&quot;] * 8  # generic borrowers<br>    + [&quot;Technology&quot;] * 5  # generic borrowers<br>    + [&quot;IT Services&quot;] * 3  # generic borrowers<br>    + [&quot;Healthcare&quot;] * 8  # generic borrowers<br>    + [&quot;Manufacturing&quot;] * 11  # generic borrowers<br>    + [&quot;Retail&quot;] * 6  # generic borrowers<br>    + [&quot;Business Services&quot;] * 7  # generic borrowers<br>)<br><br>loan_amounts = [random.randint(4_000_000, 11_000_000) for _ in range(62)]<br>interest_rates = [<br>    5.0<br>    if ind in [&quot;Software&quot;, &quot;Technology&quot;, &quot;SaaS&quot;]<br>    else 5.5<br>    if ind == &quot;Healthcare&quot;<br>    else 6.0<br>    for ind in industries<br>]<br>max_leverage_ratios = [<br>    4.5<br>    if ind in [&quot;Software&quot;, &quot;Technology&quot;, &quot;Manufacturing&quot;]<br>    else 5.0<br>    if ind in [&quot;SaaS&quot;, &quot;Healthcare&quot;, &quot;IT Services&quot;]<br>    else 6.0<br>    for ind in industries<br>]<br>min_coverage_ratios = [<br>    2.5<br>    if ind in [&quot;Software&quot;, &quot;Technology&quot;, &quot;Manufacturing&quot;]<br>    else 2.0<br>    if ind in [&quot;SaaS&quot;, &quot;Healthcare&quot;, &quot;IT Services&quot;]<br>    else 1.5<br>    for ind in industries<br>]<br>min_liquidities = [int(loan * 1.2) for loan in loan_amounts]<br><br>loan_terms = new_table(<br>    [<br>        string_col(&quot;BorrowerID&quot;, borrower_ids),<br>        string_col(&quot;BorrowerName&quot;, borrower_names),<br>        string_col(&quot;Industry&quot;, industries),<br>        int_col(&quot;LoanAmount&quot;, loan_amounts),<br>        double_col(&quot;InterestRate&quot;, interest_rates),<br>        double_col(&quot;MaxLeverageRatio&quot;, max_leverage_ratios),<br>        double_col(&quot;MinCoverageRatio&quot;, min_coverage_ratios),<br>        int_col(&quot;MinLiquidity&quot;, min_liquidities),<br>    ]<br>)<br><br># Save to CSV for use in the analysis below<br>write_csv(borrower_financials, &quot;/data/Q4_2025_portfolio_financials.csv&quot;)<br>write_csv(loan_terms, &quot;/data/loan_terms.csv&quot;)</pre><p>This creates 62 borrowers with realistic financial metrics and loan terms. The data includes:</p><ul><li><strong>Sector concentration</strong>: ~46% in tech-related sectors (Software, SaaS, Technology, IT Services)</li><li><strong>Size distribution</strong>: Mix of small (&lt;$25M EBITDA), medium ($25–50M), and large (&gt;$50M) borrowers — small borrowers are the most numerous segment (~55%)</li><li><strong>Risk profiles</strong>: Varying leverage ratios (3.0x — 5.5x) and covenant thresholds by industry</li><li><strong>Problem borrowers</strong>: ACME_Corp, TechStart_Inc, and others for pattern matching examples</li></ul><p>The write_csv calls save these tables to the /data folder so we can load them in the next step. In practice, you&#39;d load directly from the Excel files or CSV exports your borrowers and loan management system already produce.</p><p>Now let’s load the data and set up our analysis environment.</p><p><em>Note: The sample data above is randomly generated for illustration. Any specific counts or ratios discussed in the analysis sections below come from this synthetic data — they are not benchmarks and will differ from a real portfolio.</em></p><pre>from deephaven import read_csv, merge<br>from deephaven.column import string_col, double_col, int_col<br>from deephaven import agg<br>from deephaven.updateby import rolling_avg_tick, delta<br>import deephaven.plot.express as dx<br>from deephaven.pandas import to_pandas<br><br># Load Q4 2025 quarterly financials — in practice these come as Excel or CSV exports<br>borrower_financials = read_csv(&quot;/data/Q4_2025_portfolio_financials.csv&quot;)<br><br># Load loan terms and covenant thresholds<br>loan_terms = read_csv(&quot;/data/loan_terms.csv&quot;)<br><br>TOTAL_EXPOSURE = to_pandas(loan_terms)[&quot;LoanAmount&quot;].sum()<br><br># Join financials with loan terms to create complete portfolio view<br>portfolio = borrower_financials.natural_join(<br>    loan_terms,<br>    on=[&quot;BorrowerID&quot;],<br>    joins=[<br>        &quot;BorrowerName&quot;,<br>        &quot;Industry&quot;,<br>        &quot;LoanAmount&quot;,<br>        &quot;InterestRate&quot;,<br>        &quot;MaxLeverageRatio&quot;,<br>        &quot;MinCoverageRatio&quot;,<br>        &quot;MinLiquidity&quot;,<br>    ],<br>)<br><br># Calculate key ratios<br>portfolio = portfolio.update_view(<br>    [<br>        &quot;LeverageRatio = TotalDebt / EBITDA&quot;,<br>        &quot;CoverageRatio = EBITDA / InterestExpense&quot;,<br>    ]<br>)</pre><p>This gives us a complete view of the portfolio with both financial metrics and covenant requirements in one table. Every row is one borrower with their current financials and loan terms.</p><p>Now let’s get a quick overview of the portfolio to understand what we’re working with.</p><pre># Portfolio summary statistics<br>portfolio_summary = portfolio.agg_by(<br>    [<br>        agg.count_(&quot;TotalBorrowers&quot;),<br>        agg.sum_([&quot;TotalExposure = LoanAmount&quot;]),<br>        agg.avg([&quot;AvgLeverageRatio = LeverageRatio&quot;]),<br>        agg.avg([&quot;AvgCoverageRatio = CoverageRatio&quot;]),<br>        agg.min_([&quot;WorstCoverageRatio = CoverageRatio&quot;]),<br>        agg.max_([&quot;WorstLeverageRatio = LeverageRatio&quot;]),<br>    ]<br>)</pre><p>The portfolio summary tells you the averages — but the cockroach theory is about what averages conceal. The minimum coverage ratio and maximum leverage already flag outliers worth investigating.</p><h3>Identifying small borrower vulnerability</h3><p>Borrowers with less than $25M in EBITDA are showing the highest default rates in the current market. These companies typically have less financial cushion, more sensitivity to rate changes, and fewer resources to weather economic stress. Let’s segment our portfolio by borrower size to understand our exposure to this vulnerable group.</p><p>We’ll create three size segments: small (under $25M EBITDA), medium ($25–50M), and large (over $50M). Then we’ll calculate average metrics for each segment to see how risk profiles differ.</p><pre># Create size segments and risk flags<br>portfolio = portfolio.update_view(<br>    [<br>        &quot;SizeSegment = EBITDA &lt; 25_000_000 ? `Small (&lt;$25M)` : &quot;<br>        + &quot;(EBITDA &lt; 50_000_000 ? `Medium ($25-50M)` : `Large (&gt;$50M)`)&quot;,<br>        &quot;IsHighLeverage = LeverageRatio &gt; 4.0 ? 1 : 0&quot;,<br>        &quot;IsLowCoverage = CoverageRatio &lt; 2.5 ? 1 : 0&quot;,<br>    ]<br>)<br><br># Analyze metrics by segment<br>segment_analysis = portfolio.agg_by(<br>    [<br>        agg.count_(&quot;BorrowerCount&quot;),<br>        agg.sum_([&quot;TotalExposure = LoanAmount&quot;]),<br>        agg.avg([&quot;AvgLeverageRatio = LeverageRatio&quot;]),<br>        agg.avg([&quot;AvgCoverageRatio = CoverageRatio&quot;]),<br>        agg.sum_([&quot;HighLeverage = IsHighLeverage&quot;]),<br>        agg.sum_([&quot;LowCoverage = IsLowCoverage&quot;]),<br>    ],<br>    &quot;SizeSegment&quot;,<br>)<br><br>segment_analysis = segment_analysis.update_view(<br>    f&quot;PercentOfPortfolio = (TotalExposure / {TOTAL_EXPOSURE}) * 100&quot;<br>)</pre><p>The results reveal a concerning pattern: small borrowers carry the highest average leverage ratio of any segment and the largest share of borrowers in elevated-risk territory. They also make up more than half the portfolio by borrower count. The most stressed segment is also the most numerous — and the hardest to monitor individually.</p><p>Now let’s identify which specific small borrowers are showing the highest risk indicators.</p><pre># Flag small borrowers with elevated risk<br>high_risk_small = portfolio.where(<br>    [&quot;EBITDA &lt; 25_000_000&quot;, &quot;LeverageRatio &gt; 4.0 || CoverageRatio &lt; 2.5&quot;]<br>).sort_descending([&quot;LeverageRatio&quot;])<br><br># Show key metrics for these borrowers<br>high_risk_small_summary = high_risk_small.view(<br>    [<br>        &quot;BorrowerName&quot;,<br>        &quot;Industry&quot;,<br>        &quot;EBITDA&quot;,<br>        &quot;LeverageRatio&quot;,<br>        &quot;CoverageRatio&quot;,<br>        &quot;LoanAmount&quot;,<br>    ]<br>)</pre><p>This filters down to a subset of small borrowers with either high leverage or low coverage. The combination of small size and stressed metrics makes these the most likely candidates for near-term problems. Small borrowers rarely default in isolation — one is a signal worth tracing across the rest of the segment.</p><blockquote>Small borrowers may look like a smaller credit risk because exposure is concentrated elsewhere — but their metrics often tell a different story.</blockquote><h3>Stress testing interest coverage</h3><p>With coverage ratios hovering around 2.0x across the portfolio, there’s almost no margin for error. Rising interest rates directly impact interest expense, and even a modest rate increase could push borrowers into covenant breaches. Let’s model what happens to coverage ratios under different rate scenarios.</p><p>We’ll test three scenarios: a 50 basis point increase (modest), 100 basis points (moderate), and 200 basis points (severe). For each scenario, we’ll calculate the new interest expense, the resulting coverage ratio, and whether the borrower would breach their covenant.</p><pre># Model multiple rate increase scenarios<br>stress_scenarios = portfolio.update_view(<br>    [<br>        # Current state<br>        &quot;CurrentInterestExpense = InterestExpense&quot;,<br>        &quot;CurrentCoverageRatio = EBITDA / CurrentInterestExpense&quot;,<br>        # Scenario 1: +50 basis points (rate rises by 0.5 percentage points)<br>        &quot;InterestExpense_50bp = InterestExpense * (InterestRate + 0.5) / InterestRate&quot;,<br>        &quot;CoverageRatio_50bp = EBITDA / InterestExpense_50bp&quot;,<br>        &quot;Breach_50bp = CoverageRatio_50bp &lt; MinCoverageRatio&quot;,<br>        # Scenario 2: +100 basis points (rate rises by 1.0 percentage points)<br>        &quot;InterestExpense_100bp = InterestExpense * (InterestRate + 1.0) / InterestRate&quot;,<br>        &quot;CoverageRatio_100bp = EBITDA / InterestExpense_100bp&quot;,<br>        &quot;Breach_100bp = CoverageRatio_100bp &lt; MinCoverageRatio&quot;,<br>        # Scenario 3: +200 basis points (rate rises by 2.0 percentage points)<br>        &quot;InterestExpense_200bp = InterestExpense * (InterestRate + 2.0) / InterestRate&quot;,<br>        &quot;CoverageRatio_200bp = EBITDA / InterestExpense_200bp&quot;,<br>        &quot;Breach_200bp = CoverageRatio_200bp &lt; MinCoverageRatio&quot;,<br>        # Calculate margin to breach<br>        &quot;MarginToBreach = ((CoverageRatio_100bp / MinCoverageRatio) - 1.0) * 100&quot;,<br>    ]<br>)</pre><p>This creates parallel scenarios showing how each borrower’s coverage ratio would change under different rate environments. The MarginToBreach calculation shows how much cushion each borrower has. Negative values mean they&#39;d breach their covenant.</p><p>Let’s identify which borrowers are most vulnerable to rate increases.</p><pre># Find borrowers most vulnerable to rate increases<br>vulnerable_to_rates = stress_scenarios.where(<br>    [<br>        &quot;Breach_100bp&quot;  # Would breach with 100bp increase<br>    ]<br>).sort(&quot;MarginToBreach&quot;)<br><br># Summary: How many borrowers breach under each scenario?<br>breach_summary = stress_scenarios.agg_by(<br>    [<br>        agg.count_(&quot;TotalBorrowers&quot;),<br>        agg.sum_([&quot;Breaches_50bp = Breach_50bp&quot;]),<br>        agg.sum_([&quot;Breaches_100bp = Breach_100bp&quot;]),<br>        agg.sum_([&quot;Breaches_200bp = Breach_200bp&quot;]),<br>    ]<br>)</pre><p>The results are sobering. Some borrowers are already in breach of their coverage covenants. Even a modest rate increase pushes that number higher, and at 100bp, close to half the portfolio would breach. At 200bp, roughly three-quarters would be in violation.</p><p>Let’s visualize how coverage ratios change under stress for the top 10 borrowers by exposure.</p><pre># Track coverage ratio trends for top borrowers by exposure<br>top_borrowers = stress_scenarios.sort_descending([&quot;LoanAmount&quot;]).head(10)<br><br># Reshape to long format so each scenario becomes a color group<br>top_borrowers_scenarios = merge(<br>    [<br>        top_borrowers.view(<br>            [&quot;BorrowerName&quot;, &quot;CoverageRatio = CurrentCoverageRatio&quot;]<br>        ).update_view(&quot;Scenario = `Current`&quot;),<br>        top_borrowers.view(<br>            [&quot;BorrowerName&quot;, &quot;CoverageRatio = CoverageRatio_100bp&quot;]<br>        ).update_view(&quot;Scenario = `+100bp`&quot;),<br>        top_borrowers.view(<br>            [&quot;BorrowerName&quot;, &quot;CoverageRatio = CoverageRatio_200bp&quot;]<br>        ).update_view(&quot;Scenario = `+200bp`&quot;),<br>    ]<br>)<br><br>coverage_stress_plot = dx.bar(<br>    top_borrowers_scenarios,<br>    x=&quot;BorrowerName&quot;,<br>    y=&quot;CoverageRatio&quot;,<br>    color=&quot;Scenario&quot;,<br>    barmode=&quot;group&quot;,<br>    title=&quot;Coverage Ratio Stress Test — Top 10 Borrowers by Exposure&quot;,<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/860/1*ngRMLpAK9ypAjBNDKqT65Q.png" /></figure><p>The visualization shows how quickly coverage ratios deteriorate under rate stress. Several borrowers that look safe today would breach covenants with just a 100bp increase. The borrowers currently closest to their covenant minimums are the ones that need immediate attention.</p><p><strong>Key insight:</strong> A meaningful share of the portfolio would breach coverage covenants with a 100bp rate increase. Rate stress is often how you discover the cockroaches you couldn’t see in static quarterly reports — treat these vulnerable borrowers as candidates for the same pattern-matching in the next section.</p><h3>Analyzing sector concentration</h3><p>Nearly 40% of private credit portfolios are concentrated in software and tech companies. This concentration creates correlated risk: if the sector experiences stress, it could cascade across a large portion of your portfolio. Let’s quantify our sector exposure and identify whether we have a concentration problem.</p><p>We’ll aggregate the portfolio by industry sector to calculate total exposure, borrower count, and average risk metrics for each sector. This shows both where our capital is deployed and which sectors are showing the most stress.</p><pre>portfolio = portfolio.update_view(<br>    &quot;IsHighRisk = (LeverageRatio &gt; 4.0 || CoverageRatio &lt; 2.5) ? 1 : 0&quot;<br>)<br><br># Aggregate by industry sector<br>sector_exposure = portfolio.agg_by(<br>    [<br>        agg.count_(&quot;BorrowerCount&quot;),<br>        agg.sum_([&quot;TotalExposure = LoanAmount&quot;]),<br>        agg.avg([&quot;AvgLeverageRatio = LeverageRatio&quot;]),<br>        agg.avg([&quot;AvgCoverageRatio = CoverageRatio&quot;]),<br>        agg.sum_([&quot;HighRiskCount = IsHighRisk&quot;]),<br>    ],<br>    &quot;Industry&quot;,<br>)<br><br># Calculate percentage of portfolio<br>sector_exposure = sector_exposure.update_view(<br>    [<br>        f&quot;PercentOfPortfolio = (TotalExposure / {TOTAL_EXPOSURE}) * 100&quot;,<br>        &quot;PercentHighRisk = (HighRiskCount / (double)BorrowerCount) * 100&quot;,<br>    ]<br>)<br><br># Sort by exposure to see top concentrations<br>sector_exposure_sorted = sector_exposure.sort_descending([&quot;TotalExposure&quot;])</pre><p>The sector breakdown reveals significant concentration in tech-related sectors. Look for sectors where high exposure and elevated risk metrics overlap — that combination indicates correlated risk, and it’s where a single troubled borrower is most likely to be followed by others.</p><p>The cockroach theory operates at the sector level too. One borrower in the tech sector showing rising leverage is worth asking whether others in the same sector are tracing a similar trajectory.</p><p>Let’s group all tech-related sectors together to see the full picture of our tech exposure.</p><pre># Group software/tech/SaaS into single category<br>tech_exposure = portfolio.where(<br>    &quot;Industry in `Software`, `Technology`, `SaaS`, `IT Services`&quot;<br>).agg_by(<br>    [<br>        agg.count_(&quot;TechBorrowerCount&quot;),<br>        agg.sum_([&quot;TechExposure = LoanAmount&quot;]),<br>        agg.avg([&quot;TechAvgLeverage = LeverageRatio&quot;]),<br>        agg.avg([&quot;TechAvgCoverage = CoverageRatio&quot;]),<br>    ]<br>)<br><br>tech_exposure = tech_exposure.update_view(<br>    f&quot;TechPercentOfPortfolio = (TechExposure / {TOTAL_EXPOSURE}) * 100&quot;<br>)</pre><p>This aggregation reveals your sector concentration and whether specific industries show elevated risk metrics compared to your portfolio average. Look for sectors where you have both high exposure and stressed metrics.</p><p>Visualizing sector concentration with plotting methods makes it easier to communicate these risks to your team and LPs.</p><pre># Pie chart of sector exposure<br>sector_concentration_plot = dx.pie(<br>    sector_exposure_sorted.head(8),<br>    names=&quot;Industry&quot;,<br>    values=&quot;TotalExposure&quot;,<br>    title=&quot;Portfolio Exposure by Sector&quot;,<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/860/1*GAvzUrb3Vpq4j-yqJ_wFRQ.png" /></figure><h3>Finding the “other cockroaches”</h3><p>The “cockroach theory” suggests that if you see one problem loan, there are likely many others with similar risk profiles hiding in your portfolio. Let’s use historical data and pattern matching to identify borrowers that look like known problem cases.</p><p>First, we’ll generate historical quarterly data to calculate trends over time. This shows which borrowers are deteriorating even if they’re not in breach yet.</p><pre># Generate 8 quarters of historical data for trend analysis<br># In practice, this would come from your quarterly reporting archive<br>import random<br><br>random.seed(42)<br><br># Derive Q4 2025 baseline values from the loaded borrower_financials table<br>_bf = to_pandas(borrower_financials)<br><br>quarters_list = [<br>    &quot;Q1_2024&quot;,<br>    &quot;Q2_2024&quot;,<br>    &quot;Q3_2024&quot;,<br>    &quot;Q4_2024&quot;,<br>    &quot;Q1_2025&quot;,<br>    &quot;Q2_2025&quot;,<br>    &quot;Q3_2025&quot;,<br>    &quot;Q4_2025&quot;,<br>]<br>deteriorating_ids = [&quot;B001&quot;, &quot;B002&quot;, &quot;B004&quot;, &quot;B005&quot;, &quot;B011&quot;, &quot;B014&quot;, &quot;B016&quot;, &quot;B019&quot;]<br><br># Build historical records by creating trends from Q4 2025 baseline<br>all_historical_records = []<br><br>for idx in range(len(_bf)):<br>    borrower_id = _bf[&quot;BorrowerID&quot;].iloc[idx]<br>    q4_ebitda = int(_bf[&quot;EBITDA&quot;].iloc[idx])<br>    q4_debt = int(_bf[&quot;TotalDebt&quot;].iloc[idx])<br>    q4_interest = int(_bf[&quot;InterestExpense&quot;].iloc[idx])<br>    q4_cash = int(_bf[&quot;CashBalance&quot;].iloc[idx])<br>    q4_liabilities = int(_bf[&quot;CurrentLiabilities&quot;].iloc[idx])<br><br>    for i, quarter in enumerate(quarters_list):<br>        quarters_back = 7 - i<br><br>        if borrower_id in deteriorating_ids:<br>            # Deteriorating borrowers: EBITDA declining 3% per quarter, debt rising 2%<br>            ebitda = int(q4_ebitda * (1 + 0.03 * quarters_back))<br>            debt = int(q4_debt * (1 - 0.02 * quarters_back))<br>        else:<br>            # Stable borrowers: minor random fluctuations<br>            ebitda = int(<br>                q4_ebitda * (1 + random.uniform(-0.005, 0.005) * quarters_back)<br>            )<br>            debt = int(q4_debt * (1 + random.uniform(-0.002, 0.003) * quarters_back))<br><br>     all_historical_records.extend(<br>            [<br>                borrower_id,<br>                quarter,<br>                ebitda,<br>                debt,<br>                int(q4_interest * debt / q4_debt),<br>                int(q4_cash * ebitda / q4_ebitda),<br>                int(q4_liabilities * ebitda / q4_ebitda),<br>            ]<br>        )<br><br># Create historical table<br>historical_financials = new_table(<br>    [<br>        string_col(<br>            &quot;BorrowerID&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(0, len(all_historical_records), 7)<br>            ],<br>        ),<br>        string_col(<br>            &quot;Quarter&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(1, len(all_historical_records), 7)<br>            ],<br>        ),<br>        int_col(<br>            &quot;EBITDA&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(2, len(all_historical_records), 7)<br>            ],<br>        ),<br>        int_col(<br>            &quot;TotalDebt&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(3, len(all_historical_records), 7)<br>            ],<br>        ),<br>        int_col(<br>            &quot;InterestExpense&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(4, len(all_historical_records), 7)<br>            ],<br>        ),<br>        int_col(<br>            &quot;CashBalance&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(5, len(all_historical_records), 7)<br>            ],<br>        ),<br>        int_col(<br>            &quot;CurrentLiabilities&quot;,<br>            [<br>                all_historical_records[i]<br>                for i in range(6, len(all_historical_records), 7)<br>            ],<br>        ),<br>    ]<br>)<br><br># Save for later use<br>write_csv(historical_financials, &quot;/data/historical_portfolio_financials.csv&quot;)</pre><p>This generates 496 rows (62 borrowers × 8 quarters) with realistic trends:</p><ul><li><strong>Deteriorating borrowers</strong> (B001, B002, B004, B005, B011, B014, B016, B019): EBITDA declining ~3% per quarter, debt rising ~2%</li><li><strong>Stable borrowers</strong>: Minor random fluctuations around baseline</li><li><strong>Historical context</strong>: 8 quarters from Q1 2024 through Q4 2025</li></ul><p>Now let’s join the historical data with loan terms and calculate ratios for trend analysis.</p><pre># Join with current portfolio to get loan terms<br>historical_portfolio = historical_financials.natural_join(<br>    loan_terms, on=[&quot;BorrowerID&quot;], joins=[&quot;BorrowerName&quot;, &quot;Industry&quot;, &quot;LoanAmount&quot;]<br>).update_view(<br>    [<br>        &quot;LeverageRatio = TotalDebt / EBITDA&quot;,<br>        &quot;CoverageRatio = EBITDA / InterestExpense&quot;,<br>    ]<br>)</pre><p>Now we’ll calculate rolling averages and quarter-over-quarter changes to identify deteriorating trends.</p><pre># Calculate rolling averages and trends for each borrower<br>borrower_trends = historical_portfolio.update_by(<br>    [<br>        rolling_avg_tick(cols=[&quot;AvgEBITDA_8Q = EBITDA&quot;], rev_ticks=8),<br>        rolling_avg_tick(cols=[&quot;AvgLeverage_8Q = LeverageRatio&quot;], rev_ticks=8),<br>        delta(cols=[&quot;EBITDA_QoQ_Change = EBITDA&quot;]),<br>    ],<br>    &quot;BorrowerID&quot;,<br>)<br><br># Get most recent quarter for each borrower<br>latest_trends = borrower_trends.last_by([&quot;BorrowerID&quot;])<br><br># Identify deteriorating trends<br>deteriorating = latest_trends.update_view(<br>    [<br>        &quot;EBITDA_Declining = EBITDA &lt; AvgEBITDA_8Q * 0.95&quot;,  # Down &gt;5% from 8Q avg<br>        &quot;Leverage_Rising = LeverageRatio &gt; AvgLeverage_8Q * 1.10&quot;,  # Up &gt;10% from 8Q avg<br>        &quot;Deteriorating = EBITDA_Declining &amp;&amp; Leverage_Rising&quot;,<br>    ]<br>)<br><br>deteriorating_borrowers = deteriorating.where(&quot;Deteriorating&quot;)</pre><p>This identifies borrowers where EBITDA is declining and leverage is rising — a classic pattern of financial stress. These borrowers may not be in breach yet, but they’re heading in the wrong direction.</p><p>Now let’s use pattern matching to find borrowers that look like known problem cases. Assume we’ve identified three problem borrowers (defaulted or distressed). We’ll find other borrowers with similar characteristics.</p><pre># Assume we have identified problem borrowers (defaulted or distressed)<br>problem_borrowers = [&quot;ACME_Corp&quot;, &quot;TechStart_Inc&quot;, &quot;Retail_Co&quot;]<br>_problem_names = &quot;, &quot;.join(f&quot;`{n}`&quot; for n in problem_borrowers)<br><br># Extract characteristics of problem borrowers<br>problem_profiles = portfolio.where(f&quot;BorrowerName in {_problem_names}&quot;)<br><br># Find borrowers with similar profiles<br># Criteria: Same industry, similar size, high leverage, low coverage<br>similar_risk_profile = portfolio.where(<br>    [<br>        &quot;Industry in `Software`, `Technology`, `Retail`&quot;,  # Industries of problem borrowers<br>        &quot;EBITDA &gt;= 14_000_000 &amp;&amp; EBITDA &lt;= 35_000_000&quot;,  # Similar size range<br>        &quot;LeverageRatio &gt; 4.0 || CoverageRatio &lt; 2.5&quot;,  # Either stressed metric like problem borrowers<br>        f&quot;BorrowerName not in {_problem_names}&quot;,  # Exclude known problems<br>    ]<br>)</pre><p>This pattern-matching identifies borrowers who share key characteristics with the known problem loans: the same industry, similar size, and at least one stressed metric — high leverage or low coverage. These are the stockroaches — borrowers following the same trajectory before the financials make it obvious.</p><p>Let’s create a comprehensive risk score that combines multiple factors to identify the highest-risk borrowers across the portfolio.</p><pre># Create composite risk score<br>portfolio_with_risk = portfolio.update_view(<br>    [<br>        # Leverage component (40% weight)<br>        &quot;LeverageScore = (LeverageRatio / MaxLeverageRatio) * 40&quot;,<br>        # Coverage component (40% weight)<br>        &quot;CoverageScore = (MinCoverageRatio / CoverageRatio) * 40&quot;,<br>        # Size component (20% weight - small borrowers riskier)<br>        &quot;SizeScore = EBITDA &lt; 25_000_000 ? 20 : 0&quot;,<br>        # Total risk score (0-100)<br>        &quot;RiskScore = LeverageScore + CoverageScore + SizeScore&quot;,<br>        # Risk category<br>        &quot;RiskCategory = RiskScore &gt; 80 ? `High` : (RiskScore &gt; 60 ? `Medium` : `Low`)&quot;,<br>    ]<br>)<br><br># Sort by risk score to identify highest-risk borrowers<br>highest_risk = portfolio_with_risk.sort_descending([&quot;RiskScore&quot;]).head(20)</pre><p>The risk score combines leverage utilization, coverage cushion, and borrower size into a single metric. Scores above 80 indicate critical risk, 60–80 is high risk, and below 60 is medium to low risk. The top 20 borrowers by risk score should be on your critical watch list.</p><blockquote>The “cockroach theory” isn’t about finding more defaults — it’s about finding borrowers that look like the ones that already defaulted.</blockquote><h3>Acting on what you find</h3><p>The analyses so far have identified stressed borrowers, rate-sensitive exposures, and sector concentrations. Now you need two things: summary tables you can put in front of LPs, and a prioritized list of who to engage directly.</p><p>Let’s start with the headline metrics LPs want to see.</p><pre># Add covenant breach flag<br>portfolio_with_risk = portfolio_with_risk.update_view(<br>    &quot;IsCovenantBreach = (LeverageRatio &gt; MaxLeverageRatio || CoverageRatio &lt; MinCoverageRatio || CashBalance &lt; MinLiquidity) ? 1 : 0&quot;<br>)<br><br># Overall portfolio health metrics<br>portfolio_health = portfolio_with_risk.agg_by(<br>    [<br>        agg.count_(&quot;TotalBorrowers&quot;),<br>        agg.sum_([&quot;TotalExposure = LoanAmount&quot;]),<br>        agg.avg([&quot;AvgRiskScore = RiskScore&quot;]),<br>        agg.avg([&quot;AvgLeverageRatio = LeverageRatio&quot;]),<br>        agg.avg([&quot;AvgCoverageRatio = CoverageRatio&quot;]),<br>        agg.sum_([&quot;CovenantBreaches = IsCovenantBreach&quot;]),<br>    ]<br>)</pre><p>This creates the headline metrics LPs want to see: total borrowers, exposure, average risk levels, and current covenant breaches.</p><p>Combine this with a stress test summary to show portfolio sensitivity to rate increases:</p><pre># Add current breach flag and exposure at risk column<br>stress_scenarios = stress_scenarios.update_view(<br>    [<br>        &quot;IsCurrentBreach = CurrentCoverageRatio &lt; MinCoverageRatio ? 1 : 0&quot;,<br>        &quot;ExposureIfBreach_100bp = Breach_100bp ? LoanAmount : 0&quot;,<br>    ]<br>)<br><br># Comprehensive stress test summary<br>stress_summary = stress_scenarios.agg_by(<br>    [<br>        agg.count_(&quot;TotalBorrowers&quot;),<br>        agg.sum_([&quot;CurrentBreaches = IsCurrentBreach&quot;]),<br>        agg.sum_([&quot;Breaches_50bp = Breach_50bp&quot;]),<br>        agg.sum_([&quot;Breaches_100bp = Breach_100bp&quot;]),<br>        agg.sum_([&quot;Breaches_200bp = Breach_200bp&quot;]),<br>        agg.sum_([&quot;ExposureAtRisk_100bp = ExposureIfBreach_100bp&quot;]),<br>    ]<br>)<br><br>stress_summary = stress_summary.update_view(<br>    [f&quot;PercentAtRisk_100bp = (ExposureAtRisk_100bp / {TOTAL_EXPOSURE}) * 100&quot;]<br>)</pre><p>These two tables show LPs where you stand today and how vulnerable you are to rate increases. The watch list below is the action side of the same picture.</p><p>Let’s create a prioritized list of borrowers that need direct engagement.</p><pre># Create prioritized watch list<br>watch_list = (<br>    portfolio_with_risk.where(<br>        [<br>            &quot;RiskScore &gt; 70&quot;  # High and upper-medium risk<br>        ]<br>    )<br>    .update_view(<br>        [<br>            &quot;Priority = RiskScore &gt; 80 ? `Critical` : `High`&quot;,<br>            &quot;RecommendedAction = RiskScore &gt; 80 ? `Immediate engagement` : `Enhanced monitoring`&quot;,<br>        ]<br>    )<br>    .sort_descending([&quot;RiskScore&quot;])<br>)<br><br># Show key fields for action<br>watch_list_summary = watch_list.view(<br>    [<br>        &quot;BorrowerName&quot;,<br>        &quot;Industry&quot;,<br>        &quot;RiskScore&quot;,<br>        &quot;RiskCategory&quot;,<br>        &quot;Priority&quot;,<br>        &quot;LeverageRatio&quot;,<br>        &quot;CoverageRatio&quot;,<br>        &quot;LoanAmount&quot;,<br>        &quot;RecommendedAction&quot;,<br>    ]<br>)</pre><p>This creates the watch list — the cockroach manifest in practical terms. Critical-priority cases need immediate engagement: request updated financials, discuss operational improvements, and prepare for potential covenant negotiations. High-priority borrowers need closer monitoring with more frequent check-ins.</p><h4>Updating the analysis quarterly</h4><p>When new quarterly data arrives, re-run the entire analysis to track how risk profiles are changing. This creates a consistent, repeatable process for portfolio risk assessment.</p><pre># When Q1 2026 data arrives, re-run the entire analysis<br>q1_2026_financials = read_csv(&quot;/data/Q1_2026_portfolio_financials.csv&quot;)<br><br># Join with loan terms<br>q1_portfolio = q1_2026_financials.natural_join(<br>    loan_terms,<br>    on=[&quot;BorrowerID&quot;],<br>    joins=[<br>        &quot;BorrowerName&quot;,<br>        &quot;Industry&quot;,<br>        &quot;LoanAmount&quot;,<br>        &quot;MaxLeverageRatio&quot;,<br>        &quot;MinCoverageRatio&quot;,<br>    ],<br>)<br><br># Calculate ratios and risk scores (same operations as before)<br>q1_portfolio = q1_portfolio.update_view(<br>    [<br>        &quot;LeverageRatio = TotalDebt / EBITDA&quot;,<br>        &quot;CoverageRatio = EBITDA / InterestExpense&quot;,<br>        &quot;LeverageScore = (LeverageRatio / MaxLeverageRatio) * 40&quot;,<br>        &quot;CoverageScore = (MinCoverageRatio / CoverageRatio) * 40&quot;,<br>        &quot;SizeScore = EBITDA &lt; 25_000_000 ? 20 : 0&quot;,<br>        &quot;RiskScore = LeverageScore + CoverageScore + SizeScore&quot;,<br>    ]<br>)<br><br># Compare to previous quarter<br>quarter_comparison = q1_portfolio.natural_join(<br>    portfolio_with_risk,<br>    on=[&quot;BorrowerID&quot;],<br>    joins=[&quot;PrevRiskScore = RiskScore&quot;, &quot;PrevLeverageRatio = LeverageRatio&quot;],<br>).update_view(<br>    [<br>        &quot;RiskScoreChange = RiskScore - PrevRiskScore&quot;,<br>        &quot;LeverageChange = LeverageRatio - PrevLeverageRatio&quot;,<br>        &quot;Improving = RiskScoreChange &lt; -5&quot;,<br>        &quot;Deteriorating = RiskScoreChange &gt; 5&quot;,<br>    ]<br>)</pre><p>This quarter-over-quarter comparison shows which borrowers are improving and which are deteriorating. Track whether your interventions are working and whether new risks are emerging.</p><h3>Building portfolio-wide visibility with Deephaven</h3><p>Private credit is under pressure. Defaults are rising, LPs are pulling capital, and the “cockroach theory” keeps proving itself. Visible problems are just the beginning, and portfolio-wide visibility isn’t a luxury anymore — it’s survival.</p><p>The analyses we’ve built here work with the quarterly data you already collect. Segment borrowers by size and find the vulnerable ones. Stress test coverage ratios under different rate scenarios. Quantify sector concentration. Use pattern matching to find borrowers that look like the ones that already defaulted. These aren’t theoretical exercises — they’re analyses you can run Monday morning.</p><p>Deephaven handles this well because the same table operations scale across your entire portfolio. Write a ratio once and you’ve written it for all 60 borrowers. Add a stress scenario and the breach flags, margin calculations, and summaries follow automatically. When Q1 data arrives, every downstream table refreshes — no separate pipeline to maintain.</p><p>You can extend this further. Add more sophisticated risk models, incorporate external market data, build custom scoring algorithms, and integrate with your loan management systems. The foundation is the same: clean data, flexible table operations, portfolio-wide visibility. A follow-up post covers connecting these tables directly to Excel via the Deephaven add-in — no export step, no copy-paste, live data as the quarter evolves.</p><h3>Try it yourself</h3><ol><li><a href="https://controller.try-dh.demo.community.deephaven.io/"><strong>Try it in the browser</strong></a> — no install required.</li><li><a href="https://deephaven.io/core/docs/getting-started/quickstart/"><strong>Install Deephaven</strong></a> — free, takes about five minutes.</li><li><strong>Load your portfolio data</strong> — CSV, Parquet, or Excel exports from your loan management system work directly.</li><li><strong>Run the segmentation analysis</strong> — find which borrowers fall into high-risk categories with your actual data.</li><li><strong>Stress test your coverage ratios</strong> — how many would breach covenants if rates rise another 100 basis points?</li></ol><p>One cockroach rarely travels alone. The sooner you start looking, the better.</p><p>Questions? Join us on <a href="https://deephaven.io/slack/">Slack</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ee33566f3391" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>