Data Warehousing Fundamentals

Here we'll be covering the fundamentals of what a Data Warehouse is, it's purpose and why you need one. This is intended for people without prior knowledge of Data Warehousing.

Imagine you’ve just moved into a new house, and your things are scattered everywhere. Your books are mixed with kitchen utensils, and your shoes are somehow in the pantry. To make your life easier, you’d want to organize everything, putting similar items in one place. That’s essentially what a data warehouse does for a business. It’s a dedicated system that organizes and consolidates information from various parts of a business, so it’s all stored neatly, ready to be used.

But wait, why would anyone need a data warehouse? Isn’t data already being stored in databases? It is, but here’s the catch: data in businesses is often stored in many different places, like in different rooms of your house. Sales records might be in one system, customer data in another, and product details somewhere else. Not only that, but the way these systems record information can be wildly different, making it difficult to combine and analyze everything as a whole. That’s where a data warehouse comes in. It’s the big, organized storage unit for all this information, designed to help you understand and use it better.

Why You Need a Data Warehouse

Think of a company as a giant, complex organism with thousands of moving parts. Data flows constantly — sales data, customer support interactions, financial transactions, product inventories — all these streams of information exist in their own silos. A data warehouse takes all these disorganized streams and brings them together into one unified, clean, and organized place. It allows businesses to make better decisions based on a complete and accurate view of their data. Think of your data as legos, when they're just spread out and disorganized, they don't really look like anything in particular (more than legos obviously) and you run a risk of stepping on one. Anybody with children will tell you that isn't very pleasant experience. But if you assemble them you can tell a story, and ultimately take better, informed decisions.

Let’s say you’re the manager of a retail chain. You want to know which products are selling best in different regions, whether customer complaints are rising, or if there are patterns in how customers buy. All that data might be trapped in separate systems. By consolidating it into a data warehouse, you can connect the dots and gain valuable insights. Without it, you’re essentially trying to find patterns in a chaotic mess.

Business Point of View: The Key to Success

Here’s something crucial: a data warehouse isn’t just about dumping data in one place; it’s about transforming that data so it makes sense from a business perspective, essentially turning it into information and insights. Imagine you’re organizing your house, but instead of grouping things by how you use them, you group them by where they came from. Now, your bathroom towels are next to your milk because they both came from the store. This wouldn’t make your life any easier, and probably give you sour milk or cold towels.

In the same way, a data warehouse should model data according to how the business uses it, not how it happens to be stored in different systems. For example, in a business, a “customer” might mean different things in different systems — in sales, it might refer to anyone who has ever made a purchase; in support, it could refer to anyone who has ever called in. A data warehouse organizes this to create one unified version of “customer” that makes sense to the business as a whole.

The Four Pillars of a Data Warehouse

So, what makes a data warehouse special? Why is it different from your regular old database? There are four fundamental properties that every data warehouse shares, and they help ensure that businesses can rely on it to provide consistent, accurate, and meaningful insights.

It is important to understand that Data Warehouse as a concept is not a technical solution. Data Warehouse is a logical design pattern that you can apply on almost any technology. So independent of what database engine / platform you use, you can apply the principles and rules on how to build a Data Warehouse. So, what are the fundamental principles that your solution need to fulfill to be seen as a Data Warehouse?

Subject-Oriented

A data warehouse is subject-oriented, meaning it organizes data around specific areas of interest for the business, such as customers, sales, or products. In a traditional database, data might be stored according to how the individual systems record it — think of it as organizing your house based on where you bought things rather than how you use them. In a data warehouse, everything is organized around business subjects, so it makes sense to those using it.

Integrated

Integration is where the magic happens. A data warehouse pulls in data from all sorts of different sources (think of all the different rooms in your house) and then integrates it. It standardizes formats, cleans up inconsistencies, and makes sure that when you’re talking about “sales” from one system, it means the same thing as “sales” from another. This integration is what makes a data warehouse so powerful, because it enables you to compare apples to apples.

Time-Variant

A data warehouse also keeps track of changes over time. In your operational systems, you might just want to know the current state of things—what are today’s sales, what products are in stock right now? But in a data warehouse, you want to be able to look back and see how things have changed over time. It’s like a time machine for your business data. You can see trends, make predictions, and understand historical performance, which is essential for decision-making.

Non-Volatile

Finally, a data warehouse is non-volatile. Once data is loaded into the warehouse, it doesn’t get overwritten or deleted. You’re building a permanent record of your business’s data, like an archive. This is important because you can always go back to the data you loaded last month or last year, ensuring you never lose valuable information. This is also a very important aspect for audibility. In industries where there are regulations that require you to have the ability to reproduce (old) reports, we can’t remove or update data.

The Three-Layered Architecture

Now that we’ve talked about what a data warehouse is, let’s dive into how it works. Every data warehouse has a three-layered architecture, each with its own purpose. These conceptual layers can be set up as many more physical layers or even less physical layers. But it is important to understand each conceptual layer and their purpose in a Data Warehouse architecture.

Staging Layer (preferably persisted!)

Think of this as the kitchen of the data warehouse. When data comes in from all sorts of sources — sales systems, customer support, finance — it’s often messy and inconsistent, just like the raw ingredients in your kitchen. The staging layer is where all this raw data is collected and cleaned up, getting it ready to be used. In this phase, the data is checked for errors, inconsistencies, and missing values. It’s a crucial step because bad data in equals bad insights out.

Integration Layer

Once the data is cleaned up it is moved and transformed into the integration layer. The data in the integration layer is organized and stored so it represents the business, both semantically and structurally. This layer has as purpose to create a data representation that would make it look like the company only have one large system, and not many different operational systems. Even if we would have four different sales systems, there will in the Integration layer only have one representation of Sales. This layer has to have a focus on handling change and reusability of data. Therefore it is not organized to support specific analytical use cases (reports etc). The Integration layer is where the data is represented/stored according to the fundamental principles of Data Warehouse design - Subject Oriented, Integrated, Time-Variant, Non-Volatile.

Access Layer

Finally, we have the access layer. This is where data gets specialized to support specific analytical use cases through Data Products. The Access layer should get its data from the Integration Layer, where data has been cleaned, structured and semantically transformed for us to create Data Products independent of what the underlying source systems are and how the different source systems work. Through that create an ability to reuse data with the same definition and create the same values independent of user. Through this we create a way of getting consistent results, independent of what Data Product we build.

Data Warehousing is like turning the chaos of everyday business into organized, accessible knowledge.

Fun fact: Daana is actually an old word in Farsi that means "Knowledge"

By structuring data around business definitions, cleaning it up, and organizing it across time, businesses can make more informed decisions much easier. And with its three-layer architecture, the data warehouse ensures that the right information is always ready to be used - and used again and again and again with the same result.

How Daana Fits Into the Picture

Now imagine you’ve got a massive library, but it’s not organized. You’ve got history books next to recipe books, and no one knows where anything is. That’s your Data Lake—lots of information but no structure. Daana steps in as the ultimate librarian, but here’s the twist: it doesn’t just organize the books; it asks you what sections of the library you care about.

Daana starts by making you define the business view of your information. Instead of worrying about where the data is coming from, Daana forces you to think like a business owner. You’ve got to answer questions like, “What does a ‘customer’ mean to my business? How do I define ‘sales’ or ‘products’?” Once you have this business-friendly model in place, Daana connects the dots with your Data Lake (whether it’s Big Query, Snowflake, or even MSSQL), mapping the raw data to your business definitions.

But here’s where it gets interesting: Daana doesn’t stop at just understanding the business. It automatically builds the integration layer and access layer for you, based on that business view, and it does it without moving any data out of your environment. All your data stays right where it is, safe in whatever storage solution you’re using. And the best part? Daana builds all the data pipelines automatically, with no engineers required to write a single line of code except for the transformations of attributes in your Information Model.

If you ever need to check on how your data is flowing, Daana gives you a complete picture of the data lineage. It’s like following the trail of breadcrumbs all the way from raw data to polished reports. And to top it off, Daana provides a comprehensive data dictionary, so both business folks and engineers can speak the same language when they’re talking about the data. In short, Daana doesn’t just organize your data—it teaches your business how to use it effectively.