Bring Back Scenario Analysis!

Why leaving behind spreadsheets made us forget how to ask "what if" questions.

In the most recent podcast episode, Sarah Krasnik dives into how configuration-as-code can automate away data work, why you might want to consider adding a data lake to your architecture, and how those looking to build a self-serve data culture can look to self-serve frozen yogurt shops for inspiration.

Enjoy!

- Tristan

---

What if…?!

One of the more controversial stances I’m on public record with is my undying love of spreadsheets:

I think that decision-makers—you know, the people who are actually responsible for making the operational decisions that data informs—have not been well-served in the modern data stack. Executives? Sure, they get amazing dashboards. Analysts? Absolutely. But there are a large number of people (like, hundreds of millions) whose primary professional tool is Excel, and I believe that their experience of data has actually gotten worse with the advent of the modern data stack. All the sudden they’re locked out from participating.

Since writing that post, the idea that maybe the modern data stack wasn’t serving every data stakeholder perfectly has achieved a level of acceptance. I don’t think that a universal love of spreadsheets in our community has followed, however.

Spreadsheets are often looked down on by “real” data professionals as the tools of the amateur, the business user (a term that I despise). This is because folks often pick up a spreadsheet for the first time, learn how to create simple formulas, and just go to town doing the adult equivalent of finger-painting:

Look, I made a spreadsheet! (image credit)

It really does get very messy very quickly. And I know this because I’ve made a lot of bad spreadsheets in my career. I was basically a spreadsheet jockey for all of my internships during college, and it was then that I created my most egregious finger paintings. At some point an interviewer was asking me about an approach I took to one project and I explained it to him:

Me: “Yes I have a separate column for each survey question, 300 in total!”

Him: “Have you ever heard of ‘third normal form’?”

Me: “No…”

Him: “Well, thanks for coming in!”

I was so scarred from that experience that over a decade later I had my mother-in-law participate in a wedding planning spreadsheet that did, in fact, adhere to 3NF (the event went great).

I think the whole “looking down on spreadsheets” thing has become more about identity than functionality though. We choose not to use spreadsheets because people who use spreadsheets are one type of people and we are a different type of people. Our code scales, is version-controlled, is rigorously tested. Our job titles—signifiers of identity that have real economic value!—are often directly associated with the tools we use. Solve a problem in Excel? You’re a business analyst. Solve that same problem in SQL + dbt + LookML? You’re an analytics engineer making 2x more. Solve that same problem in Python? You’re a data scientist. Economic incentives and tribal identity form a mutually reinforcing cycle that we’re all caught in.

Let’s try to take a step away from the identity stuff, though, for a minute. Let’s just talk about functionality.

The Primary Jobs of Data

Data is used by companies largely to answer these questions:

• What already happened?

• What will happen?

• What would happen if…?

The first—descriptive statistics—is the world that most of us live in. We attempt to build and scale systems that help an organization efficiently introspect its own operations. This is where the entire modern data stack—all built on SQL—is strongest today.

The second—predictive statistics—involves everything from time series forecasting to regression to self-driving cars. This work is typically done in Python and other imperative programming languages. It’s common for folks who spend most of their time in descriptive statistics to have lightweight tools in their tool belt here, but it’s rare for data analysts / analytics engineers spend more than 20% of their time on this.

The third—scenario analysis—is something that data professionals don’t really do today. Our internal data team does scenario analysis very rarely. In the 50+ consulting relationships I had over the early days of Fishtown Analytics, I think I did scenario analysis exactly one time (it was a pricing strategy project that I’m particularly proud of). The team who does a lot of this internally is actually FP&A!

Looking outside our own walls, I don’t read data people writing posts about scenario analysis best practices, I don’t hear anyone talking about scenario analysis success stories at events. This lack of interest is fascinating to me because, two decades ago, scenario analysis was the star of the show. The main reason you worked with numbers was to reason about the future using scenarios.

Learn to Love Uncertainty

Here’s a scenario analysis question: how long could the balance sheet for your ecommerce store sustain a 50% increase in CoGS due to global supply chain disruption? 100%? This is an entirely non-obvious question, and depends on factors as broad as your margins, cash balance, price elasticity of demand, customer acquisition cost, and more.

It’s a question that you really should have a rough answer to as the CEO at an ecommerce company, because, well, the global supply chain is a bit of a mess right now. If you’re in a race to gain market share and have a long runway, you may prefer to eat the increased shipping costs if you think they’ll be temporary. If you have short runway and inelastic demand, you may choose to pass increase costs along to customers. Nothing will help you answer this question as much as a great scenario analysis.

Scenario analysis, while often being quantitatively not-that-complex, is different from descriptive and prescriptive statistics in one key way that is culturally very hard for data people: it forces you to assume things you don’t know. Building an integrated model of the world requires that you describe relationships between different pieces of a puzzle explicitly, assuming a level of cause and effect that you can’t be totally confident in. It also requires that you provide specific starting values and trend lines that are sometimes wild guesses at the outset. But it also forces you to ask really important questions about the systems that you’re a part of: how does X impact Y? We ask this too infrequently today.

This can be deeply uncomfortable for people who are used to thinking of themselves as seekers of truth: they ask us to traffic in uncertainty:

Weirdly, predicting sales over the coming twelve months is actually “business as usual” if your ecommerce store has enough historical data. Predicting the impact of an event you have never observed before in a sufficiently complex system is something you can’t know. Scenario analysis helps you to reason about such possible futures in an inexact, directional way.

The funny thing is, even if you don’t know all of the inputs to a model at the outset, the process of constructing the model and tweaking it over time has a way of making those inputs more clear. You start to develop an intuitive feel for the functioning of the system as you develop a relationship with it longitudinally. After 24 months of tweaking a model for some system, you’ll often start to develop a high degree of trust in it even if you can’t conclusively prove why every causal relationship and why every trendline is true. Its success is empirical.

Of course, this is dangerous—past performance does not necessarily predict future results—but we needn’t let potential failure modes disqualify an entire reasoning tool. As long as you approach the exercise with humility and you keep an eye out for disruptive events that would change the underlying assumptions, the exercise can be done with maturity and rigor.

What’s Gone Missing

Maybe constructing scenarios isn’t the job of a data team? Maybe the data team is about doing the hard technical work of descriptive and predictive statistics, but reasoning about causal links and integrated models of the world should be left to folks on the business side? Maybe—and that’s how it seems to mostly be shaking out today. But I think that’s a missed opportunity.

The thing that I’ve observed, in the absence of data teams that create integrated models of the world and run them through scenarios, is that we fail to see the elephant. We know how to reason about the parts of the system that we are most familiar with, but we don’t spend as much time on the causal links and downstream impacts of changes in our metrics.

As a result, we understand our individual metrics better than ever, but we still have trouble formulating the strategy for an entire organization. For that we must be able to reason about the whole system.

If we want a seat at the table, this has to be the goal.

For all that can be said negatively about the impacts of finance quants with Excel models—as Warren Buffet said of the 2008 financial crisis, “beware of geeks bearing formulas”—there is real tribal knowledge around how to do scenario modeling well. Go through a Training the Street bootcamp and tell me that they aren’t every bit as serious about how they apply their craft as we analytics engineers are. Of course, the tools and methods are different, but the point is that it is actually quite possible to use spreadsheets very skillfully. It’s not only a place for finger painting.

Where Spreadsheets Shine

Spreadsheets can do almost anything you might want with data, but we now have tooling that is meaningfully superior when it comes to descriptive and predictive statistics (SQL / Python / R). The thing that spreadsheets continue to be truly fantastic at is scenario modeling.

The reason for this is simple: in spreadsheets it’s extremely easy to model complex causal relationships using cell references. A leads to B leads to C is a really normal way to think in a spreadsheet, and then it’s really easy to fiddle around with A in order to see what happens to C. You can do the same thing in SQL or Python but it’s far more arduous. I’ve previously created entire dbt sub-DAGs to model bifurcations in reality, each with a different assumed price, for example. This is ugly (to put it mildly).

What I’m excited for is an analytics-engineering-friendly spreadsheet: a spreadsheet that feels consistent with the AE workflow and tooling.

Let’s start with Equals: a built-from-scratch-for-the-MDS spreadsheet. Think of it as Google Sheets that bakes in a link to your data warehouse to easily grab source data. From there, I think you need four things to create a truly great scenario analysis tool:

1. A metric() function. It doesn’t feel particularly spreadsheet-native to write SQL directly inside of a sheet, or to import an entire table from your warehouse. Instead, you should write an array formula that looks like this: =metric(‘revenue’, ‘01/01/2020’, ‘12/31/2021’, month). The appropriate API for pulling data into a spreadsheet is a metric, not a table. This feels idiomatic, and it also makes the right assumptions about personas—the human constructing the model shouldn’t need to understand the underlying schema.

2. A native understanding of actuals vs. forecasts. One of the most onerous things about scenario modeling is maintaining your models over time. If you are wired into the source of truth via metrics, then you need the sheet to easily tick forwards every calendar period, grab new actuals, and update forecasts accordingly. This is doable with existing formula constructs but is certainly onerous. I recently learned to use Pigment, which makes this impressively straightforward.

3. A human-readable file format that can be code-reviewed and passed through a pull request process.

4. The ability to compile the entire model into a function in a language of your choosing, with defined inputs and outputs. Prototype in a sheet, then productionize in code.

If that tooling existed, I would be so excited for us to all start asking a lot more what-if questions.

From elsewhere on the internet…

David goes deeper on ROI. There’s a lot in this post and you should read the whole thing, but this snippet really hit home for me:

[At Lyst], it’s expected for the business to drive their own impact from the data provided to them. This does divorce data from value.

Here’s the conclusion (which hits hard):

Data work should drive measurable value; it should have a commercial target. It’s more fulfilling if it does. This is evident in my lack of fulfilment without the direct connection to value, having had it before. We need to help our organisations succeed to feel fulfilled and be valued as data practitioners.

🙌 🙌

Sarah Krasnik talks about modernity, or more specifically, why the focus on the “modern data stack” is counterproductive. The short version: it’s about business value, not tools. I 100% agree here, but the point of the modern data stack has always been that it allowed practitioners to work differently, and therein, achieve different (and better) outcomes.

“Modern” is always in reaction to something. Modern architecture in the early 1900’s is a great parallel: it arose out of advances in materials, was in reaction to more traditional styles that existed previously, and had its own distinct values (form follows function). Notably, advances in materials came first—this is what unlocked the new potential!

The revolution in materials came first, with the use of cast iron, drywall, plate glass, and reinforced concrete, to build structures that were stronger, lighter, and taller.

Similarly, advances in tooling had to happen first in order to unlock new workflows for data practitioners.

It continues to be called “modern architecture” today even though it’s no longer the dominant architectural style used—it is no longer strictly “modern”. That naming was simply the way that professionals of the period offset their own practice vs. that which had come before. This is important! In order to do something new, we have to have ways to talk about it.

Arpit Choudhury updates his 2021 post on data activation with news that’s happened in the past 12 months…and there’s been a lot. This space is evolving very quickly. And yes—I’m a fan of the rebranding (“reverse ETL” » “data activation”). Reverse ETL was never a good term.

Mikkel Dengsøe talks about data testing and why it’s so hard to operationalize well today. Fantastic and short read, highly recommend.

Chad Sanderson, head of data at Convoy, believes that the modern data warehouse is broken! The post is thoughtful and challenging—I see the central point as being how to manage schema migrations and avoid breakage. Who makes what promise to whom / what is the “API”?

My read is that much of this could be solved by software engineering teams owning the “staging” layer of the dbt DAG for their subsystems. They own the source data, so they should be responsible for providing a consistent API to that data in the warehouse.

❗ TiDB, an HTAP database I just heard of for the first time, just released a dbt adapter!