Data Warehouse Design – Short Tutorial

As your company grows and you start to think about taking advantage of business intelligence services, creating a data warehouse is one of the very first steps to take. Data warehouses play a key role in business intelligence, and, therefore, need to be well-thought-out. In this article, we are going to bring this question forward. We will talk about the data warehouse design principles, and also tackle the agile data warehouse design issue. Let’s find out what do you need to know about data warehouse design!

First things first. The data warehouses are immensely important in any company that wants to use business intelligence and big data in order to improve the decision-making process within it, and, thus, speed up its development and growth. Although they aren’t on the front line, they play an essential role in the background. In fact, without them, business intelligence wouldn’t have been possible! But what exactly are data warehouses? And how to design a data warehouse? You’re about to find that out.

Key Takeaways

• A data warehouse consolidates data from multiple sources into a single repository optimized for analytical queries and business intelligence — distinct from a transactional database, which is optimized for individual record updates.
• The five core components of a warehouse are the database, ETL/ELT tools, metadata, query tools, and the data warehouse bus.
• Two design approaches dominate: top-down (Inmon) — build the enterprise warehouse first, then marts; bottom-up (Kimball) — build marts first and integrate them later.
• Data modeling typically uses a star schema (simple, fast), snowflake schema (normalized, storage-efficient), or fact constellation (multiple linked fact tables for enterprise scale).
• Modern warehouse design in 2026 increasingly means cloud-native platforms (Snowflake, BigQuery, Databricks SQL, Microsoft Fabric), ELT instead of ETL, real-time ingestion, and often the lakehouse pattern rather than a classical warehouse.

What is a data warehouse?

If you run a company, most likely, you store your data in various places and forms. For instance, let’s say you have:

• Customer data in your CRM software
• Bills and invoices in PDF and JPG files
• Website stats in Google Analytics
• Your e-mail data in XLS/CSV files

And so on.

You see, nowadays, data comes in various sources and forms. The idea behind building a data warehouse is to combine all these different forms of data in one, versatile tool. And that tool is a data warehouse! There are also data lakes, but that’s a subject for a separate article.

We can say that data warehouses are repositories that collect data from various systems and sources and use online analytical processing (OLAP) to query that data for better business insights. Naturally, there is no single all-purpose data warehouse. It has to be built and designed in accordance with the requirements of a given company.

Data warehouse design from the technical point of view

The data warehouse is based on an RDBMS (Relational Database Management System) server, which is a central information repository that is surrounded by other components necessary to make the entire environment functional. Obviously, the structure of a data warehouse may vary depending on its purpose and the company’s needs.

However, we can indicate five major components of a DW:

• The database (it’s the central element, the foundation of the entire DW structure)
• ETL tools (data within a data warehouse has to be preprocessed before it can be transferred into a data warehouse, and this is what ETL tools are for)
• Metadata (it specifies the source, usage, and features of data stored within a data warehouse)
• Query tools (they allow you to interact with the data warehouse system and obtain necessary insight/knowledge)
• BUS (the data warehouse bus determines the flow of data and allows to integrate data marts into one large data warehouse)

That’s the theory, but usually, you don’t have to build a data warehouse entirely from scratch. There are many platforms available on the market that can be used with ease in order to build your own, customized warehouse.

The most widely used data warehouse platforms today are Snowflake, Google BigQuery, Amazon Redshift, Databricks SQL (lakehouse-native), Microsoft Fabric / Azure Synapse Analytics, and Teradata Vantage. Each takes a slightly different approach to storage, compute and pricing, but all share the modern cloud-native fundamentals: separation of storage and compute, columnar storage, and elastic scaling.

Data warehouses can be built on-premise (locally, within your company’s structure) or in the cloud. The second option is usually cheaper and quicker to implement.

Data warehouses–two approaches

Generally speaking, there are two major approaches to design a data warehouse:

• Top-down design
• Bottom-up design

What’s the difference between these two approaches? In the first approach, you start ‘at the top’–by designing the data warehouse structure first. The second approach, you start ‘at the bottom’–by creating data marts first.

Now, a short digression: data marts are smaller versions of data warehouses or, rather, data warehouse subsets. They are focused on a single subject, while a data warehouse is designed to address many problems and comprise many subjects within one company.

In the first approach, the data warehouse is built first… In the second approach, data marts are built first. In such a situation, data marts are made after data warehouse and serve as a support for the central structure. then, in the second approach, data marts are built first, and, then, they are integrated into one, larger data warehouse. Integration is implemented using a data warehouse bus.

The bottom-up design is quicker and more flexible, but it can be difficult to maintain. On the other hand, the top-down design is easier to maintain and robust against business changes, but it’s slower and more complex to set up. In many instances, it’s also more expensive, especially at the very beginning.

To sum up, there is no single universal approach. It all depends on many factors, and each company should decide which data warehouse design strategy is best.

Modern data warehouse design: what’s changed since the classical model

The fundamentals above — fact tables, dimensions, ETL, OLAP — still apply. But several shifts have reshaped how warehouses are designed in practice today:

• From ETL to ELT. In the classical model, data was transformed before being loaded into the warehouse. Modern cloud warehouses (Snowflake, BigQuery, Redshift, Databricks SQL) are powerful enough to transform data after loading — a pattern called ELT (Extract → Load → Transform). Tools like dbt have made this the dominant approach, because transformations live as version-controlled SQL inside the warehouse rather than in a separate ETL tool.
  Separation of storage and compute. Older warehouses tied storage and processing together — scaling one meant scaling the other. Cloud-native warehouses decouple them, so you can store petabytes cheaply and only pay for compute when queries run. This is what makes modern warehouses elastic and (usually) cheaper to operate.
• Columnar storage by default. Modern warehouses store data column by column rather than row by row, which dramatically speeds up analytical queries (which typically scan few columns across many rows). It’s also why query performance is usually less about indexes and more about clustering keys, partitioning, and query planning.
• The lakehouse pattern. A data lakehouse combines the flexibility of a data lake (cheap storage for all data types) with the structure of a data warehouse (ACID transactions, schema, performance). Built on open table formats like Delta Lake, Apache Iceberg, and Apache Hudi, lakehouses let you keep one copy of your data and serve both BI workloads and machine learning from it. For greenfield projects in 2026, this is often the right starting point instead of a classical warehouse — we explore this in more depth in our piece on modern data architecture.
• Real-time and streaming ingestion. Classical warehouses ran nightly batch loads. Today, change-data-capture (CDC) tools and streaming pipelines (Kafka, Kinesis, Snowpipe Streaming) push fresh data into the warehouse in minutes — sometimes seconds. This blurs the line between warehouse and operational database for an increasing number of use cases.

If you’re designing a warehouse today, the question isn’t only star vs snowflake — it’s also classical warehouse vs lakehouse, ETL vs ELT, batch vs streaming, single-vendor vs open table formats. The right answer depends on your scale, regulations, and existing stack.

Agile data warehouse design

Agile comprises various approaches to software development and is based on adaptive planning, evolutionary development, early delivery, and continual improvement. Also, it encourages rapid and flexible response to change[2].

According to Agile, data warehouse design should be done in such a way:

• Individuals and interactions over processes and tools.
• Working data warehouse systems over comprehensive documentation.
• Collaboration with end-users and stakeholders over contract negotiation.
• Responding to change over following a detailed plan.

There is one primary goal in the Agile approach: To build the data warehouse systems that work, add real, measurable value to the organization, and are built with high-quality standards in mind. Simply put, it’s all about making a useful solution, and not art for art’s sake.

We work in the same way. We always want to address the real customers’ needs and help them to do business in a quicker, more effective, and better way. This is our main goal, and this is what you can expect from cooperation with us!

Steps in data warehouse design

In general, business intelligence companies like Addepto design data warehouses on behalf of their clients. Our experience and know-how ensure that the data warehouse you receive iis thought-out and tailored to your company’s needs.

If you’d like to see what the data warehouse designing process looks like, we have a short description for you right here:

Think about your needs and requirements

As we told you earlier, there is no one versatile data warehouse. It has to be built in accordance with your needs. That’s why every department in your company should be engaged in this process. You have to know what you need the data warehouse for. Every department needs to understand the purpose of it, how it will benefit them, and what kind of results they can expect. Only then, it’s possible to design a data warehouse that’s efficient and useful.

Set up the physical environments

There are three significant environments within a data warehouse–development, testing, and production. Why do you need these three elements? All these environments have different resource needs, therefore trying to combine all functions into one structure can be terrible for performance. Naturally, there can be more environments, designed to suit specific business needs, for instance, the quality assurance environment.

Data modeling

Data modeling is where your warehouse stops being theory and becomes structure. It defines how data is organized, related, and queried — and shapes both performance and how easy the warehouse will be to maintain. There are three modeling schemas you’ll meet most often:

• Star schema. The most common warehouse model. One central fact table (containing measurable business events like sales, transactions, page views) is surrounded by dimension tables (describing the who, what, where, when — customers, products, stores, dates). The shape of a star, with the fact table in the middle and dimensions radiating outward, is where the name comes from. Star schemas are simple, fast to query, and the default choice for most BI workloads.
• Snowflake schema. A variant of the star schema where dimension tables are further normalized — broken into sub-dimensions to reduce redundancy. For example, a Product dimension might be split into Product → Category → Department. This saves storage and enforces data integrity, but adds joins and complexity. Use it when storage matters more than query speed, or when dimensions are large and hierarchical.
• Fact constellation (galaxy schema). Multiple fact tables sharing the same dimension tables. This is what you build when a single warehouse needs to support several related but distinct business processes — for example, sales, returns, and inventory, all linked to the same customer and product dimensions. More complex to design, but realistic for enterprise warehouses.

Two more concepts you’ll meet immediately:

• Slowly Changing Dimensions (SCD) — how to handle dimensions that change over time, like a customer changing address. SCD Type 1 overwrites the old value; Type 2 keeps a full history with versioning; Type 3 keeps both current and previous values. Choose based on whether your reports need to reflect history accurately.
• Grain — the level of detail in each fact table row. Decide grain before building anything else — every other modeling decision flows from it. “One row per order line” and “one row per order” produce very different warehouses.

A good data model is the difference between a warehouse that answers questions in seconds and one that takes minutes — and between one that’s easy to extend and one nobody wants to touch.

Design the ETL process

As you know from one of our previous articles, ETL is a data-related, three-stage process. In general, it is about transferring data into a data warehouse. First, you have to EXTRACT data from different source systems. Then, data needs to be TRANSFORMED and finally LOADED into a given data warehouse system. If you’d like to know how exactly this process happens, we encourage you to read our article about the ETL process.

Design pre-aggregations and query performance layers

Analytical queries often re-compute the same things — daily sales totals, monthly active users, revenue by region. Pre-computing those once and reusing the result is what keeps dashboards fast.
In classical warehouses, this was done by building OLAP cubes — multidimensional structures pre-calculated for fast slicing and dicing. The term you’ll still hear, but modern cloud warehouses have largely replaced explicit cubes with:

• Materialized views — pre-computed query results that the warehouse keeps current automatically (Snowflake, BigQuery, Redshift all support these).
• Clustering and partitioning — physical organization of data on disk so the engine reads less.
• Aggregation tables — denormalized summary tables (daily metrics, monthly KPIs) built and refreshed by your transformation tool (dbt, Dataform).
• Semantic layers — a separate layer (LookML, dbt Semantic Layer, Cube.dev) that defines metrics once and serves them consistently to BI tools.

Whatever approach you use, the design principle is the same: don’t make end-user dashboards do heavy lifting at query time. Compute it once, store it, refresh it on a schedule.

Front-end

Now, it’s time to tackle the front-end issue. Simply put–your data warehouse needs visualization if you want it to be a valuable asset in your company. Front-end development is all about how users will access data for analysis and run reports. Thanks to front-end visualization, users can immediately understand and apply the results of data queries. That’s why it’s so important not to go overboard with front-end design. Keep it simple and legible. If you’re struggling with this stage, we advise you to go back to point 1. Think about your needs and requirements first. Moreover, at this point, you ought to ensure access to the data warehouse from any device.

Optimization and rollout

The last thing to do is to optimize your data warehouse. Try to maximize workflows in order to speed up queries and the way your data warehouse works. Conduct necessary tests and initial training for your staff. Only then can they fully benefit from this new solution. Your employees have to understand how “their” data warehouse works and how it can improve their everyday work. Devote at least 2-3 weeks and help your staff get acquainted with a data warehouse and learn how to use it.

To sum up, data warehouses can be immensely helpful. With this foundation, you are ready to implement business intelligence into your company and start working far more efficiently. BI improves the decision-making process and gives you an entirely new insight into the data you possess. But there is one vital condition. You have to understand WHAT IS IT FOR. Only then can you fully benefit from the fantastic tool the data warehouses are!

Designing a data warehouse well isn’t about adopting the trendiest platform — it’s about deeply understanding what questions your business needs to answer, choosing the right schema for the way your data flows, and resisting complexity that doesn’t earn its keep. The technical choices (Snowflake or Databricks, star or snowflake schema, ETL or ELT) only matter once those fundamentals are right.

If you’d like a second opinion on your current warehouse design — or help building one from scratch with the right modern foundation — book a 30-minute call with our data engineering team. We’ll walk through your data, your reporting needs, and the trade-offs of each platform option together.

References

[1] G2. Best Data Warehouse Software. URL: https://www.g2.com/categories/data-warehouse. Accessed May 7, 2020.

[2] Wikipedia. Agile software development. URL: https://en.wikipedia.org/wiki/Agile_software_development. Accessed May 7, 2020.