Around 2010, I was running the analytics department for a large grocery retailer. We had one of the most sophisticated tools for analyzing the data. We had a fairly competent team. We had mature processes. In short, we had all the required ingredients for complex analyses.
And yet, my analysts took four hours to answer the simple question – “how many apples did we sell last week?”. if you asked two different analysts, you would get two – possibly three different numbers. And none of them were wrong.
Welcome to the challenges of database schema.
A schema represents that logical and physical structure of data in a relational database.
Schemas decide how data is stored (and by extension, how the data is accessed) The data storage rules impact how the data is aggregated. All analyses are built on aggregated data. For example, you aggregate the millions of transactions by day or week, so that you don’t have to add it up every single time.
Schemas provide structure and structure builds efficiency. At a time when computing was relatively expensive, schemas allowed you to do more with less.
Structure also builds rigidity. Database schema cannot be changed easily. Extracting data out of 2 different schemas is a nightmare.
Back to the question of apples. The retailer had grown through acquisitions. There were two major databases and two schema. In one of them, apples were stored as a category. The sub-categories were fresh and frozen. In the second retailer, the category was Produce. And it had red apples and green apples. In the first retailer, a calendar week started on a sunday. In the 2nd retailer, it started on a saturday.
How much structure we need in a database is a function of efficiency vs. agility. The modern cloud-native databases are built with flexible schema. These databases are optimized for maximum fungibility of data. They are built knowing that computing is available on demand. They are built for today’s VUCA (volatile, uncertain, complex, ambiguous) world, where the biggest competitive advantage comes from staying nimble.
The ratio of data sitting in structured databases vs. semi-structured or cloud-native databases is a proxy indicator of how advanced the company is in its digital journey.
Leave a Reply