The Ultimate Guide to Data Warehouse Design

Data warehouses help you run logical queries, build accurate forecasting models, improve real-time data analysis, and identify trends impacting your organization.

But what goes into designing a data warehouse?

In short here are the 8 steps to data warehouse design:

1. Gather Requirements: Aligning the business goals and needs of different departments with the overall data warehouse project.
2. Set Up Environments: This step is about creating three environments for data warehouse development, testing, and production, each running on separate servers
3. Data Modeling: Design the data warehouse schema, including the fact tables and dimension tables, to support the business requirements.
4. Develop Your ETL Process: ETL stands for Extract, Transform, and Load. This process is how data gets moved from its source into your warehouse.
5. OLAP Cube Design: Design OLAP cubes to support analysis and reporting requirements.
6. Reporting & Analysis: Developing and deploying the reporting and analytics tools that will be used to extract insights and knowledge from the data warehouse.
7. Optimize Queries: Optimizing queries ensures that the system can handle large amounts of data and respond quickly to queries.
8. Establish a Rollout Plan: Determine how the data warehouse will be introduced to the organization, which groups or individuals will have access to it, and how the data will be presented to these users.

Whether you choose to use a pre-built vendor solution or to start from scratch, you'll need some level of warehouse design to successfully adopt a new data warehouse and get more from your big data.

What is a Data Warehouse?

A data warehouse is a centralized repository where an organization can store substantial amounts of data from multiple source systems and locations. These sources and locations include:

• Customer relationship management (CRM) systems
• Enterprise resource planning (ERP) systems
• SaaS tools
• Relational databases
• Transactional databases

Essentially, data warehouses house all the essential data businesses need to run analyses and collect the valuable business insights reflected in that data. The data warehouse is the ultimate destination supporting business intelligence (BI) activities such as identifying trends and making smarter organizational decisions.

Some of the top benefits of a data warehouse include:

• Consistency: Data warehouses compile data from multiple sources and clean it, creating consistency among all data.
• Security: A data warehouse provides security because it's a stable, nonvolatile entity that doesn't change over time. 
• Saves time: A data warehouse helps organizations and individual employees save time by acquiring data within seconds.
• Data governance: A data warehouse can make it easier to adhere to data governance guidelines such as GDPR and CCPA rather than relying on legacy systems to safeguard sensitive data.
• Removes data silos: A data warehouse removes existing silos in your organization, such as legacy systems in different departments unable to communicate.

Consider this example of a data warehouse in action: you’re trying to figure out the overall value of your leads in Salesforce. You could push your Salesforce data into your data warehouse, set up a schema, and run a query that tells you which marketing activities led to your highest-value prospects. The results of the query outline how to target those high-value prospects more easily with focused marketing materials, move them through your sales funnels, and — ultimately — increase revenue.

Examples of data warehouses include Snowflake, Amazon Redshift, Microsoft Azure, and IBM Db2.

8 Steps in Data Warehouse Design

Here are the eight core steps that go into data warehouse design:

1. Defining Business Requirements (or Requirements Gathering)

Data warehouse design is a business-wide journey. Data warehouses touch all areas of your business, so every department needs to be on board with the design. Since your warehouse is only as powerful as the data it contains, aligning departmental needs and goals with the overall project is critical to your success.

So, if you currently can't combine all your sales data with all your marketing data, your overall query results are missing some critical components. Knowing which leads are valuable can help you get more value from your marketing data.

Every department needs to understand the purpose of the data warehouse, how it benefits them, and what kinds of results they can expect from your warehousing solution.

This Requirements Gathering stage should focus on the following objectives:

• Aligning departmental goals with the overall project
• Determining the scope of the project in relation to business processes
• Discovering your current and future needs by diving deep into your data (finding out what data is useful for analysis) and your current tech stack (where your data is currently siloed and not being used)
• Creating a disaster recovery plan in the case of system failure
• Thinking about each layer of security (e.g., threat detection, threat mitigation, identity controls, monitoring, risk reduction, etc.)
• Anticipating compliance needs and mitigating regulatory risks

You can think of this as your overall data warehouse blueprint. But this phase is more about determining your business needs, aligning those to your data warehouse, and, most importantly, getting everyone on board with the data warehousing solution.

Related Reading: What to Consider When Selecting a Data Warehouse for Your Business

2. Setting Up Your Physical Environments

Data warehouses typically have three primary physical environments — development, testing, and production. This mimics standard software development best practices, and your three environments exist on completely separate physical servers.

Why do you need three separate environments?

• You need a way to test changes before they move into the production environment.
• Some security best practices require that testers and developers never have access to production data.
• Running tests against data typically uses extreme data sets or random sets of data from the production environment — and you need a unique server to execute these tests en masse.
• Having a development environment is a necessity, and dev environments exist in a unique state of flux compared to production or test environments.
• Production environments have much higher workloads (your whole business is using it), so trying to run tests or develop in that environment can be stressful for both team members and servers.
• Data integrity is much easier to track, and issues are easier to contain when you have three environments running. It makes headhunting issues less stressful on your workloads, and data flow in production and testing environments can be stalled without impacting end users.
• Running tests can often introduce breakpoints and hang your entire server. That's not something you want happening in your production environment.
• Imagine sharing resources between production, testing, and development. You don’t want that! Testing, development, and production environments all have different resource needs, and trying to combine all functions into one server can be catastrophic for performance.

Remember, BI development is an ongoing process that really never grinds to a halt. This is especially true in Agile/DevOps approaches to the software development lifecycle, which all require separate environments due to the sheer magnitude of constant changes and adaptations.

You can choose to run more than these three environments, and some business users choose to add additional environments for specific business needs. Integrate.io has seen staging environments that are separate from testing solely for quality assurance work, as well as demo and integration environments specifically for testing integrations.

You should have these three core environments, but you can layer in additional settings to fit your unique business goals.

3. Data Warehouse Design: Introducing Data Modeling

Data modeling is the process of visualizing data distribution in your warehouse. Think of it as a blueprint. Before you start building a house, it's important to know what goes where and why it goes there. That's what data modeling is to data warehouses.

Data modeling helps you:

• Visualize the relationships between data
• Set standardized naming conventions
• Create relationships between data sets
• Establish compliance and security processes
• Align your processes with your overarching IT goals

The above benefits of data modeling help improve decision-making throughout your organization.

However, data modeling is probably the most complex phase of data warehouse design, and there are multiple data modeling techniques businesses can choose from for warehouse design. Before jumping into a few of the most popular data modeling techniques, let's take a look at the differences between data warehouses and data marts:

A data warehouse is a system to store data in (or push data into) to run analytics and queries. A data mart, on the other hand, is an area within a data warehouse that stores data for a specific business function.

So, say you've built your entire data warehouse. That's great! But does it account for how different departments will use the data? Your sales team will use that data warehouse in a vastly different way than your legal team. Plus, certain workflows and data sets are only valuable to certain teams. Data marts are where all those team-specific data sets are stored, and related queries are processed.

Data modeling typically takes place at the data mart level and branches out into your data warehouse. It's the logic behind how you store certain data in relation to other data.

The three most popular data models for warehouses are:

1. Snowflake schema
2. Star schema
3. Galaxy schema

You should choose and develop a data model to guide your overall data architecture within your warehouse. The model you choose will impact the structure of your data warehouse and data marts — which impacts the ways that you utilize ETL tools like Integrate.io and run queries on that data.

Related Reading: Snowflake Schema vs Star Schema

4. Choosing Your Extract, Transform, Load (ETL) Solution

ETL or Extract, Transform, Load is the process used to pull data out of your current tech stack or existing storage solutions and put it into your warehouse. It goes something like this:

• You extract data from a source system and place it into a staging area.
• You transform that data into the best format for data analytics. You also remove any duplicated data or inconsistencies that can make analysis difficult.
• You then load the data to a data warehouse before pushing it through BI tools like Tableau and Looker.

Normally, ETL is a complicated process that requires manual pipeline-building and lots of code. Building these pipelines can take weeks or even months and might require a data engineering team. That’s where ETL solutions come in. They automate many tasks associated with this data management and integration process, freeing up resources for your team.

You should pay careful attention to the ETL solution you use so you can improve business decisions. Since ETL is responsible for the bulk of the in-between work, choosing a subpar tool or developing a poor ETL process can break your entire warehouse. You want optimal speeds, high availability, good visualization, and the ability to build easy, replicable, and consistent data pipelines between all your existing architecture and your new warehouse.

This is where ETL tools like Integrate.io are valuable. Integrate.io creates hyper-visualized data pipelines between all your valuable tech architecture while cleaning and nominalizing that data for compliance and ease of use.

Remember, a good ETL process can mean the difference between a slow, painful-to-use data warehouse and a simple, functional warehouse that's valuable throughout every layer of your organization.

ETL will likely be the go-to for pulling data from systems into your warehouse. Its counterpart Extract, Load, Transfer (ELT) negatively impacts the performance of most custom-built warehouses since data is loaded directly into the warehouse before data organization and cleansing occur. However, there might be other data integration use cases that suit the ELT process. Integrate.io not only executes ETL but can handle ELT, Reverse ETL, and Change Data Capture (CDC), as well as provide data observability and data warehouse insights.

Related Reading: ETL vs ELT

5. Online Analytic Processing (OLAP) Cube

OLAP (Online Analytical Processing) cubes are commonly used in the data warehousing process to enable faster, more efficient analysis of large amounts of data. OLAP cubes are based on multidimensional databases that store summarized data and allow users to quickly analyze information from different dimensions. 

Here's how an OLAP cube fits into the data warehouse design:

• OLAP cubes are designed to store pre-aggregated data that has been processed from various sources in a data warehouse. The data is organized into a multi-dimensional structure that enables users to view and analyze it from different perspectives.
• OLAP cubes are created using a process called cube processing, which involves aggregating and storing data in a way that enables fast retrieval and analysis. Cube processing can be performed on a regular basis to ensure that the data is up-to-date and accurate.
• OLAP cubes enable users to perform complex analytical queries on large volumes of data in real-time, making it easier to identify trends, patterns, and anomalies. Users can also slice and dice data in different ways to gain deeper insights into their business operations.
• OLAP cubes support drill-down and roll-up operations, which allow users to navigate through different levels of data granularity. Users can drill down to the lowest level of detail to view individual transactions or roll up to higher levels of aggregation to view summary data.
• OLAP cubes can be accessed using a variety of tools, including spreadsheets, reporting tools, and business intelligence platforms. Users can create reports and dashboards that display the data in a way that is meaningful to them.

You'll likely need to address OLAP cubes if you're designing your entire database from scratch, or if you're maintaining your own OLAP cube — which typically requires specialized personnel.

So, if you plan to use a vendor warehouse solution (e.g., Redshift or BigQuery) you probably won't need an OLAP cube (cubes are rarely used in either of those solutions*.)

*Note: some vendor solutions will let you build OLAP cubes on top of Redshift or BigQuery data marts, but Integrate.io can't recommend any since it has never used them personally.

If you have a set of BI tools requiring an OLAP cube for ad-hoc reporting, you may need to develop one or use a vendor solution.

OLAP Cubes vs. Data Warehouse

Here are the differences between a data warehouse and OLAP cubes:

• A data warehouse is where you store your business data in an easily analyzable format to be used for a variety of business needs.

• Online Analytic Processing cubes help you analyze the data in your data warehouse or data mart. Most of the time, OLAP cubes are used for reporting, but they have plenty of other use cases.

These resources on OLAP cubes can help you dig deeper:

• Overview of Service Manager OLAP cubes for advanced analytics (Microsoft)

• OLAP Cubes (OLAP.com)

• Understanding Cubes (Oracle)

6. Data Warehouse Design: Creating the Front End

So far, this guide has only covered back-end processes. There needs to be front-end visualization, so users can immediately understand and apply the results of data queries.

That's the job of your front end. There are plenty of tools on the market that help with visualization. BI tools like Tableau (or PowerBI for those using BigQuery) are great for visualization. You can also develop a custom solution — though that's a significant undertaking.

Most small-to-medium-sized businesses lean on established BI kits like those mentioned above. But, some businesses may need to develop their own BI tools to meet ad-hoc analytic needs. For example, a Sales Ops manager at a large company may need a specific BI tool for territory strategies. This tool would probably be custom-developed given the scope of the company’s sales objectives.

You should pay keen attention to reporting during this stage. How often does reporting need to be done? Do you need each person to create their own reports? Questions like these should guide you to a BI toolkit that fits your unique requirements.

Pro-tip: Keep it simple. Your employees don't care about most of the fancy features or deep complexities. They just want something that works for them and makes their lives easier.

7. Optimizing Queries

Optimizing queries is a critical part of data warehouse design. One of the primary goals of building a data warehouse is to provide fast and efficient access to data for decision-making. During the design process, data architects need to consider the types of queries that users will be running and design the data warehouse schema and indexing accordingly. 

Optimizing your queries is a complex process that's hyper-unique to your specific needs. But there are some general rules of thumb.

We heavily recommend the following during database design:

• Ensure your production, testing, and development environments have mirrored resources. This mirroring prevents the server from hanging when you push projects from one environment to the next.

• Try to minimize data retrieval. Don't run SELECT on the whole database if you only need a column of results. Instead, run your SELECT query by targeting specific columns. This is especially important if you're paying for your query power separately.

• Understand the limitations of your OLAP vendor. BigQuery uses a hybrid SQL language, and RedShift is built on top of a Postgre fork. Knowing the little nuances baked into your vendor can help you maximize workflows and speed up queries.

8. Establishing a Rollout Plan

Once you're ready to launch your warehouse, it's time to start thinking about education, training, and use cases. Most of the time, it will be a week or two before your end-users start seeing any functionality from that warehouse (at least at scale). But they should be adequately trained in its use before the rollout is completed.

A rollout plan typically includes the following steps:

1. Identifying the target audience: This involves determining which groups or individuals within the organization will benefit from using the data warehouse.
2. Determining the data requirements: This involves identifying the types of data that the target audience needs access to and ensuring that this data is available within the data warehouse.
3. Developing user-friendly interfaces: This involves creating user interfaces that are intuitive and easy to use, and that provide users with the ability to interact with the data in meaningful ways.
4. Testing and refining: This involves conducting user testing to ensure that the data warehouse meets the needs of its users, and making adjustments as necessary.
5. Training users: This involves providing training and support to users to help them understand how to use the data warehouse effectively.
6. Deploying the data warehouse: This involves introducing the data warehouse to its intended users, and ensuring that the rollout process goes smoothly.

By establishing a rollout plan, organizations can ensure that their data warehouse is introduced effectively and that users are able to make the most of the valuable data that it contains.

Congratulations! You're ready to embark on your data warehouse design!

How Integrate.io Can Help

These are the core components of data warehouse design. But remember, your business may have different steps that aren't included in this list. Every data warehouse is different.

Integrate.io can simplify data warehouse integration by moving data from a source to a warehouse such as Redshift or Snowflake without lots of code or data engineering. This data pipeline platform’s pre-built native connectors simplify the data integration process so you can focus on other tasks in your business.

Integrate.io’s philosophy is to streamline data integration and make it easier to move data between locations. Schedule a demo now!