Jonathan Dufault

Author: Jonathan Dufault

  • Before Strategy Becomes a Story

    In Strategy is a Story I called our experience in the simulation analysis paralysis. By the end, that was fair. We had passed the point where we were able to make proactive changes but kept collecting data. It misjudged the beginning.

    The early quarters taught us how the pricing levers worked, how marketing and demand modeling could help with production capacity. These observations were what made the forecasts possible. Reacting was the right move at the start. The mistake came later, when reacting had taught us enough to predict and we didn’t notice.

    “I’m changing nothing for 90 days”

    I’ve worked across multiple avenues in supply chain: retail stores, manufacturing, transportation, from the shop floor all the way to leading a team of data scientists. New leaders would come in and rotate. I knew when a good one came along from their first day.

    The best leaders I saw entered the stores and operations, and often said some variation of: “I’m changing nothing for 90 days. I need to understand what’s happening.” Unnerving because they usually came into an operation that was on fire. At one retail job, the backroom was enormous but you couldn’t move because there were 30 pallets of product, toys thrown into shopping carts, clothes piled on top of boxes. In manufacturing, we were staring down losing half our production capacity because a filler kept going down for unplanned maintenance.

    In transportation, a leader came in who had decades in the industry, from an organization much stronger than ours. However, he said he didn’t know enough yet to know what to change. He was willing to let observation overturn the plan that he brought with him.

    The leaders still ran the operation, addressing the big three: safety/service/quality, but withheld discretionary changes. They would watch the actual process, the difference between what was documented and what was actually used. The one that would get the work done best.

    A leader who arrives with a ready-made plan looks proactive, while acting on a fictional process.

    CAPD before PDCA

    In Six Sigma and Lean, a foundational methodology is a loop called PDCA: Plan Do Check Act. After Act you go back to Plan. At any level of the business, you have an idea of where you want to go, make plans to get there, enact them, check what actually happened, and act on course corrections. Rinse and repeat.

    One that I learned about only when getting my Black Belt was CAPD, or CAPDo. Same verbs, same actions, but you start with the reaction. Scan the environment, react to anything that’s going wrong, make future plans based on what happened. Reactive management can be a four-letter word, but an improvement cycle is as valid when it begins with a Check of the current condition.

    CAPD is improvement work when the first fact is that you don’t understand the process. When I was studying for the Black Belt exam, I went to the quality leader incredulous that they were teaching the CAPD cycle, and she wrote on a piece of paper:

    CAPD CAPD CAPD CAPD CAPDCA... CA|PD ... PDCA PDCA PDCA

    Early cycles are observation-led. Later ones are hypothesis-led. The bar works in both directions. When you realize that your mental model of the situation has deteriorated, then you shift into CAPD.

    Starting PDCA too early makes a detailed plan for an imaginary process, and keeping CAPD too long turns disciplined observation into avoidance. A good mental model should be the goal when you’re running the ship. It’s okay to drift for a little while as you get there.

    Root Cause Analyses

    A concrete example of the interplay between proactive and reactive came when we were rethinking our existing system for RCAs in manufacturing. We had five main triggers for events that affected operations enough to warrant paying the thousand dollars to have a team of associates come off the floor and spend days to understand the foundational issues in our process that could cause them.

    After research into RCAs, I found a book on applying them in education that categorized them into four buckets based off of two modalities.

    ReactiveProactive
    PositiveWhat caused an existing success?What roots must be established for future success?
    NegativeWhat caused an existing failure?What roots could obstruct future success?

    Paul G. Preuss, School Leader’s Guide to Root Cause Analysis.

    We had been training our associates to think Reactive = we let something bad happen, and Proactive = we are doing good things, but the takeaway I got from this book was that proactive and reactive modalities were equally valid. Reactive for things you didn’t catch, proactive for things you’d like to do. Good and bad are in each bucket.

    For us, RCAs were the bridge from reactive to proactive.

    We filled the rest of the grid. If a line ran for a week with a mean time between failures well beyond expected, that warranted an RCA. As we matured in different departments, we enacted proactive RCAs as the bridge to the proactive cycle, dissecting and refining our mental model to understand how we wanted to change.

    Reactive RCAs helped us build the mental model. Proactive ones helped us use it.

    90 days

    Being reactive is necessary to build a mental model. In the simulation we had made a strategic error, by being at the point where we had enough information to make predictions but staying in learning mode. I still believe that strategy is a story, and you need proactive plans to tell it.

    Ninety days is an arbitrary standard, more metaphorical than anything. When you can understand what the next quarter is going to look like, more often right than wrong, then the 90 days are over. The hard part is noticing.

  • Limiting the worst-case bill from a Fabric F2 capacity

    I passed DP-600. Below is a setup I had for making a Fabric F2 Capacity for personal testing with a limited blast radius. Headers so you can skip sections that aren’t relevant.

    Background

    A lot of companies only use part of Fabric, so getting experience with the whole ecosystem can be tough. I’m more familiar with Databricks for engineering/ML, and Fabric for dataflows, Power BI, and Copilot. There’s some serious strengths in an overlapping Databricks/Fabric ecosystem (for a future post), but since Fabric is designed to be end-to-end, some of the more Microsoft-specific exam components including its eventstream implementation, dataflows writing into a lakehouse, domains, and Fabric Python libraries weren’t part of my day-to-day stack.

    It’s possible to set up Fabric for cheap on Azure, less than a dollar an hour, pennies when you pause it. Fabric is also a single resource that is contained, no worrying about making networking and external storage pieces mix together by default, so it’s super easy to provision and manage through the front-end. I use Terraform for other hosts (Ramnode, Railway, and Backblaze being some of them), but only when there’s a hard spending cap. None of my personal machines will ever have api or terminal access to anything with unlimited spending.

    With pay-as-you-go on Azure, there’s no hard spending cap. You sign an agreement saying you’re going to pay for the resources you provision. I’ve read horror stories about people being on the hook for a $40k gpu mining operation on their account, or leaving an expensive service running. I love Azure and fear it in equal measures. I think that’s healthy.

    Okay, so the setup.

    Objects

    At a high level there’s two administrator accounts. One as a global admin in entra, one as an admin in Azure in case for some reason the global admin was locked out and there was a resource still online.

    Entra

    I created these accounts. The admin and analyst aren’t strictly needed. Analyst is useful if you want to test out role based access controls, which the exam covers. Contributor and above roles in Fabric workspace can sidestep some of the rules, so it’s useful to have someone where you can see what it looks like from their perspective.

    • me@my_domain.onmicrosoft.com: card and billing relationship
    • admin@my_domain.onmicrosoft.com: global admin, subscription owner
    • jonathan@my_domain.onmicrosoft.com: daily account, capacity admin and operator role (custom role created, more below)
    • analyst@my_domain.onmicrosoft.com: test identity, no admin roles

    In entra, I enforced MFA and created the four accounts. Set them up one at a time.

    Fabric

    In https://app.fabric.microsoft.com/, I signed in with admin, jonathan, and analyst to set up their free accounts. Admin is required to sign in so it can create the capacity.

    Azure

    Single subscription fabric-test, created by admin, signed in private mode on my browser. The me and admin accounts are kept signed out. These are the only two accounts that can create subscriptions.

    In fabric-test I created the objects:

    • Policies: allowed resource types (Fabric capacities, action groups, and activity log alerts), allowed locations (eastus), and a custom F2-only SKU rule.
    • Logging and alerting:
      • Catch-all activity log alert rule
      • Action group for emailing my account
    • Budget alerts
    • Fabric F2 capacity, created with jonathan as the admin.
    • Fabric Operator role

    The F2 SKU rule:

    {
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Fabric/capacities"
        },
        {
          "not": {
            "field": "Microsoft.Fabric/capacities/sku.name",
            "equals": "F2"
          }
        }
      ]
    },
    "then": {
      "effect": "deny"
    }
  }
}

    The custom Azure Fabric operator role has Read Fabric capacity, suspend Fabric capacity, and resume Fabric capacity. It’s assigned to jonathan, the account that stays signed in. It’s upper limited at about $260 a month. The built-in contributor grant would let it create more F2 capacities, so that was out, since the policy limits size and not count.

    {
  "assignableScopes": [
    "/subscriptions/<subscription-id>"
  ],
  "permissions": [
    {
      "actions": [
        "Microsoft.Fabric/capacities/resume/action",
        "Microsoft.Fabric/capacities/suspend/action",
        "Microsoft.Fabric/capacities/read"
      ],
      "notActions": [],
      "dataActions": [],
      "notDataActions": []
    }
  ]
}

    Capacity admin and the operator role are separate systems. Capacity admin is a Fabric setting that lets jonathan assign workspaces to the capacity. Pausing is an Azure action, so it needs the custom role too.

    If you use Copilot, you might come up against capacity limits (I’ve written about it here, here, and here). If you’re comfortable you can widen the SKU limits to F2-F4, allow write, and scope the role to a single existing capacity.

    Daily driver can spend money, but it’s capped above by simply leaving the Fabric capacity online. Annoying more than devastating.

    Guardrails

    • MFA
    • Subscription is locked down to a single paying resource
    • Anyone who can create resources is signed out by default, only accessed from ephemeral sessions
    • The only user that is capable of triggering spend is limited by about $260 a month
    • Every change to an Azure resource triggers a log alert email
    • Budget alerts at $5, $20, and $50

    Final

    So that’s the setup that’s worked for me. The holes I know about: admin and me can remove the policies, so if either is compromised there’s no cap, and budget alerts lag about a day behind spend. Let me know the other holes you see.

    Fabric is much wider and more capable than I had imagined in my prior roles. A lot of headaches I had about Power BI, thinking Microsoft did something backwards, were actually “no, that’s not what that tool is for.” Knowing how Microsoft conceptualizes its system makes it easier to understand how to coordinate with other tools, like Databricks. While certification without use can be hollow, use without certification can lead to misunderstandings.

    I’m excited to do more building on the platform.

  • Tell It Who You Are

    This is a post about food.

    I arrived in France two months ago. My husband had his usual grocery routine. People vary in their tastes but core products remain the same. It’s how we choose who we are. In Tennessee I bought California olive oil and Huy Fong sriracha as a way to keep a little bit of home in my cabinet. Walking through the rayons at Super U, I understood the products, I speak French well enough, but I didn’t know them. A new identity.

    Sitting in a small French town near Luxembourg wondering who am I without food, I wanted something that helps me with the implicit part of grocery purchases. Before it looked up a single price, I wanted it to understand who I am.

    Because

    Food is the thing we buy most often and the thing we’ve bought longest. Someone was deciding what to put in a pot three thousand years ago. Someone will be in three thousand more. It’s also the most visible way people consume an identity. Each choice you make is how you make your internal conception tangible, concrete.

    My last employer’s version was on the walls and advertised, food is connection. On my 31st birthday, the first one 2,000 miles away from family, sitting alone in a new apartment, questioning what I had done, I bought sprinkle cupcakes because my little sister would make that for my birthday when we were living together. The shopping cart is as much a portrait of the person buying it as it is the event itself.

    My food choices are a reflection of who I am, informed by how I was raised. A large family required cooking in batches, asynchronous feeding. It could be feast or famine, so we would freeze rice and beans in bulk to economize. Physical presence with each other while eating was the exception, so we connected in what we prepared for each other. I worked graveyard shifts, so I’d leave Jambalaya I made at 6am in the fridge for my family, and would open the fridge at 10pm to pot roast and vegetables. Changes in the refrigerator helped us communicate our lives, even if we saw each other a few hours a week.

    My tastes evolved. I keep the spicy foods that my family loved, but started cooking more southern dishes, Tennessee. My husband and I met online, and as I came to learn his ravenous sweet tooth, I started buying more sweet things. If we were separated by 6,000 miles, I could at least think of him with what I cooked.

    Once I got to France, I started from the basics. I knew the brands I bought in Nashville, but they were informed by what I liked in myself. I bought trail mix because it fit multiple identities: it was a big bag, low in price. It followed nutritional buckets my doctor recommended, having lost 120 lbs. It made me think of my husband. Food choices go beyond what tastes good. Somebody raised differently would optimize for organic, or for time, or for what the table looks like on Sunday. Trail mix here comes in smaller bags and is more expensive. It’s the same product inside. It isn’t the same.

    AI

    Everyone is adding AI. Instacart launched one last week. Walmart has Sparky, Amazon has Alexa, and Carrefour has one too, all doing the same thing. You say what you want and they build a basket, including recommendations from other likeminded customers, possibly your history. That’s fine. But it’s still a search box, one that talks. A tool built on a website helps you build your basket, but it doesn’t let you talk about the why. The company is footing the bill for the compute and the tokens. It’s not your psychologist.

    There’s a missed opportunity. Food goes so far beyond whether the association rules says that Mark buying Pampers and Purina probably will want Budweiser, or the Holt-Winters model says that Mary buys coffee every Sunday so she’ll buy it again next Sunday. The food you buy, it’s pictures, it’s words, it’s insecurity. I get why the economics makes it impossible for any company to address this (someone has to pay for the tokens), but it still feels like a massive miss. Something that can get a clear read on why I buy things can help me understand what I want to buy to be who I am, my ideal self.

    It’s a simple system, and I hope that more grocery players get into that space, but there’s a difficulty. The store has a model of you, forecasted and optimized for expected customer lifetime value. There’s an incentive for a tool to be smart more than intelligent. Its recommendations are bets on what you’ll buy. You’re still left wondering who is the person buying it.

    Agents

    I built a plugin to try to reconstruct some of how I communicate who I am to myself through food. It’s a simple agent. Two part system. The first has a searcher and a promos. That’s it. For Super U it borrows my browser cookie and replays browsing like if I were on the page.

    The second part is an agent. Where I pasted the purchases I made at Kroger, pictures of recipes I like, wrote about the types of food that I like, what I like to do, the restaurants I gave up, foods I can’t find here. That we’re two adults in a small flat with a small fridge and no freezer. We batch cook in a pressure cooker. Cheap and good, in that order, and nothing that rots before we get to it. I don’t sweeten coffee. My husband will eat the same chocolate cereal every day for the rest of his life and I will eat oatmeal with honey, and neither of us is going to change.

    For now, I’d like to keep AI on my side of the fence to avoid becoming a commodity, but any level of in depth customization means I’m paying tokens to serve it.

    Before the first order, I also gave it fifteen orders of purchase history from my old grocer in Nashville, pasted raw off the page with the navigation menus still in it. Pictures from cabinets in Nashville. What home looked like through food. It pulled out instant coffee, which was on every order, and sriracha and tofu, and it was right about all three. It also pulled out canned tuna and sugar. I stopped eating tuna a year ago and I’ve never put sugar in coffee.

    As I talked through it, the agent added to a preferences.md. Never suggest. Already stocked. Watch list. Not stocked at this store, stop searching, no I don’t like onions, those cookies are too soft. Each came from an individual mistake in searching. The agent had a hypothesis, tested it out and recommended a product. Somewhere around line thirty I realised it’s the identity paragraph, one wrong guess at a time.

    Whose agent

    The stores say so themselves, in the numbers they publish. Walmart reports that customers who use its assistant spend about 35% more per order than customers who don’t. Instacart’s, five days old, already produces baskets above the company’s 115 dollar average, and that’s the headline of the launch. Walmart is testing ads inside the answers. The assistant lives inside the store, ranks the store’s catalogue, ends at checkout, and is graded on how much more you spent.

    The protocols being written now, so that an agent in a chat can reach a store’s systems, come from the platforms and the payment networks. Google’s has two signed documents. The first records what you asked for, and the example in the announcement is “Find me new white running shoes.” The second records the exact items and the price. There’s a field for what you want and a field for what you’ll pay. There’s no field for who you’re trying to be.

    I’m hoping that the systems become more intelligent. That I can link ChatGPT to Carrefour from the other side of the fence, and it works collaboratively with me. I don’t mind if advertised products make it back, or if there’s a recommendation algorithm alongside it. It feels like a wasted opportunity when all the pieces are there. Mine exists by borrowing my own cookie and working through my browser. It reads the same prices as one of their assistants would but comes to different answers.

  • We Were Developing Great Data Engineers

    In an analytics organization

    During the COVID pandemic the news showed carnage at the grocery store. Empty shelves down the paper aisle and shopping carts full of toilet paper. People were panic buying paper towels and toilet paper while the stores were trying to reassure the public in vain that the product was on its way.

    I worked in supply chain in manufacturing. My boss told me that we knew we had a thousand pallets of toilet paper in our supply chain, but no idea where. The best we could do was reassure people that it was coming.

    Something had to change, and it resulted in a supply chain analytics organization. I came over that year from a creamery, where I’d been a continuous improvement leader, and I stayed with SCA until 2026.

    The island

    We started as an island, a scrappy team air-dropped in to fill a massive gap. We built Tableau and Power BI dashboards, the data engineering and the data warehousing servers behind them. The infrastructure was there but immature. Our mandate was to get the business represented outside of manual spreadsheets, and to get it done yesterday. 2020 was never going to happen again.

    For the business segment I joined, transportation was transitioning from a cost center to a profit center with its own internal financial statements, and a profit center needs reporting that a cost center never had.

    Nobody could quite place where our team was. Operations thought we were the part of technology that spoke English instead of computer. Technology saw us as the business. The data science organization saw us as the business too, the ones who could sometimes convince technology to change their mind. A continuous bridge department is always going to be like that, but as I’ve argued before, strategy falls apart without a clear story.

    Great data engineers

    A good bridge department is movement. Someone could use it to get from one department to another and carry what they’d learned with them. We had that as a nebulous future state concept. People retooling for other roles, or gaining perspectives to bring back to their home department. The recurring cast was a different breed, generalists.

    And people did cross the bridge from us to other departments, the data science organization even, but they only went as data engineers. They were good at it. They became good at it on our team.

    It was a clear sign something was wrong. A bad system will defeat a good person every time. Whatever our org chart said, our team was developing great data engineers, because we were still fundamentally a data engineering team.

    Rebasing in credibility

    As an organization founded out of a crisis of missing information, our strategy was credibility. With leadership that they had numbers to run the business on, with the field that the reports accurately represented what they were doing, with technology that we wouldn’t expose massive risk. I didn’t set that strategy. I helped and influenced it, and I led the integration work that came out of it.

    Transportation was the first team to move its data engineering over to the technology organization. I led it, and guided the data scientists on our team to move data the SQL sandbox we’d always used to the dedicated team. We learned technology’s governance, their production releases, their release schedules for data engineering.

    After strategy sessions with our subteam, we decided that, day to day, the work was meant to be based on 70% operations, 20% technology and 10% data science. My manager wanted data science to grow to 20 or 30% eventually. Lean into cross pollination without losing who we are.

    When the team’s composition would change, I would recruit members with that percentage in mind. One was a CS grad, the other came from a car shop. My job was integration. Our mandate was building data understanding into the organization. Each recruitment, each project, judged against that standard.

    As the data organization matured, the transportation subteam took the lead in moving specializations off to technology or to the data science organization. We kept some data science projects on purpose, for keeping current on a common language (science reviews and governance), or for proving out ideas that didn’t have enough concrete evidence to be worth tens of millions of dollars a year.

    The integration was the part I liked. Strategic alignment, org change, the relationship on the other side of every handoff. A continuous improvement background follows you, and working on a team’s credibility was the hook for me. You don’t get far in technology without strong relationships.

    Leaning into operations

    Most of our work was for operations, and that’s where we had to keep our credibility. SCA was supposed to stay close to the ground and never turn into an ivory tower.

    I pushed for relationships with the field and pushed for a power users space in Power BI, and we got it. People we’d identified in operations could build and deploy their own models and semantic models there. We learned what they were using data for and folded those uses into our own work. They built credibility with their peers. And we got to poach them.

    Two operations people came to SCA. One of them was my hire. He’d been building reporting on his own, and he filled a gap on our team. I had a similar strategy in manufacturing. Operations employees on rotating office assignments for continuous improvement, learning to ask questions from a different perspective, relationships on both sides, moving back to the field armed with tools to effect real change.

    And strengthening the science

    The data science organization was a subsidiary with its own formal process, check-ins and science reviews. The future vision for SCA was tighter integration with the science organization. I was the point for integration on our side of the fence. The first project we ran their way was a survey of the drivers delivering into our distribution centers. The existing dashboard in Qualtrics presented numbers but did nothing to inform the sites what to do.

    SCA volunteered to take it over, building the data pipeline from the Qualtrics API into our Databricks data lake, modeling it into Power BI. After analysis we recommended restructuring the survey from 15 questions to 3, with a free form text box and a language model trained to read the comments, and people decided what to act on.

    NPS went from 15 to 31 while the toolkit was in use. The organizational design we’d pictured from the start was coming into shape.

    Reconciliation

    I got us our own Unity Catalog domain for analytics products and data staging. That meant the Data Architecture Committee, and getting technology, engineering, governance, data science and the business to agree on a setup that followed the data science organization’s standards instead of technology’s defaults. It had stalled before. It went through.

    The data mesh was built to support analytics, so analytics could be purely analytics. We were the people talking to the business, building the reporting and the agents. (The data teams did some of that too. It’s a mesh.) I worked with the data teams to move the pipelines I’d designed, the ones the org depended on, over to them. The next step was moving everything into the data science organization’s data workspaces. I was the owner on our side, working with a director on theirs.

    When I left, two people had come to SCA from operations. A few had gone to the data science organization as data engineers, but we had a clear idea of who we were and a clear game plan on how to get there.

  • How do you count a million milk crates?

    We were losing hundreds of thousands of milk crates a year. At $4 a piece, it was half a million dollars. Conventional wisdom was that the majority of the loss was stores using them for storage, or people taking them home for furniture or their garage, but the volume we were seeing had nothing to do with people wanting cheap shelving.

    A hidden supply chain

    Milk crates are made out of high-density polyethylene, and HDPE is a commodity whose price tracks crude oil. A single crate weighs about three pounds, and a recycler will pay 14 to 28 cents a pound for ground plastic, depending on the market. Each crate brings 50 to 80 cents in scrap value, free money if you don’t pay for the crate itself.

    The crews work at night, driving behind grocery stores and bakeries and loading empty crates off the dock. From there, the crates go to a rented warehouse with an industrial grinder inside. Whole crates go in one end. Plastic pellets come out the other. The pellets get bagged and shipped overseas, where they get melted down and turned into pipes and flower pots. Ironically sometimes new crates. A certain percentage of the crates we bought were made out of those stolen in the first place.

    The dairy industry loses 20 to 25 million crates a year this way. $80 to $100 million. One dairy in Downey, lost 424,000 crates in a single year. $1.6 million in replacements, for one plant. The LA County Sheriff’s department had a dedicated unit for it, the Industrial Plastic Theft Task Force, and they recovered $6 million in stolen plastics in one year of raids.

    The Dairy Institute eventually hired a private investigator who ran stings. He’d load up a truck with milk crates and drive around to recyclers offering to sell. Eleven of them bought without question. All eleven got arrested. From there he expanded across Orange, San Diego, and LA counties. Miami ran its own version of the sting and got two dozen arrests plus $1.5 million in stolen crates.

    How do you count what’s missing?

    We knew we were losing crates. We could see it in the replacement costs. But we didn’t know how many were actually being lost, because there were always confounding variables. Were stores holding onto crates in their backrooms, or was it truly missing? If the majority of the crates were still in the network, then it was an operational controls question. If they aren’t, then it’s still an operational controls question, just different levers.

    But we didn’t even know how many were in circulation, which meant we couldn’t measure the loss rate, which meant we couldn’t tell whether anything we tried was working. Not knowing how much was out there was the worst part. Do we plan a large water run or will that cause us to run out of crates and short milk to the stores?

    An email blast would increase the crate return, but was that 10% of the problem or 90%?

    The crates are never all in one place. At any given moment they’re scattered across the creamery, trucks, store back rooms, distribution centers, and a nontrivial number of them are in a warehouse in Commerce getting fed into a grinder. You can’t pause the whole system and count. Ecologists have the same problem with animal populations.

    Capture Recapture

    You can’t count every fish in a lake. But you can estimate it. Capture-recapture. You catch a sample of animals, mark them, release them back into the population, and then wait for them to mix in. When you catch another sample and count how many marked ones show up, you can estimate how big the total population is. Say you marked 100 fish and your second catch of 50 has 5 marked fish in it. That’s 10 percent, so the total population is about 1,000. We did the same thing with milk crates. This solution would let us count crates that were lingering in backrooms and trucks, because the marked crates would mix into the population. Crates that were lost would not be counted.

    We deployed a fixed number of gray crates into the distribution network. Then we waited. Once the system had time to mix, we started counting. Every time a batch of crates came back to the creamery, we recorded how many gray crates were in it.

    The simple Petersen estimator assumes you do your sampling all at once, and our data didn’t work like that. Returns came in over weeks, so we used a beta-binomial model instead. In a beta-binomial setup the true share of colored crates floating around out there is a latent variable, and every returning batch updates it. At the start the estimates come out wide. Then the batches accumulate and the posterior tightens, and you get to watch the uncertainty shrink in real time. It approached an equlibrium.

    For the first time we had a defensible number, the size of the project. How many crates were actually in circulation, how many were gone, and what that was worth in dollars. The annual replacement spend was about $500,000, and from the results we brought in, we estimated that $250,000 of that was organized theft. The retail side had a better staffed loss prevention unit with former detectives that picked up the case from us, once we could show them the scale of the problem.

    What I think about milk crates

    Milk crate and plastic reusable container loss is still significant, but decreasing. The last project I worked on at the company when I left was on a different type of theft. The tools for combatting it have gotten more sophisticated, but the underpinning needing to have numbers that square with reality hasn’t. A recurring theme on this blog is accurate measurement, and for me, the milk crates episode encapsulates that. Not knowing if on the other side of the wall you have 200,000 crates or 0 is a legitimately terrifying feeling. Vibes aren’t good enough.

  • In-Spec Isn’t Good Enough

    Note: the numbers have been changed for privacy.

    Every quality check was passing. Customers were calling in about a chemical taste in the water anyway. Both of those things were true at the same time.

    I was a continuous improvement leader at a manufacturing plant that produced spring water and drinking water alongside dairy products. We had an increase in customer comment frequency for the spring water. They complained about a chemical taste.

    In the prior year, we’d get one to two complaints in a month. Between August and September, we received half a dozen. This was occurring across shifts and production dates. We checked the quality processing system. All quality tests were green.

    Was it real?

    Before chasing causes, you have to make sure it’s not noise. The main lesson from Six Sigma is that variation happens. If a packaging line is meant to fill to 500 grams, it’s not going to be 500.00 grams in each package. Some will have 490, some 510. Your task is to understand what amount of variation is acceptable compared to how much variation reduction is possible. Process specs vs capability.

    That’s to say, a few extra complaints in a month doesn’t necessarily mean anything meaningfully changed. Clustering illusion has its own page on wikipedia for a reason.

    We checked the control chart. Control charts attempt to quantify the “yeah, I know this product can vary between 480 and 520 grams, but how do I know if there’s an underlying shift.” If you know that 99% of the time that a process will behave one way, then finding something rare indicates you’re really lucky, or something has changed. The underlying principle is that you’re not special, so go with the more likely explanation, and conclude that something has changed. The system we followed was Nelson Rules, a collection of 8 “this data is sufficiently weird” rules to decide if the process had changed.

    There are different types of control charts. Counts, proportions, averages. For tracking rare events like customer complaints, we used a G-chart instead of a standard control chart. A G-chart tracks the time between events, instead of counting the number of events in an interval. When you’re getting one or two complaints a month, a count-based chart needs many months of data before it can detect a shift. A G-chart picks it up faster because the spacing between events is more sensitive to changes than the count.

    Each event gives you more information. It uses some basic probabilities to give you an estimate of what “99% of the time” looks like. In the below chart, based off the average time being 34 days, 99% of the time, you’d see next comment within 196 days. The below one is simulated, but it showed the same trend. Nelson rule 2 violation. Something had changed.

    The spec gap

    We decided to focus on chlorine levels in the finished product, since that was the main cause of the chemical taste associated with tap water. The current process involves testing for chlorine at the final water filter, with an upper spec limit of 0.50 milligrams per liter. The product release records for the past four months indicated that there were no products released with a level above the upper spec limits.

    So we conducted a taste threshold experiment, using the guidelines provided in (American Public Health Association, 1976). Eight people, five concentration levels, blanks mixed in to keep them honest.

    We’d done organoleptics work before, on an orange juice off-flavor project, and one thing we’d learned was that taste detection thresholds follow a lognormal distribution. I’d spent time at a university library digging into why, and the short version is that sensory receptor triggers are exponential, which makes the distribution right-skewed. You can’t just average everyone’s detection point and call it a threshold. You need the geometric mean, which pulls lower than a simple average would. That matters when you’re setting a spec limit against it.

    The experiment revealed an average detection level of 0.152 milligrams of free chlorine per liter, well below the upper spec limit. Comparing this to the final water filter results revealed multiple samples released at a level higher than the average threshold detection level.

    The spec allowed more than three times the amount a person could detect. Product was passing every quality check while containing chlorine that customers could plainly taste. The spec was the problem.

    Number line showing chlorine concentration from 0 to 0.55 mg/L. The taste detection threshold at 0.152 mg/L and new spec at 0.148 mg/L are nearly identical on the left. The old spec limit at 0.50 mg/L is far to the right, with the entire region between labeled as in spec but tasteable.

    We adjusted the chlorine standard down to 0.148 mg/L, from 0.50 mg/L.

    What changed

    Ultimately we discovered that a failing diversion valve was letting small amounts of city water into the line while we were bottling spring water.

    The fact there was a failing valve was almost to the side of the other lesson from the investigation: the spec wasn’t good enough. Two main things went into place: the tightened chlorine spec (0.148 mg/L) and a control plan that monitored both. Customer complaints dropped from six in the investigation period to one over the following four months, a trend that has held in the years since.

  • Making Tempeh: An Unreasonably Thorough Approach

    Making Tempeh: An Unreasonably Thorough Approach

    I have had so much trouble making tempeh. Crumbly, inconsistent results, batch after batch. And the troubleshooting guides online? Useless. Every single one boils down to the same set of contradictions:

    • You cooked the beans too much
    • You cooked the beans too little
    • You dried the beans too much
    • You dried the beans too little
    • You incubated too hot
    • You incubated too cold
    • You packed too tight
    • You packed too loose
    • You split the beans too much
    • You split the beans too little

    Right. So that narrows it down to everything. I decided the only way forward was to go clinical, document every step, measure every variable, and remove every excuse. If this batch failed, I’d know exactly how and why.

    Cracking the Beans

    Most instructions say to soak the beans and then scrub the hulls off by hand, squeezing each one between your fingers. I skipped that entirely. It’s a waste of water and time when you can just pre-crack them.

    KoMo Fidibus XL grain mill on granite countertop
    My KoMo Fidibus XL. I’ve had this mill for over a decade and it has paid for itself many times over.
    Soybeans loaded in the grain mill hopper
    Soybeans loaded and ready to crack.

    I widened the grinding wheels and ran a few test passes until I found a setting that splits the beans in half without creating too much dust. When you crack them this way, the hulls tend to fall right off.

    A note: this post mixes photos from two batches, one garbanzo, one soybean. The process is the same for both.

    Cracked garbanzo beans in a blue bowl
    Cracked and dehulled in about two minutes.
    Bean hulls and dust in a blue colander
    Running the cracked beans through a colander to sift out the dust.

    I shook the colander a few times and the empty hulls floated to the top. A quick pass with a hair dryer, one I keep in the kitchen specifically for cooking, cleared them off in a couple of passes.

    Clean split soybean halves in a blue colander
    Clean splits. Hulls removed, minimal dust.

    I boiled the beans until they reached the consistency of a boiled peanut, maybe a lima bean. Soft enough to eat, firm enough to hold shape. I didn’t photograph this step because it’s just boiling beans.

    The Bags

    Brother XM2701 sewing machine
    The sewing machine. Another piece of equipment that’s earned its counter space.

    I read a paper that described optimal tempeh incubation using bags with holes punched by a number 7 needle, spaced half an inch apart, on 1.5mm polyethylene. Here’s what I actually used a size 12 sewing needle at one-inch intervals on a 3mm polyethylene bag. Size 12 is thicker than size 7.

    Drying and Inoculation

    This is the step I suspect most guides don’t emphasize enough, and where most batches quietly go wrong.

    Beans drying on a parchment-lined baking sheet in the oven
    Drying in the oven at 170°F, stirring every few minutes.

    I set my oven to 170°F and stirred every few minutes until the beans were dry. Actually dry, not “they look dry.” Dry as in my hand doesn’t get wet when I grab a handful. I raised my fist to my face and told each bean it would become tempeh or die.

    Once the surface moisture was gone, I added a few tablespoons of distilled white vinegar and let that evaporate too. The vinegar lowers the pH enough to give the Rhizopus a head start over competing bacteria.

    Tempeh starter packet labeled Ragi Tempe
    The tempeh starter (Rhizopus oligosporus). Kept in my freezer until needed.

    Mixed the starter into the cooled, dry beans. Packed them into the perforated bags, pressed flat to about an inch thick, sealed them up.

    Incubation

    Brod and Taylor folding proofer displaying 90 degrees
    The Brod & Taylor folding proofer, set to 90°F. Designed for bread, but it holds temperature precisely enough for fermentation work.

    At this point I hadn’t confirmed the optimal incubation range. A quick search turned up this:

    Growth rate vs incubation temperature chart for Rhizopus
    Rhizopus growth rate peaks around 30–35°C (86–95°F) and drops sharply above 37°C. Source: tempeh.info

    I adjusted to 86°F and loaded the bags.

    Four bags of inoculated beans in the incubator
    Four bags loaded, day zero. No visible growth.

    Over-Engineering the Monitoring

    I wanted the actual temperature inside the bean cake, not just the ambient air reading from the incubator’s display. So I ran a probe thermometer directly into one of the bags.

    Temperature probe cable running into the incubator
    Temperature probe running into the bean cake.

    Then I built a data logger.

    Data logger enclosure labeled Ambient Temp Data Logger
    ESP8266 microcontroller inside the data logger

    An ESP8266 microcontroller, programmed with Arduino to read the temperature sensor and transmit data over WiFi at three-second intervals.

    Raspberry Pi connected to home network panel
    The Raspberry Pi, connected directly to the router. This is the server receiving and logging the temperature data.

    I wrote a small web server so I could check temperatures from my phone. If someone was going to tell me the incubation temperature was wrong, I’d have a timestamped log at three-second resolution to discuss.

    Phone screen showing timestamped temperature log
    Raw temperature log. Timestamped, continuous, three-second resolution.

    Was this level of monitoring necessary for making tempeh? No. But the troubleshooting advice I kept getting was some variation of “your temperature was probably wrong,” and I was done guessing.

    The Wait

    After 12 hours: nothing visible. The bags looked exactly the same as when I loaded them.

    Four bags in incubator showing no visible change after twelve hours
    Twelve hours in. The bags look exactly the same.

    I wrote a pointed review of the tempeh starter on Amazon.

    But I checked back at lunch the next day and noticed something. The tempeh didn’t look different yet, but the temperature probe told a different story, the internal temperature was climbing above ambient. The beans were generating their own heat. Something was growing.

    Annotated scatter plot of temperature vs time
    The temperature log tells the whole story. You can see where I accidentally started at 90°F and had to let it cool, where the temperature crept up and I turned off the incubator a little too long, and finally, around hour 18, where the tempeh started generating its own metabolic heat. I turned the incubator off entirely and let the mold regulate itself.

    It Worked

    I opened the incubator and saw mycelium.

    White mycelium growing through the soybeans
    Mycelium. Finally.
    Chart showing bean temperature vs incubator setting over time
    The full picture. Blue is the actual bean temperature; red dashed line is the incubator setting. At the end, the incubator is off and the tempeh is holding its own temperature around 30°C. Self-sustaining fermentation.

    A few more hours and the beans were fully bound together. Dense, white, solid blocks.

    Four completed blocks of tempeh
    Four blocks of finished tempeh. Uniform mycelium growth, firm structure.

    I changed my Amazon review.

    Amazon review updated to five stars
    “Pretty good. Don’t give up on it.” updated to 5 stars.

    What Actually Mattered

    The vague troubleshooting guides aren’t wrong, exactly. They’re just useless without measurement. “Too hot” and “too cold” don’t mean anything without a number attached. After going through this with three-second temperature resolution and documented steps, here’s what I think actually makes the difference:

    1. Dry the beans completely. Beyond “they look dry.” your hand shouldn’t feel any moisture when you grab a fistful. Then dry them a little more. Then add vinegar and dry that too.
    2. Start around 86°F (30°C), but watch it. Once the mold takes hold at around 18–24 hours, it generates enough metabolic heat to overshoot the optimal range. You may need to turn the incubator down or off entirely.
    3. Twelve hours of nothing is normal. The growth is invisible at first. If your temperature is in range and your beans were properly inoculated, wait. It happens fast once it starts.
    4. Measure what you can. You don’t need an ESP8266 and a Raspberry Pi (probably). But a probe thermometer inside the bean cake, rather than relying on the incubator’s ambient display, would have saved me several failed batches.

  • Strategy is a Story

    In 2016, during my MBA at Cal State Long Beach, I competed in a semester-long business simulation competition. Five teams running fictional companies, making quarterly decisions on pricing, production, marketing, and R&D. I was the CIO for ours.

    When I downloaded the year 3 quarter 4 reports, I found the first of two questions that defined the course for me. What do we do next?

    Up until that point our team had focused on getting a feel for the business, discovering how the pricing and marketing levers worked, submitting our individual decisions, and hoping for the best. It wasn’t working. Our market share dropped to 16%. We had 200 units extra inventory. Our product was a flop. We couldn’t outprice the competition. I was stuck trying to divine some pattern in the data, looking for that last bit of information that would clarify what was happening in our business, what we needed to do.

    Analysis paralysis. As a math major, this technique had worked well in the platonic world of absolute truths, and even when I branched out into statistics, I could make sense of seemingly random information by unmasking trends amid the noise. My MBA classes reinforced this view, with cases that allowed us to take an impersonal, outsider’s view of the situation. It was easy to declare a strategy broken and suggest solutions that would require a radical shift in direction. We never had to make binding decisions that required us to live with and interpret the unclear responses.

    Staring at the reports with concrete numbers in the past and an unwritten future ahead, I realized an implicit assumption underlying my work until this point: I was using static tools in a dynamic world, and our decisions had more sway on most immediate financial results for our firm. Analysis could illuminate the external world, but it couldn’t make our decisions. This both clarified our job in the competitive environment, and begged the need for a strategic framework to make decisions. Quantitative tools became a method for predicting factors none of us could control, like macroeconomic demand, so we could get a sense for what goals would be realistic, but our job as managers was to create a definition of success for our firm, and a path to get to that point.

    This led us to shift our management philosophy. I gathered information on relevant uncontrollable external factors, including estimating the production capacities of our competitors, forecasting the amount of industry sales in the upcoming quarters, and tracking when we’d achieve new model numbers.

    Spreadsheet tracking competitor production capacity, line expansion, historical sales, and market share targets across simulation quarters
    Competitor production capacity tracker.
    Spreadsheet forecasting industry sales by market area, with target market share percentages and units-per-salesperson calculations
    Industry demand forecast.
    Spreadsheet tracking R&D investment, model numbers, and training investment across simulation quarters
    R&D and training investment tracker.

    This information allowed us to set measurable targets with a clear sense of the actions to get there. For instance, we could set a goal of achieving a 1.5% increase in market share, and with the forecast, would understand the required excess production, financing for overtime, and amount of marketing to increase demand. Our next product was a breakout success, and we had a clear picture on our external environment. We learned not to simply predict the future, but to make it.

    By year 6, our team had a good handle on the tactics needed to steer the business towards the metrics we wanted to win. We had accomplished 8 of our 9 goals and were in a dominant market position. Then came the second question. It wasn’t the bank loans (that was an exercise in determination). On Thursday night, looking at our historical earnings compared to the competition, I kept asking why do our results look different?

    Our earnings over time varied wildly from quarter to quarter, but the other teams’ earnings were a straight linear trend, suggesting an even investment policy and a predictable return for investors. Even before we got our first bank loan, I sensed we were in serious trouble. Our tactics and short term strategies had given us predictable results on a two quarter horizon for the past 2 years, but the graphs hinted that we didn’t have a clear long term destination in sight. We knew how to move the business’s sails, but we were adrift at sea.

    Throughout the competition, we threw around the “best provider” strategy without a concrete definition on what the business would ultimately look like with it. We kept our options open with an organic growth strategy without being committed (or aligned) to any particular vision of the future operations. We let our uncertainty about the future run rampant. A story without a plot.

    In the last three quarters, we formulated a vision for what best provider meant to us: strong manufacturing presence in each marketing area with the goal of winning market share from our competitors through an extensive investment in training and lowering the price as our COGS decreased. We finished the competition with a market leading 24.6% market share and a clear path for the next few years. Other teams won the prizes, but we won an insight into strategy.

    Two lessons that have held up in the ten years since.

    The first is that strategy is essentially a story. Mission, vision, and objectives can help clarify elements into a standardized format, but an overarching narrative with a clear vision (or at least a guess) of the conclusion is necessary to keep from floating around aimlessly.

    The second is that analysis is most useful for elements that you can’t change, like the past, or uncontrollable factors. It can help illuminate the area around you, but the path forward is in your control. Don’t just be a character in someone else’s story.

    Also, don’t be late to meetings.

    Here’s the email I sent my family during the intensive phase, when we went bankrupt twice in two days and had to explain ourselves to the board of directors at 2:30 in the morning.

    Email to family recounting the chaotic intensive phase of the business simulation, going bankrupt twice, an earthquake wiping out production, and board meetings at 2:30 in the morning
    tl;dr: we went bankrupt twice but didn’t give up.

  • The Price of a Hidden Column

    In March I spent a weekend running test queries through Power BI Copilot on a Fabric F2 capacity, reviewing the diagnostic JSON to understand how to improve my reports. One of those diagnostics is the reason for this post. When I asked a question about product growth trends, the answer had taken 107 seconds to arrive. The DAX query behind it executed in 173 milliseconds. The other 106.8 seconds was LLM overhead, and the why didn’t exist outside of the diagnostics.

    Copilot tackles a data question in two tiers. A semantic parser gets the first shot, matching your words against the model’s tables, columns, and relationships, and it plays by the model’s rules, so a column marked Hidden (IsHidden = 1) might as well not exist. My question needed Product[Category], which I had hidden. Refused. The question dropped through to the second tier, an LLM that writes DAX from scratch.

    Diagram of the answerDataQuestion tool showing Tier 1, the Q&A semantic parser that respects column visibility, falling back to Tier 2, the NL-to-DAX generator that ignores column visibility and retries on errors
    The two tiers inside answerDataQuestion. Tier 1 respects column visibility. Tier 2 doesn’t.

    The DAX generator has auto-retry logic. If the generated DAX fails to execute, it tries again with a different approach, up to a limit. Mine hit the limit:

    "activityDetails": {
  "fallbackReason": "queryNotSupported"
},
"daxExecution": {
  "autoRetryCount": 2,
  "notRetryableReason": "MaxAutoRetry",
  "executeDaxDuration": 172.5
},
"generateDaxDuration": 106814.5

    Three generation attempts, each with its own token cost. The first produced invalid GROUPBY DAX. The second tried a join with no common join columns. The third ran. Interestingly, the DAX generator does not respect column visibility the way the parser does (Microsoft’s documentation mentions this). The generated DAX reached the hidden Product[Category] through TREATAS without complaint. The tier that honors your configuration refused the question. The tier that ignores your configuration billed three attempts and then answered using the column you hid.

    Four Layers

    The reflex is to blame the question. Growth trends means comparing periods, comparing periods means real DAX, expensive question, expensive answer. The diagnostic says otherwise. A question of comparable complexity on the same model, five tables, three filters, three aggregations, resolved through the parser in about 2 seconds. The price didn’t come from what I asked. It came from a checkbox in the model view, and between that checkbox and the invoice there are four layers a user can’t see through.

    They can’t see the visibility flags. IsHidden lives in the model properties, set by whoever built the semantic model, possibly years ago, possibly for cosmetic reasons. Hiding a column is how you tidy a field list. Nobody hides a column thinking they’re routing future questions to the expensive tier.

    Power BI model Properties pane for the Category field showing name, description, synonyms, and the Is hidden toggle
    Where IsHidden lives. One toggle in the model’s Properties pane.

    They can’t see which tier is handling the query. There is a tell, the loading message switches from “Checking the underlying data” to “Generating a DAX query,” but it only reads as a tell if you’ve been in the diagnostics. To everyone else it’s a progress message with vocabulary.

    Two Copilot panes side by side. Left: Copilot stating it does not execute DAX code directly. Right: the loading message Generating a DAX query with a Cancel button
    Left: the outer LLM insisting it doesn’t run DAX. Right: the fallback tier generating DAX. Same product.

    They can’t see the retries. Three generations happened behind one spinner. The UI shows a wait, not an attempt count, and each attempt carried its own tokens at Copilot’s rates, 100 CU seconds per 1,000 input tokens and 400 CU seconds per 1,000 output tokens.

    And they can’t see the cost land. Copilot operations are classified as background jobs, so they get spread across 24 hours. The 400 CU seconds from one query gets divided into roughly 16.67 CU seconds per hour across the next day. By the time consumption is visible in the Capacity Metrics app, the question, the retries, and the hidden column are a day in the past. The Capacity Metrics app is retrospective, not preventive.

    Four layers, one property. Everything that determines the price sits upstream of anything the person paying can observe. The cost of a Copilot query is coupled to model hygiene, and model hygiene is invisible from the chat pane.

    Why the Estimate Missed

    Azure pricing calculator showing F2 with 2 Capacity units at $1.80 per month pay-as-you-go
    The estimate.
    Azure spending rate and forecast showing $18.78 current cost with climbing trend line
    The bill.

    Before that weekend I went into the Azure calculator, pulled up the resource usage, all told would be no more than $5.00. The bill came to $18.78, and at the time I filed the gap under salespeople. That was unfair. The calculator estimates provisioned capacity, and it got that part right. The F2 itself cost $1.80 for the hours I ran it. The rest was consumption, and consumption is retry behavior multiplied by token counts. Retry behavior is determined by whether the semantic model gives the parser a clean path. No estimator can price whether somebody hid a column three weeks before you typed your question.

    What the System Does

    The purpose of a system is what it does. Nobody at Microsoft sat down and designed a meter that charges people for a checkbox they can’t see. The parser honors visibility because that’s the whole point of visibility. Retries exist because a second attempt beats an error message, and the smoothing is there to protect shared capacity from bursts. Every one of those choices is defensible on its own. Chain them together and you get a machine that spends 107 seconds of paid reasoning on a hidden column, then scatters the evidence across the next 24 hours. Configuration is priced in. Nobody sees the price until it’s been paid.

    The full pipeline teardown, with the diagnostic exports all of this comes from, is in Looking Under the Hood for Power BI Copilot.

  • Is It Okay?

    Is It Okay?

    I built an MCP (Model Context Protocol) that gives an LLM five tools for working with a data lake: list tables, describe schema, sample data, row count, run query. I use it every day. Different data sets, different questions.

    It supports different workflows. You can paste a screenshot, tell the agent “replicate this data and validate your query.” You can say “hey uhh this data point doesn’t look right. This repo has the code to generate it, can you tell me what’s up?” You can give it a query and tell it “I want to add this column but I need to thread it through all these CTEs. Can you add it?” It explores the database, writes SQL, checks its own results. Iteratively.

    I’m proud of the MCP idea. I had the insight on a Sunday that those five tools in that configuration would be useful. The implementation took an afternoon.

    It Works

    A colleague was the first other person to test this out. He used it to produce an accurate query feeding a tool in a day that would have taken a week. SQL wasn’t his background, but in the process he learned the application, the data, and the business context, along with the relationships with the business users. That was the strategy. SQL isn’t as important as the domain. The MCP handles the SQL. It’s been playing out well.

    Not just playing out well. The thing he built has been finding things worth investigating, the kind of things that accumulate in any system over time. It finds stuff because it checks its own work, reasons, asks questions. It’ll run a query, notice the count dropped unexpectedly after a filter, and investigate. It does exactly what a careful data analyst does, just faster. The AI does the mechanical part, but he’s understanding the business.

    I’ve shared it with others now. This tool is clearly useful.

    Mostly

    One example made me pause. I had a query to write and ran the agent alongside my own work. It made its version in 10 seconds and, reading through it, it had done a completely different strategy than I had thought of. I wrote my own as a way to validate whether my internal approach was wrong or suboptimal. I’m okay with being wrong if I can learn. I’m not okay with staying wrong.

    Our queries were generally similar. Mine was tighter, 85 lines to its 115. I also caught a bug it couldn’t see, a subtle data integrity issue where the agent’s approach was structurally wrong but by chance didn’t appear in the example we tested. Ultimately it didn’t matter.

    I made an unrelated demonstration of the type of issue that can come up so you can understand the questions yourself, to get a sense for the context of problems this tool can solve.

    Demo

    Like many libraries, a regional library system with 470 branches across ten upper midwest states is launching a tool lending program, drills, saws, ladders, tile cutters. One existing branch per state will be selected as the tool depot for less commonly used tools, with deliveries to other branches when patrons place holds. The task: compute a circulation-weighted geographic center for each state using 2025 data, then select the nearest currently-open branch as the depot.

    There are three existing tiers for the libraries on the system, which determine the processing network and priority for new book releases. Demographics and circulation mean that libraries can be switched between tiers.

    In the database there are two tables. The branches table is an append-only log: every tier assignment, reassignment, and closure is a separate row. A branch that got reassigned from tier 2 to tier 1 has at least two rows. A branch that closed has a row with status = 'closed'. The circulation table has annual circulation figures by branch.

    TableRowsWhat it is
    branches575Append-only log. branch_id, name, city, state, lat, lng, tier, status, effective_date. Multiple rows per branch.
    circulation2,587Annual circulation by branch and year. This is the weight.
    The database the agent sees through the MCP.

    The wrinkle: about 40 branches were reassigned between tiers. When a branch moves from tier 2 to tier 1, it gets an active record in the new tier. The old tier’s record gets set to closed, but due to operational lag, the closure is timestamped after the new assignment. Another 25 branches are genuinely closed.

    The correct approach takes the last record per branch within each tier. If any tier’s latest record is active, the branch is open. This handles both cases: reassigned branches (closed in old tier, active in new tier) and genuinely closed branches (closed in their only tier, no active record anywhere).

    StateCenter LatCenter LngCirculationBranchesDepot
    CO39.5785-105.35105,754,43551Lark Community Library, Lakewood
    IA41.9220-92.73304,650,09953Buckeye Library, Marshalltown
    KS38.4919-97.38104,625,76749Crane Library Branch, Salina
    MN45.2642-93.32787,017,75565Summit Branch Library, Maple Grove
    MO38.6307-92.95614,707,75051Sassafras Lending Library, Sedalia
    ND47.2868-99.93502,540,05725Stone Memorial Library, Bismarck
    NE41.3372-99.12275,247,03146Catalpa Library, Broken Bow
    SD44.2348-100.61303,366,75830Pine Library, Pierre
    WI44.0521-89.02205,835,96755Sumac Library Branch, Oshkosh
    WY42.4943-107.26392,066,23920Valley Branch, Casper
    Ground truth: circulation-weighted ton-centers by state, with nearest open branch as depot.

    I pointed an LLM at the MCP and gave it the task.

    Roo Code agent output showing selected tool depots for 10 states
    The agent’s depot selections. Same ten branches as ground truth.

    It produced a query that joined the branches table directly to circulation without resolving the append-only log into current state first:

    Agent SQL query joining branches directly to circulation without deduplicating the append-only log
    The agent’s query. No deduplication of the append-only log.

    There were two structural problems. First, no dedup: a reassigned branch with three log rows (initial assignment, new tier assignment, old tier closure) gets its circulation counted three times in the ton-center calculation. The weights are inflated and skewed. Second, it filtered WHERE status = 'active' to find open branches, which keeps genuinely closed branches, their original active record is still in the log, and the filter just drops the closed record that superseded it. 445 branches are actually open. The agent’s approach counts 470.

    The ton-centers shifted. The depot selections didn’t. All ten states picked the same branch.

    445 branches (black). Blue: ground truth. Red: agent query. You can barely see the gap.

    The Ideal

    The correct query. Resolve the log into current state by taking the last record per branch within each tier. If any tier’s latest record is active, the branch is open:

    WITH per_tier AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY branch_id, tier
            ORDER BY effective_date DESC
        ) as rn
    FROM branches
),
latest_per_tier AS (
    SELECT * FROM per_tier WHERE rn = 1
),
open_branches AS (
    SELECT DISTINCT branch_id, branch_name,
        city, state, lat, lng
    FROM latest_per_tier
    WHERE status = 'active'
),
ton_centers AS (
    SELECT
        bc.state,
        SUM(c.annual_circulation * bc.lat)
            / SUM(c.annual_circulation) AS center_lat,
        SUM(c.annual_circulation * bc.lng)
            / SUM(c.annual_circulation) AS center_lng,
        SUM(c.annual_circulation) AS total_circ,
        COUNT(DISTINCT bc.branch_id) AS num_branches
    FROM open_branches bc
    INNER JOIN circulation c
        ON bc.branch_id = c.branch_id
        AND c.year = 2025
    GROUP BY bc.state
)

    The PARTITION BY branch_id, tier is the key. Partitioning by branch alone picks the most recent record overall, which for reassigned branches is the closed record in the old tier, because the closure happened after the new assignment. Partitioning by branch and tier lets you see each tier independently. The old tier’s latest is closed. The new tier’s latest is active. The branch is open.

    The agent didn’t know about the operational lag. It didn’t know that some branches have multiple log entries, or that status = 'active' doesn’t mean “currently open” when the table is append-only. It applied standard patterns, join, filter, aggregate, and got the same answer from a structurally wrong query. Pattern filling without contextual awareness.

    An LLM is a pattern filler. The agent’s query was a reasonable starting point, and it landed on the same depot selections. I can’t tell you mine mattered.

    Is a sandcastle good enough?

    A lot of people I talk to have an existential unease about AI. Being good at something, then watching the definition of good shift under you in real time. I think there’s the real risk that people will forget that the part AI does is immediately a commodity. The artifact is ordinary, only as valuable as the tokens used to generate it, while quietly dropping the undocumented context that derisks it.

    Is knowing the piece that the AI can do redundancy, or is it dangerous when its 80% contribution, without reflection, can initially pass for 100? As a developer, if you have ever tried to refactor a tangled mess of tightly coupled duplicative code that Claude has written, after it’s tried 10 iterations to implement a feature that constantly breaks another, or seeing its performance degrade from one version to the next, the worry about learned helplessness built on sand becomes unavoidable.

    When I ran the query, the agent’s version had two structural bugs and produced the same result. Does it take my ability to write SQL and lived experience to anticipate those problems? Automating a task means nothing if it makes you materially wrong, but how do you know when you’re there? Chicken and egg.

    This Post

    I’ve been writing SQL since I was a child, and my parents enrolled me in classes at the local community college when I was 11 for programming. I don’t think I’ll lose that ability. It’s a native tongue.

    But when I create tools like this, I cycle through “will this cause my skill to atrophy,” to “do they even matter,” settling on “can you evaluate without creation?”

    When the tool says there’s something wrong? Often correct. When the tool makes an error? It’s usually slips or failures of a global mental model. The internal validation means the mistakes are edge cases not encountered yet. Sometimes it breaks something visible. But like you see above, sometimes it’s just potential.

    I like crafting tight, efficient queries, but I’m also proud of this tool, making something that’s eliminated an entire class of problems. The benefit is not theoretical. I’m not worried about AI taking my job. If all the tasks had been simple enough for an AI to take, then the job wasn’t worth doing in the first place. They aren’t.

    What AI does becomes the floor, and it does it without understanding. Patterns. My job isn’t to craft complex SQL queries. That is an effect, an output, evidence from a mental model.

    I worry people will equate output with judgment.