This is Chapter 13 of Azure Cosmos DB for .NET Developers. Previous: Chapter 12: Vector Search — Adding AI to the Same Container.

This chapter is going to be about engineering tradeoffs and I'm going to use the topic of reporting with Cosmos DB as the scenario. But first, I want to talk about psychology. Specifically, the psychology of loss aversion.

The 95/5 Problem

Loss aversion is a well-documented finding in behavioral psychology. The short version: humans feel losses roughly twice as intensely as equivalent gains. If I hand you $100, you feel good. If I take $100 away from you, you feel terrible — and the terrible feeling is about twice as strong as the good feeling. The gain and the loss are the same dollar amount, but they don't register the same way in your brain.

This isn't a character flaw. It's not a sign of weakness or irrationality. It's how human brains are wired. Daniel Kahneman and Amos Tversky documented this in their prospect theory work in the late 1970s, and it's been replicated across decades of research. We are, at a neurological level, more sensitive to losing things than to gaining them.

Let's say you're evaluating something in order to make a decision — it's an important decision but it's not life or death so there's some ambiguity. And to make it a little more interesting, let's say that we know that there isn't a totally clear answer. There's no outcome that's going to be obviously right or obviously wrong. But whatever outcome you pick, it gives you 95% of what you need but is missing 5%.

Now you'd think a rational person would weigh the 95% gain against the 5% loss and conclude "that's a great deal." But that's not what happens. What happens is that the 5% loss dominates your thinking. The missing piece feels enormous. The 95% you're gaining fades into the background because gains just don't register as intensely as losses.

And there's a second layer that makes this worse: humans are generally bad at perceiving long-term, gradual costs. We're great at spotting acute, immediate problems. "I can't do X right now" is clear, sharp, and urgent. But "this decision will slowly accumulate long-term consequences over next 3 years" is fuzzy and abstract. The acute problem is up-in-our-face and hard to ignore. The chronic problem is quitely over the horizon.

We humans consistently make decisions based on what's on fire and up close.

Now why is a chapter that's nominally about reporting starting with a discussion of psychology?

Because I wanted you to notice something important BEFORE I say the next bit.

The Reveal

Ready? Here goes.

Cosmos DB isn't particularly good at reporting.

I'm going to guess that you had a little "gah!" moment. A little moment of "oh crap...we can't use this thing" or "maybe I should pick a relational database for our app instead" or "oh man! I don't want to bother having to sell this to my boss or my team...I'll just pick relational and no one will blink an eye."

It's perfectly natural. Remember the 95/5 problem. Yeah. There it is. That's it in a nutshell.

Assuming you're reading this book from start to finish, you've just spent twelve chapters reading about Cosmos DB. And now I want to let you in on a little bit of my mindset. As I've written these chapters, I've made a specific effort to write about Cosmos DB in a certain way and at the same time use that same treatment for relational databases. I tried to be be neutral on what The Right Choice™ is while emphasizing the capabilities and benefits of Cosmos DB. I really wanted to avoid dunking on relational databases and relational design because that's just not valuable. I want you to be able to craft strong architectural arguments and communicate them using positive linguistic frames.

I focused on the gains but also tried hard to NOT frame the relational database side as "losses". No judgements. Just the facts. And the facts just are what they are.

This is the first chapter where I'm saying there's anything like a loss. And this loss is coming in on the Cosmos DB side. The thing I've written twelve chapters about.

So here's what I want you to notice. Twelve chapters of gains — no impedance mismatch, no DTOs, no adapter layers, domain objects that serialize directly to storage, horizontal scalability, vector search for a penny. I laid all of that out, carefully, over tens of thousands of words. And then one sentence — "Cosmos DB isn't particularly good at reporting" — and there's a decent chance that one sentence carries more emotional weight than the other twelve chapters combined.

That's the 95/5 problem in action. Twelve chapters of gain. One sentence of loss. And the loss is probably winning.

I'm not saying the reporting limitation doesn't matter. It does. I'm saying that your emotional response to hearing about it is probably disproportionate to its actual impact — and if you don't notice that disproportion, it'll drive your architecture decisions in ways that likely aren't in your best interest.

So let's slow down. Let's look at the full picture instead of focusing on our ancient lizard-brain reacting to what's on fire.

Making the Business Case

One of the things I've been trying to do throughout this book — and I'm going to be more explicit about it now — is help you build the vocabulary to make the business case for your architectural decisions. Not just to other developers, but to the people in your organization who don't think in terms of partition keys and RUs. The managers, the VPs, the product owners. The people who hear "we can't do reporting" and think "then why are we using this?"

If you're an architect or a tech lead, the technical analysis is only half the job. The other half is communication — explaining why a short-term limitation doesn't justify abandoning a long-term architectural advantage, in language that doesn't require the listener to understand document databases. That's a skill most of us never got trained on, and it's the skill that determines whether your good technical decisions actually survive contact with the rest of the organization.

The framework I'm about to walk you through isn't just a tool for your own thinking. It's a communication framework. It gives you a structure for having that conversation in a meeting, with a slide deck, or in a Slack thread with your VP — not just in your own head.

The Acute/Chronic Tradeoff Test

Here's a thinking framework I use when I'm evaluating technology decisions where one option has an obvious, visible limitation. I call it the Acute/Chronic Tradeoff Test, and it's designed to counteract exactly the bias we just talked about.

Step 1: Name the acute pain.

What's the thing you can't do right now? Be specific. Not "reporting is hard" — that's too vague to evaluate. Something oriented on business problems like: "I can't run a GROUP BY across my recipe data and therefore I can't get a dashboard for the product team." Or: "My VP wants a chart showing sales by region and I can't produce one without writing some awkward C# code." Specificity matters because vague fears feel bigger than they are. The "business value" focus helps you stay focused on strategic things ("what does the business need?") versus tactical things ("what's the syntax for my query?").

Step 2: Find & name the chronic costs of the alternative.

What ongoing complexity are you signing up for if you switch technologies to solve the acute pain? Not the migration cost — that's a one-time expense. I'm talking about the permanent operational cost. The things you'll be paying for every sprint, every release, every maintenance window, for the life of the project.

It's entirely possible that you come up with nothing. Maybe there simply AREN'T long term chronic costs and issues. If that's the case, that makes your desicions a whole lot easier. But let's assume that that isn't the case and that there actually are some lingering, nagging concerns. In my experience, just about every choice in tech has trade-offs.

Step 3: Ask whether the acute pain has a targeted solution.

Can you solve the specific gap without adopting the full chronic cost? For example, can you get the reporting without encountering the impedance mismatch problem and having to add entities and adapters to your application? If a targeted solution exists, you don't have to make the binary choice. This is the step most people skip, because loss aversion makes the decision feel urgent — and urgency kills exploration.

Once again, it's one of those ways that the ancient structures in our brains that have kept life living for a couple billion+ years sometimes nuke our ability to make good decisions. Stress — and make no mistake, that's stress — tends to push our decisions toward "expedient" rather than "well-reasoned".

Step 4: Check your weighting.

Are you overweighting the acute loss relative to the chronic gain you'd be giving up? Here's a test: would you describe the tradeoff the same way if someone laid out both sides on paper with dollar amounts and time estimates? Or does the acute pain just feel bigger?

This framework isn't a rule that says "always ignore the acute pain." Sometimes the acute pain really is a dealbreaker. If your application's primary purpose is analytical reporting, then Cosmos DB could unambiguously be the wrong tool and no framework will change that. The test says: before you react to the pain, check whether you're seeing the full picture.

And this generalizes well beyond databases. Microservices vs. monolith: the acute appeal of independent deployability can hide the chronic cost of distributed coordination and operational complexity. Rewrite vs. refactor: the acute satisfaction of starting clean can hide the chronic cost of re-discovering everything the old system encoded in its quirks and edge cases. Build vs. buy: the acute sense of control from building your own can hide the chronic cost of maintaining it forever versus forever having to pay licenses for that off-the-shelf system.

In each case, the acute feeling is real — but so is the chronic cost that the acute feeling obscures.

Applying the Test: Cosmos Reporting vs. the Relational Retreat

Let's apply the framework to the decision in front of us.

Step 1 — the acute pain: Cosmos DB doesn't do analytical queries well. It just doesn't. The end. You can't easily answer "what are the most popular base spirits across all recipes" or "which ingredients appear together most frequently" or let a product manager explore the data ad hoc with filters they didn't plan for in advance. That's a real limitation, and I can't pretend otherwise.

Step 2 — the chronic cost of retreating to relational: What you're signing up for when you say "let's just use SQL Server instead":

DTOs and adapter layers for every entity. Remember Chapter 1? Domain models are trees. Relational databases store boxes. You chop the tree into boxes to save, reassemble boxes into a tree to read. That impedance mismatch requires adapter code for every entity, and that adapter code needs to be written, tested, maintained, and debugged. Permanently. For every entity. Forever. If that sounds dramatic, think about how many hours you've spent in your career debugging Entity Framework mappings, writing migration scripts, and fixing bugs where a property got mapped to the wrong column. Now multiply that by the life of the project. Just because we've been working this way and writing code this way for years doesn't mean that we should continue writing code this way indefinitely.

But it's not just the impedance mismatch. When your application grows and you need to scale, relational databases present compounding complexity. Scaling data writes means replication or sharding. Replication or sharding means coordinating distributed transactions. Distributed transactions mean your referential integrity enforcement — the thing that made relational attractive in the first place — starts working against you. The very features that make set operations powerful are the features that make horizontal scaling expensive and fragile.

It's not just one liability. It's scalable data writes PLUS transactional integrity PLUS referential integrity enforcement. Those three things interact and compound. Each one is manageable in isolation. Together, at scale, they're the reason "scaling a relational database" is a recognized engineering specialty with its own job titles...with some real, practical, technical limits.

Compare that to what you've been building for twelve chapters. Domain objects that serialize directly to JSON and get stored as-is. No DTOs. No adapter layers. No entity configuration files. No migration scripts. Horizontal scalability without distributed transaction complexity. The tree goes in, the tree comes out. That's not a marginal improvement — that's potentially thousands of hours of developer time and operational headache over the life of a project. Having written both kinds of systems, it feels amazing to not have to write all that boilerplate stuff just so that the app is long-term maintainable.

But all of that chronic gain gets eclipsed the moment someone says "I need a report."

The right-tool reframe: Here's the thing. If you're trying to hire a new developer for your team, do you pass on that candidate because they're not a world-class violinist? Do you pass on them because they've never visited Zimbabwe? Because they don't like blue cheese? No. And why not? Because those things aren't relevant to the job.

In the case of reporting in Cosmos DB, or JSON document storage and retrieval in SQL Server, you need to ask yourself if this tool is the RIGHT tool for the job at hand. Cosmos DB's job is transactional document storage and retrieval. SQL Server's job is... well, lots of things, but one of them is set-based analytical operations. Using Cosmos for reporting is like hiring that developer and then being disappointed they can't improvise a frickin' sick cadenza off of Vivaldi.

Using SQL Server for horizontally-scaled document persistence is like hiring a violinist and being disappointed they can't write a React component.

Step 3 — the targeted solution: Does one exist? Can you solve the reporting gap without adopting the full chronic cost of relational? (Or to put it in the reverse, can you solve the ORM mismatch problem without choosing Cosmos DB?)

Yes. We'll get to that in the next chapter. But first, let's understand why Cosmos DB is bad at reporting — because the architectural explanation will make the solution make a lot more sense.

Why Cosmos DB Is Bad at Reporting (And Why That's Probably By Design)

Before we get to the solution, let's understand the problem honestly. Not as some kind of garment-rending, overwraught apologia, but as a structural analysis. Understanding why Cosmos DB is bad at reporting will make the solution make more sense.

Relational: Set Operations Live Next to the Data

Relational databases are good at analytical queries because of a fundamental architectural property: all the data lives in one logical pile. Tables sit together in a shared storage space. When you run a GROUP BY query, the database engine walks through the relevant table or tables, computes the aggregation, and returns the answer. The set operations and the data are direct neighbors. The computation happens right next to the storage.

That proximity is genuinely powerful for analytics. You can do enormous amounts of data munging without ever leaving the context of the database. You can join tables, aggregate across millions of rows, compute running totals, rank results, and return computed answers — all without the data leaving the database engine. You return answers, and not the raw data that then has to be analyzed out-of-band by your application. If you need that kind of thing, it's a real structural advantage. It's arguably the relational database's super power.

But the Achilles heel of relational databases is that they don't scale all that efficiently. To generalize it: they scale up rather than out. (BTW, that's the expensive kind of scaling.)

Cosmos: Sharding Inverts the Tradeoff

Cosmos DB's architecture inverts this. Data is sharded across partitions by design. Rather than the data being in one bit pile, the data gets spread out on to multiple sub-instances of Cosmos running on relatively inexpensive hardware. That's scale out rather that up and that's the inexpensive kind of scaling.

That data partitioning (sharding) is what makes it horizontally scalable — writes distribute across partitions and instances, no single partition becomes a bottleneck, and you can scale to enormous throughput without the compounding complexity of distributed transactions and referential integrity enforcement that relational databases face...at the expense of pretty much not being able to do distributed transactions and engine-enforced referential integrity.

But that sharding creates a structural problem for analytical queries. When you want to do set-based operations, the data might live across multiple partitions, and the database engine has to gather it into working memory before it can compute your answer. That's essentially the same work your application would do if it just read all the data out and analyzed it in C#. The Cosmos database engine is doing the same gather-then-compute that you could do at the app layer — which raises the question of what value the database engine is adding for that workload.

This is — I think — why the suite of set-based operations in Cosmos DB is somewhat underwhelming. Why invest serious engineering effort into building sophisticated aggregation, join, and windowing capabilities into a product whose architecture makes those operations structurally awkward? Why build something into the product to support a workload that the product is, pretty much by design, not optimized for?

This isn't a knock on Cosmos DB. It's actually a sign of good product judgment. The Cosmos team seems to have decided — correctly, I'd argue — that if the database engine is going to do the same gather-then-compute work that your application could do, why would we think that we could solve that better than your app? There's not much point pretending the database is adding unique value there or solving it more efficiently. Better to be excellent at what the architecture supports (transactional document operations) than to be mediocre at a whole bunch of stuff.

The Cost Problem (Even Within a Single Partition)

Even within a single logical partition, analytical queries have a cost problem. All 5,800 recipes in the cocktail app sit at ["COCKTAILS", "CocktailRecipe"] — one logical partition. A GROUP BY query to count recipes by base spirit isn't a cross-partition scan. But it still reads every document in that partition and charges RUs proportional to the data touched. You're running an analytical workload on a transactional engine at transactional prices. Do it once, fine. Put it behind a dashboard that auto-refreshes every five minutes, and those RUs add up. (Remember the "heat death of the universe" problem though...this might still be a valid approach.)

Things Cosmos Simply Can't Do

And some things Cosmos genuinely cannot do at all. Joins. "Which ingredients appear together most frequently" requires a self-join across the ingredients arrays within each recipe — you'd need to compare every ingredient to every other ingredient in the same recipe, across all recipes. Cosmos doesn't support that kind of join. "How many recipes does each ingredient classification map to" requires joining recipe data with taxonomy data. Same container, different entity types, no real join capability. To answer these questions today, you'd have to read all the relevant documents into your application's memory and do the work in C#.

The Practical Honesty Moment

Now, here's a practical honesty moment. A lot of datasets are smaller than people think. The cocktail database is 5,800 recipes at roughly 1 to 2 kilobytes each — that's about 11.6 MB on the high side. That fits comfortably in memory on even a small Azure App Service SKU. I'm always a little surprised by how small my datasets actually are. You could, in theory, just read the whole thing into memory and analyze it with LINQ in C#. It'd be super-fast and your RU count would be zero for just about everything.

But. Your dataset might be a LOT bigger. Your analysis might be more complex. And more importantly, you probably don't want to write and maintain custom C# code for every analytical question a product manager might ask next Tuesday and for the rest of time.

For the rest of this chapter and the next, we're going to assume that you don't have the luxury of reading your entire dataset into memory — that you actually need a proper analytics solution.

The Scalability Irony

Here's the thing I find structurally fascinating about this whole situation.

The same architectural property that makes relational databases a scalability liability for transactions is what makes them an asset for analytics. All the data in one logical blob means writes contend with each other, integrity enforcement compounds the scalability cost, and horizontal scaling requires increasingly expensive engineering and hardware. That's bad for transactions at scale. But all the data in one logical blob also means set operations can reach everything without gathering data from across a distributed system. That's great for analytics.

Cosmos DB flips that tradeoff. Sharding means writes scale horizontally and cheaply. Sharding also means set operations have to gather before they can compute. The tradeoff is symmetric: you can't have both "writes distribute without coordination" and "set operations reach everything without gathering" in the same architecture. Physics, not product management or tech skills, is the constraint.

Which means the question isn't "which database architecture is better?" It's "which database architecture is better for the specific workload?" And the answer is: it depends on the workload. Use the right tool for the job.

Which brings us to the next question: do you need to own and operate a relational database to get the analytical benefit? Or is there a way to get the set-operations-living-next-to-the-data experience on top of your Cosmos data, without maintaining a separate database and without building an ETL pipeline?

That's what the next chapter is about.

Next: Chapter 14: Stop Retreating to SQL — Reporting with Fabric.